mysql5.6 improved the statistics so that statistics for data tables using the InnoDB storage engine can be managed permanently (persistent). Starting with 5.6, indexes can also be accessed in the Innodb_index_stats tables and innodb_table_stats tables in the MySQL database.
Root@localhost: Mysql3316.sock the: .: + [MySQL]>Show tables like"%_stats ";+---------------------------+|Tables_in_mysql (%_stats)|+---------------------------+|Innodb_index_stats||Innodb_table_stats|+---------------------------+
When you create a table in mysql5.6, you can set the Stats_persistent option to control whether statistics are saved
(1) stats_persistent=0 #采用mysql5.6 The statistics of the previous mode management tables are not saved in the MySQL database Innodb_index_stats and innodb_table_stats.
Root@localhost: Mysql3316.sock the: .: the [MySQL]>Create TableTest.zhangshuo (IDint Primary Key, namevarchar( -)) Stats_persistent=0;Query OK,0Rows Affected (0.62Sec
(2) Stats_persistent=1 #将表统计信息保存到mysql数据库的innodb_index_stats和innodb_table_stats.
Root@localhost: Mysql3316.sock the: -: - [MySQL]>Create TableTest.zs (IDint Primary Key, namevarchar( -)) Stats_persistent=1; Query OK,0Rows Affected (0.33Sec
(3) Access to Innodb_table_stats table statistics in MySQL library, you can see only the ZS data table (created with the stats_persistent=1 option)
Root@localhost: Mysql3316.sock the: -: $ [MySQL]>Select * fromInnodb_table_statswheretable_nameinch('Zhangshuo','ZS');+---------------+------------+---------------------+--------+----------------------+-------------------------- +|database_name|table_name|Last_update|N_rows|Clustered_index_size|Sum_of_other_index_sizes|+---------------+------------+---------------------+--------+----------------------+------------------------- -+|Test|Zs| .- ,-Geneva the: -: on | 0 | 1 | 0 |+---------------+------------+---------------------+--------+----------------------+-------------------------- +1Rowinch Set(0.00Sec
(4) Of course you can change table statistics to persistent storage State (stats_persistent=1) using the ALTER TABLE command
mysql5.6 Statistical information