线上MySQL慢查询日志堆了二十多条EXPLAIN一跑全是typeALL索引明明建了却像摆设——这种场景后端和DBA大概率都经历过。MySQL索引优化这件事很多人卡在“原理知道一点、调优全靠猜”的阶段建了几个索引SQL慢了就加索引不行再换最后库表写了上百个索引写入越来越慢查询还是没快到哪去。这篇就把我从原理到调优的完整思路整理出来从B树结构、聚簇索引到底层存储到EXPLAIN的每个关键字段怎么读再到慢查询定位和真实调优案例一步步来。适合刚接触索引优化但被“理论全会、实战全废”困住的开发也适合被慢SQL追着跑、想系统过一次索引调优的运维。1. 索引的本质为什么B树能救MySQL1.1 从二分查找说起B树的底层逻辑索引到底解决什么问题说白了就是让数据库少干点活。没有索引的时候MySQL要从表的第一行读到最后一行每行都做匹配这叫全表扫描数据量一上来就是灾难。索引的本质有点像一本书的目录你查某个知识点不会从头翻到尾先看目录定位到章节再直接翻过去。但为什么MySQL的InnoDB引擎最终选了B树而不是二叉搜索树、红黑树或者哈希表这里有一个关键约束磁盘IO。数据库的数据存在磁盘上读一次磁盘页的开销比内存操作慢几个数量级所以索引结构必须尽量减少“访问磁盘”的次数。二叉搜索树和红黑树的问题是树太高几百万行数据可能需要二十多层每层一次磁盘IO基本不可用。哈希索引能做精确等值查询但做不了范围查询和排序一次查询就要遍历全部哈希桶所以也不能当通用索引。B树的优势在于“矮胖”。非叶子节点只存索引键值和子节点指针不存数据因此一个16KB的页能塞下成百上千个键值。按MySQL默认页大小16KB来算三层B树大概能索引千万级甚至上亿的数据行。三次磁盘IO定位到数据这个性价比很值。B树的叶子节点之间还有双向链表天然支持范围查询和排序这正是SQL里WHERE id 100 AND id 200、ORDER BY这类操作最需要的特性。1.2 InnoDB聚簇索引主键为什么这么重要InnoDB的索引和MyISAM有个本质区别数据行本身存储在聚簇索引的叶子节点上。换句话说InnoDB表就是一棵以主键为索引的B树你访问任何一行数据最终都要通过主键索引找到它。这也是为什么InnoDB强烈建议你显式定义主键——没有主键时它会找个非空唯一索引还找不到就偷偷生成一个6字节的rowid当主键这个隐藏主键你完全控制不了后续所有二级索引的回表都会受影响。聚簇索引带来的最直接约束是主键的插入顺序最好是有序的。如果主键是自增整数新行总是追加到B树的最右叶子节点页分裂很少发生。我用一个比喻来解释页分裂叶子页满了就像图书馆一块书架上书满了必须把书挪到两个新书架上原有书的物理位置全部改变。这个操作代价很高而且会让索引产生碎片。如果主键是UUID之类的随机字符串每次插入都可能落在B树中间的某个位置频繁触发页分裂插入性能会明显下降。还有一点很多人容易忽略二级索引的叶子节点存的是主键值不是行记录本身。所以拿普通索引查一行数据要先扫二级索引定位到主键再回聚簇索引查完整行。主键越大二级索引占用的空间就越大缓冲池能缓存的索引页就越少。这不是“用varchar当主键行不行”的事而是“用varchar当主键会让所有索引膨胀”的事。能用整型做主键就尽量别用字符串能自增就别随机。1.3 二级索引如何“回表”以及什么时候不用回表二级索引和聚簇索引的差别就是查询时可能要“多走一步”。比如表里有user_id普通索引执行SELECT * FROM user WHERE user_id100MySQL先扫二级索引拿到主键id再拿这个主键回聚簇索引里查完整行。这个“回表”动作在数据量大时是额外开销一次两次无所谓成千上万次就有感知了。但如果查询的列已经被包含在索引里就完全不用回表。比如SELECT user_id, status FROM order WHERE user_id100而user_id上有索引order表加个status字段组成(user_id, status)复合索引那查询需要的两列都能在二级索引的B树里直接拿到这就是覆盖索引。我习惯在优化高频查询时把select的字段和where、order by的字段一起放进复合索引里这是成本最低的一类优化SQL和表结构都不用大改。2. 索引设计实战如何把索引建在刀刃上2.1 什么字段值得建索引建索引不是越多越好而是要把钱花在刀刃上。这个“刀刃”就是查询频率高、区分度高、使用频繁出现在WHERE/ON/GROUP BY/ORDER BY里的字段。判断区分度有一个很简单的SQLSELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;这个值越接近1说明字段的可选择性越高索引效果越好。性别列、状态列往往只有两三个值选择性几乎趋近于0如果查询结果占总行数比例太高优化器很可能放弃索引直接全表扫描因为全表扫描相比“大量回表”反而更快。这不是索引失效是优化器在帮你算成本账。还有字段类型的问题。整型字段的索引比字符串字段更紧凑比较速度也更快。固定长度的CHAR比可变长度的VARCHAR在索引存储上更有优势。对于VARCHAR这种变长字符串做索引如果长度太长比如存URL、长文本可以考虑只对前N个字符建索引也就是前缀索引。ALTER TABLE article ADD INDEX idx_url(url(64));注意前缀索引有一个天然缺陷索引里只存了前64个字符无法用于覆盖索引因为叶子节点里没有完整列值。另外排序场景下前缀索引也用不上因为索引中存的是截断后的内容。2.2 复合索引的最左前缀原则到底怎么用复合索引是实战里最常用也最容易用错的索引。它遵循“最左前缀”原则查询条件必须从索引的最左列开始连续命中索引才会被用到。比如建了(user_id, status, create_time)这个复合索引那么-- 走索引最左列user_id在条件里且后续列连续 SELECT * FROM order WHERE user_id 1 AND status 2; SELECT * FROM order WHERE user_id 1 AND status 2 AND create_time 2024-01-01; -- 部分走索引只用到user_id进行索引定位status无法继续用索引 SELECT * FROM order WHERE user_id 1 AND create_time 2024-01-01; -- 走不了索引没包含最左列user_id SELECT * FROM order WHERE status 2 AND create_time 2024-01-01;这里有个常见误区把区分度最高的字段放在复合索引最左边是不是一定最好不一定。最左前缀原则决定了如果你经常按status做等值查询但又把status放在第二列且第一列不是常量的情况下很多查询用不上索引。所以设计复合索引时第一优先是查询模式——哪个字段经常作为独立等值条件出现谁就更适合放最左边然后再在这个基础上考虑字段区分度。复合索引还能帮ORDER BY和GROUP BY省事。如果索引的列顺序恰好是排序顺序MySQL可以直接扫索引返回有序结果省掉一次filesort。比如索引(a, b)ORDER BY a, b就能避免额外排序但ORDER BY b, a或是ORDER BY a DESC, b ASC这类方向不一致的排序索引也帮不上忙。2.3 覆盖索引与索引下推把回表次数降到最低覆盖索引前面提过就是让查询的字段全部落在二级索引里。实战里我经常通过“冗余”一两个字段进索引来消灭回表。举个例子CREATE TABLE order ( id BIGINT PRIMARY KEY, user_id BIGINT, status TINYINT, amount DECIMAL(10,2), create_time DATETIME, KEY idx_user_status (user_id, status) ); SELECT user_id, status FROM order WHERE user_id 100;这个查询只需要user_id和status而user_id和status都在idx_user_status索引里MySQL只要扫二级索引就能返回结果Explain里的Extra字段会显示Using index意思是纯索引扫描不用回表。还有一个容易被忽视的优化是索引下推Index Condition PushdownICP。MySQL 5.6开始默认开启它允许MySQL在二级索引的存储引擎层就过滤掉一部分不满足条件的记录减少回表次数。没有ICP时MySQL是先通过索引定位到主键再回表取完整行然后在server层用WHERE剩下的条件过滤有ICP时如果查询条件里有关联到索引列且能下推到存储引擎的条件比如复杂复合索引里跳过了中间列的条件存储引擎会在读二级索引时就判断不满足的直接跳过。简单说ICP把一部分原本在server层做的过滤下推到了存储引擎减少回表次数。EXPLAIN里Extra出现Using index condition就是这个机制在生效。3. EXPLAIN必备技能读懂执行计划3.1 先看type判断扫描级别的第一指标执行EXPLAIN SELECT ...出来的结果里第一眼应该看type列。它反映了MySQL是怎么访问数据的从好到差大概是这样type级别含义典型场景system表只有一行系统表几乎见不到const通过主键或唯一索引等值查询WHERE id 1eq_ref被驱动表通过主键/唯一索引等值匹配join关联时被驱动表用主键查ref普通索引等值匹配WHERE user_id 100range索引范围扫描WHERE id 1 AND id 100index遍历整棵索引树比全表略好但也没好到哪去ALL全表扫描没有使用索引优化目标一般是让查询至少达到range级别核心查询尽量达到ref或以上。实际工作中我看到大量慢SQL是ALL或index这种基本是索引没建对或者查询写法破坏了索引使用。key列也要一起看它显示的是实际选择的索引名称。如果possible_keys里明明有候选索引但key是NULL说明优化器算了成本之后认为当前索引不合适常见原因包括选择性太低、回表代价高、数据量太小直接全表扫更快。3.2 Extra里的信息才是关键Extra包含的信息往往比type更值钱它决定了MySQL在索引之上还额外做了什么事。看到下面这些词就要警惕Using filesortMySQL在磁盘或内存里额外做了一次排序。排序无法利用索引顺序时就会出现比如ORDER BY字段和索引列顺序不一致。这个非常影响性能尤其排序结果集大的时候。Using temporary查询需要建立临时表。常见于GROUP BY、DISTINCT、UNION这类操作。临时表可能落到磁盘是性能杀手。Using index纯索引扫描覆盖索引生效这是好现象。Using index condition索引条件下推生效回表行数被提前减少。Using where存储引擎返回行后在server层又做了条件过滤。单独出现不算坏事但如果行数很大说明过滤条件里有些字段没进索引。我调优的顺序是先把ALL变成range以上再看Extra里有没有filesort和temporary想办法让排序和分组也利用上索引。如果EXPLAIN结果显示rows非常大但filtered很低那说明MySQL扫描了大量行只筛出很少的结果优先优化扫描行数比优化filtered更直接。3.3 实操一条慢查询的执行计划解读看一条实际执行的慢SQLEXPLAIN SELECT order_id, user_id, amount FROM order WHERE status 1 ORDER BY create_time DESC LIMIT 10;假设结果idtabletypekeyrowsExtra1orderALLNULL50000Using where; Using filesorttypeALL说明全表扫描rows50000说明预估扫描了全表Extra里还有Using filesort排序也没用到索引这条查询在数据量继续增长后会越来越慢。问题就出在索引设计上status1的数据可能有几千条但MySQL为了找到这几千条扫了五万行还要额外对create_time排序。优化思路是建一个包含过滤字段和排序字段的复合索引ALTER TABLE order ADD INDEX idx_status_create_time (status, create_time);再次EXPLAINidtabletypekeyrowsExtra1orderrefidx_status_create_time200Using index conditiontype从ALL变成refrows从50000降到200Using filesort消失了。因为create_time在复合索引中是有序的MySQL可以直接从索引中按逆序取前10条排序成本被优化掉。4. 慢查询定位与索引调优完整流程4.1 开启慢查询日志但要控制采样策略索引优化的第一步是先找到值得优化的SQL不是拿到一条就调一条。我一般先开慢查询日志设置阈值。MySQL里相关参数主要是这几个SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time单位是秒生产环境第一次巡检建议先设成1秒或2秒不要一上来就设0.1秒否则满屏都是慢查询根本没法筛选。log_queries_not_using_indexes会把没有用索引的查询也记进去这个对排查索引漏建很有用但会产生大量日志生产环境建议只开一段时间分析完就关。日志会不断膨胀建议配合pt-query-digest这类工具做聚合分析按“总执行时间”、“平均执行时间”、“出现次数”排序快速定位最值得优化的Top SQL。没有工具的话直接看日志里的Time和Rows_examined也能初步判断Rows_examined过万的查询优先处理。4.2 从日志到优化一个真实场景演练有一次我处理过一张订单流水表表里几百万行慢查询日志里反复出现这样一条SQLSELECT id, order_no, user_id, amount, status, create_time FROM payment_flow WHERE user_id 123456 AND create_time BETWEEN 2024-06-01 AND 2024-06-30 ORDER BY create_time DESC LIMIT 20;当时表上已经有一个idx_user_id单列索引。EXPLAIN一看type是refrows约8000Extra里面直接出现了Using filesort。问题很明显user_id等值过滤能缩小范围但create_time的排序字段不在索引里MySQL把8000行数据捞出来又做了一次文件排序再取前20条。数据量小的时候感知不到量大以后每次查询都排8000行非常浪费。我的调整是删掉idx_user_id改成复合索引(user_id, create_time)。这里没有把status、amount这些select字段加进去因为返回列太多全部冗余进索引会让索引过于臃肿写入开销上升。改成复合索引后EXPLAIN里Extra变为Using index conditionrows也降到了20以内。因为create_time在索引里已经有序MySQL直接从user_id123456对应的索引段末尾反向取20条返回不再做filesort。还有一个细节原来只有idx_user_id时凡是只用user_id作为条件的其他查询也能走索引改成(user_id, create_time)后因为最左前缀原则user_id单独作为条件的查询依然能走这个复合索引一箭双雕旧查询不受影响。4.3 优化后如何验证不能只看EXPLAIN加完索引不能只在测试环境跑一下就算完我通常会用PROFILE看一下各阶段耗时确认瓶颈是不是真的被解掉了。SET profiling 1; -- 执行目标SQL SELECT ... -- 查看profile SHOW PROFILE FOR QUERY 1;SHOW PROFILE可以看到Sending data、Sorting result、Creating sort index等阶段耗时。优化前如果Creating sort index时间很长优化后这个阶段应该显著下降或消失。Sorting result没了说明排序被索引覆盖了。验证的时候还要控制变量同一套数据、同一台机器、同一个时段跑对比优化前后的响应时间和rows变化。生产环境上线索引前关注一下表大小大表加索引可能导致锁表或长时间DDL建议用在线DDL工具或者避开业务高峰操作。MySQL 8.0在部分场景下已经支持原子DDL但表数据量大时仍然要谨慎。5. 索引失效的经典场景与避坑清单5.1 函数、隐式转换、模糊查询为何击穿索引索引不生效很多时候不是索引没建而是SQL写法在“骗”优化器。最常见的就是对索引列使用了函数。比如SELECT * FROM user WHERE DATE(create_time) 2024-06-01; SELECT * FROM user WHERE YEAR(create_time) 2024;即使create_time上有索引MySQL也无法直接利用因为索引里存的是原始时间值不是函数的计算结果。优化器没法在B树上做快速查找只能全量计算再过滤。解决办法是改成范围条件SELECT * FROM user WHERE create_time 2024-06-01 AND create_time 2024-06-02;这样能让查询落到索引范围扫描上。另一个高频坑是隐式类型转换。字段是字符串查询条件写成了数字MySQL会悄悄把字段转成数字再比较索引就会失效。比如-- phone字段是varchar条件里写成数字 SELECT * FROM user WHERE phone 13800138000;这个查询在phone上有索引也会变成全表扫描正确姿势是数字加引号SELECT * FROM user WHERE phone 13800138000;还有一类是模糊查询。LIKE abc%能走索引LIKE %abc%走不了因为左边有通配符时B树不知道从哪个节点开始匹配没法进行范围定位。确实需要全文检索的业务可以考虑FULLTEXT索引或者搜索引擎方案而不是硬扛LIKE %...%。5.2 索引失效排查速查表场景示例是否走索引解决方案索引列使用函数WHERE YEAR(create_time)2024否改写为范围条件隐式类型转换WHERE phone123456否查询值加引号匹配字段类型左模糊查询LIKE %abc否用右模糊或全文索引OR连接非索引条件WHERE id1 OR namea可能否拆成两条SQL加UNION ALL跳过最左前缀索引(a,b)条件WHERE b1否调整索引列顺序或单独建b索引对索引列做计算WHERE id1100否改写为WHERE id99不等于条件WHERE status ! 2可能否视区分度必要时业务拆分复合索引中间列范围条件索引(a,b,c)WHERE a1 AND b2 AND c3部分c无法走索引情况允许时调整索引列顺序这个表是我排查慢查询时常用的一张对照表。每次遇到“明明建了索引但没走”的报障照着表里的场景逐一核对基本能定位到原因。6. 那些让我印象深刻的坑和心得6.1 冗余索引和重复索引没人注意的拖累索引不是免费的午餐每多一个索引写入、更新、删除时都要额外维护B树。我遇到过一个项目表只有几万行索引却建了12个其中(a, b)和单独的(a)同时存在。单独(a)的索引就是冗余的因为(a, b)索引的左侧已经能覆盖单列a的查询。这类冗余索引唯一的作用就是拖慢写入和占用磁盘。MySQL 8.0提供了不可见索引INVISIBLE我调优时会先把怀疑冗余的索引改成不可见观察一段时间业务的慢查询和报错情况确认没有查询真正用到后再DROP。这个操作比直接DROP安全得多因为不可见索引只是对优化器不可见数据还在随时可以改回来。另外提醒一句USE INDEX这个hint要慎用。它是在告诉优化器“必须用我这个索引”如果索引选择不对反而会制造更慢的执行计划。我调优时尽量让优化器自己选只在它确实选错且没法通过改SQL纠正时才用。6.2 三个真实案例状态列索引、隐式转换、连表排序第一个案例是状态列建索引。有个订单表status只有三个值0、1、2业务查询大量用WHERE status1 LIMIT 10。刚上手的人会直觉建个status索引但EXPLAIN一看还是全表扫描。原因很简单status1的行占了全表90%优化器算了下回表成本觉得全表更快。真正的解法是把status和其他筛选字段组合成复合索引或者把查询改成先按主键范围扫描再过滤而不是单独给低区分度字段建索引。第二个案例是隐式转换。一次排查慢查询发现表里user_id是varchar类型代码里拼SQL传的是整数结果MySQL做了隐式转换每次查询都要把列值转成数字几百万行的表直接全表扫描。加上引号之后同一个SQL从耗时1.2秒降到30毫秒。这个案例我印象特别深因为问题不是索引设计而是上游传参没按字段类型来。第三个案例是连表查询里的排序索引。一条SQL关联了三张表ORDER BY字段来自其中一张表的非驱动表列EXPLAIN里出现Using temporary; Using filesort查询要等所有join做完才能排序。我调整了驱动表顺序让排序字段所在的表作为驱动表并且在该表上按照排序字段建了索引排序的临时表和filesort就消掉了。连表查询的执行顺序和驱动表选择直接影响排序能否利用索引这也是EXPLAIN里id列的价值——确认哪张表先被访问。6.3 调优的边界索引不是万能药最后说点可能不太中听但很真实的经验索引优化不是万能药甚至不该是第一步。我见过很多慢查询根因是业务逻辑写得有问题——循环里逐条查询、嵌套子查询当主查询用、一个大事务里塞了几百条SQL。这种场景下你建再多索引也救不回来。先看业务逻辑有没有不合理的循环、能不能批量查询、能不能减少不必要的去重和排序再谈索引。小表也不用过度优化。一张表只有几千行全表扫描耗时可能比走索引还快因为InnoDB的内存缓冲池可能已经把整张表都缓存了。这个时候硬加索引反而让写入变慢没有任何收益。我一般会把“全表扫描”和“慢查询”分开看只有扫描行数和实际返回行数差距很大、且会随着数据增长持续恶化时索引优化才真正有价值。还有一点索引设计要跟着查询模式走而查询模式是会发生变化的。上线新功能之前专门review一下新增SQL的执行计划成本很低但收益很高。我习惯把慢查询日志、索引清单、EXPLAIN结果这三样东西放到一起定期过一遍避免等到线上出故障了才想起来看。7. 关于索引调优我最后想分享的小技巧有一次处理一个线上案例order by排序字段没在索引里导致每次查询都触发filesort。我在调整索引顺序时发现把WHERE里的等值字段和ORDER BY字段放进同一个复合索引filesort就直接消失了。这个技巧很简单但很多人容易忽略复合索引不只是用来过滤的索引天然带有序性完全可以用它来替代排序。还有一个习惯新上线查询前先跑一遍EXPLAIN看type和Extra至少保证核心查询不是ALL和Using filesort。这个成本只需要一分钟但能省掉后续大量的排查时间。索引设计本来就不存在一次性做到完美它更像是给未来的查询模式留一块可以灵活变化的跳板上线后持续观察有新的慢查询再回头调整索引。这种“先定位、再设计、后验证”的循环才是我这些年调优下来真正有效的做法。