上个月帮一个电商团队处理线上订单查询变慢的问题接口高峰期要3秒多才返回查了一圈发现数据库负载并不高真正的原因是慢查询SQL在全表扫描。后来加了一个联合索引查询从2.8秒降到40毫秒。这件小事让我又一次意识到mysql索引调优从来不是玄学而是一套可验证、能复现的排查与设计流程。这篇内容适合谁不管是刚入行的后端开发还是被线上慢查询折磨的运维研发又或者是准备面试需要系统梳理索引知识的人都能从中拿到可以直接落地的操作方法和经验教训。我会从索引的底层工作方式讲起再结合慢查询日志、explain分析、联合索引设计、索引失效场景、生产案例和日常维护这条完整链路展开尽量把为什么讲透而不只是给一堆命令。1. 索引不是越多越好先搞清楚它到底怎么工作1.1 B树与聚簇索引索引加速查询的底层逻辑InnoDB的索引结构是B树。为什么选B树而不是B树、也不是哈希表原因是磁盘IO的物理特性一次IO读取一个页默认16KBB树的非叶子节点只存键值和指针不存数据所以一个页能容纳的键值数量很多。树的高度通常只有2到3层这意味着从根节点走到叶子节点只需要2到3次磁盘IO。做一个简单估算假设主键是bigint8字节加上6字节的行指针非叶子节点每个键值对占14字节。一个16KB的页大约能存16 * 1024 / 14 ≈ 1170个键值对。三层B树的容量大约是 1170 * 1170 * 每个叶子页能存的行数。如果一行数据约1KB一个叶子页能存约16行那么三层树可以支撑约 1170 * 1170 * 16 ≈ 2180万行。也就是说两千万行以内的表按主键查询最多3次磁盘IO就能定位到数据。这就是索引最核心的价值把全表扫描的线性复杂度降成接近常数次的磁盘访问。这里还要区分聚簇索引和二级索引。InnoDB中主键索引就是聚簇索引叶子节点保存整行数据二级索引的叶子节点保存主键值。所以通过二级索引查询时通常需要先找到主键值再回到聚簇索引查一次整行这个动作叫回表。理解这一点后面讲覆盖索引才有基础。另外因为聚簇索引按主键有序排列主键的选择直接关系数据插入性能。随机主键比如UUID会导致页分裂频繁写入性能明显下降这也是我一直建议线上表用自增主键的原因。1.2 索引的代价写放大、空间开销与优化器的抉择索引不是免费的。每建一个二级索引意味着每次insert、update、delete除维护聚簇索引外还要同步维护该二级索引的B树。写入频繁的表索引过多会让磁盘IO和日志量明显增大。一个订单表如果有6个索引每次插入就要同时写7棵B树。这就是为什么很多业务在写入高峰期加了索引后写入反而变慢。空间开销同样容易被忽视。一个int类型列上的索引加上主键值每行记录在索引里要占十多个字节。百万行级别看起来不大但几个大表几十个索引累积下来磁盘占用和buffer pool命中率都会受影响。还有一个容易被忽略的点优化器在选择执行计划时会参考索引的基数cardinality和选择性。如果索引列的值大量重复优化器可能认为走索引还不如全表扫描划算从而放弃使用索引。这也是我明明建了索引explain还是显示ALL的常见原因之一。所以索引调优的核心是在读性能和写成本之间找到平衡点而不是堆砌索引数量。2. 调优起点不是加索引而是找到真正慢的SQL2.1 慢查询日志的配置与线上实践很多人一听到索引调优第一反应是给哪个字段加索引。但正确的顺序应该是先找到慢SQL再分析为什么会慢最后才决定加什么索引。MySQL提供了现成的工具慢查询日志。配置方式比较简单在配置文件或运行时设置slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time单位是秒生产环境建议从1秒开始设如果日志量太大再适当调大。log_queries_not_using_indexes这个选项能把没走索引的查询也记录下来即使执行时间不长对发现漏加索引的SQL很有帮助。线上比较推荐的方式是用 mysqldumpslow 做统计例如mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log-s at表示按平均查询时间排序-t 10表示只显示前10条。这一步能快速把真正值得调优的SQL从一堆请求里捞出来。一个容易踩的误区开启log_queries_not_using_indexes后日志量会暴涨很多全表扫描的查询都会被记录。建议配合定时任务定期归档或者用 pt-query-digest 做汇总分析不要让日志无限增长拖垮磁盘。2.2 explain输出一眼就能看出问题的几个字段拿到慢SQL后下一步是解释执行计划。在SQL前面加explain关键字即可重点是看以下几个字段字段关注点type访问类型从好到差依次是 system const eq_ref ref range index ALLkey实际使用的索引是 null 就说明没走索引rows预估扫描行数数值越大越可疑Extra是否出现 Using filesort、Using temporary、Using indextype字段是最直观的。ALL基本意味着全表扫描这种SQL大概率需要优化index表示扫描了整个索引树虽然比ALL好一点但如果覆盖的列很多一样可能慢range表示范围扫描通常在where中使用了范围条件时出现ref和const是我们期望的目标说明优化器使用索引做了等值查找。rows字段预估的是扫描行数虽然不一定精确但可以很好地反映索引是否真正生效。比如一个80万行的表查询条件带user_id等值rows却显示40万那基本可以断定索引没起作用。Extra里有一个非常容易被忽略的值Using filesort。它表示查询结果需要额外排序。MySQL在无法用索引直接给出有序结果时会启动一个排序过程这通常发生在order by字段不在索引里或者排序方向与索引顺序不一致时。filesort不一定都慢但当排序数据量很大时会产生临时文件和额外IO。很多查询从2秒降到几十毫秒往往不只是因为避免了全表扫描还因为order by字段被并入了联合索引一并解决了filesort。3. 联合索引与覆盖索引的设计最左前缀与列顺序3.1 最左前缀法则联合索引不是简单的多列索引联合索引 (a, b, c) 实际上会按 a、b、c 的顺序建立一棵B树排序时先按aa相同再按bb相同再按c。所以查询条件中能不能用到索引取决于是否满足最左前缀法则必须从第一列开始连续匹配且中间不能断。where a ?可以用到索引where a ? and b ?可以用到where a ? and b ? and c ?可以用到where b ?用不到跳过了第一列where a ? and c ?只能用到a这一列c无法利用索引继续精确定位为什么a ? and c ?只能用a因为b的排序条件缺失c无法在b缺失的情况下继续做精确定位a等值过滤后剩下的记录只能在b、c上过滤但无法利用B树的有序性直接跳到目标c值。设计联合索引时一个实用原则是等值查询的列放在前面范围查询的列放在后面。因为范围查询一旦发生该列右边的列就难以利用索引的有序性做精确定位。比如where status 1 and create_time 2024-01-01 order by user_id正确的索引顺序大概率是 (status, create_time, user_id)而不是 (create_time, status, user_id)。区分度高的列是否一定放前面也不绝对。如果区分度高的列是被等值条件匹配的放前面完全没问题如果它本身是范围条件则应优先让等值列排前面。3.2 覆盖索引让查询免回表的性能红利二级索引的叶子节点存的是主键值查询时如果需要索引中没有的列就得回表。回表意味着每行数据都要按主键再做一次B树查询如果命中几千行就要几千次随机IO。覆盖索引的思路是让查询需要的所有列都包含在索引中这样直接从索引叶子节点就能拿到结果连回表都省了。举一个常见例子订单列表页需要按user_id查询订单号与状态SELECT order_no, status FROM orders WHERE user_id ?如果只建(user_id)索引查询时需要先找user_id对应的主键值再逐个回表取order_no和status。如果建(user_id, order_no, status)联合索引查询所需的字段全在索引列里explain中Extra会显示Using index表示覆盖索引扫描。数据量大时这个差异往往能把查询时间降低一个量级。但这里有个权衡覆盖索引的列越多索引体积越大写入开销越高。不要为了追求覆盖索引把所有字段都塞进去。经验做法是先分析select列表中最常出现、且本身已经有索引基础的少量列把高频查询需要的列加进去如果发现加列后索引体积明显膨胀就要计算一下收益是否值得。尤其是limit限制下的查询回表行数恒等于最终返回行数这时候覆盖索引的收益会变小更需要仔细评估。4. 索引失效的六种典型场景与排查方法4.1 隐式类型转换与函数操作最常见的两个杀手第一种是隐式类型转换。字段是varcharwhere条件直接写数字where phone 12345678901MySQL会把字段的字符串转成数字再比较结果就是索引列被函数包裹优化器无法使用索引。解决办法是where phone 12345678901字符串类型用引号包裹。反过来如果字段是bigint条件写成字符串也可能引发类型转换同样需要留意。判断方法很简单explain看key字段是否为null以及rows是否过大。或者执行show warningsMySQL会显示经过转换后的语句能看到隐式转换的痕迹。第二种是函数操作。最典型的是对日期字段做函数SELECT * FROM orders WHERE DATE(create_time) 2024-01-01左侧的DATE()函数让create_time列失去索引的有序性。正确写法是范围条件SELECT * FROM orders WHERE create_time 2024-01-01 AND create_time 2024-01-02这样优化器能直接用create_time索引做range扫描。类似的情况还有对字段做加减运算、字符串拼接、JSON函数操作等。凡是在索引列上套了函数基本都很难用上索引。4.2 范围查询、or连接与模糊匹配边界问题的处理范围查询会破坏联合索引中该列右侧的列。比如索引 (a, b)where a 10 and b 5b是否能用索引取决于a的范围跨度。如果a 10过滤后的数据量仍然很大优化器可能直接选择不继续利用b即使继续扫描b也无法用B树的精确匹配只能做索引内过滤。这里没有绝对答案建议用explain看实际效果必要时交换列顺序或调整索引结构。or连接的情况比较直接where a 1 or b 2如果a和b不是同一个索引优化器需要分别找两棵索引树再合并结果往往选择全表扫描更划算。解决办法是把or改成union all或者给两侧单独的列分别建索引。MySQL 8.0的index merge优化虽然能处理部分场景但依赖统计信息和成本估算不能完全依赖。模糊匹配的规则是前导通配符会失效like %abc无法使用索引因为B树按字典序排列无法从abc前面的任意字符开始定位而like abc%可以利用索引做range扫描。如果业务确实需要前导通配符的搜索一般建议引入专门的搜索引擎而不是在MySQL层面强行优化。另外一个容易被忽略的场景是NULL值判断。is null/is not null在索引列上是否能走索引与MySQL版本、列的nullable属性以及统计信息有关。稳妥做法是给常用列设置not null default值不光是索引考虑也能减少业务方的迷惑。5. 一个生产案例订单查询从2.8秒到40毫秒5.1 现象与初步定位查询阻塞并非数据库负载问题这个案例来自一个电商后台系统订单表orders约80万行线上高峰期查询当前用户的待处理订单列表这个接口经常超过2秒。最开始大家怀疑是数据库负载高、连接数打满但查看监控发现CPU、内存、磁盘IO都在正常范围数据库连接数也远未到上限。这就比较典型负载低但查询慢问题多半出在执行计划上。把线上执行的SQL捞出来简化后大约是这样SELECT order_no, status, amount, create_time FROM orders WHERE user_id 10086 AND status 1 ORDER BY create_time DESC LIMIT 20;对应的表结构上原有索引是单个的idx_user_id(user_id)。explain结果如下typerefkeyidx_user_idrows大约8000Extra里出现了Using filesort。看到这里基本可以确定瓶颈不在全表扫描而在两个地方一是user_id等值过滤后仍然有8000行左右的估量需要逐行回表取order_no、status、amount、create_time二是排序字段create_time不在索引里必须做filesort。可能有人会问既然limit 20排序后才取20行为什么还是慢因为filesort需要把所有命中的行集约8000行全部读出来排序即使最终只取20行。而且在高并发下这个排序任务会反复执行累积起来的开销就很可观。5.2 索引方案设计与实施从ref到Using index分析到这里方案就明确了。把现有单列索引 idx_user_id 替换成联合索引ALTER TABLE orders DROP INDEX idx_user_id, ADD INDEX idx_user_status_time (user_id, status, create_time);为什么是(user_id, status, create_time)而不是其他顺序首先user_id是等值条件且区分度足够高放在最左边负责把扫描范围直接从80万行缩小到几千行其次status是等值条件可以继续压缩虽然status只有几个取值但加上它对缩小每个user_id下需要排序的数据量有帮助最后create_time放在最右边作用是让索引直接按create_time有序返回省掉filesort。同时select中的order_no、status、amount、create_time中status和create_time已在索引内order_no和amount需要回表获取。那要不要把order_no和amount也放进索引做成覆盖索引我的建议是不要。因为limit 20固定了最终返回行数哪怕回表也就回20行成本很低但amount如果放进索引会导致索引体积明显膨胀写入开销增加性价比不高。实际开发中应根据select列表的出现频率和表写入压力做取舍。上线方式我用了 pt-online-schema-change 工具做在线变更避免在业务高峰期执行ALTER TABLE锁表。大表加索引不只是create index本身的速度还需要重建整张表的数据页。80万行不算特别大但生产环境还是稳妥为上。5.3 调优结果与验证上线前这样确认收益变更完成后再看explaintyperefkeyidx_user_status_timerows从8000降到几十行Extra里的Using filesort消失变成了Using index condition因为status和create_time都用于索引判断。实测同一SQL耗时从2.8秒降到40毫秒左右接口TP99也恢复到了正常水平。把验证过程总结成一个清单供参考变更前先记录慢SQL的执行时间、explain关键字段变更后对比同样的explain输出确认type、key、rows、Extra四项都有改善在测试环境用线上导出的数据压测确认写入性能没有明显恶化设置回滚预案保留旧索引定义必要时快速重建。这一步很关键很多朋友加完索引后发现没效果往往是没对比explain只看执行时间忽略了其他因素干扰。6. 索引维护的日常功课冗余清理与碎片整理6.1 冗余索引检查多花十分钟省下大量写入开销线上系统运行一段时间后索引往往会越加越多其中有大量冗余。比如已经有了(user_id, status)联合索引再单独建(user_id)单列索引后者就是冗余的因为联合索引的最左前缀已经覆盖了user_id的查询。冗余索引不只浪费空间还会让每次写入的维护成本增加需要定期清理。排查冗余索引最简单的办法是用information_schemaSELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols FROM information_schema.statistics WHERE table_schema your_db GROUP BY table_name, index_name;把每个索引的列组合拉出来后人工比对一下是否有前缀相同且列数更多的索引。也可以使用Percona Toolkit里的pt-duplicate-key-checker它会自动扫描并给出冗余索引建议输出很直观适合没有专门工具链的小团队使用。清理时注意先在测试库跑一遍业务回归删除后观察一周慢查询数量确认没有SQL依赖被删索引再做后续动作。6.2 碎片整理与统计信息更新索引也需要体检InnoDB的索引在频繁的增删改后会产生页分裂和页碎片原本紧密的B树会变得稀疏导致索引占用空间变大扫描效率下降。常见的处理手段有两类一是重建整表ALTER TABLE orders ENGINEInnoDB;这会重建表压缩碎片但期间需要锁表MySQL 5.6 会做在线DDL但依然建议低峰期操作。二是用OPTIMIZE TABLE orders效果类似。8.0版本里还有一个更轻量的方式重建单个索引但同样有成本。碎片之外统计信息的准确度直接影响优化器对索引的选择。频繁更新但统计信息没有及时收集时优化器可能因为rows估算失准而放弃索引。可以通过ANALYZE TABLE orders手动收集统计信息。很多线上案例表现为索引明明存在且查询条件合理explain却不用跑完ANALYZE之后执行计划就恢复正常了。日常维护的时间点也有讲究。我一般习惯在每周对核心大表做一次ANALYZE每月或每次大促前做一次索引冗余检查和碎片整理。修改表结构永远比直接改SQL风险高先低峰测试再上线执行。索引调优不是一次性的项目而是一条需要持续观察和迭代的链路。最后分享一个我个人坚持了很久的习惯每次给生产环境加索引前我都会写一条调优前后对比记录内容包括执行时间、explain关键字段、rows估算、是否出现filesort。长期积累下来哪些场景加什么索引有效、哪些场景容易翻车基本一眼就能判断。很多朋友问我调优有没有捷径我的回答永远是先学会看explain再动手加索引每次变更前都想清楚为什么。技巧可以速成但这种记录习惯很难速成恰恰是它让调优从碰运气变成可预期。 MySQL索引调优实战：从慢查询分析到联合索引设计
上个月帮一个电商团队处理线上订单查询变慢的问题接口高峰期要3秒多才返回查了一圈发现数据库负载并不高真正的原因是慢查询SQL在全表扫描。后来加了一个联合索引查询从2.8秒降到40毫秒。这件小事让我又一次意识到mysql索引调优从来不是玄学而是一套可验证、能复现的排查与设计流程。这篇内容适合谁不管是刚入行的后端开发还是被线上慢查询折磨的运维研发又或者是准备面试需要系统梳理索引知识的人都能从中拿到可以直接落地的操作方法和经验教训。我会从索引的底层工作方式讲起再结合慢查询日志、explain分析、联合索引设计、索引失效场景、生产案例和日常维护这条完整链路展开尽量把为什么讲透而不只是给一堆命令。1. 索引不是越多越好先搞清楚它到底怎么工作1.1 B树与聚簇索引索引加速查询的底层逻辑InnoDB的索引结构是B树。为什么选B树而不是B树、也不是哈希表原因是磁盘IO的物理特性一次IO读取一个页默认16KBB树的非叶子节点只存键值和指针不存数据所以一个页能容纳的键值数量很多。树的高度通常只有2到3层这意味着从根节点走到叶子节点只需要2到3次磁盘IO。做一个简单估算假设主键是bigint8字节加上6字节的行指针非叶子节点每个键值对占14字节。一个16KB的页大约能存16 * 1024 / 14 ≈ 1170个键值对。三层B树的容量大约是 1170 * 1170 * 每个叶子页能存的行数。如果一行数据约1KB一个叶子页能存约16行那么三层树可以支撑约 1170 * 1170 * 16 ≈ 2180万行。也就是说两千万行以内的表按主键查询最多3次磁盘IO就能定位到数据。这就是索引最核心的价值把全表扫描的线性复杂度降成接近常数次的磁盘访问。这里还要区分聚簇索引和二级索引。InnoDB中主键索引就是聚簇索引叶子节点保存整行数据二级索引的叶子节点保存主键值。所以通过二级索引查询时通常需要先找到主键值再回到聚簇索引查一次整行这个动作叫回表。理解这一点后面讲覆盖索引才有基础。另外因为聚簇索引按主键有序排列主键的选择直接关系数据插入性能。随机主键比如UUID会导致页分裂频繁写入性能明显下降这也是我一直建议线上表用自增主键的原因。1.2 索引的代价写放大、空间开销与优化器的抉择索引不是免费的。每建一个二级索引意味着每次insert、update、delete除维护聚簇索引外还要同步维护该二级索引的B树。写入频繁的表索引过多会让磁盘IO和日志量明显增大。一个订单表如果有6个索引每次插入就要同时写7棵B树。这就是为什么很多业务在写入高峰期加了索引后写入反而变慢。空间开销同样容易被忽视。一个int类型列上的索引加上主键值每行记录在索引里要占十多个字节。百万行级别看起来不大但几个大表几十个索引累积下来磁盘占用和buffer pool命中率都会受影响。还有一个容易被忽略的点优化器在选择执行计划时会参考索引的基数cardinality和选择性。如果索引列的值大量重复优化器可能认为走索引还不如全表扫描划算从而放弃使用索引。这也是我明明建了索引explain还是显示ALL的常见原因之一。所以索引调优的核心是在读性能和写成本之间找到平衡点而不是堆砌索引数量。2. 调优起点不是加索引而是找到真正慢的SQL2.1 慢查询日志的配置与线上实践很多人一听到索引调优第一反应是给哪个字段加索引。但正确的顺序应该是先找到慢SQL再分析为什么会慢最后才决定加什么索引。MySQL提供了现成的工具慢查询日志。配置方式比较简单在配置文件或运行时设置slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time单位是秒生产环境建议从1秒开始设如果日志量太大再适当调大。log_queries_not_using_indexes这个选项能把没走索引的查询也记录下来即使执行时间不长对发现漏加索引的SQL很有帮助。线上比较推荐的方式是用 mysqldumpslow 做统计例如mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log-s at表示按平均查询时间排序-t 10表示只显示前10条。这一步能快速把真正值得调优的SQL从一堆请求里捞出来。一个容易踩的误区开启log_queries_not_using_indexes后日志量会暴涨很多全表扫描的查询都会被记录。建议配合定时任务定期归档或者用 pt-query-digest 做汇总分析不要让日志无限增长拖垮磁盘。2.2 explain输出一眼就能看出问题的几个字段拿到慢SQL后下一步是解释执行计划。在SQL前面加explain关键字即可重点是看以下几个字段字段关注点type访问类型从好到差依次是 system const eq_ref ref range index ALLkey实际使用的索引是 null 就说明没走索引rows预估扫描行数数值越大越可疑Extra是否出现 Using filesort、Using temporary、Using indextype字段是最直观的。ALL基本意味着全表扫描这种SQL大概率需要优化index表示扫描了整个索引树虽然比ALL好一点但如果覆盖的列很多一样可能慢range表示范围扫描通常在where中使用了范围条件时出现ref和const是我们期望的目标说明优化器使用索引做了等值查找。rows字段预估的是扫描行数虽然不一定精确但可以很好地反映索引是否真正生效。比如一个80万行的表查询条件带user_id等值rows却显示40万那基本可以断定索引没起作用。Extra里有一个非常容易被忽略的值Using filesort。它表示查询结果需要额外排序。MySQL在无法用索引直接给出有序结果时会启动一个排序过程这通常发生在order by字段不在索引里或者排序方向与索引顺序不一致时。filesort不一定都慢但当排序数据量很大时会产生临时文件和额外IO。很多查询从2秒降到几十毫秒往往不只是因为避免了全表扫描还因为order by字段被并入了联合索引一并解决了filesort。3. 联合索引与覆盖索引的设计最左前缀与列顺序3.1 最左前缀法则联合索引不是简单的多列索引联合索引 (a, b, c) 实际上会按 a、b、c 的顺序建立一棵B树排序时先按aa相同再按bb相同再按c。所以查询条件中能不能用到索引取决于是否满足最左前缀法则必须从第一列开始连续匹配且中间不能断。where a ?可以用到索引where a ? and b ?可以用到where a ? and b ? and c ?可以用到where b ?用不到跳过了第一列where a ? and c ?只能用到a这一列c无法利用索引继续精确定位为什么a ? and c ?只能用a因为b的排序条件缺失c无法在b缺失的情况下继续做精确定位a等值过滤后剩下的记录只能在b、c上过滤但无法利用B树的有序性直接跳到目标c值。设计联合索引时一个实用原则是等值查询的列放在前面范围查询的列放在后面。因为范围查询一旦发生该列右边的列就难以利用索引的有序性做精确定位。比如where status 1 and create_time 2024-01-01 order by user_id正确的索引顺序大概率是 (status, create_time, user_id)而不是 (create_time, status, user_id)。区分度高的列是否一定放前面也不绝对。如果区分度高的列是被等值条件匹配的放前面完全没问题如果它本身是范围条件则应优先让等值列排前面。3.2 覆盖索引让查询免回表的性能红利二级索引的叶子节点存的是主键值查询时如果需要索引中没有的列就得回表。回表意味着每行数据都要按主键再做一次B树查询如果命中几千行就要几千次随机IO。覆盖索引的思路是让查询需要的所有列都包含在索引中这样直接从索引叶子节点就能拿到结果连回表都省了。举一个常见例子订单列表页需要按user_id查询订单号与状态SELECT order_no, status FROM orders WHERE user_id ?如果只建(user_id)索引查询时需要先找user_id对应的主键值再逐个回表取order_no和status。如果建(user_id, order_no, status)联合索引查询所需的字段全在索引列里explain中Extra会显示Using index表示覆盖索引扫描。数据量大时这个差异往往能把查询时间降低一个量级。但这里有个权衡覆盖索引的列越多索引体积越大写入开销越高。不要为了追求覆盖索引把所有字段都塞进去。经验做法是先分析select列表中最常出现、且本身已经有索引基础的少量列把高频查询需要的列加进去如果发现加列后索引体积明显膨胀就要计算一下收益是否值得。尤其是limit限制下的查询回表行数恒等于最终返回行数这时候覆盖索引的收益会变小更需要仔细评估。4. 索引失效的六种典型场景与排查方法4.1 隐式类型转换与函数操作最常见的两个杀手第一种是隐式类型转换。字段是varcharwhere条件直接写数字where phone 12345678901MySQL会把字段的字符串转成数字再比较结果就是索引列被函数包裹优化器无法使用索引。解决办法是where phone 12345678901字符串类型用引号包裹。反过来如果字段是bigint条件写成字符串也可能引发类型转换同样需要留意。判断方法很简单explain看key字段是否为null以及rows是否过大。或者执行show warningsMySQL会显示经过转换后的语句能看到隐式转换的痕迹。第二种是函数操作。最典型的是对日期字段做函数SELECT * FROM orders WHERE DATE(create_time) 2024-01-01左侧的DATE()函数让create_time列失去索引的有序性。正确写法是范围条件SELECT * FROM orders WHERE create_time 2024-01-01 AND create_time 2024-01-02这样优化器能直接用create_time索引做range扫描。类似的情况还有对字段做加减运算、字符串拼接、JSON函数操作等。凡是在索引列上套了函数基本都很难用上索引。4.2 范围查询、or连接与模糊匹配边界问题的处理范围查询会破坏联合索引中该列右侧的列。比如索引 (a, b)where a 10 and b 5b是否能用索引取决于a的范围跨度。如果a 10过滤后的数据量仍然很大优化器可能直接选择不继续利用b即使继续扫描b也无法用B树的精确匹配只能做索引内过滤。这里没有绝对答案建议用explain看实际效果必要时交换列顺序或调整索引结构。or连接的情况比较直接where a 1 or b 2如果a和b不是同一个索引优化器需要分别找两棵索引树再合并结果往往选择全表扫描更划算。解决办法是把or改成union all或者给两侧单独的列分别建索引。MySQL 8.0的index merge优化虽然能处理部分场景但依赖统计信息和成本估算不能完全依赖。模糊匹配的规则是前导通配符会失效like %abc无法使用索引因为B树按字典序排列无法从abc前面的任意字符开始定位而like abc%可以利用索引做range扫描。如果业务确实需要前导通配符的搜索一般建议引入专门的搜索引擎而不是在MySQL层面强行优化。另外一个容易被忽略的场景是NULL值判断。is null/is not null在索引列上是否能走索引与MySQL版本、列的nullable属性以及统计信息有关。稳妥做法是给常用列设置not null default值不光是索引考虑也能减少业务方的迷惑。5. 一个生产案例订单查询从2.8秒到40毫秒5.1 现象与初步定位查询阻塞并非数据库负载问题这个案例来自一个电商后台系统订单表orders约80万行线上高峰期查询当前用户的待处理订单列表这个接口经常超过2秒。最开始大家怀疑是数据库负载高、连接数打满但查看监控发现CPU、内存、磁盘IO都在正常范围数据库连接数也远未到上限。这就比较典型负载低但查询慢问题多半出在执行计划上。把线上执行的SQL捞出来简化后大约是这样SELECT order_no, status, amount, create_time FROM orders WHERE user_id 10086 AND status 1 ORDER BY create_time DESC LIMIT 20;对应的表结构上原有索引是单个的idx_user_id(user_id)。explain结果如下typerefkeyidx_user_idrows大约8000Extra里出现了Using filesort。看到这里基本可以确定瓶颈不在全表扫描而在两个地方一是user_id等值过滤后仍然有8000行左右的估量需要逐行回表取order_no、status、amount、create_time二是排序字段create_time不在索引里必须做filesort。可能有人会问既然limit 20排序后才取20行为什么还是慢因为filesort需要把所有命中的行集约8000行全部读出来排序即使最终只取20行。而且在高并发下这个排序任务会反复执行累积起来的开销就很可观。5.2 索引方案设计与实施从ref到Using index分析到这里方案就明确了。把现有单列索引 idx_user_id 替换成联合索引ALTER TABLE orders DROP INDEX idx_user_id, ADD INDEX idx_user_status_time (user_id, status, create_time);为什么是(user_id, status, create_time)而不是其他顺序首先user_id是等值条件且区分度足够高放在最左边负责把扫描范围直接从80万行缩小到几千行其次status是等值条件可以继续压缩虽然status只有几个取值但加上它对缩小每个user_id下需要排序的数据量有帮助最后create_time放在最右边作用是让索引直接按create_time有序返回省掉filesort。同时select中的order_no、status、amount、create_time中status和create_time已在索引内order_no和amount需要回表获取。那要不要把order_no和amount也放进索引做成覆盖索引我的建议是不要。因为limit 20固定了最终返回行数哪怕回表也就回20行成本很低但amount如果放进索引会导致索引体积明显膨胀写入开销增加性价比不高。实际开发中应根据select列表的出现频率和表写入压力做取舍。上线方式我用了 pt-online-schema-change 工具做在线变更避免在业务高峰期执行ALTER TABLE锁表。大表加索引不只是create index本身的速度还需要重建整张表的数据页。80万行不算特别大但生产环境还是稳妥为上。5.3 调优结果与验证上线前这样确认收益变更完成后再看explaintyperefkeyidx_user_status_timerows从8000降到几十行Extra里的Using filesort消失变成了Using index condition因为status和create_time都用于索引判断。实测同一SQL耗时从2.8秒降到40毫秒左右接口TP99也恢复到了正常水平。把验证过程总结成一个清单供参考变更前先记录慢SQL的执行时间、explain关键字段变更后对比同样的explain输出确认type、key、rows、Extra四项都有改善在测试环境用线上导出的数据压测确认写入性能没有明显恶化设置回滚预案保留旧索引定义必要时快速重建。这一步很关键很多朋友加完索引后发现没效果往往是没对比explain只看执行时间忽略了其他因素干扰。6. 索引维护的日常功课冗余清理与碎片整理6.1 冗余索引检查多花十分钟省下大量写入开销线上系统运行一段时间后索引往往会越加越多其中有大量冗余。比如已经有了(user_id, status)联合索引再单独建(user_id)单列索引后者就是冗余的因为联合索引的最左前缀已经覆盖了user_id的查询。冗余索引不只浪费空间还会让每次写入的维护成本增加需要定期清理。排查冗余索引最简单的办法是用information_schemaSELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols FROM information_schema.statistics WHERE table_schema your_db GROUP BY table_name, index_name;把每个索引的列组合拉出来后人工比对一下是否有前缀相同且列数更多的索引。也可以使用Percona Toolkit里的pt-duplicate-key-checker它会自动扫描并给出冗余索引建议输出很直观适合没有专门工具链的小团队使用。清理时注意先在测试库跑一遍业务回归删除后观察一周慢查询数量确认没有SQL依赖被删索引再做后续动作。6.2 碎片整理与统计信息更新索引也需要体检InnoDB的索引在频繁的增删改后会产生页分裂和页碎片原本紧密的B树会变得稀疏导致索引占用空间变大扫描效率下降。常见的处理手段有两类一是重建整表ALTER TABLE orders ENGINEInnoDB;这会重建表压缩碎片但期间需要锁表MySQL 5.6 会做在线DDL但依然建议低峰期操作。二是用OPTIMIZE TABLE orders效果类似。8.0版本里还有一个更轻量的方式重建单个索引但同样有成本。碎片之外统计信息的准确度直接影响优化器对索引的选择。频繁更新但统计信息没有及时收集时优化器可能因为rows估算失准而放弃索引。可以通过ANALYZE TABLE orders手动收集统计信息。很多线上案例表现为索引明明存在且查询条件合理explain却不用跑完ANALYZE之后执行计划就恢复正常了。日常维护的时间点也有讲究。我一般习惯在每周对核心大表做一次ANALYZE每月或每次大促前做一次索引冗余检查和碎片整理。修改表结构永远比直接改SQL风险高先低峰测试再上线执行。索引调优不是一次性的项目而是一条需要持续观察和迭代的链路。最后分享一个我个人坚持了很久的习惯每次给生产环境加索引前我都会写一条调优前后对比记录内容包括执行时间、explain关键字段、rows估算、是否出现filesort。长期积累下来哪些场景加什么索引有效、哪些场景容易翻车基本一眼就能判断。很多朋友问我调优有没有捷径我的回答永远是先学会看explain再动手加索引每次变更前都想清楚为什么。技巧可以速成但这种记录习惯很难速成恰恰是它让调优从碰运气变成可预期。