这几年我接手过的慢查询里十个有八个问题出在索引上面。要么是压根没建索引导致全表扫描要么是建了索引却因为写法问题压根没走还有一批是把索引建得又多又杂写入性能被拖垮了也不知道。MySQL的索引就是这么个东西用得好查询能快几个数量级用不好反而成为负担。这篇笔记算是把索引这条线从头捋一遍从数据结构到聚簇索引和回表从联合索引的最左前缀到覆盖索引再到实操里怎么用EXPLAIN验证设计最后把索引失效的典型场景整理成速查表。适合刚啃完SQL基础、准备深入学习数据库原理的开发者也适合写了好几年业务SQL但没系统梳理过索引逻辑的后端工程师。1. 索引是怎么工作的先搞懂B树再动手建索引1.1 索引的本质数据库的“图书目录”说到索引最直白的类比就是词典。你想在一本新华字典里查“索引”这个词正常人不会从第一页开始翻而是先查目录找到页码再直接翻到那一页。数据库里的索引干的就是这件事——它把某个列的值组织成一种便于查找的结构让查询不用从头到尾扫一遍表。没有索引时MySQL做查询基本就是全表扫描typeALL一张表一千万条数据就算每行记录只有几百字节也要把整个数据文件过一遍这就是典型的“翻字典从第一页开始”。而有了索引之后查询只需要走一遍索引结构定位到目标位置再去取数据。不过这里有个关键点要拎清楚索引不是万能的它本质上是用空间换时间。每建立一个索引InnoDB就要额外维护一棵B树写入数据时这棵树也要跟着更新。所以索引建多了查询是快了插入、更新、删除的代价都会上升。这也是为什么“索引越多越好”在实战中是一个危险想法后面我会专门讲。1.2 为什么InnoDB选择B树而不是哈希和二叉树MySQL默认存储引擎InnoDB的索引结构是B树。很多人只知道“索引是B树”这个结论却不知道为什么要选它。我把几个候选结构放在一起对比一下就明白了哈希表单行等值查询确实极快O(1)搞定。但哈希结构不支持范围查询也不支持排序。你写个WHERE age 20哈希索引直接抓瞎ORDER BY age它也排不了序。二叉搜索树理论上查询是O(log n)但数据按顺序插入时会退化成链表查询复杂度变成O(n)完全不可控。红黑树/AVL树能保持平衡但是树高随数据量增长。一千万条数据红黑树高度大约在二十多层每次查询要经历二十多次磁盘IO磁盘随机IO的耗时是内存访问的几万倍这开销谁也扛不住。B树每个节点能存放多个键值把树高压得很低。InnoDB里一个节点默认是一页16KB三到四层就能放下几千万条数据。更关键的是B树的叶子节点通过双向链表串起来范围查询和排序都能做到顺序扫描不用回溯父节点。所以B树几乎就是为磁盘存储这种场景量身定做的矮、胖、支持顺序访问。我在实际调优中见过不少次有人拿MEMORY引擎的哈希索引做业务表索引查询等值条件确实快但一加范围查询或排序就全线崩溃最后还得换回B树结构。另外一个值得说的是InnoDB的B树叶子节点之间是用双向链表连接的这正好对应了搜索引擎里搜到的“双向索引”这个词。这个双向链表带来的能力就是范围扫描和排序可以一路沿着链表走效率极高也是B树对比普通B树的优势之一。2. 聚簇索引、回表与覆盖索引理解索引的三层关系2.1 主键索引为什么是“聚簇”的InnoDB的数据文件本身就是一棵B树。这棵树有两个特点叶子节点存的是整行数据主键值作为B树的排序键。也就是说表数据实际上是按主键顺序组织在磁盘上的这个结构就叫聚簇索引。每个InnoDB表有且只有一个聚簇索引规则按优先级排列显式定义了主键PRIMARY KEY用它做聚簇索引没有主键选第一个非空唯一索引UNIQUE NOT NULL做聚簇索引上面两个都没有InnoDB自动生成一个隐藏的6字节rowid做聚簇索引。这个逻辑有时候会产生意想不到的坑。我遇到过一张表业务上压根不用主键开发图省事就建了个自增id结果后来需求变了要按业务流水号查虽然给流水号建了普通索引但每次查询都要回表性能就是上不去。这是我下面要展开的一个重点。2.2 二级索引与回表为什么查询会多一次IO除聚簇索引之外其他索引统称二级索引也叫辅助索引或非聚簇索引。二级索引的叶子节点存的不是整行数据而是索引列的值 主键值。这就引出一个非常关键的概念——回表。假设表结构是id主键name上有普通索引你执行SELECT * FROM user WHERE name 张三MySQL先走name索引的B树找到name张三的叶子节点取出对应的主键id再用这个id去聚簇索引的B树里查拿到完整行数据。第二步就是回表一次额外的索引查找。数据量大、命中行数多的时候回表代价相当可观。我第一次意识到这个问题是在优化一个报表查询时发现明明已经走了索引但执行计划里的rows是一万两千行实际耗时却是全表扫描的好几倍。用EXPLAIN分析发现就是回表导致大量随机IO。2.3 覆盖索引让查询绕开回表既然回表贵那就想办法不回去。如果二级索引的叶子节点里已经包含了查询需要的所有列就不需要再回表了。这个状态叫覆盖索引Covering IndexEXPLAIN里对应的是Extra字段出现Using index。举个例子SELECT id, name FROM user WHERE name 张三如果name上有索引那么索引叶子节点里有name和id这两个字段完全够用MySQL直接在索引树上返回结果不回表查询快很多。这也是我在设计索引时几乎必用的技巧分析业务SQL需要用到的所有字段能塞进联合索引就尽量塞进去。当然凡事有度字段太多会导致索引体积暴涨写入变慢这需要在性能和数据维护成本之间做权衡。3. 联合索引与最左前缀面试和实战都绕不开的考点3.1 联合索引到底是怎么排序的联合索引就是把多个列合在一起建一个B树。比如建了(a, b)联合索引B树里先按a排序a相同再按b排序。这里有个隐藏的规则必须吃透——最左前缀原则MySQL在查询时只有从联合索引的最左边第一列开始连续匹配这个索引才能被有效利用。换句话说(a, b)联合索引能支持WHERE a ?和WHERE a ? AND b ?但单独执行WHERE b ?时这个索引基本用不上因为B树的b列只有在a值相同时才是有序的。很多人记不住我提供一个更直观的理解方式把联合索引想象成一本英文字典(a, b)就是“先按首字母再按第二个字母”排序。想用第二个字母查单词字典帮不上忙。这个类比在给同事讲索引时屡试不爽。3.2 题目来了where a and b 应该怎么建索引这个命题太常见了网上一搜一大把几乎每个后端开发都遇到过某张表经常执行WHERE a ? AND b ?该怎么建索引我的答案分情况但核心结论是优先建联合索引(a, b)而不是分别建两个单列索引。原因有几层第一联合索引能一站式定位只查一棵B树就能命中条件。两个单列索引时MySQL需要先用索引a找到一批主键再用索引b找另一批主键然后做交集Index Merge Intersection这个过程在绝大多数场景下不如联合索引快。第二联合索引天然携带a的排序关系后续如果查询变成WHERE a ? ORDER BY b根本不需要额外的排序操作直接通过索引顺序读出来就行能省一次filesort。第三(a, b)联合索引可以覆盖WHERE a ?的场景但反过来两个单列索引没法做到这样合并利用。不过有一个前提要明确比如a列区分度极低只有几个枚举值b列区分度极高并且查询大量按b单独过滤那这时候b单独建一个索引反而比放进联合索引更有价值。这种反直觉的情况我后面会在“常见问题”里举一个真实案例。另外如果a和b都是等值查询条件(a, b)和(b, a)差别不大索引可以任意顺序但如果查询里有一个条件是范围那就得把范围条件放在联合索引的末尾否则后面的列无法利用索引排序。3.3 范围查询、排序与分组对索引的影响联合索引还有几个容易被忽略的细节范围条件后的列天然失效(price, category)联合索引遇到WHERE price 100 AND category foodcategory那一列匹配时索引已经不再有序MySQL只能把price范围内所有行先找出来再过滤category。所以建联合索引时精确等值列放前面范围列放后面。ORDER BY 与 GROUP BY 也能走索引如果排序字段恰好是联合索引的一部分并且满足最左前缀MySQL就不需要额外做filesort直接从索引按序读取。我优化过一个列表页把排序字段加进联合索引后原先3秒的接口直接降到300毫秒。前缀索引对文本类长列比如varchar(255)建索引时可以只取前N个字符建前缀索引体积小很多。但代价是无法用于排序和覆盖索引并且在区分度不够时容易出现过滤不掉的情况。4. 主键索引和唯一索引别再说它们是一样的4.1 物理结构、约束与可空性的差异数据库设计面试题里“主键索引和唯一索引的区别”几乎天天被问。很多人能说出“一个表只能有一个主键多个唯一索引”“主键不能为空唯一索引可以”但落到InnoDB层面这两个东西有更本质的差异物理结构主键索引是聚簇索引整张表的数据按主键排序存储索引即数据数据即索引。唯一索引只是二级索引叶子节点存的还是主键值查询时通常需要回表。约束行为主键除了唯一性还隐式带有NOT NULL约束唯一索引允许NULL而且MySQL里多个NULL在唯一索引中是允许共存的因为NULL不等于NULL。存储与维护因为聚簇索引直接决定了数据在磁盘上的存储顺序插入一个主键值小于当前最大值的记录时InnoDB可能要把后面的数据“挤一挤”移动位置造成页分裂唯一索引作为普通二级索引插入时只需要更新索引树。4.2 使用场景与性能权衡从性能角度上看如果一张表没有显式主键InnoDB会自己生成隐藏rowid做聚簇索引。这时候你再去建唯一索引每次查询都要走唯一索引再回表到隐藏rowid性能比有显式主键差不少。所以我的建议一直是每个表都要有业务无关的自增主键哪怕业务上已经有天然的流水号也最好单独建一张代理主键。唯一索引更适合用在“业务上需要唯一性约束但又不是主键”的列比如用户表的手机号、账号表的登录名这类场景。唯一索引还有一个额外功能在INSERT ... ON DUPLICATE KEY UPDATE或INSERT IGNORE这类语法中用来做冲突判断的依据。下面这个表格把这几个点放在一起对比对比项主键索引唯一索引数量限制每表最多一个每表可多个NULL值不允许允许且可多行NULL索引类型聚簇索引二级索引非聚簇数据存储叶子节点存整行数据叶子节点存主键值查询特性直接定位数据无回表通常需要回表冲突检测可作INSERT ... ON DUPLICATE KEY的依据同样可作依据5. 实操用EXPLAIN把索引设计验证一遍5.1 建索引和删索引的SQL先过一遍最基础的SQL语法平时写业务代码用得最多-- 建普通索引 ALTER TABLE user ADD INDEX idx_name (name); -- 建唯一索引 ALTER TABLE user ADD UNIQUE KEY uk_mobile (mobile); -- 建联合索引 ALTER TABLE order ADD INDEX idx_user_status (user_id, status); -- 建前缀索引取name字段前10个字符 ALTER TABLE user ADD INDEX idx_name_prefix (name(10)); -- 删索引 DROP INDEX idx_name ON user;有几个细节很少有人提一是MySQL 8.0之后支持降序索引建索引时可以用(user_id ASC, create_time DESC)这对ORDER BY create_time DESC这种高频排序场景非常有用二是MySQL不支持函数索引8.0以下所以对列做函数运算会导致索引失效这个坑我下面会重点讲。5.2 EXPLAIN执行计划逐字段解读索引建没建对不能靠猜要看执行计划。最常用的就是EXPLAINEXPLAIN SELECT id, name FROM user WHERE name 张三;执行结果里重点看这几个字段type访问类型按效率从高到低大致是systemconsteq_refrefrangeindexALL。见到ALL就说明没走索引全表扫描了。key实际用到的索引名。如果为NULL说明没走任何索引。rows预估需要扫描的行数。这个数字直接反映索引的过滤能力。Extra额外信息看到Using filesort说明排序没走索引看到Using temporary说明存在临时表看到Using index说明当前查询走了覆盖索引。我一般优化SQL的步骤很简单先EXPLAIN看type是不是ALL看rows估算行数再对照Extra里的告警信息。这套流程走两三次基本能把一个索引设计得明明白白。5.3 实战案例订单表的索引设计全过程拿一个典型的电商订单表举例表结构简化成CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(64) NOT NULL, status tinyint NOT NULL, create_time datetime NOT NULL, amount decimal(10,2) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB;业务上有三个高频SQL-- 查询1用户订单列表带状态过滤按时间倒序 SELECT * FROM order_info WHERE user_id ? AND status ? ORDER BY create_time DESC LIMIT 20; -- 查询2按订单号查订单 SELECT * FROM order_info WHERE order_no ?; -- 查询3财务统计某时间段的金额 SELECT SUM(amount) FROM order_info WHERE create_time ? AND create_time ?;分析过程是这样的查询2已经有唯一索引uk_order_no覆盖不用动。查询1的核心是user_id status等值过滤再加上create_time排序。最理想的联合索引是ALTER TABLE order_info ADD INDEX idx_user_status_time (user_id, status, create_time DESC);user_id和status是等值条件放前面create_time是排序字段放最后正好利用索引自带的有序性替代filesort。DESC方向在MySQL 8.0里可以直接声明旧版本不加也行MySQL会自动反向扫描。查询3是范围查询聚簇索引按主键排序跟create_time没关系所以要单独建一个索引ALTER TABLE order_info ADD INDEX idx_create_time (create_time);建完索引后用EXPLAIN逐一验证type从表里的ALL变成ref和range查询1的Extra里不再出现Using filesort这就算设计达标了。我还顺手测过覆盖索引的优化效果把查询1改成SELECT id, user_id, status, create_time配合idx_user_status_timeExtra直接显示Using index完全省掉回表。不过这个优化在SELECT *面前没法落地所以项目规范里我一般要求频繁查询的列表页按需取列别动不动就整行取回。6. 索引失效场景与排查技巧实录6.1 索引失效场景速查表这部分是实战里最容易踩的坑我把它做成一个速查表建议大家收藏场景示例写法原因与应对LIKE 前导通配符WHERE name LIKE %三索引按前缀排序%在前没法二分查找应改为前导通配或考虑全文本检索对列做函数运算WHERE DATE(create_time) 2024-01-01索引存的是原始值函数破坏了列可比性应改成范围查询create_time ? AND create_time ?隐式类型转换WHERE mobile 13800000000mobile是varchar数字常量触发隐式转换索引失效应保持类型一致OR 连接非索引列WHERE id 1 OR name 张三OR两边只有一个有索引MySQL可能放弃索引应改为UNION或保证OR两端都有索引NOT IN / NOT EXISTSWHERE id NOT IN (1,2,3)负向查询难以使用索引改写法或接受全表扫描联合索引不满足最左前缀WHERE b 1索引为(a,b)不满足最左前缀无法使用联合索引范围查询后的列WHERE a 100 AND b 1索引为(a,b)范围条件后列的排序失效设计时把等值列放前面范围列放后面索引列参与运算WHERE id 1 5索引列变形无法匹配应改写成WHERE id 4这张表是我自己排查慢查询时的第一参考几乎每次都能在里面找到对应的坑。6.2 一次“明明有索引却不走索引”的排障记录说个真实案例。有段时间我们订单查询接口偶尔超时SQL长这样SELECT * FROM order_info WHERE order_no 202403051234567890123456789;order_no字段上明明有唯一索引EXPLAIN却显示typeALL全表扫描。一开始我也懵索引在啊怎么不走后来用SHOW CREATE TABLE order_info看了下表结构发现order_no类型是bigint但JAVA代码里拼接订单号时用了字符串传进来的参数是202403051234567890123456789这种带引号的长字符串。问题就出在这里MySQL把字符串和bigint比较时进行了隐式类型转换把索引列那一侧转型成字符串再比较于是索引就失效了。修复方法也简单代码里把参数强制转成Long或者SQL里明确CAST。排查过程不复杂但如果没有EXPLAIN这个工具这种问题能让人排查一整天。从那之后我凡是处理慢查询第一步永远是先EXPLAIN绝对不靠肉眼猜。另外还有一个常见案例是关于低区分度索引的。有一次我们给一张统计表的status字段单独建了索引结果EXPLAIN出来依然是ALL。原因很简单status只有0和1两个值分布各占一半MySQL优化器判断走索引还要回表一大半数据还不如顺序扫一下全表划算。这也是我在前面强调的低区分度列不要单独建索引。如果要过滤状态尽量把它放在联合索引靠前的位置配合其他等值条件一起生效。6.3 建索引前必须想清楚的几个问题最后把我的建索引经验浓缩成几个判断标准每次建索引前都问自己一遍这个索引到底服务哪些SQL建索引前先收集慢语句搞清楚高频查询的WHERE、ORDER BY、GROUP BY都有哪些列再设计联合索引而不是给每个列都单独建索引。区分度够不够列里不同值太少索引几乎没有过滤效果。区分度太低的列不建议单独建索引。会不会导致写入变慢每多一个索引INSERT/UPDATE/DELETE都要多维护一棵B树。一张表索引总数我一般控制在5个以内超过的都要重新评估。这个索引能覆盖查询的所有列吗如果查询只需要少量字段优先考虑覆盖索引省去回表。联合索引的顺序对不对等值条件放前面范围条件放后面排序字段结合场景放在恰当位置。索引这块的知识点并不难难的是把原理和实战结合起来知道自己为什么建这个索引、建完之后怎么验证。真遇到慢查询先别急着加索引把SQL和执行计划摆出来一步一步分析大多数问题都能在这个思路里找到答案。