Detailed explanation of MySQL persistent statistics

1. The significance of persistent statistical information:

Statistics are used to guide MySQL to generate execution plans. The accuracy of the execution plan directly affects the efficiency of SQL execution.

The previous statistics are gone, so when the SQL statement comes, MySQL will collect the statistics and then generate the execution of the SQL statement.

plan. If you can save the statistics when you shut down MySQL, you don't need to collect them again when you start it.

Helps improve efficiency.

Second, the accuracy of statistical information is equally important:

In the first section, we explained the significance of persistent statistics. Our assumption is that statistics are useful and accurate. If the statistics themselves

It is outdated. For example, if the statistics are calculated when there are only 100 rows in the table, the full table scan will often be less expensive.

Woolen cloth! Now the number of rows in the table has reached 1 million. Obviously, such outdated statistical information will cause performance disasters, so the timeliness of statistical information is also

are equally important. So when does MySQL automatically update statistics? By default, the table will be updated when 10% of the data has been modified.

3. MySQL's processing of statistical information:

MySQL has solutions for both of the above problems, and both can be solved through simple configuration.

1. Whether to persist statistical information in MySQL can be controlled by the innodb_stats_persistent parameter

2. Regarding the timeliness of statistical information, MySQL uses the innodb_stats_auto_recalc parameter to control whether to automatically update

3. For the accuracy of statistical information, MySQL controls updates through the innodb_stats_persistent_sample_pages parameter

The number of sample pages used for statistical information sampling.

4. Manually update statistics:

MySQL manually updates statistics using the analyze table statement

5. Check when the statistics of the table are updated:

MySQL records statistics in the two tables mysql.innodb_table_stats and mysql.innodb_index_stats.

mysql.innodb_table_stats records statistics by table

mysql> select * from innodb_table_stats;
+---------------+----------------------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+----------------------------+---------------------+--------+----------------------+--------------------------+
| fdb | auth_group | 2017-08-10 14:36:40 | 0 | 1 | 1 |
| fdb | auth_group_permissions | 2017-08-10 14:36:41 | 0 | 1 | 2 |
| fdb | auth_permission | 2017-08-10 14:36:41 | 30 | 1 | 1 |
| fdb | auth_user | 2017-08-10 14:36:41 | 0 | 1 | 1 |
| fdb | auth_user_groups | 2017-08-10 14:36:41 | 0 | 1 | 2 |
| fdb | auth_user_user_permissions | 2017-08-10 14:36:41 | 0 | 1 | 2 |
| fdb | cninfo_company | 2017-08-10 14:36:58 | 4996 | 161 | 6 |
| fdb | csindex_indexdetail | 2017-09-17 14:04:27 | 0 | 1 | 0 |
| fdb | csindex_indexoverview | 2017-09-01 12:44:18 | 11 | 1 | 0 |
| fdb | django_admin_log | 2017-08-10 14:36:47 | 0 | 1 | 2 |
| fdb | django_content_type | 2017-08-10 14:36:47 | 10 | 1 | 1 |
| fdb | django_migrations | 2017-09-04 14:04:09 | 37 | 1 | 0 |
| fdb | django_session | 2017-08-10 14:36:47 | 0 | 1 | 1 |
| fdb | glod_glodprice | 2017-08-10 14:36:48 | 2271 | 10 | 0 |
| fdb | pbc_moneysupply | 2017-08-10 14:37:08 | 78 | 1 | 0 |
| fdb | shibor_shiborrate | 2017-08-10 14:37:18 | 2711 | 14 | 0 |
| fdb | sse_marketoverview | 2017-08-15 16:06:12 | 0 | 1 | 0 |
| mysql | gtid_executed | 2017-09-06 11:02:14 | 2 | 1 | 0 |
| sys | sys_config | 2017-08-10 12:19:06 | 6 | 1 | 0 |
| tempdb | person | 2017-09-14 11:18:15 | 1 | 1 | 0 |
| tmp | t | 2017-08-15 11:06:18 | 2 | 1 | 0 |
+---------------+----------------------------+---------------------+--------+----------------------+--------------------------+
21 rows in set (0.00 sec)

The meaning of each column:

database_name The name of the database where the table is located

table_name table name

last_update The time of the last update

n_rows The number of rows in the table

clustered_index_size The size of the primary key

sum_of_other_index_sizes The size of all secondary indexes

6. Some experience in the analyze table process:

If we use the explan statement to view the SQL execution plan, we find that the plan is not accurate, which is mostly caused by outdated statistical information.

Sometimes you need to execute analyze table to regenerate the execution plan; sometimes you may find that regenerating the execution plan is useless.

SQL still does not work correctly. The most likely reason is that the number of sample pages when generating the execution plan is too low.

The value of this parameter should not be too large, otherwise it will take a long time to complete the execution of the analyze table statement.

VII. Additional Notes:

The mysql mentioned above actually refers to the Innodb engine.

The above is a detailed explanation of MySQL persistent statistics. For more information about MySQL persistent statistics, please pay attention to other related articles on 123WORDPRESS.COM!