MySQL基础 索引回顾------聚族索引和非聚簇索引

数据库的索引

索引的本质

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

在我看来，数据库索引其实就是一种空间换时间的方式，提高获取数据的效率。

 

常见的索引模型

• 哈希表

通过键值对来取值，速度快，但不适合范围查询，范围搜索时不得不遍历整张表。

哈希表一般只适用于等值查询的场景，比如Memcached等NoSQL引擎。

 

• 有序数组

有序数组有着很好的等值查询和范围查找的效率。通过二分法进行等值查找时时间复杂度是O(logN)，范围查找也可以通过简单的往一个方向遍历有序数组实现。

但有序数组的插入效率很低，插入一个数据可能需要挪动后面所有的数据。

所以有序数组索引只适合用与静态存储引擎，如一些不会再修改的数据。

 

• 二叉搜索树

根据二叉搜索树的特点，查询每个元素的时间复杂度都为O(logN)，有很好的查询效率。

但二叉搜索树有时候可能退化成线性树，如果要防止这种情况发生，我们就需要花费时间复杂度为O(logN)的操作来保证二叉树的平衡，变为平衡二叉搜索树。

当然还有平衡度没那么高的红黑树，可以减少平衡操作带来的花销，但同时搜索的效率也会有所下降。

但其实时间复杂度并不是最大的影响因素，对于数据库磁盘的I/O消耗才是大头。

二叉搜索树的搜索效率是很高的，但一般数据库引擎不会使用二叉树，因为每个节点存储的数据量少，如数据到达百万级别以后，树的高度可能会达到20层（2^20 = 1048576），这样有的数据就要经过20次磁盘I/O才能拿到。

所以一般使用的是N叉树。

 

• B树和B+树

B树，也称B-树，是一种平衡的多叉树，它的每个节点可以存储多个数据，由于是多叉树所以在大数据量的时候B树也可以控制住高度，所以有着很好的查找效率，但它的每一个节点都包含了完整的数据。

B+树和B树不同的地方就是B+树除了叶子节点，其余的节点都只存索引，这样在查找数据的时候磁盘可以减少读取一些不必要的数据。

而且B+树在叶子节点之间还增加了指向下一个叶子节点的指针，更有利于顺序遍历。

对于B树和B+树：

1. 二者在范围查询需要遍历叶子节点时，B树必须使用中序遍历按序扫库，而B+树只需直接根据叶子节点间的指针遍历即可，非常方便。

2. B+树中除了叶子节点，其余节点都只存储了索引信息，而B树每个节点都存储了完整的数据信息，所以相同的数据量下，B树会比B+树更高。

所以数据库底层数据结构选择的是B+树。

（但这并不是说B+树就一定比B树好，有一些频繁的搜索是会选用B树的，因为不论是B树还是B+树，搜索时都需要把数据页读入内存，如果是频繁的搜索那么可能需要搜索的内容早已经在内存中了，而在内存中的话就不存在磁盘I/O的消耗问题，所以是B树占优势。）

 

MySQL中不同引擎的索引实现

在MySQL中，索引是在存储引擎层实现的，而不是在服务器层。所以不同的存储引擎的索引实现是不同的，这里主要讨论在MyISAM和InnoDB中B+树索引结构的区别。

MyISAM中索引的实现

在MyISAM中使用的是非聚簇索引，它的叶子节点仅仅只是保存数据行的地址信息。

非聚簇索引的主键索引和辅助索引之间没有什么大的不同，叶子节点都是记录着数据行的地址信息，但是主键索引要求key是唯一的，而辅助索引的key可以不唯一。

非聚簇索引在磁盘上存储成三个文件，其中的索引和数据是分开存放的，会先通过key得到数据的索引地址，然后通过索引来读取数据。

 

InnoDB中索引的实现

在InnoDB中使用的是聚簇索引，它的叶子节点包含了索引和完整的数据信息。

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键。

如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。

InnoDB的辅助索引叶子节点记录的是主键的key值，当需要获得全部数据时需要根据得到的key回表去主键索引再进行一次搜索才能得到。

 

聚簇索引和非聚簇索引的优劣

这里针对上述的不同点讨论各自的优劣。

1. 磁盘I/O次数

InnoDB 的叶子节点存储的是完整的数据信息，所以当把该页加载进内存之后，要读取信息时我们可以直接在内存中读取，减少了磁盘的I/O。

MyISAM 的话，可能每一次读取都需要一次磁盘I/O。

2. 辅助索引的指针维护

InnoDB 的辅助索引存的是主键，这样当行移动或数据页分裂时无需更新辅助索引的信息。

MyISAM 中辅助索引存的是数据的地址，所以只要数据位置发生变化就要更新辅助索引。

3. 通过辅助索引查找

InnoDB 如果要通过辅助索引查询到行所有的信息，就要通过主键回表到主键索引再进行一次查询，即经过两次查询。

MyISAM 可以通过辅助索引存的数据索引地址，一次查询直接得到数据。

 

---

参考：

http://blog.codinglabs.org/articles/theory-of-mysql-index.html

https://www.cnblogs.com/hoxis/p/10117674.html

https://student-lp.iteye.com/blog/2114128