1. 一次全表扫描引发的线上超时索引到底在救什么火写这篇笔记的时候我正盯着线上的一张订单表发呆。表里只有三百万行不算多但运营后台昨天点一次查询直接等了快两秒。这不是第一次因为少一个索引被人吐槽了。更尴尬的是我第一反应不是去看业务代码而是先上服务器查SQL——因为这种怪问题十有八九出在MySQL索引上。我处理过的故障流程基本是固定的。先执行show processlist看有没有长时间Running的查询再打开慢查询日志设置long_query_time1回放一段时间把嫌疑SQL捞到测试库跑一遍EXPLAIN重点看type、key和rows三列。如果发现typeALL说明MySQL正在做全表扫描那么优化方向就很明确了根据WHERE和ORDER BY条件设计合适的索引然后上线一条CREATE INDEX。很多人有一个误解以为索引只是“加速查询”的优化项实际上它解决的是“减少磁盘I/O次数”这个核心问题。三百万行的表假设每行500字节全量数据大概1.5GB。InnoDB默认16KB一个数据页全表扫描至少碰九万多个页。即使是SSD随机读几万个页也需要好几秒机械硬盘更不用说。而走B树索引从根节点到叶子一般是三层加上回表一次总共也就三四次逻辑I/O。这个差距是数量级的不是百分之几十的提升。1.1 从报警到定位慢SQL的排查路径实际处理的时候我不会上来就说“加索引”而是把SQL、表结构、数据分布放在一起看。比如下面这条从慢查询日志里捞出来的语句SELECT order_id, order_status, created_at FROM orders WHERE user_id 12345 AND order_status IN (1, 2) ORDER BY created_at DESC LIMIT 20;这条SQL的特点是user_id和order_status都是过滤条件created_at还用来排序。最直观的索引设计是建立联合索引(user_id, order_status, created_at)因为user_id等值过滤之后order_status可以继续过滤而created_at本身就处在索引的连续有序区间里排序可以直接借助索引顺序省掉filesort。但是要注意order_status如果只有几个枚举值区分度很低把它放在created_at前面可能并不划算。优化器经过成本估算后索引扫描加回表的开销不一定比全表扫描好。所以我当时最终建的是(user_id, created_at)让created_at用来排序order_status只做过滤效果反而更好。索引设计不是套公式而是看真实数据分布来决定字段先后顺序。1.2 全表扫描的代价磁盘I/O和三层B树的对比要理解为什么索引快得先把“全表扫描为什么慢”想透彻。全表扫描不能利用缓存中的数据页顺序几乎每读一个页都可能触发一次磁盘I/O。假设每秒能做500次随机I/O九万个页就是180秒这还只是读数据没算过滤和网络开销。所以大表直接丢给全表扫描慢是必然的。而B树索引的高度决定了查询需要访问几个页。平时我们用的大多数表索引都能控制在三层以内找到一条记录只需要三四次页访问其中根节点和中间层通常在buffer pool里已经缓存真正落到磁盘的往往只有最后一次读叶子页。这就是索引能把查询从“秒级”降到“毫秒级”的根本原因。2. InnoDB的B树索引结构为什么是这样2.1 哈希、B树和B树的取舍面试时经常被问“为什么MySQL用B树不用哈希索引”。这个问题如果只回答“哈希不支持范围查询”其实没有答到根上。我们换个角度数据库的索引不仅要支持单点查询还要支持排序、范围扫描、前缀匹配这些操作要求索引本身是有序的。哈希结构等值查询确实快但数据存储时是无序散布的范围查询只能全量穷举排序更是无从谈起。B树虽然有序但它有一个问题非叶子节点也会保存完整的数据记录导致每个节点能容纳的索引项变少树就会变高。树越高查询需要的磁盘I/O次数越多。B树则不同所有数据都存放在叶子节点非叶子节点只保存索引键和子页指针所以单个页能容纳的索引项更多树更矮I/O更少。同时B树的叶子节点之间用双向链表串起来范围查询找到起始位置后沿着链表顺序扫就完了这比B树一层层回溯要高效得多。2.2 数据页的物理世界页、页目录与双向链表InnoDB最小的存储单位是页默认16KB。一张表的数据不会零散乱放而是按主键顺序组织在页内。页内部有一个页目录相当于对记录做了一层稀疏索引可以在页内做二分查找定位到具体记录。页和页之间通过双向链表连接形成逻辑上的有序结构。如果按自增主键连续插入新记录基本都追加在末尾写入最顺畅。如果主键是UUID这种随机值新记录可能落在任意页InnoDB需要反复做页分裂和记录移动连带产生大量碎片和随机I/O。这也是生产环境中主张“主键用自增整数”的根本原因不是单纯的洁癖而是关系到底层存储的写入效率。页分裂的过程不复杂但代价不小当新记录要插入的页已经满了InnoDB会申请一个空页并把一半记录搬过去重新调整页目录和链表指针。频繁分裂不仅拖慢写入还会让数据物理分布不再连续后续范围扫描的性能也会受影响。2.3 三层B树能装多少行数据这个数字值得自己算一遍算完就对索引层高有直觉了。假设主键是bigint8字节加上6字节的子页指针每个非叶子节点条目约14字节。16KB的页能放约1170个索引项。叶子页如果一条记录约1KB一个页能放约16行。三层B树大约能支撑1170 * 1170 * 16约两千一百多万行。如果主键换成int索引项更小三层树承载量能到四千万行左右。这个估算有什么实际意义当你预判一张表的数据量会长期保持在几千万行以内时索引基本可以稳定在三层查询延迟非常可控。一旦数据量继续膨胀树增加一层查询就可能多一次离散I/O性能会明显下滑。这时候要思考的不只是索引本身还包括分库分表、归档冷数据等更上层的方案。3. 聚簇索引与二级索引回表问题背后的设计逻辑3.1 聚簇索引叶子节点直接放整行InnoDB和MyISAM最大的区别就是InnoDB的表本身是一棵B树。这张表的聚簇索引按主键构建叶子节点直接存放完整行数据。也就是说主键就是数据的物理组织方式每个表只能有一个聚簇索引。如果你建表时没有定义主键InnoDB会挑第一个非空的唯一索引作为聚簇索引如果连唯一索引都没有它会生成一个隐藏的6字节row_id当主键。了解这个规则很重要因为它意味着“没有主键不代表没有聚簇索引”隐藏主键反而会让索引结构不可控所以建表时应当主动设计主键。通过主键查行是最高效的路径因为从根节点走到叶子拿到的就是整行数据不需要再找其他地方。反过来也成立一旦查询条件不是主键MySQL就需要借助二级索引可能产生回表。3.2 二级索引为什么要存主键而不是地址二级索引是“非主键索引”的统称它的叶子节点并不存行的物理地址而是存主键值。很多刚开始学MySQL的同学不理解这一点为什么不直接存个指针回表多快答案是物理地址不稳定。数据页发生分裂、行记录移动时物理位置会变如果二级索引直接存地址每次移动都要同步更新所有二级索引代价高到无法接受。存主键值的方案更稳妥。主键本身在设计上不会轻易变化行移动时二级索引不需要跟着改。代价就是查询时需要“回表”先用二级索引找到主键再回到聚簇索引里取完整行。如果二级索引命中了上万行最坏情况下就要回表上万次产生大量随机I/O。所以设计索引时要时刻问自己这个查询能不能不回表只要把需要用到的字段都放进索引查询就能用颗覆盖索引解决。覆盖索引的意义不是省一点半点的CPU而是把可能发生的大量回表直接消除掉性能提升往往是数量级的。3.3 覆盖索引与索引下推的收益覆盖索引就是指“查询的所有字段都包含在索引中”比如SELECT order_status FROM orders WHERE user_id12345只要联合索引包含user_id和order_status查询直接在二级索引上就能返回结果不需要回表。与覆盖索引容易混淆的是索引下推Index Condition PushdownICP。在MySQL 5.6之后即使索引不能完全覆盖WHERE条件InnoDB在遍历索引时也可以先对索引中的字段做一次条件判断过滤掉不满足的记录减少回表次数。举个例子联合索引(user_id, created_at)查询里还带了一个order_status条件这个条件虽然不在索引里但遍历索引时已经知道user_id和created_at是否匹配先把不匹配的跳过真正需要回表的记录就少很多。这个特性默认开启大多数情况下能明显降低随机I/O。4. 联合索引的字段顺序最左前缀原则的工程落地4.1 联合索引在B树里的排序规则联合索引不是多个独立索引的简单叠加它本质上还是一棵B树只不过排序规则变成了“先按第一个字段再按第二个字段再按第三个字段”。也就是说索引里的记录在第一列上是全局有序的第二列只在第一列相同的前提下有序第三列又只在第一、第二列都相同时才有序。这个排序规则直接决定了最左前缀原则。如果查询条件里没有索引的第一列那第二列或第三列在整棵索引树上并没有完整的全局顺序优化器很难利用索引的有序性去快速定位自然就退化成扫描整棵索引树甚至全表扫描。很多人把“最左前缀”背成一条规则其实只要理解了排序规则就能自己推导出来B树的有序性是从最左边开始建立的跳过前缀就切断了有序链。4.2 怎么判断一条SQL能不能用联合索引我判断一条SQL能否命中联合索引习惯分三步思考。第一先把SQL里的等值条件摘出来看它们是否从联合索引第一列开始连续覆盖第二遇到第一个范围条件比如、、BETWEEN范围之后的字段顺序就无法继续保证只能作过滤不能用于排序第三看ORDER BY或GROUP BY的字段是否和索引顺序一致一致就能省掉filesort。拿两个例子来说-- 能用到索引前缀 user_id 和 order_status WHERE user_id 1 AND order_status IN (1, 2)IN可以理解成多个等值条件的集合所以它不会阻断后面的字段继续匹配。-- 索引只能用到 user_idcreated_at 无法进一步过滤 WHERE user_id 1 AND created_at 2024-01-01这里created_at一出现范围条件若索引是(user_id, order_status, created_at)则created_at之后的字段都参与不了排序或范围定位但依然可以做索引内过滤能不能优化取决于具体行数。4.3 字段顺序设计等值、范围、排序的三段式设计联合索引时我常用一个“三段式”思路等值条件的字段放最前范围条件的字段放中间排序字段放最后。这样可以让查询在最开始就用最少的行数收敛同时尽量利用索引的有序性。但三段式只是起点不是终点。还要考虑字段的区分度。比如一个status字段只有五个枚举值把它放在索引第一位虽然能命中索引但过滤能力不强后续还要扫很多行。更合理的做法是把user_id这种区分度高的放前面低区分度字段甚至可以不用放进索引避免无端增加存储和写入开销。我在生产里会用真实SQL和数据分布做对比实验建几种联合索引的组合分别EXPLAIN看rows和Extra两列的变化再结合占用空间和写入压力做决定。纸上谈兵式的索引设计很容易在真实流量下翻车。4.4 冗余索引的识别与清理索引建多了最先出现的问题不是查询慢而是写入变慢和存储膨胀。最典型的是冗余索引已有(user_id)索引时再建(user_id, order_status)那么前面的单列索引就没有存在的必要因为它能覆盖的查询联合索引的最左前缀也能覆盖。冗余索引不仅占空间每次插入、更新、删除都要维护多棵B树会让TPS明显下降。我在一次优化中遇到过一张表有十几个索引去掉六个完全冗余的之后写入性能回来了读性能几乎没变化。识别冗余索引的方法可以靠信息模式表查询也可以靠索引使用统计这部分我放在最后源码章节里。5. 索引失效的经典场景与排查链路5.1 隐式类型转换最隐蔽的索引杀手我遇到最多也最难发现的“索引没生效”其实是隐式类型转换。比如表里phone字段是varchar查询却写WHERE phone 13800138000MySQL会把字符串和数字做比较时进行类型转换等于对索引列施加了一个隐式函数B树的有序性自然失效。解决办法看起来很简单执行上却很容易忽略一是保证查询参数类型和字段类型一致二是在应用层做统一参数校验三是字符集也要一致。字符集不同导致的隐式转换更隐蔽比如utf8mb4和latin1关联时也可能让索引失效。所以我会在项目规范里明确要求建表统一字符集接口层统一参数类型。5.2 函数处理列与前置通配符对索引列做函数处理也是导致索引失效的高频原因。最典型的写法是把日期列包在函数里WHERE DATE(create_time) 2024-01-01这样的写法对索引极不友好。改写为范围查询之后create_time没有被函数包裹索引就能正常工作WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00LIKE前导通配符同理。LIKE abc%还能利用索引LIKE %abc%就基本只能全表扫了。如果业务确实需要任意位置的模糊匹配更合理的选择是走全文索引或外部搜索引擎而不是在数据库里硬扛。5.3 优化器成本判断没走索引不等于索引失效有时候SQL写得很规范索引也存在但EXPLAIN仍然显示typeALL。这种时候不要急着骂“索引失效”先想想优化器在做什么。优化器是基于成本选择执行计划的它会估算全表扫描和索引扫描加回表的成本如果某个低区分度字段过滤后还要返回大量行回表成本反而高优化器就会选全表扫描。这种场景常见于小表或数据分布极端的情况。比如一张只有几千行的配置表全表扫描本来就不慢索引的收益体现不出来或者status字段只取两个值查询返回一半以上的数据全表扫描比逐个回表更划算。排查这类问题要看rows和filtered再结合实际耗时判断而不是简单把责任推给“MySQL不用索引”。5.4 看懂EXPLAIN里的关键信号EXPLAIN输出里的关键列不多但每一列都值得盯住。type从ALL、index、range、ref、eq_ref到const是逐渐变好的趋势。key显示实际使用的索引名。rows是优化器预估的扫描行数。Extra里面藏着最值钱的信息。信号含义处理方向ref等值匹配索引正常range索引范围扫描正常但范围条件后的字段无法继续用于排序Using filesort未利用索引排序调整索引顺序尽量覆盖ORDER BYUsing temporary使用了临时表优化GROUP BY、DISTINCT、UNIONUsing index覆盖索引理想状态无需回表Using index condition索引下推5.6特性减少了回表看到Using filesort时我的第一反应是检查ORDER BY字段是否在索引里以及字段顺序是否和联合索引一致。看到Using temporary则要检查是否存在非等值连接或复杂分组这类场景往往涉及隐式排序和临时表是大查询的热门雷区。6. 附源码慢SQL定位与索引维护脚本6.1 慢查询日志配置和打开时机一条慢SQL没抓到之前一切优化都是盲人摸象。先把慢查询日志打开配置如下# /etc/my.cnf slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 0long_query_time不建议一上来就设成0.1不然日志量会暴涨反而干扰判断。生产环境从1秒开始比较稳妥观察一段时间后再根据业务容忍度往下调。6.2 用performance_schema找出TOP慢SQLMySQL 5.7之后不用重启实例也能查历史SQL的聚合统计。用下面这条SQL可以把最消耗时间的SQL按归一化文本排行SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT / 1000000000 AS total_ms, AVG_TIMER_WAIT / 1000000000 AS avg_ms, MAX_TIMER_WAIT / 1000000000 AS max_ms FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME IS NOT NULL ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;如果用的是MySQL 8.0还有更便捷的sys.statement_analysis视图字段更友好SELECT query, exec_count, avg_latency, rows_examined_avg FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 20;拿到DIGEST_TEXT或query之后下一步就是复制到测试库跑EXPLAIN。这套流程我基本每周跑一遍能及时发现新上线的SQL质量问题。6.3 索引使用率统计与冗余索引检测MySQL本身没有直接记录“每个索引被用了多少次”但8.0可以通过IO等待统计看出索引的热度SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR AS io_times FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db ORDER BY COUNT_STAR ASC LIMIT 20;如果某个索引长期排在末尾io_times接近0说明它基本没被使用可以考虑下线。但下线前要在业务低峰期观察一段时间避免因统计窗口短而误判。冗余索引的检测可以用下面这条SQL按同前缀索引做初步筛选SELECT s1.TABLE_SCHEMA, s1.TABLE_NAME, s1.INDEX_NAME AS redundant_index, s2.INDEX_NAME AS needed_index FROM information_schema.statistics s1 JOIN information_schema.statistics s2 ON s1.TABLE_SCHEMA s2.TABLE_SCHEMA AND s1.TABLE_NAME s2.TABLE_NAME AND s1.SEQ_IN_INDEX s2.SEQ_IN_INDEX AND s1.INDEX_NAME ! s2.INDEX_NAME WHERE s1.TABLE_SCHEMA NOT IN (mysql,sys,information_schema,performance_schema) AND s1.SEQ_IN_INDEX (SELECT MAX(s3.SEQ_IN_INDEX) FROM information_schema.statistics s3 WHERE s3.TABLE_SCHEMA s2.TABLE_SCHEMA AND s3.TABLE_NAME s2.TABLE_NAME AND s3.INDEX_NAME s2.INDEX_NAME) GROUP BY s1.TABLE_SCHEMA, s1.TABLE_NAME, s1.INDEX_NAME, s2.INDEX_NAME;这个SQL的逻辑是把索引字段前缀一一比对如果A索引的所有字段都是B索引的前缀A就是冗余候选。实际删索引前一定要人工确认A索引没有被单独用于特殊场景。6.4 大表加索引的实操提醒给大表加索引是个高危操作不能随手对线上库执行。MySQL 8.0支持INPLACE算法很多索引操作不用重建整张表但索引构建仍然会占用大量I/O和锁资源。更稳妥的方式是用gh-ost或pt-osc这类在线DDL工具在后台从库或临时实例完成索引重建再切换流量。另外删除索引也要谨慎。一个索引虽然长期没用但删除后如果某个低频查询突然变慢影响可能比想象中严重。我在生产环境里会先标记候选索引观察两周慢日志确认没有相关慢SQL回升之后再真正执行删除。我在实际项目中踩过最深的坑不是“没加索引”而是“加了太多索引”。一张表塞了十几个索引写TPS掉了一大截binlog体积也跟着膨胀最后花两个晚上砍掉没用的读性能没受影响写入才恢复。索引设计的完整链路应该是从慢SQL出发理解B树和回表基于真实查询模型设计联合索引用EXPLAIN验证最后靠监控数据持续清理冗余。这份带源码的总结里的每一个脚本都是我线上还在用的希望你也能少走弯路。 MySQL索引原理与慢SQL优化：从B+树到回表，彻底搞懂索引设计
1. 一次全表扫描引发的线上超时索引到底在救什么火写这篇笔记的时候我正盯着线上的一张订单表发呆。表里只有三百万行不算多但运营后台昨天点一次查询直接等了快两秒。这不是第一次因为少一个索引被人吐槽了。更尴尬的是我第一反应不是去看业务代码而是先上服务器查SQL——因为这种怪问题十有八九出在MySQL索引上。我处理过的故障流程基本是固定的。先执行show processlist看有没有长时间Running的查询再打开慢查询日志设置long_query_time1回放一段时间把嫌疑SQL捞到测试库跑一遍EXPLAIN重点看type、key和rows三列。如果发现typeALL说明MySQL正在做全表扫描那么优化方向就很明确了根据WHERE和ORDER BY条件设计合适的索引然后上线一条CREATE INDEX。很多人有一个误解以为索引只是“加速查询”的优化项实际上它解决的是“减少磁盘I/O次数”这个核心问题。三百万行的表假设每行500字节全量数据大概1.5GB。InnoDB默认16KB一个数据页全表扫描至少碰九万多个页。即使是SSD随机读几万个页也需要好几秒机械硬盘更不用说。而走B树索引从根节点到叶子一般是三层加上回表一次总共也就三四次逻辑I/O。这个差距是数量级的不是百分之几十的提升。1.1 从报警到定位慢SQL的排查路径实际处理的时候我不会上来就说“加索引”而是把SQL、表结构、数据分布放在一起看。比如下面这条从慢查询日志里捞出来的语句SELECT order_id, order_status, created_at FROM orders WHERE user_id 12345 AND order_status IN (1, 2) ORDER BY created_at DESC LIMIT 20;这条SQL的特点是user_id和order_status都是过滤条件created_at还用来排序。最直观的索引设计是建立联合索引(user_id, order_status, created_at)因为user_id等值过滤之后order_status可以继续过滤而created_at本身就处在索引的连续有序区间里排序可以直接借助索引顺序省掉filesort。但是要注意order_status如果只有几个枚举值区分度很低把它放在created_at前面可能并不划算。优化器经过成本估算后索引扫描加回表的开销不一定比全表扫描好。所以我当时最终建的是(user_id, created_at)让created_at用来排序order_status只做过滤效果反而更好。索引设计不是套公式而是看真实数据分布来决定字段先后顺序。1.2 全表扫描的代价磁盘I/O和三层B树的对比要理解为什么索引快得先把“全表扫描为什么慢”想透彻。全表扫描不能利用缓存中的数据页顺序几乎每读一个页都可能触发一次磁盘I/O。假设每秒能做500次随机I/O九万个页就是180秒这还只是读数据没算过滤和网络开销。所以大表直接丢给全表扫描慢是必然的。而B树索引的高度决定了查询需要访问几个页。平时我们用的大多数表索引都能控制在三层以内找到一条记录只需要三四次页访问其中根节点和中间层通常在buffer pool里已经缓存真正落到磁盘的往往只有最后一次读叶子页。这就是索引能把查询从“秒级”降到“毫秒级”的根本原因。2. InnoDB的B树索引结构为什么是这样2.1 哈希、B树和B树的取舍面试时经常被问“为什么MySQL用B树不用哈希索引”。这个问题如果只回答“哈希不支持范围查询”其实没有答到根上。我们换个角度数据库的索引不仅要支持单点查询还要支持排序、范围扫描、前缀匹配这些操作要求索引本身是有序的。哈希结构等值查询确实快但数据存储时是无序散布的范围查询只能全量穷举排序更是无从谈起。B树虽然有序但它有一个问题非叶子节点也会保存完整的数据记录导致每个节点能容纳的索引项变少树就会变高。树越高查询需要的磁盘I/O次数越多。B树则不同所有数据都存放在叶子节点非叶子节点只保存索引键和子页指针所以单个页能容纳的索引项更多树更矮I/O更少。同时B树的叶子节点之间用双向链表串起来范围查询找到起始位置后沿着链表顺序扫就完了这比B树一层层回溯要高效得多。2.2 数据页的物理世界页、页目录与双向链表InnoDB最小的存储单位是页默认16KB。一张表的数据不会零散乱放而是按主键顺序组织在页内。页内部有一个页目录相当于对记录做了一层稀疏索引可以在页内做二分查找定位到具体记录。页和页之间通过双向链表连接形成逻辑上的有序结构。如果按自增主键连续插入新记录基本都追加在末尾写入最顺畅。如果主键是UUID这种随机值新记录可能落在任意页InnoDB需要反复做页分裂和记录移动连带产生大量碎片和随机I/O。这也是生产环境中主张“主键用自增整数”的根本原因不是单纯的洁癖而是关系到底层存储的写入效率。页分裂的过程不复杂但代价不小当新记录要插入的页已经满了InnoDB会申请一个空页并把一半记录搬过去重新调整页目录和链表指针。频繁分裂不仅拖慢写入还会让数据物理分布不再连续后续范围扫描的性能也会受影响。2.3 三层B树能装多少行数据这个数字值得自己算一遍算完就对索引层高有直觉了。假设主键是bigint8字节加上6字节的子页指针每个非叶子节点条目约14字节。16KB的页能放约1170个索引项。叶子页如果一条记录约1KB一个页能放约16行。三层B树大约能支撑1170 * 1170 * 16约两千一百多万行。如果主键换成int索引项更小三层树承载量能到四千万行左右。这个估算有什么实际意义当你预判一张表的数据量会长期保持在几千万行以内时索引基本可以稳定在三层查询延迟非常可控。一旦数据量继续膨胀树增加一层查询就可能多一次离散I/O性能会明显下滑。这时候要思考的不只是索引本身还包括分库分表、归档冷数据等更上层的方案。3. 聚簇索引与二级索引回表问题背后的设计逻辑3.1 聚簇索引叶子节点直接放整行InnoDB和MyISAM最大的区别就是InnoDB的表本身是一棵B树。这张表的聚簇索引按主键构建叶子节点直接存放完整行数据。也就是说主键就是数据的物理组织方式每个表只能有一个聚簇索引。如果你建表时没有定义主键InnoDB会挑第一个非空的唯一索引作为聚簇索引如果连唯一索引都没有它会生成一个隐藏的6字节row_id当主键。了解这个规则很重要因为它意味着“没有主键不代表没有聚簇索引”隐藏主键反而会让索引结构不可控所以建表时应当主动设计主键。通过主键查行是最高效的路径因为从根节点走到叶子拿到的就是整行数据不需要再找其他地方。反过来也成立一旦查询条件不是主键MySQL就需要借助二级索引可能产生回表。3.2 二级索引为什么要存主键而不是地址二级索引是“非主键索引”的统称它的叶子节点并不存行的物理地址而是存主键值。很多刚开始学MySQL的同学不理解这一点为什么不直接存个指针回表多快答案是物理地址不稳定。数据页发生分裂、行记录移动时物理位置会变如果二级索引直接存地址每次移动都要同步更新所有二级索引代价高到无法接受。存主键值的方案更稳妥。主键本身在设计上不会轻易变化行移动时二级索引不需要跟着改。代价就是查询时需要“回表”先用二级索引找到主键再回到聚簇索引里取完整行。如果二级索引命中了上万行最坏情况下就要回表上万次产生大量随机I/O。所以设计索引时要时刻问自己这个查询能不能不回表只要把需要用到的字段都放进索引查询就能用颗覆盖索引解决。覆盖索引的意义不是省一点半点的CPU而是把可能发生的大量回表直接消除掉性能提升往往是数量级的。3.3 覆盖索引与索引下推的收益覆盖索引就是指“查询的所有字段都包含在索引中”比如SELECT order_status FROM orders WHERE user_id12345只要联合索引包含user_id和order_status查询直接在二级索引上就能返回结果不需要回表。与覆盖索引容易混淆的是索引下推Index Condition PushdownICP。在MySQL 5.6之后即使索引不能完全覆盖WHERE条件InnoDB在遍历索引时也可以先对索引中的字段做一次条件判断过滤掉不满足的记录减少回表次数。举个例子联合索引(user_id, created_at)查询里还带了一个order_status条件这个条件虽然不在索引里但遍历索引时已经知道user_id和created_at是否匹配先把不匹配的跳过真正需要回表的记录就少很多。这个特性默认开启大多数情况下能明显降低随机I/O。4. 联合索引的字段顺序最左前缀原则的工程落地4.1 联合索引在B树里的排序规则联合索引不是多个独立索引的简单叠加它本质上还是一棵B树只不过排序规则变成了“先按第一个字段再按第二个字段再按第三个字段”。也就是说索引里的记录在第一列上是全局有序的第二列只在第一列相同的前提下有序第三列又只在第一、第二列都相同时才有序。这个排序规则直接决定了最左前缀原则。如果查询条件里没有索引的第一列那第二列或第三列在整棵索引树上并没有完整的全局顺序优化器很难利用索引的有序性去快速定位自然就退化成扫描整棵索引树甚至全表扫描。很多人把“最左前缀”背成一条规则其实只要理解了排序规则就能自己推导出来B树的有序性是从最左边开始建立的跳过前缀就切断了有序链。4.2 怎么判断一条SQL能不能用联合索引我判断一条SQL能否命中联合索引习惯分三步思考。第一先把SQL里的等值条件摘出来看它们是否从联合索引第一列开始连续覆盖第二遇到第一个范围条件比如、、BETWEEN范围之后的字段顺序就无法继续保证只能作过滤不能用于排序第三看ORDER BY或GROUP BY的字段是否和索引顺序一致一致就能省掉filesort。拿两个例子来说-- 能用到索引前缀 user_id 和 order_status WHERE user_id 1 AND order_status IN (1, 2)IN可以理解成多个等值条件的集合所以它不会阻断后面的字段继续匹配。-- 索引只能用到 user_idcreated_at 无法进一步过滤 WHERE user_id 1 AND created_at 2024-01-01这里created_at一出现范围条件若索引是(user_id, order_status, created_at)则created_at之后的字段都参与不了排序或范围定位但依然可以做索引内过滤能不能优化取决于具体行数。4.3 字段顺序设计等值、范围、排序的三段式设计联合索引时我常用一个“三段式”思路等值条件的字段放最前范围条件的字段放中间排序字段放最后。这样可以让查询在最开始就用最少的行数收敛同时尽量利用索引的有序性。但三段式只是起点不是终点。还要考虑字段的区分度。比如一个status字段只有五个枚举值把它放在索引第一位虽然能命中索引但过滤能力不强后续还要扫很多行。更合理的做法是把user_id这种区分度高的放前面低区分度字段甚至可以不用放进索引避免无端增加存储和写入开销。我在生产里会用真实SQL和数据分布做对比实验建几种联合索引的组合分别EXPLAIN看rows和Extra两列的变化再结合占用空间和写入压力做决定。纸上谈兵式的索引设计很容易在真实流量下翻车。4.4 冗余索引的识别与清理索引建多了最先出现的问题不是查询慢而是写入变慢和存储膨胀。最典型的是冗余索引已有(user_id)索引时再建(user_id, order_status)那么前面的单列索引就没有存在的必要因为它能覆盖的查询联合索引的最左前缀也能覆盖。冗余索引不仅占空间每次插入、更新、删除都要维护多棵B树会让TPS明显下降。我在一次优化中遇到过一张表有十几个索引去掉六个完全冗余的之后写入性能回来了读性能几乎没变化。识别冗余索引的方法可以靠信息模式表查询也可以靠索引使用统计这部分我放在最后源码章节里。5. 索引失效的经典场景与排查链路5.1 隐式类型转换最隐蔽的索引杀手我遇到最多也最难发现的“索引没生效”其实是隐式类型转换。比如表里phone字段是varchar查询却写WHERE phone 13800138000MySQL会把字符串和数字做比较时进行类型转换等于对索引列施加了一个隐式函数B树的有序性自然失效。解决办法看起来很简单执行上却很容易忽略一是保证查询参数类型和字段类型一致二是在应用层做统一参数校验三是字符集也要一致。字符集不同导致的隐式转换更隐蔽比如utf8mb4和latin1关联时也可能让索引失效。所以我会在项目规范里明确要求建表统一字符集接口层统一参数类型。5.2 函数处理列与前置通配符对索引列做函数处理也是导致索引失效的高频原因。最典型的写法是把日期列包在函数里WHERE DATE(create_time) 2024-01-01这样的写法对索引极不友好。改写为范围查询之后create_time没有被函数包裹索引就能正常工作WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00LIKE前导通配符同理。LIKE abc%还能利用索引LIKE %abc%就基本只能全表扫了。如果业务确实需要任意位置的模糊匹配更合理的选择是走全文索引或外部搜索引擎而不是在数据库里硬扛。5.3 优化器成本判断没走索引不等于索引失效有时候SQL写得很规范索引也存在但EXPLAIN仍然显示typeALL。这种时候不要急着骂“索引失效”先想想优化器在做什么。优化器是基于成本选择执行计划的它会估算全表扫描和索引扫描加回表的成本如果某个低区分度字段过滤后还要返回大量行回表成本反而高优化器就会选全表扫描。这种场景常见于小表或数据分布极端的情况。比如一张只有几千行的配置表全表扫描本来就不慢索引的收益体现不出来或者status字段只取两个值查询返回一半以上的数据全表扫描比逐个回表更划算。排查这类问题要看rows和filtered再结合实际耗时判断而不是简单把责任推给“MySQL不用索引”。5.4 看懂EXPLAIN里的关键信号EXPLAIN输出里的关键列不多但每一列都值得盯住。type从ALL、index、range、ref、eq_ref到const是逐渐变好的趋势。key显示实际使用的索引名。rows是优化器预估的扫描行数。Extra里面藏着最值钱的信息。信号含义处理方向ref等值匹配索引正常range索引范围扫描正常但范围条件后的字段无法继续用于排序Using filesort未利用索引排序调整索引顺序尽量覆盖ORDER BYUsing temporary使用了临时表优化GROUP BY、DISTINCT、UNIONUsing index覆盖索引理想状态无需回表Using index condition索引下推5.6特性减少了回表看到Using filesort时我的第一反应是检查ORDER BY字段是否在索引里以及字段顺序是否和联合索引一致。看到Using temporary则要检查是否存在非等值连接或复杂分组这类场景往往涉及隐式排序和临时表是大查询的热门雷区。6. 附源码慢SQL定位与索引维护脚本6.1 慢查询日志配置和打开时机一条慢SQL没抓到之前一切优化都是盲人摸象。先把慢查询日志打开配置如下# /etc/my.cnf slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 0long_query_time不建议一上来就设成0.1不然日志量会暴涨反而干扰判断。生产环境从1秒开始比较稳妥观察一段时间后再根据业务容忍度往下调。6.2 用performance_schema找出TOP慢SQLMySQL 5.7之后不用重启实例也能查历史SQL的聚合统计。用下面这条SQL可以把最消耗时间的SQL按归一化文本排行SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT / 1000000000 AS total_ms, AVG_TIMER_WAIT / 1000000000 AS avg_ms, MAX_TIMER_WAIT / 1000000000 AS max_ms FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME IS NOT NULL ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;如果用的是MySQL 8.0还有更便捷的sys.statement_analysis视图字段更友好SELECT query, exec_count, avg_latency, rows_examined_avg FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 20;拿到DIGEST_TEXT或query之后下一步就是复制到测试库跑EXPLAIN。这套流程我基本每周跑一遍能及时发现新上线的SQL质量问题。6.3 索引使用率统计与冗余索引检测MySQL本身没有直接记录“每个索引被用了多少次”但8.0可以通过IO等待统计看出索引的热度SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR AS io_times FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db ORDER BY COUNT_STAR ASC LIMIT 20;如果某个索引长期排在末尾io_times接近0说明它基本没被使用可以考虑下线。但下线前要在业务低峰期观察一段时间避免因统计窗口短而误判。冗余索引的检测可以用下面这条SQL按同前缀索引做初步筛选SELECT s1.TABLE_SCHEMA, s1.TABLE_NAME, s1.INDEX_NAME AS redundant_index, s2.INDEX_NAME AS needed_index FROM information_schema.statistics s1 JOIN information_schema.statistics s2 ON s1.TABLE_SCHEMA s2.TABLE_SCHEMA AND s1.TABLE_NAME s2.TABLE_NAME AND s1.SEQ_IN_INDEX s2.SEQ_IN_INDEX AND s1.INDEX_NAME ! s2.INDEX_NAME WHERE s1.TABLE_SCHEMA NOT IN (mysql,sys,information_schema,performance_schema) AND s1.SEQ_IN_INDEX (SELECT MAX(s3.SEQ_IN_INDEX) FROM information_schema.statistics s3 WHERE s3.TABLE_SCHEMA s2.TABLE_SCHEMA AND s3.TABLE_NAME s2.TABLE_NAME AND s3.INDEX_NAME s2.INDEX_NAME) GROUP BY s1.TABLE_SCHEMA, s1.TABLE_NAME, s1.INDEX_NAME, s2.INDEX_NAME;这个SQL的逻辑是把索引字段前缀一一比对如果A索引的所有字段都是B索引的前缀A就是冗余候选。实际删索引前一定要人工确认A索引没有被单独用于特殊场景。6.4 大表加索引的实操提醒给大表加索引是个高危操作不能随手对线上库执行。MySQL 8.0支持INPLACE算法很多索引操作不用重建整张表但索引构建仍然会占用大量I/O和锁资源。更稳妥的方式是用gh-ost或pt-osc这类在线DDL工具在后台从库或临时实例完成索引重建再切换流量。另外删除索引也要谨慎。一个索引虽然长期没用但删除后如果某个低频查询突然变慢影响可能比想象中严重。我在生产环境里会先标记候选索引观察两周慢日志确认没有相关慢SQL回升之后再真正执行删除。我在实际项目中踩过最深的坑不是“没加索引”而是“加了太多索引”。一张表塞了十几个索引写TPS掉了一大截binlog体积也跟着膨胀最后花两个晚上砍掉没用的读性能没受影响写入才恢复。索引设计的完整链路应该是从慢SQL出发理解B树和回表基于真实查询模型设计联合索引用EXPLAIN验证最后靠监控数据持续清理冗余。这份带源码的总结里的每一个脚本都是我线上还在用的希望你也能少走弯路。