SQL 优化思路和经典案例分析

date

Oct 8, 2022

slug

sql-optimization-ideas-and-case-studies

status

Published

SQL 优化思路

开启慢查询日志，定位慢 SQL

默认情况下，MySQL 数据库是不开启慢查询日志（slow query log）的，需要时需要手动开启，常用命令如下：

通过慢查日志，定位那些执行效率较低的 SQL 语句，重点关注分析。

使用 explain 查看分析 SQL 的执行计划

explain 与 SQL 一起使用，将显示来自优化器的有关语句执行计划的信息。即 MySQL 解释了它将如何处理该语句，包括如何连接表以及以何种顺序连接表等信息。需要重点关注的值包括：type、rows、filtered、extra、key。

type

表示连接类型，查看索引执行情况的重要指标之一。

性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

system：这种类型要求数据库表中只有一条数据，const 类型的一个特例，极少出现；

const：通过一次索引就可以找到数据，如主键或唯一索引，查询效率高，速度快；

eq_ref：主键或唯一索引的关联查询；

ref：非主键或唯一索引的索引关联查询；

ref_or_null：类似于 ref，区别在于会额外搜索包含 NULL 值的行；

index_merge：查询时使用到了两个以上的索引；

unique_subquery：类似于 eq_ref，条件中有 in 子查询；

index_subquery：非主键或唯一索引的子查询，可能返回重复值；

range：范围查询，比如 between...and 或 in 操作；

index：全索引扫描；

ALL：全表扫描；

rows

表示 MySQL 估算查找需要读取的行数，对于 InnoDB 表，此值并不准确。

filtered

一个百分比的值，表示符合条件的记录数所占的百分比。

extra

包含 MySQL 如何解析查询的其他信息，如下：

Using filesort：表示指定文件排序的方式和索引排序不一致。如使用 order by 语句。

Using index：是否使用了覆盖索引；

Using temporary：是否使用了临时表，重点优化项。如使用 group by 或 union 语句。

Using where：使用 where 进行了条件过滤；

Using index condition：使用了 MySQL 5.6 之后新增的索引下推。在存储引擎层进行数据过滤，利用索引现有的数据减少回表的数据。

key

实际用到的索引，一般配合 possible_keys 一起查看。

开启 profiling 查询 SQL 真正执行线程的状态及消耗时间

explain 只是 MySQL 预估的执行计划，SQL 真正的执行线程状态及消耗的时间，需要手动开启 profiling 参数，记录 SQL 语句执行的资源开销，包括 IO、上下文切换、CPU、内存等。

常用指令如下：

使用 Optimizer Trace 分析 SQL 语句解析优化执行的全过程

profiling 只能查看 SQL 的执行耗时，但是无法看到 SQL 真正执行的过程信息，Optimizer Trace 可以跟踪执行语句解析优化执行的全过程。

常用指令如下：

使用方式为：

通过分析执行树可以发现，其过程包括三个阶段：

join_preparation：准备阶段；

join_optimization：分析阶段；

join_execution：执行阶段；

确定问题并采用相应的措施

索引相关的问题，如没有加索引、索引不生效、不合理等，可以优化索引；

SQL 语句不合理，需要优化 SQL 语句，如 in 元素过多，可以进行分批查询；深分页问题，可以基于上一次的数据进行过滤；查询数据过多，可以进行时间分段查询；

SQL 如果没有办法进行很好的优化，可以改用 ES 或者数仓；

单表数据过大导致慢查询，可以考虑分库分表；

刷脏页导致慢查询，可以和 DBA 讨论，考虑优化参数；

存量太大可以考虑进行归档；

更多分析可以参考盘点 MySQL 慢查询的 12 个原因

经典案例分析

隐式转换导致索引失效

查询 userId 条件为数字时，索引失效，如下：

字符串与数字比较，类型不匹配，MySQL 会做隐式类型转换，把它们转换为浮点数再做比较。此时，索引会失效。

不满足最左匹配原则导致的索引失效

最左匹配原则：MySQL 建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个 (a,b,c) 的联合索引，相当于建立了 (a)，(a,b)，(a,b,c) 三个索引。

执行以下语句时：

因此查询条件为 name，而不是 idx_userid_name 中的第一列，不满足最左匹配原则，导致索引不生效。

limit 深分页导致慢查询

执行以下 SQL:

SQL 的执行流程为：

通过普通二级索引树 idx_create_time，过滤 create_time 条件，找到满足条件的主键 ID；

回表，取出需要展示的列；

扫描满足条件的 100010 行，扔掉前 100000 行，返回。

可以看到，limit 深分页导致需要扫描 offset+n 行，回表更多的次数。

解决办法有两种：

标签记录法

标记一下上次记录到哪条数据了，下次再来查的时候，从标记点开始扫描，就像看书加书签。

该方法需要有一个连续自增的字段。

延迟关联法

延迟关联法即把条件转移到主键索引树，减少回表。

优化思路就是先通过 idx_create_time 二级索引树查询到满足条件的主键 ID，再与原表主键 ID 内连，从而减少回表。

in 元素过多导致慢查询

in 元素建议不超过 200 个，如果超过了，建议分组。

in 查询为什么慢呢？
这是因为 in 查询在 MySQL 底层是通过 n*m 的方式去搜索的，类似于 union。in 查询在计算 cost 的时候，是通过将 in 中包含的数值，一条一条去查询，获取元数据的，这个过程比较慢，MySQL 5.6 版本设置了一个临界值 eq_range_index_dive_limit，默认是 200，超过 200 个后，代价计算就存在问题，可能会导致 MySQL 选择索引不准确。

order by 导致的慢查询

order by 排序，分为全字段排序和 rowid 排序，如果结果的行数超过 max_length_for_sort_data，就会走 rowid 排序，反之，则走全字段排序。

rowid 排序

执行过程如下：

MySQL 为对应线程初始化 sort_buffer，放入需要排序的 age 字段和主键 ID；

从索引树 idx_city 找到第一个满足条件 city='深圳' 条件的主键 ID；

根据主键 ID 拿到整行数据，将 age 和主键 ID 存储至 sort_buffer；

重复 2、3 步骤，拿到所有符合条件的数据；

将所有数据根据 age 进行排序，取前 10 行数据，根据主键 ID 取出 city、name 和 age 三个字段的值；

全字段排序

执行过程如下：

MySQL 为对应线程初始化 sort_buffer，放入需要查询的 name、age、city 字段；

从索引树 idx_city 找到第一个满足条件 city='深圳' 的主键 ID；

根据主键 ID 拿到整行数据，将 name、age、city 三个字段的值存储至 sort_buffer；

重复 2、3 步骤，拿到所有符合条件的数据；

将所有数据根据 age 进行排序，直接将前 10 行数据返回给客户端；

结论

可以看出，rowid 排序需要回表，全字段排序会占用更多的 sort_buffer。

sort_buffer 的大小是由一个叫做 sort_buffer_size 的参数控制的：

如果要排序的数据小于 sort_buffer_size ，排序在 sort_buffer 中完成；

如果要排序的数据大于 sort_buffer_size，则借助磁盘文件进行排序；

借助磁盘文件进行排序，效率会比较慢，因为需要先把数据放入 sort_buffer，当快要满的时候，进行一次排序，然后把 sort_buffer 中的数据放入临时的磁盘文件，如此重复，直至所有数据都查完排序好，再用归并算法将磁盘中的临时排序好的文件合并成一个有序的大文件。

如何优化

从存储入手，存储时就按照顺序进行存储；

从实际需求入手，根据实际情况，调整 max_length_for_sort_data 、sort_buffer_size。

null 引发的索引失效

!= 或者 <> 可能导致索引失效

MySQL 优化器会进行判断，此时走索引会扫描更多的行。使用时需要特别注意。

编码格式不同导致的索引失效

其中 user 表的 name 字段是 utf8mb4，user_job 表的 name 字段编码为 utf8，执行如下查询时，索引不会生效：

解决办法是将字段的编码格式统一。

group by 导致的慢查询

group by 进行分组统计时，会使用临时表，可能会导致慢查询。

可以看到，Extra 字段既有 Using temporary（使用了临时表），也有 Using filesort（使用了文件排序）。

SQL 的执行顺序如下：

创建临时表，存储 name 和 count；

全表扫描

如果临时表中有 city='X'，count 值加 1；
如果临时表中没有 city='X'，插入一条记录，count 值设为 1；

对临时表进行排序；

group by 为什么会慢？

临时表大小如果超过了上限，即 tmp_table_size，会将内存临时表转为磁盘临时表；

排序过程使用的临时表可能占用大量的磁盘空间；

如何优化？

group by 后面的字段加索引；

不进行排序；

尽量只使用内存临时表；

使用 SQL_BIG_RESULT 告诉 MySQL 当前 select 语句返回的结果集较大；

delete + in 子查询导致索引失效

执行 SQL 语句如下：

如果把 delete 换成 select ，子查询就会走索引。这是为什么呢？

因为 MySQL 对 select in 子查询做了优化，把子查询改成 join 的方式，所以可以走索引，但是对于 delete in，MySQL 没有做这个优化。