MySQL之索引学习——索引(二)

最新推荐文章于 2023-02-24 16:40:58 发布

Serena丶

最新推荐文章于 2023-02-24 16:40:58 发布

阅读量378

点赞数

分类专栏： MySQL学习笔记文章标签： 1024程序员节 mysql 数据库学习 sql

本文链接：https://blog.csdn.net/qq_48871931/article/details/127472121

版权

MySQL学习笔记专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本篇主要介绍MySQL索引的常见优化手段。

一、索引覆盖

索引覆盖：一个索引包含(或覆盖)所有需要查询的字段的值，这种索引中已经包含所有需要读取的列，省去了回表操作带来的性能损耗，即只需扫描索引而无须回表。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

例如：
下面是这个表的初始化语句：

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

在这里插入图片描述
如果执行如下语句：

select * from T where k between 3 and 5

这条 SQL 查询语句的执行流程：

1、在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2、再到 ID 索引树查到 ID=300 对应的 R3；
3、在 k 索引树取下一个值 k=5，取得 ID=500；
4、再回到 ID 索引树查到 ID=500 对应的 R4；
5、在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在查询的过程中，回到主键索引树搜索的过程，我们称为回表。

这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

如果执行如下语句：

select ID from T where k between 3 and 5

这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。

也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

覆盖索引可以减少树的搜索次数，显著提升查询性能。

二、索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

例如：
有一个表的联合索引（name, age），该表的索引树如下：
在这里插入图片描述

如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。

则有如下sql语句

select * from tuser where name like '张%' and age=10 and ismale=1;

在执行这个语句时，通过like '张%'找到第一个满足条件的记录 ID3，之后拍断其他条件是否满足。

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值，执行流程如下：
在这里插入图片描述
这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，执行流程如下：
在这里插入图片描述
InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。

在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

其中的虚线代表执行了一次回表操作。

三、前缀索引

在介绍前缀索引前，在B+树中，B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

假设有如下索引树：
在这里插入图片描述
从上述索引树可以看出，索引项是按照索引定义里面出现的字段顺序排序的，即先以姓名字段排序，在姓名字段相同的情况下，再以年龄排序。

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

如果你要查的是所有名字第一个字是“张”的人，SQL 语句如下

select * from t_user where name like ‘张 %’

这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。

这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

基于上面对最左前缀索引的说明，在建立联合索引的时候，如何安排索引内的字段顺序？

评估标准是，索引的复用能力。

因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。

因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

那么，如果既有联合查询，又有基于 a、b 各自的查询呢？

查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。

这时候，我们要考虑的原则就是空间了。

比如上面这个用户表的情况，name 字段是比 age 字段大的，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

再来就是介绍一下前缀索引

MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。

默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

例如在数据库中有如下建表语句：

create table SUser(
	ID bigint unsigned primary key,
	email varchar(64), 
	... 
)engine=innodb;

此时可以可以建立如下两个索引列：

alter table SUser add index index1(email);
alter table SUser add index index2(email(6));

第一个语句创建的 index1 索引里面，包含了每个记录的整个字符串；

第二个语句创建的 index2 索引里面，对于每个记录都是只取前 6 个字节。

生成的B+树如下：
在这里插入图片描述

由上图可知，email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。

创建的这两个索引，在使用这两个索引查询数据时，执行的过程也是有所不同：

如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：
- 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
- 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
- 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。
- 这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：
- 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
- 到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
- 取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
- 重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。
- 在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。

通过这个对比，可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。

但是通过表中的数据可以发现，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了。

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

那么，当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？

在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

首先，通过下边的语句，算出这个列上有多少个不同的值：

select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下 4~7 个字节的前缀索引，可以用这个语句：

mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

前缀索引对覆盖索引的影响

对比如下两个sql语句：

select id,email from SUser where email='zhangssxyz@xxx.com';

select id,name,email from SUser where email='zhangssxyz@xxx.com';

上述sql语句中，第一条语句只要求返回id和email字段；

如果使用email整个字段作为索引，则第一条语句可以使用覆盖索引，在索引中查询到结果即可直接返回；

如果使用email的前缀索引，就不得不回到主键索引再去判断 email 字段的值；

因此，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素；

前缀的区分度不够好的情况时，如何创建索引？

比如对身份证字段建立索引，在同一地区下身份证的前几位是相同的，因此有可能需要创建长度为 12 以上的前缀索引，才能够满足区分度要求。

但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

为了解决这个问题，可以采取如下的方法：

第一种方式是使用倒序存储
- 由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度。
```
select field_list from t where id_card = reverse('input_id_card_string');
```
第二种方式是使用 hash 字段
- 可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
```
alter table t add id_card_crc int unsigned, add index(id_card_crc);
```

然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。

由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样，索引的长度变成了 4 个字节，比原来小了很多。

四、普通索引 VS 唯一索引

在不同的业务场景下，从性能角度考虑，应该选择普通索引，还是唯一索引？

假设作为索引的字段不重复，则由查询过程可知：

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录；
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索；

可以看出两个查询所带来的性能差距微乎其微。

InnoDB 的数据是按数据页为单位来读写的。当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

因为引擎是按页读写的，当找到记录时，它所在的数据页就都在内存里了。

对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

当然，如果这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

但是，对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。

所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

而更新过程中，MySQL中有一个change buff，首先介绍一下change buff

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

change buffer实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。

除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。
在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。

而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。
比如，要插入某条记录，就要先判断现在表中是否已经存在该记录，而这必须要将数据页读入内存才能判断。
如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer了。
因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

普通索引与唯一索引的更新过程，如果要在这张表中插入一个新记录的话，InnoDB 的处理流程是怎样的。

第一种情况是，这个记录要更新的目标页在内存中
- 对于唯一索引来说，找到对应的插入的位置，判断到没有冲突，插入这个值，语句执行结束；
- 对于普通索引来说，找到对应的插入的位置，插入这个值，语句执行结束。
- 普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。
第二种情况是，这个记录要更新的目标页不在内存中
- 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
- 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。
- 将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。
- change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer 的使用场景

使用 change buffer 对更新过程的加速作用 ，change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。

但是，普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但**之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。**所以，对于这种业务模式来说，change buffer 反而起到了副作用。

change buffer 主要节省的则是随机读磁盘的 IO 消耗。

五、自增主键

InnoDB 创建主键索引默认为聚簇索引，数据被存放在了B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，即避免的页分裂的操作，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

因此，在使用 InnoDB 存储引擎时，如果没有特别的业务需求，建议使用自增字段作为主键。

另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。

在为某个列创建索引时，需要考虑该列中不重复的个数占全部记录数的比例。如果比例太低，则说明该列包含过多重复值，那么在通过二级索引+回表的方式执行查询时，就有可能执行太多次回表操作。

索引列的类型尽量小，类型大小指的就是该类型占用的存储空间的大小。因为数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以存放更多的记录，磁盘I/O带来的性能损耗也就越小（一次页面I/O可以将更多的记录加载到内存中），读写效率也就越高。

以上就是总结一些索引中的一些优化手段。