mysql 索引 简书_MySQL索引

一、基础知识

主存存取是根据地址总线和数据总线上的信息完成。

磁盘存取涉及磁头磁盘等机械动作。因此需要特殊的算法提高查询速度。

磁盘块是16KB，页的4倍。

二、索引

索引是帮助高效获取数据的数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现快速查找算法。这种数据结构就是索引。索引一般较大，以文件形式保存到磁盘中，这就涉及到怎么样快速查询索引的方式。

索引的原理：根据建立索引的字段建立某种数据结构，存放字段值以及对应记录的物理地址，从而在搜索的时候根据字段值得到物理地址进而访问记录。

三、索引算法

B-Tree

在B-Tree中按key检索数据的算法：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。

一个度为d的B-Tree，假设索引有N个key，则其树高h的上限为logd((N+1)/2)，检索一个key，其查找节点个数的渐进复杂度(磁盘IO次数)为O(logdN)。可以看出，B-Tree是一个非常有效率的索引数据结构。

B树中degree表示节点中可以放入的元素个数，称为度或阶。B数是多叉。

B+Tree

与B-Tree最大不同是，内节点不存储data，只存储key。叶子节点不存储指针。

一般在数据库系统或文件系统中使用的B+Tree结构都在B+Tree的基础，增加了顺序访问指针。提高区间访问的性能。符合计算机的局部性原理。并利用磁盘预读机制，可以大幅提高查询速度。

B+Tree在叶子节点之间有双向链表。叶子节点中的数据是有序的。

B+Tree支持两种查找：一种是基于主键的范围查找和分页查找，另一种是从根节点开始的随机查询。

其他树

二叉树

BST

AVL

红黑树

二叉平衡树

二叉平衡树

有序二叉树

有序二叉树

有序二叉树

四、存储引擎

不同数据文件在磁盘上的组织形式。

MyISAM存储引擎

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

data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

非聚集索引，即索引文件和数据文件是分离的。

InnoDB存储引擎

InnoDB的数据文件本身就是索引文件。

表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这种索引叫做聚集索引。

memory 存储引擎

支持哈希索引。

五、索引分类

主键索引、唯一索引、二级索引、全文索引、组合索引

六、索引的数据结构

哈希(只适合等值查询、碰撞、扰动函数)

B+Tree [ 二叉树(树的深度较大，导致IO次数过多)、B树(内节点保存数据导致存储信息较少)]

七、索引的匹配方式

全值匹配、最左前缀匹配、、列前缀、范围值、只访问索引

八、概念

聚簇索引，数据和索引是聚集存储。

最左匹配，组合索引的最左边的列。

索引下推，组合索引中的多个列查询条件时，直接在存储引擎过滤，不在server中过滤。

回表， 二级索引(辅助索引)(非聚簇索引)的时候，需要再查询主索引(聚簇索引)。需要避免。

索引覆盖，非聚簇索引的叶子节点包含查询的所有字段，不需要回表的时候。

参考