跳至主要內容

为什么 MySQL 数据库表加了索引还是查询慢？

程序员小富大约 7 分钟

为什么 MySQL 数据库表加了索引还是查询慢？

你觉得加了索引查询就一定会变快，是因为你只看到了“建索引”这个动作，没看到 MySQL 底层 B+ 树的检索机制以及随之而来的物理开销。

加了索引，MySQL 不一定会用；用了索引，也不一定会快。

索引失效：MySQL 优化器放弃了索引

即便表上建了索引，如果 SQL 编写得不够合理，MySQL 优化器在生成执行计划时也会直接无视索引，转而选择全表扫描。

来看几个开发中很常见的 SQL 写法：

1. 隐式类型转换（最隐蔽的坑）

假设 phone 字段是 VARCHAR(20) 类型，并且建了索引。来看这条查询语句：

SELECT * FROM user WHERE phone = 13800000000;

这条 SQL 能够查出数据，但它完全不走索引。因为传入的值是整型 13800000000，而字段类型是字符型。MySQL 为了进行比较，会自动把每一行的 phone 字段都强行转换成整型，相当于执行了下面的逻辑：

SELECT * FROM user WHERE CAST(phone AS signed) = 13800000000;

原本的 B+ 树索引是按照字符串排好序的，一旦每一行都要经过函数转换，其有序性就直接失效了。MySQL 只能放弃索引，老老实实去走全表扫描。

2. 在索引列上做计算或调用函数

SELECT * FROM `order` WHERE YEAR(create_time) = 2026;
-- 或者
SELECT * FROM `order` WHERE age + 1 = 18;

这种写法直观上很好理解，但对 MySQL 的 B+ 树来说，它存的是 create_time 和 age 的原始值。一旦给索引列套上了函数（如 YEAR）或者算术运算（如 +1），B+ 树就无法利用其有序性去进行快速的二分定位，最终退化为全表扫描。

3. 前缀通配符模糊查询

SELECT * FROM user WHERE name LIKE '%小富';

B+ 树索引是按字符从左到右排序的。如果是后置模糊匹配（如 name LIKE '小富%'），MySQL 还可以利用前缀定位到大概范围。但如果前缀是一个通配符 %，MySQL 根本不知道开头的字符是什么，索引的有序性在此完全派不上用场，只能走全表扫描。

4. 联合索引不符合“最左匹配原则”

如果建了一个联合索引 idx_a_b_c (a, b, c)，查询条件却跨过了 a：

SELECT * FROM t WHERE b = 1 AND c = 2;

联合索引的 B+ 树是严格按照 a -> b -> c 的顺序来排序的。在没有第一列 a 的过滤条件时，b 和 c 在索引树里其实是无序的。此时，MySQL 就无法使用这个联合索引，只能选择全表扫描。

回表：走索引省了力，但也可能让磁盘更痛苦

用 EXPLAIN 查看执行计划，发现 key 列显示确实用了索引，但查询依然很慢。这通常是卡在回表上了。

在 InnoDB 引擎中，聚簇索引（主键索引）的叶子节点存的是整行数据的完整记录，而二级索引（非聚簇索引）的叶子节点存的只有“索引列的值 + 主键 ID”。

看下面这个普通的查询：

SELECT * FROM user WHERE age = 18;

如果 age 字段建了索引，MySQL 的实际执行路径是这样的：

  1. 先去 age 二级索引的 B+ 树里，找到所有 age = 18 的记录，拿到对应的主键 ID（假设找到了 1 万个主键）。
  2. 回表：拿着这 1 万个主键 ID，再去主键索引里一条一条查出完整的整行数据。

在二级索引里的扫描是顺序的，非常快。但拿着 1 万个主键去主键索引里捞整行数据，大概率就是 1 万次磁盘随机 I/O。 MySQL 优化器非常现实，它在生成执行计划时会计算成本。如果符合条件的数据量过大，优化器评估后认为“与其去做上万次昂贵的随机 I/O，还不如直接去做全表扫描的顺序 I/O”，就会放弃二级索引。即便强制走了索引，大量的随机回表也会把查询时间拖得极长。

对应的优化思路：覆盖索引（Covering Index）

如果业务不需要整行数据，而是只需要 ID 和 age：

SELECT id, age FROM user WHERE age = 18;

因为所需要的数据在二级索引的叶子节点上已经全部存在了，MySQL 不需要再去主键索引里回表，速度就会有质的提升。

区分度：没效果的索引，优化器连看都不想看

有的字段建了索引，SQL 写法也没问题，但 MySQL 就是不用。这往往是因为字段的区分度太低。

区分度（Selectivity）的计算很简单：count(distinct col) / count(*)。这个值越接近 1，说明字段的重复率越低，索引的筛选效果越好。

如果把索引建在像**性别（男/女）或者订单状态（未支付/已支付）**这种只有两三个可能值的字段上，一千万条记录里男女各占一半。查询 gender = '男' 时，索引树筛选出来的是五百万条数据。为了这五百万条数据去跑回表，代价无法承受。因此，优化器会直接无视这个索引，选择全表扫描。

深分页 LIMIT：最常见的回表惨剧

在分页查询中，随着页码越往后翻，查询会变得越慢：

SELECT * FROM `order` ORDER BY create_time LIMIT 1000000, 10;

即使 create_time 建了索引，MySQL 的底层执行逻辑依然是：先通过索引读出前 1,000,010 条记录，做完 1,000,010 次回表，然后把前 1,000,000 条数据全部丢弃，只保留最后 10 条返回。 白白做了 100 万次昂贵的回表操作，这就是深分页慢的根本原因。

解决方案 1：延迟关联（Deferred Join）

可以利用覆盖索引先只获取 10 个主键 ID，然后再拿主键关联查出整行数据：

SELECT o1.* 
FROM `order` o1 
INNER JOIN (
    SELECT id FROM `order` ORDER BY create_time LIMIT 1000000, 10
) o2 ON o1.id = o2.id;

子查询里只走二级索引，因为不需要回表，速度极快。外层主查询最终只需要对这 10 条记录做回表，性能有了根本性的改观。

解决方案 2：游标分页（滚动分页）

在一些瀑布流或者滚动加载的场景中，可以通过记录上一页最后一条记录的 ID，利用主键的有序性直接过滤，从而避免使用 OFFSET：

SELECT * FROM `order` WHERE id > 1000000 ORDER BY id LIMIT 10;

外部瓶颈：SQL 没毛病，但被旁边的人卡住了

除了 SQL 和索引本身的设计问题，很多时候查询慢是由于数据库系统当时处于非健康状态：

  1. 锁等待：查询需要读取或者更新的行记录，正被其他长事务持有排他锁（写锁）。当前查询被迫在队列中等待，直到锁释放或者连接超时。
  2. Buffer Pool 命中率低：InnoDB 的缓存池容量不足，或者由于大批量历史数据的导入导致热点数据页被刷出内存。查询数据时必须频繁去磁盘读取，产生大量的磁盘物理 I/O 开销。
  3. 系统硬件资源耗尽：服务器 CPU 被其他复杂 SQL 压满，或者磁盘 IOPS 达到物理极限，查询线程在 CPU 队列里得不到足够的调度时片。

总结

索引是提高查询效率的手段，但绝对不是银弹。

在实际业务中，解决慢查询问题，最核心的动作是用 EXPLAIN 去看执行计划，明确数据检索的具体路径和成本。懂得如何建索引是基础，懂得 MySQL 如何做执行路径评估、计算磁盘 I/O 代价，才是优化数据库性能的本质所在。

上次编辑于: