MySQL 索引优化实战:慢查询提速

举报
yd_262150314 发表于 2026/11/06 02:27:59 2026/11/06
【摘要】 本文以一张千万级订单表为例,从慢查询定位与 EXPLAIN 解读讲起,实战演示复合索引、最左前缀与覆盖索引的用法,并总结六种常见索引失效场景与上线检查清单,帮你把慢查询优化落到实处。

功能上线时跑得好好的,数据量涨到几百万之后,某个查询突然从几十毫秒变成几秒,接口超时、页面转圈,最后发现只是一条 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,按本文步骤走一遍。

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。