01-索引的本质

最新推荐文章于 2021-07-22 21:28:16 发布

liaoqing1999

最新推荐文章于 2021-07-22 21:28:16 发布

阅读量156

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/liaoqing1999/article/details/109987937

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

索引的本质（Mysql）

定义

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构提取句子主干，就可以得到索引的本质：索引是数据结构。

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

比如我们如果通过索引来

select *  from test where a = 0

就会顺序查找整个表来寻找a=0的数据

索引数据结构

二叉树
红黑树
Hash表
B-tree

二叉树

对于每一个节点来说，它的右边子节点大于本身，它的左边子节点小于本身

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JjtavCb1-1618299173863)(https://note.youdao.com/yws/api/personal/file/6A7EC5015F944021AFCF652A9EE4DA03?method=download&shareKey=30be3cbd940e3b5fe47ae45bb26ea2fe)]

如我们对Col2来设置索引，并为它建立二叉树

那我们查找Col2 = 89的元素而言，没有索引则需要扫描全表，而有了二叉树，则只需要从34->89两次查找就找到了我们所需要的数据。

但是二叉树无法满足更多的情况。

如我们对Col1设置二叉树索引，那么就会形成下面的二叉树，以下生成网站来源于旧金山计算机科学大学
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BzkkcCQ4-1618299173865)(https://note.youdao.com/yws/api/personal/file/09BBDA618FF04196B4353475B70E7974?method=download&shareKey=7f8b93eb36ca9f801e95280fcd31a6c6)]

这样就会形成一个单链，同样查找Col2 = 89，即Col1= 6 需要6次，性能上没有什么提高

红黑树

还是针对上面的例子，对Col1生成红黑树如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WB91hQ7t-1618299173866)(https://note.youdao.com/yws/api/personal/file/2A59BCAFAFCB4916B3A39420C0013DE0?method=download&shareKey=41972aecae0c90c28c7cb2685f2168a8)]

看上去是比二叉树更平衡了，对于查找Col1=6，需要1->4->6三次就查找到了。

但是红黑树同样也存在着以下缺点：

对于数据量较大的元素，会形成很深的层级，如100万，则会形成log2 100万的层级

Hash表

还是针对上面的例子，对Col1生成Hash表如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lYqPuhKP-1618299173868)(https://note.youdao.com/yws/api/personal/file/4C26B67BB90E466F9C232B1A6DFBF5FF?method=download&shareKey=28746177dd47f5eae34d7d8eab979bc8)]

可以看出，对于先插入的元素，如果需要找到它（如2），则需要遍历数组元素上链的所有元素，（28->54->15->2）

那么同样对于大量的数据而言，不够完美，我们最终需要的是对于百万的数据能够查找3-5次就能找到是最好的。

当然，有些hash算法算出的散列值是可以唯一（或者接近唯一）的，那么对于查找而言，便只需要进行一次hash，就可以快速定位到数据所在的地方。

但是这种对于 where id>6 这种就比较麻烦，所以常见生活中一般使用的索引类型还是B+tree （B-tree是一样的道理，范围查找比较麻烦）（B+tree在叶子节点设置了指针，则就可以解决这个问题）

B-tree

B-tree本质上还是一颗树，只是增大了一个节点的容量，比如之前的二叉树一个节点只能放1个数据，而现在一个节点就可以放多个数据

如下图（节点容量为3）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-97zzumk0-1618299173871)(https://note.youdao.com/yws/api/personal/file/338B1E0E6C48405F9B890BD4C3A94631?method=download&shareKey=9e0e62eab77d3e556a217d1feb99eae5)]

那么对于大量数据而言，我们只要将节点的容量设置的大一点，就可以实现树的层级只有3-5层，并且我们可以发现，节点内部的索引是递增排列的，对于查找也非常方便。

Mysql索引使用的数据结构:B+Tree

针对上面的例子，生成B+tree

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TdICqnZf-1618299173874)(https://note.youdao.com/yws/api/personal/file/D8A596B8C9284F35B16D17E4637F350E?method=download&shareKey=1983a2f5c4562b0d139e885e3084daf0)]

B+Tree和B-tree的区别

非叶子节点不存储data（指针），只存储索引（会存在冗余），但是可以存放更多的索引了
叶子节点包含所有索引字段
叶子节点用指针链接，提高区间间访问的性能

Mysql 默认节点大小

SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

默认为16384（字节）（16kb）

举例：

如用bigint类型建立索引：8个字节

mysql节点指针大小：6个字节

即这个索引总共占14字节

即一个节点可以存放：16384/14 = 1170 个索引元素

假设树的层级为3

则叶子节点有1170*1170 = 1,368,900 个叶子节点

每个叶子节点：16kb/1kb（假设索引+数据为1k（真是情况数据可能只存了一个地址引用，达不到1k）） = 16 条数据

则整个树可以存放1,368,900*16 = 21,902,400条数据

Mysql查找索引过程

首先，所有的节点都是存在磁盘上的
先将根节点加载至内存，在内存中将需要寻找的索引和根节点内的索引去比较（因为是按顺序存储的所以要么存在，要么在左边节点的指针指向的下一个节点）
如果找到了，便直接由地址去得到元素
如果没找到，则去指针指向的下一个节点，将该节点加载至内存
重复循环，直至找到
由地址得到数据

MyISAM引擎和InnoDB引擎

MyISAM引擎（非聚集引擎）

MyISAM引擎索引文件和数据文件是分离的（非聚集引擎）

非聚集索引-叶节点只包含了存储该条数据的磁盘地址

MyISAM引擎的表会建立三个文件

test01.frm   //存放结构
test01.MYD	 //存放数据
test01.MYI	 //存储索引数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lDbVx4sl-1618299173875)(https://note.youdao.com/yws/api/personal/file/292BAFD077A7432DB05D5330FBA71665?method=download&shareKey=04f4d3de365a4e194e8d94551404f305)]

查找索引 ->到叶子节点-> 拿到地址->对应磁盘数据

InnoDB引擎（聚集）

表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引-叶节点包含了完整的数据记录
InnoDB必须要有主键（因为第一点）！（没有设计主键会默认找一个合适的或者新增一列）并且推荐使用整形的自增主键（整形的原因是因为好比较大小）（递增的原因是因为在下一次插入时，因为不是递增，所以要找到是在哪两个索引中间，但是有可能找到的这个节点它已经放满了，所以它需要移动其他数据，会造成性能开销）
非主键索引结构叶子节点存储的是主键值(一个索引对应的就是一颗B+树，而我们的主键索引已经在叶子节点上存储了数据，所以对于其他索引叶子节点只需要存储主键值就可以了————即节省存储空间)（当进行DML操作时插入更新删除等，同一行记录的页地址会发生改变，因非主键索引保存的是主键的值，无需进行更改）

InnoDB引擎的表会建立两个文件

test02.frm	 //存放结构
test02.ibd	 //存放索引+数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-X1LqOhig-1618299173876)(https://note.youdao.com/yws/api/personal/file/4354EDF1B09D491DA284FD0535C101BF?method=download&shareKey=aab5d3de18f4ae9966f8455e567807c8)]

联合索引

将几个字段联合起来作为一个索引，如（a,b,c）

如果将联合索引作为主键索引，则叶子节点存放的data就是除了索引外的其他的数据

命名规则：表名_字段名
经常出现在where条件中，考虑加索引
数据量少的字段不需要加索引
如果where条件中是OR关系，加索引不起作用
符合最左原则（一个查询可以只使用索引中的一部份，但只能是最左侧部分）

例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

所以说创建联合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，联合索引非常有用；仅对后面的任意列执行搜索时，联合索引则没有用处。

利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。

联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

同时存在联合索引和单列索引（字段有重复的），这个时候查询mysql会怎么用索引呢？
如（a,b),(b)

这个涉及到mysql本身的查询优化器策略了，当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引；

其他知识点

B+ Tree索引和Hash索引区别？

哈希索引适合等值查询，但是无法进行范围查询
哈希索引没办法利用索引完成排序
哈希索引不支持多列联合索引的最左匹配规则
如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

聚簇索引、覆盖索引

索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引
索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引
聚簇索引查询会更快，因为主键索引叶子节点直接就是我们要查询的整行数据了，查询只会查一次，而非主键索引需要回表查询多
覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。
当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。

Index Condition Pushdown（索引下推）

MySQL 5.6中对索引的重要优化

默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。

和联合索引配合使用

people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people 
WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果没有使用索引下推技术，则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据（因为索引不支持like “%X%”，但支持like “X%”），返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。

如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054’的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

查询优化器

Mysql执行一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。

这个成本最低的方案就是所谓的执行计划。优化过程大致如下：

根据搜索条件，找出所有可能使用的索引
计算全表扫描的代价
计算使用不同索引执行查询的代价
对比各种执行方案的代价，找出成本最低的那一个

liaoqing1999

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
01-索引的本质

索引的本质（Mysql）定义MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search
复制链接

扫一扫