学习笔记是学习了 极客时间 - 《MySQL实战45讲》整理的笔记。
在之前的 总结 《MYSQL语句执行大体流程》中我们看到一条SQL语句的查询操作会走到优化器，然后才是执行器去执行，优化器的作用就是选择索引，优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在 数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数 据的次数越少，消耗的 CPU 资源越少。
MySQL查询时有时候会选择错索引？
在数据库查询中，保证查询走索引是最基本的SQL优化，但是有时候我们会发现MYSQL没有走我们设置的索引，这到底是什么原因？
MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而 只能根据统计信息来估算记录数。
这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分 度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是 说，这个基数越大，索引的区分度越好。
那么，MySQL 是怎样得到索引的基数的呢？这里，我给你简单介绍一下 MySQL 采样统 计的方法。
为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是 代价太高了，所以只能选择“采样统计”。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个 平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。由于是采样统计，所以不管 N 是 20 还是 8，这个基数都是很容易不准的。
当我们发现如果MYSQL选择错索引的解决方法都有哪些呢？
- 执行
analyze table 表名语句来重新统计索引信息。 - 采用
force index强行选择索引 比如select * from t force index(a)
课程中还有两种我认为平时开发用不到的方式，就不总结了。
字符串索引的添加
在开发过程中，特别是平台开发，会使用到邮箱以及身份证号，有时候查找某个人的账号 需要使用邮箱或者身份证，那么免不了给这两个字段加上索引，但是邮箱和身份证号都是非常长的字符串，如果添加索引的话，会非常消耗空间，那么有没有好的办法来解决呢？
使用前缀索引
MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默 认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
那么我们又如何去判断前缀索引的长度呢？
我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着 重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的 前缀。
假设我们有一张用户表
create table User( ID bigint unsigned primary key, email varchar(64), ... );
我们执行下列语句可以大体区分出 MYSQL的区分度
select
count(distinct email) as L,
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 User;
一般我们控制在占比在5%以内即可,在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。我们就可以给用户表添加索引
alter table SUser add index index_prefix_email(email(6));
这样我们既能减少索引查询后回表查询的次数，又能节省空间。因此使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查 询成本。
但是要注意一点
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使 用前缀索引时需要考虑的一个因素。
其他方式
对于邮箱这种比较有固定格式的可以使用前缀索引，但是如果是身份证号呢?我们知道身份证号前6位是地址编码，如果使用前缀索引的话，那得覆盖到12位以上才能做区分但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少， 搜索的效率也就会越低。
使用倒序存储
由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足 够的区分度
select field_list from t where id_card = reverse('input_id_card_string');
使用Hash字段
每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段,索引的长度减小到了4个字节。由 于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能 是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card = 'xxxxxx'
总结
这两种方式都是基于精确查找。不适用于范围查询，因此在选择的时候要考虑到业务上的影响。
两种方式的优缺点
- 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该 是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
- 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数， 而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂 度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。 3. 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来 的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。 而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。
- 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来 的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。 而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。
SQL查询时突然变慢？
首先我们要知道一个概念
- 脏页：当内存数据页跟磁盘数据页内容不一致的时候。
- 干净页：内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致。
这里的数据页 无论是干净页还是脏页 都是在内存中。
WAL 机制（ Write-Ahead Logging）
当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而写磁盘有四种情况
- 系统比较空闲的时候
- redo log写满的时候
- 系统内存不足就要淘汰一些数据页，空出内存给别的数据页使用
- 系统正常关闭
当我们了解了WAL机制后，其实大家就可以联想到 当我们执行平时很快的更新操作（写内存和日志），但是偶尔SQL会突然变慢，这里的情况可能就是 内存数据也往磁盘写数据的时候（刷脏页）
刷脏页性能的影响
- 系统空闲时刷脏页：由于系统空闲，刷脏页对系统的性能影响可以忽略。
- 系统正常关闭时: 如上。
- redo log写满时：如果redo log写满，MYSQL将不会接受更新，所有的更新操作基本就堵住了，严重影响到了数据库的性能。
- 内存不够时：这种是刷脏页的理想状态，InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。
InnoDB 刷脏页的控制策略
正确的使用 innodb_io_capacity 参数
首先我们需要知道MYSQL所在主机的IO能力，我们可以使用这个命令去检测。
fio -filename=/usr/local/testWrite -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
如果没有fio 工具，可以使用下面命令安装
yum install fio
执行后的结果如下图
wirteIOPS.png
可以参考IOPS的值来设置。
关注脏页比例
参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。
查看脏页比例的执行命令
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;
刷脏页的“连坐”机制
在MySQL中有个机制是 准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。
因此如果如果脏页过多，查询会变得非常慢。
我们可以通过innodb_flush_neighbors 来设置这个行为，在MySQL 8 之前默认值是1，因此如果使用的是MYSQL8之前的版本，需要手动设置这个参数，MYSQL8以后已经将默认参数调整为0了，不需要再去设置。
总结
整理了自己学习后的笔记，在大量的读写请求的时候，上面的概念还是可以起到帮助的作用。最后祝大家2021年新年快乐。
网友评论