mysql 统计信息记录
相关参数
计信息的持久化功能
系统级---innodb_stats_persistent = ON
表级- STATS_PERSISTENT = 1
统计信息的持久化优化自动计算
系统级--innodb_stats_auto_recalc
表级--STATS_AUTO_RECALC
持久化统计信息时包含被打上删除标记的记录
系统级--innodb_stats_include_delete_marked
统计信息采样
innodb_stats_persistent_sample_pages 默认为20
mysql> show global variables like '%innodb_stats%'; +--------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------+-------------+ | innodb_stats_auto_recalc | ON | | innodb_stats_include_delete_marked | OFF | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_sample_pages | 8 | | innodb_stats_transient_sample_pages | 8 | +--------------------------------------+-------------+ 8 rows in set (0.01 sec)
统计信息的持久化优化手动计算
ANALYZE TABLE
FLUSH TABLE tbl_name ---重新加载更新过后的统计信息
当某表添加新的索引时,无论系统参数innodb_stats_auto_recalc的值如何,都会触发重新计算索引统计信息(不是表及其数据相关统计信息)并将其添加到innodb_index_stats表中。
要想在添加索引时数据相关的统计信息同时更新到mysql.innodb_table_stats表中,需要启用系统变量innodb_stats_auto_recalc或者修改表的innodb_stats_auto_recalc建表选项,或者对表执行ANALYZE TABLE语句。
2.相关的表
innodb_table_stats
-
database_name:数据库名称。
-
table_name:表名、分区名或子分区名称。
-
last_update:表示InnoDB上次更新此统计信息行的时间戳。
-
n_rows:表中的估算数据记录行数。
-
clustered_index_size:主键索引的大小,以页为单位的估算数值。
-
sum_of_other_index_sizes:其他(非主键)索引的总大小,以页为单位的估算数
innodb_index_stats
-
database_name:数据库名称。
-
table_name:表名、分区表名、子分区表名称。
-
index_name:索引名称。
-
last_update:表示InnoDB上次更新此统计信息行的时间戳。
-
stat_name:统计信息名称,其对应的统计信息值保存在stat_value列。
-
stat_value:保存统计信息名称stat_name列对应的统计信息值。
-
sample_size:stat_value列中提供的统计信息估计值的采样页数。
-
stat_description:统计信息名称stat_name列中指定的统计信息的说明信息。
从表中查询所得的数据中,我们可以看到:
-
stat_name列一种有如下几种统计值。
* size:当stat_name为size值时,stat_value列值表示索引中的总页数量。
* n_leaf_pages:当stat_name为n_leaf_pages值时,stat_value列值显示索引叶子页的数量。
* n_diff_pfxNN:NN代表数字(例如:01,02等),当stat_name为n_diff_pfxNN时,stat_value列值显示索引的first column(即索引的最前索引列,从索引定义顺序的第一个列开始)列的唯一值数量,例如:当NN为01时,stat_value列值就表示索引的第一个列的唯一值数量,当NN为02时,stat_value列值就表示索引的第一和第二个列的组合唯一值数量,以此类推。此外,在stat_name = n_diff_pfxNN的情况下,stat_description列显示一个以逗号分隔的计算索引统计信息列的列表。
-
从index_name为PRIMARY数据行的stat_description列的描述信息"a,b"中,我们可以看出 ,主键索引的统计信息列实际上就等于定义的索引列数量。
-
从index_name为i2uniq数据行的stat_description列的描述信息"e,f"中,我们可以看出 ,唯一索引的统计信息列实际上就等于定义的索引列数量。
-
从index_name为i1数据行的stat_description列的描述信息 "c,d,a,b"中,我们可以看出,普通索引(非唯一的辅助索引)的统计信息列实际上除了定义的索引列之外,还包含了主键列。即对于非唯一索引在该表中记录的统计信息,InnoDB会附加主键列。
-
注意:MySQL 5.7中系统变量innodb_stats_persistent_sample_pages定义的持久化统计信息采样页为20,这里示例中的sample_size列值为1是因为表中数据量太小,在一个页中已经足够存放,所以实际采样也只使用了1个页,如果数据量够多,则这里显示的值就会为innodb_stats_persistent_sample_pages系统变量指定的值。
索引大小的计算
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;