第14章 mysql系统库之统计信息表

14.1　统计信息概述

• 1．如何配置统计信息持久化优化

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,  		# 启用InnoDB表的持久化统计信息功能
  STATS_AUTO_RECALC=1, 		# 启用自动重新计算InnoDB表的持久化统计信息功能
  STATS_SAMPLE_PAGES=25;  	# 设置估算索引列的基数和其他统计数据时要抽样的索引页数为25

• 5．如何配置在持久化统计信息的计算中包含删除标记的记录

mysql> CREATE TABLE test ( a INT, b INT, c INT, d INT, e INT, f INT, PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f) ) ENGINE=INNODB;   
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values(1,1,1,1,1,1),(2,2,2,2,2,2), (3,3,3,3,3,3),(4,4,4,4,4,4), (5,5,4,4,5,5),(5,6,6,6,6,6);     
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from test;
......
6 rows in set (0.00 sec)

14.2.1　innodb_table_stats

mysql> use mysql
Database changed
mysql> select * from innodb_table_stats where table_name= 'test'\G
......
1 row in set (0.00 sec)

14.2.2　innodb_index_stats

# 注：以下示例中的test表显式定义了三个索引，主键索引(a,b)、唯一索引i2uniq(e,f)和普通索引i1(c,d)
mysql> select * from innodb_index_stats where table_name='test';
......
14 rows in set (0.00 sec)

mysql> 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' AND database_name = 'test' GROUP BY index_name;                         
......
3 rows in set (0.01 sec)