揭秘索引失效与滥用的性能黑洞

发布于 2025-10-01 分类: 数据库
系列文章: MySQL开发避坑指南
第 1 部分: 别让数据类型和字符集成为性能瓶颈 第 2 部分: 揭秘索引失效与滥用的性能黑洞 (当前) 第 3 部分: 优化SQL查询，告别COUNT(*)、SELECT *与“万恶”的子查询 第 4 部分: 事务、锁与连接池的终极迷思

前言

在MySQL的世界里，“索引”无疑是决定性能好坏的核心。

很多开发者知道“慢查询要加索引”，但往往因为对索引底层原理的理解不足，导致加了索引却不起作用，或者滥用索引拖慢了整个系统。今天，我们就来揭开4个关于索引的性能黑洞，让你真正地“会用”索引。

坑六：索引失效的“隐形杀手”

最让人沮丧的事情莫过于：我明明给字段加了索引，为什么查询还是那么慢？这就是因为你的SQL写法触发了“索引失效”的条件，让优化器放弃了使用索引。

场景描述:
一张订单表 orders，created_at 字段是 DATETIME 类型，并且已经创建了索引 idx_created_at(created_at)。现在需要查询2025年10月1日的所有订单。

-- 看起来很自然，但却是索引杀手
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2025-10-01';

执行 EXPLAIN 后，你会发现在 type 列显示的是 ALL（全表扫描），key 列是 NULL，索引完全没有被用上。

问题剖析:
根本原因：对索引列使用函数或进行计算，会导致索引失效。

MySQL的索引（如B+树）中存储的是原始的、排好序的字段值。当你使用 DATE(created_at) 时，MySQL无法直接在索引树中进行定位，因为它不知道哪个 created_at 值经过 DATE() 函数计算后会等于 '2025-10-01'。为了得到结果，它不得不放弃索引，转而对表中的每一行都执行一次 DATE() 函数计算，然后进行比较。这就是全表扫描。

除了函数，以下情况也是常见的“隐形杀手”：

  1. 隐式类型转换: 如果 phone 字段是 VARCHAR(20) 并有索引，但你这样查询：WHERE phone = 13800138000（一个数字）。MySQL为了匹配类型，可能会将 phone 列的值转换为数字再进行比较，这相当于对列使用了函数 CAST(phone AS UNSIGNED)，导致索引失效。
  2. LIKE 以通配符开头: WHERE name LIKE '%john%'。索引是有序的，但你告诉MySQL从“不确定”的某个位置开始查找，它只能放弃索引，进行全表扫描。
  3. OR 连接非索引列: WHERE indexed_col = 'A' OR unindexed_col = 'B'。优化器可能会认为，既然 unindexed_col 无论如何都要全表扫描，那还不如整体都用全表扫描，省去回表的开销。

正确实践:
核心原则：保持索引列的“纯粹”，不要在 WHERE 子句中对它进行任何计算或函数操作。

  1. 转换查询条件，而非索引列: 对于日期查询，应该使用范围查询。

    -- 正确的写法，能完美命中索引
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2025-10-01 00:00:00' AND created_at < '2025-10-02 00:00:00';

    这样，MySQL可以直接利用 created_at 索引树进行高效的范围查找。

  2. 保持类型一致: WHERE phone = '13800138000'，给查询值加上引号。

  3. 前缀匹配: 如果业务允许，尽量使用 WHERE name LIKE 'john%'，这样索引仍然有效。

坑七：复合索引的“最左前缀”魔咒

复合索引（Composite Index）是提升多条件查询性能的利器，但如果不能理解其核心的“最左前缀原则”，建了也等于白建。

场景描述:
一个用户表 users，经常需要根据 city 和 age 来查询用户。于是创建了一个复合索引 idx_city_age(city, age)。

-- 查询1: 能用上索引
EXPLAIN SELECT * FROM users WHERE city = 'Beijing';

-- 查询2: 能用上索引
EXPLAIN SELECT * FROM users WHERE city = 'Beijing' AND age = 30;

-- 查询3: 无法使用索引进行快速定位
EXPLAIN SELECT * FROM users WHERE age = 30;

为什么查询3用不上索引？

问题剖析:
最左前缀原则（Leftmost Prefix Principle）: 对于一个复合索引 (col1, col2, col3)，查询条件必须从索引的最左边的列开始，并且不能跳过中间的列，索引才会生效。

你可以把复合索引想象成一本电话簿。电话簿是先按“姓氏”排序，再按“名字”排序的。

  • idx_city_age(city, age) 就好比是按 (城市, 年龄) 排序。
  • 你可以轻松找到所有“北京”的条目（查询1）。
  • 在“北京”的条目中，你也可以轻松找到所有年龄是“30”的条目（查询2）。
  • 但你无法直接找到所有年龄是“30”的条目，因为它们散落在“上海”、“深圳”等各个城市分类下，不是连续存放的（查询3）。

可视化理解:
用Mermaid图来模拟一下B+树的索引结构：

graph TD
    subgraph "复合索引 idx(city, age) 的逻辑结构"
        A("Beijing") --> B1("Beijing, 20");
        A --> B2("Beijing, 25");
        A --> B3("Beijing, 30");

        S("Shanghai") --> C1("Shanghai, 22");
        S --> C2("Shanghai, 30");

        Root --> A;
        Root --> S;

        D("查询: WHERE age = 30") -..-> E{无法定位, 需扫描所有城市};
        B3 -..-> D;
        C2 -..-> D;
    end

正确实践:

  1. 设计索引时，将最常用、区分度最高的列放在最左边。
  2. 确保你的查询条件遵循最左前缀原则。WHERE city='Beijing' AND age > 25 可以用上索引。WHERE city='Beijing' AND name='John'（假设name未在索引中）只能用到 city 部分的索引。
  3. 注意: WHERE age = 30 AND city = 'Beijing' 同样能用上索引。MySQL优化器足够智能，会自动调整 AND 条件的顺序来匹配索引。关键在于查询条件中是否包含了最左边的列。

坑八：索引并非“多多益善”

看到慢查询就加索引，这是很多人的肌肉记忆。但索引是需要成本的，无节制地添加索引，会带来新的问题。

场景描述:
一个高频写入的日志表 operation_logs，为了满足各种查询需求，运维人员给表上的7、8个字段都单独创建了索引。结果发现，应用的日志写入性能急剧下降。

问题剖析:
索引的维护成本:

  1. 空间成本: 每个索引都需要占用额外的磁盘空间。
  2. 时间成本: 当你对表进行 INSERT, UPDATE, DELETE 操作时，不仅仅是数据行需要变更，这张表上的每一个索引也都需要进行相应的更新（新增、删除、调整B+树节点）。索引越多，写操作的耗时就越长。

这就形成了一个经典的 读/写性能权衡。索引加快了读（SELECT），但减慢了写（INSERT, UPDATE, DELETE）。对于一个读多写少的表，多建一些索引是合理的；但对于一个写多读少的表（如日志表），过多的索引就是一场灾难。

正确实践:

  1. 按需创建: 只为真正高频的、性能影响大的查询创建索引。利用慢查询日志（slow query log）来定位问题。
  2. 使用复合索引: 尽量使用复合索引来覆盖多个查询场景，而不是为每个字段都建一个单列索引。例如，一个 idx(a, b, c) 索引，可以服务于 WHERE a=?，WHERE a=? AND b=?，WHERE a=? AND b=? AND c=? 三种查询。它比建立三个单列索引 idx(a), idx(b), idx(c) 要高效得多。
  3. 定期清理: 定期审查并删除那些不再使用或效果不佳的“僵尸索引”。MySQL 5.6及以上版本，可以通过 performance_schema 中的 table_io_waits_summary_by_index_usage 表来监控索引的使用情况。

坑九：对“低基数”列建索引的徒劳

是不是任何列，只要加了索引，查询就会变快？答案是否定的。

场景描述:
用户表 users 有一个 gender (性别) 字段，其值只有 'M', 'F', 'U' 三种。为它创建索引 idx_gender(gender) 后，执行 SELECT * FROM users WHERE gender = 'M';，发现查询速度和没加索引时几乎一样，优化器依然选择了全表扫描。

问题剖析:
索引的选择性（Selectivity）:
选择性是指索引列中不同值的数量（基数，Cardinality）与表中总行数的比率。比率越高，意味着索引的筛选能力越强，选择性越好。

  • 高选择性: 像 user_id, email 这样的唯一值字段，基数约等于总行数，选择性接近1，效果最好。
  • 低选择性: 像 gender 字段，基数只有3。如果表中有100万用户，WHERE gender = 'M' 可能会筛选出50万行数据。

当选择性很低时，MySQL优化器会算一笔账：

  • 方案A (走索引): 先通过索引找到50万条记录的磁盘地址（主键ID），然后根据这些地址，逐一“回表”到主键索引中去捞取完整的行数据。这个过程涉及大量的随机I/O，成本很高。
  • 方案B (全表扫描): 直接从头到尾读取整张表的数据，进行顺序I/O，然后筛选出符合条件的行。

当要筛选的数据量占全表的比例很大时（比如超过20%-30%），优化器会认为方案B的成本更低，从而放弃使用索引。

正确实践:

  1. 避免在低基数列上单独创建索引。
  2. 联合高基数列: 如果确实需要按性别查询，并且通常会带上其他条件，可以考虑将其作为复合索引的末尾列。例如，idx_city_gender(city, gender)。当你查询 WHERE city='Beijing' AND gender='M' 时，索引会先快速定位到“北京”这个高选择性的范围，然后再在这个小范围内筛选“M”，此时索引是高效的。

-- 感谢阅读 --