B+树索引的使用——从根上理解Mysql的学习笔记第二部分

最新推荐文章于 2023-04-13 17:27:07 发布

SCU Polars

最新推荐文章于 2023-04-13 17:27:07 发布

阅读量232

点赞数 1

文章标签： mysql b树学习

本文链接：https://blog.csdn.net/LinkBoBo/article/details/126538982

版权

匹配范围值

考虑如下查询语句：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

由于B+树中数据页和记录本身就是先由name排序的，所以上述的查询过程其实是这样的：

B+树先在叶子节点中找到第一条name大于Asa的二级索引记录，读取记录的主键值进行回标，获取对应的聚簇索引记录后发给客户端。
根据上一条找到的主键值，沿着所在链表向后查找下一条二级索引记录，判断是否符合name<‘Barlow’，如果符合，则也回表发送给客户端。
重复上面的步骤，直到某条二级索引记录不符合name<'Barlow’为止。

如果又是对多个列进行范围查找，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

先通过条件name > ‘Asa’ AND name < ‘Barlow’ 来对name进行范围，查找的结果可能有多条name值不同的记录。
对这些记录用birthday > '1980-01-01’条件继续过滤。
这样对于联合索引idx_name_的birthday_phone_number来说，只用到了name的部分，用不到birthday的部分，因为只有name值相同时，birthday才在一个name下是有序的。
所以在搜索条件中继续以birthday列进行查找时是用不到这个B+树索引的
精准匹配某一列并范围匹配另外一列
例如以下的查询语句：

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';

对于**name='Ashburn’来说 name列是精确查找，是B+树索引。
对于birthday > ‘1980-01-01’ AND birthday < ‘2000-12-31’**来说，由于name列是先查找好的，所以进行birthday列的范围查找是可以用到B+树索引的。
对于phone_number列，由于birthday的值可能不同，所以没办法继续用B+树查找了，只能遍历上一次查询得到的记录。

**SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1980-01-01' AND phone_number > '15100000000';**

这个代码明显就可以用到联合索引了。

用于排序

在我们写查询语句的时候经常会需要对查询出来的记录通过order by子句按照某种规则进行排序，一般情况我们把记录都加载在内存中，再用一些排序算法在内存中进行排序，但有些时候内存不够用，那么我们借助磁盘的空间存放中间结果，这种就叫做文件排序（filesort），和“文件”一沾边，速度就慢了，但是考虑索引列的查询语句：

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

由于这个B+树索引本身就是按照上述规则排好序的，因此直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了，索引就是这么牛。

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

注意是由于可以直接使用B+树索引才能这么方便，因此我们ORDER BY后面的语句也必须按照索引列的顺序给出，如果顺序颠倒，用不了B+树索引的话，就不能用了。

SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;

这个查询能使用联合索引进行排序是因为name列的值相同的记录是按照birthday, phone_number排序的，说了好多遍了都。

不可以使用索引进行排序的几种情况

ASC、DESC混用
如果我们要使用联合索引，那么要求各个列的排列顺序应该一致，也就是ASC规则或者都是DESC规则。
如果没有显式声明到底是ASC还是DESC，那么就默认是ASC
考虑idx_name_birthday_phone_number：

先按name升序排序
如果name值相同，就按照birthday列的值进行升序排序
如果birthday相同，再按照phone_number列的值进行升序。
考虑ORDER BY name, birthday LIMIT 10
这种情况直接从索引的最左边往右读10行记录就可以了。
如果是ORDER BY name DESC, birthday DESC LIMIT 10
这种情况直接从索引的最右边往左读10行记录就可以了。

但是如果我们查询需求是先按照name列升序，再按照birthday列降序
比如：

SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;

这样的话，检索过程就变成了下面的：

先从索引的最左边确定name最小的值，在这个值下从右往左寻找10条记录。
如果记录不够10条，那么就去寻找name第二小的值，在这个值下找累计足够10条记录
……
可见这种方式不仅累而且效率非常低，还不如文件排序，所以规定使用联合索引的各个排序列的排序顺序必须一致。

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

有时候用来排序的列并不是一个索引里的，因此也不能用索引进行排序。

SELECT * FROM person_info ORDER BY name, country LIMIT 10;

name和country不在一个联合索引内，因此无法使用索引排序。

排序列使用了复杂的表达式：

如果想使用索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式：

SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

使用UPPER函数修饰过的列就不是单独的列了。
ps：*U PPER *函数的作用是切成大写再输出，但不改变原用户记录。

用于分组：

有时候为了统计表中信息更为方便，会把表中的记录按照一些列进行分组：

SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

先把记录按照name分组
然后把name相同的记录按照birthday进行分组
最后再把birthday相同的记录按照phone_number 分成更小的分组
最后进行统计
如果没有B+树索引，这些过程应该在内存中实现，但这个分组顺序恰好和我们B+树中的索引顺序是一致的，那么就可以直接用B+树进行分组。

回表的代价：

注意这个查询：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

步骤如下：

由idx_name_birthday_phone_number的这个B+树中取出name在Asa~Barlow之间所有的记录。
由于索引只包含name、birthday、phone_number、id 这四个字段，但是查询列表是* 这说明除了这几个字段之外，country字段是没有被得到的，也就是说我们要通过每一个id字段都到对应的B+树中找到完整的用户记录，也就是我们说的回表，然后把完整的用户记录返回给查询用户。
由于索引idx_name_birthday_phone_number对应B+树中的记录会先按照name排序，因此在磁盘中的储存是相连的，集中分布在一个或者几个数据页中，我们可以很快的把这些相连的记录从磁盘中提取出来——这种读取方式我们称其为顺序I/O。
虽然name是相连的，但是id大概率不相连，而聚簇索引中记录是按照id顺序排列的，所以这些并不连续的id值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中——这种读取方式我们称其为随机I/O。
一般情况下，顺序I/O的性能要比随机I/O高很多。
所以步骤1的执行可能很快，步骤2要慢一些。
这个使用索引的查询有如下两个特点：
会使用到两个B+树索引，一个二级索引，一个聚簇索引。
访问二级索引用顺序I/O，访问聚簇索引用随机I/O。

因此我们发现：
需要回表的记录越多，二级索引效率越低。
甚至比如说，当name值在Asa~Barlow之间的用户记录占全部记录数量90%及以上的情况下，如果使用二级索引，有90%多的id值需要回表，这还不如直接扫描聚簇索引(全表扫描）。

那么什么时候使用二级索引，什么时候不使用呢？
这就是 查询优化器 的工作了！

查询优化器会事先根据表中的记录计算一些统计数据，再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，如果很多，那么就倾向于全盘扫描，反过来也是一样的。（当然优化器做的工作远远不止这么简单，但大致如此）
比如如果我们把查询改成这样：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' LIMIT 10;

这个优化器就会更加倾向于采用二级索引+回表。因为有LIMIT 10

SELECT * FROM person_info ORDER BY name, birthday, phone_number;

对于有排序需求的查询，这个情况也是成立的。

SELECT * FROM person_info ORDER BY name, birthday, phone_number;

由于上面查询语句的查询列表是*，所以如果用二级索引排序，需要把排序完的二级索引全部进行回表操作，这样操作的成本还不如直接遍历聚簇索引然后进行filesort低，所以优化器会倾向于使用全盘扫描来进行查询。
但如果我们还是对他加入LIMIT语句：

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

这样需要回表的记录就很少，会倾向于采用二级索引+回表的方式执行查询。

覆盖索引

由于回表会带来性能损耗，所以我们自然而然的建议：
最好在查询列表里只包含索引列。
比如一下代码：

SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow'

由于这个需要查询的列已经完全被包含，因此就不需要回表了。
我们把这种只需要用到索引的查询方式称为索引覆盖
考虑：

SELECT name, birthday, phone_number  FROM person_info ORDER BY name, birthday, phone_number;

虽然没有LIMIT子句，但是采用的覆盖索引，因此查询优化器会直接使用二级索引而不进行回表操作（他还是蛮聪明）

如何挑选索引

也就是说，只为出现在WHERE子句中的列、连接子句中的连接列，或者出现在ORDER BY或GROUP BY子句中的列创建索引。而出现在查询列表中的列就没必要建立索引了：

SELECT birthday, country FROM person_name WHERE name = 'Ashburn';

像查询列表中的birthday、country这两个列就不需要建立索引，我们只需要为出现在WHERE子句中的name列创建索引就可以了。

考虑列的基数

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值

2，5，8，2，5，8，2，5，8

虽然其中有9条记录，但是列的基数是3，也就是说，基数越大，值越分散，越小，值越集中。
极限情况是如果列的基数是1，那么建立索引没有什么意义，
所以最好为那些列的基数大的列建立索引，为基数太小的列建立索引效果可能不太好。

索引列的类型尽量小

以整形类型为例，有

TINYINT、MEDIUMINT、INT、BIGINT

他们占用的储存空间依次递增，这里我们说的类型大小就是该类型表示的数据范围的大小，能表示的证书范围当然也是依次递增，如果我们想要对某一个整数列建立索引的话，尽量让索引列使用较小的类型，比如能用INT就不要用BIGINT，能用MEDIUMINT就不要用INT。
这是因为：

数据类型越小，在查询时进行的比较操作就越快
数据类型越小，索引使用的储存空间就越小，在一个数据页内就能放下更多的记录，加快读写效率。

这个建议对于主键更加适用，因为不仅仅是聚簇索引会保存主键，其他所有的二级索引的节点都会存储一份记录的主键值，因此可以节省更多的储存空间和更高效的I/O。

索引字符串值的前缀

本质上，字符串其实是由若干字符组成的。如果我们在MySQL中使用utf8字符集去存储字符串，一个字符需要1-3个字节，假设字符串很长，那么存储一个字符串需要占用很大的存储空间。面临下列两个问题：

B+树索引中的记录需要把该列完整字符串存储起来，而且字符串越长，在索引中占用的存储空间越大。
如果B+树索引中索引列的字符串很长，那么在做字符串比较的时候会占用更多的时间。

索引列的字符串前缀其实也是排好序的，所以索引的设计者提出了个方案 — 只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再对比就好了。这样只在B+树中存储字符串的前几个字符的编码，既节约空间，又减少了字符串的比较时间，还大概能解决排序的问题，何乐而不为，比方说我们在建表语句中只对name列的前10个字符进行索引可以这么写：

CREATE TABLE person_info(
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

name(10) 就表示在建立的B+树索引中只保留记录的前10个字符的编码。

索引列前缀对排序的影响

如果使用了上一个板块的索引列前缀，考虑如下查询语句：

SELECT * FROM person_info ORDER BY name LIMIT 10;

因为二级索引中不包含完整的name列信息，所以无法对前10个字符相同，后面的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持索引排序，只能用文件排序了。

让索引列在比较表达式中单独出现

考虑以下两个WHERE子句：

WHERE my_col * 2 < 4

WHERE my_col < 4/2

第一个 my_col列并不是以单独列形式出现的，而是my_col*2这样的表达式，存储引擎会依次遍历所有的记录，计算表达式是不是小于4，这种情况用不到B+树索引。
第二个my_col是以单独列形式出现的，因此可以直接使用B+树索引。
如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

主键插入顺序

在这里插入图片描述数据页存储的主键，如果此时我们要插入一条主键值为9的记录，插入的位置应该如下图所示：

在这里插入图片描述但是这个数据页已经满了！解决办法是要把当前页面分裂为两个页面，把本页中一些记录移动到新创建的页面中，这意味着性能的损耗。
我们当然想要避免这种性能损耗，解决办法如下：
让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入。

CREATE TABLE person_info(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

SCU Polars

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
B+树索引的使用——从根上理解Mysql的学习笔记第二部分

这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再对比就好了。他们占用的储存空间依次递增，这里我们说的类型大小就是该类型表示的数据范围的大小，能表示的证书范围当然也是依次递增，如果我们想要对某一个整数列建立索引的话，尽量让索引列。因为二级索引中不包含完整的name列信息，所以无法对前10个字符相同，后面的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持索引排序，只能用文件排序了。但是这个数据页已经满了！...
复制链接

扫一扫