SQL数据库索引数据结构与原理

SQL数据库索引数据结构与原理

文章推荐：CodingLabs - MySQL索引背后的数据结构及算法原理

--索引类型，分两类：
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
--不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

为了提高获取数据的效率，使用索引。索引是数据结构。

为了查找数据有很多查找算法，但是对于一些优秀的，例如二分查找，二叉树查找来说，其要么需要有序的队列，要么需要二叉树上才可进行。而借用这些数据结构，来进行高效数据查找便是索引。

btree

对于B-与B+树请不了解的自行了解，这里不过多讲解。

相较于传统的B+树，数据库系统所使用的B+树是带有顺序访问指针的。每一个叶子节点都有一个指针指向下一个相邻叶子节点。以此提高区间访问的性能，在访问至6后，可直接顺序访问6以后的数据。

• 索引存储在哪里？

索引一般来说都是存储在磁盘中的，索引文件太大了，不可能一直存储在内存中的。而使用索引的过程，就需要频繁的进出磁盘，即IO操作。相较于其他消耗，IO操作消耗要大得多。所以尽可能地使用减小IO操作，提高IO效率的数据结构就尤为必要了。

因为文件是在磁盘中进行存储的，根据空间局部性原理，文件将存储在一段连续的存储空间中，所以主要的时间消耗相对于找到文件寻道的时间，旋转读取所花费的时间将变得更大。为了提供IO效率，预读就显得尤为重要。

我们需要知道的是，对于磁盘与内存的信息数据交换都是以块为单位，这样的数据块被称作页，即一份文件被分为大小相等的块。

• 为什么选取B树？

我们已知查找一个数据需要最多访问树高个节点，

通过磁盘预读的方式，将树的每个节点设置为一个页。而获取一个页就需要以此IO操作。所以在新建节点时，会直接申请一个页的空间。

因此，我们可以需要多少次IO操作，就是和树高有关，树高越小，IO操作越少。

而B树相较于红黑树来说，树高h更小。

MyISAM（非聚集）索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

我们需要明确的是B+树的特性之一是内节点只存储key，而不存储data.但是对于叶子节点来说，既有key也有data，这里的data为数据的地址。对于主索引的key是不可以重复的，而辅助索引key是可重复的。

所以MyISAM为按照B+树算法搜索索引，找到数据地址，通过地址找到数据记录。

注意这里的索引文件与数据文件是分离的

InnoDB（聚集）索引实现

使用B+树作为索引结构。索引文件与数据文件存在一起，叶子节点data中存储了完整的数据记录，索引的key就是数据表的主键。

因为InnoDB的数据文件需要主键，如果没有指定，系统会自动选择一个唯一标识的列作为主键，如果不存在，系统会自动生成。

InnoDB不是通过地址再去寻找数据，而是直接通过辅助索引的数据作为主键。

：即通过辅助索引获取主键，然后通过主键检查获得记录。

• 为了避免因为添加非自增型数据导致B树调整，这样不仅仅将会增多过多的IO操作，而且将造成过多的碎片，这样也不利于管理，所以推荐使用自增字段作为主键是一个好的选择。

• 不仅仅是要使用自增字段作为主键，而且使用与业务无关的自增字段作为主键。

最左前缀匹配

假设有一个表，内元素分别为<name,id,class>

在表中，<name,id,class>为主索引

当我们

select * from [table] where name = '' and id = '' and class = '';

只要主索引存在所有元素，就可以进行使用，无论我们以怎样的顺序去查看效率相同。

当我们

select * from [table] where name = '';

这是用到了只是精确匹配索引的左边连续一个或多个列。

select * from [table] where name = '' and class = '';

这里的索引因为name在索引第一列，使用到了。但是对于class,虽然其也在索引中，但是无法与左前缀连接，所以需要进行扫描过滤，无法使用主索引。为了使用索引可添加辅助索引<name,class>，或者通过IN把id写上。

select * from [table] where name = '' and class = '' and id in ('','','');

显然这样的操作是针对于id较小的存在，对于较大的直接建立辅助索引。

未更完！