Shulou(Shulou.com)05/31 Report--

MySQL how to collect statistical information, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

I. Manual

Execute Analyze table

Both the innodb and myisam storage engines can collect table statistics by performing "Analyze table tablename". Do not easily perform this operation unless the execution plan is not accurate, which can affect the performance of the table if it is a large table.

Second, automatic trigger

The following behavior automatically triggers the collection of statistics

1. When opening the table for the first time

two。 When the table modifies more than 1 stroke 6 or 2 billion rows

3. When a new record is inserted

4. When executing show index from tablename or executing show table or querying information_schema.tables\ statistics

3. Enable parameter innodb_stats_on_metadata

Accessing the following table when the parameter innodb_stats_on_metadata is enabled will also trigger the collection of statistics.

Statistics for the innodb table can be collected automatically when accessing the following tables

Information_schema.TABLES

Information_schema.STATISTICS

Information_schema.PARTITIONS

Information_schema.KEY_COLUMN_USAGE

Information_schema.TABLE_CONSTRAINTS

Information_schema.REFERENTIAL_CONSTRAINTS

Information_schema.table_constraints

Parameter description:

Innodb_stats_sample_pages: the number of pages sampled each time statistics are collected. Default is 20.

Innodb_stats_persistent: the default on saves the statistics generated by analyze table to disk until the next analyze table, which avoids dynamic updates of statistics, ensures the stability of the execution plan, and saves resources for collecting statistics for large tables.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.