在上一期《数据库对象信息记录表|全方位认识 mysql 系统库》中,我们详细介绍了mysql系统库中的元数据记录表,本期我们将为大家带来系列第四篇《统计信息记录表|全方位认识 mysql 系统库》,下面请跟随我们一起开始 mysql 系统库的系统学习之旅吧。
如何配置统计信息持久化优化。
如何配置统计信息的持久化优化自动计算。
如何配置单个表的统计信息持久化优化。
CREATE TABLE `t1` (
`id` int(8) NOT NULL auto_increment,
`data` varchar(255),
`date` datetime,
PRIMARY KEY (`id`),
INDEX `DATE_IX` (`date`)
) ENGINE=InnoDB,
STATS_PERSISTENT=1,
STATS_AUTO_RECALC=1,
STATS_SAMPLE_PAGES=25;
如何配置InnoDB优化器统计信息的采样页数。
如何配置在持久统计信息的计算中包括删除标记的记录。
统计信息持久化依赖于mysql数据库下的表innodb_table_stats和innodb_index_stats,这些表在安装,升级和源代码构建过程中会自动设置。
该表提供查询表数据相关的统计信息。
下面是该表中存储的信息内容。
root@localhost : test 08:00:46> use mysql
Database changed
root@localhost : mysql 08:01:30> select * from innodb_table_stats where table_name='test'\G
*************************** 1. row ***************************
database_name: test
table_name: test
last_update: 2018-05-24 20:00:50
n_rows: 6
clustered_index_size: 1
sum_of_other_index_sizes: 2
1 row in set (0.00 sec)
表字段含义。
该表提供查询索引相关的统计信息。
下面是该表中存储的信息内容。
root@localhost : mysql 08:01:34> select * from innodb_index_stats where table_name='test';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value |
sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | test | PRIMARY | 2018-05-24 20:00:50 | n_diff_pfx01 | 5 | 1 | a |
| test | test | PRIMARY | 2018-05-24 20:00:50 | n_diff_pfx02 | 6 | 1 | a,b |
| test | test | PRIMARY | 2018-05-24 20:00:50 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | test | PRIMARY | 2018-05-24 20:00:50 | size | 1 | NULL | Number of pages in the index |
| test | test | i1 | 2018-05-24 20:00:50 | n_diff_pfx01 | 5 | 1 | c |
| test | test | i1 | 2018-05-24 20:00:50 | n_diff_pfx02 | 5 | 1 | c,d |
| test | test | i1 | 2018-05-24 20:00:50 | n_diff_pfx03 | 6 | 1 | c,d,a |
| test | test | i1 | 2018-05-24 20:00:50 | n_diff_pfx04 | 6 | 1 | c,d,a,b |
| test | test | i1 | 2018-05-24 20:00:50 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | test | i1 | 2018-05-24 20:00:50 | size | 1 | NULL | Number of pages in the index |
| test | test | i2uniq | 2018-05-24 20:00:50 | n_diff_pfx01 | 6 | 1 | e |
| test | test | i2uniq | 2018-05-24 20:00:50 | n_diff_pfx02 | 6 | 1 | e,f |
| test | test | i2uniq | 2018-05-24 20:00:50 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | test | i2uniq | 2018-05-24 20:00:50 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
14 rows in set (0.00 sec)
表字段含义。
从表中查询所得的数据中,我们可以看到:
PS:我们可以使用该表中的索引信息页数结合系统变量innodb_page_size的值来计算索引的数据大小,如下
root@localhost : mysql 08:31:14> SELECT SUM(stat_value) pages, index_name,
SUM(stat_value)*@@innodb_page_size size FROM mysql.innodb_index_stats WHERE
table_name='dept_emp' AND stat_name = 'size' GROUP BY index_name;
+-------+------------+----------+
| pages | index_name | size |
+-------+------------+----------+
| 737 | PRIMARY | 12075008 |
| 353 | dept_no | 5783552 |
| 353 | emp_no | 5783552 |
+-------+------------+----------+
3 rows in set (0.01 sec)
本期内容就介绍到这里,本期内容参考链接如下:
https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html
“
"翻过这座山,你就可以看到一片海!"。坚持阅读我们的"全方位认识 mysql 系统库"系列文章分享,你就可以系统地学完它。谢谢你的阅读,我们下期不见不散!
”
| 作者简介
《千金良方——MySQL性能优化金字塔法则》、《数据生态:MySQL复制技术与生产实践》作者之一。
熟悉MySQL体系结构,擅长数据库的整体调优,喜好专研开源技术,并热衷于开源技术的推广,在线上线下做过多次公开的数据库专题分享,发表过近100篇数据库相关的研究文章。
全文完。