一、通过 EXPLAIN 分析 SQL 执行计划

通常，我们在执行一条 SQL 语句时，要想知道这个 SQL 先后查询了哪些表，是否使用了索引，这些数据从哪里获取到，获取到数据遍历了多少行数据等等，我们可以通过 EXPLAIN 命令来查看这些执行信息。这些执行信息被统称为执行计划。

假设现在我们使用 EXPLAIN 命令查看当前 SQL 是否使用了索引，先通过 SQL EXPLAIN 导出相应的执行计划如下：

字段说明：

• id：每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id。

• select_type：表示 SELECT 查询类型，常见的有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。

• table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。

• partitions：访问的分区表信息。

• type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。

• system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。如果是 B + 树索引，我们知道此时索引构造成了多个层级的树，当查询的索引在树的底层时，查询效率就越低。const 表示此时索引在第一层，只需访问一层便能得到数据。

• eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。

• ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。

• range：索引范围扫描，比如，<，>，between 等操作。

• index：索引全表扫描，此时遍历整个索引树。

• ALL：表示全表扫描，需要遍历全表来找到对应的行。

• possible_keys：可能使用到的索引。

• key：实际使用到的索引。

• key_len：当前使用的索引的长度。

• ref：关联 id 等信息。

• rows：查找到记录所扫描的行数。

• filtered：查找到所需记录占总扫描记录数的比例。

• Extra：额外的信息。

二、索引失效场景

1. 对索引列使用函数或表达式

场景：在WHERE子句中对索引列应用函数或运算，如 WHERE YEAR(create_time) = 2023 或 WHERE a + 1 = 5。

失效原因：MySQL无法直接使用索引值，需逐行计算。

优化：将计算移到条件右侧，如 WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' 或 WHERE a = 4。

1. 隐式类型转换

场景：索引列与查询值类型不一致（如字符串列用数字查询：WHERE phone = 123）。

失效原因：MySQL需隐式转换类型，导致索引失效。

优化：保持类型一致，如 WHERE phone = '123'。

1. OR条件未完全覆盖索引

场景：OR连接的条件中有未索引列，如 WHERE id = 1 OR name = 'Alice'（name无索引）。

失效原因：优化器可能选择全表扫描。

优化：为所有OR涉及的列建索引，或改用UNION。

1. 联合索引未遵循最左前缀原则

场景：联合索引为(a, b, c)，但查询未包含最左列a（如 WHERE b = 2）。

失效原因：索引按左到右匹配，缺失左列则无法使用。

优化：调整查询条件或索引顺序，确保包含最左列。

1. 使用不等于操作符（!= 或 <>）

场景：WHERE status != 'active'。

失效原因：不等于操作需扫描大量数据，优化器可能选择全表扫描。

优化：结合业务逻辑调整查询，或使用覆盖索引。

1. LIKE以通配符开头

场景：WHERE name LIKE '%abc'。

失效原因：前导通配符导致无法利用索引有序性。

优化：改用LIKE 'abc%'，或使用全文索引。

1. 范围查询后的索引列失效

场景：联合索引中，范围查询后的列无法使用索引（如 WHERE a > 10 AND b = 20，仅a生效）。

失效原因：范围查询会终止后续索引使用。

优化：调整索引顺序或拆分为多个查询。

1. 数据量过大导致优化器放弃索引

场景：查询结果集过大（如超过表30%）。

失效原因：优化器认为全表扫描更快。

优化：使用FORCE INDEX强制索引，或优化查询条件。

1. 字符集或校对规则不匹配

场景：多表关联时字段字符集不一致。

失效原因：隐式转换导致索引失效。

优化：统一字符集和校对规则。

1. ORDER BY导致索引失效

场景：排序字段顺序或方向与索引不一致（如索引为(a ASC, b DESC)，但查询为ORDER BY a DESC, b ASC）。

失效原因：索引无法支持不匹配的排序。

优化：调整索引或排序条件。

1. 其他场景

IN或OR参数过多：优化器可能选择全表扫描，需分批次查询。

索引列存在NULL值：IS NULL可能走索引，取决于数据分布。

统计信息不准确：定期执行ANALYZE TABLE更新统计信息。