MySQL 8.0 histogram statistics

原文: https://mysqlserverteam.com/histogram-statistics-in-mysql/
官方文档：https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html

创建和删除

引用

ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] WITH N BUCKETS;
ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];

新参数 histogram_generation_max_mem_size

决定多少内存大小来创建 singleton 或者 equi-height 类型 histogram，这也就意味着 MySQL 会做数据的 sampling , 对大表且有 data skew 的情况出现 histogram 不准的问题。

查看 sampling rate 语句

引用

SELECT histogram->>'$."sampling-rate"' FROM   information_schema.column_statistics WHERE  table_name = "customer"  AND column_name = "c_birth_country";

查看 histogram 语句

引用

SELECT TABLE_NAME, COLUMN_NAME,HISTOGRAM->>'$."data-type"' AS 'data-type',(HISTOGRAM->>'$."buckets"') AS 'bucket' FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;