MySQL Statistical information

Introduction

The purpose of database maintenance statistics is to optimize the performance of the optimization, first of all the statistics are based on the index, if the table is not indexed then the table has no statistics.

How the system collects statistical information

First, manual

1. Execute Analyze Table

The InnoDB and Mysiam storage engines can collect the statistics of the table by executing "Analyze table tablename", unless the execution plan is inaccurate, and do not do so easily, if it is a large table, the operation will affect the performance of the table.

Second, automatic trigger

The following behavior automatically triggers the collection of statistical information

1. The first time you open a table

2. When the table modifies more than 1/6 or 2 billion rows

3. When a new record is plugged in

4. When you execute show index from tablename or execute Show table, query Information_schema.tables\statistics

Three, open parameter Innodb_stats_on_metadata

Access to the following table after the parameter innodb_stats_on_metadata is turned on also triggers the collection of statistics

Statistics for InnoDB tables are automatically collected when you access the following tables

Information_schema. Tablesinformation_schema. Statisticsinformation_schema. Partitionsinformation_schema. Key_column_usageinformation_schema. Table_constraintsinformation_schema. Referential_constraintsinformation_schema.table_constraints

Parameter description:

innodb_stats_sample_pages: the number of pages sampled each time the statistics are collected , by default

Innodb_stats_persistent: By default, the statistics generated by the Analyze table are saved on disk until the next analyze table , which avoids the dynamic update of statistics. The stability of the implementation plan was ensured, and the resources needed to collect statistics were also saved for the large scale;

Summary

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

MySQL Statistical information