1. 索引到底在解决什么问题先搞清楚它为什么存在大概每个DBA或后端开发第一次被慢查询折磨都是从一句“这表怎么这么慢”开始的。前两年我接手过一个订单系统订单表不到三百万行按用户ID查历史订单的时候愣是花了三秒多。主管过来看了一眼就说了句这表是不是没建索引。我那会儿对MySQL索引的理解基本停留在“加个索引能快一点”的阶段后来把那段时间踩的坑、翻的源码、调的慢查询串起来才算是把索引这回事真正理顺了。1.1 没有索引时MySQL是怎么“硬翻”数据的在给表加索引之前MySQL只能老老实实做全表扫描。所谓全表扫描就是从头到尾把整张表的数据页读一遍逐行判断where条件是否成立。InnoDB存储引擎的数据是按页存放的默认每页16KB。假设订单表每行数据大概1KB一个数据页大概能放十几行。三百万行数据大约要占用二十多万个数据页全表扫描就意味着要把这二十多万个数据页从头到尾读一遍。磁盘随机读和顺序读的差距非常大这种量级的IO操作跑出几秒延迟一点都不奇怪。用生活里的事来类比全表扫描就像一本几百页的书没有目录也没有页码你要找一个具体的句子只能从第一页开始一页一页翻。假如运气好你要找的内容在第一页那速度很快可大多数情况下内容在第三百页你就得翻三百次。更扎心的是翻的时候每一页还得停下来读一读判断这页是不是你要找的。数据量小的时候几百行甚至几千行全表扫描的代价可以忽略不计。可一旦数据量上到百万级、千万级这种线性查找的耗时就会变得完全不可接受这就是索引要出场的原因。1.2 索引是什么一份和书目录同等性质的数据结构索引本质上是一份独立于数据的结构化目录它保存着某个或某几个字段的值以及这些值对应的数据行所在的位置在InnoDB中通常是主键值。索引中的键值会按照一定的顺序排列好这样查找某个值时就可以通过树形结构快速定位而不必扫描整张表。比如给order表的user_id字段建了一个索引MySQL会额外维护一棵树树的每个节点都包含了排好序的user_id值以及对应的主键值。当你执行select * from orders where user_id 12345时MySQL会先到这棵索引树里按照B树的查找算法从根节点出发一路比较大小很快定位到user_id等于12345的记录再顺着节点拿到主键去主键索引树里取完整行数据。整个过程涉及的磁盘IO次数大概就是树的层数次也就是个位数和全表扫描几万次磁盘读相比速度提升可以说是降维打击。索引就是拿空间换时间。它额外占用磁盘空间存储索引结构写入数据时还需要同步维护索引所以插入、更新、删除操作的代价会增加。这也是为什么索引不是越多越好更不是随便哪个字段都值得建索引。读多写少的表可以放心大胆建索引写频繁的表就要精打细算。1.3 索引生效的本质减少IO次数同时把随机IO尽可能变成顺序IO很多人对索引的认知停留在“加了就快”这个层面真到了排查慢查询的时候却发现有些SQL加了索引也没用或者优化器压根没走索引。这时候需要理解索引生效的本质到底是什么。索引能提速核心是两条。第一通过B树的结构把查找范围从“全表”缩小到“从根到叶子的一条路径”这直接减少了参与比较和读取的数据页数量也就减少了磁盘IO次数。第二索引中键值有序排列范围查询时只需找到起始位置然后借助叶子节点之间的链表顺序向后扫描即可这又把随机IO转化成了顺序IO。反过来说如果你在索引列上做了函数运算、类型转换破坏了键值的有序性或者查询条件用不上索引的最左前缀那就算有索引优化器也会放弃它老老实实回头做全表扫描。理解了这两条底层逻辑后面所有的“为什么这样建索引”“为什么这个索引失效”就都能自己推断出来而不是靠死记硬背。2. MySQL的索引结构B树凭什么成为绝对主流聊到索引绕不开的就是底层数据结构。MySQL其实支持好几种索引结构但InnoDB存储引擎的默认且最核心的索引结构永远都是B树。这个选择背后是磁盘IO特性、查询模式、存储成本等多方面博弈的结果。2.1 哈希索引和普通二叉树为什么在数据库里吃不开先说说哈希索引。哈希索引通过哈希函数把键值映射到固定位置的桶里等值查询非常快时间复杂度接近O(1)。但它有个致命短板一旦涉及范围查询比如where age 20 and age 30哈希索引就完全用不上。因为哈希函数把键值打散之后原有的大小顺序彻底丢失你没办法在哈希结构里做区间扫描。排序也类似联合索引部分匹配更是不可能因为哈希的对象是整个键值拼接结果。普通二叉树包括平衡二叉树AVL树和红黑树倒是能维护键值有序性。但问题出在“高”上。一棵二叉树的每个节点只能有两个子节点存一千万条数据树的高度就有二十多层。数据库的节点是存在磁盘上的每向下走一层就要做一次磁盘IO二十多次IO对于一个查询来说太慢了。红黑树虽然有自平衡能力避免了退化成链表但高度依然太高并不适合数据库这种大量数据落盘的场景。2.2 B树的节点设计矮胖、多路、有序还带链表B树最大的优势就是矮胖。它是一棵多路搜索树每个节点可以容纳很多个键值和子指针。InnoDB默认一个节点就是一页16KB在16KB的空间里索引键值可以存很多个。具体算一笔账就明白了。假设主键是bigint类型占8字节相邻节点指针占6字节那么一个非叶子节点大约能存储16 * 1024 / (8 6)约等于1170个键值。两层非叶子节点就可以索引到1170乘以1170也就是约136万条记录的位置。到了叶子节点一页16KB假设一行数据1KB一页能放约16行数据。三层B树的总容量就是136万乘以16达到了两千多万行。也就是说对于一张两千万行的表任何一次主键查找只需要从根节点出发经过三次磁盘IO就能定位到目标行。这个数量级意味着什么普通二叉树需要二十多次磁盘IO才能找到的数据B树三次就搞定了。而且B树的叶子节点之间还通过双向链表连在一起一旦查找到范围起点后续的数据都是沿着链表顺序往后读非常契合范围查询和排序扫描的场景。为什么不直接用B树呢B树的所有节点都存储数据单个节点能容纳的键值数就少了树的高度相应增加。更关键的是做范围查询的时候B树需要在中序遍历上反复横跳局部性很差。B树则不同非叶子节点只存索引键不存数据单页能容纳的键更多树更矮叶子节点既存数据又有链表串联范围查询就是一段顺序扫描。两个特性叠加让B树完胜B树。再加上数据库查询天然会有大量范围查询和排序需求MySQL选择B树作为主要索引结构是完全合理的。2.3 InnoDB聚簇索引与非聚簇索引结构决定了访问方式B树是索引的通用结构但InnoDB在使用B树时还有一套特殊的组织方式也就是聚簇索引。InnoDB的表数据本身就是按主键B树组织的这棵主键索引树的叶子节点直接存放了完整的数据行这种索引就叫聚簇索引。所以InnoDB表里的每一行数据实际上是主键索引树上的一个叶子节点。没有显式定义主键时InnoDB会选择第一个非空唯一索引作为聚簇索引再没有的话它会生成一个隐藏的6字节rowid作为聚簇索引键。除了主键索引之外的索引统称二级索引也叫非聚簇索引。二级索引的叶子节点并不存放完整数据行只存放索引列的值加上主键值。查询的时候先通过二级索引找到对应的主键再拿着这个主键回主键索引树里查一遍才能拿到完整数据行这个过程叫回表。举个例子。有一张用户表user(id, name, age, email)主键是id在name字段上建了二级索引。执行select * from user where name 张三时MySQL先走name索引树找到name等于张三的叶子节点取出主键id然后再回到主键索引树里定位到id对应的完整行返回给用户。这里有个宝贵的优化空间如果查询的字段正好全都在二级索引里就不需要回表这种场景叫覆盖索引。比如上面例子里执行select name from user where name 张三因为name索引的叶子节点本身就有name和主键id两个字段查询需要的name字段已经躺在索引里了MySQL可以直接返回省掉一次回表IO。理解了聚簇索引和二级索引的关系你就能明白为什么“建索引时尽量包含查询需要的字段”这条经验是有底层依据的而不是别人随口说的口诀。3. 索引分类全解从功能维度到物理结构一张表缕清全部类型索引的分类有多种维度。按功能逻辑分有主键索引、唯一索引、普通索引、联合索引、全文索引、空间索引按存储结构分有B树索引、哈希索引、全文索引、空间索引按数据组织方式分又分为聚簇索引、非聚簇索引、覆盖索引。很多人一说索引分类就说主键索引、唯一索引其实这只是按功能分的一层容易遗漏物理存储层面的差异。3.1 按功能逻辑分类六种索引的定位与创建方式先看最常用的一组分类。下面这张表可以帮你快速了解每种索引的定位和典型创建语句索引类型特点数量限制创建示例主键索引唯一且不允许为空InnoDB中聚簇索引一个表只能有一个ALTER TABLE user ADD PRIMARY KEY (id)唯一索引列值唯一但允许多个NULL可多个CREATE UNIQUE INDEX uk_email ON user(email)普通索引单纯加速查询无唯一性约束可多个CREATE INDEX idx_name ON user(name)联合索引多个字段组合成一个索引有左前缀规则可多个CREATE INDEX idx_name_age ON user(name, age)全文索引针对大文本字段做全文检索可多个CREATE FULLTEXT INDEX ft_content ON article(content)空间索引针对空间数据类型GEOMETRY等可多个CREATE SPATIAL INDEX sp_idx ON location(gps)主键索引是InnoDB中性能最高的索引因为它的叶子节点就是数据行本身按主键查询的时候直接走聚簇索引一次IO就能拿到数据。唯一索引经常用在业务上要保证不重复的字段上比如用户表的手机号、邮箱。普通索引就是最常见的加速索引可以在where、order by、join的字段上建立。联合索引需要特别留意。它是多个字段组合成的一个索引比如idx_name_age(name, age)这个索引内部先按name排序name相同再按age排序。联合索引的核心规则是最左前缀原则也就是查询条件里必须包含最左边的字段name才能用上这个索引直接查age而没查name这个索引就帮不上忙。关于联合索引的详细机制我在下一章专门展开。全文索引和空间索引属于特化场景。全文索引用于match ... against这种全文检索查询早期仅MyISAM支持InnoDB是从MySQL 5.6开始支持全文索引的中文场景一般会配合分词器使用用得相对少。空间索引用于地理位置和几何图形数据的查询平时开发中不常见知道有这个东西就行。3.2 按存储结构分类BTree、Hash、Fulltext、R-Tree第二个维度是按物理存储结构划分。InnoDB的索引默认是BTree结构这在前一章已经详细聊过。但MySQL还有一些使用其他存储结构的索引。Hash索引在等值查询上性能极高但它不支持范围查询和排序。InnoDB本身没有直接开放Hash索引Memory存储引擎支持Hash索引另外InnoDB还有一个自适应哈希索引的特性它会根据热点数据的查询模式自动在内存中为某些索引页建立哈希索引用来加速等值匹配但这是内部机制DBA不需要也无法手动创建。等值查询频繁且数据量可控的场景下Memory表的Hash索引可以作为一种选择不过用得不多。Fulltext全文索引在内部实现上使用了倒排索引的原理而R-Tree空间索引则更擅长多维数据的范围查询比如在地图上画一个矩形框找出落在框内的POI点。这些结构各有所长日常开发中真正需要频繁打交道的主要还是BTree索引其余了解即可。3.3 按数据组织方式分类聚簇索引、非聚簇索引与覆盖索引第三个维度回到了InnoDB的数据组织方式。聚簇索引和非聚簇索引的区别前文讲过了这里补充一个实操层面的认知覆盖索引严格来说不是一种索引类型而是一种查询优化策略。如果一个二级索引包含了查询需要的全部字段MySQL就不必回表那么可以说这个查询被这个二级索引“覆盖”了。覆盖索引的价值在于减少一次回表IO。在数据量大、二级索引走完还需要回表的场景下回表可能是随机IO代价不小。所以设计索引时把查询最常用的几个字段塞进同一个联合索引里让查询尽可能命中覆盖索引是一个非常重要的优化手段。但要注意索引字段越多存储空间和写入维护成本越高不能为了覆盖而塞一堆用不上的字段还是要结合实际查询模式来权衡。三套分类方式并不冲突而是从不同角度描述同一个索引。比如CREATE INDEX idx_name_age ON user(name, age)从功能看它是联合索引从存储结构看它是BTree索引从数据组织方式看它属于非聚簇索引当查询只需要name、age时它又变成了覆盖索引。搞清楚这三个维度的交叉关系后面建索引的时候思路会清晰很多。4. 联合索引与最左前缀原则区别“建了索引”和“用上索引”联合索引是目前实战中被误解最多、坑最多的领域。很多开发知道联合索引这个词却不知道它的排序规则和匹配规则于是经常出现“明明建了联合索引查询还是很慢”的现象。这一章专门把联合索引吃透。4.1 where条件里同时有a和b到底该怎么建索引先回到一个高频搜索问题where a and b应该怎么建索引。这个问题的答案取决于a和b的查询模式和数据特点但有一个基础原则先记住尽量用联合索引而不是分别建两个单列索引。假设有订单表orders(id, user_id, status, create_time)最常见的查询是select * from orders where user_id ? and status ?。如果分别在user_id和status上各建一个单列索引MySQL优化器一般只会选择其中一个最优的索引去定位另一个条件则拿到数据后再进行过滤这就可能导致大量无效回表。比如user_id区分度高走了user_id索引查到100条记录然后status在这100条里过滤那还好如果status区分度低走status索引查出来几万条再逐条回表过滤user_id就非常浪费了。更稳妥的做法是建立一个联合索引(user_id, status)。联合索引内部先按user_id排序user_id相同再按status排序。查询时先通过user_id精确定位到一批记录再在这个小范围内用status做二次定位整个过程只走一棵索引树不需要回表过滤效率自然高很多。关于两个字段的顺序也不是随便定的一般遵循两条经验区分度高的放前面等值查询条件优先放前面。区分度高意味着这个字段能在B树中更快地把范围缩小。还是上面那个例子user_id的区分度显然远高于status所以把它放前面。如果两个字段都是等值匹配那覆盖查询条件更频繁的那个字段放前面即可。4.2 最左前缀原则联合索引的匹配规则与边界联合索引(a, b, c)匹配规则是可以单独用a可以同时用a和b也可以同时用a、b、c但完全跳过一个字段去用后面的字段是不行的。这就是最左前缀原则。不理解这个规则的根源在于没搞清联合索引内部的排序方式。联合索引先按第一个字段排序因此第一个字段相同的记录会聚在一起当第一个字段相同再按第二个字段排序。所以单独查第二个字段时索引树中接second字段在全局上是无序的B树无法借助它做快速定位。具体看实例。索引为idx(a, b, c)查询条件where a 1可以用整个索引走最左前缀a。where a 1 and b 2可以用索引a和b都生效。where a 1 and b 2 and c 3完全覆盖最理想。where b 2不走索引因为跳过了a。where a 1 and c 3a能走索引c用不上因为中间断了b。where a 1 and b 2a走了索引做范围扫描但b用不上因为范围之后无法再按b精确定位。上面最后一条涉及另一个关键点范围查询之后的列无法继续走索引。B树做范围查找时会在范围区间内顺序扫描区间内的数据虽然按b排序但b保持相对有序的前提是a完全相同。当a是一个范围而不是确定值时b的排序就失去了全局意义所以b不能再用来加速。实际建联合索引时要尽可能把范围查询的字段放在联合索引的末尾。4.3 索引失效的常见场景实战中的自查清单联合索引建好了还要防止用不上。MySQL中索引失效的坑非常多把高频的几个整理出来遇到慢查询时可以对照排查对索引列使用函数或表达式比如where YEAR(create_time) 2024。索引中保存的是原始的create_time值不是算出来的年份优化器在绝大多数情况下无法直接使用B树查找只能全表扫描。正确写法是改成范围查询where create_time 2024-01-01 and create_time 2025-01-01。隐式类型转换。比如手机号字段是varchar查询时写成where phone 13800138000MySQL会把索引列的字符串转成数字去比较导致索引失效。解决方法是字符串字段查询时加引号保证类型一致。前导模糊查询比如like %abc。B树依靠键值的顺序性做前缀匹配前导通配符会破坏前缀有序性索引无法定位。但like abc%可以用索引因为它还是前缀匹配。使用or连接多个条件且其中某个条件没有索引时整个查询可能放弃索引走全表扫描。优化器需要对or两边的结果做合并如果一边没有索引全表扫描可能比两条路径合并后更快。not in、、!这类否定条件在有索引的情况下优化器也可能选择全表扫描要看预计扫描行数与表总行数的比例。表特别小的时候全表扫描反而更快优化器会自行判断。自查的手段也很简单给目标SQL加上EXPLAIN前缀看输出里的type和key字段。type从好到差大致是system、const、eq_ref、ref、range、index、ALL看到ALL基本就是全表扫描key为NULL说明没有可用的索引。从实际经验看一个联合索引设计得好不好拿上面这份清单对照着检查几遍基本心里就有数了。建索引不是一劳永逸每一条慢查询都是一个回头审视的机会。5. 建索引的实战思路场景判断、成本权衡、验证手段讲了这么多原理和分类最终都要落到一个问题上一张表到底怎么建索引才算合理。这一章分享我的实操流程和一些容易忽略的细节。5.1 哪些字段该建索引哪些字段建了也白建建索引之前先判断字段是否满足高频查询的基本特征。一般来说下面这些情况适合建索引where条件中频繁出现的字段尤其是等值条件。order by和group by涉及的字段。索引本身有序可以让排序避免额外文件排序这也是很多慢排序查询的优化突破口。join操作中连接表的关联字段比如根据user_id关联用户表。区分度高的字段。区分度可以用count(distinct col) / count(*)衡量接近1说明每个值都很少重复索引筛选效果极好。区分度低于某个阈值比如10%的字段建索引可能收效甚微。反面场景同样值得留意。性别、状态这类只有两三个取值的字段单独建索引通常意义不大因为查询结果里大量数据都符合条件MySQL优化器大概率会觉得“我直接扫全表然后逐行过滤”反而更快。频繁更新的字段也不适合建索引每次更新都需要同步修改索引结构写入成本会成倍上升。数据量很小的表比如几千行同样不必建索引全表扫描可能比走索引加回表更快。大文本字段如text类型不能在普通索引上做完整索引一般用前缀索引或者全文索引。给新手一个建议先拿慢查询日志说话而不是逮着字段就加索引。把线上慢查询捞出来找出高频查询条件再针对这些条件做联合索引设计。没有慢查询压力的情况下不要凭空给表增加索引负担。5.2 用EXPLAIN验证索引而不是“感觉建了会快”索引加完之后光看SQL跑得快不快还不够更准确的验证方式是用EXPLAIN看执行计划。判断标准围绕几个关键列列名关注点type至少要达到range级别最好到ref或constpossible_keys理论上可能用到的索引列表key实际选中的索引为NULL说明没走索引key_len使用的索引字节数越长说明用到的索引列越多rows预估扫描行数越小越好Extra出现Using filesort、Using temporary说明还有优化空间出现Using index说明命中了覆盖索引看一个具体例子。有一张用户表在(name, age)上建了联合索引执行EXPLAIN SELECT name, age FROM user WHERE name 张三;正常的执行计划里type应该是refkey对应联合索引rows很小Extra会出现Using index因为name和age都在索引里不需要回表。如果同样的SQL里改成where age 25丢掉name条件执行计划会变成type ALLkey NULL这就是最左前缀失效的典型信号。线上验证还有一个容易被忽略的点key_len。它能够反推索引实际用了几个字段。比如联合索引(name, age)name是varchar(50)且是utf8mb4字符集那么name字段最多占用50 * 4 2 202字节如果key_len刚好是202说明只用了name这个字段如果key_len变成了202加age占用的字节说明两个字段都生效了。做联合索引优化时这个细节非常有用能确认索引是不是被“完整使用”。5.3 维护索引冗余索引、碎片、统计信息建索引不是一锤子买卖长期的维护同样重要。先谈冗余索引。假设已经有了联合索引(a, b)又单独建了索引(a)那么(a)就是冗余索引因为联合索引的最左前缀a已经覆盖了单独a的查询需求。冗余索引纯粹浪费空间还增加写入负担。用SHOW INDEX FROM table可以查看表上的所有索引排查一下是否有重复项能删就删。索引碎片也是个常见的后患。频繁的增删改会让B树的叶子节点出现间隙或页分裂形成碎片导致索引页利用率下降、IO增多。遇到这种情况可以执行ALTER TABLE xxx ENGINEInnoDB重建表或者OPTIMIZE TABLE xxx整理碎片。不过要在业务低峰期执行因为这类操作会锁表和重建。最后是统计信息。优化器决定是否走索引依赖表的统计信息。如果统计信息过期优化器可能做出错误选择明明有索引却选了全表扫描。通常ANALYZE TABLE xxx可以刷新统计信息。平时不建议频繁执行但遇到执行计划异常走偏的情况可以在安全窗口做一次。索引的维护其实是一种很朴素的取舍把存储和写入成本花在最值得加速的查询路径上。核心逻辑就是我前几章反复强调的那几条——减少IO、保持有序、避免回表、慎用联合索引左前缀。只要这条主线清晰具体场景下的建索引决策就不会跑偏。我个人在做索引设计和优化时有一个固定流程先接慢查询日志再逐个分析查询模式画出高频where条件然后按区分度排出联合索引字段顺序建完索引后立刻用EXPLAIN核验执行计划确认type、key、rows、Extra都达到预期。这套流程跑下来绝大多数慢查询都能在半小时内解决。希望这篇文章能帮你在索引这条路上少走几条弯路。