很多人对 MySQL 的印象停留在“会写增删改查就行”真到线上慢查询堆积、面试被问索引原理的时候才发现这块知识一直是夹生的。最近我把一套《MySQL 数据库入门到大牛》里索引概念篇又完整过了一遍也就是笔记对应的 115 到 119 这五节很多以前一知半解的点终于串起来了。这篇就当是这一阶段的整理笔记重点把索引的核心概念、底层数据结构、创建索引的实操方法以及日常最容易踩的索引失效坑一次性讲清楚。不管你是刚学完 SQL 基础、准备系统补索引知识的新人还是想理清思路应对面试的开发者应该都能用得上。1. 从一条慢 SQL 开始索引到底在解决什么问题1.1 没有索引时的全表扫描先看最朴素的情况一张表里存了 1000 万行数据你要查where name 张三但name上没有索引。InnoDB 只能从表里第一个数据页开始一页一页往后读逐行判断name是否等于目标值。这个动作叫全表扫描。全表扫描慢在哪不是 CPU 判断那一下慢而是磁盘 IO。InnoDB 的数据页默认是 16KB假设一行数据平均 1KB一个页能装下 16 行左右1000 万行大概要占 60 多万个数据页。如果这些页没有全部缓存在 Buffer Pool 里MySQL 就不得不从磁盘把这些页读进来。机械硬盘一次随机 IO 的延迟往往在 10ms 级别哪怕靠顺序预读打个折几十万次 IO 累积下来也是秒级起步数据量再大一点跑出几十秒的慢查询也不奇怪。这就是为什么很多业务表数据量一涨接口就开始卡。1.2 索引像一本书的目录索引解决的核心问题就是让 MySQL 不用把整本书从头翻到尾。它的本质是一个额外的、排好序的数据结构里面存着索引字段的值和对应行所在的位置或者是主键值。查询时先在这个结构里定位找到目标记录的位置再去数据页里取数。用查字典类比就很好理解没有索引时你从第一页开始一个字一个字找“索引”这个词有索引后你直接翻到拼音目录定位到字母 S 的区域再按音节缩小范围很快就找到了。索引数据结构里用的查找算法是二分查找和 B 树定位时间复杂度从 O(n) 直接降到接近 O(log n)数据量越大收益越明显。1.3 索引不只是提速还有约束和排序的作用很多人以为索引只跟查询有关其实它还有两个隐藏功能第一唯一索引可以用来保证字段值不重复这比应用层先查再插要可靠得多第二索引本身是有序的所以ORDER BY、GROUP BY、DISTINCT这些操作能直接利用索引的顺序省掉临时表和文件排序。我遇到过一位刚入门的朋友为了做唯一校验在业务代码里先select再insert并发一上来就出现重复数据。后来改成在表上建唯一索引让数据库从底层拒绝重复值问题才算根治。这也是索引的一部分价值它不单是性能手段更是数据完整性机制。2. 索引底层的 B 树搞懂这个面试和调优都稳一半2.1 从二叉树到 B 树的演进逻辑索引需要一个高效的数据结构但为什么 MySQL 最终选了 B 树而不是二叉树、红黑树或者哈希表这个演进过程值得理一遍。二叉树在极端情况下会退化成链表查找效率变成 O(n)完全失去索引意义。红黑树虽然能保持平衡但每个节点最多存一个键值树的高度还是太高。数据库里数据量大树高等于查询时访问磁盘的次数树每高一层就多一次磁盘 IO。如果一棵树有 20 层查一次要访问 20 次磁盘照样慢得离谱。B 树解决的是“节点多存一点”的问题一个节点能存多个键和多个子指针树变矮了。但 B 树的非叶子节点也会存数据范围查询时需要在多个层级之间来回跳跃不够高效。B 树则做了两个关键调整所有数据只放在叶子节点非叶子节点只存键值和指针叶子节点之间用双向链表串起来。这样一来非叶子节点能存更多的键树更矮而且范围查询只要顺着叶子链表往后扫就行不用回溯。2.2 InnoDB 的页与 B 树的高度计算InnoDB 选择 B 树还有一个重要原因它能跟 16KB 的数据页完美配合。一个节点就是一个页页是磁盘和内存交换的最小单位读取一个页就是一次 IO。这里有一个很经典的计算。假设主键是bigint占 8 字节指针占 6 字节非叶子节点里一条记录大概 14 字节。一个 16KB 的页能存16 * 1024 / 14 ≈ 1170个键和指针。也就是说B 树第一层能放 1170 个键。第二层每个键对应一个子页也能继续分叉 1170 路两层非叶子节点就能覆盖1170 * 1170 ≈ 136万个叶子页。假设每个叶子页能装 16 行数据三层高的 B 树就能存下136万 * 16 ≈ 2180万行数据。这意味着什么一张 2000 万行左右的表InnoDB 只要三次磁盘 IO 就能定位到目标行第一次读根节点第二次读中间层节点第三次读叶子节点。就算每次 IO 要 10ms三次也就是 30ms 左右。这和全表扫描几十万次 IO 相比完全是两个量级。这也是为什么 B 树能成为 InnoDB 的事实标准。2.3 哈希索引为什么不是默认选择哈希索引做等值查询确实快where a 1这种场景理论上 O(1) 就能命中。InnoDB 里也有自适应哈希索引但它是自动为热点数据建立的加速结构不是你来创建的。真正把 Hash 作为主索引结构的是 Memory 引擎。哈希索引的短板也很明显它不支持范围查询因为哈希值是无序的也不支持排序order by没法用多个索引列的组合条件哈希索引通常要把所有列都拼起来精确匹配才高效。而 B 树既支持等值查询又天然支持范围和排序所以成为绝大多数业务场景下的默认选择。我见过有人问“既然哈希等值这么快为什么不用它做主索引”本质上就是没想清楚业务里很少只有等值查询这一种需求。3. 聚簇索引与二级索引回表和覆盖索引的本质3.1 主键索引就是聚簇索引InnoDB 里表数据本身并不是散乱存的而是按照主键顺序组织在一棵 B 树里。这棵树的叶子节点存的是完整的整行数据它既是索引也是数据文件本身这个结构叫聚簇索引。这里有个容易被忽略的规则聚簇索引其实不一定要你手动指定主键。如果你建表时明确指定了主键InnoDB 就用主键建聚簇索引如果没有主键它会找第一个非空的唯一索引作为聚簇索引如果连唯一索引都没有InnoDB 会隐式生成一个 6 字节的rowid作为主键。换句话说聚簇索引永远存在只是你选不选主键的区别。那为什么一直推荐自增主键因为聚簇索引的数据页按主键顺序排列新插入一行时自增主键总是往后追加不容易触发页分裂如果用随机 UUID 做主键新行可能插到已有页的中间位置导致页分裂、数据挪动写入性能会明显下滑。3.2 二级索引的叶子节点存的是主键值除了聚簇索引其他索引都叫二级索引也叫辅助索引。普通索引、唯一索引、复合索引都是二级索引。二级索引的叶子节点不存整行数据只存索引字段的值和对应主键的值。举个例子表里有个idx_name索引它的 B 树按键值排好序每个叶子节点里存的是name和主键id。查询时先在idx_name这棵 B 树里定位到name对应的主键值然后再拿着主键去聚簇索引里找完整的行。这种聚簇索引和二级索引之间互相配合才构成了 InnoDB 完整的索引体系。3.3 回表和覆盖索引拿着二级索引查到的结果再回聚簇索引里取数据这个过程叫回表。回表本质上是额外的一次 B 树查询如果查询命中了几千行那就需要几千次回表性能损失不可忽视。有一种情况可以完全省掉回表查询需要的字段全部都在二级索引里。比如select id from user where name 张三id和name都已经在idx_name索引里了MySQL 读完索引页直接返回结果不需要再回表。EXPLAIN 的Extra字段里显示Using index就代表这是覆盖索引查询。我在实际开发里有一个习惯对于高频查询尽量让 SQL 里查询的字段和 WHERE 条件字段一起组成复合索引目的就是追求覆盖索引。但也要注意索引字段不是越多越好把一大串无关字段都塞进索引会让索引体积膨胀写入变慢这个平衡后面专门说。3.4 索引下推减少回表的隐形优化MySQL 5.6 开始支持索引下推。简单说以前用复合索引查where name like 张% and age 20存储引擎可能先查出所有满足name like的记录再回表逐一过滤age有了索引下推存储引擎在索引层面就先把age 20这个条件过滤掉再决定哪些记录需要回表。这个优化对慢查询的改善非常明显因为很多时候它能直接减少回表次数。EXPLAIN 里Extra显示Using index condition就是索引下推生效了。理解了这个再看那些“明明把条件写在 SQL 里却感觉没用到索引”的问题就有更全面的视角。4. 建索引不是随便建分类、语法和真实场景4.1 索引分类与创建语法先梳理一下 MySQL 里常见的索引分类普通索引最基本的索引只加速查询不约束唯一性。唯一索引索引列的值必须唯一允许 NULL。既加速查询又能做唯一约束。全文索引针对大文本做关键词搜索MyISAM 时代用得比较多InnoDB 在 MySQL 5.6 之后也支持但一般中文业务场景会有专门的分词方案。复合索引多个字段组合成一个索引是日常建索引最核心的类型。前缀索引对字符串的前几个字符建索引能省空间但会损失一些区分度。创建索引最简单的方式是在建表时声明CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT, UNIQUE KEY uk_name (name), KEY idx_name_age (name, age) ) ENGINEInnoDB;表已经建好的情况可以用ALTER TABLE或CREATE INDEXALTER TABLE user ADD INDEX idx_age (age); CREATE INDEX idx_age ON user (age); CREATE UNIQUE INDEX uk_email ON user (email);4.2 复合索引与最左前缀法则复合索引的灵魂是最左前缀原则。对于索引(a, b, c)MySQL 可以高效使用以下几种形式where a 1where a 1 and b 2where a 1 and b 2 and c 3where a 1 order by b, c但where b 2或者where c 3这种跳过了最左边字段的条件基本用不上这个索引。因为 B 树先按a排序再按b排序最后按c排序少了a就等于失去了一开始的定位依据只能在索引里做全量扫描优化器通常会放弃。还有一点很多人容易搞错SQL 里条件写的顺序不影响最左前缀。where b 2 and a 1和where a 1 and b 2效果一样因为优化器会重排条件顺序它关心的是条件里有没有索引定义左边的字段而不是书写位置。4.3 热搜题where 条件是 a and b索引该怎么建这个高频问题值得单独拉出来说。表里有a和b两个字段查询条件是where a 1 and b 2最合理的做法是建一个复合索引idx_a_b (a, b)而不是在a和b上单独建两个单列索引。为什么如果只有两个单列索引MySQL 的优化器通常只能选择其中一个作为访问路径用另一个索引去过滤变成了额外的回表操作。虽然在 MySQL 5.0 后有index merge能力可以把两个单列索引合并使用但这种优化有一定条件效果也经常不稳定。直接建复合索引是最稳妥、最符合直觉的选择。那字段顺序怎么定一个实用经验是把等值查询的字段放前面把范围查询或需要排序的字段放后面。比如where a 1 and b 10 order by b建(a, b)索引就比(b, a)合理因为a直接定位b既能过滤又能排序。当然实际还要结合字段区分度来调整。4.4 线上加索引的一些实操细节建索引本身就是ALTER TABLE操作在数据量大的表上如果直接用ALTER TABLE ADD INDEX会带来很长一段时间的锁表风险。虽然 MySQL 5.6 起支持 Online DDL很多操作不再全程锁表但大规模 DDL 依然可能造成主从延迟、磁盘 IO 毛刺。我自己的习惯是线上核心表加索引优先用pt-online-schema-change或者gh-ost这类工具在低峰期执行。它们通过临时表拷贝和增量同步的方式让 DDL 对业务的影响降到最低。不要小看这一步我见过有人直接在千万级表上跑ALTER TABLE结果几个小时后慢查询和死锁一起爆发。索引是拿来优化性能的不能让建索引的过程反过来拖垮线上。5. 索引失效的几个常见场景以及 EXPLAIN 排查方法5.1 隐式类型转换、函数处理、like 通配符先说说索引失效最经典的三兄弟。第一是隐式类型转换。比如phone字段的列类型是VARCHARSQL 写成where phone 13812345678这里传了一个数字。MySQL 比较时会把字段值转成数字来比较相当于对列做了CAST索引就失效了。反过来如果字段本身是INT传入字符串123优化器会把字符串参数转成数字这种情况下索引还能用。所以别一听到“隐式转换导致索引失效”就每个字段都套关键看转换是否落在列这一侧。第二是函数处理。where DATE(create_time) 2024-01-01对列使用了函数B 树里存的是原始值无法按函数结果快速定位索引失效。正确写法是create_time 2024-01-01 AND create_time 2024-01-02让条件落在原始值的范围上。第三是LIKE通配符。like abc%可以利用 B 树有序性走前缀匹配但like %abc和like %abc%因为开头不确定无法从索引的某一位置开始搜索只能扫整个索引优化器一般会放弃。所以左侧通配符是索引的大敌。5.2 排序和分组对索引顺序有严格要求ORDER BY能不能走索引同样要看最左前缀和字段顺序。索引(a, b)对order by a、order by a, b是友好的对order by b不友好。如果查询里既有 WHERE 又有 ORDER BY设计复合索引时最好让 WHERE 用到的字段和 ORDER BY 的字段能组成同一个索引前缀避免出现Using filesort。GROUP BY也有类似逻辑。比如按status, date分组统计有(status, date)索引时分组操作可以直接沿着索引顺序累计不再需要临时表。我在做报表查询时特别看重这一点很多慢查询不是死在过滤上而是死在分组和排序上。5.3 优化器主动放弃索引回表成本太高存在索引不代表优化器一定走索引。有时候某个字段的区分度很低比如性别字段只有两个值用索引查出来的记录数可能占全表的 50%优化器算一下发现与其在二级索引里找到大量主键再挨个回表取数据不如干脆做全表扫描顺序读反而更快。这种场景下哪怕你建了索引EXPLAIN 的结果也可能显示typeALL。解决办法不是强行加索引而是先看 SQL 的过滤条件能不能加一个区分度更高的字段进去组合查询或者调整查询范围。很多时候一个设计糟糕的查询无论如何优化索引都救不回来。5.4 看懂 EXPLAIN 的关键列遇到索引问题第一件事永远是开EXPLAIN。我主要关注这四项type访问类型。从好到差大致是system const eq_ref ref range index ALL。看到ALL就要警觉大概率有问题。key实际用到的索引名。如果key为 NULL说明没走索引。rows优化器估计要扫描的行数。一般越小越好但要结合实际情况看不必单纯迷信。Extra这里是重点。Using index表示覆盖索引Using where表示存储引擎返回后再过滤Using filesort表示额外排序Using index condition表示索引下推生效。举个例子EXPLAIN SELECT id FROM user WHERE name 张三 AND age 20;如果key显示用了idx_name_ageExtra里能看到Using index condition说明这个 SQL 的访问路径基本是健康的。6. 索引的代价和冗余索引处理别让优化变成负担6.1 索引是消耗写入性能的很多人只盯着索引带来的查询收益忽略了索引是有代价的。每次INSERT、UPDATE、DELETEInnoDB 不只是改数据页还要同步维护表上的每一个二级索引。索引越多写入路径要做的工作越多B 树还可能发生页分裂、页合并产生随机 IO。一张承载高频写入的表如果为了“看起来每个查询都能走索引”建了七八个索引写性能很容易被拖垮。我做过一个数据同步任务表上有四个索引高峰期批量写入时经常出现锁等待后来把两个低效索引删掉写入延迟立刻掉下来。索引不是多多益善它是用写入成本和存储空间换查询速度的。6.2 冗余索引的典型形态冗余索引最常见的形态是已经有了复合索引(a, b)又单独建了(a)。因为(a, b)已经能覆盖所有需要a的查询单独的(a)就变成纯冗余白白占用空间且拖慢写入。但要注意(a, b)和(b, a)不算互相冗余它们的字段顺序不同能支撑的查询模式也不同。(a, b)和(b)也不算冗余因为(b)支撑where b 1而(a, b)的最左前缀是a对b单独查询无能为力。判断冗余的标准是看新索引的字段是不是已有索引的前缀以及实际查询里有没有真正用到。6.3 怎么发现冗余索引如果数据库是 MySQL 5.7 及以上版本可以直接查系统视图SELECT * FROM sys.schema_redundant_indexes;这个视图会自动分析information_schema里的统计信息把疑似冗余的索引组合列出来。没有 sys 库的话也可以自己写 SQL 从information_schema.STATISTICS里统计每个索引的字段顺序再人工对比。删除索引前一定先做两件事查慢查询日志确认没有 SQL 依赖这个索引在低峰期执行DROP INDEX。我有个原则是“一次只删一个索引线上观察一周再看下一个”这样万一有突发问题回滚路径也清晰。最后分享一个自己摸索出来的习惯建索引之前先开慢查询日志和performance_schema跑一段时间把真实的 Top SQL 拉出来分析然后针对每条核心 SQL 设计索引而不是凭感觉“这个字段以后可能常用先建一个放着”。索引这东西做得精比做得多重要得多。把上面这些基础概念理清楚之后再回头看 115 到 119 这几节课的内容你会发现建索引不再是被动背规则而是能根据 SQL 执行计划和数据分布主动做取舍了。