Mysql的索引知识点
目录
定义
索引就是一个帮助MySQL高效获取数据的排好序的数据结构。
索引数据结构
二叉树
结构
一个节点最多左右两个子节点
规则：左小右大
通俗例子
优点
查找逻辑简单
致命缺点（MySQL绝对不用）
- 数据有序插入会变成斜树/链表 比如插 1、2、3、4、5 → 直接变成一条直线。
- 查找深度暴增，磁盘 IO 暴多，速度暴跌。
红黑树
作用
解决二叉树倾斜变链表问题，自动平衡保证左右高度差不大
优点
查询稳定，比普通二叉树快很多
缺点（MySQL 不用它做主索引）
- 节点分支太少，最多 2 个分叉
- 数据量大了树太高，读磁盘次数多
- 磁盘页一次能存很多数据，红黑树太 “瘦” 浪费空间
结论
用于java HashMap，不适合MySQL。
Hash表
原理
给字段值算哈希值，直接映射存放位置
优点
- 精准匹配最快
- 没有顺序关系
缺点
- 不能范围查询，不能查 > 、< 、between、排序 order by、like 前缀模糊的
- 存在哈希碰撞，多个值算成同一个地址
- 不支持联合索引有序匹配
B-Tree
结构特点
- 一个大节点能存很多个小节点
- 所有节点存储的都是真实的数据
- 树特别矮，100 万条数据都只有3-4层
优点
- 树极矮，找数据只需查询几层节点
- 磁盘利用率高，一个节点能存储多条数据
缺点
- 数据散落在各个节点：你想找所有 10-20 的数字，得在树上来回跳转
- 范围查询麻烦：找到一个数据，又得重新从根节点开始找下一个数据
总结
比红黑树好，但范围查询还是不方便。
B+Tree（B-Tree变种）
B 树和 B + 树的区别
B 树：所有节点都存放数据
B + 树：只有叶子节点存放数据，其他节点只是单纯的目录
最通俗的结构
- 非叶子节点：纯目录，不放任何真实的数据
- 叶子节点：存放所有的数据 ；把数据都用双向链表连起来，从小到大排列成直线
优点
- 上层全是精简的目录，一个节点能存几千个数据。1000 万条数据，B + 树只需要 3 层。也就是说，任何查询最多只需要读 3 次磁盘，大大减少了读取磁盘的时间。
- 不用来回跳树，不用重新查找，一口气读完
- 全表扫描特别方便，直接遍历最底层的链表就行
- 增删改操作更稳定，不会导致树的结构剧烈变化
总结
全能选手，等值查询快，范围查询快，排序快，磁盘利用率高。InnoDB 所有索引底层全是 B + 树。
聚集索引和非聚集索引
聚集索引（Innodb 核心）
特点
一张表只能有一个聚集索引
数据和索引是一体的
- 聚集索引的叶子节点 = 完整的一行数据
- 找到索引，就找到了所有数据，不需要再去别的地方找
主键设定
- 如果建表时没指定主键，MySQL 会自动选一个唯一键当聚集索引
- 如果连唯一键都没有，MySQL 会偷偷生成一个隐藏的 6 字节 rowid 当聚集索引
注意
在Innodb 表中必须有聚集索引
非聚集索引
特点
-
一张表可以有多个非聚集索引
-
叶子节点只存索引列值和主键值（节省空间，减少磁盘浪费）
查询流程（最核心：返表）
- 客户端发起 SQL 查询，优化器判定走非聚集索引；
- 在非聚集索引对应的 B + 树中，从根节点逐层向下检索，匹配索引键值；
- 检索至叶子节点，获取存储的聚集索引键（主键值）；
- 利用该主键值，再次进入聚集索引 B + 树执行二次索引检索；
- 定位到聚集索引叶子节点，读取完整行数据；
- 整理结果集，返回查询数据。
MyISAM 存储引擎
MyISAM中有三个文件：.frm表结构文件（被淘汰，MySQL8.0以后的版本都直接存在了自带的数据字典）、.MYD数据文件、.MYI索引文件
.MYD数据文件
- 独立存放：数据和索引彻底分家，互不干扰
- MyISAM没有聚集索引
- 数据在磁盘上就是顺序平铺存放，无序不跟着索引走
- 删除数据只会标记空位，不会立刻回收空间
.MYI索引文件
- Innodb 二级索引存主键
- MyISAM 所有索引叶子节点，全部存【数据行物理地址】
覆盖索引：不用返表的神器
如果你的查询需求，只需要索引里已经有的信息，那就不需要回表了。
二者对比
| 对比项 | 聚簇索引（主键） | 非聚簇索引（普通索引） |
|---|---|---|
| 叶子节点存什么 | 完整的一行数据 | 索引值 + 主键值 |
| 数量 | 一张表只能有 1 个 | 一张表可以有多个 |
| 查询速度 | 最快，一步到位 | 通常需要回表，慢一些 |
| 物理存储 | 数据按索引顺序排列 | 索引和数据分开存储 |
联合索引
底层存储规则
排序优先级：从左到右，严格依次
- 先按第一个字段 a 排序
- a 相同的，再按第二个字段 b 排序
- b 相同的，再按第三个字段 c 排序
叶子节点存放：(a 值，b 值，c 值) + 主键
- B + 树结构
- 有序双向链表
最核心原则：最左前缀原则
- 联合索引内字段排序优先级严格遵循索引创建先后顺序，左侧字段为首要排序依据。
- 查询语句必须匹配索引最左侧首个索引列，方可触发联合索引生效。
- 若左侧等值匹配完成，可继续向后匹配后续索引字段；若跳过左侧字段直接匹配右侧字段，联合索引失效。
- 索引字段出现范围索引后，该字段右侧所有索引字段自动失去索引匹配能力。
联合索引的技巧
- 等值条件放左边，范围条件放右边
- 范围条件后面的字段索引失效
- 尽量建联合索引，少建单字段索引
Explain工具
定义
Explain 就是模拟 MySQL 优化器执行 SQL，不真正跑业务数据，只返回「执行计划」
作用
- 看这条 SQL 有没有走索引
- 看走了哪个索引、是不是最优索引
- 看有没有全表扫描、临时表、文件排序（都是慢 SQL 信号）
- 依据执行计划，判断要不要加索引、怎么改联合索引、怎么改 SQL
列
id
- 有几个
SELECT就有几个 id - 规则：
- id 越大，越先执行
- id 相同，从上往下执行
- id 为 NULL 最后执行用途：看多表关联、子查询、派生表的执行顺序
select_type 查询类型
- SIMPLE：简单查询，无子查询、UNION
- PRIMARY：复杂查询最外层SELECT
- SUBQUERY：SELECT 里的子查询
- DERIVED：FROM 里的子查询，会生成临时派生表
- UNION：UNION 中第二个及后面的 SELECT
table
当前行正在访问哪张表； 遇到 <derived N>、<union 1,2> 就是临时表 / 合并结果集。
type
从最优到最差排序： system > const > eq_ref > ref > range > index > ALL
- system：表只有 1 条数据，const 特例，顶级最优
- const：主键 / 唯一索引 等值匹配，最多 1 行，优化器直接当常量
- eq_ref：联表查询，用主键/唯一索引关联，匹配一行，非常好
- ref：普通索引 / 联合索引前缀 等值匹配，可能匹配多行，日常开发达标级别
- range：范围查询
> < >= <= BETWEEN IN，走索引范围扫描 - index：扫整个二级索引树叶子，比全表扫描快，但没精准走索引高效
- ALL：全表扫描，最差，必须优化加索引
注意：优化底线：至少到 range，最好 ref，坚决避免 ALL
possible_keys
理论上可以用到的索引
key
真正用到的索引
常见情况： possible_keys 有值、key 为 NULL → 数据量小，MySQL 觉得走索引不划算，直接全表扫
key_len
含义
前面 SQL 用到的联合索引几个字段、占用多少字节
作用：
- 可以精准判断最左前缀有没有生效
- 看是不是跳过了字段、是不是范围截断了后面索引
ref
索引匹配的是常量（const）还是别的表字段
rows
MySQL预估要扫描多少行，行数越少越好
filtered
百分比，估算符合条件数据比例，用于联表行数预估
Extra
展示额外信息
- Using index：覆盖所索引，不用回表，只从二级索引就能拿到所有查询字段，性能极高
- Using where：server 层过滤数据，没走索引或索引没覆盖
- Using index condition：索引下推，范围条件用到索引前缀
- Using temporary：用到临时表，一般是分组、去重、排序没索引导致，要优化
- Using filesort：文件排序，没走索引排序，自己额外排序，慢 SQL 重灾区
AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。
更多推荐
6
0- 0
已为社区贡献2条内容
所有评论(0)