MySQL进阶：索引原理与查询优化

前面三篇文章你的 SQL 已经能从单表 CRUD 写到多表 JOIN 子查询 聚合统计了。但如果数据量到了十万、百万行你会发现一条看似正确的查询跑了几秒钟还没出结果——而同事只加了一个索引同样的查询从 3 秒变成了 3 毫秒。索引是 MySQL 性能优化的核心也是面试里最常被深挖的话题。本文从 BTree 的原理开始到 EXPLAIN 执行计划解读再到实际加索引前后的性能对比完整覆盖索引的为什么、是什么、怎么用。没有索引的时候MySQL 怎么查数据先回到本质问题。假设enrollments表有 100 万行数据你执行SELECT*FROMenrollmentsWHEREstudent_id42;如果没有索引MySQL 只能从第一行开始逐行扫描全表扫描直到找到所有student_id 42的行。运气好目标在第一页运气不好得把 100 万行全读一遍。时间复杂度是 O(n)行数翻倍时间也翻倍。索引的作用就是给 MySQL 一个快速定位的能力——就像书的目录让你直接翻到第 3 章而不是从第 1 页开始逐页翻。没有索引全表扫描 [行1] → [行2] → [行3] → ... → [行1000000] ↑ 运气不好得全部扫一遍 有索引BTree 定位 [根节点] ├── [分支1] → ... → [目标行] └── [分支2] ↑ 3~4 次磁盘 I/O 就能定位BTree为什么索引用这棵树MySQL InnoDB 引擎的索引默认使用 BTree 数据结构。为什么不是二叉树不是哈希表二叉树的问题二叉搜索树每个节点只存一个值如果数据是递增插入的比如自增主键树会退化成一条链1 \ 2 \ 3 \ ...这样查一行要遍历和行数一样多的节点退化为 O(n)。平衡二叉树AVL、红黑树通过旋转保持平衡解决了退化问题但有一个致命缺陷每个节点只存一组键值树的高度随着数据量线性增长。100 万行数据的二叉树高度大约是 20 层意味着一次查询要做 20 次磁盘 I/O——在机械硬盘时代这是灾难即使在 SSD 上也不可接受。哈希表的问题哈希索引能 O(1) 定位单行但它不支持范围查询-- 哈希索引能加速SELECT*FROMstudentsWHEREid42;-- 哈希索引完全无法加速SELECT*FROMstudentsWHEREageBETWEEN18AND22;SELECT*FROMstudentsWHEREnameLIKE张%;SELECT*FROMstudentsORDERBYage;范围查询、模糊匹配、排序——这三件事哈希索引都做不了而它们恰恰是 SQL 查询里最常见的一类。BTree 的三个关键特性[50 | 100] ← 根节点非叶子 / | \ [10|20|30] [60|70|80] [110|120|130] ← 内部节点非叶子 / | | \ / | | \ / | | \ [具体数据行...通过指针指向] ← 叶子节点存全量数据双向链表连接BTree 的设计针对的是磁盘 I/O 场景有三个核心特性1. 多叉而非二叉。每个节点存多个键值、有多个子节点。InnoDB 中一个节点的大小通常是一页16KB可以装几百个键值。这意味着树的高度极低——100 万行数据的 BTree 高度通常是 3 层一次查询只需要 3 次磁盘 I/O。2. 所有数据存在叶子节点。非叶子节点只存索引键用于导航不存具体数据。这让非叶子节点能容纳更多键值、分叉更多、树更矮。3. 叶子节点之间用双向链表连接。这意味着一旦你通过树结构定位到了叶子层的第一个目标节点范围查询只需要沿着链表向右走就行不需要回到根节点重新搜索。根节点[50 │ 100]只存索引键用于路由内部节点[10│20│30]内部节点[60│70│80]内部节点[110│120│130]叶子节点数据行: id1..30双向链表连接 ←→叶子节点数据行: id51..100←→叶子节点数据行: id101..130←→BTree 是数据库索引的标准答案。几乎所有主流关系型数据库MySQL InnoDB、PostgreSQL、Oracle、SQL Server都用它不是因为理论最优而是因为它最适配磁盘的物理特性——把树做矮做胖用最少的 I/O 找到数据。聚簇索引与二级索引InnoDB 的索引分为两大类理解它们的区别是后续一切知识的基础。聚簇索引Clustered Index聚簇索引 主键索引 数据本身。在 InnoDB 中表数据是按照主键的顺序物理存储的。主键 BTree 的叶子节点里存的就是完整的行数据不是指针。如果你建表时没有定义主键InnoDB 会找一个 UNIQUE NOT NULL 的列当主键如果也没有它会自动生成一个隐藏的 6 字节ROW_ID作为聚簇索引。这就是为什么主键查询最快——找到主键 BTree 的叶子节点数据就在那里一步到位。二级索引Secondary Index你自己手动建的索引都是二级索引也叫辅助索引、非聚簇索引。它的 BTree 里叶子节点存的不是完整行数据而是主键值。二级索引 BTree [name 索引] ├── 李四 → 主键值 2 ├── 孙七 → 主键值 5 ├── 张三 → 主键值 1 └── ... 聚簇索引 BTree [主键索引] ├── id1 → {完整行: 张三, 20, zhangsan..., ...} ├── id2 → {完整行: 李四, 21, lisi..., ...} └── ...回表当你用二级索引查询时MySQL 先走二级索引的 BTree 找到主键值再拿着主键值去聚簇索引里找完整行数据——这个查两次的过程叫回表。-- 假设 name 列上建了索引SELECT*FROMstudentsWHEREname张三;执行过程在name索引的 BTree 里找到张三对应的主键值1拿着1去主键索引的 BTree 里取出该行的所有列回表操作涉及两次 BTree 查找比直接走主键索引多一次 I/O。大量回表会严重影响查询性能——后面会讲怎么避免。覆盖索引不用回表的查询如果查询需要的所有列都在二级索引里了MySQL 就不需要回表。这叫覆盖索引Covering Index。-- 假设 (name, age) 上建了联合索引-- 这个查询只需要 name 和 age都在索引里不需要回表SELECTname,ageFROMstudentsWHEREname张三;-- 这个查询要 email索引里没有必须回表SELECTname,age,emailFROMstudentsWHEREname张三;EXPLAIN 输出中Extra列出现Using index就表示使用了覆盖索引——这是性能极好的标志。联合索引与最左前缀原则这是整个索引话题里最重要的知识点面试必考生产环境每天都在用。什么是联合索引联合索引就是把多列组合在一起建一个索引-- 在 enrollments 表上建联合索引CREATEINDEXidx_student_courseONenrollments(student_id,course_id);它对应的 BTree 是这样一个结构先按student_id排序student_id相同的行再按course_id排序idx_student_course 联合索引 BTree (student_id1, course_id1) → 主键值 (student_id1, course_id3) → 主键值 (student_id2, course_id1) → 主键值 (student_id2, course_id2) → 主键值 (student_id2, course_id4) → 主键值 (student_id3, course_id3) → 主键值 (student_id3, course_id4) → 主键值 ...最左前缀原则联合索引只能从左往右逐列匹配不能跳过前面的列直接匹配后面的列。把(A, B, C)联合索引想象成一个三级的排序规则——先按 A 排序A 相同的再按 B 排序A 和 B 都相同的再按 C 排序。这就决定了-- 以下查询能用上索引从最左列开始匹配WHEREA1-- 只用 A 列WHEREA1ANDB2-- 用 A 和 BWHEREA1ANDB2ANDC3-- 用全部三列-- 以下查询用不上索引跳过了最左列WHEREB2-- 没有 AB 是无序的WHEREC3-- 没有 AC 是无序的WHEREB2ANDC3-- 没有 AB 和 C 都是无序的-- 这个查询用上了索引但只用 A 列WHEREA1ANDC3-- A 匹配上了但 C 跳过了 B无法使用为什么 B 单独查就用不了索引因为数据是按 A→B→C 的顺序排列的。在全局范围看B 的值是乱序的——A1的 B 可能是 1、3、5A2的 B 可能是 1、2、4。没有 A 作为前缀B 组成的链表不是有序的BTree 无法进行二分查找。可视化的理解方式是把这个联合索引想象成电话簿电话簿的排序逻辑姓氏 → 名字 → 街道 按姓氏查姓张的 → 直接翻到张姓那一块快 按姓氏名字查姓张、名三的 → 在张姓里找名字叫三的也快 只按名字查名叫三的 → 整本电话簿里三分布在各处只能一页页翻范围查询的断点一旦联合索引的某一列用了范围查询、、BETWEEN、LIKE ‘前缀%’它右边的列就无法使用索引。-- 索引 (A, B, C)WHEREA1ANDB10ANDC3-- A 精确匹配B 范围匹配C 无法使用索引-- 因为 B 的范围把数据分成了多段每一段内部 C 是有序的但段与段之间 C 是无序的解决方案是调整索引列的顺序把等值条件的列放前面范围条件的列放最后。实际案例学生选课系统-- 建联合索引CREATEINDEXidx_student_course_scoreONenrollments(student_id,course_id,score);-- 能用到全部三列SELECT*FROMenrollmentsWHEREstudent_id1ANDcourse_id3ANDscore80;-- 只能用 student_id 一列course_id 用了范围查询score 无法使用SELECT*FROMenrollmentsWHEREstudent_id1ANDcourse_id2ANDscore80;-- 完全用不上索引跳过了最左列 student_idSELECT*FROMenrollmentsWHEREcourse_id3ANDscore80;EXPLAIN看 MySQL 怎么执行你的查询EXPLAIN让你看到 MySQL 优化器对一条 SQL 的执行计划——用了哪个索引、扫描多少行、有没有用到临时表。它是调优 SQL 的第一步也是最关键的工具。基本用法EXPLAINSELECT*FROMenrollmentsWHEREstudent_id1ANDcourse_id3;输出---------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------------- | 1 | SIMPLE | enrollments | ref | idx_student_course | idx | 8 | const | 3 | NULL | ----------------------------------------------------------------------------------------------不需要理解每一个字段。日常调优聚焦以下四个就够了type访问类型最重要的一列type 是衡量查询效率的核心指标从好到坏排列NULL const eq_ref ref range index ALL逐级解释const主键等值查询或UNIQUE 索引等值查询最多返回一行。这是最快的。EXPLAINSELECT*FROMstudentsWHEREid1;-- type: const主键查询唯一结果eq_refJOIN 时被驱动表用主键或 UNIQUE 索引匹配每个值只匹配一行。EXPLAINSELECT*FROMenrollments eJOINstudents sONe.student_ids.id;-- students 表: type eq_ref用主键匹配每个 student_id 对应唯一一行ref普通索引等值查询可能返回多行。这是日常开发中最常见的好用级别。EXPLAINSELECT*FROMenrollmentsWHEREstudent_id1;-- type: ref普通索引匹配可能有 3~4 条选课记录range索引范围扫描。出现在、、BETWEEN、IN、LIKE 前缀%等场景。EXPLAINSELECT*FROMenrollmentsWHEREstudent_idBETWEEN1AND3;-- type: rangeindex全索引扫描——遍历整个索引树而不是数据表。比 ALL 快一点但仍然很慢。EXPLAINSELECTstudent_idFROMenrollments;-- type: index只查 student_id不需要回表但还是要扫完整个索引ALL全表扫描。大表里出现这个就是明确的性能问题。EXPLAINSELECT*FROMenrollmentsWHEREscore80;-- type: ALLscore 列没有索引只能全表扫描日常开发的目标是让所有查询的 type 保持在 range 及以上级别。如果 EXPLAIN 里出现 ALL 或 index问你三个问题这个列上建索引了吗索引被用上了吗是不是条件写错了导致索引失效key实际使用的索引MySQL 优化器最终选择了哪个索引。如果这个字段是 NULL说明没有用上任何索引——要么没建索引要么索引失效了。possible_keys是候选索引列表key是实际选的。如果possible_keys有值但key是 NULL说明 MySQL 判断用索引比全表扫描还慢通常数据量很小时会出现。rows预估扫描行数MySQL 优化器估算需要扫描多少行才能完成查询。这是一个估算值不精确但数量级能说明问题——100 万行的表这个值在 10 和 10 万之间优化空间天差地别。Extra额外信息这一列经常有惊喜几个关键值Extra 值含义评价Using index覆盖索引不需要回表极好Using whereMySQL 在存储引擎返回数据后又用 WHERE 过滤了一遍正常Using index condition使用了索引下推ICP部分条件在引擎层就过滤了好Using temporary需要用到临时表常见于 GROUP BY ORDER BY 不同列警告Using filesort需要用额外步骤排序无法利用索引的有序性警告NULL什么都没额外做直接返回好实战对比加索引前后在不加索引的情况下查一个学生的选课记录-- 没建 student_id 索引时EXPLAINSELECT*FROMenrollmentsWHEREstudent_id1;-- type: ALL, rows: 17扫描全表 17 行-- 加上索引后CREATEINDEXidx_student_idONenrollments(student_id);EXPLAINSELECT*FROMenrollmentsWHEREstudent_id1;-- type: ref, rows: 4只扫描张三的 4 条选课记录17 行看不出差距。但如果表有 100 万行type: ALL要扫 100 万行type: ref可能只需要 3 行 3 次回表。差距是数量级的。索引失效的五个经典场景索引建了但查询没用上比没建索引还令人沮丧。下面五个场景覆盖了 90% 的索引失效问题。1. LIKE 以 % 开头-- 能走索引SELECT*FROMstudentsWHEREnameLIKE张%;-- 不能走索引SELECT*FROMstudentsWHEREnameLIKE%三;SELECT*FROMstudentsWHEREnameLIKE%三%;LIKE 张%能走索引是因为 BTree 的排列顺序让相同前缀的值聚在一起——张开头的值在索引中是连续的一段。LIKE %三要找以三结尾的值在 BTree 里这些值是分散的无法利用二分查找。2. 对索引列使用了函数或表达式-- 不能走索引SELECT*FROMstudentsWHERELOWER(name)zhangsan;SELECT*FROMstudentsWHEREage121;-- 能走索引SELECT*FROMstudentsWHEREnamezhangsan;-- 存进去的就是小写SELECT*FROMstudentsWHEREage20;-- 把计算移到等号右边MySQL 必须先对每一行的值执行函数或表达式才能与条件比较这意味着必须先拿到所有数据——索引帮不上忙。3. 隐式类型转换-- phone 是 VARCHAR 类型SELECT*FROMteachersWHEREphone138xxxx1111;-- 不能走索引SELECT*FROMteachersWHEREphone138xxxx1111;-- 能走索引当你在字符串列上用整数去比较时MySQL 会触发隐式类型转换——把列值字符串转为数字后再比较。这个转换过程等同于在列上套了一个函数导致索引失效。4. OR 条件中有非索引列-- 假设只有 student_id 上有索引course_id 上没有索引-- 不能走索引OR 的另一侧需要全表扫描SELECT*FROMenrollmentsWHEREstudent_id1ORcourse_id3;-- 改写成 UNION ALL 让两边各走各的索引SELECT*FROMenrollmentsWHEREstudent_id1UNIONALLSELECT*FROMenrollmentsWHEREcourse_id3;5. 联合索引不满足最左前缀前面已经详细讲过了这里只总结联合索引(A, B, C)→ 查询条件必须从 A 开始不能跳过。慢查询日志找到需要优化的 SQL写了几百条 SQL 之后你不可能逐条 EXPLAIN。慢查询日志帮你自动找出执行时间超过阈值的查询-- 开启慢查询日志SETGLOBALslow_query_logON;-- 设置阈值为 0.5 秒默认是 10 秒太大了SETGLOBALlong_query_time0.5;-- 也记录没有用上索引的查询可选会产生大量日志SETGLOBALlog_queries_not_using_indexesON;-- 查看日志文件路径SHOWVARIABLESLIKEslow_query_log_file;然后定期分析慢查询日志# 用 mysqldumpslow 工具分析mysqldumpslow-st-t10/var/lib/mysql/slow.log# -s t: 按总时间排序, -t 10: 显示前 10 条拿到最耗时的几条 SQL 后逐个 EXPLAIN加索引再 EXPLAIN 验证。这是标准的 SQL 调优流程。实战为学生选课系统建索引我们的数据不到 100 行EXPLAIN 出来的rows差异没有说服力。但思路是一样的——分析高频查询模式按查询模式建索引。第一个索引覆盖最频繁的查询学生端最常见的操作是查我选了哪些课及成绩SELECTc.name,e.scoreFROMenrollments eJOINcourses cONe.course_idc.idWHEREe.student_id1;WHERE e.student_id 1是核心过滤条件给enrollments.student_id建索引CREATEINDEXidx_enrollments_studentONenrollments(student_id);但这里 JOIN 还需要course_id查询还要取score——如果这三个列都在一个联合索引里就能实现覆盖索引enrollments整个查询不用回表CREATEINDEXidx_enrollments_student_course_scoreONenrollments(student_id,course_id,score);EXPLAIN 验证EXPLAINSELECTc.name,e.scoreFROMenrollments eJOINcourses cONe.course_idc.idWHEREe.student_id1;enrollments的Extra应出现Using index——覆盖索引达成。第二个索引老师端的查询老师端最常见的操作是查我教的课的选课情况-- 查陈老师所有课的学生成绩SELECTs.nameASstudent_name,c.nameAScourse_name,e.scoreFROMenrollments eJOINcourses cONe.course_idc.idJOINstudents sONe.student_ids.idWHEREc.teacher_id1ORDERBYc.name,e.scoreDESC;分析查询路径WHERE c.teacher_id 1— 在 courses 表上需要teacher_id索引e.course_id c.id— enrollments 表需要course_id索引来做 JOINORDER BY c.name, e.score DESC— 如果索引能覆盖排序能省去 filesort-- courses 表CREATEINDEXidx_courses_teacherONcourses(teacher_id);-- enrollments 表按 course_id 找选课记录要 include score 用于排序和输出CREATEINDEXidx_enrollments_course_scoreONenrollments(course_id,score);索引清单经过分析学生选课系统推荐的索引如下-- students 表按 name 查学生是高频操作CREATEINDEXidx_students_nameONstudents(name);-- students 表邮箱 UNIQUE 已自动建索引UNIQUE 约束自带索引-- courses 表按老师查课程CREATEINDEXidx_courses_teacherONcourses(teacher_id);-- enrollments 表按学生查选课覆盖索引含 course_id 和 score避免回表CREATEINDEXidx_enrollments_studentONenrollments(student_id,course_id,score);-- enrollments 表按课程查选课含 score用于排序和过滤CREATEINDEXidx_enrollments_courseONenrollments(course_id,score);索引不是越多越好每多一个索引INSERT、UPDATE、DELETE就需要多维护一棵 BTree。写入性能会随索引数量线性下降。一张表的索引控制在 3~5 个是合理的范围超过 7 个就要回头审视——是不是有些索引从来没用上是不是可以用联合索引替代多个单列索引总结索引是 MySQL 性能优化的核心掌握以下要点就能应对日常开发中 90% 的性能问题BTree 矮胖的结构让 100 万行数据的查询只需 3 次 I/O这是索引快的根本原因聚簇索引存完整行数据二级索引存主键值——二级索引查非索引列要回表覆盖索引可以避免回表联合索引遵循最左前缀原则——查询条件必须从索引的最左列开始不能跳过范围查询会截断后续列的匹配EXPLAIN 看四个字段type扫表方式、key实际用的索引、rows预估行数、ExtraUsing index/Using filesort/Using temporary索引失效五大场景LIKE ‘%x’、函数包裹列、隐式类型转换、OR 中有非索引列、联合索引跳最左列慢查询日志 EXPLAIN 加索引是标准的三步调优流程索引不是免费的——控制在 3~5 个用联合索引替代多个单列索引下一篇文章将深入 MySQL 的事务机制——ACID、四种隔离级别、MVCC 多版本并发控制、行锁与间隙锁、以及死锁的排查与预防。这是迈向理解 MySQL 底层的关键一步。