Shulou(Shulou.com)06/01 Report--

The role of statistical information

Last week, colleagues encountered at the customer site due to statistical information, resulting in the application data migration time is too slow, the whole migration almost failed. At the critical moment, colleagues found that the SQL statement execution plan of the test environment was inconsistent with that of the production environment, and collected statistics immediately to ensure that the migration could be completed normally.

Statistical information has an important impact on the execution time of SQL. Inaccurate statistical information will lead to inaccurate execution plan of SQL, which will slow down the execution time of SQL. Oracle DBA knows the collection rules of statistical information very well, and there are also relevant parameters in MySQL to control statistical information.

Related parameters

Innodb_stats_auto_recalc

Controls whether innodb automatically collects statistics, which is turned on by default. When the data in the table changes more than once, the statistics are recalculated. The validity of the parameter depends on whether the innodb_stats_persistent is turned on or CREATE TABLE is specified when the table is created, and the number of STATS_PERSISTENT=1 sampling page specified when ALTER TABLE is controlled by the parameter innodb_stats_persistent_sample_pages.

Test verification

Create a test table and create an index on the table:

Create table dhytest (id int) STATS_PERSISTENT=1; create index idx_id on dhytest (id)

Through mysql.innodb_index_stats, you can check the time when the index last collected statistics. We don't have to look at the clustered index here, just look at the secondary index created by ourselves.

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:37:43] > select * from mysql.innodb_index_stats where database_name = 'test' +- -- + | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +- -- +-+ | test | dhytest | idx_id | | 2017-07-10 22:36:06 | n_diff_pfx01 | 0 | 1 | id | | test | dhytest | idx_id | 2017-07-10 22:36:06 | n_diff_pfx02 | 0 | 1 | id | DB_ROW_ID | | test | dhytest | idx_id | 2017-07-10 22:36:06 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | dhytest | idx_id | 2017-07-10 22:36:06 | size | 1 | NULL | Number of pages in the index | | +-+- -+ 7 rows in set (0.00 sec)

We manually insert data into the table to make the data change more than

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:37:56] > insert into dhytest values (10); Query OK, 1 row affected (0.00 sec) [root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:17] > insert into dhytest select * from dhytest Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 [root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:28] > insert into dhytest select * from dhytest;Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 [root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:31] > insert into dhytest select * from dhytest Query OK, 4 rows affected (0.01sec) Records: 4 Duplicates: 0 Warnings: 0 [root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:34] > insert into dhytest select * from dhytest;Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 [root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:35] > insert into dhytest select * from dhytest Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0

At this time, we are looking at the mysql.innodb_index_ stats table. The last_update time has changed.

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:36] > select * from mysql.innodb_index_stats where database_name = 'test' +- -- + | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +- -- +-+ | test | dhytest | idx_id | | 2017-07-10 22:38:28 | n_diff_pfx01 | 1 | 1 | id | | test | dhytest | idx_id | 2017-07-10 22:38:28 | n_diff_pfx02 | 2 | 1 | id | DB_ROW_ID | | test | dhytest | idx_id | 2017-07-10 22:38:28 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | dhytest | idx_id | 2017-07-10 22:38:28 | size | 1 | NULL | Number of pages in the index | | +-+- -+ 7 rows in set (0.00 sec)

Innodb_stats_persistent

Control whether statistics are persisted to disk. After setting this parameter, we do not need to collect statistics in real time, because real-time collection of statistics may have a certain performance impact under high concurrency and lead to different execution plans. It is recommended that this parameter be turned on and the innodb_stats_auto_recalc parameter turned off.

Innodb_stats_persistent_sample_pages

Controls the number of page sampled when collecting statistics, which defaults to 20. The more page is collected, the longer the statistics are actually collected each time, but the statistics are also relatively accurate.

We can specify different page numbers for different tables when creating tables, whether to persist statistics to disk, and whether to collect statistics automatically.

CREATE TABLE `t1` (`id` int (8) NOT NULL auto_increment, `data` varchar (255), `date` datetime,PRIMARY KEY (`id`), INDEX `DATE_ IX` (`date`) ENGINE=InnoDB, STATS_PERSISTENT=1, STATS_AUTO_RECALC=1, STATS_SAMPLE_PAGES=25

Innodb_stats_on_metadata

This parameter is enabled by default before version 5.6.5. After setting this parameter, statistics will be collected when we execute show index or show table status or visit the INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS table, but this may cause a change in the execution plan.

In the past, statistics were collected when a change was recorded in the table greater than 1ax 16, but now this would not be the case if innodb_stats_persistent was set.

Innodb_stats_include_delete_marked

The new parameter in version 5.6.35 is that if we delete records in uncommitted transactions, we troubleshoot those deleted records when collecting statistics. This may result in inaccurate statistics, which are collected when you set this parameter to include data marked as deleted in uncommitted transactions.

Innodb_stats_method

An algorithm for controlling statistics for null values in the index

When set to nulls_equal, all null values are treated as a value group

When set to nulls_unequal, each null value is treated as a value group

The NULL value is ignored when set to nulls_ignored

This parameter colleague Peng Xusheng has done some tests and found that nulls_equal and nulls_unequal do not find any difference in cardinality in show index, but it will be different if it is set to nulls_ignored.

Test

Table structure data

Set to nulls_ignored

Set to nulls_unequal

Set to nulls_equal

Recommended configuration

Automatic collection of innodb_stats_method statistics may cause performance jitter in high concurrency situations. It is recommended that this parameter be turned off.

Innodb_stats_persistent recommends that you turn on this parameter to persist statistics to disk.

Innodb_stats_include_delete_marked recommends that you set it on so that statistics can also be collected for data deleted in uncommitted transactions.

Innodb_stats_method has been tested and mos can see the default configuration, of course, if you set nulls_ignored will let your statements go to the index, but the efficiency is not necessarily good.

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.