mysql执行计划是优化sql性能的关键工具，使用explain命令可查看其详细信息。1. id字段表示查询顺序，相同则从上到下执行，不同则值越大越先执行；2. select_type说明查询类型，如simple为简单查询，subquery为子查询，建议改写为join；3. table字段显示访问的表名；4. partitions显示分区表的命中情况；5. type为访问类型，all和index应避免，优先提升至eq_ref或ref；6. possible_keys列出可能使用的索引；7. key显示实际使用的索引，若为null需检查索引有效性；8. key_len用于判断是否使用组合索引全部列；9. ref显示索引匹配的具体列；10. rows表示预估扫描行数，越少越好；11. filtered表示过滤比例，越高越优；12. extra提供额外信息，如using index为覆盖索引，using filesort和using temporary应尽量避免。此外，索引失效常见于where中使用函数、类型不匹配、like以%开头、or条件未全用索引、组合索引未使用最左前缀等场景，可通过改写sql、添加索引等方式应对。结合慢查询日志分析可进一步优化数据库性能。
MySQL执行计划，简单来说，就是MySQL优化器对于SQL语句执行过程的预估。它能告诉你MySQL将如何使用索引、连接表，以及整个查询的执行顺序。理解执行计划是优化SQL性能的关键一步。
EXPLAIN命令是查看执行计划的利器。在SELECT语句前加上EXPLAIN，就能得到MySQL对该查询的执行计划报告。
EXPLAIN结果深度解析
EXPLAIN 语句会返回多行数据，每一行代表查询中的一个操作。以下是EXPLAIN结果中各个字段的详细解释以及如何利用它们来优化SQL语句：
1. id：查询的标识符
- 含义：表示SELECT查询的序列号，用于标识查询中操作的执行顺序。
- 值：
- 如果id相同，则执行顺序从上到下。
- 如果id不同，值越大优先级越高，越先被执行。
- 如果id为NULL，则表示这是一个UNION查询的结果。
- 优化思路：关注id的顺序，确保连接顺序合理，避免不必要的全表扫描。
2. select_type：查询的类型
- 含义：描述查询的类型，例如简单查询、子查询或UNION查询。
- 常见值：
- SIMPLE：简单查询，不包含子查询或UNION。
- PRIMARY：最外层的SELECT查询。
- SUBQUERY：SELECT或WHERE列表中包含的子查询。
- DERIVED：在FROM子句中出现的子查询。MySQL会将结果存放在一个临时表中，也称为派生表。
- UNION：UNION语句中的第二个或后面的SELECT查询。
- UNION RESULT：从UNION的临时表中检索结果。
- 优化思路：尽量避免SUBQUERY和DERIVED，因为它们通常会导致性能问题。可以尝试将子查询改写成JOIN。
3. table：查询访问的表
- 含义：表示查询访问的表名或别名。
- 值：直接显示表名或者表的别名。
- 优化思路：确认是否访问了正确的表，是否存在不必要的表连接。
4. partitions：表分区
- 含义：如果表是分区表，则显示查询访问的分区。
- 值：显示命中的分区。
- 优化思路：如果查询没有用到分区索引，可能会导致全部分区扫描，需要检查SQL语句和分区策略。
5. type：访问类型
- 含义：描述MySQL如何查找表中的行，是性能优化的关键指标。
- 常见值（从最佳到最差）：
- system：表只有一行记录，是const类型的特殊情况。
- const：通过主键或唯一索引一次就能找到。
- eq_ref：使用唯一索引查找，常见于主键或唯一索引的关联查询。
- ref：使用非唯一索引查找。
- fulltext：使用全文索引。
- ref_or_null：类似于ref，但是MySQL会对包含NULL值的列进行额外的搜索。
- index_merge：使用了索引合并优化策略。
- unique_subquery：用于替换IN子查询的一种形式，返回不重复值字段。
- index_subquery：类似于unique_subquery，但返回的是非唯一值字段。
- range：使用索引范围扫描。
- index：全索引扫描。
- ALL：全表扫描。
- 优化思路：尽量避免ALL和index，尽可能提升到ref或eq_ref。
6. possible_keys：可能使用的索引
- 含义：MySQL在查询中可能使用的索引。
- 值：列出可能用到的索引。
- 优化思路：即使possible_keys中有索引，MySQL也可能不使用。需要结合key字段来判断。
7. key：实际使用的索引
- 含义：MySQL实际使用的索引。
- 值：显示实际使用的索引名。
- 优化思路：如果key为NULL，但possible_keys不为NULL，说明MySQL认为没有合适的索引可用。需要检查索引是否有效，或者考虑创建新的索引。
8. key_len：索引的长度
- 含义：使用的索引的长度。在不损失精确性的情况下，长度越短越好。
- 值：计算得到索引长度。
- 优化思路：可以通过计算key_len来判断是否使用了组合索引的所有列。
9. ref：索引的哪一列被使用了
- 含义：显示索引的哪一列被使用了，常用于关联查询。
- 值：显示具体的列名或const。
- 优化思路：检查是否使用了正确的列进行索引匹配。
10. rows：估计需要检查的行数
- 含义：MySQL估计为了找到所需的行而需要读取的行数。
- 值：估计的行数。
- 优化思路：rows越小越好，说明MySQL需要扫描的行数越少。
11. filtered：过滤比例
- 含义：表示经过搜索条件过滤后剩余记录的百分比。
- 值：百分比。
- 优化思路：filtered越高越好，说明搜索条件过滤性越好。
12. Extra：额外信息
- 含义：包含MySQL解决查询的额外信息。
- 常见值：
- Using index：使用了覆盖索引，避免了回表查询。
- Using where：使用了WHERE子句过滤结果。
- Using temporary：MySQL需要创建临时表来存储结果，常见于ORDER BY和GROUP BY。
- Using filesort：MySQL需要使用文件排序，而不是索引排序。
- Using join buffer (Block Nested Loop)：使用了连接缓存。
- Impossible WHERE noticed after reading const tables：WHERE子句总是false，导致没有符合条件的行。
- Select tables optimized away：使用了某些优化策略，例如直接从索引中获取数据，而不需要访问表。
- Distinct：优化DISTINCT操作，当找到第一匹配的元组后停止搜索。
- 优化思路：
- Using temporary和Using filesort通常是性能瓶颈，应该尽量避免。可以通过添加索引来优化排序。
- Using index是好的，说明使用了覆盖索引。
索引失效的常见情况与应对
索引失效是导致查询性能下降的常见原因。以下是一些常见的索引失效情况以及相应的应对策略：
-
WHERE子句中使用函数或表达式：
- 例如：
WHERE DATE(order_date) = '2023-10-26'。 - 应对：尽量避免在WHERE子句中使用函数或表达式，可以将函数或表达式移到等号的另一边。例如：
WHERE order_date = STR_TO_DATE('2023-10-26', '%Y-%m-%d')。
- 例如：
-
类型不匹配：
- 例如：索引列是字符串类型，但WHERE子句中使用数字类型进行比较。
- 应对：确保WHERE子句中使用的数据类型与索引列的数据类型一致。
-
LIKE语句以
%开头：- 例如：
WHERE column LIKE '%abc'。 - 应对：尽量避免使用以
%开头的LIKE语句，如果必须使用，可以考虑使用全文索引。
- 例如：
-
OR条件：
- 如果OR连接的多个条件中，只有一个条件使用了索引，则MySQL可能会放弃使用索引。
- 应对：尽量使用UNION ALL代替OR，或者确保OR连接的所有条件都使用了索引。
-
组合索引未使用最左前缀：
- 例如：组合索引是(a, b, c)，但WHERE子句中只使用了b和c。
- 应对：确保WHERE子句中使用了组合索引的最左前缀列。
-
MySQL认为全表扫描更快：
- 当MySQL估计全表扫描比使用索引更快时，它可能会放弃使用索引。
- 应对：可以通过ANALYZE TABLE命令更新表的统计信息，或者强制使用索引（FORCE INDEX）。
慢查询日志分析
MySQL慢查询日志可以记录执行时间超过指定阈值的SQL语句。通过分析慢查询日志，可以找到需要优化的SQL语句。
-
开启慢查询日志：
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 设置阈值为1秒
-
查看慢查询日志文件位置：
SHOW VARIABLES LIKE 'slow_query_log_file';
-
分析慢查询日志：
可以使用
mysqldumpslow工具或者其他日志分析工具来分析慢查询日志。
总结
理解MySQL执行计划是SQL优化的基础。通过EXPLAIN命令，我们可以了解MySQL如何执行查询，并根据执行计划中的信息来优化SQL语句，例如添加索引、改写SQL语句等。同时，结合慢查询日志，可以找到需要优化的SQL语句，从而提升数据库的整体性能。
