做后端和数据库的同学几乎没人能绕开MySQL索引这四个字。我最早被索引“教育”是刚工作第一年一张订单表数据量才几十万行一条简单查询没索引的时候跑了1.2秒加了一个普通索引之后变成20毫秒整整六十倍的差距。那一刻我才真正反应过来SQL写得好不好很多时候就取决于索引用得对不对。这篇文章我打算按“全网最详细”的标准来写把索引这件事从头到尾完整梳理一遍从MySQL没有索引时怎么查数据到索引底层为什么是B树到六类索引的区别与创建语法到回表、覆盖索引、索引下推这些进阶概念再到线上最常踩的索引失效坑和加索引的实操姿势。无论你是正在准备面试还是被慢查询日志折磨得头疼这篇都值得花十分钟静下心看完。1. 从慢查询开始索引到底替我们省了哪些成本1.1 没有索引时MySQL是怎么查数据的想象一下你有一张100万行的用户表要查user_name zhangsan。如果表上没有任何索引MySQL能做的就是把表从头到尾扫一遍每一行都读出来再判断user_name等不等于目标值。这个操作叫全表扫描full table scan。全表扫描最大的问题不是CPU开销而是磁盘IO。InnoDB的数据是按页存储的默认每页16KB100万行、每行按1KB算就是100万页。扫描下来要读100万页虽然Buffer Pool能缓存一部分热数据但完全没索引时这种读取很难命中预读大量随机IO会让查询时间直接飙到秒级。你可以在EXPLAIN里看到这种情况type列显示ALLrows列显示估算扫描的行数接近全表。ALL在MySQL的执行计划里是所有访问方式里最慢的没有之一。看到ALL基本就意味着这条SQL该加索引了除非表本身小到全表扫描也就几毫秒。1.2 索引的本质把“顺序查找”变成“树查找”很多人觉得索引是个神秘的东西其实它就是在表之外额外维护的一棵树可以理解成书的目录。没有目录时你想找某一章得从第一页翻到最后一页有目录时你直接翻到对应页码就行这就是索引存在的意义。MySQL里的索引默认是B树数据按照某个或多个列的值排好序存在树里面。当查询条件命中索引时MySQL只要在树里从根节点往下走基本上三次磁盘IO就能定位到目标数据这和全表扫描的百万级IO相比完全不在一个量级。所以索引的作用简单说就是把查找一条数据的时间复杂度从O(n)的全表扫描降到O(log n)的树查找。表行数越多这个差距越明显这也是为什么几十万行的表加了索引能从秒级降到毫秒级。1.3 索引不是免费的空间、写入成本与维护成本聊索引不能只说好处。索引本质上是用“额外的存储空间 写入开销”换“查询速度”。每一棵索引树都要占用磁盘空间而且每次INSERT、UPDATE、DELETEMySQL都要同步维护表上的所有索引树。这意味着索引越多写入就越慢磁盘占用也越高。我见过不少新手把表里每个字段都加上索引结果写入性能掉得厉害。正确的做法是只给高频查询、高频排序、高频关联的字段建索引并且区分度太低的列比如性别、状态枚举不要单独建索引因为查出来一大片优化器很可能还是走全表扫描。提示索引是给查询服务的不是给表“上保险”。加之前先问自己有没有一条真实存在的慢SQL会用到它2. 索引的底层数据结构为什么偏偏是B树2.1 如果只是“查得快”哈希表不香吗先处理一个常见的疑问哈希表的查找复杂度是O(1)比B树的O(log n)还快为什么MySQL默认不用哈希索引因为实际的业务查询几乎从来不只要“精确等值匹配”。user_name zhangsan这种等值查找哈希索引确实很合适但一旦查询变成user_name LIKE zhang%、age 18、ORDER BY create_time哈希表就完全无能为力了。哈希是无序的不支持范围查询也不支持排序更不能利用前缀去匹配。InnoDB其实也有自适应哈希索引但它是引擎内部基于热点数据自动构建的加速结构不是我们手动创建的那个索引。平时能手动创建的哈希索引主要用在Memory引擎上业务中很少直接用到。2.2 红黑树、B树、B树的取舍接下来看看为什么B树成了MySQL的默认选择。二叉搜索树和红黑树理论上也能做索引但问题是树的高度。数据量一大比如1000万行平衡二叉树的高度会到20多层意味着一次查询最多要访问20多个节点每次都涉及磁盘IO性能完全扛不住。B树相比二叉树已经把树高降了很多但B树的每个节点既存key又存数据在16KB的页里能放下的分支数也就是出度有限。出度一低树还是要变高。B树则把数据全部放到叶子节点非叶子节点只存key和指针这样每个节点能容纳非常多的key出度大大增加树变得又矮又胖。B树还有一个杀手锏叶子节点之间用双向指针串成了一个有序链表。范围查询、排序、分页都可以顺着链表顺序读取这对数据库来说太友好了B树就没有这个特性。2.3 B树的一笔账三层能存多少行数据我们来算一笔具体的账看看B树为什么能在3到4层内撑起千万级数据。假设一行数据大小约1KBInnoDB一个页16KB那么叶子节点一页能存16行数据。非叶子节点存的不是数据行而是“索引键 指向子节点的指针”。主键如果是BIGINT占8字节指针按6字节算一条记录占14字节那么一页能放大约1170个16384 / 14 ≈ 1170索引项。这样一棵三层B树根节点有1170个分支第二层有1170 * 1170个节点第三层也就是叶子节点总共有1170 * 1170 * 16 ≈ 2190万行。也就是说一张2000万行的表用BIGINT主键InnoDB一般三到四次磁盘IO就能定位到目标数据。这个效率远超其他树结构也是面试里特别喜欢考的一个计算点。2.4 聚簇索引与非聚簇索引是怎么回事在InnoDB里索引不只是“辅助查找的结构”它和数据存储方式深度绑定。InnoDB的表本身就是一个以主键为key的B树这棵树叫做聚簇索引clustered index它的叶子节点直接存放整行数据。换句话说找到了主键就是找到了这一行数据的物理存储位置。除了主键之外你创建的普通索引、联合索引都叫二级索引或非聚簇索引。二级索引的叶子节点不存整行存的是“索引列的值 主键值”。所以当你用一个二级索引列查询时流程是先到二级索引树里找到主键再回到聚簇索引树里查出整行数据这个动作就叫回表。这里有一个很现实的主键选择问题InnoDB要求表必须有聚簇索引如果你没有显式定义主键它会找一个非空唯一列做主键实在没有就生成一个隐藏的rowid。隐藏rowid对业务完全不可控所以建表时一定要主动设计主键。我更倾向于用自增整数或雪花ID尽量避免用UUID做主键因为UUID无序插入时会频繁触发索引页分裂产生大量碎片写性能和空间利用率都会变差。3. 索引分类与创建语法别再只会建普通索引3.1 六种索引一次性讲清楚MySQL里的索引类型很容易把人绕晕其实站在应用角度看主要就是下面六种。我把它们的核心差异整理成了一张表方便对照。索引类型是否允许重复是否允许NULL典型使用场景普通索引允许允许单纯加速查询没有唯一性要求唯一索引不允许允许手机号、邮箱等需要唯一约束的字段主键索引不允许不允许每张表必须有且只能有一个联合索引允许允许多字段组合查询、排序全文索引允许允许大文本字段的模糊搜索如文章内容空间索引允许允许GIS地理位置数据一般业务用不上补充一个容易混淆的点唯一索引和唯一约束其实是一回事。MySQL里建唯一约束就是创建唯一索引两者没有本质区别只是从“约束”和“索引”两种视角去理解而已。全文索引在InnoDB从5.6版本开始支持但中文环境下需要考虑ngram分词实际业务如果你要做全文搜索我更推荐直接用专门的搜索引擎比如Elasticsearch而不是在MySQL里硬扛。3.2 建索引的三种方式很多同学问建索引到底写哪种语法其实三种方式都能达到目的区别只在于使用时机。建表时就把索引定义好可以保证从第一天起查询就是高效路径表已经存在了就用CREATE INDEX或ALTER TABLE追加。下面这段SQL把三种方式都覆盖了。-- 方式一建表时直接定义 CREATE TABLE user_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_name VARCHAR(64) NOT NULL, email VARCHAR(128) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_email (email), -- 唯一索引/唯一约束 KEY idx_user_name (user_name) -- 普通索引 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 方式二CREATE INDEX 追加 CREATE INDEX idx_user_name ON user_info(user_name); CREATE UNIQUE INDEX uk_email ON user_info(email); -- 方式三ALTER TABLE 追加 ALTER TABLE user_info ADD INDEX idx_user_name (user_name); ALTER TABLE user_info ADD UNIQUE KEY uk_email (email);查看和删除索引也有对应的命令。看索引信息会用SHOW INDEX FROM user_info这个命令会列出索引的字段顺序、唯一性、基数等信息删除索引就是DROP INDEX或ALTER TABLE ... DROP INDEX。SHOW INDEX FROM user_info; DROP INDEX idx_user_name ON user_info;另外特别建议做个索引命名规范普通索引用idx_前缀唯一索引用uk_前缀后面接字段名比如idx_user_name。线上索引一多没有命名规范的人肉排查会非常痛苦。3.3 联合索引理解最左前缀原则建联合索引时字段顺序真的不是随便排的。考虑一个查询WHERE status 1 AND create_time 2024-01-01。如果status区分度低create_time区分度高很多人会纠结该建(status, create_time)还是(create_time, status)这两个组合在B树里的排序逻辑完全不同。比如建立联合索引(a, b, c)底层会先按a排序a相同再按b排序b相同再按c排序。这个排序规则决定了它只能用于匹配(a)、(a,b)、(a,b,c)这几种查询条件。如果你直接查WHERE b 1 AND c 2因为跳过了a索引无法按b定位只能退化成扫描。最左前缀原则说的就是这个。前缀的“最左”指的是查询条件里必须包含索引最左边的列并且不能跳过中间的列。实际上范围查询还会进一步影响后续列的使用比如WHERE a 1 AND b 10 AND c 2联合索引只能把a和b用于定位c那列因为中间隔着范围条件b只能在b确定的范围内做过滤不能再用精确定位。这里再提醒一句不要把最左前缀理解为“必须把第一个字段写在SQL最左边”。优化器会调整where条件的先后顺序真正的关键是查询里有没有用到这个列。比如WHERE b 2 AND a 1只要a参与等值匹配索引照样能用前面写的顺序不影响执行计划。4. 回表、覆盖索引与索引下推EXPLAIN里最常见的三件事4.1 回表为什么会影响性能前面说过二级索引只存主键不存整行数据。如果你执行SELECT * FROM user_info WHERE user_name zhangsanMySQL会先到idx_user_name这棵二级索引树里找到zhangsan对应的主键id再拿这个id回到聚簇索引树里查出完整行。第二次这个动作就是回表。回表不是不行而是每次回表都意味着一次额外的随机IO。如果一条SQL通过索引过滤出几百条记录就要回表几百次性能自然下降。真正要避免的是“大批量回表”比如数据量大、筛选结果集大、而且查询列还不在索引里的场景。我曾经优化过一条线上慢SQL条件命中了索引但SELECT带了十几个字段结果回表几百次耗时始终降不下来。后来把查询要的字段全部收进联合索引做成覆盖索引耗时直接从400毫秒降到20毫秒。这就是回表成本最真实的案例。4.2 覆盖索引让需要查询的字段直接长在索引树上覆盖索引是最实用的优化手段之一。只要索引包含了查询需要的所有列MySQL就不需要回表直接从索引树里拿数据。比如SELECT id, user_name FROM user_info WHERE user_name zhangsan;如果表上有联合索引(user_name, id)或者user_name普通索引InnoDB二级索引天然包含主键id那么这条SQL要的数据id和user_name都在索引树的叶子节点里不需要回表。EXPLAIN里Extra列会显示Using index这就是覆盖索引的标志。日常优化慢查询时我经常用这个思路先看SELECT要哪些列再把这些列和WHERE条件里的列一起设计成联合索引。但要注意覆盖索引不是越多越好索引列越多占用的存储空间和写入成本就越大。通常只对线上高频且关键的SQL做覆盖优化不能所有SELECT都无脑堆列。4.3 索引下推MySQL 5.6之后的隐藏福利索引下推Index Condition PushdownICP是MySQL 5.6引入的优化很多老开发都不知道。它的作用是在存储引擎层直接对索引中包含的字段进行条件过滤减少回表次数。举个例子假设有联合索引(age, city)执行SELECT * FROM user_info WHERE age 18 AND city 杭州;如果没有ICPInnoDB先用age 18从索引里取出一批主键然后全部回到聚簇索引找到整行再由server层去判断city是不是杭州。如果有ICPInnoDB会在索引遍历过程中直接利用索引里的city字段筛掉不满足条件的记录只对剩余记录回表。在筛选率很高的情况下收益非常明显。对应的EXPLAIN Extra列会显示Using index condition。注意ICP并不是万能药它只对索引里已经包含的字段生效如果city不在索引里还是得回表之后才能判断。5. 索引失效的典型场景与排查技巧5.1 一张速查表看全失效场景索引失效是面试高频题也是线上慢查询高发区。我先放一张速查表把常见场景、原因和应对思路一次讲清楚。失效场景原因典型例子建议对索引列使用函数函数改变列原始顺序B树无法利用WHERE LEFT(phone, 3) 138改写为范围条件或冗余一个字段隐式类型转换列类型与比较值类型不一致触发转换WHERE phone 13800138000phone是varchar参数写成字符串13800138000LIKE左模糊通配符在最前无法用前缀匹配WHERE name LIKE %张%改右模糊name LIKE 张%或全文索引OR连接非索引列只要一个条件列没有索引可能整体全表扫描WHERE name a OR status 1status无索引给status加索引或改UNION ALL联合索引不满足最左前缀查询条件缺少最左列或跳过中间列索引(a,b,c)WHERE b 1调整索引列顺序或查询条件范围查询右侧列范围条件打断后续列的精确匹配索引(a,b)WHERE a 1 AND b 2调整索引列顺序或换其他方案对索引列做算术运算表达式让索引失去顺序WHERE age 1 18改写为WHERE age 17负向查询!、、NOT IN容易被优化器放弃WHERE status 1结合数据分布必要时候改造查询IS NOT NULL部分情况下优化器认为扫描范围更大WHERE name IS NOT NULL用EXPLAIN确认再考虑改造字符集不一致关联或比较时列字符集不同引发转换utf8表join utf8mb4表统一使用utf8mb4优化器放弃索引数据量小或命中行占比太高全表扫描更快表只有几百行更新统计信息或用FORCE INDEX兜底需要说明的是失效场景不全是绝对化规则。IS NOT NULL、!这些能不能走索引取决于优化器对数据分布和成本的估算不同版本、不同数据量下行为可能不一样。所以我的习惯是先用这张表做初筛最终判断一律以EXPLAIN结果为准。5.2 两个线上最容易踩的坑第一个坑是隐式类型转换。最经典的是phone字段定义成varchar查询时写成WHERE phone 13800138000漏了单引号。MySQL在比较字符串列和数字时会把字符串转换成数字再比较相当于在索引列上偷偷做了一次类型转换索引自然失效。这个坑特别隐蔽因为单独执行看起来没有任何报错但执行计划已经变了慢就慢在查询计划上。第二个坑是字符集不一致。比如订单表是utf8mb4历史老表是utf8用user_id关联的时候MySQL必须先把一方的字符串转换成另一方字符集才能比较这也会让索引失效或无法高效使用。尤其是join场景一旦驱动表和被驱动表的关联字段字符集不一致明明两边都有索引执行计划却可能提示Using join buffer。线上加新表的时候一定要检查关联字段的字符集和排序规则是否一致。5.3 三分钟快速定位EXPLAIN看哪几列排查索引问题一定会用到EXPLAIN。我拿到一条慢SQL会重点看四列type、key、rows、Extra。type访问类型。性能从好到差大致是 system const eq_ref ref range index ALL。看到ALL基本就是全表扫描index虽然扫了整棵索引树也比ALL好一些。key实际用到的索引。如果为NULL说明这条SQL没有命中索引。rows预估扫描行数。这只是估值但越小越好。Extra如果出现Using filesort、Using temporary通常意味着排序或去重没能用上索引如果出现Using index说明走了覆盖索引出现Using index condition说明用了索引下推。实操命令很简单EXPLAIN SELECT * FROM user_info WHERE user_name zhangsan;如果需要看更详细的代价信息可以加上FORMATJSONEXPLAIN FORMATJSON SELECT * FROM user_info WHERE user_name zhangsan;JSON输出里能看到read_cost、eval_cost这些代价估算在排查优化器为什么不选某个索引时很有用。6. 线上建索引的实操套路与避坑指南6.1 建索引之前先看这三个问题第一个问题是区分度。区分度约等于count(distinct 列) / count(*)比如性别字段可能只有0.5基本没有建索引的价值而user_name、email这种可以到0.99以上。想要快速算区分度跑这条SQL就可以SELECT COUNT(DISTINCT user_name) / COUNT(*) FROM user_info;第二个问题是查询频率。是否真的有一条线上SQL会因为缺这个索引而变慢如果只是“我觉得以后可能有用”那就先别建。索引越少写入越快维护成本越低以后排查问题也越省心。第三个问题是写入放大。索引每多一个每次写入都要多维护一棵树。如果你的业务写多读少加索引更要谨慎读多写少则相对可以放开一些。这个判断没有绝对标准但写多读少的表加了一圈索引写入性能下降往往比想象中明显。6.2 大表加索引的正确姿势大表直接执行ALTER TABLE ADD INDEX在MySQL 8.0里默认走INPLACE算法可以并发DML但整个变更过程依然不是完全没有代价重做日志会增长主从复制会有延迟而且DDL语句在拿元数据锁MDL时还可能阻塞后到的查询。所以“直接执行”只适合中小表。如果数据量是几百万上千万行更稳妥的方式是用专业的在线表结构变更工具比如pt-online-schema-change或gh-ost。这类工具会用触发器或者binlog解析的方式把新索引同步创建到一张临时表期间业务读写不受影响最后通过原子换表完成切换。我自己在千万级表上做过多次整体思路都一样选择业务低峰期执行先确认磁盘空间充足因为工具会复制一份表结构执行过程中持续监控主从延迟延迟过大就暂停或限速执行前准备好回滚方案变更完成后观察一段时间再收工。注意任何时候都不要在生产环境直接对超大表执行长时间阻塞的DDL除非你确认影响可控、有完整预案。6.3 用慢查询日志反推索引需求线上的索引需求不是拍脑袋想出来的最靠谱的来源是慢查询日志。MySQL的慢查询日志可以这样开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;开启后凡是执行时间超过1秒或没用到索引的SQL都会记入慢日志。然后用mysqldumpslow快速汇总mysqldumpslow -s t -t 10 /var/lib/mysql/*-slow.log这个命令会按执行时间排序把最耗时的SQL排在最前面。拿到SQL之后再配合EXPLAIN和上面说的索引失效速查表判断是该加索引、改SQL还是调整已有联合索引的字段顺序。慢日志是索引调优的“需求池”先看需求再做施工才不会做出没人用的索引。最后分享一个我自己的习惯每次新需求上线前我会把SQL里的WHERE条件、ORDER BY、GROUP BY字段全部摘出来按“等值条件在前、范围条件在后、区分度高的列靠左”的方式设计联合索引然后开一条EXPLAIN确认type不是ALL、Extra里没有Using filesort才肯合代码。这套流程不复杂但真的帮我挡掉了好多线上慢查询。索引这东西说到底是给优化器铺路你铺得越规整它跑得就越听话。