mysql5.6 Statistical information

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.