MySQL索引(二)

2021-01-01 本文已影响0人 NealLemon

学习笔记是学习了极客时间 - 《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年新年快乐。