【MySQL】深入学习B+索引的使用

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

文章目录

本文参考《MySQL是怎样运行的从根儿上理解 MySQL》——作者小孩子4919

1. 前言

学习数据库的同学应该都知道索引是可以提高MySQL的查询效率的但是吧这个索引可不能乱加如果加了一些没用的索引不仅会浪费了空间并且还会浪费了时间。因为索引本身就是一个B+树MySQL在进行增删改查的时候都需要维护所有的B+索引树因此可能会带有空间和时间上的损耗。

2. 索引的代价

前言这里提到过一下索引的代价无非就是下面两种

  1. 空间上的代价
  2. 时间上的代价

2.1 空间上的代价

每一个索引其实本质就是一个B+索引树。

而每一个B+树的每一个节点都是一个数据页。每一个数据页都会默认占用16KB的存储空间。

假如该B+索引树非常大的话那这颗树就由很多数据页组成因此会占用很大的内存空间。

2.2 时间上的代价

作为一名CRUD工程师我们的工作无非就是对数据库的记录进行CRUD。

每当我们对表中的记录进行CRUD的时候都需要修改每一个B+树索引。

而CRUD的操作就可能会对节点和记录的排序造成破坏。

为什么说是可能呢

就聚簇索引来说它是使用主键值的大小进行记录和页排序的。

比如现在有主键值为【156】的三条记录。

如果主键值不是递增的话比如插入一条主键值为2的记录那么在聚簇索引中就需要插入到1的后面就会对节点和记录的排序造成破坏。

但是如果主键值是递增的话插入的主键值就应该为7插入到6的后面就不会对节点和记录的排序造成破坏

并且需要注意的是在执行查询语句前会先生成一个执行计划一条查询语句在执行过程中最多使用一个二级索引当然也有例外。

在生成执行计划的时候需要分析使用不同索引执行查询时所需的成本最后选取成本最低的那个索引执行。

但是如果索引太多就可能导致成本分析过程耗时过长从而影响查询语句执行效率。

3. B+树索引的使用

这里先准备一个表

CREATE TABLE single_table(
	id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY(id),
    KEY idx_key1(key1),
    UNIQUE KEY uk_key2(key2),
    KEY idx_key3(key3)
    KEY idx_key_part(key_part1, key_part2, key_part3)
)Engine=InnoDB CHARSET=utf8

接着需要认清两个定义扫描区间和边界条件。

接下面这条SQL语句来说

SELECT * FROM single_table WHERE id >= 2 AND id <= 100

这个语句是想查询id值在[2100]区间中所有聚簇索引记录。可以通过聚簇索引对应的B+树快速定位id为2的那条记录。然后沿着记录所在的单向链往后扫描直到id不在[2100]区间中为止。

其中我们把待扫描记录的区间称为扫描区间把形成这个扫描区间的搜索条件称为这个扫描区间的边界。

并且把只包含一个值的扫描区间称为单点扫描区间把巴汗多个值的扫描区间称为范围扫描区间。

但是并不是所有搜索条件都可以成为边界条件。

SELECT * FROM single_table WHERE key1 < 'a' AND key3 > 'z' and common_field = 'abc'

在这个SQL语句中如果使用idx_key1索引执行查询那么扫描区间就是[-∞‘a’]形成该扫描区间的边界条件就是key1<'a'而key3>'z' and common_field = 'abc' 就是普通的搜索条件这些普通搜索条件需要获取到idx_key1的二级索引记录后再执行回表操作在获取到完整的用户记录后才能判断是否成立。

3.1 提取正确的扫描区间

3.1.1 所有搜索条件都可以生成合适的扫描区间的情况

在使用某个索引执行查询的时候有时每一个小的搜索条件都可以生成一个合适的扫描区间来减少需要扫描的记录数量。

SELECT * FROM  single_table WHERE key2 > 100 AND key2 > 200

如果使用idx_key2执行查询两个小的搜索条件是使用AND操作符连接的所以最终的扫描区间就是对这两个搜索条件的扫描区间取交集后的结果。

image-20230116135007230

因此对应的扫描区间为(200+∞)边界条件就是key2>200

但是如果AND操作符改为OR操作符。

SELECT * FROM  single_table WHERE key2 > 100 OR key2 > 200

OR也就是意味着取各个扫描区间的交集。

image-20230116135323602

对应的扫描区间就是(100+∞)形成的扫描区间的条件就是key2 > 100

3.1.2 有的搜索搜索条件不能生成合适的扫描区间

在使用某个索引执行询时 某个小的搜索条不能生成合适的扫描区间来减少需要扫描记录数量。

SELECT * FROM  single_table WHERE key2 > 100 AND common_field = ‘abc’

在使用uk_key2执行查询的时候很显然搜索条件key>100可以形成扫描区间100 +∞但是uk_key2的二级索引不按照common_field进行排序。所以单凭搜索条件common_field = ‘abc’并不能减少需要扫描的二级索引记录数量。也就是单凭单凭搜索条件common_field = ‘abc’的扫描区间为-∞+∞。

于是使用uk_key2执行查询是扫描区间为100+ ∞形成该扫描区间的条件为key2>100。

将AND操作符改为OR操作符。

SELECT * FROM  single_table WHERE key2 > 100 OR common_field = ‘abc’

根据上面说的common_field这个字段用不上因此将该索引条件改为TRUE

SELECT * FROM  single_table WHERE key2 > 100 OR TRUE

接着化简

SELECT * FROM  single_table WHERE TRUE

可见对应的扫描区间就是-∞+∞)也就是需要扫描uk_key2的所有二级索引记录并且对于获取到的每一条二级索引记录都需要进行回表操作这个代价肯定比执行全表扫描代价大。在这种情况下我们是不考虑用uk_key2执行查询的。

3.1.3 从复杂的搜索条件中找出扫描区间

在使用SQL中肯定会用到一些复杂的SQL那么想要定位出对应的扫描区间那该怎么办呢

SELECT * FROM  single_table WHERE
(key1 > 'xyz' AND key2 = 748) OR
(key1 < 'abc' AND key1 > 'lmn') OR
(key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc'));

像这种复杂SQL我们需要按照以下套路分析一下

  1. 首先查看 WHERE子句中的搜索条件都涉及了哪些列以及我们为哪些列建立了索引这个查询语句的搜索条件涉及了 key1、key2、common_field这3个列其中 key1 列有普通的二级索引idx_key1key2列有唯一二级索引uk_key2。
  2. 对于那些可能用到的索引分析它们的扫描区间。

假使用idx_key1执行查询

首先先把不能不能形成合适的扫描区间的搜索条件展示移除也就是将它们替换成TRUE

化简后

SELECT * FROM  single_table WHERE
(key1 > 'xyz') OR
(key1 < 'abc' AND key1 > 'lmn') OR
(key1 > 'zzz' );

咦为什么key1 LIKE '%suf'也不能形成合适的搜索条件呢

这是因为LIKE操作符号比较特殊只有匹配完整的字符串或者匹配字符串前缀时才产生合适的扫描区间。

由于key1 < 'abc' AND key1 > 'lmn'永远为FALSE

继续化简可得

SELECT * FROM  single_table WHERE
(key1 > 'xyz') OR
(key1 > 'zzz' );

由于两个搜索条件用OR连接意味着取交集。

最后化简可得

SELECT * FROM  single_table WHERE (key1 > 'xyz');

因此扫描区间为‘xyz’ +∞边界条件为key1 > ‘xyz’。

假如使用uk_key2执行查询呢

化简可得

SELECT * FROM  single_table WHERE ( key2 = 748) OR TRUE

再化简

SELECT * FROM  single_table WHERE TRUE

因此扫描区间为-∞+∞

也就是需要扫描uk_key2的全部二级索引记录针对获取到的每一条二级索引记录还要进行回表操作。这不是得不偿失么!所以在这种博况下是不会使用 uk_key2 索引的。

3.1.4 使用联合索引执行查询时对应的扫描区间

以single_table的idx_key_part联合索引为例它的B+索引树如下图所示

image-20230116143335335

比如执行下面的语句Q1

SELECT * FROM single_table WHERE key_part1 = 'a'

二级索引记录是按照key_part1列排列的所以符合key_part1=‘a’条件的所有记录肯定相连的。

因此可以先定位符合key_part1=‘a’的第一条记录沿着这条记录往后扫描直到某条记录不符合key_part1=‘a’为止。

image-20230116172754779

也就是扫描区间是[‘a’‘a’]形成这个扫描区间的边界条件就是key_part1=‘a’

对于下面的SQL语句Q2

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b'

二级索引记录是按照key_part1列排列的在key_part1相同的情况下再按照key_part2进行排序所以符合key_part1 = 'a' AND key_part2 = 'b'这个条件的结果再二级索引记录中肯定是相连的。

在Q2中扫描区间为[(‘a’‘b’)(‘a’‘b’)]边界条件就是key_part1 = 'a' AND key_part2 = 'b'

同理对于下面SQL语句Q3来说规律是一样的就不再多说了

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'

对于Q4查询语句来说

SELECT * FROM single_table WHERE key_part1 < 'a'

这个和Q1很像二级索引记录是按照key_part1列排列的所以符合key_part1<‘a’条件的所有记录肯定相连的。

image-20230116174124136

对于Q5查询语句来说

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 > 'a' AND key_part3 < 'd'

由于二级索引记录是先按照 key_part1列的值进行排序的在 key_part1列的值相等的情况下再按照key_part2列进行排序。那么 key_part1 = 'a' AND key_part2 > 'a' AND key_part3 < 'd'条件下的二级索引记录肯定是相邻的。

image-20230116174057321

对于查询语句Q6来说

SELECT * FROM single_table WHERE key_part2 = 'a'

由于二级索引记录不是直接按照key_part2列的值排序的所以符合key_part2='a'的二级索引记录可能并不相邻也就意味着我们不能通过这个key_part2='a'搜索条件来减少需要扫描的记录数量。在这种情况下我们是不会使用idx_key_part索引执行查询的。

对于查询语句Q7来说

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part3 = 'c'

由于二级索引记录是按照key_part1列的值排序的但是在key_part1相同的情况下并不是直接按照key_part3列进行排序的也就是不能根据key_part3进一步减少需要扫描的记录数量。

所以对应的扫描空间是[‘a’‘a’]边界条件是key_part1 = 'a'与key_part3 = 'c'无关。

对于查询语句Q8来说

SELECT * FROM single_table WHERE key_part1 < 'b' AND key_part2 = 'a'

由于二级索引记录是按照key_part1列的值排序的所以key_part1<'b'条件的二级索引记录肯定是相邻的。但是对于key_part1<'b'二级索引记录并不是按照key_part2进行排序的所以不能根据key_part2减少需要扫描的记录数量

对于查询语句Q9来说

SELECT * FROM single_table WHERE key_part1 <= 'b' AND key_part2 = 'a'

很Q8很像对于key_part1 < 'b的二级索引记录并不是按照key_part2进行排序的。但是对于key_part1 = 'b'的索引记录来说是按照key_part2进行排序的key_part2 = ‘a’条件可以减少二级索引记录范围

image-20230116175447504

3.2 索引用于排序

一般情况下需要将记录加载到内存中然后再使用一些排序算法在内存中对这些记录进行排序。

但是有时候可能太大以至于无法在内存中进行排序此时就需要暂时借助磁盘空间来存放中间结果在排序操作完成后再把排序结果集返回客户端。

3.2.1 使用联合索引进行排序的注意事项

  1. ORDER BY子句后面的列的顺序也必须按照索引列的顺序给出。
    1. 因为如果颠倒顺序就不能使用索引原因和联合索引中页面和记录的排序规则是固定的。
    2. 仅对联合索引的索引列中左边连续的列进行排列也是可以利用B+索引的。
  2. 当联合索引的索引列左边连续的列为常量时也可以使用联合索引对右边的列进行排序

3.2.2 不能使用索引进行排序的几种情况

  1. ASC、DESC混用

    1. 使用索引进行排序的场景要求各个排序列的规则是一直的要么都是升序要么都是降序
    2. 因为混用的华需要较为复杂的算法从索引中读取记录不能高效使用索引。
    3. 注意这里说的是不能高效使用也就是用索引也能实现只是效率慢而已。

  2. 排序列包含非同一个索引的列

    1. 有时用来排序的多个列不是同一个索引中的这种情况也不能使用索引进行排序。

  3. 排序列是某个联合索引的索引列但是这些排序列在联合索引中并不连续

  4. 用来形成扫描区间的索引列与排序列不同

    1. 也就是这种情况

      SELECT * FROM single_table WHERE key1 = 'a' ORDER BY key2 LIMIT 10

    2. 用key1 = 'a'形成扫描区间也就是用idx_key1执行该查询时进扫描key1值为‘a’的二级索引记录即可。此时无法使用uk_key2

  5. 排序列不是以单独列名的形式出现在 ORDER BY 子句中

    1. 必须保证索引列是以单独列名的形式出现

    2. 像这种就不行了

      SELECT * FROM single_table ORDER BY UOOER(key1) LIMIT 10

3.3 索引用于分组

像这样这样的查询语句

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key1 = 'a' GROUP BY key_part1, key_part2, key_part3

如果没有 idx_key_part 索引就得建立一个用于统计的临时表在扫描聚簇索引的记录时将统计的中间结果填入这个临时表。当扫描完记录后再把临时表中的结果作为结果集发送给客户端。如果有了索引idx_key_part恰巧这个分组顺序又与idx_key_part 的索引列的顺序是一致的而 idx_key_part 的二级索引记录又是按照索引列的值排好序的这就正好了。所以可以直接使用idx_key_part 索引进行分组而不用再建立临时表了。

3.4 回表的代价

  1. 造成大量随机的I/O
    1. idx_key1在扫描某一个区间中二级索引所在的页面号回尽可能相邻尽管不相邻起码一个页面可以存放多条记录也就是执行完一次I/O后就可以把很多二级索引记录从磁盘加载到内存。
    2. 但是在扫描该区间中的二级索引记录对应的id值是毫无规律的。
    3. 每一次读取二级索引记录都需要根据二级索引记录对应id进行回表。
    4. 如果对应的聚簇索引记录所在页面不在内存中就需要将该页面从硬盘加载到内存。
    5. 由于要读取很多id值并不连续的聚簇索引记录而且这些聚簇索引分布在不同数据页因此数据页页号也毫无规律

那么什么时候采用全表扫描什么时候采用二级索引+回表呢

这是查询优化器做的事情查询优化器会事先针对表中的记录计算一些统计数据然后再利用这些统计数据或者访问表中的少量记录来计算需要执行回表操作的记录数。

如果需要执行回表操作的记录数越多就越倾向于使用全表扫描否则使用二级索引+回表。

但是如果给查询条件指定LIMIT子句来限制查询返回的记录数这可能会让查询优化器更倾向于使用二级索引+回表方式进行查询。

原因是记录越少性能越高。

同时对于结果需要进行排序的查询如果再采用二级索引执行查询需要执行回表操作的记录特别多也倾向于使用全表扫描+文件排序的方式执行查询。

3.5 更好地创建和使用索引

  1. 只为用于搜索、排序或分组的列创建索引
    1. 对于出现在查询列表的列就没有必要创建索引了
  2. 考虑索引列中不重复值个数
    1. 我们在为某个列创建索引时需要考虑该列中不重复值的个数占全部记录条数的比例。
    2. 如果比例太低则说明该列包含过多重复值那么在通过二级索引+回表的方式执行查询时就有可能执行太多次回表操作
  3. 索引列的类型尽量小
    1. 如果想要对某个整数类型的列建立索引在表示的整数范围允许的情况下尽量让索引列使用较小的类型
    2. 这样索引占用的内存就越少一个页就能放下更多的记录磁盘I/O带来的性能损耗也越少

3.6 为列前缀建立索引

为字符串所在的列建立索引时也就意味着对应B+树中需要将该列的完整字符串存储起来。

字符串越长占用的存储空间就越大。

我们可以设置一个巧妙的方法就是将字符串的前几个字符存放在所以索引中。

当列中存储的字符串包含的字符较多时候这种方法明显减少索引的大小。

不过这样做的话就不能使用该索引完成排序需求了

至于为什么相信不说大家也知道。

3.7 覆盖索引

把这种索引中已经包含所有需要读取的列的查询方式称为覆盖索引。排序操作也优先使用覆盖索引进行查询比如下面这个查询语句:

SELECT key1 FROM single_table  ORDER BY key1

虽然这个查询语句中没有 LIMIT 子句但是由于可以采用覆盖索引所以查询优化器会直接使用id_key1索引进行排序而不需要执行回表操作。

3.8 新插入记录时主键大小对效率的影响

如果新插入记录的主键值是依次增大的话则每插满一个数据页就换到下一个数据页继续插入。

但是如果主键值随机这就难搞了

假设某个数据页存储的聚簇索引记录已经满了它存储的主键值在1~100之间

image-20230116190331622

再插入一条主键值为9的记录

这就需要把当前页面分裂成两个页面把本页中的一些记录移动到新创建的页中。这意味着性能损耗所以如果想尽量避免这种无谓的性能损耗最好让插入记录的主键值依次递增。就像 singletable表的主键id列具有AUTO_INCREMENT 属性那样MySQL会自动为新插入的记录生成递增的主键值。

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
标签: mysql
返回列表

上一篇：右键万能格式转换工具

下一篇：使用nginx搭建HTTP FLV流媒体服务器

“【MySQL】深入学习B+索引的使用” 的相关文章

Day876.redolog刷盘问题 -MySQL实战1年前 (2023-02-02)
MySQL安装配置教程（超级详细、保姆级）_mysql安装配置1年前 (2023-02-02)
Linux-安装MySQL（详细教程）_linux安装mysql1年前 (2023-02-02)
MySQL高级【锁】1年前 (2023-02-02)
mysql 字符串转日期1年前 (2023-02-02)
基于jsp+mysql+Spring的SpringBoot招聘网站项目（完整源码+sql）1年前 (2023-02-02)
mysql case when then用法1年前 (2023-02-02)
MySQL整体使用》导入数据、约束、多表查询、事务、变量类型、资源占用1年前 (2023-02-02)
MySQL数据库基本操作-DML1年前 (2023-02-02)
Using a password on the command line interface can be insecure，mysql报错解决办法1年前 (2023-02-02)

阿里云国际版