mysql github week2_week2-MySQL实践一

MySQL实践一

所有结论都基于InnoDB引擎，参考丁奇的MySQL45讲

唯一索引与普通索引的选择

查询

在查询中：

对于普通索引，当查询到满足条件的一条记录后，数据库会继续查询下一条记录，知道不满足为止。

对于唯一索引，当查询到满足条件的一条记录后，就终止了。

计算机时按页读取的，一般满足条件的数据是在这同一页的。对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。因此，在查询上两者差距微乎其微。

更新

情况一：需要更新的数据页在内存中，则两者差距不大。

情况二：需要更新的数据页在磁盘上。

在利用普通索引做更新操作时，如果数据页在磁盘上，InnoDB会引入change buffer记录下对该数据页的更新操作。当需要读取该数据页的记录时，读取该数据页到内存，根据change buffer再对数据页进行merge。change buffer 减少了随机访问磁盘的操作。

利用唯一索引时，为了保证唯一性，引擎一定会进行I/O操作取出数据页。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

所作的事情上，change buffer和redo log是一样的，但它们两者的功能是不同，change buffer是减少随机访问磁盘，而redo log是减少随机写磁盘。

当然，change buffer并非永远有效，当我更新记录后，立即查询，会立即触发 merge 过程。随机访问磁盘没有减少，反而增加了change buffer的维护成本。

给字符串加索引

邮箱一般有字符串进行存储，如执行select f1, f2 from SUser where email='xxx';如果没有建立索引，则会进行全表扫描。而以整个字符串建立索引，数据页存储行数变少影响数据库性能。

前缀索引

前缀索引会损失区分度，选择合适长度，是建立前缀索引的关键。观察前缀区分的sql如下：

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,

前缀索引会对覆盖索引索引产生影响，即使email(max_len) 的前缀索引，这时候虽然前缀索引已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。

如果遇到身份证号码类似的情况，前缀索引的区分度不高。

可以将身份证号倒序后再存储。

mysql> select field_list from t where id_card = reverse('input_id_card_string');

使用hash字段。新建一列，存储身份证号码的hash值。

而这两种方法相同点：都不支持范围查询。

数据库刷脏页

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

什么情况会引发数据库的(刷脏页) flush 过程呢？

1、redo log 满了，系统不得不将进行写磁盘操作，将redo log空出一部分。

2、系统内存不足了。需要淘汰一部分数据页，如果淘汰的是干净页，直接释放，如果是脏页，就 需要将脏页写到磁盘。

3、空闲时间，清理redo log写磁盘。

4、MySQL正常关闭，将内存中所有脏页写到磁盘。

后两者，对MySQL运行时的效率没有影响。

出现第一种情况时，所有的更新操作都会被阻塞，知道redo log有空余。

第二种情况时，InnoDB 用缓冲池(buffer pool)管理内存，缓冲池中的内存页有三种状态：没有使用的页、使用了是干净页、使用了是脏页。

InnoDB 刷脏页的控制策略

首先我们需要告诉InnoDB所在主机的IO能力。需要设置innodb_io_capacity参数，一般情况下，将innodb_io_capacity设置成磁盘的IOPS。

测试磁盘随机读写的命令如下：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

InnoDB控制刷脏页速度，有两个参考因素，一个是缓冲池中脏页比例，一个是 redo log 写盘速度。参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。

根据这两个因素分别计算出两个(0-100)的数R1与R2，选择

Max(R1，R2)%*innodb_io_capacity作为当前刷脏页的速度。

合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。

脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到

mysql> 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 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居。

机械硬盘IOPS一般不大，连坐机制可以有效减少随机IO次数。但对SSD硬盘IOPS不是瓶颈，需要关闭连坐机制，减少响应时间。在MySQL8.0中，innodb_flush_neighbors默认为0。