1. 索引到底解决了什么问题先聊个真实的场景。我刚工作那会儿公司有个订单查询接口每天被调用几十万次。某天线上报警数据库 CPU 直接飙到 100%一查慢查询日志发现一条查询跑了整整 12 秒。表里才多少数据不到 300 万行。当时的 SQL 长这样SELECT * FROM orders WHERE user_id 12345 AND status 1 ORDER BY create_time DESC;这张表没有索引所以每次查询都是全表扫描300 万行数据从头到尾读一遍。更要命的是这张表每天还在不断插入新数据全表扫描的时间随着数据量线性增长到最后几乎没法用。后来我给user_id和status加了个复合索引这条查询从 12 秒降到了 30 毫秒。就这一个索引省了两台数据库服务器。这事儿之后我就明白了索引就是数据库的“目录”。你想在一本 1000 页的书里找一个关键词没有目录就从头翻到尾有目录直接翻到对应页码就行。MySQL 的索引本质上就是为数据建的目录结构它把某个列或某几个列的值按特定顺序组织起来让查询可以快速定位到目标记录而不是把整张表扫一遍。很多人对索引的理解就停留在“加了索引查询就快了”但一遇到实际问题就懵为什么明明建了索引却没用为什么a AND b的查询建了(a,b)复合索引还是不生效为什么有些场景加索引反而拖慢写入这些问题背后是对索引原理和机制的掌握程度。这篇长文就把 MySQL 索引从底层数据结构到实际调优完整地过一遍。说句实在话索引不是越多越好它是一把双刃剑。用好了是性能利器用不好就是存储和写入的负担。下面从数据结构开始一步步拆解清楚。2. 索引的底层数据结构B 树的来龙去脉2.1 为什么不是二叉搜索树或哈希表很多初学者都会疑惑为什么 MySQL 的 InnoDB 引擎选择 B 树作为索引结构而不是更常见的二叉搜索树或者哈希表先说二叉搜索树。它的查找时间复杂度是 O(log n)理论上不错但问题出在树的高度上。二叉搜索树每个节点最多只有两个子节点当数据量达到几百万行时树的高度会达到 20 到 30 层。这意味着每次查询都要经过 20 到 30 次磁盘 I/O而每次 I/O 都是毫秒级的操作累积下来就是几百毫秒完全无法接受。再说明盐表。哈希索引的查找速度是 O(1)听起来无敌但它有两个致命缺陷。第一哈希索引不支持范围查询。WHERE age 18这种条件哈希表没办法处理因为哈希表是无序的没法快速遍历一个区间。第二哈希索引不支持排序ORDER BY age在哈希索引下依然要走 filesort。而数据库查询里有相当大比例是范围查询和排序场景所以哈希表只能作为一种辅助索引存在也就是 InnoDB 的自适应哈希索引。B 树的设计恰好解决了这两个问题。它是一种多路平衡搜索树每个节点可以存多个子节点指针数据量在百万级时树的高度只有 3 到 4 层。而且 B 树的叶子节点通过链表相连天然支持范围查询和排序。2.2 B 树的具体结构和查询过程B 树有两个关键设计非叶子节点只存索引键值叶子节点存真实数据和相邻节点的指针。非叶子节点不存数据所以每个节点能容纳更多的键值这直接压低了树的高度。举个例子假设 InnoDB 每页大小是 16KB一个索引键占 8 字节那每个非叶子节点大约能存 16KB / (8 6) 大约 1100 个键值6 字节是子节点指针。当树的高度为 3 时叶子节点数量约 1100 的平方也就是 120 万左右再乘上每个叶子节点能存的记录数一张表轻轻松松存下千万级数据查询只需要做 3 次磁盘 I/O。查询过程也很直观。比如要查WHERE age 25从根节点开始比对 key 的大小决定往左还是往右走。走到叶子节点后在有序链表里做二分查找找到目标记录。整个过程就像你翻一本书的目录先看大章节再看小章节最后定位到具体页。叶子节点的链表设计是 B 树比 B 树更适合数据库的核心原因之一。B 树的叶子节点之间没有链接范围查询还是得“回溯”去查父节点。B 树则直接顺着链表往后扫一次范围查询的代价几乎等于一次普通查询再加上遍历链表。2.3 InnoDB 和 MyISAM 的索引存储差异MySQL 早期主流的 MyISAM 引擎和现在的 InnoDB 引擎在索引存储上的理念完全不一样。MyISAM 的索引文件和数据文件是分开的所以叫“非聚簇索引”或“二级索引”索引的叶子节点存的是数据的物理地址拿到地址再回表取数据。InnoDB 则是“聚簇索引”的鼻祖。它的主键索引和数据是存在一起的主键索引的叶子节点直接存整行数据。所以 InnoDB 表一定要有主键如果你没指定主键InnoDB 会找一个非空的唯一列当主键实在找不到就偷偷生成一个 ROWID 当主键。这也是为什么面试喜欢问“为什么 InnoDB 表必须有主键”本质就是聚簇索引的结构决定的。聚簇索引带来一个天然优势按主键范围查询时数据在磁盘上就是物理有序的顺序读取速度极快。但副作用是主键如果是一个 UUID 这种随机字符串插入时索引会因为频繁的页分裂而效率低下所以 InnoDB 表强烈建议使用自增整数作为主键。3. 索引的分类和创建方式3.1 主键索引、唯一索引、普通索引、全文索引MySQL 里常见的索引类型按功能分有四种主键索引、唯一索引、普通索引、全文索引。它们的核心区别在于约束能力和使用场景。主键索引就是 PRIMARY KEY一张表只能有一个它的值不能为空也不能重复。由于 InnoDB 聚簇索引的特性主键索引直接决定数据的物理存储顺序。需要特别说明的是主键不仅仅是一个“索引”它还是数据行的唯一标识。所以能用自增整数就不要用业务字段当主键比如身份证号虽然能保证唯一但作为字符串主键会让索引页的利用率变低写入性能也差。唯一索引和主键索引的区别在于唯一索引的值不能重复但可以为空而且一张表可以有多个唯一索引。两者在查询性能上几乎没差别唯一索引末尾多一步去重复检查而已。在线上环境业务上需要发货单号这种唯一约束但又允许空值的字段就很适合建唯一索引。普通索引就是最常见的索引它不加任何约束只加速查询。你可以在任意列上建普通索引但要注意一个索引本质上是一棵独立的 B 树每多一个索引就多一份存储开销写入时也要多维护一棵树。所以索引不是越多越好而是越精准越好。全文索引是用于全文检索的早期 MyISAM 支持InnoDB 在 MySQL 5.6 之后也支持了。它适合LIKE %关键词%这种模糊匹配场景。但是说实话在数据量大且搜索需求复杂的场景下Elasticsearch 这类专业搜索引擎比 MySQL 全文索引好得多。MySQL 的全文索引应对中小体量的站内搜索够用但别指望它替代专业搜索引擎。3.2 单列索引和复合索引的选择复合索引也叫联合索引是索引优化里最容易出问题也最值得花时间研究的部分。它是指在一个索引里包含多个列比如建立(a, b, c)索引实际上等于建了(a)、(a, b)、(a, b, c)三个索引的效果这就是“最左前缀原则”的价值所在。很多开发者在建复合索引时犯的最典型错误就是盲目跟风。看到查询里出现了A AND B条件就建(A, B)完全没有考虑字段的区分度和查询频率结果常常是索引建了但没起到应有的效果甚至比全表扫描还慢。建复合索引遵循一个基本策略把查询里最常出现、区分度最高的字段放在最左边。区分度是指字段值的唯一程度比如性别字段只有“男”“女”两个值区分度极低而订单号的每个值都不同区分度极高。把高区分度的字段放在左边可以最大化地削减索引树的搜索范围。3.3 创建索引的具体语法和操作实际创建索引的 SQL 语法比较简单但很多细节值得反复确认。最基本的两种方式-- 方式一建表时指定 CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_username (username), KEY idx_email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 方式二ALTER TABLE 添加 ALTER TABLE users ADD INDEX idx_email_username (email, username);这里有个比较容易忽略的点索引命名规范。虽然 MySQL 不强制要求但你要是接手过那种索引名毫无规律的项目维护起来真能让人崩溃。我个人的习惯是主键索引就叫 PRIMARY唯一索引用uk_字段名前缀普通索引用idx_字段名前缀复合索引用idx_字段1_字段2把所有参与字段列出来。这样在SHOW INDEX FROM table的返回结果里一眼就能看出每个索引的用途。创建索引时有个参数值得关注USING BTREE。InnoDB 引擎下索引默认就是 B 树MySQL 也支持 HASH 索引但只在 MEMORY 引擎里才有实际意义InnoDB 下指定的 HASH 会被自动忽略。另外前缀索引也是一个有用的优化手段比如对长字符串建索引时可以只取前 N 个字符减少索引占用空间但要注意前缀长度选择不当会导致区分度下降。4. 覆盖索引、回表与索引下推4.1 回表查询的代价理解了 InnoDB 的聚簇索引结构就能自然理解“回表”这个概念。假设表里有主键 id还有一个普通索引idx_user_id建在 user_id 列上。你执行SELECT * FROM orders WHERE user_id 100MySQL 会先通过idx_user_id这棵二级索引树找到所有符合条件的主键 id然后再拿着这些主键 id 去聚簇索引树里查完整的行数据。这个第二步就是“回表”。一次回表意味着一次随机 I/O如果命中了 1000 行就要回表 1000 次性能损耗相当可观。那怎么避免回表答案是覆盖索引。如果查询的字段全部包含在索引的键里那 MySQL 在二级索引树上就能拿到所有需要的数据压根不需要回表。比如你执行SELECT user_id, status FROM orders WHERE user_id 100而且存在(user_id, status)这个复合索引那 MySQL 直接在索引的叶子节点上就能读出来这两个字段省掉回表开销。这也是很多资深 DBA 建议“能把 SELECT * 改成 SELECT 指定字段就改成指定字段”的原因之一。除了减少网络传输和内存占用还可能让原本需要回表的查询变成覆盖索引查询。4.2 索引下推是怎么“下推”的索引下推是 MySQL 5.6 引入的优化英文叫 Index Condition Pushdown简称 ICP。理解它需要先看一个对比场景。假设有复合索引(age, city)执行查询SELECT * FROM users WHERE age 20 AND city 上海;根据最左前缀原则age可以用上索引但city因为跳过了一个范围条件并不能完整地走索引过滤。在没有 ICP 的旧版本里MySQL 的做法是先从索引树里把所有age 20的记录找出来然后每条都回表拿到完整行数据后再在服务层判断city 上海。这个回表量非常大可能取回了几万条数据最后只留下几百条。有了 ICP 之后MySQL 会在存储引擎层把city 上海这个条件下推到索引遍历的过程中。也就是说在遍历索引时通过索引里已有的 city 值先判断一次不符合的直接跳过只有同时满足age 20 AND city 上海的数据才会回表。回表次数锐减查询速度自然大幅提升。ICP 优化默认是开启的它最典型的受益场景就是复合索引里第二列及之后的条件过滤。这也是为什么我一直强调复合索引列顺序要慎重因为 ICP 虽然能帮你挽回一部分性能但能走完整最左前缀还是比依赖 ICP 强得多。4.3 通过 EXPLAIN 看执行计划检查一条 SQL 是否用上了覆盖索引、是否触发了回表最有效的方式是看执行计划。EXPLAIN 是每个搞 MySQL 的人都必须熟练掌握的工具。EXPLAIN SELECT user_id, status FROM orders WHERE user_id 100;关键看Extra这一列。如果出现Using index说明这条查询用了覆盖索引没有回表。如果出现Using index condition说明触发了索引下推。如果出现Using where但前面的 key 不为空通常意味着索引只定位了一部分数据剩下的条件在回表后过滤了。我自己排查慢 SQL 的标准流程是先EXPLAIN看 type 和 key再根据rows估算扫描行数最后结合 Extra 来判断是否要继续优化索引。type 列的取值从好到差依次是 system、const、eq_ref、ref、range、index、ALL只要能到 range 以上通常问题不大一旦看到 ALL 就是全表扫描得重点排查。5. 复合索引与最左前缀原则的实战经验5.1 最左前缀原则到底怎么理解最左前缀原则是复合索引最核心的规则也是面试必考题。它的表述很简单复合索引的生效顺序必须从索引最左边的列开始不能跳过中间的列。假设有一个复合索引(a, b, c)那么以下查询能用到索引WHERE a 1WHERE a 1 AND b 2WHERE a 1 AND b 2 AND c 3WHERE a 1 AND c 3这里只用到了 a 列c 列无法走完整索引但有 ICP 可能还能过滤一些以下查询则用不上索引或只能部分使用WHERE b 2没带 a直接失效WHERE c 3没带 a 和 b直接失效WHERE b 2 AND c 3同理失效为什么会有这样的规则还是回到 B 树的结构。复合索引在排序时先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。这种“字典序”的组织方式决定了只有从 a 开始的条件才能借助索引的有序性快速定位。如果直接从 b 开始定位就相当于在电话簿只知道对方名字而不知道姓氏根本没法用目录快速查找。5.2 高频提问“where a and b 应该怎么建索引”这是被问得最多的问题因为 SQL 里两个条件并列是再常见不过的场景。比如热词里的“mysql where条件a and b应该怎么建索引”答案不是一句“建 (a,b) 复合索引”就完了还得看两个条件是什么类型。先分情况讨论。如果 a 和 b 都是等值查询a 1 AND b 2建(a, b)还是(b, a)其实差别不大因为等值查询不涉及到范围截断两个字段的顺序不改变索引可用性。但对单点查询的性能而言把区分度更高的字段放前面能更快收敛目标区间。如果 a 是等值、b 是范围查询a 1 AND b 100那就必须把等值字段 a 放在前面范围字段 b 放后面。因为一旦 b 这种范围条件走了索引它后面的字段就都失效了但 a 的等值条件不受影响。顺序反过来的话b 的范围条件会让 a 的等值判断失效整体性能会差很多。如果 a 和 b 都是范围查询那就只能选择一个字段走索引另一个字段回表后用 USING WHERE 过滤。这时候要选区分度更高、过滤效果更好的那个字段建在左边另一个只能靠 ICP 或者干脆建两个单列索引后让优化器自己选择。在这个场景里还有一个常见的优化技巧如果 a 的区分度极高比如订单号b 的区分度极低那可以考虑把 b 作为索引的一部分写到覆盖索引里这样既能让 b 的过滤条件在 ICP 下生效又可能做到覆盖索引避免回表。这个做法适合那种 WHERE 里两个字段都出现、SELECT 里也只有这两个字段的轻量查询。5.3 复合索引设计的一个完整案例举个例子。一个订单表最常用的查询是查某个用户某天之后的订单SELECT id, order_no, amount FROM orders WHERE user_id 588 AND create_time 2024-01-01 ORDER BY create_time DESC;这个表里 user_id 区分度很高create_time 范围查询。按上面的原则索引应该建(user_id, create_time)。create_time 放后面是因为它作为范围查询放在后面不会影响前面的等值条件使用。同时这个查询只取 id、order_no、amount 三个字段如果再把 order_no 加进索引里变成(user_id, create_time, order_no)amount 无法避免回表但 order_no 可以覆盖EXPLAIN 能看到 Using index condition 加部分 Using index。再看这个表的写入是否频繁。如果线上同时有大量插入每多一个字段进索引都意味着插入时要做更多工作所以这个索引的职责要平衡好。把查询出现频率最高、数据量最大的那条 SQL 优化到位比贪多求全地建大复合索引更实际。5.4 排序和 GROUP BY 里的复合索引复合索引不仅能优化 WHERE 过滤还能优化 ORDER BY 和 GROUP BY。因为索引本身就是有序的如果排序字段和索引顺序能匹配上MySQL 直接按索引顺序读出来就是排好序的结果不需要额外 filesort。最典型的就是ORDER BY create_time DESC配合WHERE user_id 588。上面(user_id, create_time)索引天然就是按 user_id 分组、组内按 create_time 排序的查询走索引后结果已经有序EXTRA 里看不到 filesort。但注意如果排序方向不一致比如索引是升序而查询是ORDER BY create_time DESCMySQL 8.0 支持倒序索引才有办法直接利用早期版本就需要 filesort。加上这种方向性问题在处理复合索引时更麻烦比如ORDER BY a ASC, b DESC索引(a, b)就没法严格满足双向排序需求。GROUP BY 的优化逻辑类似分组字段正好是索引的最左侧列时MySQL 可以通过索引做“松散索引扫描”来避免临时表和文件排序。这也是为什么统计类查询如果能在字段上设计好索引性能提升会非常明显。6. 索引失效的常见场景和排查方法6.1 你建的索引为什么没用上索引失效是实战中最伤脑筋的问题建了索引EXPLAIN 一看 type 是 ALL完全没走。这里面有规律可循最常见的几个坑我用一张表整理出来。失效场景原因说明应对手段对索引列使用函数WHERE YEAR(create_time) 2024改成WHERE create_time 2024-01-01 AND create_time 2025-01-01对索引列做隐式类型转换WHERE phone 13800138000phone 是 varchar把参数改成字符串写法13800138000使用 LIKE 前置通配符WHERE name LIKE %张尽量改成后缀匹配实在需要全文检索就另建搜索引擎OR 连接非索引列WHERE id 1 OR name 张三改为 UNION 两条查询或给 OR 两侧字段都加索引索引列参与运算WHERE age 1 20把等号右侧的运算去掉改成WHERE age 19复合索引未遵循最左前缀WHERE b 2且索引为(a,b)调整查询条件或调整索引列顺序每一项背后都是 B 树有序性的问题。函数和运算会把索引键的值变成另一个值导致索引树的二分查找无法按原有序性进行隐式类型转换意味着索引列的原本数据和传入参数的类型不匹配MySQL 只能把每一条索引值都转一遍再比OR 条件则是因为 MySQL 无法在单棵索引树上同时完成两个列的搜索条件合并。6.2 隐式类型转换的坑我踩过不止一次这里展开说下隐式类型转换因为它的隐蔽性特别强。热词里就有“mysql ssl 连接错误”和一堆数据库工具问题但隐式转换导致索引失效是比连接错误更常见的业务事故。有个典型例子表里手机号字段是 varchar 类型查询写的是WHERE phone 13800138000。很多新手觉得这没问题SQL 的两侧一个字段一个数字值对得上就查呗。可 MySQL 的隐式类型转换规则是字符串和数字比较时字符串会被转换为数字。也就是说MySQL 要把表里每一行的 phone 字符串都转成数字再和 13800138000 比较索引列的隐式函数处理直接导致索引失效。而且这类问题在生产环境出了之后很难排查因为本地数据量小全表扫描也就几十毫秒完全没感觉。上了生产几百万行数据慢查询日志一拉才发现是这种低级但高频的错误。排查方法很简单EXPLAIN看 type 是不是 ALL再看 SQL 里有没有参数类型和字段类型不一致的情况。养成习惯写 SQL 时先DESC table看看字段类型再决定参数怎么传。现在 ORM 框架里很多查询是自动生成 SQL 的参数类型要对齐好否则很容易踩坑。6.3 优化器说不用就是不用的场景有些时候你确实没犯上面任何错误索引还是没被使用。这是因为 MySQL 的优化器自己判断“走索引还不如全表扫描快”。最典型的是低区分度字段。比如性别字段只有男、女两个值查询条件WHERE gender 男可能匹配全表一半数据。这种情况下走索引需要大量回表还不如直接全表扫描优化器的成本估算会选择 ALL。另一个常见场景是大范围查询。WHERE id 1这种条件虽然完美匹配主键索引但结果集几乎是全表优化器会放弃索引直接扫表。有些资料说“范围查询导致索引失效”其实不是索引失效而是优化器认为走索引不划算。这类问题怎么处理如果是低区分度字段看业务是否能接受为它建立索引合并Index Merge策略即多个单列索引分别扫描后再合并结果集或者直接接受全表扫描毕竟低区分度字段的全表扫描成本也不算太高。如果是大范围查询那要检查业务逻辑是不是漏了必要的边界条件比如是不是应该加上时间范围限制。7. 索引维护和 SQL 优化的实操经验7.1 慢查询日志和索引分析工具搞清楚索引是否有效不能靠猜要基于数据来诊断。慢查询日志是最直接的入口。MySQL 默认慢查询阈值是 10 秒对互联网应用来说太宽了我通常在生产环境调成 1 秒甚至 500 毫秒。-- 查看当前慢查询设置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 开启慢查询日志临时生效 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;日志里打印出来的慢 SQL先用 EXPLAIN 分析再结合SHOW INDEX FROM table看表上有哪些索引可用。很多时候你会发现项目早期建了一堆冗余索引比如单独建了(a)索引又建了(a, b)前者完全多余是典型的资源浪费。information_schema.statistics表可以用来写诊断 SQL一次查出所有索引的字段组成和基数cardinality。基数太低说明索引列的区分度不足这类索引往往对查询帮助不大可以考虑删掉。7.2 索引的存储开销与写入代价索引带来的最大负作用是写入变慢。每插入一条记录除了往主键索引树里写数据之外每个二级索引树也要同步插入一个索引节点。如果表里有 5 个索引一次插入就要写 6 棵树事务提交时这些树的变更都要刷到磁盘。数据量越大索引树的深度越深插入成本越明显。所以我在线上环境一向的态度是尽量用最少的索引覆盖最多的查询模式。不要一个查询加一个索引而是分析所有慢查询的共性用复合索引统一覆盖。比如你有一堆查询条件但它们都带 user_id那核心索引就围绕 user_id 来设计再把不同的次要条件按频率依次加入。可以通过SHOW INDEX FROM table查看索引的基数。基数太低说明索引列的区分度不足这类索引往往对查询帮助不大可以考虑删除。7.3 大表的索引重建和在线 DDL一旦索引设计不合理生产环境上要改索引最大的担心是表锁和长时间阻塞业务。早期的 MyISAM 时代 ALTER TABLE 会全程锁表InnoDB 的在线 DDL 特性MySQL 5.6 以后已经能解决大多数场景。-- 添加索引算法为 INPLACE ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time), ALGORITHMINPLACE, LOCKNONE;LOCKNONE表示允许 DDL 期间并发读写不会阻塞业务。但即使如此大表的索引创建还是会消耗大量 IO 资源建议在业务低峰期操作或者使用 gh-ost 这类工具把 DDL 操作拆成小块同步到影子表里。特别提醒DROP INDEX 之前一定先确认它没有被任何查询依赖。我犯过的错误是删了一个看似没用的索引结果某条报表 SQL 从 300ms 直接退化到 9 秒。后来养成了删索引前先查慢查询日志、确认最近一周没有依赖这个索引的慢 SQL 才开始动手的习惯。8. 高频问题排查实录这个部分汇总一些我在社区和实际工作中反复遇到的问题有点像“相亲角”一样把常见疑难杂症集中挂出来方便对号入座。8.1 主键索引和唯一索引的区别别再说“一样”了很多人回答这个问题时就说“唯一索引允许 NULL主键不允许”这只说对了一小半。在 InnoDB 的聚簇索引结构下主键索引决定数据的物理存储顺序而唯一索引只是逻辑上的唯一约束。主键的叶子节点存的是整行数据唯一索引的叶子节点存的是主键值查询时还需要回表。这个本质区别意味着主键删除成本极高因为要移动数据页而唯一索引删除只是动索引树的节点。另外“唯一索引允许 NULL”在 MySQL 里有个特殊情况一个表可以有多个 NULL 值因为 NULL 不等于任何值包括另一个 NULL。而主键索引因为是 NOT NULL 加唯一所以完全不允许任何形式的重复或者空值。8.2 为什么 MySQL 建议主键用自增整数这个建议的本质原因在前面聚簇索引部分已经说透了。自增主键保证新插入的数据在主键索引树上是往“最右边”追加的不需要大量移动已有数据页分裂的频率也最低。如果用 UUID 这种随机字符串做主键每次插入都可能落在索引树的中间位置触发大量页分裂和索引重组数据页的利用率还会降低。有一种具体的对比案例我实操过。一张 1000 万行的日志表自增 int 主键的情况下插入峰值能到每秒 5000 行改成 UUID varchar(36) 主键后峰值直接掉到每秒 800 行左右差距就是这么明显。8.3 哪些场景用会导致索引失效这个问题的标准答案就是第 6 章那张表里的内容但有一个细节容易被忽略MySQL 的优化器对索引成本的决定是数据相关的。同一个 SQL在表数据量小的时候走全表扫描数据量大了之后反而会走索引。所以“索引失效”经常是个动态现象你开发时看着 EXPLAIN 正常上了生产数据量大了才发现走了 ALL。这就是为什么监控慢查询、周期性检查执行计划这么重要。另一个容易忽略的是“索引列参与比较时合并了多个范围条件”。比如WHERE a 1 OR a 0这种条件从数学上可以合并成一个全量范围但实际上优化器会老老实实把两个范围 UNION如果数据分布差可能出现一条 SQL 扫多个区间的现象效率反而不如全表扫。8.4 排序变慢是因为没用上索引吗排序慢有两种情况一种是完全没用上索引MySQL 走 filesort 把结果集放到内存或临时文件里排序另一种是用上了索引但排序字段本身不在索引覆盖范围内。如果在 EXPLAIN 的 Extra 中看到Using filesort说明排序没有走索引。要优化就是把 ORDER BY 的字段加入某个合适的复合索引并确保 WHERE 条件的前缀字段和它顺序匹配。比如已经建了(user_id, create_time)索引WHERE user_id 123 ORDER BY create_time DESC就能直接走索引顺序。但如果查询里排序字段前面还有个范围条件比如WHERE create_time 2024-01-01 ORDER BY user_id而索引是(create_time, user_id)那 user_id 的排序就用不上了。这种时候重新设计索引顺序或者改写 SQL 去配合现有索引都得看实际查询频率来权衡。8.5 MySQL 索引在事务和锁里的角色这个经常被人忽略。索引是 InnoDB 行锁的基础。InnoDB 的行锁不是“给数据行上锁”而是“在索引记录上加锁”。如果一条 UPDATE 语句的 WHERE 条件没有索引InnoDB 无法在索引上定位记录就只能退化成对全表做扫描并给每条扫描到的记录加锁表面上锁了一堆行实质上已经接近表锁的并发表现。我遇到过一个线上事故一条 UPDATE 因为没有索引在并发场景下把整张 200 万行的表锁住了所有读写全部阻塞数据库连接数瞬间爆掉。排查到最后就是 WHERE 的字段没建索引。所以判断一个字段该不该建索引时不仅要看它是否出现在查询里还要看它是否出现在 UPDATE/DELETE 的 WHERE 条件里。索引既是查询的加速器也是锁的定位器。8.6 工具类问题和索引的关系热词里出现了 navicat、数据库工具、连接错误这些词虽然不是索引本身的内容但有一个共同点使用图形化工具查看和设计索引时直观性和准确性不可兼得。Navicat 这类工具设计索引确实方便点几下就出来了但你得清楚工具帮你在后台执行了什么样的 DDL。我建议团队内部统一用 SQL 脚本管理表结构变更不要用 GUI 直接改生产库否则没有任何记录后续审计和回滚都无从谈起。9. 索引选型与调优的综合思路9.1 一个索引设计流程能解决 80% 的问题做索引设计这么多年我总结了一个固定流程遇到新业务表就按这个走基本不会出大问题。第一步收集查询模式。把业务里跑得最多的所有 SQL 列出来标注 WHERE 条件、JOIN 条件、ORDER BY、GROUP BY、覆盖查询字段。第二步筛选高优先级字段。统计每个字段在查询里出现的频率和区分度。出现频率高、区分度高的字段优先进索引。第三步设计复合索引。优先覆盖最热点查询的 WHERE 等值加排序组合不要贪多。一个复合索引能覆盖尽量覆盖多个相似查询。第四步用 EXPLAIN 验证。把核心 SQL 跑一遍 EXPLAIN确认 type 不低于 rangeExtra 没有出现 Using filesort 这种需要优化的情况。第五步上线后监控。慢查询日志持续观察至少一周确认优化后的查询没有退化同时观察写入性能没有明显回退。这五步里最容易出错的是第二步。很多开发只关注字段出现频率忽视了区分度结果把性别这类低区分度字段放进了复合索引左边导致整个索引价值大打折扣。9.2 索引不是银弹表设计远比索引重要说了这么多索引技巧我必须泼一盆冷水索引能解决的是“已有查询模式下的性能问题”而不是“错误表设计带来的结构性问题”。最常见的错误表设计是字段类型选错。明明存数字却用 varchar明明存日期却用字符串这类表无论怎么建索引都有先天缺陷。比如日期用字符串存储范围查询BETWEEN 2024-01-01 AND 2024-01-31在字符串类型下是按字典序比较一旦日期格式不统一有些是 2024-1-1有些是 2024-01-01结果就完全不对索引也帮不上忙。再比如大字段。一张表里塞了几个 TEXT 字段行数据特别长每个数据页能存的记录数就少聚簇索引的扫描效率自然低。这种表本身就该考虑垂直拆表把大字段拆到附属表里主表只保留高频查询需要的列。索引设计在这种场景下只是治标表结构调整才是治本。9.3 什么时候应该放弃索引不是所有查询都需要索引。当出现以下信号时完全可以考虑不加索引表数据量在万级以内全表扫描本身就是毫秒级。字段区分度极低比如状态字段只有两三个取值。查询结果经常占到全表的 20% 以上走索引的回表成本已经高于全表扫描。写入远大于查询而查询本身压力也不大。这类判断需要结合具体场景不能拍脑袋。比如“万级以内不加索引”不是绝对真理如果这个表是频繁 JOIN 的维表JOIN 条件上有索引能避免驱动表每行去查被驱动表时的全表扫描这种场景下即使数据量小也值得建索引。核心原则始终是让优化器有更廉价的执行路径可走而不是为了“有索引”这个形式去建。9.4 索引调优的长期主义最后聊点务虚的。索引设计不是一锤子买卖随着业务发展查询模式一直在变。今天的热点查询明天可能就不用了今天没人用的索引明天可能变成核心路径的加速器。所以我会建议团队定期做索引健康检查每个季度拉一次慢查询日志看看哪些 SQL 的响应时间在缓慢上升结合表数据量的增长来分析是不是索引的区分度下降或者页分裂严重了。长周期的数据变化会让索引效果慢慢劣化比如时间字段的基数在持续变大原来的索引策略可能从“高效”变成“低效”。在一个业务系统里做索引调优最忌讳的就是“改完就跑”运维数据要持续跟。一个健康的数据库慢查询数量应该是稳定或下降的如果慢查询曲线持续上升即使单条 SQL 看起来还没到告警线也要提前介入排查了。我个人在实际操作中的体会是索引调优更像一门“平衡艺术”。你要在查询加速和写入成本之间找平衡也要在单条 SQL 的极致优化和整体系统的稳定之间找平衡。不要为了展示技术实力而堆砌索引也不要在查询已经扛不住的时候还坚持不建索引。每建一个索引就问自己三个问题它覆盖了哪些高频查询它会不会成为某些写入路径的瓶颈如果删掉它哪些 SQL 会变慢三个问题都有明确答案这个索引才值得留在线上。