MySQL 索引优化实战:慢查询提速
功能上线时跑得好好的,数据量涨到几百万之后,某个查询突然从几十毫秒变成几秒,接口超时、页面转圈,最后发现只是一条 SQL 没有走索引。索引优化是性价比最高的数据库优化手段,不改业务逻辑,往往加一个索引就能让慢查询快一个数量级。但索引也不是万能药,加错了还会拖慢写入。本文结合一张千万级订单表,讲清楚索引怎么建、怎么查、怎么避坑。
一、索引到底是什么
索引的本质,是用额外存储空间换取查询速度。InnoDB 默认使用 B+Tree 索引:数据按索引键有序存放,没有索引只能全表扫描逐行比对,有了索引就能沿 B+Tree 快速定位,时间复杂度从 O(n) 降到 O(log n)。
InnoDB 的表本身就是按主键组织的聚簇索引,行数据存放在主键 B+Tree 的叶子节点上。普通索引的叶子节点存放的是主键值,查到后还要根据主键回聚簇索引取完整行数据,这个过程叫回表。理解回表,是理解覆盖索引的前提。
索引是有成本的:每建一个索引,写入时都要多维护一棵 B+Tree,插入、更新、删除都会变慢。所以优化目标不是建得越多越好,而是让高频查询命中合适的索引,同时控制索引总数。
二、先定位:找到真正的慢查询
优化之前先定位,不要凭感觉猜。MySQL 的慢查询日志是最直接的工具,先确认是否开启:
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
没有开启可以在运行时动态打开,long_query_time 单位是秒,设为 1 表示超过 1 秒的 SQL 会被记录:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
拿到慢 SQL 后,用 EXPLAIN 看执行计划:
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND status = 1
ORDER BY create_time DESC LIMIT 20;
重点看四列:type(访问类型,从好到差是 system、const、eq_ref、ref、range、index、ALL,出现 ALL 就是全表扫描)、key(实际使用的索引名,为 NULL 说明没用到索引)、rows(预估扫描行数)、Extra(出现 Using filesort 说明要额外排序,出现 Using index 表示走了覆盖索引,是好信号)。
记住这个流程:慢查询日志找 SQL,EXPLAIN 看执行计划,改完索引再验证,最后到线上观察真实耗时。
三、实战案例:一张千万级订单表
假设订单表 orders 约 1200 万行,结构简化如下:
CREATE TABLE orders (
id BIGINT NOT NULL AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
user_id BIGINT NOT NULL,
status TINYINT NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
create_time DATETIME NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
业务里最常见的查询,是查某个用户的近期订单:
SELECT id, order_no, total_amount, create_time
FROM orders
WHERE user_id = 1001 AND status = 1
ORDER BY create_time DESC
LIMIT 20;
表上只有主键和 order_no 的唯一索引,user_id 上没有任何索引,EXPLAIN 显示 type 为 ALL、rows 近 1200 万,实测约 2.8 秒。给 user_id 加一个普通索引:
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
加完后 type 变为 ref,rows 降到几百,耗时降到 15 毫秒左右:从全表扫描变成了按索引定位。
但该查询还有 ORDER BY create_time DESC,单列索引只解决 WHERE 过滤,排序仍需 filesort,订单多时排序代价不容忽视,这就该考虑复合索引。
四、复合索引与最左前缀原则
复合索引就是在一个索引里包含多个列。针对上面的查询,更合适的索引是:
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);
它能同时服务 WHERE 里的 user_id 和 status 条件,create_time 在索引内有序,可直接利用索引完成倒序,避免 filesort。建好后 Extra 不再出现 Using filesort,耗时进一步降到 5 毫秒以内。
复合索引要遵守最左前缀原则:查询条件必须从索引最左列开始连续匹配,跳过某一列,后面的列就用不上索引。以 (user_id, status, create_time) 为例:WHERE user_id = 1001 能用到第一列;加上 status = 1 能用到前两列;只查 status = 1 跳过了 user_id,整个索引都用不上;user_id 加 create_time 范围条件时跳过了 status,只能用到 user_id。
列的顺序一般是:等值查询的列放前面,范围查询的列放后面;区分度高的列优先。区分度可以用 COUNT(DISTINCT 列名) / COUNT(*) 估算,比值越接近 1 区分度越高。status 只有几种取值,单独建索引意义不大,但放在复合索引里配合 user_id 过滤是合理的。注意最左前缀针对的是索引列顺序,与 WHERE 里条件的书写顺序无关。
范围条件也会截断索引:一旦某列用了大于、小于、BETWEEN 这类范围查询,后面的列就无法继续用于索引过滤。所以既有 user_id 等值条件又有 create_time 范围条件时,索引设计成 (user_id, create_time) 往往更划算。
五、覆盖索引:少一次回表,快一大截
如果查询需要的所有列都在索引里,MySQL 就不需要回表,这就是覆盖索引。上面的查询只需要 id、order_no、total_amount、create_time,可建这样一个索引:
ALTER TABLE orders ADD INDEX idx_cover (user_id, status, create_time, order_no, total_amount);
id 是主键,二级索引叶子节点自带主键值,不用写进索引。建好后 Extra 会出现 Using index,说明查询完全在索引内完成。实测中覆盖索引通常比普通索引再快 30% 到 50%,数据量越大差距越明显。
但覆盖索引不是越多越好,这个索引含 5 列,体积和写入成本都不小。是否值得建要看调用频率:核心高频接口值得,一周跑几次的查询没必要,索引设计永远是读写之间的权衡。
还有一个误区:SELECT * 很容易破坏覆盖索引,一旦查询里多了一个不在索引中的列,MySQL 就必须回表。高频查询只查需要的列,不要图省事写 SELECT *。
六、索引失效的六个常见坑
很多时候索引明明建了,EXPLAIN 却显示没用上,问题往往出在写法上:
第一,对索引列做函数或运算。如 WHERE DATE(create_time) = ‘2026-11-06’,套了函数就失效,应改写成范围条件:
SELECT * FROM orders
WHERE create_time >= '2026-11-06 00:00:00'
AND create_time < '2026-11-07 00:00:00';
第二,隐式类型转换。user_id 是 BIGINT,写成 WHERE user_id = ‘1001’ 会触发类型转换,索引失效,传参类型必须跟字段类型一致。
第三,LIKE 以通配符开头。WHERE order_no LIKE ‘%1234’ 无法走索引,因为 B+Tree 按前缀有序,前缀不确定就没法定位,改成 LIKE ‘1234%’ 即可。
第四,OR 条件两侧的列没有索引。如 WHERE user_id = 1001 OR order_no = ‘X’,其中一列没有索引时优化器可能放弃索引直接全表扫描,能改成 UNION 就改:
SELECT * FROM orders WHERE user_id = 1001
UNION
SELECT * FROM orders WHERE order_no = 'X';
第五,负向查询。NOT IN、!=、<> 一般走不了索引,是否用它要结合数据量和 EXPLAIN 判断,有时全表扫描反而是优化器的正确选择。
第六,索引列参与表达式运算,或混用了函数与不等于的组合写法。判断方法很简单:写完 SQL 就跑一遍 EXPLAIN,看 key 列是不是预期的索引,不要凭感觉上线。
七、索引不是越多越好:维护与成本
一张表建了十几个索引,每次 INSERT 都要更新十几棵 B+Tree,写入性能会明显下降。定期清理无用索引是必要的维护:performance_schema 的 table_io_waits_summary_by_index_usage 表能看到每个索引的读取次数,COUNT_FETCH 长期为 0 的索引大概率没人用,可以考虑删除,但要先确认没有低频关键查询依赖它,比如月末报表,这类 SQL 不常跑,可一旦没了索引就会拖垮数据库。
大表加索引还要注意锁表问题。MySQL 5.6 之后支持在线加索引,但千万级大表仍可能耗时几分钟到几十分钟、消耗大量 IO。稳妥做法是:先在测试环境用同等量级数据验证耗时,选业务低峰期执行,并显式声明算法和锁模式:
ALTER TABLE orders ADD INDEX idx_user_id (user_id),
ALGORITHM=INPLACE, LOCK=NONE;
如果加索引失败或耗时超出预期,先停下来分析,不要反复重试。
八、上线前的检查清单
上线前对照这份清单过一遍:慢查询日志确认这条 SQL 真的慢;EXPLAIN 对比优化前后的 type、key、rows 和 Extra,确认走了预期索引;索引列顺序符合最左前缀,等值列在前、范围列在后;高频查询只查需要的列,优先覆盖索引,避免 SELECT *;参数类型与字段类型一致,LIKE 不以通配符开头;评估索引对写入的影响;大表加索引选低峰期,显式指定 ALGORITHM 和 LOCK,提前验证耗时;上线后看监控确认耗时真的下降。
结语
索引优化的核心思路只有三步:找到慢 SQL,看懂执行计划,建对索引。B+Tree、最左前缀、覆盖索引、回表这几个概念理解透了,绝大多数慢查询都能自己解决。剩下的就是习惯:写 SQL 前先想想有没有索引,写完跑一遍 EXPLAIN,上线后看一眼监控。挑一条你系统里的慢 SQL,按本文步骤走一遍。
- 点赞
- 收藏
- 关注作者
评论(0)