MySQL字符串索引&脏页刷盘

字符串加索引的方式？

对于字符串进行添加索引，我们除了对整个字符串加索引以外，还可以添加前缀索引。

什么是前缀索引？

-- 普通索引
alter table test add index email(email);
-- 前缀索引
alter table test add index email_pre(email(6));

前缀索引的好处？

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

前缀索引的弊端？

前缀索引会使覆盖索引失效，额外增加回表的消耗，如果前缀索引的长度选择区分度不高，会额外导致扫描行数增加。

其他给字符串加索引的方式？

• 倒序存储以后如果用较少的长度获得较高的区分度，可以加前缀索引
• 使用hash字段：表上额外增加一个整数字段，用来保存字符串的校验码，同时对该整数字段加索引

什么是脏页？

MySQL在更新数据的时候会写redo log并且更新内存以后就会返回，数据文件并不会立即更新，这就是所谓的WAL机制。

当内存被更新以后，内存中的数据页就会和磁盘上的数据页存在不一致的情况，该内存也就被称为脏页。

内存中的数据被写入磁盘以后，内容变为一致，此时该内存页就被称为干净页。

什么叫刷脏页？

内存数据页中的内容被写入磁盘数据页中的过程称为刷脏页。

什么时候会刷脏页？

• redo log写满了，会阻塞所有更新，对系统影响较大，需要redo log的一部分日志对应的内存页刷新到磁盘数据页中
• 系统内存不够，当需要新的内存数据页时，此时需要淘汰一些旧的数据页，如果淘汰的数据页是脏页，就需要刷脏页，如果一次淘汰的脏页太多，会导致查询响应时间变长
• MySQL空闲时，会进行刷脏页操作
• MySQL正常关闭时，会进行刷脏页操作

InnoDB如何控制刷脏页的频率？

首先确认InnoDB所在主机的IO能力，此时需要用到数据库的innodb_io_capacity参数，该参数推荐设置为磁盘的IOPS。磁盘的IOPS可以通过fio工具进行测试。

-- 查看innodb_io_capacity的设置
show global variables like 'innodb_io_capacity';

InndoDB刷脏页主要考虑以下两个因素：

• 脏页的比例
• redo log的写盘速度

MySQL会根据F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

• F1(M)：根据当前脏页比例（M）计算出来的一个0-100之间的数字
• F2(N)：InnoDB每次写入日志都有一个序号，当前写入的序号和checkpoint对应的序号之间的差值记为N，然后根据F2(N)一个特殊的公式计算出0-100之间的一个数字，F2(N)中，N越大算出来的值越大

脏页比例

参数innodb_max_dirty_pages_pct是脏页比例的上限，MySQL 8.0中是90%。

当前脏页比例可以通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total计算获得，具体sql计算指令如下：

-- 需要在performance_schema库下执行
select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

连坐机制

InnoDB在刷脏页的时候，如果该脏页旁边的页也是脏页，会同时把相邻的脏页刷掉。

该刷脏页行为由参数innodb_flush_neighbors控制:

• 如果为0：禁止此行为
• 如果为1：开启连坐行为，刷相邻的脏页

show global variables like  'innodb_flush_neighbors';

对于机械硬盘，开启连坐会减少随机IO的消耗，但对于SSD，没必要开启该参数。