20200617学习笔记

基数

 一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好

我们可以使用 show index 方法，看到一个索引的基数

MySQL 是怎样得到索引的基数的呢？

采样统计 : 因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

analyze table t 命令，可以用来重新统计索引信息

给字符串加索引

一张email表给email列加索引，语句如下

• mysql> alter table SUser add index index1(email);  //默认整个email字段
• mysql> alter table SUser add index index2(email(6)); //取email字段前六个字符
• 截取部分字符当索引的优点： 占用空间少
• 带来的问题： 不恰当的长度会减小基数
• 如何确定长度： 
  • 先统计整个表不同的数据有多少条 select count(distinct email) as L from t;
  • 然后假设再统计不同email长度对应的count是多少：

//挑选一个在你可接受的损失范围的长度即可
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 t;

• 也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
• 注意：使用了前缀索引那么就相当于放弃了覆盖索引的优化

脏页和干净页

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

平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush） ，一下四种情况

1. redo log 写满了，停止更新操作， 把 checkpoint 往前推进，redo log 留出空间可以继续写
2. 系统内存不足， 当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
3. 系统空闲，flush脏页（非上述问题原因）
4. 一个sql中存在大量的flush 脏页的行为
5. 系统正常关闭，flush脏页

• innodb_io_capacity 参数可以告诉InnoDB电脑刷脏页的能力

试想一下，如果你来设计策略控制刷脏页的速度，会参考哪些因素呢？

• InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。
• 参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字
• InnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值，我们假设为 N