Mysql索引篇：SQL调优 | 飙戈的博客

type

Post

status

Published

date

Jul 7, 2024

slug

summary

Mysql索引篇：SQL调优

1、什么是SQL调优

SQL 调优是指通过各种手段提升数据库查询性能的过程，旨在减少响应时间和资源消耗。SQL 调优的核心目标：

降低响应时间：让单条 SQL 执行得更快。

提高吞吐量：让数据库在单位时间内处理更多的请求。

减少资源消耗：降低 CPU、内存和 I/O 的压力。

SQL 调优是一个系统性的工程，涉及索引优化、查询语句重写、数据库参数调整等多个层面。一个高效的调优流程通常遵循以下步骤：

发现问题：通过监控、慢查询日志等找到需要优化的 SQL。

分析问题：使用工具（如 EXPLAIN）分析 SQL 的执行计划，定位瓶颈。

解决问题：根据分析结果，采取相应的优化手段（如加索引、改 SQL）。

验证效果：对比优化前后的性能，确认优化是否有效。

2、发现慢SQL

如果不知道哪些 SQL 慢，优化就无从谈起。

开启慢查询日志 (Slow Query Log)

这是最核心的方法。MySQL 可以将执行时间超过指定阈值的 SQL 语句记录到日志文件中。

使用性能模式 (Performance Schema) 和 sys 库

MySQL 5.7+ 的 performance_schema 和 sys 库提供了更强大的内部性能数据视图。

应用程序监控

使用 APM (Application Performance Monitoring) 工具，如 SkyWalking, Pinpoint 等，从应用层面发现数据库调用瓶颈。

3、分析执行计划（EXPLAIN）

使用 EXPALIN 可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 怎么处理你的SQL语句的，进而分析查询语句的性能瓶颈。注意 EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划。

字段解析如下：

id：每个 SELECT 关键字都对应一个唯一的id。如果是简单的单表查询，只有一个 SELECT 关键字，则只会有一条 id=1 的记录；如果是包含子查询的SQL语句，就可能有多个SELECT关键字，那么每个SELECT关键字都会对应一个唯一的id值。

select_type：查询的类型。

SIMPLE：SIMPLE 就是非常简单的查询，意味着不包含 UNION 或子查询，连接查询也算是 SIMPLE 类型。
PRIMARY：如果查询有任何复杂的子部分，如 UNION、UNION ALL、子查询，则最外层部分标记为 PRIMARY。
UNION：在 UNION 或 UNION ALL 子句中的第二个之后的 SELECT 都会被标记为 UNION。
UNION RESULT：UNION 查询需要使用内存临时表来去重，针对该临时表的查询的 select_type 就是 UNION RESULT，上面介绍 UNION 时可以看到这个类型。
SUBQUERY：包含在 SELECT 列表中的子查询中的 SELECT 会标记为 SUBQUERY，不包含 FROM 子查询。
DERIVED：DERIVED 用来表示包含在 FROM 子句的子查询中的 SELECT，MySQL 会将子查询的结果物化，放到一个临时表中，也称为派生表，因为该临时表是从子查询中派生来的。
DEPENDENT UNION、DEPENDENT SUBQUERY、DEPENDENT DERIVED：DEPENDENT 意味着 SELECT 依赖于外层查询中发现的数据。
MATERIALIZED：MATERIALIZED 意味着SQL中包含子查询（非FROM子查询），且子查询是物化的方式和外层查询进行连接查询的。
UNCACHEABLE SUBQUERY、UNCACHEABLE UNION：UNCACHEABLE 意味着 SELECT 中的某些特性阻止结果被缓存。

table：查询的表（别名），可以从这一列中从上往下观察SQL执行的关联顺序。

partitions：分区信息，一般情况下查询语句的执行计划的 partitions 列的值都是 NULL。

type：访问类型或者访问方法，就是MySQL决定如何查找表中的行。这个属性是分析执行计划时需要重点关注的信息，它告诉我们SQL是如何查询这张表的数据，有没有用到索引等等。优先级为：NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL。

NULL：这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。
system：当引擎是 MyISAM 或者 Memory 的时候并且只有一条记录，就是 system，表示可以系统级别的精准访问，这个不常见可以忽略。
const：查询命中的是主键或者唯一二级索引等值匹配的时候。比如 where id = 1。
eq_ref：通过主键或者唯一二级索引列等值匹配的方式进行查询，最多只会返回一条符合条件的记录。
ref：通过普通二级索引列与常量进行等值匹配时来查询某个表。相比 eq_ref，ref 可能返回多条符合条件的记录。
ref_or_null：类似于 ref，只是 ref_or_null 表示查询条件是 where second_key is null。
index_merge：一般情况下对于某个表的查询只会用到一个索引，但也可能同时使用多个索引，使用索引合并的方式来执行查询。不常见，可跳过。
unique_subquery：在一些包含 IN 子查询的SQL语句中，in 语句的时候命中了唯一索引。
index_subquery：类似于 unique_subquery，in 语句的时候命中了普通索引。
range：表示基于索引的范围扫描，比 index 全索引扫描好一些，因为它不用遍历全部索引。
index：命中了索引，但是需要扫描全部索引。index 类似于 ALL 全表扫描，不过 index 是顺序扫描索引的全部记录。它的主要优点是避免了排序，但如果要回表的话，开销可能比 ALL 更大。
ALL：没有使用索引，走的是全表扫描。应尽量避免这种方法的出现。

possible_keys：可能用到的索引

key：实际上使用的索引，也可能不在 possible_keys 中。想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。这个也是重点关注部分。

key_len：实际使用到的索引长度，不损失精确性的情况下，长度越短越好

ref：使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows：预估需要读取的记录条数

filtered：某个表经过搜索条件过滤后剩余记录条数的百分比。其实 filtered 在单表查询中意义不是很大，一般在多表连接中，则可用于预测被驱动表会被查询多少次。

Extra：扩展信息，但是非常重要，可以更好的辅助你定位 MySQL 到底如何执行的这个语句。从上往下性能越来越差。

Impossible WHERE：查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息。
Using index：性能最高，这意味着使用了覆盖索引，通过索引可以直接定位到满足查询条件的数据行，不需要进行回表查询。当我们查询条件和返回内容都存在索引里面，就可以走覆盖索引，不需要回表。Using index 同时适用于主键索引和二级索引。比如 select second_key from test where second_key = 10 。
Using index condition：经典的索引下推（ICP），将原来在 server 层进行的 table filter 中可以进行 index filter 的部分，在引擎层面使用 index filter 进行处理，不再需要回表进行 table filter。Using index condition 仅适用于二级索引，一般发生在查询字段无法被二级索引覆盖的场景。比如 select * from test where second_key > 10 and second_key like '%0’。
Using where：表示使用 where 的条件过滤数据，有可能命中了索引，也有可能没命中，不管是否命中都需要回表查询所需的数据。一般发生在查询的列未被索引覆盖，且 where 筛选条件是索引列前导列的一个范围，或者是索引列的非前导列，或者是非索引列等场景。因为需要回表，所以性能比前两者差。比如 select * from test where text = 't'。
Using temporary：出现 Using temporary 意味着 MySQL 内部使用了临时表来完成一些功能，比如去重、排序之类的，前面演示 UNION 时，因为 UNION 要对结果集去重，所以就使用了临时表。
Using filesort：查询没有命中任何索引，需要将数据查到内存中，或者在数据较大时放到磁盘中，再进行排序。比如 select * from test where text = 't' order by text desc limit 10。
Using join buffer (Block Nested Loop)：在连接查询执行过程中，当被驱动表没有索引或者不能有效的利用索引加快访问速度时，MySQL一般会为其分配一块名叫 join buffer 的内存块，然后将数据读到这块内存块中，再基于块中的数据做嵌套循环查询，以此来加快查询速度。

Extra 还有一些组合情况，比如：

Using where;Using index：表示查询的列被索引覆盖，且 where 筛选条件是索引列前导列的一个范围，或者是索引列的非前导列。一般发生在联合索引，且查询条件和返回内容都存在索引里面，这种效率也很高。例如：select id from test where id > 5; 。

另外注意 type=index 和 Extra=Using index 的区别：

type=index 表示命中了索引。

Extra=Using index 表示查询使用了覆盖索引，没有回表查询。

4、主要的优化手段

4.1 索引优化

索引优化是最有效的手段，我们先了解索引失效的场景。

4.1.1 索引失效的情况

联合索引不满足最左匹配原则：创建了一个 (a, b, c) 联合索引，如果查询条件是 where b=2 或者 where b=2 and c=3 ，因为不满足最左匹配原因导致索引失效。

LIKE 以通配符 % 开头：当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。

对索引使用函数： select * from t_user where length(name) = 6; 对 name 字段使用了 LENGTH 函数，导致索引失效。

对索引进行表达式计算：select * from t_user where id + 1 = 10; 对 id 字段进行计算后再比较，导致索引失效。如果把查询语句的条件改成 where id = 10 - 1 就可以走索引查询了。

对索引隐式类型转换：参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效。例如如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，例如 select * from t_user where phone = 1300000001; 会导致索引失效。但是如果索引字段是整型类型，查询条件中的输入参数即使字符串，是不会导致索引失效，还是可以走索引扫描，例如 select * from t_user where id = '1'; 。原因是 MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。

使用 OR 条件但未对所有条件建立索引：如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

使用不等于操作（!=，<>，NOT LIKE）：当查询条件为字符串时，使用 <> 或 != 作为条件查询有可能会导致索引失效。Mysql 优化器会自动判断，当查询结果集占比比较小时会走索引，占比比较大时不会走索引。

IS NOT NULL：查询条件使用 is null 时正常走索引，使用 is not null 时，不走索引。

NOT IN 和 NOT EXISTS：查询条件使用 not in 时，如果是主键则走索引，如果是普通索引，则索引失效。查询条件使用 not exists 时，索引失效。

ORDER BY 使用不当：当查询条件涉及到 order by、limit 等条件时，是否走索引情况比较复杂，而且与 Mysql 版本有关，通常普通索引，如果未使用 limit，则不会走索引。 order by 多个索引字段时，可能不会走索引。其他情况，建议在使用时进行 expain 验证。

4.1.2 索引的最佳实践

选择合适的列建立索引：只为经常出现在 WHERE 子句或 GROUP BY 子句中的列创建索引。索引字段要求

离散度高：离散度低会导致查到大量的重复数据，发生大量的回表操作
更新频率低：频繁更新会导致数据页面分裂，影响性能。

控制索引数量：索引会占用存储空间并影响写入性能，小表通常不需要索引，数据频繁更新且查询不频繁的表不宜过多索引，通常建议不超过 5-6 个索引。

多字段使用联合索引，联合索引遵循最左原则：如果是查询多字段，建立联合索引，并把将选择性高的列放在前面。

使用覆盖索引和索引下推：如果 SELECT 查询的所有列都包含在索引中，这种索引称为覆盖索引。覆盖索引可以发挥索引下推的特性，把 WHERE 条件下推到存储引擎层去过滤数据，减少回表操作，提高查询性能。

对于长字符串列，考虑使用前缀索引：可以为该列的前几个字符创建索引，也就是在二级索引的记录中只会保留字符串的前几个字符。比如我们可以为 phone 列创建索引，索引只保留手机号的前3位：ALTER TABLE user_innodb ADD INDEX IDX_PHONE_3 (phone(3));

4.2 SQL语句优化

SQL 语句优化的目的是减少无效的查询，并发挥索引的高效性能。常见的 SQL 语句优化方法：

避免 SELECT *：只取需要的字段。特别是 TEXT/BLOB 字段，SELECT * 会导致大量不必要的数据传输和缓存浪费。

优化 JOIN 操作：小表驱动大表，并确保 ON 或 USING 子句中的列上有索引。让结果集小的表作为驱动表，这样去遍历被驱动的大表表的次数也会少，可以减少磁盘IO，提高性能。驱动表的定义原则：

LEFT JOIN 左连接，左边为驱动表，右边为被驱动表
RIGHT JOIN 右连接，右边为驱动表，左边为被驱动表
INNER JOIN 内连接，Mysql 会选择数据量比较小的表作为驱动表，大表作为被驱动表。

优化分页查询 LIMIT：对于 LIMIT 1000000, 20 这种深度分页，效率极低。优化方案：使用延迟关联。

避免在 WHERE 子句中对字段进行函数操作或表达式计算：这会导致索引失效。

使用 UNION ALL 替代 OR：有时 OR 会导致索引失效，而 UNION ALL 效率更高（注意去重问题）。

使用 EXISTS 代替 IN：对于子查询，EXISTS 通常比 IN 性能更好。

4.3 数据库设计优化

选择合适的数据类型：越小越好，越简单越好。例如，用 INT 而不是 VARCHAR 存储数字，用 DATETIME 而不是 VARCHAR 存储时间。

范式与反范式的平衡：适当的反范式设计（如冗余字段）可以减少 JOIN 操作，以空间换时间。

垂直拆分：将包含大量 TEXT/BLOB 等大字段的表拆分开，避免影响核心表的查询性能。

水平拆分（分库分表）：当单表数据量过大时（如千万级以上），考虑进行分库分表。

4.4 服务器参数调优

InnoDB Buffer Pool Size (innodb_buffer_pool_size): 这是最重要的参数。它定义了 InnoDB 缓存数据和索引的内存大小。通常设置为可用物理内存的 70%-80%。

InnoDB Log File Size (innodb_log_file_size): redo log 文件大小。更大的 log file 可以减少磁盘 I/O，但会增加恢复时间。

Query Cache (query_cache_type and query_cache_size): 注意：MySQL 8.0 中已移除该功能。在 5.7 版本中，如果查询重复度不高，建议关闭（query_cache_type = 0），因为维护缓存的开销可能大于收益。

警告：不要盲目修改参数，最好在测试环境充分测试。调优应基于监控指标（如缓存命中率）。

4.5 定期分析和优化表

4.5.1 分析表ANALYZE TABLE

ANALYZE TABLE 用于更新表的统计信息，例如表的键分布统计信息，这些信息帮助优化器生成最佳的执行计划，例如决定表的连接顺序和索引使用。可以使用 show index from t_log_iteminfo 命令查看表的键分布统计信息。

注意字段 Cardinality，表示某个索引对应的列包含多少个不同的值。如果 Cardinality 和数据的实际数量相差太远，那么索引就基本失效了。例如上面的 t_log_iteminfo 全表才 226 万数据，但主键的索引基数居然有 509 万，说明统计信息不准确了。这时候就需要使用ANALYZE TABLE 来更新表的统计信息，帮助生成更准确的执行计划。

基本语法：

NO_WRITE_TO_BINLOG：设置 ANALYZE 操作不记录 binlog。默认会记录 binlog。

LOCAL：NO_WRITE_TO_BINLOG 的别名，效果等同 NO_WRITE_TO_BINLOG。

示例：

4.5.2 优化表OPTIMIZE TABLE

OPTIMIZE TABLE用于重组表的物理存储（重建表并更新表的统计信息），可以减少存储空间并提高 I/O 效率。OPTIMIZE TABLE对于有大量更新/删除操作的表特别有用，因为这些操作会产生大量的空间碎片。

OPTIMIZE TABLE 会进行锁表，在不同的执行引擎下有不同的行为：

对于 InnoDB 表：执行在线 ALTER TABLE 操作重建表，减少空间碎片。

对于 MyISAM 表：修复碎片化的数据文件并重建索引。

可以执行 show table status like 't_log_iteminfo' 命令查看表的空间碎片情况。

重点关注几个字段：

Data_free：已分配但未使用的空间（字节），可以简单理解为内存碎片。

Data_length：数据总长度（字节）。

Index_length：索引总长度（字节）。

一般来说 表大小 = Data_length + Index_length，这里 t_log_iteminfo 的表大小是 319MB，但 Data_free 却有 473MB，说明碎片很多了。需要执行 OPTIMIZE TABLE 命令进行碎片整理。

基本语法：

NO_WRITE_TO_BINLOG：设置 OPTIMIZE 操作不记录 binlog。默认会记录 binlog。

LOCAL：NO_WRITE_TO_BINLOG 的别名，效果等同 NO_WRITE_TO_BINLOG。

示例：

另外 InnoDB 表不完全支持传统的 OPTIMIZE TABLE 命令，执行 OPTIMIZE TABLE 命令可能会报错 Table does not support optimize, doing recreate + analyze instead。因为 InnoDB 有自己的空间管理机制，使用聚簇索引，数据存储方式与 MyISAM 不同。可以使用以下命令替代

OPTIMIZE TABLE 和 ANALYZE TABLE 的区别是什么？

特性	ANALYZE TABLE	OPTIMIZE TABLE
主要目的	更新统计信息	重组物理存储
影响性能	轻微影响	可能影响较大（表重建）
使用频率	可较频繁使用	应在必要时使用
适用场景	查询计划不佳时	大量更新/删除后