Session 7 B+树索引的使用

一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好又少的索引，我们需要了解这些索引在哪些条件下起作用的。

本章的辅助表：

1
2
3
4
5
6
7
8
9
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)
);

image-20230722105048330

全值匹配

我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配。

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

WHERE子句中的几个搜索条件的顺序对查询结果没有影响（查询优化器的作用）。

匹配左边的列

如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列（索引列的前缀列）。

比如对于联合索引（A, B, C），只有搜索列是A、A,B、A,B,C才会用到索引。

匹配列前缀

所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的，比方说我们想查询名字以'As'开头的记录，那就可以这么写查询语句：

1
2
SELECT * FROM person_info WHERE name LIKE 'As%';
SELECT * FROM person_info WHERE name LIKE '%As%'; /* 这样就不行 */

对如下的URL表

1
2
3
4
5
6
7
8
9
+----------------+
| url            |
+----------------+
| www.baidu.com  |
| www.google.com |
| www.gov.cn     |
| ...            |
| www.wto.org    |
+----------------+

改为

1
2
3
4
5
6
7
8
9
+----------------+
| url            |
+----------------+
| moc.udiab.www  |
| moc.elgoog.www |
| nc.vog.www     |
| ...            |
| gro.otw.www    |
+----------------+

就可以用WHERE url LIKE 'moc%'来查询了。

匹配范围值

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

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

这个查询中通过name进行范围查找的记录中可能并不是按照birthday列进行排序的，所以在搜索条件中继续以birthday列进行查找时是用不到这个B+树索引的。

精确匹配某一列并范围匹配另外一列

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找（也就是使用=），则右边的列可以进行范围查找，比方说这样：

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

用于排序

我们在写查询语句的时候经常需要对查询出来的记录通过ORDER BY子句按照某种规则进行排序。有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。在MySQL中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort），跟文件这个词儿一沾边儿，就显得这些排序操作非常慢了

但是如果ORDER BY子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤。

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

因为这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出列。

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

  • ASC、DESC混用

​ 要求各个排序列的排序顺序是一致的

  • WHERE子句中出现非排序使用到的索引列
1
SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10; /* 因为country不在联合索引中，所以不会用到索引 */
  • 排序列使用了复杂的表达式
1
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

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

用于分组

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

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

回表的代价

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

由于索引idx_name_birthday_phone_number对应的B+树中的记录首先会按照name列的值进行排序，所以值在Asa～Barlow之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这些连着的记录从磁盘中读出来，这种读取方式我们也可以称为==顺序I/O==。从二级索引获取到的记录的id字段的值可能并不相连，而在聚簇索引中记录是根据id（也就是主键）的顺序排列的，所以根据这些并不连续的id值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式我们也可以称为==随机I/O==。一般情况下，顺序I/O比随机I/O的性能高很多。

需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引，比如上述的SQL语句，假如name值在Asa～Barlow之间的用户记录数量占全部记录数量90%，意味着90%多的id值需要回表，那还不如全表扫描。

查询优化器会事先对表中的记录计算一些统计数据，计算需要回表的记录数。需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。

1
2
SELECT * FROM person_info ORDER BY name, birthday, phone_number; # 全表扫描
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10; # 二级索引 + 回表

覆盖索引

为了彻底告别回表操作带来的性能损耗，我们建议：最好在查询列表里只包含索引列，比如这样：

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

只需要用到索引的查询方式称为索引覆盖。相应的排序操作也优先使用覆盖索引的方式进行查询。

我们很不鼓励用*号作为查询列表，最好把我们需要查询的列依次标明。

如何挑选索引

在使用索引时需要注意下面这些事项：

  • 只为用于搜索（WHERE）、排序或分组的列创建索引（出现在查询列表中的列就没必要建立索引了）

  • 为列的基数大的列创建索引

    ​ 列的基数指的是某一列中不重复数据的个数。在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中

  • 索引列的类型尽量小

​ 以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT这么几种类型（1~4字节）。在表示的整数范围允许的情况下，尽量让索引列使用较小的类型。数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

  • 可以只对字符串值的前缀建立索引

在二级索引的记录中只保留字符串前几个字符，只对字符串的前几个字符进行索引，这样既节约空间，又减少了字符串的比较时间。比方说我们在建表语句中只对name列的前10个字符进行索引可以这么写

1
2
3
4
5
CREATE TABLE person_info(
    name VARCHAR(100) NOT NULL,
	...
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

 name(10)就表示在建立的B+树索引中只保留记录的前10个字符的编码，这种只索引字符串值的前缀的策略是我们非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候。

  • 只有索引列在比较表达式中单独出现才可以适用索引
  1. WHERE my_col * 2 < 4
  2. WHERE my_col < 4/2

第1个WHERE子句中会依次遍历所有的记录，计算这个表达式的值是不是小于4

第2个WHERE子句中my_col列并是以单独列的形式出现的，这样的情况可以直接使用B+树索引。

所以结论就是：如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

  • 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。

减少类似以下情形的发生：

image-20230722113509796

  • 定位并删除表中的重复和冗余索引
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE person_info(
	...
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
    KEY idx_name (name(10)) # 冗余
);  
CREATE TABLE repeat_index_demo (
	...
    UNIQUE uidx_c1 (c1),
    INDEX idx_c1 (c1) # 重复
);

  • 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。