mysql索引专题考点总结

聚集索引和非聚集索引的结构

image.png

[ 概念 ] 聚集索引, 非聚集索引, 回表 , 覆盖索引

1. 索引用B+树存储, 假设索引的叶节点存放的是行记录, 那么多个索引就会存放多份完全相同的行记录, 造成大量冗余 ; 因此实际的索引分为两种 : 聚集索引和非聚集索引

2. 聚集索引是B+树结构, 叶节点存放的是行记录 ; 非聚集索引也是B+树结构, key存放索引的行的数据, value存放聚集索引的值(主键索引), 如果要查的数据全部在非聚集索引中匹配, 那么不需要查询聚集索引就完成了检索, 我要查找的数据在索引里就找到的情况就称这个索引为覆盖索引; 如果还需要额外的非索引字段, 则用主键值找到对应的行记录, 这个过程叫做回表

非聚集索引里存放什么数据

非聚集索引里只存放改字段的数据; 例如有一张表的字段有a,b,c, 索引建立在b字段上, 叶节点的key就只存储了b字段的值

总结: 使用索引要注意以下两点

1. 非聚集索引中存在的数据在行记录里有一个完整且重复的数据, 索引越多占用空间越大 , 尽量避免超过5个索引在同一张表中

2. 因为非聚集索引本身占用空间, 所以要尽可能避免创建重复或冗余的索引

B+树结构

image-20200613152239237.png

从上图可知b+树有如下特点:

1. b树不是二叉树 , 节点不一定限制为2个

2. 一个节点不止存一个元素

3. b+树的叶节点数据都会有一份冗余在父节点上

4. 叶节点上有单向的指针指向下一个节点

mysql中使用的b+树和标准b+树的区别是

image-20200613153726900.png

对比两张图可知:

1. mysql使用的并非完全标准的b+树, 区别在叶节点是双向链表, 为了在使用范围条件如 a ≤ 5 能返回5之前的数据

2. 叶节点的父节点取叶节点最小的值, 得到 1 , 3 , 5 ; 此时已经完成了索引功能; 为了进一步优化索引, 在父节点之上还有更多的节点, 用来存几个父节点中最小的值

mysql 索引和全表扫描查找数据的方式分别是什么 ?

1. 索引是按照根节点到叶节点的顺序, 利用二分查找的方式快速定位到条件指定的行记录集, 也就是从上到下检索

2. 全表扫描只用到叶节点, 从第一个叶节点开始一个个往后走, 因为索引总是排序的, 当走到一个不匹配的节点则表示检索完成

mysql 索引如何实现快速查找数据 ?

image-20200613160752897.png

如上图所示, 假设执行语句 select * from tableA where id = 4 , 假设id 字段已经设置为索引字段

1. 检索根节点, 在1和5之间判断, 4 < 5且 4 > 1 , 目标在左边的节点下

2. 1的子节点有1和3 , 4 >1 且4 > 3 ,那么4在节点3下

3. 3的子节点有3和4 , 遍历节点的值找到4, 完成检索

mysql 如何实现范围查找, 如何判断范围查找是否使用索引

1. 判断范围查找是否用索引的方法就是把条件换成＝ , 例如 fieldA > 5 换成 fieldA = 5, 如果还能用到索引那么范围查找也能用到索引

2. 范围查找的原理是先找到条件的记录, 再返回记录之前 / 之后的数据; 例如找 number between 1 and 5, 先找到 number = 1 , 再找number = 5 , 最后返回1和5之间的数据; 所以说使用范围查找跟直接用等于条件是一样的, 区别是返回的数据不同

使用通配符%开头的模糊查询会导致索引失效

这一点在官网上已经表明了, 千万不要这样使用, 原因是违背了索引的查询规则

image-20200613154258228.png

为什么建立了索引不利于写数据

因为索引会对新插入的数据进行自动排序, 索引加的越多需要做的额外处理就越多, 响应速度就越慢

注意: 自动排序不是发生在检索数据的时候, 而是在插入的时候

mysql的最左前缀原则是什么原理, 为什么模糊查询第一个值不能是通配符%

最左前缀原则和模糊查询通配符问题, 本质是同一个问题

要从联合查询的查询原理开始说起, 假设字段 fieldA , fieldB , fieldC 按顺序组成联合索引 index ( fieldA , fieldB , fieldC ), 那么我在使用索引检索数据时要先缩小 fieldA的范围, 当找到fieldA 的范围再用fieldB继续缩小范围 , 最后是fieldC, 最后得到的集合就是我要检索的数据 ;

通配符查询也是相同的原理, 假设通配符是这样的 "hello%", 索引按照从左往右的顺序进行范围缩小, 我们知道字符串也可以进行排序并且索引总是会进行自动排序(这一点官网也提到了) 先找到h开头的数据, 再检索e开头的数据...最后完成o, 返回结果

因为多个数据的查找总是从左往右进行的, 所以第一个数据的缺失会直接导致索引无法工作 , 进而选择全表扫描

字符类型的索引是否自动排序, 排序规则是什么?

任何类型的索引都会进行自动排序, 假如字段是char / varchar 排序规则根据创建表时指定的字符集和排序规则进行自动排序

image-20200613161855927.png

b+树的高度

在数据库中，B+Tree的高度一般都在2_{4层。[**mysql**](http://lib.csdn.net/base/mysql)的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作

什么是mysql局部性原理?

局部性原理定义: 指CPU访问存储器时，无论是存取指令还是存取数据，所访问的存储单元都趋于聚集在一个较小的连续区域中

局部性类型:

1. 时间局部性：如果一个信息项正在被访问，那么在近期它很可能还会被再次访问。

2. 空间局部性：在最近的将来将用到的信息很可能与正在使用的信息在空间地址上是临近的。

简单的理解 : 当一个数据被用到时，其附近的数据也通常会马上被使用, 所以一次IO用到的数据要多读一部分到内存

mysql的应用 : 当读取一个字节时, 实际上取了一页(page), 假如下一次要用到紧跟其后的数据无需再访问磁盘, 节约了一次IO时间

操作系统的页为4kb , mysql的页默认为16kb, 此参数可以修改但必须是4的整数倍

查innoDB分页大小 :

SELECT @@innodb_page_size ;
或
show global variables like '%page_size%';

使用覆盖索引的优点?

1. 索引中存储的数据是不完整的,因此每页可以存储更多个数据点, 减少索引的IO次数

2. 在少量数据中检索目标数据, 速度更快

参考:

数据结构可视化 : https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

mysql 索引教程 : https://www.bilibili.com/video/BV1f7411T7CC?t=799