B+树索引的使用

最新推荐文章于 2023-05-21 16:00:00 发布

qq_27502511

最新推荐文章于 2023-05-21 16:00:00 发布

阅读量422

点赞数

分类专栏： Mysql 文章标签： b树数据结构 Powered by 金山文档

本文链接：https://blog.csdn.net/qq_27502511/article/details/129754331

版权

Mysql 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

文章详细阐述了数据库中B+树索引的工作原理，包括如何通过索引进行扫描区间定位，回表操作，以及查询条件如何影响索引的使用效率。同时，讨论了如何优化查询语句，如避免全表扫描，利用覆盖索引和索引条件下推等策略提高查询性能。

摘要由CSDN通过智能技术生成

CREATE TABLE `single_table`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `key2` int(255) NULL DEFAULT NULL,
  `key3` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `key_part1` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `key_part2` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `key_part3` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `common_field` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_key2`(`key2`) USING BTREE,
  INDEX `idx_key1`(`key1`) USING BTREE,
  INDEX `idx_key3`(`key3`) USING BTREE,
  INDEX `id_key_part`(`key1`, `key2`, `key3`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

扫描区间和边界条件

SELECT * FORM single_table WHERE id >= 2 AND id <= 100;

在聚簇索引对应的B+树快速地定位到id值为2，然后沿着记录所在单向链表向后扫描，直到某条聚簇索引记录的id值不在[2,100]区间中为止。

SELECT * FROM single_table WHERE key2 IN(1438, 6328) OR (key2 >= 38 AND key2 <= 79);

使用uk_key2索引执行这个查询，则相当于从下面3个扫描区间中获取二级索引记录。

[1438,1438]：对应的边界条件集就是key2 IN (1438)。

[6328,6328]：对应的边界条件集就是key2 IN (6328)。

[38,79]：对应的边界条件就是key2>=38 AND key2<=79。

查询列表为*，需要根据该二级索引记录的id列的值执行回表操作，找到对应的聚簇索引记录。

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

如果使用idx_key1执行，形成的扫描为(-∞,'a')，而key3 > 'z' AND common_field = 'abc'变成了普通条件，这些普通的索引需要在获取到idx_key1的二级索引记录后，在执行回表操作。

如果使用idx_key3执行，形成的扫描为('z',+∞)，而key<'a' AND common_field='abc'就是普通条件，在获取到idx_key3的二级索引记录后，在执行回表操作，在获取到完整的用户记录后才能去判断他们是否成立。

对于B+树索引，只要索引列和常熟使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（<>）或者LIKE 操作符连接起来，就可以产生所谓的扫描区间。

IN 操作符的语义与若干个等值匹配操作符（=）之间用OR连接起来的语义是一样的，都会产生多个单点扫描区间。

SELECT * FROM single_table WHERE key2 in (1438, 6328);
SELECT * FROM single_table WHERE key2 = 1438 OR key2 = 6328;

!= 产生的扫描区间比较有趣，也容易被大家忽略

SELECT * FROM single_table WHERE key1 != 'a';

此时使用idx_key1执行查询时对应的扫描区间就是(-∞,'a')和('a',+∞)。

LIKE操作符比较特殊，只有在匹配完整的字符串或者匹配字符串前缀是产生合适的扫描区间。

先比较字符串的第一个字符；第一字符小的那个字符串就比较小。

如果两个字符串的第一个字符相同，再比较第二个字符；第二个字符比较小的那个字符串就比较小；

如果两个字符串的前两个字符都相同，那就接着比较第三个字符；以此类推。

cond1 AND cond2 ：只有当cond1和cond2都为TRUE时，整个表达式采薇TRUE

cond1 OR cond2：只要cond1或者cond2中有一个为TRUE，整个表达式为TRUE

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

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

两个小的搜索条件使用AND操作符连接，最终两个小的搜索条件形成的扫描区间取交集后的结果。

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

OR异味着需要取各个扫描区间的并集。

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

SELECT * FROM single_tbale WHERE key2 > 100 AND common_field = 'abc';

使用uk_key2，搜索条件形成了（100，+∞），但是由于uk_key2的二级索引记录并不按照common_field列进行排序（uk_key2二级索引压根儿就不包括common_field列）。

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

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'));

假设使用idx_key1执行查询

将不能形成合适扫描区间的搜索条件移除掉，

(key1 > 'xyz' AND TRUE) OR (key1 < 'abc' AND key1 > 'lmn') OR (TRUE AND key1 > 'zzz' AND (TRUE OR TRUE)

优化

(key1 > 'xyz') OR (key1 < 'abc' AND key1 > 'lmn') OR (key1 > 'zzz')

再次优化

(key1 > 'xyz') OR (key1 > 'zzz')

将两个扫描区间连接起来为('xyz',+∞)，也就是满足key1 > 'xyz'条件的所有二级索引记录都取出来，针对获取到的每一条二级记录，都要用它的主键值再执行回表操作，在得到完整的用户记录之后在使用其他的搜索条件进行过滤。

假设使用uk_key2执行查询

将不合适扫描区间的条件暂时使用TRUE替换掉

(TRUE AND key2 = 748) OR (TRUE AND TRUE) OR (TRUE AND TRUE AND (key2 < 8000 OR TRUE))

优化

key2 = 748 OR TRUE

简化后：TRUE，也就是扫描全表。

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

idx_key_part联合索引为例，它采用排序规则：

先按照key_part1列的值进行排序

在key_part1列的值相同的情况下，在按照key_part2列的值进行排序；

在key_part1和key_part2列的值都相同的情况下，在按照key_part3列的值进行排序。

SELECT * FROM single_table WHERE key_part1 = 'a';

SELECT * FROM single_table WHERE key_part1 = 'a';

定位到key_part1='a'，然后沿着记录所在的单向表向后扫描。

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

由于二级索引记录是按照key_part1列的值排序，在按照列的值相等的情况下再按照key_part2列进行排序。

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

在key_part1列相等的情况下在按照key_part2列进行排序；在key_part1和key_part2列的值都相同的情况下，在按照key_part3列进行排序。

SELECT * FROM single_table WHERE key_part1 < 'a';

定位到符合key_part1 < 'a' 条件的第一条记录，然后沿着九路所在的单项链表向后扫描，直到某条记录不符合key_part1 < 'a'条件为止。

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

先按照key_part1列的值进行排序，在key_part1列的值相等的情况下再按照key_part2列进行排序，定位到key_part1='a' AND key_part2 > 'a' AND key_part2 < 'd' 条件的第一条记录，扫描直到某条记录不符合key_part1='a'条件或者key_part2>'a'或者key_part2<'d'条件为止。

SELECT * FROM single_table WHERE key_part2 = 'a';

二级索引记录不是直接按照key_part2列的值排序的，符合key_part2='a'的二级索引记录可能并不相邻，我们不能通过这个key_part2='a'搜索条件来减少需要扫描的记录数量。

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

二级索引先按照key_part1列的值排序，key_part1='a'条件的二级索引记录来说，并不是直接按照key_part3列进行排序的。在使用idx_key_part索引查询时，虽然搜索条件key_part3='c'不能形成扫描区的边界条件，但是idx_key_part的二级索引记录是包含key_part3列，可以先对查出来的数据判断key_part3='c'条件，如果符合改条件，在执行回表操作，如果不符合就不执行回表操作，直接跳到下一条二级索引记录，这种优化方式称为索引条件下推。

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

二级索引记录是按照key_part1列的值排序，使用idx_key_part查询，定位到符合part1<'b'条件的第一条记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合key_part1 < 'b'条件为止。

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

使用idx_key_part索引时，当扫描到key_part1列值为'b'时，也可以通过key_part2='a'条件减少需要扫描的二级索引记录范围。

索引用于排序

使用ORDER BY时，一般情况下，我们只能把记录加载到内存，然后在用一些排序算法在内存中对这些记录进行排序，如果查询结果集太大，以至于无法在内存中进行排序，此时就需要暂时借助磁盘的空间来存放中间结果，在排序操作完成后再把排好序的结果集返回客户端。

这样在内存或者磁盘中进行排序的方式统称为文件排序。但是，如果ORDER BY 子句使用了索引列，就可以省去内存或磁盘中排序的步骤。

SELECT * FROM single_table ORDER BY key_part1, key_part2, key_part3 LIMIT 10;

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

ORDER BY 子句后面的列的顺序也必须按照索引列的顺序给出

当然ORDER BY key_part1 和 ORDER BY key_part1, key_part2，这些仅对联合索引的索引列中左边连续的列进行排序的形式。

不可以使用索引进行排序的集中情况

ASC、DESC混用

先按照key_part1值升序排序

如果记录的key_part1值相同，再按照key_part2值升序排序；

如果记录的key_part1和key_part2值都相同，在按照key_part3值升序排序。

如果查询语句中各个排序列的排序规则是一致的，比如下面这两种情况。

ORDER BY key_part1,key_part2 LIMIT 10

我们可以直接从联合索引最左边的那条二级索引记录开始，向右读10条索引记录就可以了。

ORDER BY key_part1 DESC,key_part2 DESC LIMIT 10

我们可以直接从联合索引最右边的那条二级索引记录开始，向左读条二级索引记录就可以了。

如果查询的需求是先按照key_part1列升序排序，再按照key_part2列降序排序，比如下面这个查询语句：

SELECT * FROM single_table ORDER BY key_part1, key_part2 DESC LIMIT 10;

此时，如果使用联合索引执行具有排序需求的上述查询，过程就是下面这样。

先找到联合索引最左边的那条二级索引记录的key_part1值（将其称为min_value）,然后向右找到key_part1值等于min_value的所有二级索引记录，然后再从key_part1值等于min_value的最后一条二级索引记录开始，向左找10条二级索引记录。

如果key_part1值等于min_value的二级索引记录共有n条（且<10）,那就得找到key_part1值为min_value的最后一条二级索引记录的下一条二级索引记录。假设该二级索引记录的key_part1值为min_value2,那就得在找到key_part1值为min_value2的所有二级索引记录，然后再从key_part1值等于min_value2的最后一条二级索引记录开始，向左找10-n条记录。

如果key_part1值为min_value1和min_value2的二级索引记录还不够10条。。。

这种需要较为复杂的算法从索引中读取记录的方式，不能高效地使用索引，所以这种情境下是不会使用联合索引执行排序操作的。在MySQL8.0引入了一种称为Descending Index的特性。

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

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

SELECT * FROM single_table ORDER BY key1,key2 LIMIT 10;

对于idx_key1的二级索引记录来说，只按照key1列的值进行排序。而且在key1值相同的情况下是不按照key2列的值进行排序打的，所以不能使用idx_key1索引执行上述查询。

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

不能使用idx_key_part查询。

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

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

使用idx_key1执行查询时间，无法使用uk_key2执行查询。

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

想要使用索引进行排序操作，必须保证索引列是以单独列名的形式（而不是修饰过的形式）出现。

SELECT * FROM single_table ORDER BY UPPER(key1) LIMIT 10;

因此key1列是以UPPER(key1)函数调用的形式出现在ORDER BY 子句中的（UPPER函数用于将字符串转化为大写形式），所以不能使用idx_key1执行。

索引用于分组

不需要建立临时表，直接使用idx_key_part索引进行分组。

回表代价

SELECT * FROM single_table WHERE key1 > 'a' AND key < 'c';

以全表扫描

使用idx_key1进行查询

使用何种方式进行查询，需要看查询结果的数量来看，如果查询出来的数量占总量的99%，肯定就以全表扫描来处理。假设使用idx_key1来查询，因为查询的是所有列，所以必须要回表处理，当查询的数据列比较分散时，就需要多次加载不同索引页来获取具体数据，这样就会这几到多次回表处理。如果是这样的情况，还不如进行全表扫描，减少IO。

具体使用何种方式来插叙，由查询优化器事先针对表中的数据记录进行一些统计，然后利用这些统计数据或者访问表汇总的少量记录来计算需要执行回表操作的记录数。

使用LIMIT子句来限制哈讯返回的记录数，可能会让优化器倾向使用二级索引+回表方式来处理。