MySQL性能调优：提升性能的关键步骤

缺乏、安全感 2023-10-12 22:56 9阅读 0赞

## 调优维度 ##

当我们讨论对数据库进行优化时，很多人第一反应想到的就是SQL优化，如何创建索引，如何改写SQL，他们把数据库优化与SQL优化划上了等号。

那今天我们站在架构的角度来聊聊这一问题，数据库优化可以从哪些维度入手？

![format_png][]

正如上图所示，数据库优化可以从架构优化，硬件优化，DB优化，SQL优化四个维度入手。

## 架构优化 ##

一般来说在高并发的场景下对架构层进行优化其效果最为明显，常见的优化手段有：分布式缓存，读写分离，分库分表等，每种优化手段又适用于不同的应用场景。

### 分布式缓存 ###

有句老话说的好，性能不够，缓存来凑。当需要在架构层进行优化时我们第一时间就会想到缓存这个神器，在应用与数据库之间增加一个缓存服务，如Redis。

### 读写分离 ###

一主多从，读写分离，主动同步，是一种常见的数据库架构优化手段。

![format_png 1][]

### 水平切分 ###

水平切分，也是一种常见的数据库架构优化手段。

当你的应用业务数据量很大，单库容量成为性能瓶颈后，采用水平切分，可以降低数据库单库容量，提升数据库写性能。

![format_png 2][]

### 架构优化总结 ###

1.读写分离主要是用于解决 “数据库读性能问题”  
2.水平切分主要是用于解决“数据库数据量大的问题”  
3.分布式缓存架构可能比读写分离更适用于高并发、大数据量大场景。

## 硬件优化 ##

对硬件设备和操作系统设置进行优化，例如调整操作系统参数、禁用 swap、增加内存、升级固态硬盘等等。

*  确保MySQL服务器的硬件资源足够，例如内存、磁盘空间和CPU等。
 *  调整MySQL的配置参数，如缓冲区大小、连接数限制等，以适应应用程序的需求。

我们使用数据库，不管是读操作还是写操作，最终都是要访问磁盘，所以说磁盘的性能决定了数据库的性能。一块PCIE固态硬盘的性能是普通机械硬盘的几十倍不止。这里我们可以从吞吐率、IOPS两个维度看一下机械硬盘、普通固态硬盘、PCIE固态硬盘之间的性能指标。

**吞吐率：单位时间内读写的数据量**

机械硬盘：约100MB/s ～ 200MB/s  
普通固态硬盘：200MB/s ～ 500MB/s  
PCIE固态硬盘：900MB/s ～ 3GB/s

**IOPS：每秒IO操作的次数**

机械硬盘：100 ～200  
普通固态硬盘：30000 ～ 50000  
PCIE固态硬盘：数十万

通过上面的数据可以很直观的看到不同规格的硬盘之间的性能差距非常大，当然性能更好的硬盘价格会更贵，在资金充足并且迫切需要提升数据库性能时，尝试更换一下数据库的硬盘不失为一个非常好的举措，你之前遇到SQL执行缓慢问题在你更换硬盘后很可能将不再是问题。

## DB优化 ##

通过[InnoDB架构概述][InnoDB]对于DB优化主要优化各种buffer、log。

SQL执行慢有时候不一定完全是SQL问题，手动安装一台数据库而不做任何参数调整，再怎么优化SQL都无法让其性能最大化。要让一台数据库实例完全发挥其性能，首先我们就得先优化数据库的实例参数。

数据库实例参数优化遵循三句口诀：**日志不能小、缓存足够大、连接要够用。**

数据库事务提交后需要将事务对数据页的修改刷（ fsync）到磁盘上，才能保证数据的持久性。这个刷盘，是一个随机写，性能较低，如果每次事务提交都要刷盘，会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法：

先将事务写到日志文件RedoLog（WAL），将随机写优化成顺序写  
加一层缓存结构Buffer，将单次写优化成顺序写  
所以日志跟缓存对数据库实例尤其重要。而连接如果不够用，数据库会直接抛出异常，系统无法访问。

接下来我们看下MySQL参数该如何配置。

![format_png 3][]

## SQL优化 ##

SQL优化很容易理解，就是通过建立合适的表结构、给查询字段添加索引或者改写SQL提高其执行效率，一般而言，SQL编写有以下几个通用的技巧： 重点来看前两个纬度，要点如下图所示。

![format_png 4][]

### 表结构及索引优化 ###

1.  合理使用索引
    
     *  索引少了查询慢；
     *  索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能
     *  选择率高（重复值少）且被where频繁引用需要建立B树索引；
     *  JOIN字段建议建立索引；
     *  复杂文档类型查询采用全文索引效率更好；
     *  索引的建立要在查询和DML性能之间取得平衡；
     *  复合索引创建时要注意基于非前导列查询的情况；
     *  创建联合索引时要根据最左原则考虑索引的复用能力，不要重复创建索引；要为保证数据不能重复的字段创建唯一索引等等。不过要注意索引对插入、更新等写操作是有代价的，不要滥用索引，比如像性别这样唯一很差的字段就不适合建立索引。
2.  表拆分
    
     *  要在设计表结构时，考虑数据库的水平与垂直扩展能力，提前规划好未来1年的数据量、读写量的增长，规划好分库分表方案。比如设计用户信息表，预计 1 年后用户数据 10亿 条，写 QPS 约 5000，读 QPS 30000，可以设计按 UID 纬度进行散列，分为 4 个库每个库 32 张表，单表数据量控制在 KW 级别。
     *  可以将字段多的表分解成多个表，必要时增加中间表进行关联。假如一张表有 40～50 个字段显然不是一个好的设计。
     *  一般来说，设计关系数据库时需要满足第三范式，但为了满足第三范式，我们可能会拆分出多张表。而在进行查询时需要对多张表进行关联查询，有时为了提高查询效率，会降低范式的要求，在表中保存一定的冗余信息，也叫做反范式。但要注意反范式一定要适度。
3.  选择合适数据类型
    
     *  要为字段选择合适的数据类型，在保留扩展能力的前提下，优先选用较小的数据结构。例如保存年龄的字段，要使用 TINYINT 而不要使用 INT。
     *  尽可能使用not null。MySQL 难以对使用 null 的列进行查询优化，允许 null 会使索引、索引统计和值更加复杂，允许 null 值的列需要更多的存储空间，还需要 MySQL 内部进行特殊处理。

### sql语句优化 ###

1.  确定合适的索引：
    
     *  通过使用适当的索引，可以加快查询速度。分析查询语句，确定最常用的查询条件和连接条件，并为这些字段创建合适的索引。
     *  使用单列索引或联合索引来覆盖查询条件和排序操作，以避免额外的表扫描和排序过程。
     *  使用最左前缀原则
        
        如果使用联合索引，要遵守最左前缀规则。即要求使用联合索引进行查询，从索引的最左前列开始，不跳过索引中的列并且不能使用范围查询(>、<、between、like)。
        
        ![format_png 5][]
2.  确保查询语句使用最有效的语法结构。例如，使用JOIN来替代子查询，或使用UNION ALL来替代UNION等。
    
     *  UNION ALL的执行效率比UNION高，UNION执行时需要排重；UNION需要对数据进行排序
3.  使用合适的JOIN类型（如INNER JOIN、LEFT JOIN等），以确保获取正确的结果集。
    
     *  在多表查询中，选择合适的JOIN操作（如INNER JOIN、LEFT JOIN等），以避免产生笛卡尔积和不必要的数据量。
     *  确保连接条件上有索引，以提高连接操作的性能。
4.  不用select \*，只选择需要的列
    
     *  更多消耗CPU、内存、IO、网络带宽
     *  避免查询出不需要使用的字段
     *  select/join减少磁盘临时表的生成，特别是有text/blob时
     *  每次查询都要回表，不能走覆盖索引。
5.  避免使用负向查询和%前缀模糊查询
    
     *  比如NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE %前缀等
     *  原因：B+树无法使用索引，导致全表扫描
6.  MySQL子查询
    
     *  子查询大部分情况比较差，子查询嵌套在主查询中，每次执行主查询时都需要执行子查询。这会导致在执行过程中进行多次查询操作，增加了数据库的负载和查询时间。
     *  避免复杂SQL语句 ：提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理
7.  小表驱动大表
    
     *  小表通常包含的数据量相对较少，通过将小表作为驱动表，可以减少参与查询的数据量。这样可以减少对大表的扫描和匹配操作，从而提高查询效率。
     *  小表上的查询通常可以更有效地使用索引，而大表上的查询可能需要进行全表扫描。通过以小表为基础进行查询，可以充分利用索引提供的快速数据访问能力。
     *  在进行小表驱动查询时，可以先对小表进行筛选和过滤操作，然后再与大表进行关联。这样可以减少参与关联的数据量，提前过滤掉不符合条件的数据，减少了大表的数据处理量。
8.  控制in中的个数，建议个数小于200
    
     *  IN操作符将多个值进行匹配，如果IN列表中的值较多，会导致数据库执行更复杂的查询操作，可能会增加查询的执行时间。较小的IN列表可以更快地完成查询操作。
     *  较大的IN列表可能占用较多的内存，特别是当数据库服务器配置较低或内存资源有限时，大量的IN值可能导致内存不足的问题。
     *  数据库查询优化器在处理IN操作时，可能会根据IN列表的大小做出不同的优化决策(`eq_range_index_dive_limit`)。较大的IN列表可能导致优化器选择不理想的查询计划，从而影响查询性能。
9.  避免全表扫描：
    
     *  使用索引来加速数据检索，避免全表扫描。
     *  确保表的统计信息是最新的，以帮助优化器选择合适的执行计划。
10. order by索引生效
    
     *  order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排序，那么排序的规则必须相同（同是升序或者降序），否则索引同样会失效。
11. 使用LIMIT限制结果集
    
     *  在需要返回大量数据的查询中，使用LIMIT来限制结果集的大小，避免不必要的资源消耗。

### 分析和优化慢查询 ###

*  要么是使用最频繁的语句，要么是优化后提高最明显的语句，可以通过查询 MySQL 的慢查询日志来发现需要进行优化的 SQL 语句；
    
        ini复制代码// 可以在配置文件中设置慢查询日志
        log-slow-queries = /tmp/mysql-slow.log
        long_query_time = 2
        // 设置慢查询开启
        set global slow_query_log='ON';
    
    慢查询日志会在查询结束以后才记录，所以在应用反应执行效率出现问题的时候慢查询日志并不能定位问题，此时应该使用 show processlist命令查看当前 MySQL 正在进行的线程。包括线程的状态、是否锁表等，可以实时的查看 SQL 执行情况。同样，使用mysqladmin processlist语句也能得到此信息。
 *  要学会利用 MySQL 提供的分析工具。例如使用 Explain 来分析语句的执行计划，看看是否使用了索引，使用了哪个索引，扫描了多少记录，是否使用文件排序等等。或者利用 Profile 命令来分析某个语句执行过程中各个分步的耗时。

### SQL优化小结 ###

这里给大家总结一下SQL优化的套路：

1.  查看执行计划 `explain sql`
2.  如果有告警信息，查看告警信息 show warnings;
3.  查看SQL涉及的表结构和索引信息
4.  根据执行计划，思考可能的优化点
5.  按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
6.  查看优化后的执行时间和执行计划
7.  如果优化效果不明显，重复第四步操作

[format_png]: https://img-blog.csdnimg.cn/img_convert/83aada7be0a36f65991d1d65a0ca37d4.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/01fc56d3a53debafc02425984476010f.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/65a5d4ebd0fa893ec81815c07faf5eeb.webp?x-oss-process=image/format,png
[InnoDB]: https://juejin.cn/post/7243019785100197947
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/11a14754a528e8040c0be97399587355.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/0f11521782fe87a86fa147a14b606431.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/c9abd8337cf6ef0394dd00542703a123.webp?x-oss-process=image/format,png