葫芦岛德信成叉车 发表于 2023-10-8 11:51:08

MySQL学习(4)好好使用B+树索引

前言

每个索引都是一颗B+树,对于聚簇索引,每一条完整记录都存储在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),
INDEX idx_key1 (key1),
UNIQUE INDEX uk_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=InnoDB CHARSET=utf8; 
 
 
为什么不给每个列都建立索引


[*]每建一个索引,就会新建一颗B+树,占用很大存储空间。
[*]增删改操作时,维护索引需要耗费资源和时间。
索引不是越多越好,要用到刀刃上,索引要又好又少才好。
 
B+树索引的应用场景

扫描区间和边界条件

如下有一条查询语句:
SELECT * FROM single_table WHERE id >=2 AND id <= 100; 

[*]如果使用idx_key1索引执行查询,根据key1 < 'b'形成(-∞, 'b')的扫描区间,但是对与key3 > 'x' AND common_field = 'abc'来说,无法使用索引B+树中的列,只能通过查询(-∞, 'b')的结果,根据主键回表后的记录进行逐条判断。
[*]如果使用idx_key3索引执行查询,根据key3 > 'x'形成('x', +∞)的扫描区间,但是对于key1和common_field列,则无法使用。
这些搜索条件为在索引中不存在的列称为普通所搜条件,只能在得到的二级索引记录进行回表后得到完整的记录,才能去判断它们是否成立。

对于B+树来说,只要索引列和常数使用=、、IN、NOT IN、IS NULL、IS NOT NULL、>、=、 100 AND common_field = 'abc'; 
根据这个搜索条件很容易想到使用uk_key2索引执行查询,搜索条件key2>100可以形成扫描区间(100, +∞)。但是在uk_key2的二级索引中,记录并不包含common_field列,更不会按照common_field列进行排序,所以common_field=‘abc’这个搜索条件并没能有效的缩小扫描范围,最终的扫描区间是(100, +∞)。
如果上述查询语句中的AND改为OR,如下所示:
SELECT * FROM single_table where id IN (1, 50, 1688) OR (id >=2 AND id <= 100); 
key>100的扫描区间为(100, +∞),common_field=‘abc’的扫描区间为(-∞, +∞),使用OR连接表示取并集,最终的扫描区间是(-∞, +∞),这还不如使用聚簇索引全表扫描,还省得回表操作。
 
联合索引的情况
使用联合索引执行查询时,要遵循最左匹配原则,意思就当使用联合索引进行查询时,最左侧的索引列必须包含在查询条件中,因为在联合索引idx_key_part的B+树中,记录都是按照key_part1排序,然后按照key_part2排序,再按照key_part3排序,最后前三列都相同的情况下,按照id排序。
对于如下查询语句:
SELECT * FROM single_table where key1 IN ('a', 'c') OR (key1 >='h' AND key1 <= 'j'); 
dx_key_part索引中的记录按照key_part1列的值排序的,搜索条件key_part1=‘a’生成[‘a’, ‘a’]的扫描区间,定位到第一条符合条件的记录后,向后扫描直到不符合条件为止。
对于如下查询语句:
SELECT * FROM single_table WHERE key1 < 'b' AND key3 > 'x' AND common_field = 'abc'; 
与上一条语句相同的是搜索条件key_part1=‘a’生成[‘a’, ‘a’]的扫描区间,但是在key_part1=‘a’的第一条记录向后,并不是直接按照key_part3排序的,key_part3=‘b’的记录并不是相邻的,而是先按照key_part2排序,再按照key_part3排序,扫描区间为(-∞, +∞),并不能有效减少扫描记录量。这次查询的扫描区间最终为[‘a’, ‘a’],与key_part3列没啥关系。
对于如下查询语句:
SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc'; 
这条语句的key_part2列就能使用到索引带来的好处的,
如下语句:
SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc'; 

index_key_part索引记录按照key_part1列的值排序,key_part
页: [1]
查看完整版本: MySQL学习(4)好好使用B+树索引