innoDB数据收集方式—永久性&非永久性（四十三)

2023-10-29

上篇文章说了连接查询的成本，主要由驱动表的扇出值和被驱动表的查询方法决定，而成本这些都是可以在%cost%表查看的，因为分为server和engine表，server不管理数据成本，里面包含连接管理，查询缓存，sql解码，sql优化，engine就是数据引擎成本，而distinct，union等特殊查询，会建立临时表，临时表看数据量可能建立磁盘或者内存，比如distinct会用unique索引建立临时表去重。

连接查询成本(2)---mysql进阶（四十二)

我们前面说了show index from可以看到表的索引信息，show table status可以看到表的数据，那这些数据怎么来的呢？分为两种不同的统计数据方式。

两种不同的统计数据存储方式

innoDB_stats_persistent字段，在mysql5.6.6之前是OFF，代表默认吧统计数据存储在内存中，在之后是on，默认吧数据存储在磁盘上。

innoDB默认以表为单位存储数据，所以在建立表的时候我们可以指定存储在磁盘还是存储在内存，当指定为stats_persistent为1时，代表永久性存储，当stats_persistent为0时，为内存存储。当没有指定的时候，默认用innoDB_stats_persistent值。

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);

ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

永久性统计：

表示统计数据存储在磁盘上，服务器重启之后这些数据还在。

我们可以查询数据库：

mysql> show tables from mysql like 'innodb%';
+---------------------------+
| Tables_in_mysql (innodb%) |
+---------------------------+
| innodb_index_stats        |
| innodb_table_stats        |
+---------------------------+
2 rows in set (0.00 sec)

可以看到这两个表都位于系统数据库下面，

innoDB_table_stats：代表着每个表的统计数据，每一条记录对应着一个表的统计数据。

innoDB_index_stats：存着索引的统计数据，每一条记录对应着一个索引的统计数据。

innodb_table_stats表

我们先把表里数据查询出来分析：

mysql> SELECT * FROM mysql.innodb_table_stats;
+---------------+---------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name    | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+---------------+---------------------+--------+----------------------+--------------------------+
| mysql         | gtid_executed | 2018-07-10 23:51:36 |      0 |                    1 |                        0 |
| sys           | sys_config    | 2018-07-10 23:51:38 |      5 |                    1 |                        0 |
| mac           | single_table  | 2018-12-10 17:03:13 |   9693 |                   97 |                      175 |
+---------------+---------------+---------------------+--------+----------------------+--------------------------+
3 rows in set (0.01 sec)

database_name：代表数据库名称。

table_name：表名称。

last_update:表最后更新时间。

n_rows:表里存在的数据。

clustered_index_size：聚簇索引占的页。

sum_of_other_index_sizes：其他索引占的页。

我们可以看到熟悉的single_table表数据是9636条，聚簇索引占的页面时97，其他索引占的页面时175，这些都是估值。

n_rows数据的收集：

按照一定的算法，随机选几个叶子节点的数据，然后算一个叶子节点数据平均值，再把平均值乘以总叶子数。所以算出来的是一个估值，innodb索引设置了一个系统参数innodb_states_persistent_simple_page，默认是20，当这个值设置的越大，计算的越精确，但耗时也就越长了，当这个值越小，就计算的偏离更大，但速度也会快很多。

前面说过innoDB是以表为单位来收集和统计数据的，所以也可以设置，不设置就是取innodb_states_persistent_simple_page的默认值

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;

ALTER TABLE 表名 Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;

clustered_index_size 和 sum_of_other_index_sizes

这里会用到innoDB表空间的知识，如果大家仔细看了前面的文章，下面是可以看懂的，否则建议停止。

1、先从字典表sys_indexes查询各个索引根页面信息。

2、从根页面的page header找到对应的segment header，有两个字段：page_btr_seg_leaf:b+树叶子段对应的segment header，page_btr_seg_top：b+树非叶子段对应的segment header。

3、从叶子段和非叶子段找到两个inode entry

4、inode entry里面有三个链表，free，not_full,full。从三个链表读出list lenth占用区的大小，然后每个区有64个页，就知道有多少个页。

5、聚簇索引b+树吧叶子节点和非叶子节点里的页加起来就是clustered_inde_size,同理其他索引这样加起来获取的就是sum_of_other_sizes。

注意：前面说过满32个页面，才会吧区划分到某个段区，否则这个区就是碎片区，所以上面计算的值可能比真实聚簇索引和非聚簇索引占用的页面数要多。

innoDB_index_stats：

先把表查询来看看：

mysql> 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                  |
+---------------+--------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
| xiaohaizi     | single_table | PRIMARY      | 2018-12-14 14:24:46 | n_diff_pfx01 |       9693 |          20 | id                                |
| xiaohaizi     | single_table | PRIMARY      | 2018-12-14 14:24:46 | n_leaf_pages |         91 |        NULL | Number of leaf pages in the index |
| xiaohaizi     | single_table | PRIMARY      | 2018-12-14 14:24:46 | size         |         97 |        NULL | Number of pages in the index      |
| xiaohaizi     | single_table | idx_key1     | 2018-12-14 14:24:46 | n_diff_pfx01 |        968 |          28 | key1                              |
| xiaohaizi     | single_table | idx_key1     | 2018-12-14 14:24:46 | n_diff_pfx02 |      10000 |          28 | key1,id                           |
| xiaohaizi     | single_table | idx_key1     | 2018-12-14 14:24:46 | n_leaf_pages |         28 |        NULL | Number of leaf pages in the index |
| xiaohaizi     | single_table | idx_key1     | 2018-12-14 14:24:46 | size         |         29 |        NULL | Number of pages in the index      |
| xiaohaizi     | single_table | idx_key2     | 2018-12-14 14:24:46 | n_diff_pfx01 |      10000 |          16 | key2                              |
| xiaohaizi     | single_table | idx_key2     | 2018-12-14 14:24:46 | n_leaf_pages |         16 |        NULL | Number of leaf pages in the index |
| xiaohaizi     | single_table | idx_key2     | 2018-12-14 14:24:46 | size         |         17 |        NULL | Number of pages in the index      |
| xiaohaizi     | single_table | idx_key3     | 2018-12-14 14:24:46 | n_diff_pfx01 |        799 |          31 | key3                              |
| xiaohaizi     | single_table | idx_key3     | 2018-12-14 14:24:46 | n_diff_pfx02 |      10000 |          31 | key3,id                           |
| xiaohaizi     | single_table | idx_key3     | 2018-12-14 14:24:46 | n_leaf_pages |         31 |        NULL | Number of leaf pages in the index |
| xiaohaizi     | single_table | idx_key3     | 2018-12-14 14:24:46 | size         |         32 |        NULL | Number of pages in the index      |
| xiaohaizi     | single_table | idx_key_part | 2018-12-14 14:24:46 | n_diff_pfx01 |       9673 |          64 | key_part1                         |
| xiaohaizi     | single_table | idx_key_part | 2018-12-14 14:24:46 | n_diff_pfx02 |       9999 |          64 | key_part1,key_part2               |
| xiaohaizi     | single_table | idx_key_part | 2018-12-14 14:24:46 | n_diff_pfx03 |      10000 |          64 | key_part1,key_part2,key_part3     |
| xiaohaizi     | single_table | idx_key_part | 2018-12-14 14:24:46 | n_diff_pfx04 |      10000 |          64 | key_part1,key_part2,key_part3,id  |
| xiaohaizi     | single_table | idx_key_part | 2018-12-14 14:24:46 | n_leaf_pages |         64 |        NULL | Number of leaf pages in the index |
| xiaohaizi     | single_table | idx_key_part | 2018-12-14 14:24:46 | size         |         97 |        NULL | Number of pages in the index      |
+---------------+--------------+--------------+---------------------+--------------+------------+-------------+-----------------------------------+
20 rows in set (0.03 sec)

第一列是数据库名称。

第二列是表名。

index_name:索引名字。

stat_name：统计项名称。

stat_value：对应统计项值。

sample_size：为生成统计数据而采样的页面数据。

stat_descreption：对应的统计项描述。

注意这个表的主键是(database_name,table_name,index_name,stat_name),其中stat_name代表统计项名称，所以一个索引代表一个统计项，

Index_name：索引名字，primary主键有三条，idx_key_pary索引又6条。

Stat_name表示该索引统计项名称，stat_value表示该索引统计项的值：

N_leaf_pages：表示该索引叶子节点占用多少页面。

Size：表示该索引占用多少页面。

N_diff_pfxNN：表示索引对应不重复的值。比如n_diff_pfx01表示key_part1单个列不重复的值，比如n_diff_pfx02表示key_part1,key_part2两个列不重复的值。

在计算索引列有多少重复值时候，需要对叶子节点进行采样，sample_page就是采样的页面数量多少。

定期更新统计数据

随着我们不断的增删查改，统计数据肯定也是在变化的，mysql有两种更新统计数据的方法：

开启innodb_stats_auto_recalc：开启决定这服务器会自动计算统计数据，默认值是on，每个表维护着一个变量，当修改的数据大于表数据的百分之10，则会自动计算，更新innoDB_table_stats和innoDB_index_stats。这里是异步计算的，所以如果超过百分之10，可能有几秒钟延迟。
未开启：这时候如果innoDB_stats_auto_recalc是off的，我们可以手动运行

ANALYZE TABLE single_table;

这里计算是同步进行的，尽量不要用，用也选在业务空闲的时间段进行。

手动更新innoDB_table_stats和innoDB_index_stats

因为这两个都属于表，我们自己也可以手动更新他们的数据，修改表的字段之后，mysql优化器并没有加载他，还需要刷新一下，之后我们运行show table status就可以看到数据。

UPDATE innodb_table_stats 
    SET n_rows = 1
    WHERE table_name = 'single_table';

//刷新
flush table single_table;

非永久性统计：

存储在内存里，当服务器关闭就清除，当下次还需要的时候再重新统计。

我们吧系统变量innoDB_stats_persistent默认的on改为off，就是默认非永久性，或者创建表的时候指定stats_persistent为0，也表示非永久性。

与innodb_stats_persistent_sample_pages相比，这里控制采样数据数量的是innodb_stats_transient_sample_pages。因为mysql默认都是开启永久性统计，所以这个用的很少。

innoDB_stats_method的使用

我们知道索引不重复列的数量，这个对mysql优化很重要，他有两个使用场景：

单表查询区间太多：

SELECT * FROM tbl_name WHERE key IN ('xx1', 'xx2', ..., 'xxn');

当in里面的参数太多，采用index_drive访问b+树统计区间记录数量太耗性能，所以依赖统计数据的平均值来计算。

连接查询，如果涉及两个表等值匹配，连接条件的被驱动表又有索引时，则可以用ref访问被驱动表：

SELECT * FROM t1 JOIN t2 ON t1.column = t2.key WHERE ...;

在对t2表查询之前，t2.key的值是不确定的，所以这时候只能依赖统计数据去计算平均值来判断记录数。

假设某个索引查询出来有多个null值怎么处理呢？

所有null为一样，所以不管查询出来几个null，都只显示唯一的一个null。
每个null都是唯一的。
Null值不算唯一，忽略。

Mysql给这个设置了系统变量，innodb_stats_method，相当于这个null怎么定义可以用户自己决定，分别对应着

nulls_equals:null一样。

Nulls_unequals：null每个都不一样。

Nulls_ignored:null值忽略。

总结：innodb有两种存储统计数据的方式，永久性和非永久性，可以用innodb_stats_persistent控制，也可用个人创建表的时候控制，stats_persistent。永久性就是可以存在磁盘上，可以用innoDB_stats_persistent_sample_pages控制采集多少页面。非永久性可以用innoDB_Stats_transient_sample_pages控制采集多少页面。innoDB_stats_auto_recale控制是否自动计算统计数据。

Stats_sample_pages和stats_auto_recalc也可用在创建表的时候控制。

innoDb_stats_methods可以控制null值的对待。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)