深入分析mysql索引失效的场景

雷渊
89 阅读5分钟

知其然要知其所以然，探索每一个知识点背后的意义，你知道的越多，你不知道的越多，一起学习，一起进步，如果文章感觉对您有用的话，关注、收藏、点赞，有困惑的地方评论，我们一起交流！

1. 对索引列进行运算或函数处理

  • 场景：在WHERE子句中对索引列使用函数、正则表达式或类型转换。
  • 示例： 
    SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 索引列使用函数
SELECT * FROM products WHERE price * 2 > 100;       -- 索引列参与运算
  • 原因：索引存储的是原始值，运算后的值无法直接匹配索引结构。

2. 隐式类型转换

  • 场景：查询条件与索引列数据类型不一致，触发隐式转换。
  • 示例： 
    -- 假设 phone 是 VARCHAR 类型且有索引
SELECT * FROM users WHERE phone = 13800138000; -- 数字转为字符串
  • 原因：类型转换相当于对列使用函数，导致索引失效。

3. 不符合最左前缀原则（联合索引）

  • 场景：联合索引未按定义顺序使用，或跳过左侧列。
  • 示例（联合索引 (a, b, c)）： 
    SELECT * FROM table WHERE b = 2 AND c = 3;      -- 缺少最左列 a
SELECT * FROM table WHERE a = 1 AND c = 3;      -- 跳过中间列 b
  • 原因：联合索引按定义顺序排序，跳跃列无法利用索引有序性。

4. 使用前导通配符的LIKE查询

  • 场景：LIKE模式以%或_开头。
  • 示例： 
    SELECT * FROM articles WHERE title LIKE '%MySQL%'; -- 前导通配符
  • 例外：覆盖索引可能被使用（仅查询索引字段时）。

5. OR连接非索引列条件

  • 场景：OR条件中包含未建立索引的列。
  • 示例： 
    -- 假设 name 有索引，age 无索引
SELECT * FROM users WHERE name = 'John' OR age = 25;
  • 原因：MySQL无法单独使用索引，需合并结果集，导致全表扫描。

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

  • 场景：在联合索引中，范围查询（>、<、BETWEEN）右侧的列无法使用索引。
  • 示例（索引 (a, b, c)）： 
    SELECT * FROM table WHERE a > 10 AND b = 5; -- b 无法使用索引
  • 优化：调整索引顺序或拆分查询。

7. 数据分布不均导致全表扫描

  • 场景：索引列区分度极低（如性别、状态标志）。
  • 示例： 
    -- gender 列只有 'M'/'F' 两种值
SELECT * FROM customers WHERE gender = 'M';
  • 原因：优化器认为全表扫描比索引回表更高效。

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

  • 场景：WHERE条件中使用 != 或 <>。
  • 示例： 
    SELECT * FROM orders WHERE status != 'completed';
  • 例外：覆盖索引可能被使用（仅查询索引字段时）。

9. ORDER BY/GROUP BY未利用索引

  • 场景：排序或分组字段顺序与索引不匹配。
  • 示例（索引 (a, b)）： 
    SELECT * FROM table ORDER BY b, a; -- 顺序颠倒
SELECT * FROM table GROUP BY b;    -- 缺少最左列 a
  • 优化：调整索引或查询语句顺序。

10. 索引列存在大量NULL值

  • 场景：索引列允许NULL且存在大量NULL时。
  • 示例： 
    SELECT * FROM products WHERE category_id IS NULL; -- 可能全表扫描
  • 建议：设置默认值替代NULL，或使用WHERE category_id = 0。

11. 优化器误判（统计信息过时）

  • 场景：表数据分布变化后未更新统计信息。
  • 检测：使用 EXPLAIN 查看执行计划。
  • 修复：执行 ANALYZE TABLE table_name; 更新统计信息。

12. 索引选择性过高

  • 场景：查询条件匹配几乎所有索引条目。
  • 示例： 
    -- 假设索引列 is_deleted 的99%值为0
SELECT * FROM orders WHERE is_deleted = 0;
  • 结果：优化器可能选择全表扫描。

13. in的查询数量非常非常大可能会导致索引失效

  • 场景：in的列表元素超过表总行数的20%～30%，索引可能失效
  • 分析：当IN列表中的元素数量很大时，优化器可能会认为使用索引不如全表扫描高效，尤其是当索引的选择性不高或者表中的数据量很大时。优化器会根据统计信息来估计不同执行计划的成本，选择成本最低的那个。另外，还要考虑索引的类型。如果是覆盖索引，即查询的所有列都包含在索引中，可能仍然会使用索引，因为不需要回表查询数据行。但如果不是覆盖索引，即使使用索引，也需要回表，这可能会增加额外的I/O操作。
  • 解决方案：
    • 分批次查询：将大 IN 列表拆分成多批次（如每批 1000 个值）
    • 调整优化器参数：增大 range_optimizer_max_mem_size 以允许优化器处理更大的 IN 列表：SET SESSION range_optimizer_max_mem_size = 104857600; -- 100MB
    • 强制使用索引（慎用）：
        SELECT * FROM users FORCE INDEX (PRIMARY) 
    WHERE id IN (1,2,...,100000);

14. 使用 NOT IN  可能导致索引失效

  • 分析：NOT IN (NULL, ...) 等价于 NOT (value = NULL OR value = ...)，而NULL的比较结果未知。这可能导致查询优化器无法有效使用索引，或者改变执行计划。
场景NOT IN
子查询结果集大大概率失效（全表扫描）
子查询结果集小可能使用索引
子查询含 NULL 值必定失效（逻辑陷阱）
  • 建议：
      1. 优先使用 NOT EXISTS 
      1. 避免在 NOT IN 子查询中返回 NULL 或大数据集

索引失效一般解决方案

  1. 优化查询语句：避免索引列运算，使用最左前缀。
  2. 调整索引设计：
    • 增加覆盖索引。
    • 对高频查询条件创建独立索引。
  3. 强制索引（慎用）： 
    SELECT * FROM table FORCE INDEX (index_name) WHERE ...;
  4. 数据归档：拆分冷热数据，减少单表数据量。
  5. ...视情况而定
avatar
文章
阅读
粉丝