B+树索引的使用

CodeGuN
299 阅读5分钟

B+树索引的使用

索引的代价

索引虽好，但是不能够乱建。

  • 空间上的代价
    每个建立一个索引都需要建立一颗B+树，每棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，而一颗索引树由许多页组成…
  • 时间上的代价
    索引虽然让我们查询变得更为快速，我们都知道B+树每层节点都是按照索引列的值从小到大的顺序排序而组成双向链表。无论是叶子节点的记录，还是内节点的记录都是按照索引列的值从小到大的顺序而形成的一个单向链表。我们每次对表中的数据进行CUD操作时，可能会对节点和记录排序造成破坏，所以存储引擎需要额外的时间进行移位、页分裂、页面回收等操作来维护节点和记录的顺序。对性能影响较大。

B+树索引适用的条件

以下面的表为例

CREATE TABLE person_info(
    id INT 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, birthday, phone_number)
);

二级索引如图(记录结构中保留了name,birthday,phone_number,id)：
在这里插入图片描述

全值匹配

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

查询流程如下:

  • 在二级索引中，先按照name列进行排序索引，很快能够找到name列的值是Ashburn的位置。
  • 在name列相同的记录里，在按照birthday列进行排序索引，很快就能够找到name列为Ashburn，birthday列是1990-09-27的记录。
  • 在name列和birthday值相同的数据中查找，根据phone_number排序索引，最终找到唯一确定的那条记录。

注意：MySQL中有优化器能够在查询之前将我们的SQL语句优化为可按照索引的查询的形式(当然如果无能够使用的索引则没办法)

最左匹配原则

对于以下三条SQL，为什么第三条索引无法走索引呢？

SELECT * FROM person_info WHERE name = 'Ashburn';

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';

SELECT * FROM person_info WHERE birthday = '1990-09-27';

前面我们阐述了查找过程，因为B+树的数据页和记录先是按照name列的值排序的，在name列的值相同的情况下才使用birthday列进行排序，即name列不相同的记录中birthday的值不确保有序。
如果我们跳过索引的顺序直接查找可能的没办法走索引的。如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列
比如说:

SELECT * FROM person_info WHERE name = 'Ashburn' AND phone_number = '15123983239';

这样的只能用到name列的索引，birthday和phone_number的索引均用不到，理由前面阐述过了。

匹配列前缀

比如我们想查询url以www.开头的记录:

> SELECT * FROM url WHERE name LIKE 'www.%';
+----------------+
| url            |
+----------------+
| www.baidu.com  |
| www.google.com |
| www.gov.cn     |
| ...            |
| www.wto.org    |
+----------------+

假设我们已经给url列创建了索引，如果我们想查询以com结尾的网址的话索引条件:WHERE url LIKE '%com'，这样的话我们是无法使用url列索引的。
为什么呢？
牵扯到MySQL中的字符串排序规则，大概是这样的:

  • 先比较字符串的第一个字符，第一个字符小的那个字符串就比较小。
  • 如果两个字符串的第一个字符相同，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小。
  • 如果两个字符串的第二个字符也相同，那就接着比较第三个字符，依此类推。

所以一个排好序的字符串列其实有这样的特点：

  • 先按照字符串的第一个字符进行排序。
  • 如果第一个字符相同再按照第二个字符进行排序。
  • 如果第二个字符相同再按照第三个字符进行排序，依此类推。

这样就会有一个问题——我们字符串的前缀的有序排列的，是能够索引的，所以LIKE 'www.%'能够走索引，而%.com这样的因为只有前缀有序，尾缀是com字符串并没有排好序，所以MySQL无法快速定位记录的位置，只能够全表查询。

对于这样的数据，我们能够将数据倒置存储，这样就能够索引moc，如:

+----------------+
| url            |
+----------------+
| moc.udiab.www  |
| moc.elgoog.www |
| nc.vog.www     |
| ...            |
| gro.otw.www    |
+----------------+

匹配范围值

回头看第一个例子，B+树中所有的记录都是按照索引列的值从小到大排序，所以这也让我们的范围查询更加的方便了，比如

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

由于B+树中的数据页和记录是先按name列排序的，所以我们上边的查询过程其实是这样的：

  • 找到name值为Asa的记录。
  • 找到name值为Barlow的记录。
    由于所有记录都是由链表连起来的（记录之间用单链表，数据页之间用双链表），所以他们之间的记录都可以很容易的取出
    找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。

同样的也需要遵从最左匹配原则，否则会全表查询

ORDER BY

需要对查询出来的记录通过ORDER BY子句按照某种规则进行排序时，一般情况下，我们只能把记录都加载到内存中，再用一些排序算法，比如快速排序、归并排序等等在内存中对这些记录进行排序，有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。在MySQL中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序，跟文件这个词儿一沾边儿，就显得这些排序操作非常慢了。
但是如果ORDER BY子句里使用到了索引列，就有可能省去在内存或文件中排序的步骤，比如下边这个简单的查询语句：
ORDER BY默认升序排序

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

这个查询的结果集需要先按照name值排序，如果记录的name值相同，则需要按照birthday来排序，如果birthday的值相同，则需要按照phone_number排序。数据在查询的过程中已经完成了排序。

对于联合索引有个问题需要注意：ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出ORDER BY phone_number, birthday, name的顺序，那用不了B+树索引。

不可以使用索引的情况:

  • ASC和DESC混用
    对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。
    原因:
    在查询中的排序顺序是一致的情况下，例如:
    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列最小的值，然后找到name列等于该值的所有记录，然后从name列等于该值的最右边的那条记录开始往左找10条记录。
  • 如果name列等于最小的值的记录不足10条，再继续往右找name值第二小的记录，重复上边那个过程，直到找到10条记录为止。
  • 排序列使用了复杂的表达式
    要想使用索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式，比方说这样：
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

使用了UPPER函数修饰过的列就不是单独的列了，这样就无法使用索引进行排序。

GROUP BY

有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组。比如下边这个分组查询：

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

这个查询语句相当于做了3次分组操作：

  • 先把记录按照name值进行分组，所有name值相同的记录划分为一组。
  • 将每个name值相同的分组里的记录再按照birthday的值进行分组，将birthday值相同的记录放到一个小分组里，所以看起来就像在一个大分组里又化分了好多小分组。
  • 再将上一步中产生的小分组按照phone_number的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把大分组分成若干个小分组，然后把若干个小分组再细分成更多的小小分组。

然后针对那些小小分组进行统计，比如在我们这个查询语句中就是统计每个小小分组包含的记录条数。如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又和我们的B+树中的索引列的顺序是一致的，而我们的B+树索引又是按照索引列排好序的，这不正好么，所以可以直接使用B+树索引进行分组。

和使用B+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组

回表的代价

例如这个查询:

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

在使用二级索引进行查询时大致可以分为两个步骤：

  1. 从索引对应的B+树中取出name值在Asa～Barlow之间的用户记录。
  2. 由于索引对应的B+树用户记录中只包含name、birthday、phone_number、id这4个字段，而查询列表是*，意味着要查询表中所有字段，也就是还要包括country字段。这时需要把从上一步中获取到的每一条记录的id字段都到聚簇索引对应的B+树中找到完整的用户记录，也就是我们通常所说的回表，然后把完整的用户记录返回给查询用户。

由于二级索引对应的B+树会按照name列的值进行排序，所以值在Asa~Barlow之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这些记录从磁盘读取出来，这种读取方式称为顺序I/O,而我们根据二级索引获取的主键是不连续的，把这些不连续的主键值到聚簇索引中访问完整的数据记录，但是完整的用户记录很可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式我们也可以称为随机I/O，一般情况下，顺序I/O比随机I/O性能高很多.
所以这个使用索引的查询有这么两个特点：

  1. 会使用到两个B+树索引，一个二级索引，一个聚簇索引。
  2. 访问二级索引使用顺序I/O，访问聚簇索引使用随机I/O。

需要回表的记录越多，使用二级索引的性能就越低 甚至让某些查询宁愿使用全表扫描也不使用二级索引。

那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执行查询呢？这个就是传说中的查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。

覆盖索引

为了解决回表带来的性能损耗，最好在查询列表中只包含索引列
比如:

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

因为我们查询的是索引列，所以索引得到的结果就不必到聚簇索引中再查找记录的剩余列。

avatar
文章
阅读
粉丝