Mysql之索引数据结构

什么是索引？

说到索引这个词,联系日常生活中我们立刻就会想到书本，每一本书都有它的目录，目录就是索引，它能帮助我们快速锁定书本中的内容。在Mysql中，索引是帮助MySQL高效获取数据的数据结构，能够加快数据库的查询速度，提高数据库的性能。

磁盘IO

数据库的数据都是存放在本地磁盘里的，每次查询数据都会进行磁盘IO读取数据，将读到的数据加载到内存中比较，Mysql InnoDB默认一次磁盘IO的大小是16K.

// InnoDB默认一次磁盘IO
SHOW GLOBAL STATUS like 'Innodb_page_size';
// 展示16K
Innodb_page_size,16384

索引数据结构

如下数据表如果我要查找select * from t where col2 =11,如果没有索引我需要一行一行查找，总共需要查询7次才能找到id=7的数据行.

二叉树

二叉树是一种简单的树结构，每个节点最多有2个分叉，左子树和右子树数据顺序左小右大.

可以看到当使用了二叉树数据结构，查找column2 = '11’到这条数据，
18 -> 15 > 11.只需要3次了，相比较没有索引的情况，查询效率增加了.
但是二叉树的查询效率高度依赖于树的平衡情况，如果树不平衡，可能会导致查询效率的明显下降。如下图：

二叉树可能变为链表结构，如果要查询’5’这个数据，使用了二叉树索引数据结构和没有索引查询并没有什么区别.

平衡二叉树

为了避免上述二叉树的缺点，进而引入了一种新的数据结构那就是平衡二叉树。
它有一个非常严格的要求：必须保证左右子树高度差不超过1.

它虽然避免了二叉树变成链表结构的尴尬，但是如果数据量非常非常大的情况下，树的层级就会非常的高，
树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作.这样就会进行多次的磁盘IO,效率就会非常低下，所以AVL也不是Mysql InnoDB的索引结构。

B-Tree

假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）。上面我们已经知道一次磁盘IO默认是16K.那么上述二叉树一次IO有效数据量只有16字节，空间利用率极低。所以引进B-Tree结构.

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。
BTree做了改进就是它每个结点不止一个元素,在每个结点存储多个元素，每个元素不重复，并且每个结点中的元素从左到右依次递增排序。每个结点都有索引和数据。
假设每个节点数据为1K,按照上述计算16 * 1024/(8B+4 * 2B+1K)=15
第一层：15
第二层：15 * 15 = 225
第三层： 15 * 15 * 15 = 3375
所以三层树高B-Tree 大约可以存储 3375行数据（1个节点代表一行数据）.

还是回到原来的问题，当数据量很大的时候，树的层级还是会很深，又会进行多次磁盘IO，效率就低下了。而且查询不稳定，可能第一次IO就查到数据了，也可能在最后一层叶子结点查询到数据。而且B树不支持范围查询的快速查找.所以它也不是Mysql InnoDB的索引结构。

B+Tree

B+Tree是B-Tree的变种。只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。
还是上述例子，我们使用B+Tree计算3层树高可以搜索的数据行
第一层：16K/ (8B + 4 * 2B) = 1024
第二层： 1024 * 1024 = 1048586
第三层： 1048586 * （16K/ (8B + 4 * 2B + 1K)） = 15728640
可以得出三层树高的B+Tree可以搜索的数据大约1500W

// 非叶子节点不存储data，数据全都存放在叶子结点.
这样可以保证每次磁盘IO读到的索引元素更多，降低树的高度，增加命中率。还可以可以保证查询效率的稳定。
// 叶子结点之间有结点指针
因为索引都是排好序的，这样可以适用于范围查询，提高效率.

聚集索引和非聚集索引

// 聚集索引
主键索引就是聚集索引,叶子结点上保存的就是索引和全部的行数据.
// 非聚集索引
不是聚集索引的就是非聚集索引,叶子结点不包含数据的，放的是主键的key.
叶子节点不放全部数据的原因：1.可以有效节约磁盘空间 2.保证数据一致性

最左匹配原则

MySQL的索引最左匹配原则是指在使用联合索引（Composite Index）时，最左侧的索引列将具有最高的选择性和优先级。

假设有一个联合索引 (col1, col2, col3)，查询条件为 WHERE col2 = 'value'。
根据最左匹配原则，MySQL只会使用索引的最左侧列 col1 进行匹配，而不会使用后面的列 col2 和 col3。

覆盖索引

覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段. 如下：

// 准备
表user有字段 id 、name 、age、job、skill，创建联合索引idx_nas.
create index idx_nas on user(name, age, job); 
// sql1查询-> 此时idx_nas联合索引中包含了结果字段，不需要回表查询主键获取全部数据，这就是覆盖索引
select name,age,job from user where id ='8';
// sql2 -> 字段skill值不在idx_nas联合索引中，所以还需要回表查询主键索引获取对应skill的值
select name,age,job,skill from user where id ='8';
// 总结
覆盖索引就是根据联合索引一次就能查询到结果，不需要回表再查一次主键索引，可以大大提高查询性能.