谈谈MySQL的基数统计

Hi，大家好！我是白日梦。

今天我要跟你分享的话题是：“大家常说的基数是什么？”

推荐阅读方式#

首发地址：https://mp.weixin.qq.com/s/FgxwAFQbEjv5i-TxjvLK6Q

使用推荐阅读，有更好的阅读体验

一、基数是啥？#

Cardinality指的就是MySQL表中某一列的不同值的数量。

如果这一类是唯一索引，那基数 = 行数。

如果这一列是sex，枚举类型只有男女，那它是基数就是2

Cardinality越高，列就越有成为索引的价值。MySQL执行计划也会基于Cardinality选择索引。

通过下面的方式可以看到表中各列的基数。

比如这个经典的例子：

有一列为sex，那对于sex列中存储的值来说 非男即女，它的基数最大就是2。

那也就完全没有必要为sex建立索引。因为，为了提升你基于sex的查询速度，MySQL会为你选择的这个新索引创建一棵全新的B+Tree。但你sex只有两种值，对于MySQL来说，即使它为你指定的列建立了B+Tree索引，真正执行查询时，最多进行一次二分查询，剩下的操作只能是遍历，所以为sex创建索引意义不大。

二、InnoDB更新基数的时机？#

参数：innodb_stats_auto_recalc控制MySQL是否主动重新计算这些持久性的信息。默认为1表示true，0表示false。

默认情况下当表中的行变化超过10%时，重新计算基数信息。

三、基数是估算出来#

基数并不会实时更新！而且它是通过采样估算出来的值！

至于基数的公式是怎样的，可能并不重要。

重要的是你得知道，他是通过随机采样数据页的方式统计出来的一个估算值。

而且随机采样的页数可以通过参数innodb_stats_persistent_sample_pages 设置，默认值是20。

这就意味着 基数值并不准确，甚至你每次计算的结果相擦还是蛮大的。

四、持久化基数#

可以通过参数innodb_stats_persistent 控制是否持久化基数，默认为off。

当然你可以为一个单独的表设置 STATS_PERSISTENT=1 那么它的 innodb_stats_persistent将自动被启用。

开启它的好处是：重启MySQL不会再重复计算这个值，加快重启速度。

五、如何主动更新基数？#

执行下面的SQL时都会触发InnoDB更新基数（即使你并没有意识到它会更新基数）。

所以尽量选择一个业务低峰期

• analyze table tableName;




如果因为采样的数量太少了，计算的基数错的离谱。那很可能会导致MySQL的优化器选错索引。这是你可以将这个值适当调大。但是增加 太多可能会导致 ANALYZE TABLE运行缓慢。

反之， ANALYZE TABLE运行太慢。你可以适度调整参数

innodb_stats_persistent_sample_pages 的值。但是这又可能导致基数计算的不准确。

如果没有办法平衡两者的关系。可以考虑减少表中索引列的数量或限制分区的数量以降低 ANALYZE TABLE复杂性。表的主键中的列数也很重要，因为主键列被附加到每个非唯一索引中。

参考：

https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-analyze-table-complexity.html

推荐阅读#

1. 大家常说的基数是什么？（已发布）
2. 讲讲什么是慢查！如何监控？如何排查？（已发布）
3. 对NotNull字段插入Null值有啥现象？（已发布）
4. 能谈谈 date、datetime、time、timestamp、year的区别吗？（已发布）
5. 了解数据库的查询缓存和BufferPool吗？谈谈看！（已发布）
6. 你知道数据库缓冲池中的LRU-List吗？（已发布）
7. 谈谈数据库缓冲池中的Free-List？（已发布）
8. 谈谈数据库缓冲池中的Flush-List？（已发布）
9. 了解脏页刷回磁盘的时机吗？（已发布）
10. 用十一张图讲清楚，当你CRUD时BufferPool中发生了什么！以及BufferPool的优化！（已发布）
11. 听说过表空间没？什么是表空间？什么是数据表？(已发布)
12. 谈谈MySQL的：数据区、数据段、数据页、数据页究竟长什么样？了解数据页分裂吗？谈谈看！(已发布)
13. 谈谈MySQL的行记录是什么？长啥样？(已发布)
14. 了解MySQL的行溢出机制吗？(已发布)
15. 说说fsync这个系统调用吧! (已发布)
16. 简述undo log、truncate、以及undo log如何帮你回滚事物! (已发布)
17. 我劝！这位年轻人不讲MVCC，耗子尾汁！ (已发布)
18. MySQL的崩溃恢复到底是怎么回事？ (已发布)
19. MySQL的binlog有啥用？谁写的？在哪里？怎么配置 (已发布)
20. MySQL的bin log的写入机制 (已发布)