mysql5.6 Statistical information

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