Quartz 5

MySQL 数据库 8（Day56）

Properties1
tags来源, 数据库, mysql, 索引, b树, b+树, 覆盖索引, 最左前缀, explain, data

10分钟阅读

MySQL 数据库 8（Day56）

主题：MySQL 索引专题——储备知识（选择性/基数）、索引三维分类、二叉树→平衡二叉树→B树→B+树结构演进、覆盖索引与回表操作、索引实验（EXPLAIN/EXPLAIN ANALYZE 对比）、联合索引与最左前缀匹配原则

结构总览

MySQL 数据库 8
  ├─ 储备知识：索引作用、全表扫描、选择性 Selectivity、基数 Cardinality
  ├─ 索引分类：按功能 / 按物理存储 / 按数据结构 三维分类
  ├─ 数据结构演进：二叉搜索树 → 平衡二叉树 → B树 → B+树
  ├─ 覆盖索引与回表：二级索引访问路径对比、Using index
  ├─ 索引实验：EXPLAIN 前后对比、函数与范围条件、EXPLAIN ANALYZE
  └─ 联合索引：最左前缀匹配、范围条件截断、字段顺序设计

关键要点

储备知识： 索引是帮助数据库快速查找数据的数据结构，核心作用是减少查询扫描量。没有索引时逐行比对即全表扫描（Full Table Scan）。索引的代价：占磁盘空间、增加 INSERT/UPDATE/DELETE 维护成本——不是越多越好。

  • 选择性（Selectivity）：字段区分不同记录的能力，值越分散选择性越高（身份证号高、性别低）。
  • 基数（Cardinality）：索引列中不同值的大致数量，基数越高索引通常越有价值。
  • 索引效果还取决于：数据量、条件字段区分度、返回数据量、字段是否参与函数计算、隐式类型转换、联合索引字段顺序、优化器成本判断。
SELECT COUNT(DISTINCT gender) FROM user;   -- 看选择性
SHOW INDEX FROM user;   -- Key_name/Column_name/Cardinality/Index_type 等
EXPLAIN SELECT * FROM user WHERE id = 100;

排错：WHERE YEAR(created_at) = 2025 对索引字段做函数运算导致失效 → 改写为范围条件 created_at >= '2025-01-01' AND created_at < '2026-01-01'；字符串比较整数列引发隐式转换失效；建了索引不代表变快，必须用 EXPLAIN 验证。

索引三维分类：

分类维度类型要点
按功能普通索引无唯一性限制，纯加速
唯一索引值不重复（通常允许多个 NULL）；别当纯加速工具误伤合法重复
主键索引一表一个、非空唯一，InnoDB 中即聚簇索引
联合索引多字段整体结构，非多个独立索引
全文索引FULLTEXT + MATCH ... AGAINST，与 LIKE '%xx%' 机制不同
按物理存储聚簇索引叶子节点存完整记录，InnoDB 数据按主键聚集
二级索引叶子节点存索引字段 + 主键值
按数据结构B+树索引InnoDB 默认
哈希索引适合等值，不适合范围
全文/空间索引特定场景
CREATE INDEX idx_user_age ON user(age);
CREATE UNIQUE INDEX uk_user_email ON user(email);
CREATE INDEX idx_user_gender_age ON user(gender, age);
DROP INDEX idx_user_username ON user;

数据结构演进（为什么是 B+树）：

结构问题/特点
二叉搜索树有序插入退化为链表，O(log n) → O(n)
平衡二叉树（AVL/红黑）避免倾斜，但每节点只存少量数据，树高高 → 磁盘随机读次数多
B树多路平衡，节点存多键值、高度低，但节点内同时存键值和数据
B+树非叶子节点只存索引键不存数据 → 单节点容纳更多键、树更矮；完整数据全在叶子节点；叶子节点双向链表相连 → 范围查询和排序强

数据库索引设计还要考虑磁盘页大小、磁盘读取次数、节点容量和范围扫描能力，不只是比较次数。InnoDB 中聚簇索引叶子存整行，二级索引叶子存主键值。

覆盖索引与回表：

回表五步：二级索引查到条件记录 → 取叶子中的主键 id → 回聚簇索引 → 读完整行 → 返回。若查询所需字段全部包含在二级索引中（含主键），则无需回表，即覆盖索引——它不是独立索引类型，而是”某索引包含了查询所需全部字段”的状态。

CREATE INDEX idx_user_username_email ON user(username, email);
 
-- 不回表：Extra 出现 Using index
EXPLAIN SELECT username, email FROM user WHERE username = '张三';
-- 回表：SELECT * 需要聚簇索引取 email/age 等
EXPLAIN SELECT * FROM user WHERE username = '张三';

优点：减少聚簇索引随机读、减少读取数据页，对高频与分页查询帮助大。代价：索引字段越多占空间越大、写维护成本越高。避免无脑 SELECT *。

索引实验方法论： 保持环境一致，用 EXPLAIN 对比建索引前后执行计划。观察 type（ALL → ref/range）、key、rows、filtered、Extra。小数据量时优化器可能认为全表扫描更快；EXPLAIN ANALYZE 实际执行并显示真实行数与耗时。

排错：只看是否走索引不看扫描行数；实验数据太少看不出差异；反复跑插入脚本造成重复数据；走了索引仍慢 → 检查区分度、回表次数、排序/临时表。

联合索引与最左前缀匹配： 联合索引 (username, age, city) 是按字段顺序组织的单一 B+树结构，不是三个独立索引。

  • ✅ 可用：只有 username、username+age、三者全等值。
  • ❌ 不能充分使用：跳过最左列直接 WHERE age=20 或 WHERE city='北京'。
  • 范围截断：WHERE username='张三' AND age > 18 AND city='北京' —— age 用了范围后，后续 city 通常无法继续用于缩小索引扫描范围（等值匹配可继续向右，范围匹配截断）。
  • 查询条件书写顺序不影响匹配（WHERE age=20 AND username='张三' 优化器可调整），但能否充分使用仍以 EXPLAIN 为准。
  • 字段顺序设计考虑：高频条件字段、区分度高的字段、等值字段在前、范围字段在后，兼顾排序分组需求与索引长度/维护成本。

当日总结

  • 索引以额外空间和写维护成本换取扫描范围的缩减；价值由选择性/基数与查询方式决定
  • 三维分类：功能（普通/唯一/主键/联合/全文）、物理存储（聚簇/二级）、数据结构（B+树/哈希/全文/空间）
  • 结构演进：二叉树会退化 → 平衡二叉树太高磁盘读多 → B 树节点存数据 → B+树数据全在叶子 + 双向链表，树矮、等值与范围都强
  • 回表 = 二级索引拿主键再查聚簇索引取整行；覆盖索引让查询字段全部落在索引内，Extra 显示 Using index
  • 实验验证靠 EXPLAIN（type/key/rows/Extra）+ EXPLAIN ANALYZE 真实耗时，而非”有没有索引”
  • 最左前缀：从最左列开始连续等值可用；遇范围条件后右侧字段截断；字段顺序按等值在前、范围在后设计

相关页面

最近更新

关系图谱

反向链接