MySQL 基础教程 - 第八章索引与性能优化基础做了这么多年 MySQL 相关的开发和调优我越来越觉得索引这东西知道怎么建和真正理解为什么这么建完全是两码事。很多初学者对索引的印象就停留在“查询慢了加个索引就好”可真到线上出了问题往往不知道从哪下手。这一章我们专门聊聊索引与性能优化的基础我会把索引的底层原理、常见类型、建索引的核心逻辑、以及怎么用 EXPLAIN 和慢查询日志定位问题都串一遍让不熟悉这块的朋友也能建立一个完整、可用的知识框架。这一章适合谁看呢一类是刚系统学完 SQL 语法、想进一步弄明白“为什么这条 SQL 慢”的初学者另一类是写业务代码多年、平时能跑就行、但一说起执行计划就含糊其辞的开发同学。目标很直接看完之后你能判断一条慢 SQL 到底该不该建索引、该怎么建并且能用工具验证自己的判断而不是靠猜。1. 索引到底解决了什么问题1.1 从全表扫描说起先想一个最基础的问题一条 SELECT 语句在 MySQL 里是怎么找数据的。假设有一张用户表里面有 100 万行记录你现在要执行SELECT * FROM user WHERE age 28。在没有索引的情况下MySQL 只能把主键索引也就是聚簇索引的整棵 B 树叶子节点从头到尾扫一遍逐行检查 age 字段是否等于 28。这个过程叫全表扫描时间复杂度是 O(N)。100 万行数据可能还感觉不明显等数据量到几千万甚至上亿一次全表扫描消耗的磁盘 I/O 和 CPU 资源会让你怀疑人生。索引做的事情说白了就是给字段建立一种“目录结构”让数据库不需要全表遍历就能快速定位到目标行。你可以类比成一本几百页的书没有目录你要找某个知识点就得一页页翻有了目录直接翻到对应章节页码就行了。索引就是数据库里的“目录”。1.2 索引为什么能快B 树结构与二分查找MySQL 默认的存储引擎 InnoDB 使用 B 树作为索引的数据结构。B 树是一种多路平衡查找树它的特点是所有数据都存储在叶子节点并且叶子节点之间通过双向指针连接非叶子节点只存储索引键值和指向子节点的指针树的层数很低通常三层左右就能存储千万级数据。三层是什么概念也就是你查询一条数据最多只需要经历三次磁盘 I/O 左右就能从根节点走到叶子节点。相比之下全表扫描可能要读取几万个数据页。这就是索引能大幅提升查询性能的核心原因。有一类特殊索引叫哈希索引在 InnoDB 中主要用于自适应哈希索引它通过哈希函数直接定位数据查询速度更快但只支持等值查询不支持范围查询和排序。日常开发中我们主要使用 B 树索引选型时要根据业务查询类型来定。2. 索引类型与使用场景拆解2.1 主键索引、普通索引、唯一索引有什么区别从功能定位上索引可以分成几类主键索引PRIMARY KEY一种特殊的唯一索引不允许有空值。InnoDB 中每张表都必须有一个主键索引它就是聚簇索引数据行实际存储在它的叶子节点上。如果没有显式定义主键InnoDB 会选择一个非空唯一索引或生成隐藏主键。唯一索引UNIQUE KEY不允许重复值但允许 NULL。它和主键索引最大的区别在于一个表只能有一个主键但可以有多个唯一索引。业务上像身份证号、手机号这类业务标识字段就适合建唯一索引来保证数据唯一性和查询效率。普通索引INDEX也叫二级索引不约束值的唯一性。它针对查询频繁但不是唯一的字段建立比如订单表里的 user_id。2.2 单列索引与联合索引单列索引顾名思义只对一个字段建立索引。联合索引则是对多个字段按照一定顺序建立的一个索引比如INDEX idx_user_status (user_id, status)。联合索引必须遵守最左前缀原则。也就是说查询条件中必须用到联合索引的最左边字段这个索引才会生效。例如上面的联合索引WHERE user_id 100和WHERE user_id 100 AND status 1都能用到索引但如果直接WHERE status 1索引就无法使用。这个原则本质上是由 B 树索引的存储结构决定的。联合索引在排序时先按第一个字段排序第一个字段相同的按第二个字段排序。所以查询条件如果不包含最左字段B 树根本没法利用索引本身的顺序性。2.3 前缀索引与覆盖索引字符串类型的字段比如 URL、用户昵称往往很长。如果对整个字段建索引索引占用的空间大而且比较速度慢。这时可以只对字段的前 N 个字符建立索引叫前缀索引。比如INDEX idx_nickname (nickname(10))只取昵称前 10 个字符。需要注意的是前缀索引可能会导致查询结果不精确MySQL 拿到前缀匹配的行之后还要回表再判断完整值所以选择前缀长度时要在区分度和长度之间找平衡。覆盖索引是指查询的字段全部包含在索引字段中不需要回表查询数据行。比如你在 user_id 和 status 上建了联合索引查询SELECT user_id, status FROM order WHERE user_id 5那么索引叶子节点已经包含了这两个字段的值MySQL 就不用再回聚簇索引查完整行了。覆盖索引是减少 I/O 损耗的重要手段在设计查询时尽量让索引“覆盖”掉查询字段是一种非常实用的优化思路。3. 实操建索引的正确姿势与参数细节3.1 建索引前的字段选择分析我见过不少开发同学一遇到慢查询就随手给 WHERE 条件的字段加索引完全不管字段区分度、业务查询模式和数据分布。这种“无脑加索引”的做法短期看似解决了问题实际上可能给后续埋下一堆坑。一个字段值不同种类越多、分布越均匀区分度就越高建索引效果越好。区分度具体有多好可以通过一条 SQL 计算出来SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;这个比值约接近 1说明字段区分度越高索引效果越好。比如“性别”字段只有两个取值区分度就是 2/总行数非常低建了索引也很难有效过滤数据而“订单号”这种几乎唯一的字段区分度接近 1非常适合建索引。实际操作中我通常按照这个思路来选字段优先给 WHERE 条件中经常出现的字段建索引对 ORDER BY、GROUP BY 的字段建索引可以有效避免文件排序JOIN 的关联字段必须建索引否则大表 join 会非常痛苦数据量小比如几百上千行的表索引意义不大反而增加写入负担。3.2 联合索引怎么设计才能不踩坑联合索引的字段顺序是最容易出错的地方。很多人下意识把查询频率最高的字段放在最前面其实不完全正确。举个例子你要建一个 (a, b) 联合索引如果单查 b 也很常见而 a 的区分度很低可能就需要考虑拆成两个索引来设计。字段顺序的确定原则是把区分度高、查询条件中最常固定使用的字段放在前面。比如订单表经常按 user_id status 组合查询而且 user_id 的区分度明显高于 status那联合索引应该设计成 (user_id, status)这样既能直接支撑组合查询也能单独支撑 user_id 查询一举两得。还要注意一个细节联合索引需要考虑排序需求。如果业务上既要WHERE user_id ? ORDER BY create_time那么设计成(user_id, create_time)联合索引就非常合适因为 B 树在 user_id 相同的情况下已经按 create_time 排序好了查询时不需要额外排序。3.3 SQL 写法对索引的影响建好了索引不代表每条 SQL 都能用上。SQL 的写法对索引是否生效有决定性影响。最常见的“索引失效”场景包括对索引字段使用了函数比如WHERE DATE(create_time) 2024-01-01这样索引就不再生效因为 MySQL 无法对函数处理后的结果做索引查找隐式类型转换比如字段是字符串类型但查询用数字去比较MySQL 会先把字段转成数字索引也会失效LIKE 查询中前面使用了通配符比如LIKE %abc从索引树中间位置无法定位只能全表扫用 OR 连接非索引字段和索引字段可能导致索引失效。所以代码评审时我特别关注 SQL 写法字段是否被函数包裹、参数类型是否和字段类型一致、联合索引是否从最左列开始。这些看起来是小问题线上却能搞出大事。4. 索引优化进阶EXPLAIN 执行计划解读4.1 EXPLAIN 怎么用重点看哪些列写了 SQL建了索引到底有没有用最直接的办法就是用 EXPLAIN 看执行计划。MySQL 会告诉我们查询优化器打算怎么执行这条 SQL。基本用法很简单在 SELECT 语句前加上 EXPLAIN 关键字EXPLAIN SELECT user_id, status FROM order WHERE user_id 100 AND status 1;输出结果中重点关注的列有这些type访问类型从好到差依次是 system const eq_ref ref range index ALL。如果出现 ALL那就是全表扫描基本意味着索引没建好或者没用上。key实际使用的索引名称。如果为 NULL说明这条 SQL 没走任何索引。rowsMySQL 预估需要扫描的行数。这个数字越小越好和实际扫描行数差距太大时就要留意统计信息是否过期。Extra额外信息。里面如果出现 Using filesort 或 Using temporary说明查询需要额外排序或临时表性能通常好不到哪去出现 Using index 则是好消息说明用了覆盖索引。我第一次真正理解 type 列的价值是排查一条线上慢查询。当时有个统计报表 SQLEXPLAIN 出来 type 是 ALLrows 显示要扫 800 万行。后来通过调整查询条件和索引设计type 变成了 refrows 降到几千行查询时间从 3 秒降到 0.05 秒以下。这就是执行计划带来的直观价值。4.2 常见的 Extra 信息具体是什么含义EXPLAIN 的 Extra 列最容易被忽略但它往往藏着性能瓶颈的答案。Using filesort 表示 MySQL 需要对结果额外做一次排序操作。这个排序不一定是磁盘文件也可能发生在内存中但无论如何它都意味着无法利用索引自带的顺序性。遇到这个情况优先考虑设计联合索引让 ORDER BY 字段直接命中索引顺序。Using temporary 表示查询过程中使用了临时表常见于 GROUP BY、DISTINCT 以及多表连接中某些复杂场景。临时表的开销很大如果在高频查询中频繁出现最好是改写 SQL 或调整索引设计来规避。Using index 则是最理想的状态查询到的数据全部来自索引不需要回表。比如覆盖索引查询就会出现这个标识。很多优化场景的目标就是让尽量多的查询达到这个状态。4.3 统计信息与索引基数执行计划是优化器基于统计信息做的决策。InnoDB 会维护索引的基数Cardinality也就是索引中不同值的数量估算。如果这个统计信息不准确优化器可能会选错索引明明有索引却不用。遇到这种情况可以运行ANALYZE TABLE table_name更新统计信息。表数据量发生较大变化、或者大批量导入数据之后最好主动执行一次让优化器有准确的依据。5. 慢查询日志定位索引问题的第一工具5.1 怎么打开慢查询日志光靠 EXPLAIN 分析单条 SQL 还不够线上慢 SQL 怎么发现答案是慢查询日志。MySQL 可以把执行时间超过设定阈值的 SQL 记录下来是定位性能问题的第一入口。开启和配置慢查询日志可以动态设置SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;long_query_time 的单位是秒1 表示超过 1 秒的 SQL 会被记录。上线初期建议先设为 1等优化稳定后再慢慢调低到 0.5 甚至 0.1。设置太低会记录大量 SQL日志文件膨胀非常快影响磁盘空间和排查效率。5.2 慢查询日志里怎么分析分析慢查询日志我一般分三步走第一步找出出现频率最高的慢 SQL。用工具统计慢查询日志中相同 SQL 出现的次数优先处理频率最高的那些。第二步拿到慢 SQL 后先跑 EXPLAIN确认有没有走索引、type 是什么级别、rows 预估多少。第三步根据执行计划反推索引设计的问题。如果 type 是 ALL 或者 rows 预估很大那就回到第 3 节说的字段选择原则重新评估。过去我处理过一条典型的慢 SQL是报表统计类的频繁查询一张上亿行的流水表。EXPLAIN 显示 type 为 ALL。分析后发现查询条件里有三个过滤字段但索引只建了其中一个联合索引严重设计不足。改造索引后rows 从亿级别降到百万级别查询时间从 30 多秒降到 0.8 秒左右。这就是慢查询日志加执行计划双重分析带来的效果。5.3 慢查询日志分析的常用工具手工一条条看慢查询日志很费劲。如果环境允许可以装一些开源工具辅助分析。比如 mysqldumpslow 是 MySQL 自带的慢查询日志汇总工具可以把相同模式的 SQL 归并排序Percona Toolkit 里的 pt-query-digest 功能更强能按总耗时、平均耗时、响应时间占比排序直接输出分析报告。需要提醒的是分析工具只帮你定位问题真正的性能优化还是要回到索引结构和 SQL 写法上。6. 回表、索引下推与常见索引失效案例6.1 什么是回表查询很多初学者第一次看到“回表”这个词会有点懵。它的意思是通过二级索引找到主键值后还要再根据主键值去聚簇索引里查找完整的数据行。因为二级索引的叶子节点只存了索引字段和主键值不存完整行数据。回表本身并不是坏事它是 InnoDB 存储引擎的正常工作方式。但回表意味着额外的磁盘 I/O如果二级索引过滤后命中了大量数据行回表次数就会非常多性能随之下降。避免回表的思路就是前面说的覆盖索引让查询字段全部被索引覆盖一步到位。6.2 索引下推是怎么减少回表的索引下推Index Condition Pushdown简称 ICP是 MySQL 5.6 引入的优化手段。它允许存储引擎在索引遍历过程中直接过滤掉不满足索引中其他字段条件的数据减少回表次数。举个例子假设有联合索引 (name, age)查询WHERE name LIKE 张% AND age 28。在没有 ICP 的时候存储引擎会把所有 name 前缀匹配的记录都回表把完整的行取出来再判断 age 是否等于 28。有了 ICP存储引擎在索引叶子节点上就能判断 age 条件提前过滤掉不满足的记录只有真正符合条件的才回表。这个优化对联合索引的查询场景帮助很大也是为什么我们在设计联合索引时把多个过滤条件都放进索引里是明智选择的原因之一——即使做不到完全覆盖索引下推也能帮你省掉大量无效回表。6.3 实战案例一个典型索引失效问题的完整排查最后分享一个很有代表性的排查案例。某晚上线上订单查询接口突然变慢平均耗时从 200ms 涨到 3 秒以上。我先打开慢查询日志找到了一条按订单创建时间范围查询的 SQLSELECT id, order_no, amount FROM order WHERE create_time BETWEEN 2023-12-01 00:00:00 AND 2023-12-31 23:59:59。当时表上是创建了 create_time 单列索引的理论上应该能走索引。可是 EXPLAIN 一看type 是 ALLrows 接近全表。经过检查问题出在索引因为数据分布原因被优化器放弃了create_time 字段上有一大段数据值几乎连续优化器判断走索引还需要大量回表代价比全表扫描还大。处理方法把查询改成主键范围分段查询同时在索引设计上增加覆盖字段把 id、order_no、amount 全部放进索引改成(create_time, order_no, amount)联合索引。这样查询直接走索引还能避免回表。改造之后接口耗时恢复到 180ms 左右。这个案例给我的启发是索引优化不是一锤子买卖不要在建立索引后就高枕无忧。实际数据分布、查询模式的变化都可能让原本合理的索引失效。定期用 EXPLAIN 抽查线上慢查询学会读执行计划才能把索引优化做扎实。我个人在实际工作中还有一个体会索引不是越多越好过多索引会拖慢写入性能占用额外磁盘空间还会增加优化器的选择成本。每个索引的建立都应该能对应到真实存在的查询需求上。优化完一个查询记得用覆盖索引和联合索引的思维多问自己一句——这个查询还能不能少回一次表、少一次排序带着这个问题去调整你的 MySQL 性能优化能力才能在实践中真正成长起来。