MySQL 学习笔记2.1 B-Tree索引及索引适用情况

MySQL索引

1. B-Tree索引
2. Hash索引
3. full-text全文索引
4. R-Tree索引

B-Tree节点

个人理解：B树的每个节点存储在一个磁盘块中，每个节点包括M个数据项和(M+1)个指针，每个指针指向不同的磁盘块。

B-Tree结构：

B-Tree介绍

如上图，在根节点的磁盘块中，有数据项17和35，包含指针 P1，P2，P3，P1指向比17小的磁盘块，P2指向17与35之间的磁盘块，P3指向比35大的磁盘块。

非叶子节点不存储真实数据，只存储指引查找方向的数据项，比如17和35并不存在于数据表中。
真实数据存在于叶子节点，即3、5、9、10…

查找方式举例：

例如要查找9这个元素，先到根节点的数据块查找数据项17和35，比较9比17小，通过指针P1指向8和12的数据块，比较9比8大且比12小，则通过指针P2指向9和10的数据块，找到9，查找结束。
在此查找过程中，一个发生3次IO，即：

1. 加载根节点进入内存
2. 通过指针P1将包含8、12的数据项的磁盘块加载进内存
3. 通过指针P2将包含9、10的数据项的磁盘块加载进内存

查询结束

B-Tree索引的优势：假如数据量达到百万级，如果用全文扫描查询，则每一个包含数据项的磁盘块都要发生一次IO，这样查询的成本就会变得非常高。但是如果用B-Tree索引查询，假定一个节点可以容纳100个数据项，那么3层的B树可以容纳100万条数据，且只需要发生三次IO，性能的提高不是一星半点。

索引适用情况

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段
3. 查询中与其他表关联的字段，外键关系
4. 高并发下建立复合索引
5. 查询中排序的字段，如果排序字段若通过索引去访问将大大提高排序速度
6. 查询中统计或者分组的字段

索引不适用情况

1. 频繁更新的字段，因为每次更新不仅更新记录也要更新索引，虽然这样提高了查询速度，但同时也降低了更新的速度
2. where条件里用不到的字段
3. 如果表记录太少，则没有必要建立索引
4. 数据重复较多，分布均匀的表字段，比如字段值只有0和1