mysql索引原理

1. 背景

1.1. 读写比例10:1
1.2. 性能出现瓶颈
1.3. 问题

1.3.1. 如何简历索引
1.3.2. 索引顺序如何
2. 目的

2.1. 提高查询速度,相当于目录
3. 原理

3.1. 缩小数据范围
3.2. 随机变有序
4. 基础

4.1. 磁盘io

4.1.1. 时间

寻道时间
旋转延迟
传输时间
4.2. 预读

4.2.1. io太耗时,把相邻的也读到缓冲区
4.2.2. 每次io读取的数据为一页 4k到8k
5. 数据结构

5.1. B+Tree

5.1.1. why

io想要尽量少,高度就得尽量低
5.1.2. 结构

磁盘块

包含数据项和指针
叶子节点包含真正的数据
数据下的指针指向不同范围的数据
5.1.3. 查找过程

1.加载树根,二分查找,锁定指针,第一次io
2.根据第一步的指针加载磁盘块到内存,第二次io,锁定指针
3.加载指针对应的磁盘块到内存,二分查找找到对应的数据
注:三层B+树可以表示百万数据,只需三次io,如果没有索引需要百万次io
5.1.4. 性质

io取决于B+高度,数据为N 磁盘块的数量项为M h=log(m+1)N

N固定,M越大,h越小
M=磁盘块大小/数据项大小
场景:索引字段尽量小,真实数据放在叶子节点
最左匹配特性

(name,age,sex)会依次从name age sex 进行查询
6. 建索引原则

6.1. 最左前缀匹配原则
6.2. =与in可以乱序
6.3. 区分度高的列做索引
6.4. 索引列不能参与计算
6.5. 尽量扩展索引,不新建索引