表妹问我有哪些索引，我还能不会

MySQL 索引
MySQL
发布日期:   2021-11-04
更新日期:   2022-06-07
文章字数:   4.3k
阅读时长:   15 分
阅读次数:  

上次我们讲了 MySQL 索引的底层原理，现在我们讲讲如何使用 MySQL 索引吧

一、索引的代价

空间上的代价

每建立一个索引都要为它建立一棵 B+ 树，每一棵 B+ 树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的 B+ 树由许多数据页组 成，那可是很大的一片存储空间呢，我们可是节约资源的好小孩呢 😀😀😀

时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个 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)
);

小贴士：

表中的主键是 id 列，它存储一个自动递增的整数。所以 InnoDB 存储引擎会自动为 id 列建立聚簇索引。
二级索引 idx_name_birthday_phone_number 是由3个列组成的联合索引。所以在这个索引对应的 B+ 树的叶子节点处存储的用户记录只保留 name 、 birthday 、 phone_number 这三个列的值以及主键 id 的值，并不会保存 country 列的值

我们只要是创建多少个索引就会建多少棵 B+ 树，现在我们看看索引长啥样吧：

image-20220327103045239

idx_name_birthday_phone_number 索引对应的 B+ 树中页面和记录的排序方式就是这样的：

  • 先按照 name 列的值进行排序。
  • 如果 name 列的值相同，则按照 birthday 列的值进行排序。
  • 如果 birthday 列的值也相同，则按照 phone_number 的值进行排序

只要页面和记录是排好序的，我们就可以通过二分法来快速定位查找 （二分法我相信大家都学过）

1、全值匹配

我们先说啥叫全值匹配呢？

如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，如下面的语句：

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

查询过程：

  • 因为 B+ 树的数据页和记录先是按照 name 列的值进行排序的，所以先可以很快定位 name 列的值是 Ashburn的记录位置。
  • 在 name 列相同的记录里又是按照 birthday 列的值进行排序的，所以在 name 列的值是 Ashburn 的记录里又可以快速定位 birthday 列的值是 ‘1990-09-27’ 的记录。
  • name 和 birthday 列的值都是相同的，那记录是按照 phone_number 列的值排序的，所以联合索引中的三个列都可能被用到

假如调整 WHERE 子句中的几个搜索条件的顺序对查询结果有啥影响么？其实是没影响的，因为 MySQL 有一个叫查询优化器的东东，会分析这些搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件

2、匹配左边的列

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

SELECT * FROM person_info WHERE name = 'Ashburn';

那各位想想下面的 SQL 语句会走哪些索引呢？还是不走

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

这条语句是走 name 列索引的，而不走 phone_number 列索引，因为 B+ 树的数据页和记录先是按照 name 列的值排序的，name 值相同的记录先按照 birthday 的值进行排序， birthday 值相同的记录才按照 phone_number 值进行排序，现在跳过 birthday 去直接找 phone_number 列是用不到索引的，因为 birthday 列的值不同的记录中 phone_number 的值可能是无序的

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

3、匹配列前缀

对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的

举个栗子：查询名字以 ‘As’ 开头的记录，SQL 语句为： 

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

这样是可以走索引的，因为一个排序好的字符串有如下特点：

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

那下面这这条 SQL 语句可不可以呢？

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

答案是否定的，因为字符串中间有 ‘As’ 的字符串并没有排好序，所以只能全表扫描了

小贴士：如果我们想查找类似 “%abc” 的话，可以把数据库中的记录逆序存储，到时候可以用 “cba%”来走索引查询

4、索引下推

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

还是举一个栗子吧：

SELECT * from person_info where  name like '陈%' and country = '吴国'

假如有 name_country 联合索引，在没有使用索引下推之前：

先查询出姓陈的一堆人，然后依次根据每个人回表到聚簇索引查找，找出对应的 country，然后判断是否是 ‘吴国’，这一堆人有几个就回表几次；

使用索引下推之后：

在二级索引查找的时候直接判断 country 是否是’吴国’，把不是’吴国’的直接排除，减少回表的次数

5、匹配范围值

如下的范围查询：

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

这是可以走索引的，查询过程如下：

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

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

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

birthday 是用不到索引的，因为只有 name 值相同的情况下才能用 birthday 列的值进行排序，而这个查询中通过 name 进行范围查找的记录中可能并不是按照 birthday 列进行排序的，所以在搜索条件中继续以 birthday 列进行查找时是用不到这个 B+ 树索引的。

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

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

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

这个查询的条件可以分为3个部分：

  • name = ‘Ashburn’ ，对 name 列进行精确查找，当然可以使用 B+ 树索引了。
  • birthday > ‘1980-01-01’ AND birthday < ‘2000-12-31’ ，由于 name 列是精确查找，所以通过 name =
    ‘Ashburn’ 条件查找后得到的结果的 name 值都是相同的，它们会再按照 birthday 的值进行排序。所以此时对 birthday 列进行范围查找是可以用到 B+ 树索引的。
  • phone_number > ‘15100000000’ ，通过 birthday 的范围查找的记录的 birthday 的值可能不同，所以这个条件无法再利用 B+ 树索引了，只能遍历上一步查询得到的记录

7、用于排序

我们在写查询语句的时候经常需要对查询出来的记录通过 ORDER BY 子句按照某种规则进行排序。一般情况下，我们只能把记录都加载到内存中，再用一些排序算法。在 MySQL 中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序（filesort ）这是非常慢的。但是如果排的序和索引中的列顺序是一样的呢

如下 SQL 语句：

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

这个查询的结果集需要先按照 name 值排序，如果记录的 name 值相同，则需要按照 birthday 来排序，如果 birthday 的值相同，则需要按照 phone_number 排序 ，这不就是和我们建索引牌的序一样的吗，所以直接从索引取出结果就好了

8、用于分组

有如下分组查询语句：

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+ 树索引进行分组。

三、回表的代价

我们先讲解啥是回表，回表就是我们从二级索引查询出索引列和 id，然后到聚簇索引里根据 id 查找，还是举一个栗子吧：

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

从索引 idx_name_birthday_phone_number 对应的 B+ 树中取出 name 值在 Asa ～ Barlow 之间的用户记录。

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

访问二级索引使用 顺序I/O ，访问聚簇索引使用 随机I/O，因为二级索引是按照索引列排序的，而根据二级索引查询出来 id 在聚簇索引中不一定是排序的

需要回表的记录越多，使用二级索引的性能就越低，所以当我们回表的次数很多的时候就可以考虑使用全表扫描了，而不是二级索引 + 回表的方式

覆盖索引

覆盖索引是是啥呢？如果查询的字段在二级索引列里了，不需要进行回表操作，这就叫做覆盖索引。如下：

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

因为我们只查询 name , birthday , phone_number 这三个索引列的值，所以在通过idx_name_birthday_phone_number 索引得到结果后就不必到 聚簇索引 中再查找记录的剩余列，也就是country 列的值了，这样就省去了 回表 操作带来的性能损耗

四、如何挑选索引

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

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

2、考虑列的基数

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

假设某个列的基数为 1 ，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排序，无法进行快速查找了； 而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了

小贴士：最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

3、索引列的类型尽量小

这里所说的类型大小指的就是该类型表示的数据范围的大小

如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不要使用 BIGINT ，能使用 MEDIUMINT 就不要使用 INT，因为：

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

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

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

WHERE my_col * 2 < 4 
WHERE my_col < 4/2

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

5、主键插入顺序

在我们没有显式的创建索引时，表中的数据实际上都是存储在 聚簇索引 的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，这就比较麻烦了，可能会造成分页和记录移位，这就意味着性能损耗

如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗。可以让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入。

好了，到这里我们如何使用索引就讲完了。

巨人的肩膀：

https://juejin.cn/book/6844733769996304392

文章作者: Skyu
文章链接: https://kuangtianyu.github.io/2021/11/04/MySQL/%E8%A1%A8%E5%A6%B9%E9%97%AE%E6%88%91%E6%9C%89%E5%93%AA%E4%BA%9B%E7%B4%A2%E5%BC%95%EF%BC%8C%E6%88%91%E8%BF%98%E8%83%BD%E4%B8%8D%E4%BC%9A/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Skyu !
MySQL 索引
赏
  目录