这几天抽空整理MySQL索引相关的学习笔记对应课程第115-119讲越整理越觉得索引这个概念是绕不开的核心。很多同学学会了增删改查、学会了连表一到性能优化、面试问答就卡在索引上本质原因是没有把索引的底层原理和实际场景串起来。索引不是背几个结论就行的东西它背后是一套完整的数据结构选择逻辑、存储引擎实现差异和运维层面的取舍。这篇笔记我会结合自己的理解把B树、聚簇索引、覆盖索引、最左前缀这些概念掰开揉碎了讲清楚再补充实际创建索引时踩过的坑和排查方法适合正在入门MySQL、准备面试或者工作中经常被慢查询折磨的开发同学参考。1. 从数据结构说起为什么MySQL偏偏选了B树1.1 哈希表、二叉树、B树各自的取舍要说清楚MySQL索引必须先回答一个问题索引到底是什么数据结构估计不少人第一反应是索引就是一棵树但树也有很多种为什么主流存储引擎InnoDB选择的是B树而不是其他结构我把几种常见数据结构放在一起对比过各自适配的场景完全不同。哈希表是最直观的一种。等值查询速度极快O(1)复杂度但哈希表最大的问题是无法处理范围查询。比如你要查id大于100的所有记录哈希表只能一个个遍历拉出来判断索引优势完全发挥不了。另外哈希冲突时还得处理链地址法磁盘IO的随机性也高所以哈希索引更多是配合自适应哈希索引AHI在特定场景下做加速不会作为主索引结构。二叉树呢搜索效率是O(logn)但存在一个致命问题如果插入的数据是有序的二叉树会退化成一条链表查询退化成O(n)。平衡二叉树AVL和红黑树虽然通过旋转维持平衡但树的层数仍然会随着数据量增长而加深每一层对应一次磁盘IO几百万数据就能让树高到10层以上IO次数太多性能就崩了。B树和B树都是多路搜索树一个节点可以存放多个key树高大幅下降。但B树和B树有个关键差别B树的每个节点都能存放数据或者数据地址B树的所有数据都在叶子节点非叶子节点只存key。这个差别带来的直接收益是——B树的非叶子节点可以存放更多的key树更矮更宽IO次数更少而且叶子节点之间通过双向链表串联天然支持范围扫描、排序、分页等高频操作。1.2 B树的数据组织方式可以这样理解B树它是MySQL面向磁盘IO优化后的产物。MySQL数据存在磁盘上读取最小单位是页默认16KB树的每一层节点存放在不同的磁盘页中。树每下降一层就多一次磁盘IO所以树高直接决定了查询性能。InnoDB默认页面大小16KB假设bigint类型主键占用8字节再加指针6字节大概14字节一个非叶子节点能存大约16KB/14B≈1170个key。三层B树能存储大约1170×1170×16两千万条以上的记录也就是说两千万行数据的表从根节点到叶子节点只需要三次磁盘IO这就是B树恐怖的工程效率。再聊聊一个有些颠覆认知的事InnoDB的主键索引和数据行是绑在一起的并不是索引文件和数据文件分离。这就是所谓的聚簇索引。InnoDB表必有且仅有一个聚簇索引主键就是聚簇索引的keyB树的叶子节点直接存放完整的数据行。如果你建表时没有显式指定主键InnoDB会选择一个非空唯一索引作为聚簇索引如果连唯一索引都没有它会在内部生成一个隐藏的rowid作为聚簇索引。聚簇索引带来的直接好处是通过主键查询时拿到叶子节点就等于拿到了整行数据不需要额外跳转。坏处也有非主键索引二级索引的叶子节点存的是主键值而非完整行数据。所以用非主键索引查询时MySQL先扫描二级索引拿到主键值再回到聚簇索引里按主键查一次这个动作就叫回表。对比一下MyISAM就更容易理解了。MyISAM的索引文件和数据文件是分离的索引B树的叶子节点存放的是数据行的磁盘地址即使通过主键查询也需要根据地址去数据文件中找所以MyISAM本质上全是非聚簇索引。这也是为什么InnoDB在并发、事务场景下比MyISAM更稳但在纯查询场景两者各有优劣。2. 五种索引形态面试被问烂了的概念到底有什么区别2.1 主键索引、唯一索引、普通索引不能混为一谈很多刚接触索引的同学最容易犯的错就是把主键索引和唯一索引当成一回事。它们确实都要求列值不重复但主键索引是聚簇索引直接组织数据行的物理存储结构一张表只能有一个主键唯一索引属于二级索引只保证逻辑上数据不重复不影响数据行的物理排列。一张表的主键是唯一的但你可以建无数个唯一索引。普通索引就更好理解了纯粹为了加速查询不加任何约束。日常开发中最常见的就是给WHERE条件后面的高频筛选字段建普通索引。比如订单表按user_id查订单列表这个字段既不是主键也不要求唯一直接建普通索引即可。全文索引在MySQL 5.7之后支持中文也比较成熟了但注意它走的是倒排索引原理和我们前面讲的B树完全是两个路子。全文索引适合大文本字段如文章内容、商品描述的模糊搜索使用MATCH...AGAINST语法查询。平时开发中用得不多但面试中问到全文搜索原理时值得展开说一下。2.2 联合索引与最左前缀这是最容易翻车的地方联合索引也叫复合索引就是在一张表的多个列上建一个索引。比如建一个(a, b, c)联合索引它的B树中key是拼接的(a, b, c)。排序规则是先按a排序a相同再按b排序b相同再按c排序。这就是最左前缀原则的根源。最左前缀原则可以这么理解联合索引(a, b, c)等价于建立了三类索引——单列a索引、(a, b)联合索引、(a, b, c)联合索引。查询条件里如果没带a只带b和c那么这个索引用不上因为B树的排序顺序决定了没有a就无法定位b的记录区间。举个例子用户表建了(name, age, city)联合索引。查询WHERE name张三 AND age25能命中索引WHERE age25 AND city北京 就看不了这个索引WHERE name张三 AND city北京只能用到name这一列的索引city部分因为中间跳过了age也走不了。这就是我经常在面试中听到有人背最左前缀但完全不会实际分析的典型场景。这里还有个进阶概念叫索引下推ICP。在MySQL 5.6之前联合索引(a,b)如果查询条件是a1 AND b LIKE 2%存储引擎只能根据a条件定位记录拿到记录后再在服务层过滤b。有了索引下推存储引擎层在读取索引数据时就直接用b的条件做过滤减少了回表次数和返回给服务层的行数。这在InnoDB里默认是开启的优化器会自动判断是否使用ICP。2.3 覆盖索引减少回表的核心武器回表是二级索引查询中最大的性能开销。如果查询的列全部包含在二级索引的字段中那么二级索引的B树里就能拿到所有需要的数据不需要再回表。这就是覆盖索引。举一个实际高频场景订单表有联合索引(user_id, create_time)业务经常要查某个用户的最近10条下单时间。如果SQL写成SELECT user_id, create_time FROM orders WHERE user_id123 ORDER BY create_time DESC LIMIT 10因为查询列user_id和create_time都在索引中MySQL直接用索引数据就能返回完全避免回表。但如果多查一列order_amount由于这个列不在索引中就必须每条记录回表一次性能差距可能达到十倍级别。设计覆盖索引时有一个取舍思路不要为了覆盖索引把所有字段都塞进索引索引字段越多占用空间越大写入时维护成本越高。常用的做法是让高频查询的字段进入索引把select频率低但更新频率高的字段留在索引之外。3. 索引创建实操从建表到验证全流程3.1 创建索引的几种方式和适用场景我把常用的索引创建SQL整理成了一张对照表方便查阅操作方式SQL示例适用场景建表时定义PRIMARY KEY(id), INDEX idx_name(name)表结构设计阶段一次性建好后补主键ALTER TABLE t ADD PRIMARY KEY(id)建表时漏了主键后建普通索引ALTER TABLE t ADD INDEX idx_name(name)上线后发现慢查询需要加索引后建唯一索引ALTER TABLE t ADD UNIQUE KEY idx_email(email)需要约束字段唯一性CREATE INDEX申明CREATE INDEX idx_name ON t(name)独立创建索引语义清晰删除索引DROP INDEX idx_name ON t索引冗余时清理关于哪种方式更好我更推荐在CREATE TABLE时就把主键、唯一约束一并定义清楚。因为大表后加索引时MySQL会扫描全表数据构建索引结构如果表里的数据量已经到了千万级别在线加索引可能导致长时间的锁表和性能抖动。虽然MySQL 8.0支持了在线DDL不过对于核心业务表仍然建议在低峰期执行并做好计划。3.2 怎么选择索引列不是所有列都适合加索引索引不是建得越多越好这是一个新手特别容易走入的误区。我看到过一张十几列的业务表有人上来就给七八个字段都建了索引结果写入慢得惊人。索引的原则可以归纳成以下几条第一优先选择WHERE子句中频繁出现、且区分度高的列。区分度指的是某列的取值种类数占记录总数的比例。比如gender字段只有男女两种值区分度太低建索引后数据分布太集中优化器可能会放弃索引直接全表扫描。而order_no这种每行几乎都不一样的字段区分度极高建立索引效果立竿见影。第二注意字段长度。如果某个字段是VARCHAR(255)且内容很长整列做索引会导致索引体积膨胀。这时可以考虑前缀索引用字段的前N个字符建索引。比如用户邮箱字段前缀索引的写法是ALTER TABLE user ADD INDEX idx_email(email(20))这样索引体积小查询性能也不错。但要注意前缀索引无法用于ORDER BY和覆盖索引扫描。第三控制单表索引数量。业内比较认可的经验是单表单列索引控制在5个以内联合索引也算一个。索引本质上是拿空间换时间每个索引在写入时都要更新B树代价是真实存在的。这里再说一下索引基数的概念SHOW INDEX FROM table_name时能看到Cardinality字段它表示索引中不同值的估算数量。如果Cardinality相对表行数占比很小说明这个索引的区分度低。MySQL优化器在判定是否走索引时就会参考这个值。实际操作中如果你发现一个SQL一直没用上某个索引可以先查一下Cardinality如果太低可能压根就不适合建索引。3.3 通过SHOW INDEX和EXPLAIN验证索引是否生效创建完索引后第一步用SHOW INDEX FROM table_name查看索引信息确认索引是否创建成功、Seq_in_index顺序是否正确。对于联合索引Seq_in_index表示该列在索引中的位置顺序错了最左前缀可就全乱了。第二步就是EXPLAIN。我几乎每个慢查询都会用EXPLAIN分析一遍它的输出字段很多但真正看懂关键几列就够了字段含义常见值type访问类型system, const, eq_ref, ref, range, index, ALLkey实际使用的索引名索引名或NULLrows预计扫描的行数数字Extra附加信息Using index, Using where, Using temporary 等type列是重中之重它的性能排序大致是system const eq_ref ref range index ALL。看到ALL就说明是全表扫描这是慢查询最大的元凶。type为ref说明使用了非唯一索引或前缀索引查找range表示使用了索引做范围查询这些都是健康状态。而index类型是指遍历了整棵索引树虽然比ALL好一点但仍然不等于高效查询需要警惕。再看Extra列几个值得注意的值Using index说明是覆盖索引Using where说明在存储引擎层加载记录后又用WHERE条件做了过滤如果type是ALL加Using where那基本就是一次全表扫描后再过滤非常危险。Using temporary则说明SQL执行过程中创建了临时表常见于GROUP BY或DISTINCT操作通常伴随性能问题。Using filesort表示需要额外的排序步骤也要注意。4. 索引失效与慢查询排查实践中跳过的坑4.1 那些让索引白建的常见场景我整理了一份索引失效场景速查表基本覆盖日常开发中90%的问题场景示例为什么失效对索引列使用函数WHERE DATE(create_time)2023-01-01索引存储的是原始值函数运算后无法与索引值比较隐式类型转换WHERE phone13800138000phone为varcharMySQL会把字符串转成数字比较索引失效LIKE前置通配符WHERE name LIKE %张无法用B树定位前缀OR连接非索引列WHERE id1 OR status2MySQL可能转为全表扫描联合索引顺序错误联合索引(a,b)WHERE b1不满足最左前缀索引列参与运算WHERE num*210同函数问题类似第一个场景最经典。很多同学喜欢在日期字段上写DATE(create_time)2023-01-01这其实是个反模式。应该改成create_time 2023-01-01 AND create_time 2023-01-02。前者无法用上create_time上的索引后者可以用范围查询性能天差地别。再说隐式类型转换。如果表中phone字段是VARCHAR类型查询时写成WHERE phone13800138000MySQL内部会先把phone转成数字再比较等于对索引列做了隐式函数处理索引失效。解决方式是把查询参数改成字符串WHERE phone13800138000。关于LIKE前置通配符大多数人知道%张%会让索引失效但对张%却可以用上索引。原因是B树叶子节点是按字典序排列的张%能确定一个区间范围而%张无法确定起始位置只能全量扫描。OR连接也值得一提。当SQL写成WHERE id1 OR status2时即使id和status上都有独立索引MySQL在5.0之前基本放弃索引扫描。现在优化器有时候会把OR改写成UNION但更稳妥的方式还是用UNION ALL拆分或者用IN替代ORWHERE id IN (1) OR status IN (2)。4.2 EXPLAIN实战一个真实慢查询的诊断过程拿我之前调优过的一个订单查询SQL举例SELECT order_no, user_id, amount, status, create_time FROM orders WHERE user_id 10001 AND status 1 ORDER BY create_time DESC LIMIT 20;当时orders表有600万行user_id有普通索引查询耗时1.8秒。用EXPLAIN一看type显示refkey显示idx_user_idrows预估5万行Extra显示Using filesort。问题很清晰types字段INDEX idx_user_id(user_id, status, create_time)覆盖了WHERE条件和排序字段。第一次修改是给(user_id, status, create_time)建联合索引这次EXPLAIN里type变成refkey用上联合索引rows从5万降到2万Extra里不再出现Using filesort耗时降到0.3秒。第二次优化是既然业务高频查询只要这几列而联合索引已经包含user_id、status、create_time再看到select里还有order_no、amount就果断把这两列也放进联合索引末尾变成(user_id, status, create_time, order_no, amount)。EXPLAIN最理想的状态出现了Using index覆盖索引生效行数降到预估2万行以内耗时降到0.08秒。虽然索引文件稍大了一点但对于日查询量百万级的接口来说这顿操作值回票价。这个案例想说明两件事第一EXPLAIN是索引优化的航标每改完一次索引必须重新EXPLAIN看效果第二覆盖索引的优化空间往往被低估很多慢查询卡就卡在回表上。4.3 为什么有时候加了索引反而变慢还有一种诡异场景明明建了索引查询反而比全表扫描还慢。这通常不是索引的问题而是你没给优化器提供足够好的选择条件。前面说过Cardinality区分度。假设一个状态字段status只有三种值且业务上90%记录都是status1你在这上面建索引查询WHERE status1时优化器一算用索引要回表90万次全表扫描只要扫描100万行然后过滤反而更快。于是它弃用索引走了ALL。另一个场景是数据分布不均STATUS1的记录很少STATUS2的记录很多查询WHERE status2碰巧走了索引但每条都要回表性能反而不如直接全表扫描。解决思路是要么别在低区分度字段上建索引要么用复合索引把高区分度字段放到前面引导优化器。还有一点容易忽略索引碎片。频繁的UPDATE和DELETE操作会让B树的叶子节点产生碎片页利用率下降扫描索引时需要读取更多的页。定期用OPTIMIZE TABLE可以重建索引、回收碎片但这是一项重量级操作一定要在低峰期做而且要评估影响。5. 索引设计的一些长期经验与思路5.1 主键设计对索引的影响主键的选择直接决定聚簇索引结构进而影响所有二级索引的大小。为什么一直强调用自增整型做主键就是因为二级索引的叶子节点存储的是主键值如果主键是UUID这种随机字符串不仅占用32字节空间而且B树在插入时是随机偏移的会导致页面频繁分裂合并写入性能大幅下降。而自增主键插入的数据是顺序追加的几乎不会触发页分裂。业务主键和逻辑主键要区分。我一般会在表中额外添加一个自增id作为代理主键业务上唯一的字段比如订单号再加唯一索引约束。这么做的好处是二级索引叶子节点存的是短小紧凑的自增id索引体积小查询性能好业务字段可以通过唯一索引保证逻辑唯一性。5.2 索引维护与慢查询监控的建议有条件的团队一定要上慢查询日志或性能监控平台。至少开启slow_query_log设置long_query_time1甚至0.5把执行超过1秒的SQL全部抓出来。然后每个慢查询用EXPLAIN分析一遍看type、key、rows、Extra对照我上面的表格定位问题。我个人的排查顺序是这样的先看type是不是ALL如果是立刻检查WHERE条件的字段有没有索引再看Extra有没有Using filesort或Using temporary有的话基本是缺排序索引或联合索引设计不合理然后看key使用情况和rows预估值判断覆盖索引有没有救。需要注意的是大表结构变更时间成本非常高。在千万级行以上的表加索引虽然MySQL 8.0的在线DDL能减少锁表但IO消耗依然巨大可能拖垮主库。常用的方案是在从库上先执行DDL再通过主从切换的方式升级或者用gh-ost这类工具进行在线无锁表变更。新手阶段只要养成变更前备份、变更中监控、变更后检查的习惯就够了。5.3 区分OLTP与OLAP的索引设计最后说一个容易被忽略的点索引设计要和业务访问模式匹配。OLTP在线交易场景的特点是写入频繁、单次查询数据量少索引要小而精尽量用覆盖索引减少回表避免建太多索引影响写入。OLAP分析型场景的特点是大量数据的聚合扫描有时候反而不太依赖B树索引而是靠列存、物化视图、分区裁剪等手段来加速。如果一张表既承担在线查询又承担报表分析我的方案是用读写分离在从库上建更多分析型索引主库保持精简索引保证写入性能。实践下来这套组合拳能解决大部分业务性能压力也比盲目堆索引要高效得多。索引是MySQL性能优化的基石也是从入门到进阶绕不开的核心屏障。我个人在实际排查慢查询时最深的体会是不要凭感觉加索引一定先用EXPLAIN看清执行计划再回头设计索引顺序反了事倍功半。另外索引概念学完不算完找一张几百万行的真实业务表把常见的几类慢查询写出来逐条排查一遍比看十篇教程都管用。建议你顺着笔记中的联合索引、覆盖索引和索引失效三个方向亲手建几个表做几组对比实验自己跑一遍EXPLAIN索引这块才算真正握在手里了。