MySQL统计数据

2021-12-12 04:56:16

13 统计数据

表结构

CREATE TABLE single_table (
    id           int not null auto_increment,
    key1         VARCHAR(100),
    key2         int,
    key3         varchar(100),
    key_part1    varchar(100),
    key_part2    varchar(100),
    key_part3    varchar(100),
    common_field varchar(100),
    primary key (id),
    key idx_key1 (key1),
    unique key uk_key2 (key2),
    key idx_key3 (key3),
    key idx_part (key_part1, key_part2, key_part3)
) ENGINE = INNODB
  CHARSET = utf8;

统计数据的存储方式

InnoDB提供了两种的统计数据存储方式，分别是永久性地统计数据（存储在磁盘）、非永久性地统计数据（存储在内存），InnoDB默认以表为单位来存储统计数据，可以通过指定stats_persistent属性来指明表统计数据的存储方式。

永久性地统计数据

当选择把表的统计数据存储在磁盘上时，实际上是把这些统计数据存储到2个表上：

show tables from mysql like 'innodb%stats';

+--------------------------------+
| Tables_in_mysql (innodb%stats) |
+--------------------------------+
| innodb_index_stats             |
| innodb_table_stats             |
+--------------------------------+

innodb_table_stats：表中地每一条记录对应一个表的统计数据

innodb_index_stats：表中的每一条记录对应一个索引的统计数据

查看single_table表的统计数据

select *
from mysql.innodb_table_stats where table_name='single_table';

database_name	table_name	last_update	n_rows	clustered_index_size	sum_of_other_index_sizes
mysql_run	single_table	2022-01-13 22:24:52	9937	33	63

clustered_index_size：聚簇索引占用页面的数量

sum_of_other_index_sizes：其他索引占用页面的数量

n_rows：表中记录数量的估计值，InnoDB计算记录数量的大致过程是，按照一定的算法从聚簇索引中选取叶子节点页面，统计页面中的记录数量，然后计算平均值乘叶子节点的数量

计算表记录数量估算时，页面采样数量是系统变量innodb_stats_persistent_sample_pages决定的

聚簇索引页面及其他索引页面数量的统计

一个索引有两个段（叶子节点段、非叶子节点段），每个段由一些零星的页面及一个完整的区（64个页）构成。统计一个段的页面数量步骤如下：

从数据字典找到表的各个索引对应的根页面位置，sys_indexes表存储了各个索引对应的根页面信息
根页面中的Page Header部分中包含了Segment Header信息（对应叶子结点段、非叶子节点段）
从两个段中的找到对应的INODE Entry结构
针对每个段的INODE Entry结构，可以找出各个零星页面的地址、free、not_free、full的链表基节点
统计零星页面的个数、在链表基节点上读取占用区的数量（在区中可能有些页面并没有被占用，但统计时也把它们算进去了）

查看single_table索引的统计数据

select *
from mysql.innodb_index_stats
where table_name = 'single_table';

database_name	table_name	index_name	last_update	stat_name	stat_value	sample_size	stat_description
mysql_run	single_table	PRIMARY	2022-01-13 22:24:52	n_diff_pfx01	9937	20	id
mysql_run	single_table	PRIMARY	2022-01-13 22:24:52	n_leaf_pages	32	NULL	Number of leaf pages in the index
mysql_run	single_table	PRIMARY	2022-01-13 22:24:52	size	33	NULL	Number of pages in the index
mysql_run	single_table	idx_key1	2022-01-13 22:24:52	n_diff_pfx01	4183	13	key1
mysql_run	single_table	idx_key1	2022-01-13 22:24:52	n_diff_pfx02	10000	13	key1,id
mysql_run	single_table	idx_key1	2022-01-13 22:24:52	n_leaf_pages	13	NULL	Number of leaf pages in the index
mysql_run	single_table	idx_key1	2022-01-13 22:24:52	size	14	NULL	Number of pages in the index
mysql_run	single_table	idx_key3	2022-01-13 22:24:52	n_diff_pfx01	4209	14	key3
mysql_run	single_table	idx_key3	2022-01-13 22:24:52	n_diff_pfx02	10000	14	key3,id
mysql_run	single_table	idx_key3	2022-01-13 22:24:52	n_leaf_pages	14	NULL	Number of leaf pages in the index
mysql_run	single_table	idx_key3	2022-01-13 22:24:52	size	15	NULL	Number of pages in the index
mysql_run	single_table	idx_part	2022-01-13 22:24:52	n_diff_pfx01	4188	16	key_part1
mysql_run	single_table	idx_part	2022-01-13 22:24:52	n_diff_pfx02	8492	16	key_part1,key_part2
mysql_run	single_table	idx_part	2022-01-13 22:24:52	n_diff_pfx03	9905	16	key_part1,key_part2,key_part3
mysql_run	single_table	idx_part	2022-01-13 22:24:52	n_diff_pfx04	10000	16	key_part1,key_part2,key_part3,id
mysql_run	single_table	idx_part	2022-01-13 22:24:52	n_leaf_pages	16	NULL	Number of leaf pages in the index
mysql_run	single_table	idx_part	2022-01-13 22:24:52	size	17	NULL	Number of pages in the index
mysql_run	single_table	uk_key2	2022-01-13 22:24:52	n_diff_pfx01	10000	16	key2
mysql_run	single_table	uk_key2	2022-01-13 22:24:52	n_leaf_pages	16	NULL	Number of leaf pages in the index
mysql_run	single_table	uk_key2	2022-01-13 22:24:52	size	17	NULL	Number of pages in the index

stat_name列的值中有

n_leaf_pages：索引`叶子节点实际占用的页面数

size：索引占用的页面数（包括未使用的页面）

n_diff_pfxNN：索引中第一列到该列不重复的个数，对于联合索引，NN为01，则为联合索引中第1个列不重复值，02则为1~2列，在上面表格中04，则为key_part1,key_part2,key_part3,id这4个索引列组合中的不重复的个数。

对于普通索引，它们可能有重复的值，此时只有在索引列基础上加上主键值才可以区分索引值相同的记录。对于主键索引、唯一索引，它们保证索引列的值唯一，所以不需要在索引列的基础上加上主键列就可以进行区分。

定期更新统计数据

随着对表进行增删改操作，相关的统计数据也会发生变化，MySQL提供了2种更新统计数据的方式：

开启innodb_stats_auto_recalc：默认开启，发生变动的记录超过表大小的10%时，就会自动重新计算统计数据，这个操作是异步的
手动调用analyze table：analyze table single_table;，这个操作是同步的，表的索引较多时，这个过程可能比较慢

存储统计数据的表和普通表一样，我们可以对它进行更新操作

非永久性地统计数据

把系统变量innodb_stats_persistent设置为关闭、创建或修改表时设置stats_persistent为0，之后创建的表（对应的表）地统计数据地方式是非永久性的，非永久性的统计数据页面采样数量由

innodb_stats_transient_sample_pages决定（默认值是8）。

Null值与innodb_stas_method的使用

索引列的不重复值的数量对MySQL的优化器十分重要，它只要应用在

单调扫描区间太多时，将使用统计数据计算扫描区间的记录数量，而不使用index dive
在执行连接查询时，如果涉及了等值匹配连接条件，而被驱动表对应列上又有索引，那么就可以使用reff访问方法

查询被驱动表，在执行真正的查询前，on子句连接条件上的常数值并没有确定，也就不能使用index dive的方法来计算扫描区间中记录的数量，此时只能依赖于统计数据进行计算

在统计索引列不重复值的记录数时，可能会遇到对应的列包含NULL的情况，对于NULL值，我们可以有不同的方式看待NULL值：

每一个NULL值都是不同的
NULL都是相同的
NULL是没有意义的，计算不重复值时不考虑NULL

MySQL认为任何与NULL值进行比较的值都为NULL，但它提供了innodb_stats_method来设置对待NULL的方式，这些方式分别对应上面看待NULL值的方式

nulls_unequal：索引列NULL值多时，优化器认为重复值比较多，会倾向于不使用索引方法

nulls_equal：索引列NULL值多时，优化器认为重复值比较少，会倾向于使用索引方法

nulls_ignored

码农公寓