MySQL索引失效的八种经典场景与优化器统计信息偏差

一、理解索引：查询性能的高速公路

索引是数据库表中特定列值的排序列表，它能极大加速数据检索。当索引“失效”，意味着数据库被迫进行全表扫描——像在没有目录的图书馆逐本书翻找，耗时剧增。识别并修复索引失效场景，是优化查询性能的核心。

优化器是决定如何执行查询的“大脑”，它依赖统计信息（如表行数、列值分布）来选择是否使用索引。统计信息不准确，优化器就会做出错误决策，导致本该走索引的查询退化为全表扫描。

二、八种经典索引失效场景与应对

1. 对索引列使用函数或进行计算

原因：索引存储的是原始列值。对列施加函数或计算后，其值与索引内容不再匹配。

错误示例：

SELECT * FROM orders WHERE YEAR(create_time) = 2023;

失效分析：索引是基于 create_time 原始值（如 2023-10-27 14:30:00）建立的，YEAR() 函数改变了值，无法匹配索引条目。

优化方法：改写查询，避免对索引列使用函数。将计算移至常量一侧。

SELECT * FROM orders
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

2. 隐式或显式类型转换

原因：索引列的数据类型与查询条件中的值类型不匹配，数据库需要先进行类型转换才能比较。

错误示例：

-- 假设 phone 列是 VARCHAR 类型，且有索引
SELECT * FROM users WHERE phone = 13800138000; -- 查询值是数字，列是字符串

失效分析：数据库为了比较，会将字符串类型的 phone 列逐一转换为数字，导致索引失效。这本质上等于对索引列使用了 CAST() 函数。

优化方法：确保数据类型严格一致。查询值应加引号。

SELECT * FROM users WHERE phone = '13800138000';

3. 使用 != 或 <> 操作符

原因：查询条件排除了大部分数据，直接扫描全表比先读取所有索引条目再过滤更高效。

错误示例：

SELECT * FROM products WHERE status != 'delisted';

失效分析：如果 status 列只有 ‘listed’ 和 ‘delisted’ 两种值，且数据分布均匀，优化器会估算出使用索引后仍需回表读取一半数据，成本高于全表扫描。

优化方法：使用 IN 列出所有需要的值，避免否定逻辑。

SELECT * FROM products WHERE status IN ('listed', 'pending', 'sold');

4. LIKE 以通配符 % 开头

原因：索引是前缀有序的，以任意字符开头无法利用其有序性。

错误示例：

SELECT * FROM customers WHERE name LIKE '%son';

优化方法：尽量将通配符放在末尾，或考虑全文索引。如果业务允许，反向存储并创建索引，然后使用 LIKE 'nos%' 查询。

5. OR 条件连接

原因：当 OR 两侧的条件分别涉及不同列，或一侧条件导致索引失效时，整个查询可能无法有效使用索引。

错误示例：

SELECT * FROM articles WHERE author = 'Alice' OR views > 1000;

失效分析：即使 author 和 views 各自有索引，优化器可能认为分别扫描两个索引再合并结果的成本高于全表扫描。

优化方法：使用 UNION ALL 改写查询，让每个子查询独立高效地利用索引。

SELECT * FROM articles WHERE author = 'Alice'
UNION ALL
SELECT * FROM articles WHERE views > 1000 AND author != 'Alice';

6. 联合索引未满足最左前缀原则

原因：联合索引 (col1, col2, col3) 像电话簿，先按姓排序，再按名排序。只按名查找，无法使用索引。

错误示例：

-- 存在联合索引 `idx_city_age_name` (city, age, name)
SELECT * FROM employees WHERE age = 30 AND name = 'Bob';

失效分析：查询条件跳过了索引的最左列 city，无法从索引根节点开始快速定位。

优化方法：调整查询条件顺序，或创建新的索引。优先确保查询条件包含索引最左列。

7. 数据选择性低，优化器主动放弃索引

原因：当索引列值重复率极高（如“性别”、“状态”列），使用索引筛选后仍需访问大量数据行，优化器会判断全表扫描更快。

错误示例：

-- `gender` 列只有 ‘M’, ‘F’ 两个值
SELECT * FROM users WHERE gender = 'M';

优化方法：评估索引的必要性。对于选择性极低的列，单独建立索引意义不大。可以考虑将其作为联合索引的一部分，或接受全表扫描。

8. 优化器误判：基于统计信息的代价估算

原因：这是最复杂也最核心的场景。优化器基于统计信息估算成本，如果统计信息过时或不准确，就会错误地放弃索引。

示例：表 logs 有 log_level 索引，其中 ‘ERROR’ 级别的记录只占 1%。但优化器统计信息过时，认为它有 20%。

SELECT * FROM logs WHERE log_level = 'ERROR';

失效分析：优化器根据过时的统计信息，高估了 ‘ERROR’ 记录的数量，误判使用索引后仍需读取大量数据，从而选择全表扫描。

优化方法：刷新统计信息。

ANALYZE TABLE logs;

执行后，优化器将获得 log_level 列的最新数据分布，重新做出正确决策，启用索引。

三、深入理解：优化器统计信息偏差

优化器统计信息是决策的基石。其偏差会引发严重的性能问题。

统计信息的核心指标：

• 基数 (Cardinality)：索引列中不重复值的数量。基数越高，索引选择性越好。
• 直方图 (Histogram)：描述数据分布情况。能准确告诉优化器 WHERE value < 100 会匹配多少行。

偏差产生的原因：

1. 数据大幅变更后未更新：进行大批量 INSERT、UPDATE、DELETE 后，数据分布已改变。
2. 自动更新间隔过长：MySQL 默认在表行数变化超过一定比例时自动更新，但间隔可能不符合业务节奏。
3. 采样数据不具代表性：对于大数据表，统计信息基于采样计算，可能失真。

如何判断与修复：

1. 查看当前统计信息：

   -- 查看表的统计信息状态（行数、平均行长度等）
   SHOW TABLE STATUS LIKE 'table_name';
   
   -- 查看索引的基数（Cardinality）
   SHOW INDEX FROM table_name;

2. 手动刷新统计信息：

   -- 最常用、影响最小的方式
   ANALYZE TABLE table_name;
   
   -- 对整个数据库执行（慎用，生产环境可能影响性能）
   -- mysqlcheck -a -u root -p database_name

3. 开启统计信息自动更新（谨慎）：在 my.cnf 中设置，但对于大表仍需关注。

   [mysqld]
   innodb_stats_on_metadata = OFF -- 推荐关闭，避免在查询元数据时频繁更新统计
   innodb_stats_auto_recalc = ON  -- 开启自动重新计算
   innodb_stats_persistent_sample_pages = 20 -- 增加采样页数以提高准确性

四、实战排查与优化流程

当遇到可疑的慢查询时，按以下步骤操作：

1. 获取执行计划：使用 EXPLAIN 或 EXPLAIN ANALYZE (MySQL 8.0+) 分析查询。

   EXPLAIN SELECT * FROM orders WHERE customer_id = 1001 AND status = 'completed';

   重点关注 type 列（ALL 为全表扫描）、possible_keys、key (实际使用的索引)、rows (预估扫描行数)。

2. 检查索引设计：确认相关列是否有索引，是否是合适的联合索引。

3. 验证统计信息：对涉及的表执行 SHOW TABLE STATUS 和 SHOW INDEX，查看 Cardinality 等数值是否合理。如果表近期有大量数据变更，立即执行 ANALYZE TABLE。

4. 审视查询语句：对照上述八种失效场景，检查SQL写法。尝试改写SQL以匹配索引。

5. 创建或调整索引：根据查询模式，使用 CREATE INDEX 创建新索引，或使用 FORCE INDEX 在测试中验证效果。

   CREATE INDEX idx_customer_status ON orders(customer_id, status);

6. 持续监控：使用慢查询日志或性能监控工具，持续观察优化效果。