索引浅入

最新推荐文章于 2024-10-16 11:14:53 发布

CSDN_lol

最新推荐文章于 2024-10-16 11:14:53 发布

阅读量217

点赞数

文章标签：数据库索引

原文链接：https://www.cnblogs.com/Uglthinx/p/10771072.html https://blog.csdn.net/qq_38038480/article/details/81592828

版权

一、索引

当数据量到达一定规模时，我们通常会对经常使用的字段建立索引，来加快数据的查询。首先需要强调的是索引的本质是数据结构，前辈们经过不断完善得到了几种复杂度较低并且能够降低磁盘IO的数据结构，这里要说的是B树与B+树，他们被广泛应用在文件系统与数据库系统中。

B-Tree

B树逻辑上是一颗多叉树，3阶B树如下：

m阶B树满足以下几个条件：

非叶子节点最少有m/2颗子树（即B树的度为m/2）
叶子节点在同一层，每个节点最多有m-1个升序排列的key（索引列）和m个指针，key与指针相互间隔

搜索二叉树的查询复杂度为O(log2N)，而B树的复杂度为O(logm/2N)，对于N=62*1000000000个节点，如果度为1024，则logM/2N <=4，可以说它是效率很高的数据结构。

B+树

B+树是B树的变种，区别有三点：

非叶子节点只存储key，不存储data；叶子节点存储所有key与data，不存储指针
叶子节点增加了顺序访问指针
每个节点最多有m个升序排列的key

上述区别换来的优点包括：

非子节点可以存放更多的key，具有更好的空间局部性，提高缓存命中率
叶子节点相链便于区间查找，顺序查找替代B树的递归查找。

为什么选择B+树

首先要意识到数据检索的时间主要耗费在磁盘IO（寻道时间、旋转时间）上，因此要尽量减少IO次数。对树形结构的数据来说，树的每一层代表需要一次磁盘IO查询，因此设计了“扁平”的B树与更扁的B+树。另外，由著名的局部性原理，访问的数据通常比较集中，磁盘每次IO时会预读数据，预读的长度为页（4k）的整数倍，B/B+树新建节点会申请一个页的空间，因此取一个节点只需要一次IO（非叶子节点可存储到内存中）。

索引创建过程

mysql创建索引是通过online create index，减少业务停写时间，创建索引期间业务能正常工作。

步骤：

等待当前所有事务执行结束；新事务更新数据会把新建索引记录到Row Log中
构建索引，从主表读出数据并排序。使用临时文件进行外部排序方式，单线程两路归并。
把增量数据从Row Log更新到索引表中

二、MySQL存储引擎

首先区分聚簇索引（按主键聚集）与非聚簇索引：

二者都使用B+树作为数据结构
聚簇索引的data存于主键索引的叶子节点中，得到key同时得到data，非聚簇索引数据存于独立的地方，叶节点保存的是数据的地址。
聚簇索引的辅助键索引（非主键索引，例如employee表中对name建索引）叶节点存储主键而非数据（为了节省空间，缺陷是需要到主键索引中二次查询）；非聚簇索引叶节点保存数据的地址。

聚簇索引的优势在于找到主键同时得到data，省去二次磁盘IO；另外B+树在插入或删除节点时周围节点地址会发生变化，对非聚簇索引来说需要更新所有B+树的地址指针，增加开销。

InnoDB

InnoDB使用聚簇索引（MyISAM使用非聚簇索引），其磁盘管理逻辑单位是Page（不同于上述内存中的页！），每个Page大小为16k，使用32位int标识，对应innoDB最大64TB的存储容量。

每个Page包括头部、主体、尾部三部分：

其中头部包括id与相邻Page指针（构成双向链表）；

主体即B+树节点的存储，其中包括很多Record（节点）包括四类：

主索引非叶子节点：定位Page
主索引叶子节点：包括key与该key对应的所有列（mysql表中的一行）
辅助索引非叶子节点：定位Page
辅助索引叶子节点：包括索引键值与主键值（key）

主键选择

因为数据存于主索引中，要求一个节点的各条数据记录按主键顺序存放，当一页达到装载因子（15/16）会自动开辟新的页。如果使用自增主键，每次插入新纪录都顺序添加到索引节点的后续位置，否则会节点中key会一直移动。

最左匹配原则

在联合索引中对a，b两个字段建立索引（a, b），在查询时只有包括a时才会查询索引。

如上图（a, b）联合索引，在a相同时，b按顺序排列。在遇到范围查询时之后的字段会停止匹配。因为a是范围，b无序。

三、 Innodb

1. 简介
索引是一种排好序的用于快速查找的数据结构。
Innodb
这里写图片描述
**根据官网手册InnoDB支持B-tree索引、聚簇索引(Clustered indexes)、全文索引(Full-text search indexes)、不支持hash索引（InnoDB在内部利用哈希索引来实现其自适应哈希索引功能）、不支持T-tree索引。**Geospatial indexing support（坐标索引支持），支持索引缓存；
支持外键、支持事务。锁粒度是行锁。
2.聚簇索引

注：与myisam不同的是，Innodb叶子节点保存的是所有的数据。
每个Innodb表都有一个特殊的索引被称作聚簇索引，它存储了一行的数据。主键可以看做是索引的同义词，InnoDB会使用聚簇索引对DML操作进行优化：

在表上定义主键时，Innodb将其作为聚簇索引；
如果没有定义主键，MySQL将找到第一个UNIQUE索引，其中所有键列都是NOT NULL，而InnoDB将它用作聚簇索引。
如果没有主键，也没有合适的索引。Innodb将使用行ID值生成列名为GEN_CLUST_INDEX的隐藏聚簇索引。它是一个6字节的字段，在插入新行时，单调增加。因此物理存储顺序就是其插入的顺序。

通过聚簇索引查找是很快的，因为索引直接指向包含所有数据的页。

3.辅助索引
除了聚簇索引之外，其它都被称为辅助索引（Secondary Indexes）。辅助索引中的每条记录都包含了一行的主键列及辅助索引指定的列。Innodb使用主键值搜索在聚簇索引中的某一行的值。因此有一个短的主键是更有利的。

4.索引的物理结构
除空间索引外，InnoDB索引是B-tree数据结构。空间索引使用R-trees，R-trees是用于索引多维数据的专用数据结构。索引记录存储在其B-tree或R-tree数据结构的叶子页中。索引页的默认大小为16KB。
Innodb会剩下1/16的页空间，如果记录是顺序插入，索引页会利用15/16；如果是随机插入，会利用1/2到15/16。
通过设置MERGE_THRESHOLD，该值表示当页面的利用率降低到该值及以下时，会进行页面合并，节省空间。默认是50%。
也能通过设置innodb_page_size来设置页面大小。

5.排序索引构建
InnoDB执行批量加载，而不是在创建或重建索引时一次插入一个索引记录。这种索引创建方法也称为排序索引构建（Sorted Index Builds）。空间索引不支持排序索引构建。全文索引支持排序索引。
在排序索引构建构建期间，redo日志被禁用，但会设置一个检查点确保索引构建能忍受宕机或失败。

6.全文索引
InnoDB 全文索引(FULLTEXT)是一种倒排索引(inverted index)，它储存单词列表，并为每个单词储存出现的文档列表。
具体倒排索引介绍可以查看文章：正排索引和倒排索引；

四、 myisam引擎

1.索引
myisam支持B-tree索引、支持全文索引 (Full-text search indexes)、坐标索引支持 (Geospatial indexing support)、不支持聚簇索引、不支持hash索引、不支持T-tree索引。
另不支持外键、不支持事务。锁粒度是表锁。存储限制是256TB。
MyISAM表有以下特征：