MySQL索引优化实战：五个技巧让查询性能提升百倍

1. 为什么索引优化能产生百倍差异先把结论摆在这儿索引没有用对和用对区别就是这么大。我接手过一套电商订单查询系统核心订单表数据量在 5000 万行左右原本根据用户手机号查最近三个月订单要花 2.1 秒优化完索引结构之后直接掉到 18 毫秒。这中间相差了不止 100 倍但既没加缓存也没改架构就只是动了几个索引。很多人一开始不信等看完 explain 的结果和实际的 trace 数据才意识到问题从来不在数据库本身而在索引设计上。MySQL 的索引底层是 B 树数据在叶子节点上有序排列这个结构决定了索引能大幅减少扫描行数。但索引不是建得越多越好也不是只要 WHERE 条件里的字段都建上索引就完事。真正的核心是理解 MySQL 的索引选择逻辑、复合索引的匹配规则、回表开销以及索引在排序、分组、覆盖查询这些场景里能额外扮演的角色。5000 万行的表没有索引时数据库只能老老实实地全表扫描每行都要读一遍并做一次条件判断而有了合适的索引走 B 树查找一条路径就能定位到目标范围IO 次数从几十万次降到几次这个差距自然就有几个数量级。这篇文章我准备把 5 个真正经过生产环境验证的技巧完整拆开每一个都配套讲清楚适用场景、底层原理以及实操中的验证方法。适合已经熟悉基础 SQL但面对慢查询日志和 explain 输出不知如何下手的开发者阅读也适合那种索引建了很多但查询还是慢的情况。我会用一张实际的表结构贯穿全文把从问题定位、索引设计到最终验证的整个流程走一遍你照着操作基本就能在自己的库里复现同样的效果。注意文中所有 SQL 均在 MySQL 8.0 环境下验证存储引擎为 InnoDB部署在标准云主机上。不同版本和配置下执行计划可能有差异但底层逻辑是一样的。2. 五个核心技巧逐个拆解这个部分我会按照从索引设计到查询语句优化的顺序来讲因为这五个技巧环环相扣。先掌握复合索引的匹配规则才能理解覆盖索引的价值理解了覆盖索引也就知道为什么函数操作会毁掉索引。它们之间的关联非常紧密。2.1 技巧一正确构造复合索引吃透最左前缀原则我在实际生产环境里遇到最多的问题不是该不该建索引而是索引字段怎么排列。假设有下面这张订单表经过抽象处理后的结构CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL COMMENT 订单编号, user_phone varchar(20) NOT NULL COMMENT 用户手机号, order_status tinyint NOT NULL COMMENT 订单状态, order_amount decimal(10,2) NOT NULL COMMENT 订单金额, channel varchar(10) DEFAULT NULL COMMENT 渠道来源, created_at datetime NOT NULL COMMENT 创建时间, updated_at datetime NOT NULL COMMENT 更新时间, PRIMARY KEY (id) ) ENGINEInnoDB;业务上有这样一个高频查询根据用户手机号查某时间范围内创建的订单并按时间倒序排列。对应的 SQL 是SELECT * FROM order_info WHERE user_phone 13800001111 AND created_at 2024-01-01 AND created_at 2024-04-01 ORDER BY created_at DESC;第一反应可能是给 user_phone 和 created_at 分别建两个单列索引。这么做不是不行但 MySQL 一个查询通常只能利用一个索引优化器会在两个索引里选一个区分度更高的走另一个字段就只能作为回表后的过滤条件。结果是 user_phone 定位到几千行再在这几千行里过滤时间并排序效率差强人意。正确做法是建一个复合索引而且字段顺序有讲究ALTER TABLE order_info ADD INDEX idx_user_created (user_phone, created_at);为什么要把 user_phone 放前面因为最左前缀原则。MySQL 的复合索引从最左边的列开始按顺序匹配查询条件只有匹配到左边字段之后才能利用索引里右侧字段的有序性。user_phone 在这里是等值匹配created_at 是范围匹配把等值判断的字段放最左边范围判断的字段放第二位这样索引既能快速缩小到某个用户的订单集合又能在创建时间字段上有序ORDER BY created_at 也能直接用到索引顺序而不用单独 filesort。如果反过来建(created_at, user_phone)created_at 范围匹配只能用到索引的第一层user_phone 的过滤效率就体现不出来了。这个顺序问题是我见过最容易被忽略、但影响最大的细节。用 explain 验证一下优化后的执行计划你会发现 key 字段指向了 idx_user_createdtype 是 rangerows 的估算值缩小到几百行以内Extra 里也没有 Using filesort 了。提示复合索引里最左前缀原则意味着索引(a, b, c)实际上能支持(a)、(a, b)、(a, b, c)三种组合的查询但(b)或(b, c)就完全用不上这个索引。设计时先列出业务里真实的查询模式再决定字段顺序不要贪多。2.2 技巧二用覆盖索引消除回表开销InnoDB 的普通索引二级索引叶子节点存的是索引列和主键值。当你执行SELECT *时通过二级索引找到主键值后还要再用主键去聚簇索引里查完整行数据这个动作叫回表。回表次数多了IO 开销就上来了尤其是每次都是随机 IO 的时候性能损耗非常明显。覆盖索引的意思是查询所需的全部列都在索引里不需要回表。MySQL 的 explain 中如果 Extra 列显示Using index说明这个查询就是靠覆盖索引完成的不用再回表拿数据。接着上面的例子如果业务上只需要查手机号、时间和订单状态那可以建一个更紧凑的覆盖索引ALTER TABLE order_info ADD INDEX idx_cover (user_phone, created_at, order_status);清空原索引后单独保留这个覆盖索引再执行SELECT user_phone, created_at, order_status FROM order_info WHERE user_phone 13800001111 AND created_at 2024-01-01 AND created_at 2024-04-01;explain 输出里 Extra 列会显示Using index说明没有回表操作。这里有一个容易踩的坑很多开发者图方便直接用SELECT *导致覆盖索引永远无法生效。你换了字段更少的覆盖索引数据库也识别不出来还是要老老实实回表取全行数据。处理思路是先把高频查询里真正需要的字段列出来针对这组字段建覆盖索引而不是指望一个索引包打天下。覆盖索引对分页查询的提升也很明显。一个大表的深度分页比如LIMIT 10000, 20如果用普通方式MySQL 要先扫 10020 行再把前 10000 行扔掉。利用覆盖索引先查询出主键再通过主键关联回原表获取完整数据可以少走很多无效 IOSELECT * FROM order_info o INNER JOIN ( SELECT id FROM order_info WHERE user_phone 13800001111 ORDER BY created_at DESC LIMIT 10000, 20 ) t ON o.id t.id;子查询里的 id 查询在覆盖索引上完成外层再按主键取数据实测下来效率比直接分页高出很多。注意覆盖索引的字段一旦多了索引本身占用的空间也变大写入时的维护成本增高。所以不要一股脑把所有字段都塞进去只覆盖那种高频且字段固定的小查询即可。2.3 技巧三范围查询之后不要跟其他过滤条件先说结论复合索引里范围查询后面的列无法继续用于索引定位只作为回表后的额外过滤条件。有一个实际案例特别典型。一张订单表上有索引(user_phone, created_at, order_status)一条 SQL 是SELECT * FROM order_info WHERE user_phone 13800001111 AND created_at 2024-01-01 AND order_status 1;你可能会觉得这索引三个字段都用上了但 explain 一看key_len 只用到 user_phone 和 created_at 的长度order_status 完全没参与索引定位。为什么要这样设计因为 B 树索引的叶子节点上数据是先按 user_phone 排序再按 created_at 排序最后按 order_status 排序。created_at 一旦变成范围查询order_status 字段在索引中的顺序就已经无法保证对定位有帮助了——你只能从 created_at 范围开始一个个扫描order_status 的判断只能等回表后拿到的行数据再做。这种情况下如果业务经常按(user_phone, created_at, order_status)这个组合查询就需要重新评估字段顺序——把 order_status 等值查询放到前面created_at 范围查询放到后面。比如调整成(user_phone, order_status, created_at)这样前两个是等值匹配定位到非常精确的小范围created_at 再用索引内的有序性做范围过滤。这个调整在我们自己的某日志分析系统里效果很明显同一时间段内调整索引顺序后扫描行数从 1200 万降到了 400 万。虽然是额外的数据库扫描但配合后续的覆盖索引优化整体查询时间减少了 60% 以上。2.4 技巧四避开表达式和隐式类型转换写了索引但不生效也是排查慢查询时的高频问题。常见的索引失效场景是条件里套了函数。MySQL 对索引列做函数处理后B 树的有序性就没法直接利用了。比如SELECT * FROM order_info WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-03-15;这相当于在 created_at 上套了函数。索引里的节点存储的是完整的 datetime 值无法直接按格式化后的字符串做二叉查找只能把索引列全部取出来转换后再比较——索引就废了。正确写法是直接给范围条件SELECT * FROM order_info WHERE created_at 2024-03-15 00:00:00 AND created_at 2024-03-16 00:00:00;这个写法让索引可以直接用区间定位不需要对每个值做函数计算。另一个容易被忽略的是隐式类型转换。比如 user_phone 是 varchar 类型查询时却写SELECT * FROM order_info WHERE user_phone 13800001111;MySQL 会把字符串列转成数字再比较这种情况下索引同样失效。正确的做法是查询参数类型统一用字符串。我排查过某订单系统的实际问题就是接口层把手机号参数解析成 int 后又传给了数据库导致整个查询变成全表扫描应用层标称 50ms 响应实际数据库查询耗时超过 2 秒。接口调用方查了半天都没意识到是类型不匹配造成的索引失效。平时写完 SQL最好习惯性 explain 一下看 type 字段如果是ALL说明走全表const、ref、range才是走了索引。一道简单防线就能拦住一大批低级问题。2.5 技巧五区分度低的选择性索引该怎么处理有些开发者在状态字段上建索引比如 order_status 只有 0、1、2、3 四个值然后发现查询并没有变快数据量大时甚至比全表还慢。原因是这字段区分度太低5000 万行的表每个状态值对应一千多万行索引定位命中范围太大最终大量扫描和回表效果自然不如全表扫描优化器可能直接就弃用了这个索引。真正有效的是建复合索引或前缀索引。如果业务里要根据用户手机号和状态同时查询把状态往前挪到复合索引里等值匹配顺着索引走到状态那一步时范围就缩到某个用户下的几条或几十条记录这时区分度低不低已经无所谓了。再补充一个前缀索引的思路。比如某表有 url 字段字段长度很长直接建全文索引资源消耗大区分度也不算高。这时可以统计字段不同值的前缀区分度取一个安全的前缀长度SELECT COUNT(DISTINCT LEFT(url, 10)) AS c10, COUNT(DISTINCT LEFT(url, 15)) AS c15, COUNT(DISTINCT LEFT(url, 20)) AS c20;如果 c15 和原始值的 distinct 数量非常接近就建(url(15))前缀索引。我在某内容管理系统的链接表上实测url 字段原始长度 300 字节前缀 20 字节时区分度已经达到 99% 以上索引大小瞬间缩水 90% 多查询性能反而更好了。3. 实操验证从定位慢查询到最终优化的完整流程上面五个技巧分开看都能理解但实际优化一个查询时还需要一条完整的执行主线。我以某模拟项目 X 里的一个真实慢查询为例把从发现问题到最终验证的流程完整演示一遍。这次优化围绕一张虚拟的订单流水表展开虽然字段经过了简化但结构贴近常见业务表方法论可以直接迁移。3.1 用慢查询日志和 explain 定位问题生产库开启了慢查询日志假设日志输出配置为超过 1 秒的记录。某天日志里发现这条 SQL 频繁出现SELECT * FROM order_info WHERE user_phone 13911112222 ORDER BY created_at DESC LIMIT 20;订单表数据量 3000 万行单个手机号的订单量通常在 500 到 1500 条之间。为了复现问题先手动跑了一次这条 SQL耗时约 1.6 秒。接着看 explain 输出id: 1 select_type: SIMPLE table: order_info type: ALL possible_keys: NULL key: NULL rows: 30000000 filtered: 0.01 Extra: Using where; Using filesort一眼能看到三个问题type 是 ALL说明完全没有用索引全表扫描rows 估算值是 3000 万这意味着每一行都被扫到了Extra 里显示 Using filesort排序也没有走索引为什么 user_phone 上的单列索引没用上回查了一下表结构发现 user_phone 的索引虽然存在但字段类型是 varchar而接口层传入的参数是 int发生了隐式类型转换索引直接失效。这就是前面说的技巧四的真实复现。3.2 针对场景做索引结构调整确认根因后做了两件事第一把查询参数统一成字符串类型解决了隐式类型转换问题。第二审视查询场景发现这个接口经常被点击查询条件固定为某用户最近创建的订单于是把原来的单列索引升级为复合索引ALTER TABLE order_info DROP INDEX idx_phone; ALTER TABLE order_info ADD INDEX idx_phone_time (user_phone, created_at);这里排序字段 created_at 放第二位可以在索引内部直接完成 ORDER BY created_at 的排序不需要额外的 filesort。再跑一次 explainid: 1 select_type: SIMPLE table: order_info type: ref possible_keys: idx_phone_time key: idx_phone_time ref: const rows: 800 Extra: Using index conditionrows 从 3000 万降到 800Extra 里也不再出现 Using filesort 了。实际执行耗时从 1.6 秒降到 12 毫秒。这就是一个典型的看起来只改了一点点效果差了两个数量级的案例。3.3 用 optimizer trace 验证索引选择逻辑explain 能看出最终执行计划但有时候你还想知道 MySQL 为什么这么选。这时可以用 optimizer traceSET optimizer_trace enabledon; SELECT * FROM order_info WHERE user_phone 13911112222 ORDER BY created_at DESC LIMIT 20; SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;在 trace 输出中重点看rows_estimation部分它会列出优化器对每个候选索引的扫描行数和成本估算。我们当时发现优化器在不同候选索引之间的成本对比非常接近如果哪天数据分布变了执行计划很可能跳变。为了锁定行为才决定把多余的单列索引彻底删掉只留复合索引。排查慢查询时别只盯着一个执行计划数据分布一变优化器可能就改变选择。有条件的话在生产压力的低峰期做一次 explain 对比多跑几次看结果是否稳定。3.4 压测对比优化前后性能量化我用一套压力测试工具模拟了 200 个并发请求连续打 5 分钟场景就是上面那条 SQL。优化前接口的 TP95 延迟为 3.2 秒数据库连接池经常被打满报错率约 4%。优化后同样压力下TP95 延迟降到 45 毫秒报错率为 0。数据库侧的 CPU 使用率也明显下降说明扫描行数减少带来的收益是全方位的。在本地环境做这个小规模对比也很有意义不是为了得到绝对数字而是相对对比时能清楚看出索引结构的影响。4. 常见问题与排查技巧实录实际操作中总会遇到一些翻了文档也不一定能直接找到答案的情况。这里整理几个高频问题并附上排查思路。4.1 索引建了但没走可能是什么原因这种情况我遇到的理由主要有四种。第一种是字段类型隐式转换上面已经提过接口层传参类型和表字段类型不匹配最常见。第二种是 LIKE 前缀通配比如LIKE %abc这种写法无法用索引做前缀匹配。第三种是 OR 条件关联了非索引字段比如WHERE user_phone ... OR order_status 1即使 user_phone 有索引order_status 没有索引时优化器也可能选全表扫描。第四种是数据分布极端的时候优化器认为全表扫描成本反而更低特别是在区分度低的情况下它会直接跳过索引。排查时可以同时看 explain 和 optimizer trace重点比较全表扫描和索引扫描的成本估算值。有时候索引走了但很慢也可能是因为选择性低导致回表过多这会体现在Using index condition和大量回表 IO 上。注意不要手贱把FORCE INDEX当作常规手段。数据分布变化后强制走索引有可能会让性能更差只能作为临时手段在确认根因前用一下。4.2 联合索引字段顺序到底怎么定核心口诀等值在前范围在后排序字段按需放。先列出所有真实的高频查询模式统计每个字段在 WHERE 里的出现频率和是否等值匹配再决定顺序。举例来说查询条件user_phone等值 order_status等值 created_at范围建议索引(user_phone, order_status, created_at)如果还有 ORDER BY created_at那么这个排列同样可以利用索引直接完成排序字段顺序一旦定下来尽量早点在测试环境验证因为数据量不够大的时候 explain 的预估不一定准确你压测出来的结论也可能有误导性。4.3 分页查询越来越慢怎么办深度分页的慢不是因为 LIMIT 本身可怕而是要扫描和排序的行数太多。覆盖索引优化配合延迟关联是最常用的方案。先用覆盖索引查出目标主键列表再回原表取数据而不是直接从大表里 LIMIT 大偏移量。后面如果数据量继续涨也可以考虑改造成基于游标的翻页去掉 LIMIT 大偏移量。我实践中还发现排序字段没有进索引会造成 filesort这是分页查询变慢的另一个隐形原因。设计索引时要把排序需求考虑进去不要只盯着 WHERE 条件。5. 后续扩展方向索引优化做完之后并不代表一劳永逸。生产环境的表持续写入数据分布会不断变化索引的使用情况也要定期复盘。最直接的手段是启用 performance_schema 里的统计信息定期查看未使用过的索引。那些建了但长期没有命中记录的索引可以视为无用索引占用写入空间和维护成本该删就删。以下是常用的查询语句SELECT OBJECT_NAME AS table_name, INDEX_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE, SUM_TIMER_WAIT / 1000000000 AS total_wait_ms FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db AND INDEX_NAME IS NOT NULL AND INDEX_NAME ! PRIMARY ORDER BY COUNT_READ ASC;如果某个索引的 COUNT_READ 长期为 0就需要考虑是否还在被其他查询使用。确认无用后在业务低峰期做删除。另外如果表的数据量级继续增长复合索引的字段可能也需要调整。比如从 5000 万行涨到 5 亿行原来区分度够用的字段可能就变成低区分度了需要重新审视。通用的做法是建立一个周期性的慢查询巡检任务实时抓取 new 的慢 SQL 并自动对比 explain。我在某数据平台实践过一套简单方案每天凌晨把前一天慢查询日志导入分析表批量跑一轮 explain把执行计划变更的 SQL 挑出来推送给开发确认。这样等用户反馈慢之前就已经把问题拦截住了。最后提醒一点索引不是银弹不要以为把所有字段都加上覆盖索引就万事大吉。写入性能、磁盘空间、查询性能之间需要平衡。实际业务里 80% 的查询性能问题往往只是两三个核心高频查询导致的把这几个查询对应的索引认真设计好收益远大于追求全字段覆盖。我自己优化过的项目里最快见效的就是先把慢查询日志按出现频率排序集中火力解决前几个高频 SQL比盲目优化边缘查询有效得多。