MySQL|MySQL慢SQL调优 mysql|数据库|java|sql

前言在日常开发工作中，数据库是常用的数据存储组件，一旦使用了数据库，那慢查询SQL的优化是绕不开的一道坎，本文旨在分享下自己对平时开发工作中进行SQL调优的理解。本文使用的MySQL版本为 8.0.25。
优化的步骤 1.捕获慢查询SQL
可以通过开启MySQL的慢查询日志或通过一些框架本身（如Druid）提供的慢查询日志监控功能来捕获慢查询SQL。
2.使用 explain 分析 SQL

文章图片

explain 的执行结果包含上面图中的若干字段，重点关注的字段是 type 和 extra。
type取值，从上至下效率越来越高

ALL：扫描全表数据行
index：索引行全扫描
range：使用索引进行范围扫描，between、in、>、< 等
ref：使用非唯一索引或唯一索引前缀进行扫描，返回索引过滤的数据，可为多条，常出现在关联查询中；如下列sql，其中列 dept_id 上建有普通索引
select username from t_user inner join t_dept using(dept_id) where t_dept.dept_id = 10;
eq_ref：与 ref 类似，区别在于是使用唯一索引进行关联扫描
const / system：表示通过索引一次就查询到了相关记录，一般为主键或唯一索引查询，system 为 const 的特殊情况，表中仅有一条数据。

extra取值

Using filesort：文件排序，表示MySQL需要通过语句查找所有匹配的行，并保存排序关键字与行指针，再通过对关键字进行排序，并按排序后的顺序检索出对应的行。
Using temporary：使用临时表保存中间结果，常见的情况有使用 distinct 关键字、join 语句中使用 order by、使用 group by 无索引列、order by 与 group by 字段不同、union 子查询等。
Using where：未使用上索引，使用 where 条件扫描过滤。
Using index：表示使用了索引覆盖，仅从索引中就可以查找到对应的数据，不需要访问表。如果与 Using where 一起出现则表示索引用于查询过滤，还需回表查询出所需数据。
Using index condition：5.6版本之后新增的功能，称为索引下推；通过使用索引对存储引擎索引出的数据进行再过滤，减少回表查询的次数。

3.根据执行计划进行优化
根据上述 explain 执行出来的结果，可以从中发现慢查询SQL中的问题。
对于没有走索引的查询，通过添加适当的索引，注意需对照原表上的索引，看看有没有需要合并成联合索引，避免构建过多的索引，占用空间和影响插入/更新的效率。
对于一些有索引的语句，却没有正确走索引进行查询的，则需要进一步分析索引失效的情况，下面列出常见的一些索引失效的场景。
1) 全表扫描代价更低：MySQL通过代价计算，发现使用全表扫描会比索引的代价更低，则会放弃使用索引进行查询。一般情况下这类SQL都是比较简单的，如果作为慢SQL被发现可能是因为数据量过大，可能需要通过其他方式处理。
2) 联合索引最左匹配原则：查询语句 select * from table where index2 = 'xx' 是无法使用上索引 IX(index1, index2)，可以考虑转换联合索引顺序或新建一个index2索引（如果该联合索引有被其它语句使用）。
3) where语句中包含or：where语句中需要使用or的情况下，需or相连的所有字段都有索引才行。
4) 索引列上加函数：在查询的索引列上使用内置函数都会让索引失效。
5) 对索引列运算：与使用函数相似，都是会使得索引列值发生变化，从而无法使用索引。
6) 隐式转换：select * from t_user where tel = 123; tel字段是varchar类型，查询中使用数值进行匹配会使得执行时对该值进行类型转换，使得索引失效。
7) 负向查询和is NULL判断可能导致索引失效：负向查询包括 NOT、<>、!>、!<、!= 等。
8) asc和desc混用：select * from t order by a asc, b desc;
9) 范围查询阻断后续字段不能走索引：范围查询包括 >=、<=、>、<、in、between。例：select * from t_user where dept_id = 1 and create_date > '2022-05-04 00:00:00' and position_id = 10; 索引 IX_deptid_createdate_positionid(dept_id, create_date, position_id)，在执行查询语句时，字段 position_id 的索引不生效，仅使用了前缀 dept_id。
针对上述索引失效的情况，进行相应的索引变更或是修改对应的SQL语句，使得SQL语句能走上正确的索引。以上是基于经验法则对SQL进行优化，可能会在不同的MySQL版本和不同的数据量的情况下呈现不一样的结果，在实践过程中如果能有生产备份库，可以修改后在备份库使用执行计划查看新改动的SQL是否按预期走索引；没有备份库或者不可直接使用备份库的情况下也应在生产环境跟踪下改动后SQL的执行情况。
4.按预期走了索引的慢SQL
如果SQL按预期走了索引进行查询，但SQL语句依然执行过慢，可能有下面几种情况。
大分页查询：select * from t where a = 1 limit 10000, 10; 在分页查询数据量较大，分页选择靠后的页码时，正常的 limit 语句效率会显著降低，可通过延迟关联的形式提高效率。详见大数据量 limit 分页优化
超大数据量：对于表中数据量已经特别庞大的情况下，这个时候需要根据业务场景，考虑是否能将较远的历史数据进行归档，然后删除原表中的历史数据，降低原表数据量；有些场景可聚合业务数据，把对原表的查询转而查询聚合表；有字符串查询的场景可以考虑使用ES进行检索；如果业务量确实很大，可以考虑使用分库分表。
【MySQL|MySQL慢SQL调优】复杂SQL：对于这类SQL，一般而言是统计类型的查询，可以看看业务上是否可允许进行定期统计并以输出报表记录的形式；如果业务上本身就存在这么复杂的查询，就不建议使用SQL，可采用其他的形式进行解决，如ES等。