How to collect statistics in MySQL
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.
