1. 为什么索引这一关必须过1.1 一个慢查询引发的问题先说我自己的经历。刚接触数据库那会儿我维护着一张用户订单表数据量大概在百万级。某天业务方反馈订单查询页面特别慢打开要等五六秒。我把那条SQL拎出来一看条件里用了WHERE user_id 12345 AND create_time 2024-01-01表上并没有任何索引。当时我对索引的理解还停留在建了就行的阶段随口说了一句加个索引呗结果加了索引之后查询时间从5秒多降到了几十毫秒。那一刻我才意识到索引不是数据库的附属品而是整个查询性能体系的基石。后来我花了很长时间系统整理索引和数据库设计相关的知识踩过不少坑也总结出了一套自己的方法论。这篇文章就是我整理笔记的精华版适合刚接触MySQL不久、或者已经会用但说不清原理的开发者阅读。读懂它你能独立判断一张表该怎么设计、索引该怎么建以及线上慢查询到底该怎么排查。1.2 索引到底在解决什么问题索引的本质是一个加速查找的数据结构。你可以把它类比成书的目录没有目录你要找一个知识点就得从第一页翻到最后一页这叫全表扫描有了目录你直接翻到对应章节效率自然高得多。MySQL里最常用的索引底层结构是B树InnoDB存储引擎也是围绕B树来组织数据的。为什么不用二叉树或者哈希表原因是索引要应对的是磁盘IO场景——数据量一大内存装不下必须读磁盘而磁盘IO的开销远远大于内存计算。B树的每一个节点可以存储很多键值树的高度通常只有三四层意味着查询任意一条记录最多只需要三四次磁盘IO这在工程上是非常划算的。理解了这个前提你就能想明白很多所谓的索引优化技巧背后的逻辑而不是死记硬背。2. B树到底是怎么加速的2.1 从链表到B树的演进逻辑很多人刚学B树的时候会困惑为什么不直接用二叉搜索树原因在于树的高度。二叉搜索树在数据量大的时候会变得很高比如100万条数据二叉树的理想高度是20层但B树只要3层左右就能存下同样的数据量。树每高一层查询时可能就要多一次磁盘IO这在高并发场景下是致命的。B树还有一个关键设计只有叶子节点才存放真正的数据行或数据行的主键非叶子节点只存放索引键用来指路。这样每个非叶子节点能容纳的键数量大大增加树变得更矮更宽。同时叶子节点之间通过双向指针连接形成了一个有序链表这让范围查询BETWEEN、、这类操作变得非常高效——找到起点之后顺着链表往后走就行。还有一个容易被忽略的设计B树是自平衡的。插入和删除数据之后树会自动进行节点的分裂或合并始终保持所有叶子节点在同一层。这保证了查询时间复杂度稳定在O(log n)级别不会因为数据分布不均匀而退化。2.2 聚簇索引和非聚簇索引的区别InnoDB里表本身就是一个按主键组织的B树这个结构叫聚簇索引。聚簇索引的叶子节点直接存放整行数据也就是说找到了主键就等于找到了这条记录的所有字段。正因如此InnoDB要求每张表必须有主键。如果你没显式指定它会优先找第一个非空唯一索引实在没有就自动生成一个隐藏的列。非聚簇索引也叫二级索引则是另外一棵B树它的叶子节点存放的是索引列的值加上主键值而不是完整的行数据。这意味着通过二级索引查询时往往需要回表——先用二级索引找到主键再通过主键去聚簇索引里取完整数据。举个例子假设users表上有age字段的索引执行SELECT * FROM users WHERE age 25MySQL会先在二级索引树里找到所有age25对应的主键然后逐个回表查完整数据。这里就引出了两个重要的优化方向一个是覆盖索引一个是索引下推。2.3 回表、覆盖索引和索引下推覆盖索引指的是查询所需的字段全都包含在索引里这样就不用回表了。比如你有联合索引(user_id, create_time)执行SELECT create_time FROM orders WHERE user_id 5因为create_time就在索引的叶子节点里直接取出即可省掉了回表的开销。这是性能优化里性价比非常高的手段实际开发中我经常用把高频查询需要的字段塞进联合索引这个思路来优化慢查询。索引下推是MySQL 5.6引入的优化名字听起来高大上原理其实很简单在没有索引下推之前二级索引查出来的所有符合条件的记录都要回表然后才能在服务层过滤其他的条件有了索引下推之后可以在索引层面就先过滤掉一部分不满足条件的记录减少回表次数。比如联合索引(name, age)查询WHERE name 张三 AND age 20存储引擎会先在索引内判断age 20只对满足条件的记录回表这样IO量大大减少。3. 索引设计的实战取舍3.1 哪些列值得建索引不是每个字段都适合建索引建多了反而有害。我从实际经验出发总结了几个判断标准第一出现在WHERE、JOIN、ORDER BY、GROUP BY子句中的列是建索引的首选。这些地方是查询的过滤和排序条件索引能直接加速这些操作。而经常出现在SELECT后面但不参与过滤的列一般不单独建索引最多作为覆盖索引的一部分加进去。第二区分度高的列优先。所谓区分度指的是某列不同值的数量占记录总数的比例。比如性别列只有男女两个值区分度极低建了索引之后每个值对应的记录可能是全表的一半索引扫描的成本甚至比全表扫描还高。而订单号、身份证号这类列区分度高索引筛选效果明显。第三频繁更新的列要慎重。索引本身是需要维护的每次INSERT和UPDATE都要同步修改索引结构索引越多写放大越严重。如果一个列被频繁更新而它又不常作为查询条件就别给它建索引。3.2 联合索引的列顺序问题联合索引是实战中用得最多、也最容易出错的点。它的核心规律是最左前缀原则MySQL会从联合索引的第一个列开始依次匹配查询条件如果跳过了某一列后面的列就无法使用索引。这里有一个常见的误解很多人以为最左前缀指的是SQL语句里条件的书写顺序其实不是。它指的是索引定义时列的从左到右顺序SQL里条件写反了也没关系优化器会自己调整。举个例子索引定义为(a, b, c)那下面几种情况能用索引WHERE a 1、WHERE a 1 AND b 2、WHERE a 1 AND b 2 AND c 3。但WHERE b 2和WHERE c 3无法走这个索引因为它们跳过了最左边的a。那联合索引里的列顺序怎么排我的习惯是等值查询的列放在前面范围查询的列放在后面。原因是等值查询命中的记录少可以先缩小范围再用后面的列做范围过滤索引利用率更高。如果反过来范围条件在前面后面的等值条件就用不上了。3.3 唯一索引、前缀索引与其他进阶用法唯一索引和普通索引的区别在于唯一索引保证了列值的唯一性同时MySQL在插入和更新时会做唯一性校验所以它天然带有约束作用。业务上有唯一性要求的字段比如手机号、用户编号应该用唯一索引而不是普通索引这既保证了数据正确性又提升了查询性能。前缀索引是针对大字段的优化方案。比如TEXT或很长的VARCHAR列直接建完整索引会占用很大的空间索引树也会变得很宽。解决办法是只取前面一部分字符建立索引比如INDEX (email(10))。但前缀索引有代价它无法用于ORDER BY和覆盖索引而且可能因为前缀相同而筛选出更多记录。选择前缀长度时可以用SELECT COUNT(DISTINCT LEFT(email, N)) / COUNT(*)这样的方式去比较不同长度的区分度直到结果稳定为止。还有一种容易被忽视的用法是降序索引。MySQL 8.0开始真正支持降序索引之前的版本虽然语法上能写DESC但物理上还是升序存储。如果你有ORDER BY a ASC, b DESC这类查询MySQL 8.0下可以在联合索引里分别定义列的排序方向避免额外的文件排序。4. 最容易踩的索引坑4.1 索引失效的典型场景我把线上排查慢查询时遇到的索引失效场景整理成了一张清单每一条都对应着真实踩过的坑对索引列使用函数会让索引失效。比如WHERE DATE(create_time) 2024-01-01MySQL无法直接利用create_time上的索引因为函数运算改变了列的值。解决办法是改写为范围条件WHERE create_time 2024-01-01 AND create_time 2024-01-02。对索引列做隐式类型转换也会失效。比如索引列是VARCHAR类型但查询条件里传的是数字WHERE phone 13800138000MySQL会把索引列转成数字再比较索引自然用不上。这个坑很隐蔽因为看起来语法正确执行计划里却显示typeALL全表扫描。使用LIKE模糊匹配时只有前缀匹配能用索引%开头的是不行的。比如WHERE name LIKE 张%可以用索引WHERE name LIKE %三就完全走不了索引。OR连接的条件也是重灾区。如果WHERE a 1 OR b 2中a有索引、b没有索引MySQL通常不会走索引而是选择全表扫描。解决思路是把OR拆成两个查询用UNION ALL合并或者给b也加上索引。4.2 执行计划怎么看排查索引问题时最直接的工具就是EXPLAIN。每次写完SQL觉得慢第一件事就是执行EXPLAIN看它的输出列。重点看几个字段type表示访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。其中ALL是全表扫描index是扫描整个索引树这两种通常需要优化key表示实际命中的索引名如果它是NULL就是说优化器没可用索引问题很大rows是预估扫描行数数值越大说明越慢Extra里的Using filesort表示查询结果需要额外的文件排序Using temporary表示使用了临时表这两个都是性能隐患。我看过很多开发者的优化方式就是加个索引试试。试当然可以但要基于执行计划的判断搞清楚查询到底慢在哪一步是没走到索引还是走到了但回表太多还是排序代价太高。对症下药才能一次改到位。4.3 冗余索引和重复索引的识别表上线一段时间后索引会慢慢积累尤其是多人协作的项目今天加一个(a, b)明天看查询慢又加一个(a)实际上后者完全能被前者覆盖。MySQL在建表时不会检查这种冗余它只会机械地维护所有索引每一笔写操作都会同步更新损耗就这么白白产生了。我的清理方法是定期执行SHOW INDEX FROM 表名把同一张表的索引列列出来逐个人工判断凡是某个索引的最左前缀列和另一个索引完全一致基本可以判定为冗余。比如idx_age和idx_age_nameidx_age就是多余的。当然删索引之前要确认没有历史SQL依赖它稳妥的做法是在低峰期先观察一段时间或者利用慢日志统计各索引的实际使用频率。5. 数据库设计的核心原则5.1 范式设计从第一范式到第三范式索引优化解决的是查询怎么写的问题数据库设计解决的是表怎么建的问题。两者相辅相成设计不合理后面建再多索引也兜不住。设计关系型数据库表时最核心的理论基础是范式。第一范式要求每列的原子性也就是一个字段只存一个值不能出现兴趣爱好跑步、游泳、摄影这种逗号拼接的字符串存法第二范式要求消除部分依赖表里所有非主键字段必须完全依赖主键而不是只依赖主键的一部分这个主要针对联合主键的情况第三范式要求消除传递依赖非主键字段之间不能存在依赖关系。贴近实际地说范式设计的目的是避免数据冗余和更新异常。比如把订单表和用户表拆开用户地址只存在用户表里订单表通过user_id关联这样用户改地址时只需要改一处不会出现改了订单里的地址忘了改用户表地址的尴尬局面。但范式也不是越高越好。过度拆分会导致查询时需要大量JOIN而JOIN恰恰是数据库性能的消耗大户。所以在真实的业务场景里我会在第三范式的基础上做有限度的反范式设计。5.2 反范式设计的度在哪里反范式最常见的操作是冗余存储。比如订单列表页需要展示用户的手机号和收货地址如果严格按第三范式设计每次查询都要JOIN用户表。当并发大、数据量多的时候JOIN的代价很高。一个务实做法是在订单表里冗余一个receiver_phone字段下单时从用户表带过来查询时直接取不用再关联。但冗余是有代价的它意味着同一份数据在多个表里存在一旦用户修改手机号订单表里的旧值不会自动更新。所以反范式设计必须用在只读不经常变的场景或者允许保留历史快照的场景。订单里的收货地址就是一个经典例子——用户下单时的地址是业务快照后续改地址不该影响历史订单这种场景冗余反而更合理。我的原则是需要频繁联表查询的高频热数据优先考虑冗余但一定要在字段注释里写清楚来源和同步策略避免后来维护的人一头雾水。5.3 数据类型选型和主键设计数据类型选型看起来基础实际影响深远。原则只有一个能用小类型就不用大类型。能用INT别用BIGINT能用DATETIME别用VARCHAR存时间能用DECIMAL别用FLOAT存金额。具体来说整数类型里TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别占1、2、3、4、8字节。比如状态字段取值只有0到10TINYINT就够没必要上INT。金额字段必须用DECIMAL因为FLOAT和DOUBLE是浮点存储存在精度问题算钱差一分都是事故。时间字段推荐DATETIME作为默认选择如果需要精确到微秒就用DATETIME(6)时区敏感的用TIMESTAMP但要注意2038年问题。字符串方面定长的用CHAR变长的用VARCHAR还需要设置合理的VARCHAR长度而不是随手写255。主键设计是我看过最多次翻车的领域。最重要的一条主键必须是单调递增的这样B树插入数据时总是追加在最后不会触发频繁的页分裂。如果使用UUID作为主键因为它完全随机每次插入都可能落在已有数据中间导致页分裂、数据碎片化严重写入性能急剧下降。业务需要UUID做标识的话正确做法是额外建立一个带唯一索引的uuid列主键仍然用自增BIGINT。5.4 大表拆分的两种思路当单表数据量达到千万级别甚至更高时即使索引建得再合理写入性能和锁竞争也会逐渐恶化。这时候就要考虑拆分。拆分的思路有两大类垂直拆分和水平拆分。垂直拆分是把一张表按业务字段拆成多张表比如把orders表拆成orders_basic和orders_ext后者放一些低频访问的大字段这样单行数据的体积变小一页能容纳更多行扫描效率更高。水平拆分是把同一张表的数据按某个规则分到多张表里常见的有按时间分表、按用户ID取模分表。水平拆分带来的问题很现实跨表查询、分页排序、事务一致性都要额外处理。所以拆表的时机要慎重我通常建议先用单表加合理索引扛到千万级配合归档冷数据实在扛不住了再考虑分表。在分表框架不成熟的情况下过早分表只会让系统复杂度暴涨收益却未必明显。6. 一次完整的慢查询优化实战6.1 发现问题与定位阶段前面讲了不少理论我用一个真实的优化案例把这些串起来。背景是某项目的消息记录表messages数据量约800万行主要查询是按用户ID和会话ID拉取最近的聊天记录。线上反馈打开聊天窗口经常要白屏两三秒慢日志里也确实抓到了这条SQL。我的第一步是拿到完整SQL然后执行EXPLAIN。结果让我很意外表的user_id上有索引session_id上也有索引但执行计划却显示typeALL、rows800万完全没有用上索引。还有一个细节是Extra里出现了Using filesort说明排序也是额外做的。问题出在SQL的写法上。查询语句是SELECT * FROM messages WHERE user_id 123 AND session_id 456 ORDER BY msg_time DESC LIMIT 20看起来两个条件都有索引但MySQL只能选一个索引然后对结果逐条回表过滤另一个条件。它估算直接全表扫描还更便宜就选择了全表扫。6.2 制定方案与验证结果修复方案很明确用一个联合索引把两个过滤条件合并起来。我在(user_id, session_id)上建了联合索引但这一次特意加上了msg_time做成(user_id, session_id, msg_time)。为什么要带上msg_time因为ORDER BY msg_time DESC LIMIT 20意味着只需要取每个分组里最新的20条如果msg_time在联合索引里MySQL可以从索引的最后往前取20条记录既解决了排序问题又天然满足LIMIT的需求。建完索引后再跑EXPLAINtype变成了refrows降到了几千Using filesort消失了。实际响应时间从2.8秒降到了30毫秒左右立竿见影。这次优化让我总结了一条经验给联合索引加尾巴列来解决排序和分页问题效果远好于单独建一个排序字段的索引。类似的还有GROUP BY场景把分组字段和聚合字段一起放进联合索引通常也能收获同样的惊喜。6.3 优化后的长期维护优化完成不代表一劳永逸。数据量继续增长后我又遇到了新问题同样的联合索引在数据量达到1500万行时查询虽然不回表排序了但索引本身也变大冷数据的缓存命中率下降查询开始变慢。这时候单纯靠索引已经不够我选择了按月归档老数据把3个月前的消息迁到历史表主表只保留热数据查询性能立刻恢复。我还给这个表加了一个审计流程每周扫一次慢日志把耗时超过1秒的SQL统一收集逐个分析执行计划判断是索引问题、数据分布问题还是SQL写法问题。这个习惯坚持下来后线上很少再出现突然的性能恶化因为问题总在萌芽阶段就被发现了。7. 一些工具和习惯上的补充建议关于索引管理我日常依赖这几个手段SHOW INDEX FROM查看表索引概况EXPLAIN分析具体SQLEXPLAIN ANALYZEMySQL 8.0支持直接看实际执行耗时和行数以及performance_schema或慢日志来定位高频慢查询。如果你所在团队接入了监控平台记得把慢查询数量的告警阈值设低一点宁可多收几个告警也别漏掉真正的性能事故。有一个很容易忽略的点是字符集和排序规则的统一。如果两张表关联字段的字符集不同JOIN时索引也会失效因为MySQL需要对字符集做隐式转换。建库建表时统一使用utf8mb4字符集和同一种排序规则可以避免这类隐蔽问题。最后说说索引和数据设计这件事的学习路径。我的建议是先动手验证不要只看文档。随便建一张百万行的测试表自己插一些用户、订单数据然后用EXPLAIN测试各种SQL写法对比type和rows的变化。亲手跑一遍之后你会发现很多概念比如回表、覆盖索引、页分裂都不再是抽象名词而是看得见摸得着的执行过程。我至今还记得第一次亲手把一个全表扫描的查询优化到毫秒级的那种感觉。数据库设计没有银弹它考验的是对数据分布、业务特征和底层原理的综合理解。希望这篇笔记能帮你少走一些我走过的弯路。