MYSQL是怎样运行的-第7章-B+树索引的使用-CSDN博客

本文链接：https://blog.csdn.net/Lbogu/article/details/139654764

一、索引的代价

空间上的代价

每建立一个索引都要为它建立一棵 B+ 树，每一棵 B+ 树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的 B+ 树由许多数据页组成，会占一片存储空间。

时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个 B+ 树索引。而且 B+ 树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收啥的操作来维护好节点和记录的排序。

所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。

二、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)

);

person_info 表需要注意两点：

表中的主键是 id 列，它存储一个自动递增的整数。所以 InnoDB 存储引擎会自动为 id 列建立聚簇索引。我们额外定义了一个二级索引 idx_name_birthday_phone_number ，它是由 3个列组成的联合索引。所以在这个索引对应的 B+ 树的叶子节点处存储的用户记录只保留 name 、 birthday 、 phone_number 这三个列的值以及主键 id 的值，并不会保存 country 列的值。从这两点可以再次看到，一个表中有多少索引就会建立多少棵 B+ 树， person_info 表会为聚簇索引和 idx_name_birthday_phone_number 索引建立 2 棵 B+ 树。

内节点中存储的是目录项记录，叶子节点中存储的是用户记录（由于不是聚簇索引，所以用户记录是不完整的，缺少 country 列的值）。

2.1 全值匹配

如果搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，比方说下边这个查找语句：

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

2.2 匹配左边的列

其实搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行，比方说下边的查询语句：

SELECT * FROM person_info WHERE name = 'Ashburn';

或者包含多个左边的列也行：

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

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

2.3 匹配列前缀

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

SELECT * FROM person_info WHERE name LIKE 'As%';

2.4 匹配范围值

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

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

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

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

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

2.6 用于排序

有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。在 MySQL 中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名： filesort ）。如果 ORDER BY 子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤，比如下边这个简单的查询语句：

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

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

ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出 ORDER BY phone_number, birthday, name 的顺序，那也是用不了 B+ 树索引。使用后边的列进行排序，比如这样：

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

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

ASC、DESC混用

对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么都是 ASC 规则排序，要么都是 DESC 规则排序。

WHERE子句中出现非排序使用到的索引列

如果WHERE 子句中出现了非排序使用到的索引列，那么排序依然是使用不到索引的，比方说这样：

SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;

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

有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序，比方说：

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

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

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

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

2.7 用于分组

分组查询：

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

分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组。

三、回表的代价

这个查询：

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

由于索引 idx_name_birthday_phone_number 对应的 B+ 树中的记录首先会按照 name 列的值进行排序，所以值在 Asa ～ Barlow 之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这些连着的记录从磁盘中读出来，这种读取方式我们也可以称为顺序I/O 。根据第 1 步中获取到的记录的 id 字段的值可能并不相连，而在聚簇索引中记录是根据 id （也就是主键）的顺序排列的，所以根据这些并不连续的 id

值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式我们也可以称为随机I/O 。一般情况下，顺序 I/O 比随机 I/O 的性能高很多，所以步骤 1的执行可能很快，而步骤 2 就慢一些。所以这个使用索引 idx_name_birthday_phone_number 的查询有这么两个特点：

会使用到两个 B+ 树索引，一个二级索引，一个聚簇索引。

访问二级索引使用顺序I/O ，访问聚簇索引使用随机I/O 。

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

上边的查询可以改写成这样：

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

添加了 LIMIT 10 的查询更容易让优化器采用二级索引 + 回表的方式进行查询。

3.1 覆盖索引

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

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

只查询 name , birthday , phone_number 这三个索引列的值，所以在通过 idx_name_birthday_phone_number 索引得到结果后就不必到聚簇索引中再查找记录的剩余列，这样就省去了回表操作带来的性能损耗。这种只需要用到索引的查询方式称为索引覆盖。排序操作也优先使用覆盖索引的方式进行查询，比方：

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

虽然这个查询中没有 LIMIT 子句，但是采用了覆盖索引，所以查询优化器就会直接使用 idx_name_birthday_phone_number 索引进行排序而不需要回表操作了。

当然，如果业务需要查询出索引以外的列，那还是以保证业务需求为重。但是不鼓励用 * 号作为查询列表，最好把需要查询的列依次标明。

四、如何挑选索引

4.1 只为用于搜索、排序或分组的列创建索引

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

4.2 考虑列的基数

列的基数指的是某一列中不重复数据的个数。如果所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排序，无法进行快速查找了。如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了。所以结论就是：最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

4.3 索引列的类型尽量小

类型大小指的就是该类型表示的数据范围的大小。想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型：

数据类型越小，在查询时进行的比较操作越快（这是CPU层次的东东）

数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的 I/O 。

4.4 索引字符串值的前缀

一个字符串其实是由若干个字符组成，如果在 MySQL 中使用 utf8 字符集去存储字符串的话，编码一个字符需要占用 1~3 个字节。假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的 B+ 树中有这么两个问题：

B+ 树索引中的记录需要把该列的完整字符串存储起来，而且字符串越长，在索引中占用的存储空间越大。

如果 B+ 树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

我们前边儿说过索引列的字符串前缀其实也是排好序的，所以只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再对比。可以这么写：

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 个字符的编码，这种只索引字符串值的前缀的策略是鼓励的，尤其是在字符串类型能存储的字符比较多的时候。

索引列前缀对排序的影响

使用了索引列前缀，比方只把 name 列的前 10个字符放到了二级索引中：

SELECT * FROM person_info ORDER BY name LIMIT 10;

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

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

假设表中有一个整数列 my_col ，我们为这个列建立了索引。下边的两个 WHERE 子句虽然语义是一致的，但是在效率上却有差别：

1. WHERE my_col * 2 < 4

2. WHERE my_col < 4/2

第1 个 WHERE 子句中 my_col 列并不是以单独列的形式出现的，而是以 my_col * 2 这样的表达式的形式出现的，存储引擎会依次遍历所有的记录，计算这个表达式的值是不是小于 4 ，所以这种情况下是使用不到为 my_col 列建立的 B+ 树索引的。而第 2 个 WHERE 子句中 my_col 列并是以单独列的形式出现的，这样的情况可以直接使用 B+ 树索引。

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

4.6 主键插入顺序

对于一个使用 InnoDB 存储引擎的表来说，在没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小。如果已经插入的页已经满了，就必须得分页，进行页面分裂和记录移位。此时就会导致性能损耗。

最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入。

4.7 冗余和重复索引

避免重复建立索引。增加维护成本和空间成本。