1. 先从一次慢查询说起索引到底解决了什么问题年初帮朋友公司排查线上问题他们的订单表已经跑到了两百多万行某个后台查询接口每次请求都要 2 到 3 秒页面转圈转到用户直接放弃。我拿到 SQL 看了一眼其实就是一句很常见的WHERE user_id ?但表上除了主键之外一个索引都没建。结果就是每次查询都要把全表扫一遍两百万行数据InnoDB 要一个页一个页地读过去哪怕数据全在内存里光比较这几十万条记录也够呛更别说内存放不下的场景。这就是索引存在的最朴素理由把全表扫描变成极少数次的树查找。没有索引时你要在十万人的名单里找一个叫张三的人只能从头翻到尾有索引之后相当于拿到了一本按姓氏笔画排好的通讯录翻两三次就能定位到目标。MySQL 里的索引就是这本通讯录它牺牲了一部分写入性能和磁盘空间换来了查询时的数量级提速。所以这篇文章不打算只讲概念我会把索引的底层数据结构、InnoDB 的索引组织方式、联合索引的设计原则、EXPLAIN 的读法、常见的索引失效场景以及索引的维护和 MySQL 8.0 的新特性全部串起来每一部分都配上真实例子和我在生产环境里踩过的坑。无论你是刚接触数据库的新人还是已经被慢查询折磨过的老手这篇文章都能帮你把 MySQL 索引这件事彻底理清楚。2. 为什么 MySQL 选择了 B 树数据结构选型的底层逻辑2.1 先看看有哪些候选结构索引本质上是一种数据结构目的就是让查找更快。MySQL 中常见的索引类型有 B 树索引、Hash 索引和全文索引其中 InnoDB 存储引擎默认使用的就是 B 树索引。但为什么偏偏是 B 树我们把候选者们挨个盘一遍就明白了。Hash 索引的查找速度非常快理想情况下 O(1) 就能命中。但它有两个硬伤第一Hash 索引不支持范围查询你没法回答WHERE amount BETWEEN 100 AND 200这种问题因为哈希值的分布是随机的相邻的键在哈希表里可能隔了十万八千里第二Hash 索引不支持部分索引键匹配比如联合索引(user_id, status)你只给user_id去查Hash 索引无法处理因为它的哈希值是基于完整键值计算的。所以 Hash 索引只适合等值查询的场景在 InnoDB 里主要是以自适应哈希索引的形式作为辅助没法当主力。二叉搜索树呢它的查找复杂度是 O(log n)看着还行但极端情况下会退化成链表变成 O(n) 的线性扫描。平衡二叉树如 AVL 树解决了这个问题通过旋转保持树高平衡但每个节点只能存一个键树的高度还是偏高。磁盘 IO 是按页读取的树每深一层就多一次磁盘 IO对于动辄百万行的表AVL 树十几层的高度意味着十几次磁盘 IO性能根本无法接受。B 树是对平衡二叉树的多路改进每个节点可以存储多个键和多个子节点指针。但 B 树的特殊之处在于所有节点都可以存储数据而不只是叶子节点。这意味着查询有可能在非叶子节点就命中数据并返回但同时也带来了一个问题非叶子节点存了数据能容纳的键数量就少了树的高度还是降不下来而且做范围查询时需要在树的中序遍历中来回跳转性能不如想象的那么理想。2.2 B 树到底强在哪里B 树是 B 树的变种它把所有的数据都放到了叶子节点非叶子节点只存键和指针。这样每个非叶子节点能存下更多的键树变得更矮更宽。InnoDB 中一个数据页默认是 16KB假设主键是 BIGINT8 字节加一个指向子节点的指针约 6 字节每个索引项大概 14 字节。算一下16 * 1024 / 14 ≈ 1170也就是说一个非叶子节点能存下约 1170 个键。如果你的表里每行数据大约 1KB那么一个叶子页能存约 16 行数据。三层 B 树能存多少行1170 * 1170 * 16 ≈ 2190 万行。这意味着对于两千万行以内的表只需要三次磁盘 IO 就能从根节点走到叶子数据页同时根节点通常常驻内存实际上真正需要的磁盘 IO 只有两次。这个数据量下无论你查哪一行代价都很稳定这正是数据库想要的。B 树的另一个核心优势是叶子节点之间通过指针串联成了一个有序链表这让范围查询变得极其顺滑。比如WHERE id 100 AND id 200你只需要从索引中找到id 100的位置然后顺着叶子节点的链表一路往后扫到 200 就行不需要反复回溯树结构。InnoDB 的叶子节点双向链表设计对排序和范围扫描都是天然支持的。2.3 页分裂与碎片理解 B 树的代价B 树的写入不是没有成本的。向一个已经满的叶子页插入新数据时InnoDB 必须把这一页分裂成两页把一部分数据移动到新页同时更新父节点的指针。频繁的插入操作会让树不停地分裂产生大量碎片。如果主键是随机生成的 UUID新数据可能落在任意位置触发大规模页分裂写入性能和磁盘空间利用率都会显著下降。这也是为什么我一直强调主键尽量用自增整数别用 UUID。后面第三章会展开讲这个问题。3. 聚簇索引与二级索引搞清楚 InnoDB 表真正的存储方式3.1 一张表只能有一个聚簇索引很多人以为主键索引和普通索引只是名字不一样底层结构都一样这是个挺常见的误解。在 InnoDB 里主键索引是聚簇索引它决定了整张表数据的物理排列顺序。聚簇索引的叶子节点存的不是索引键而是完整的行数据。也就是说表数据本身就是按主键顺序存放的主键索引就是表本身。正因如此一张表只能有一个聚簇索引。如果你建表时没有显式指定主键InnoDB 会先找第一个非空的唯一索引作为聚簇索引如果也没有它会在内部生成一个隐藏的 rowid 作为聚簇索引。这带来的影响是即使你不建主键InnoDB 也会替你维护一个隐式主键与其这样不如老老实实自己建一个。聚簇索引的查询效率极高因为从根节点到叶子节点一次走到底拿到索引键的同时就能拿到整行数据不需要额外的操作。但它的弱点也很明显如果主键不是有序递增的插入新行时可能触发页分裂导致数据页出现空洞和碎片拉低写入性能和磁盘利用率。3.2 二级索引与回表除了聚簇索引其他索引统称为二级索引也叫辅助索引。二级索引的叶子节点存储的不是完整行数据而是当前索引键 主键值。比如你给user_id建了索引那么这棵 B 树的叶子节点里存的是(user_id, id)。当你执行SELECT * FROM orders WHERE user_id 1001时MySQL 会先在二级索引上找到主键 id然后再拿着这个 id 去聚簇索引里找完整行数据这个过程叫做回表。回表意味着多一次 B 树查找是有额外代价的。如果查询返回的行数很多比如user_id 1001命中了 500 行那就要回表 500 次。在极端情况下扫描二级索引加回表的成本可能比全表扫描还高优化器会评估之后决定是否走索引。这就是为什么有时候你明明建了索引EXPLAIN 结果却显示type ALL。避免回表最直接的办法就是覆盖索引。如果查询所需的列全部在索引中MySQL 就不需要回表直接扫描索引就能返回结果EXPLAIN 的 Extra 字段会显示Using index。比如我有索引(user_id, status)执行SELECT user_id, status FROM orders WHERE user_id 1001这两个字段都在索引里就不用回表。但如果你把查询改成SELECT *那必然要回表拿其他列。所以在设计索引时要尽量考虑让查询的字段落在索引里。3.3 为什么主键应该用自增整数而不是 UUID这个问题我在第三章已经提到了这里展开说透。自增主键插入时新记录总是追加在 B 树的最右边叶子页满了就新开一页很少触发页分裂页的利用率高写入性能稳定。UUID 作为主键时因为值是随机的新记录会插入到树的中间位置大概率触发页分裂、数据移动和碎片产生。我在生产环境见过一个极端案例同一张表UUID 主键的写入性能比自增主键低了 40%表空间也大了将近一倍。另一个选择主键的考量是二级索引的存储开销。前面说了二级索引叶子节点要存主键值如果主键是 UUID16 字节那每个二级索引条目都要多占用 8 字节左右的存储空间。表上每多一个二级索引这个开销就翻一倍。所以主键越短整个表的索引体积就越小缓冲池能放下的索引页就越多查询效率也就越高。主键建议用 BIGINT 自增或者用一个有意义的长整型编号千万不要用随机字符串。4. 联合索引最左前缀法则与多列查询的最佳实践4.1 联合索引的排序原理联合索引也叫复合索引是把多个列合并到一棵 B 树里。它的排序规则是先按第一个列排序第一列相同的记录再按第二列排序以此类推。你可以把联合索引想成一本电话簿先按姓氏排序姓氏相同再按名字排序。这种排序方式决定了联合索引的适用范围也就是最左前缀法则。假设我建了一个索引idx_user_status (user_id, status)那么这棵 B 树的键顺序是这样的同一个 user_id 的所有记录会排在一起在这些记录内部再按 status 排序。它能支撑以下查询场景WHERE user_id 1001直接用 user_id 命中索引没问题。WHERE user_id 1001 AND status 1先按 user_id 定位再按 status 精确定位完全命中索引。WHERE user_id 1001 ORDER BY status索引本身已经按 status 排好序了直接顺序读取就行不需要额外排序。但如果查询是WHERE status 1情况就不同了。因为索引最左侧的列是 user_id而你没给它条件MySQL 没办法在一棵按 user_id 优先排序的树里快速定位 status只能跳过这个索引做全表扫描。这就叫没有满足最左前缀。所以设计联合索引时要根据查询模式把最常用的等值条件列放在最前面。4.2 联合索引的列顺序与一个典型调优案例联合索引的列顺序核心原则是等值条件优先放前面然后再放排序字段和范围字段。原因是等值条件能精确定位到一段连续空间范围条件只能缩小范围放后面的价值更大。我调过一个比较典型的接口订单列表页需要按某个商家的订单状态筛选并按创建时间倒序排。原 SQL 大致是SELECT * FROM orders WHERE merchant_id 1002 AND status 3 ORDER BY created_at DESC LIMIT 20;表上有两个独立索引idx_merchant_id和idx_statusMySQL 优化器最终选择了走idx_merchant_id再用filesort进行排序。数据量一上来排序开销非常明显。我把它改成了一个联合索引ALTER TABLE orders ADD INDEX idx_merchant_status_time (merchant_id, status, created_at DESC);因为merchant_id和status都是等值条件放在前面created_at是排序字段放在最后并且 MySQL 8.0 支持降序索引直接声明DESC。改完之后 EXPLAIN 的 Extra 字段从Using filesort变成了空排序不再需要额外做查询从 300 多毫秒降到了 10 毫秒以内。这里要提一个细节如果联合索引里有范围条件比如WHERE user_id 1001 AND created_at 2024-01-01 AND status 1即使索引是(user_id, created_at, status)status也用不上索引因为created_at的范围条件会中断索引的等值匹配。遇到这种情况要么把 status 放到 created_at 前面要么考虑用其他方式改写 SQL。这就是为什么说联合索引列顺序的设计要结合具体查询模式没有一个万能公式但原则就是等值列放前范围列放后排序列放最后。4.3 索引下推MySQL 5.6 以来的一个重要优化既然聊到联合索引就不得不提索引下推Index Condition PushdownICP。在 MySQL 5.6 之前如果索引中包含的列不能完全过滤条件MySQL 会先把索引命中的数据全部查出来再回表取完整行后做进一步过滤。有了 ICP 之后MySQL 可以在索引层面直接过滤掉不符合条件的记录减少回表次数。举个例子索引是(user_id, status)查询是WHERE user_id 1001 AND status 1。如果没有 ICPMySQL 会先根据 user_id 找到所有符合的记录然后每个记录都回表再检查 status。有了 ICP因为 status 也在索引里MySQL 在扫描索引时就判断了 status 条件只有符合条件的记录才回表。差异在数据量大时特别明显。这个特性默认是开启的但如果你在 WHERE 里对索引列使用了函数或表达式ICP 也救不了你因为它只能处理能直接比较的列。5. EXPLAIN 读心术五分钟学会判断索引是否生效5.1 先跑一条典型的 EXPLAINEXPLAIN 是 MySQL 提供的查询执行计划展示工具判断索引是否生效、查询性能是否有问题第一件事就是跑它。下面是一条真实的生产 SQL 和它的执行计划EXPLAIN SELECT id, order_no, amount FROM orders WHERE user_id 1001 AND status 3 ORDER BY created_at DESC LIMIT 20\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders partitions: NULL type: ref possible_keys: idx_user_id, idx_user_status key: idx_user_status key_len: 9 ref: const, const rows: 12 filtered: 100.00 Extra: NULL里面几个关键字段我一个个说。type表示访问类型从好到差大致是system const eq_ref ref range index ALL。ref说明是等值匹配效率不错ALL就是全表扫描基本宣告你的索引白建了。key显示实际用到的索引possible_keys是 MySQL 认为可能用到的索引列表。rows是预估扫描的行数越小越好。key_len表示索引使用的字节数后面会详细讲。Extra这个字段信息量很大比如出现Using filesort说明排序没有走索引出现Using temporary说明用了临时表出现Using index说明覆盖索引生效了。5.2 用 key_len 判断联合索引到底用了几个字段很多人看 EXPLAIN 只看key是不是有值其实key_len才是判断联合索引实际用了几列的利器。还是拿idx_user_status (user_id, status)举例user_id 是 BIGINT占用 8 字节status 是 TINYINT在 MySQL 中占用 1 字节。上面 EXPLAIN 的结果里key_len 9正好是 8 1说明两个列都用上了。如果只用了 user_idkey_len应该只有 8。之前我看到一个执行计划key idx_user_status但key_len 8再一查 SQLwhere 条件里只有 user_idstatus 被丢到了后面的显式过滤里联合索引的第二列完全没被利用。这种情况通过 key_len 一眼就能发现。这个字段再稍微复杂一点如果列允许为 NULL要在基础上加 1 字节如果是 varchar 类型需要额外加上变长长度的 2 字节utf8mb4 字符集下每个字符占 4 字节。所以 varchar(32) 的可空字段key_len 上限是32 * 4 2 1 131。看到超过 130 的 key_len基本可以判断索引退化成了长字段匹配选择性可能不会太好。5.3 一个让我印象深刻的预判失误有一次我优化一个查询表上有索引(province, city, area)SQL 条件是WHERE city 杭州 AND area 西湖区我第一反应是你不用 province联合索引最左前缀肯定失效了结果 EXPLAIN 一跑key idx_province_city_areatype ref它竟然用了这个索引。后来翻源码和文档才知道MySQL 8.0 的优化器支持跳过联合索引的前导列进行匹配只要前导列的可区分度足够低或者统计信息显示这样查找有收益它就会尝试。这个特性在 MySQL 的skip_scan优化中实现。所以不要死记最左前缀失效这个结论要结合 EXPLAIN 实际验证。当然这个特性对city列的可区分度有要求如果city列的基数很低优化器反而不会走 skip scan。我的建议是设计索引时按最左前缀原则来但查询之前一定要用 EXPLAIN 验证真实执行计划别凭感觉下结论。6. 索引失效的几大场景生产环境踩坑实录6.1 对索引列使用函数或表达式这是最常见也最容易踩的坑。有人写WHERE DATE(created_at) 2024-06-01以为 created_at 上有索引就能加速。实际上MySQL 无法直接使用DATE()函数处理后的结果去匹配索引树它会先把所有行的 created_at 都算一遍 DATE()再做比较直接导致全表扫描。解决方法有两种一是把 SQL 改写成范围查询WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00二是在 MySQL 8.0 里建函数索引ALTER TABLE orders ADD INDEX idx_created_date ((DATE(created_at)));8.0 支持的函数索引会让优化器在匹配函数调用时自动走索引。我在生产环境把几个日期查询都改成了这种方式效果立竿见影。6.2 隐式类型转换最隐蔽的索引杀手隐式类型转换是我见过最隐蔽、最坑的失效场景。比如一个手机号字段用的是 varchar 类型查询却写了WHERE mobile 13800001111数字字面量。MySQL 会把索引列从字符串转成数字去比较这相当于对列施了隐式函数索引直接失效。你在 EXPLAIN 里会看到 type 从ref变成了ALL。关键是这种 SQL 在数据量小的时候根本感觉不出来等表涨到几百万行就突然卡死。排查技巧是如果 EXPLAIN 里type栏的等级莫名退化检查一下字段类型和查询值的类型是否一致。尽量保持 SQL 的查询参数类型和列类型完全一致。还有一种隐式类型转换发生在字符集不一致的情况下比如 utf8mb4 字段和 utf8 字段做关联查询也可能导致索引失效。6.3 前导通配符 LIKEWHERE title LIKE %MySQL%这种写法因为最前面的%使得索引树不知道从哪里开始匹配只能做全表扫描。但如果写成WHERE title LIKE MySQL%因为前缀已知索引是可以正常使用的。需要做真正的前后模糊匹配时与其用 LIKE不如考虑全文索引或者搜索引擎。我在项目里见过一个坑搜索功能一开始用 LIKE %xxx%数据量小没人在意后来表涨到百万行搜索接口直接超时。换成全文索引后问题立刻缓解。6.4 联合索引不满足最左前缀这个在前面已经讲过原理这里只说一个容易混淆的细节。联合索引(a, b, c)查询WHERE a 1 AND c 3a 能命中索引但 c 不行因为中间的 b 被跳过了。而WHERE b 1 AND a 2如果 MySQL 的优化器能识别出a和b的查询条件顺序不影响结果它会把a条件提前让索引生效。所以最左前缀失效并不绝对还是要以 EXPLAIN 的 key_len 为准。6.5 OR 条件的连锁反应WHERE user_id 1001 OR status 3如果两个列都有独立索引优化器可能会尝试用 index_merge 合并两条索引的结果但更多情况下只要其中一个条件无法走索引MySQL 就会退化成全表扫描。尤其是当 OR 两边的列都没有索引时基本可以确定扫全表。我的处理思路是把 OR 改写成 UNION 两个查询或者确保两边条件都能独立使用索引。具体改写要看执行计划不能无脑套。6.6 不等于、NOT IN 与大范围查询WHERE status ! 0或者WHERE id NOT IN (...),这类条件一般没法走索引因为 B 树索引是用来精确定位和有序扫描的不等于表示排除某个值排除对索引来说并不是一个天然的搜索路径。例外情况是如果这个不等于的字段区分度极高比如WHERE name ! admin且 name 的基数值非常大优化器评估后可能还是会走索引扫描。但总的来说这类查询需要谨慎。还有一个更常见的误区即使走了索引如果范围太大比如WHERE status 1命中了表中 80% 的数据优化器也会认为全表扫描比走索引更划算因为回表次数太多了。这不是索引失效而是优化器做出了成本更低的决策。遇到这种情况如果业务确实需要频繁执行这种大范围查询可以考虑覆盖索引来降低回表成本。6.7 统计信息过期导致的错误执行计划InnoDB 的优化器是依赖统计信息做决策的。如果表的统计信息长期不更新优化器可能高估或低估某个索引的选择性从而选错执行计划。这种问题排查起来特别头疼因为 SQL 和索引都没问题但查询就是慢。解决办法很直接重新分析表更新统计信息。ANALYZE TABLE orders;我在生产环境处理过一次这样的问题一个查询平时都是毫秒级某天突然变成几百毫秒EXPLAIN 看执行计划发现走了另一个选择性很差的索引。跑完ANALYZE TABLE之后执行计划恢复正常。这种坑在数据发生剧烈变化时容易出现比如一次性导入大量数据、批量删除数据后务必记得执行 ANALYZE。7. 索引创建与维护SQL 语法、碎片整理与 8.0 新特性7.1 常见索引操作语法索引的创建方式有几种下面是最常用的-- 建表时直接指定 CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_user_status (user_id, status) ) ENGINEInnoDB; -- 表已存在时追加 CREATE INDEX idx_user_status ON orders (user_id, status); ALTER TABLE orders ADD INDEX idx_user_status (user_id, status); -- 唯一索引 ALTER TABLE orders ADD UNIQUE KEY uk_order_no (order_no); -- 删除索引 DROP INDEX idx_user_status ON orders; ALTER TABLE orders DROP INDEX idx_user_status; -- 查看索引 SHOW INDEX FROM orders;SHOW INDEX FROM返回结果中有一个Cardinality字段代表索引的基数估测值。Cardinality越接近表的行数说明索引的选择性越高优化器更倾向于使用它。如果这个值和实际行数差距很大说明统计信息可能过期了这时候可以跑一遍ANALYZE TABLE刷新。7.2 索引不是越多越好写入放大的代价每新建一个索引等于多维护一棵 B 树。执行 INSERT、UPDATE、DELETE 时除了更新聚簇索引所有相关的二级索引也要同步更新。如果表上有 5 个二级索引一次插入就要写 6 棵树写入放大非常明显。我曾经遇到一个案例某张表为了满足各类查询场景建了 11 个索引写入吞吐率掉了将近一半。后来按实际的查询频率清理掉 4 个冗余索引写入性能立刻恢复正常。判断索引是否冗余的方法比较直接如果一个索引的高位列是另一个索引的前缀那它大概率是冗余的。比如已经有(user_id, status)索引又单独建了(user_id)索引后者就是冗余的。除非user_id列单独使用的频率极其高且二级索引体积差异带来明显的查询收益否则建议合并成一个联合索引。7.3 索引碎片与整理索引页在长期的增删改过程中会产生碎片页的数据利用率变低扫描索引时需要读取更多的页查询效率会下降。碎片化严重时可以通过重建表来整理ALTER TABLE orders ENGINEInnoDB;这个操作会重建整张表并重组索引能有效消除碎片。在 MySQL 5.6 之后这个操作支持在线 DDL也就是说执行期间不会阻塞读写但要注意它仍然会消耗大量 IO 和 CPU最好在业务低峰期执行。另外频繁删除和插入大量数据后记得分析表并重建索引可以避免空间浪费和查询性能恶化。7.4 MySQL 8.0 索引相关新特性MySQL 8.0 引入了几个对索引使用有直接影响的特性值得关注。第一个是降序索引可以在创建索引时指定某个列的排序方向比如KEY idx_user_status_time (user_id, status, created_at DESC)字段的排序方向会存储到索引的结构中优化器在进行对应的倒序排序时可以直接利用索引而不用做反向扫描。第二个是不可见索引你可以把一个索引标记为不可见优化器就不会用它这对于测试删除索引是否影响线上查询非常有帮助ALTER TABLE orders ALTER INDEX idx_user_id INVISIBLE; ALTER TABLE orders ALTER INDEX idx_user_id VISIBLE;第三个是函数索引前面已经提到过它对WHERE DATE(created_at) ...这类查询是刚需。8.0 还有一个细节默认的临时表或排序操作如果涉及 LONGTEXT、TEXT 和 BLOB 列建索引时必须指定前缀长度比如KEY idx_content (content(32))否则会报错这在归档表设计时经常会碰到。8. 一个完整的索引设计实战从慢 SQL 到性能达标前面把理论和工具都过了一遍最后用一个完整的实战案例把整个流程串起来。这是一个类似订单管理的后台系统业务上线半年后某几个核心查询越来越慢。我接到工单后第一件事就是打开慢查询日志把 TOP 慢 SQL 捞出来。其中最有代表性的两条是-- SQL 1按用户查最近订单 SELECT * FROM orders WHERE user_id 567888 ORDER BY id DESC LIMIT 20; -- SQL 2按状态和日期区间统计 SELECT COUNT(*), status FROM orders WHERE status IN (1, 2) AND created_at BETWEEN 2024-05-01 AND 2024-05-31 GROUP BY status;先看 SQL 1发现表上虽然有idx_user_id索引但ORDER BY id DESC需要回表后再排序产生Using filesort。优化方案说是把user_id和id做成联合索引(user_id, id DESC)这样查询到 user_id 的记录时叶子节点已经按 id 倒序排好了直接取前 20 条就行连回表和排序都省了。改造后这条 SQL 从原来的 120 多毫秒降到了 3 毫秒。再看 SQL 2这是一个统计类查询条件里 status 是等值集合created_at 是范围。我建了一个联合索引(status, created_at)因为 status 是分组字段也相当于等值匹配放前面created_at 放后面做范围过滤。另外这个查询只涉及 status 和 COUNT(*)如果 status 和 created_at 都在索引里那这个索引本身就是覆盖索引不需要回表去取其他列。改造后合并 EXPLAINExtra 字段直接显示Using index没有再出现Using temporary。这个案例想说明的核心观点是索引设计必须从 SQL 出发而不是从列出发。不要先把所有字段都加上索引再看哪些能用应该先收集慢查询日志分析高频率查询的模式然后针对性地设计联合索引。每一次索引的增删都应该以 EXPLAIN 的执行计划为依据同时结合写入频率做好权衡。另外一个提醒上线新索引后要持续观察一段时间。我见过有人加完索引后立即觉得快了很多但忽略了它对写入带来的影响等到业务高峰期写入量暴涨才发现问题。稳妥的做法是新索引上线选低峰期先观察一两天用性能监控对比前后写入延迟和锁等待确认没有副作用之后再长期保留。MySQL 索引这块理论集中在一棵树实践散落在每一行 SQL 和每一个执行计划里。文章写到这儿核心内容已经全部覆盖了。如果你看完之后能对自己线上表里的索引做一次系统的梳理用 EXPLAIN 验证几条曾经觉得有问题的查询那这篇文章的价值就不算白写。 MySQL索引优化：从B+树原理到EXPLAIN实战
1. 先从一次慢查询说起索引到底解决了什么问题年初帮朋友公司排查线上问题他们的订单表已经跑到了两百多万行某个后台查询接口每次请求都要 2 到 3 秒页面转圈转到用户直接放弃。我拿到 SQL 看了一眼其实就是一句很常见的WHERE user_id ?但表上除了主键之外一个索引都没建。结果就是每次查询都要把全表扫一遍两百万行数据InnoDB 要一个页一个页地读过去哪怕数据全在内存里光比较这几十万条记录也够呛更别说内存放不下的场景。这就是索引存在的最朴素理由把全表扫描变成极少数次的树查找。没有索引时你要在十万人的名单里找一个叫张三的人只能从头翻到尾有索引之后相当于拿到了一本按姓氏笔画排好的通讯录翻两三次就能定位到目标。MySQL 里的索引就是这本通讯录它牺牲了一部分写入性能和磁盘空间换来了查询时的数量级提速。所以这篇文章不打算只讲概念我会把索引的底层数据结构、InnoDB 的索引组织方式、联合索引的设计原则、EXPLAIN 的读法、常见的索引失效场景以及索引的维护和 MySQL 8.0 的新特性全部串起来每一部分都配上真实例子和我在生产环境里踩过的坑。无论你是刚接触数据库的新人还是已经被慢查询折磨过的老手这篇文章都能帮你把 MySQL 索引这件事彻底理清楚。2. 为什么 MySQL 选择了 B 树数据结构选型的底层逻辑2.1 先看看有哪些候选结构索引本质上是一种数据结构目的就是让查找更快。MySQL 中常见的索引类型有 B 树索引、Hash 索引和全文索引其中 InnoDB 存储引擎默认使用的就是 B 树索引。但为什么偏偏是 B 树我们把候选者们挨个盘一遍就明白了。Hash 索引的查找速度非常快理想情况下 O(1) 就能命中。但它有两个硬伤第一Hash 索引不支持范围查询你没法回答WHERE amount BETWEEN 100 AND 200这种问题因为哈希值的分布是随机的相邻的键在哈希表里可能隔了十万八千里第二Hash 索引不支持部分索引键匹配比如联合索引(user_id, status)你只给user_id去查Hash 索引无法处理因为它的哈希值是基于完整键值计算的。所以 Hash 索引只适合等值查询的场景在 InnoDB 里主要是以自适应哈希索引的形式作为辅助没法当主力。二叉搜索树呢它的查找复杂度是 O(log n)看着还行但极端情况下会退化成链表变成 O(n) 的线性扫描。平衡二叉树如 AVL 树解决了这个问题通过旋转保持树高平衡但每个节点只能存一个键树的高度还是偏高。磁盘 IO 是按页读取的树每深一层就多一次磁盘 IO对于动辄百万行的表AVL 树十几层的高度意味着十几次磁盘 IO性能根本无法接受。B 树是对平衡二叉树的多路改进每个节点可以存储多个键和多个子节点指针。但 B 树的特殊之处在于所有节点都可以存储数据而不只是叶子节点。这意味着查询有可能在非叶子节点就命中数据并返回但同时也带来了一个问题非叶子节点存了数据能容纳的键数量就少了树的高度还是降不下来而且做范围查询时需要在树的中序遍历中来回跳转性能不如想象的那么理想。2.2 B 树到底强在哪里B 树是 B 树的变种它把所有的数据都放到了叶子节点非叶子节点只存键和指针。这样每个非叶子节点能存下更多的键树变得更矮更宽。InnoDB 中一个数据页默认是 16KB假设主键是 BIGINT8 字节加一个指向子节点的指针约 6 字节每个索引项大概 14 字节。算一下16 * 1024 / 14 ≈ 1170也就是说一个非叶子节点能存下约 1170 个键。如果你的表里每行数据大约 1KB那么一个叶子页能存约 16 行数据。三层 B 树能存多少行1170 * 1170 * 16 ≈ 2190 万行。这意味着对于两千万行以内的表只需要三次磁盘 IO 就能从根节点走到叶子数据页同时根节点通常常驻内存实际上真正需要的磁盘 IO 只有两次。这个数据量下无论你查哪一行代价都很稳定这正是数据库想要的。B 树的另一个核心优势是叶子节点之间通过指针串联成了一个有序链表这让范围查询变得极其顺滑。比如WHERE id 100 AND id 200你只需要从索引中找到id 100的位置然后顺着叶子节点的链表一路往后扫到 200 就行不需要反复回溯树结构。InnoDB 的叶子节点双向链表设计对排序和范围扫描都是天然支持的。2.3 页分裂与碎片理解 B 树的代价B 树的写入不是没有成本的。向一个已经满的叶子页插入新数据时InnoDB 必须把这一页分裂成两页把一部分数据移动到新页同时更新父节点的指针。频繁的插入操作会让树不停地分裂产生大量碎片。如果主键是随机生成的 UUID新数据可能落在任意位置触发大规模页分裂写入性能和磁盘空间利用率都会显著下降。这也是为什么我一直强调主键尽量用自增整数别用 UUID。后面第三章会展开讲这个问题。3. 聚簇索引与二级索引搞清楚 InnoDB 表真正的存储方式3.1 一张表只能有一个聚簇索引很多人以为主键索引和普通索引只是名字不一样底层结构都一样这是个挺常见的误解。在 InnoDB 里主键索引是聚簇索引它决定了整张表数据的物理排列顺序。聚簇索引的叶子节点存的不是索引键而是完整的行数据。也就是说表数据本身就是按主键顺序存放的主键索引就是表本身。正因如此一张表只能有一个聚簇索引。如果你建表时没有显式指定主键InnoDB 会先找第一个非空的唯一索引作为聚簇索引如果也没有它会在内部生成一个隐藏的 rowid 作为聚簇索引。这带来的影响是即使你不建主键InnoDB 也会替你维护一个隐式主键与其这样不如老老实实自己建一个。聚簇索引的查询效率极高因为从根节点到叶子节点一次走到底拿到索引键的同时就能拿到整行数据不需要额外的操作。但它的弱点也很明显如果主键不是有序递增的插入新行时可能触发页分裂导致数据页出现空洞和碎片拉低写入性能和磁盘利用率。3.2 二级索引与回表除了聚簇索引其他索引统称为二级索引也叫辅助索引。二级索引的叶子节点存储的不是完整行数据而是当前索引键 主键值。比如你给user_id建了索引那么这棵 B 树的叶子节点里存的是(user_id, id)。当你执行SELECT * FROM orders WHERE user_id 1001时MySQL 会先在二级索引上找到主键 id然后再拿着这个 id 去聚簇索引里找完整行数据这个过程叫做回表。回表意味着多一次 B 树查找是有额外代价的。如果查询返回的行数很多比如user_id 1001命中了 500 行那就要回表 500 次。在极端情况下扫描二级索引加回表的成本可能比全表扫描还高优化器会评估之后决定是否走索引。这就是为什么有时候你明明建了索引EXPLAIN 结果却显示type ALL。避免回表最直接的办法就是覆盖索引。如果查询所需的列全部在索引中MySQL 就不需要回表直接扫描索引就能返回结果EXPLAIN 的 Extra 字段会显示Using index。比如我有索引(user_id, status)执行SELECT user_id, status FROM orders WHERE user_id 1001这两个字段都在索引里就不用回表。但如果你把查询改成SELECT *那必然要回表拿其他列。所以在设计索引时要尽量考虑让查询的字段落在索引里。3.3 为什么主键应该用自增整数而不是 UUID这个问题我在第三章已经提到了这里展开说透。自增主键插入时新记录总是追加在 B 树的最右边叶子页满了就新开一页很少触发页分裂页的利用率高写入性能稳定。UUID 作为主键时因为值是随机的新记录会插入到树的中间位置大概率触发页分裂、数据移动和碎片产生。我在生产环境见过一个极端案例同一张表UUID 主键的写入性能比自增主键低了 40%表空间也大了将近一倍。另一个选择主键的考量是二级索引的存储开销。前面说了二级索引叶子节点要存主键值如果主键是 UUID16 字节那每个二级索引条目都要多占用 8 字节左右的存储空间。表上每多一个二级索引这个开销就翻一倍。所以主键越短整个表的索引体积就越小缓冲池能放下的索引页就越多查询效率也就越高。主键建议用 BIGINT 自增或者用一个有意义的长整型编号千万不要用随机字符串。4. 联合索引最左前缀法则与多列查询的最佳实践4.1 联合索引的排序原理联合索引也叫复合索引是把多个列合并到一棵 B 树里。它的排序规则是先按第一个列排序第一列相同的记录再按第二列排序以此类推。你可以把联合索引想成一本电话簿先按姓氏排序姓氏相同再按名字排序。这种排序方式决定了联合索引的适用范围也就是最左前缀法则。假设我建了一个索引idx_user_status (user_id, status)那么这棵 B 树的键顺序是这样的同一个 user_id 的所有记录会排在一起在这些记录内部再按 status 排序。它能支撑以下查询场景WHERE user_id 1001直接用 user_id 命中索引没问题。WHERE user_id 1001 AND status 1先按 user_id 定位再按 status 精确定位完全命中索引。WHERE user_id 1001 ORDER BY status索引本身已经按 status 排好序了直接顺序读取就行不需要额外排序。但如果查询是WHERE status 1情况就不同了。因为索引最左侧的列是 user_id而你没给它条件MySQL 没办法在一棵按 user_id 优先排序的树里快速定位 status只能跳过这个索引做全表扫描。这就叫没有满足最左前缀。所以设计联合索引时要根据查询模式把最常用的等值条件列放在最前面。4.2 联合索引的列顺序与一个典型调优案例联合索引的列顺序核心原则是等值条件优先放前面然后再放排序字段和范围字段。原因是等值条件能精确定位到一段连续空间范围条件只能缩小范围放后面的价值更大。我调过一个比较典型的接口订单列表页需要按某个商家的订单状态筛选并按创建时间倒序排。原 SQL 大致是SELECT * FROM orders WHERE merchant_id 1002 AND status 3 ORDER BY created_at DESC LIMIT 20;表上有两个独立索引idx_merchant_id和idx_statusMySQL 优化器最终选择了走idx_merchant_id再用filesort进行排序。数据量一上来排序开销非常明显。我把它改成了一个联合索引ALTER TABLE orders ADD INDEX idx_merchant_status_time (merchant_id, status, created_at DESC);因为merchant_id和status都是等值条件放在前面created_at是排序字段放在最后并且 MySQL 8.0 支持降序索引直接声明DESC。改完之后 EXPLAIN 的 Extra 字段从Using filesort变成了空排序不再需要额外做查询从 300 多毫秒降到了 10 毫秒以内。这里要提一个细节如果联合索引里有范围条件比如WHERE user_id 1001 AND created_at 2024-01-01 AND status 1即使索引是(user_id, created_at, status)status也用不上索引因为created_at的范围条件会中断索引的等值匹配。遇到这种情况要么把 status 放到 created_at 前面要么考虑用其他方式改写 SQL。这就是为什么说联合索引列顺序的设计要结合具体查询模式没有一个万能公式但原则就是等值列放前范围列放后排序列放最后。4.3 索引下推MySQL 5.6 以来的一个重要优化既然聊到联合索引就不得不提索引下推Index Condition PushdownICP。在 MySQL 5.6 之前如果索引中包含的列不能完全过滤条件MySQL 会先把索引命中的数据全部查出来再回表取完整行后做进一步过滤。有了 ICP 之后MySQL 可以在索引层面直接过滤掉不符合条件的记录减少回表次数。举个例子索引是(user_id, status)查询是WHERE user_id 1001 AND status 1。如果没有 ICPMySQL 会先根据 user_id 找到所有符合的记录然后每个记录都回表再检查 status。有了 ICP因为 status 也在索引里MySQL 在扫描索引时就判断了 status 条件只有符合条件的记录才回表。差异在数据量大时特别明显。这个特性默认是开启的但如果你在 WHERE 里对索引列使用了函数或表达式ICP 也救不了你因为它只能处理能直接比较的列。5. EXPLAIN 读心术五分钟学会判断索引是否生效5.1 先跑一条典型的 EXPLAINEXPLAIN 是 MySQL 提供的查询执行计划展示工具判断索引是否生效、查询性能是否有问题第一件事就是跑它。下面是一条真实的生产 SQL 和它的执行计划EXPLAIN SELECT id, order_no, amount FROM orders WHERE user_id 1001 AND status 3 ORDER BY created_at DESC LIMIT 20\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders partitions: NULL type: ref possible_keys: idx_user_id, idx_user_status key: idx_user_status key_len: 9 ref: const, const rows: 12 filtered: 100.00 Extra: NULL里面几个关键字段我一个个说。type表示访问类型从好到差大致是system const eq_ref ref range index ALL。ref说明是等值匹配效率不错ALL就是全表扫描基本宣告你的索引白建了。key显示实际用到的索引possible_keys是 MySQL 认为可能用到的索引列表。rows是预估扫描的行数越小越好。key_len表示索引使用的字节数后面会详细讲。Extra这个字段信息量很大比如出现Using filesort说明排序没有走索引出现Using temporary说明用了临时表出现Using index说明覆盖索引生效了。5.2 用 key_len 判断联合索引到底用了几个字段很多人看 EXPLAIN 只看key是不是有值其实key_len才是判断联合索引实际用了几列的利器。还是拿idx_user_status (user_id, status)举例user_id 是 BIGINT占用 8 字节status 是 TINYINT在 MySQL 中占用 1 字节。上面 EXPLAIN 的结果里key_len 9正好是 8 1说明两个列都用上了。如果只用了 user_idkey_len应该只有 8。之前我看到一个执行计划key idx_user_status但key_len 8再一查 SQLwhere 条件里只有 user_idstatus 被丢到了后面的显式过滤里联合索引的第二列完全没被利用。这种情况通过 key_len 一眼就能发现。这个字段再稍微复杂一点如果列允许为 NULL要在基础上加 1 字节如果是 varchar 类型需要额外加上变长长度的 2 字节utf8mb4 字符集下每个字符占 4 字节。所以 varchar(32) 的可空字段key_len 上限是32 * 4 2 1 131。看到超过 130 的 key_len基本可以判断索引退化成了长字段匹配选择性可能不会太好。5.3 一个让我印象深刻的预判失误有一次我优化一个查询表上有索引(province, city, area)SQL 条件是WHERE city 杭州 AND area 西湖区我第一反应是你不用 province联合索引最左前缀肯定失效了结果 EXPLAIN 一跑key idx_province_city_areatype ref它竟然用了这个索引。后来翻源码和文档才知道MySQL 8.0 的优化器支持跳过联合索引的前导列进行匹配只要前导列的可区分度足够低或者统计信息显示这样查找有收益它就会尝试。这个特性在 MySQL 的skip_scan优化中实现。所以不要死记最左前缀失效这个结论要结合 EXPLAIN 实际验证。当然这个特性对city列的可区分度有要求如果city列的基数很低优化器反而不会走 skip scan。我的建议是设计索引时按最左前缀原则来但查询之前一定要用 EXPLAIN 验证真实执行计划别凭感觉下结论。6. 索引失效的几大场景生产环境踩坑实录6.1 对索引列使用函数或表达式这是最常见也最容易踩的坑。有人写WHERE DATE(created_at) 2024-06-01以为 created_at 上有索引就能加速。实际上MySQL 无法直接使用DATE()函数处理后的结果去匹配索引树它会先把所有行的 created_at 都算一遍 DATE()再做比较直接导致全表扫描。解决方法有两种一是把 SQL 改写成范围查询WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00二是在 MySQL 8.0 里建函数索引ALTER TABLE orders ADD INDEX idx_created_date ((DATE(created_at)));8.0 支持的函数索引会让优化器在匹配函数调用时自动走索引。我在生产环境把几个日期查询都改成了这种方式效果立竿见影。6.2 隐式类型转换最隐蔽的索引杀手隐式类型转换是我见过最隐蔽、最坑的失效场景。比如一个手机号字段用的是 varchar 类型查询却写了WHERE mobile 13800001111数字字面量。MySQL 会把索引列从字符串转成数字去比较这相当于对列施了隐式函数索引直接失效。你在 EXPLAIN 里会看到 type 从ref变成了ALL。关键是这种 SQL 在数据量小的时候根本感觉不出来等表涨到几百万行就突然卡死。排查技巧是如果 EXPLAIN 里type栏的等级莫名退化检查一下字段类型和查询值的类型是否一致。尽量保持 SQL 的查询参数类型和列类型完全一致。还有一种隐式类型转换发生在字符集不一致的情况下比如 utf8mb4 字段和 utf8 字段做关联查询也可能导致索引失效。6.3 前导通配符 LIKEWHERE title LIKE %MySQL%这种写法因为最前面的%使得索引树不知道从哪里开始匹配只能做全表扫描。但如果写成WHERE title LIKE MySQL%因为前缀已知索引是可以正常使用的。需要做真正的前后模糊匹配时与其用 LIKE不如考虑全文索引或者搜索引擎。我在项目里见过一个坑搜索功能一开始用 LIKE %xxx%数据量小没人在意后来表涨到百万行搜索接口直接超时。换成全文索引后问题立刻缓解。6.4 联合索引不满足最左前缀这个在前面已经讲过原理这里只说一个容易混淆的细节。联合索引(a, b, c)查询WHERE a 1 AND c 3a 能命中索引但 c 不行因为中间的 b 被跳过了。而WHERE b 1 AND a 2如果 MySQL 的优化器能识别出a和b的查询条件顺序不影响结果它会把a条件提前让索引生效。所以最左前缀失效并不绝对还是要以 EXPLAIN 的 key_len 为准。6.5 OR 条件的连锁反应WHERE user_id 1001 OR status 3如果两个列都有独立索引优化器可能会尝试用 index_merge 合并两条索引的结果但更多情况下只要其中一个条件无法走索引MySQL 就会退化成全表扫描。尤其是当 OR 两边的列都没有索引时基本可以确定扫全表。我的处理思路是把 OR 改写成 UNION 两个查询或者确保两边条件都能独立使用索引。具体改写要看执行计划不能无脑套。6.6 不等于、NOT IN 与大范围查询WHERE status ! 0或者WHERE id NOT IN (...),这类条件一般没法走索引因为 B 树索引是用来精确定位和有序扫描的不等于表示排除某个值排除对索引来说并不是一个天然的搜索路径。例外情况是如果这个不等于的字段区分度极高比如WHERE name ! admin且 name 的基数值非常大优化器评估后可能还是会走索引扫描。但总的来说这类查询需要谨慎。还有一个更常见的误区即使走了索引如果范围太大比如WHERE status 1命中了表中 80% 的数据优化器也会认为全表扫描比走索引更划算因为回表次数太多了。这不是索引失效而是优化器做出了成本更低的决策。遇到这种情况如果业务确实需要频繁执行这种大范围查询可以考虑覆盖索引来降低回表成本。6.7 统计信息过期导致的错误执行计划InnoDB 的优化器是依赖统计信息做决策的。如果表的统计信息长期不更新优化器可能高估或低估某个索引的选择性从而选错执行计划。这种问题排查起来特别头疼因为 SQL 和索引都没问题但查询就是慢。解决办法很直接重新分析表更新统计信息。ANALYZE TABLE orders;我在生产环境处理过一次这样的问题一个查询平时都是毫秒级某天突然变成几百毫秒EXPLAIN 看执行计划发现走了另一个选择性很差的索引。跑完ANALYZE TABLE之后执行计划恢复正常。这种坑在数据发生剧烈变化时容易出现比如一次性导入大量数据、批量删除数据后务必记得执行 ANALYZE。7. 索引创建与维护SQL 语法、碎片整理与 8.0 新特性7.1 常见索引操作语法索引的创建方式有几种下面是最常用的-- 建表时直接指定 CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_user_status (user_id, status) ) ENGINEInnoDB; -- 表已存在时追加 CREATE INDEX idx_user_status ON orders (user_id, status); ALTER TABLE orders ADD INDEX idx_user_status (user_id, status); -- 唯一索引 ALTER TABLE orders ADD UNIQUE KEY uk_order_no (order_no); -- 删除索引 DROP INDEX idx_user_status ON orders; ALTER TABLE orders DROP INDEX idx_user_status; -- 查看索引 SHOW INDEX FROM orders;SHOW INDEX FROM返回结果中有一个Cardinality字段代表索引的基数估测值。Cardinality越接近表的行数说明索引的选择性越高优化器更倾向于使用它。如果这个值和实际行数差距很大说明统计信息可能过期了这时候可以跑一遍ANALYZE TABLE刷新。7.2 索引不是越多越好写入放大的代价每新建一个索引等于多维护一棵 B 树。执行 INSERT、UPDATE、DELETE 时除了更新聚簇索引所有相关的二级索引也要同步更新。如果表上有 5 个二级索引一次插入就要写 6 棵树写入放大非常明显。我曾经遇到一个案例某张表为了满足各类查询场景建了 11 个索引写入吞吐率掉了将近一半。后来按实际的查询频率清理掉 4 个冗余索引写入性能立刻恢复正常。判断索引是否冗余的方法比较直接如果一个索引的高位列是另一个索引的前缀那它大概率是冗余的。比如已经有(user_id, status)索引又单独建了(user_id)索引后者就是冗余的。除非user_id列单独使用的频率极其高且二级索引体积差异带来明显的查询收益否则建议合并成一个联合索引。7.3 索引碎片与整理索引页在长期的增删改过程中会产生碎片页的数据利用率变低扫描索引时需要读取更多的页查询效率会下降。碎片化严重时可以通过重建表来整理ALTER TABLE orders ENGINEInnoDB;这个操作会重建整张表并重组索引能有效消除碎片。在 MySQL 5.6 之后这个操作支持在线 DDL也就是说执行期间不会阻塞读写但要注意它仍然会消耗大量 IO 和 CPU最好在业务低峰期执行。另外频繁删除和插入大量数据后记得分析表并重建索引可以避免空间浪费和查询性能恶化。7.4 MySQL 8.0 索引相关新特性MySQL 8.0 引入了几个对索引使用有直接影响的特性值得关注。第一个是降序索引可以在创建索引时指定某个列的排序方向比如KEY idx_user_status_time (user_id, status, created_at DESC)字段的排序方向会存储到索引的结构中优化器在进行对应的倒序排序时可以直接利用索引而不用做反向扫描。第二个是不可见索引你可以把一个索引标记为不可见优化器就不会用它这对于测试删除索引是否影响线上查询非常有帮助ALTER TABLE orders ALTER INDEX idx_user_id INVISIBLE; ALTER TABLE orders ALTER INDEX idx_user_id VISIBLE;第三个是函数索引前面已经提到过它对WHERE DATE(created_at) ...这类查询是刚需。8.0 还有一个细节默认的临时表或排序操作如果涉及 LONGTEXT、TEXT 和 BLOB 列建索引时必须指定前缀长度比如KEY idx_content (content(32))否则会报错这在归档表设计时经常会碰到。8. 一个完整的索引设计实战从慢 SQL 到性能达标前面把理论和工具都过了一遍最后用一个完整的实战案例把整个流程串起来。这是一个类似订单管理的后台系统业务上线半年后某几个核心查询越来越慢。我接到工单后第一件事就是打开慢查询日志把 TOP 慢 SQL 捞出来。其中最有代表性的两条是-- SQL 1按用户查最近订单 SELECT * FROM orders WHERE user_id 567888 ORDER BY id DESC LIMIT 20; -- SQL 2按状态和日期区间统计 SELECT COUNT(*), status FROM orders WHERE status IN (1, 2) AND created_at BETWEEN 2024-05-01 AND 2024-05-31 GROUP BY status;先看 SQL 1发现表上虽然有idx_user_id索引但ORDER BY id DESC需要回表后再排序产生Using filesort。优化方案说是把user_id和id做成联合索引(user_id, id DESC)这样查询到 user_id 的记录时叶子节点已经按 id 倒序排好了直接取前 20 条就行连回表和排序都省了。改造后这条 SQL 从原来的 120 多毫秒降到了 3 毫秒。再看 SQL 2这是一个统计类查询条件里 status 是等值集合created_at 是范围。我建了一个联合索引(status, created_at)因为 status 是分组字段也相当于等值匹配放前面created_at 放后面做范围过滤。另外这个查询只涉及 status 和 COUNT(*)如果 status 和 created_at 都在索引里那这个索引本身就是覆盖索引不需要回表去取其他列。改造后合并 EXPLAINExtra 字段直接显示Using index没有再出现Using temporary。这个案例想说明的核心观点是索引设计必须从 SQL 出发而不是从列出发。不要先把所有字段都加上索引再看哪些能用应该先收集慢查询日志分析高频率查询的模式然后针对性地设计联合索引。每一次索引的增删都应该以 EXPLAIN 的执行计划为依据同时结合写入频率做好权衡。另外一个提醒上线新索引后要持续观察一段时间。我见过有人加完索引后立即觉得快了很多但忽略了它对写入带来的影响等到业务高峰期写入量暴涨才发现问题。稳妥的做法是新索引上线选低峰期先观察一两天用性能监控对比前后写入延迟和锁等待确认没有副作用之后再长期保留。MySQL 索引这块理论集中在一棵树实践散落在每一行 SQL 和每一个执行计划里。文章写到这儿核心内容已经全部覆盖了。如果你看完之后能对自己线上表里的索引做一次系统的梳理用 EXPLAIN 验证几条曾经觉得有问题的查询那这篇文章的价值就不算白写。