关于MySQL性能优化的理解

清疚 2023-10-14 02:55 63阅读 0赞

### 关于MySQL性能优化的理解 ###

*  四种优化方案
 *   *  1. 硬件和操作系统层面的优化
     *  2. 架构设计层面的优化
     *  3. MySQL程序配置优化
     *  4. SQL执行优化
 *  常见的SQL优化规则

# 四种优化方案 #

MySQL性能优化有4个部分：

1.  硬件和操作系统层面优化
2.  架构设计层面优化
3.  MySQL程序配置优化
4.  SQL执行优化。

## 1. 硬件和操作系统层面的优化 ##

从硬件层面上来说影响mysql性能因素主要是cpu、可用内存大小、磁盘读写速度、网络带宽。

从操作系统层面上来说，应用文件句柄数、操作系统的网络配置都会影响到mysql的性能优化。

这部分的优化一般都是DBA或者运维工程师去完成。

在硬件基础资源的优化中我们重点关注的是服务本身所承载的体量，然后提出合理的指标要求避免出现资源浪费的一个现象。

## 2. 架构设计层面的优化 ##

MySQL是一个磁盘io访问非常频繁的关系型数据库，在高并发和高性能的场景中MySQL数据库必然会承受巨大的并发压力。

在此时，我们的优化的方式主要可以分几个部分：

1.  **搭建mysql主从集群**。单个mysql服务容易导致单点故障，一旦服务宕机将会导致依赖mysql数据库的应用全部无法响应。主从集群或者主主集群都可以去保证服务的高可用性。
2.  **读写分离设计**。在读多写少的场景中通过读写分离的方案可以去避免读写冲突导致性能问题。
3.  **引入分库分表的机制**。通过分库可以降低单个服务器节省的一个io压力，通过分表的方式可以降低单表数据量从而去提升sql查询效率。
4.  **针对热点数据可以引入更为高效的分布式数据库**。比如Redis、MongoDB等，他们可以很好的缓解mysql的访问压力，同时还能提升数据的检索性能。

## 3. MySQL程序配置优化 ##

MySQL是个经过互联网大厂检验过的生产级别的成熟数据库。对于mysql数据库本身的优化一般可以通过配置文件my.cnf来完成。

第一个，比如说mysql5.7版本**默认的最大链接数量**是151个。这个值可以在my.cnf中修改。

第二个**binlog日志**默认是不开启，我们也可以在这个文件中去修改**开启**。

第三个是**缓存池Bufferpool默认大小配置**等。而这些配置一般是和用户的安装环境以及使用场景有关系。因此这些配置官方只会提供一个默认的配置。

具体的情况还是要有使用者去根据实际情况去修改。

关于配置项去修改需要注意两个层面：

1.  配置的作用域，他可以分为会话级别和全局范围。
2.  是否支持热加载。

因此针对这两个点我们需要注意的是全局参数的设定对于已经存在的会话是无法生效的，会话参数的设定，随着会话的销毁而失效。

全局的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效。

## 4. SQL执行优化 ##

sql优化又可以分为三个步骤：

1.  **慢sql的定位和排查**，我们可以通过慢查询日志和慢查询工具分析得到有问题的sql列表。
2.  执行计划分析针对慢sql可以**使用关键字explain来去查看当前sql的执行计划**，可以重点关注type、key、rows、filterd等字段从而去定位该sql执行慢的根本原因再去有地放失的进行优化。
3.  **使用show、profile工具**，它们是sql自带的去**分析当前会话中sql语句资源消耗情况**的工具。可以用于sql调优的测量，在当前会话中默认情况下show、profile是关闭状态，打开之后会保存最近15次的运行结果，针对运行慢的sql通过profile工具进行详细分析可以得到sql执行过程中所有资源的开销情况，比如io开销、cpu开销、内存开销等.。

# 常见的SQL优化规则 #

1.  SQL查询**一定要基于索引**来进行数据扫描。
2.  **避免索引列上使用函数或者运算符**，这样会导致**索引失效**。
3.  where字句中**like** **%号，尽量放置在右边**。
4.  使用索引扫描，**联合索引中的列从左往右**，**命中越多越好**。
5.  尽可能使用SQL语句用到的**索引**完成**排序**。避免使用文件排序的方式。
6.  查询有效的列信息即可。**少用**\*代替列信息。
7.  永远用小结果集驱动大结果集。

**参考资料**：[高频面试题：关于MySQL性能优化，这么回答分分钟拿下高薪offer][MySQL_offer]

[MySQL_offer]: https://www.bilibili.com/video/BV17e411w7EM/?spm_id_from=333.337.search-card.all.click&vd_source=a1af903e57688df870625021f7f3a7eb