文章目录
- SQL索引本质
- Hash - 哈希索引
- B+Tree 索引
- 二叉搜索树
- 平衡二叉树
- B-Tree 多路搜索树、多叉平衡查找树
- B+Tree
- 聚集索引
- `myisam` 引擎
- `innodb`引擎
- 主键索引
- 优秀的索引
- 列的离散性
- 联合索引
- 覆盖索引
- 三星索引
SQL索引本质
索引（index）是帮助MySQL高效获取数据的数据结构。
在RDBMS中，索引存储在硬盘中。
Hash - 哈希索引
AHI-adaptive hash index - 自适应哈希索引
对字段计算hash值，将hash值和行指针对应存在hash表中。
为什么innodb引擎不支持手动使用hash索引：
- 存在内存中，数据量大，内存容易爆
- hash查询行记录，不适合指定查某几列
- 按hash值排序，对范围查找没有帮助，如果还有order，需要二次排序
- 哈希碰撞，计算出的hash值相同。一个hash值指向多行指针，需要逐一查找
memory引擎可以手动使用hash索引，因为memory引擎使用内存。
一般中间表使用memory引擎。
B+Tree 索引
二叉搜索树
一个节点最多分出两个节点，子节点满足左小右大。
缺点：极限情况，假设1为根节点，往下节点依次为2、3、4、5等等，要查询5需要5次。
平衡二叉树
一个节点最多分出两个节点，两个子节点的深度差绝对值不能大于1。
- IO次数过多，随着数据变多，树的深度变大。
- 目标数据过少，造成很大的IO资源浪费。
B-Tree 多路搜索树、多叉平衡查找树
- 多关键数
- 多路数
- 每条路深度一样、绝对平衡
每个节点，最多关键数的个数 = 路数 - 1。
假设节点为：1，5，10
则分路为
[负无穷,1),[1, 5),[5,10),[10,正无穷)
每个节点都有数据区。
B+Tree
节点关键数和路数1：1。
假设节点为：1，5，10
则分路为
[1, 5),[5, 10),[10, 正无穷)
数据区存放在叶子节点，所以所有查询都要查询到叶节点，虽然部分查询比B-Tree更耗时，但是稳定。
优点：
- 基于索引扫表能力强
- 基于索引排序能力强
- 查询所有数据都要到叶子节点，时间稳定。
- 取消数据区内容，读写能力更强
聚集索引
myisam 引擎
myi 文件，存索引，叶节点存地址
myd 文件，存地址对应的数据
innodb引擎
ibd文件：
- 主键索引：叶节点挂载行记录
- 非主键索引：叶节点保存主键值
innodb引擎中，只有主键是聚集索引，其他索引都是非聚集索引。
主键索引
如果不手动建立主键索引，会自动用隐藏列_rowid作为主键索引，占用6byte，而int只占用4byte。
并且行锁会升级为表锁
优秀的索引
列的离散性
count distinct col : count col
非重复列数：所有列数
列重复越少越好。
联合索引
最左匹配原则：联合索引的任何前缀都可启用联合索引
create index idx_name_ph_age(name,phone,age)
name
name, phone
name, phone, age
以上三个均可启用联合索引，而
phone
phone, age
phone, name ,age
等不可启用联合索引下述两个查询是否启用联合查询，使用了几个：
- where name=‘peter’ and phone > 1333333 and age = 18
使用了联合索引，1个。“范围之后全失效”
因为phone>1333333范围过大，而age=18重复率高。 - where phone = 1333333 and name = ‘peter’
使用联合索引2个，mysql优化器，会把选择性最后的列放在where的最左边。
连接器：管理连接，权限验证
分析器：词法分析，语法分析
优化器：执行计划生成，索引选择
执行器：操作引擎，返回结果
覆盖索引
不触发回表操作。
通过索引项的信息可以直接返回锁查询的列，则该索引成为查询SQL的覆盖索引。
尽量使用覆盖索引，因为能加快查询速度。
回表操作：
根据前面提到的
innodb引擎
- 主键索引：叶节点挂载行记录
- 非主键索引：叶节点保存主键值
根据非主键索引查出主键后，再根据主键索引查询行记录的操作为回表操作。
假设表有：
- 主键索引
- 非主键索引
name- 联合索引
name, phone则：
select * from user where name = 'mervyn'触发回表操作，通过非主键索引name查询出id后， 又根据主键索引查出行数据。
select id, name from user where name = 'mervyn'没有触发回表操作，通过非主键索引name可以直接查询出id和name。
select id, phone, name from user where name = 'mervyn'没有触发回表操作，联合索引
三星索引
- where后匹配的索引关键字列越多越好，扫描的数据越精确，越少越好。
- 避免再次排序。
- 尽可能的应用覆盖索引。减少回表操作。
