MySQL中的統(tǒng)計信息相關(guān)參數(shù)介紹

統(tǒng)計信息的作用

上周同事在客戶現(xiàn)場遇到了由于統(tǒng)計信息的原因，導(dǎo)致應(yīng)用數(shù)據(jù)遷移時間過慢，整個遷移差點失敗。關(guān)鍵時刻同事發(fā)現(xiàn)測試環(huán)境與生產(chǎn)環(huán)境SQL語句執(zhí)行計劃不一致，立刻收集統(tǒng)計信息才保證遷移得以正常完成。 
統(tǒng)計信息對于SQL的執(zhí)行時間有重要的影響，統(tǒng)計信息的不準(zhǔn)確會導(dǎo)致SQL的執(zhí)行計劃不準(zhǔn)確，從而致使SQL執(zhí)行時間變慢，Oracle DBA非常了解統(tǒng)計信息的收集規(guī)則，同樣在MySQL中也有相關(guān)的參數(shù)去控制統(tǒng)計信息。

相關(guān)參數(shù)

innodb_stats_auto_recalc

控制innodb是否自動收集統(tǒng)計信息，默認是打開的。當(dāng)表中數(shù)據(jù)變化超過%10時候，就會重新計算統(tǒng)計信息。參數(shù)的生效依賴于建表時指定innodb_stats_persistent是打開的或CREATE TABLE , ALTER TABLE 時指定STATS_PERSISTENT=1采樣page的個數(shù)通過參數(shù)innodb_stats_persistent_sample_pages來控制。

• 測試驗證

創(chuàng)建一張測試表，并在表上創(chuàng)建一個索引:

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

通過mysql.innodb_index_stats可以查看索引最后收集統(tǒng)計信息的時間,這里的聚集索引我們刪除先不用去看，只看自己創(chuàng)建的二級索引

[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) 

我們手工往表中插入數(shù)據(jù)，讓數(shù)據(jù)的變化超過%10

 [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.01 sec)
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 

這時我們在查看下mysql.innodb_index_stats表,last_update時間發(fā)生了變化

	

		[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

控制是否將統(tǒng)計信息持久到磁盤當(dāng)中，設(shè)置此參數(shù)之后我們就不需要實時去收集統(tǒng)計信息了，因為實時收集統(tǒng)計信息在高并發(fā)下可能會造成一定的性能上影響，并且會導(dǎo)致執(zhí)行計劃有所不同。建議是將此參數(shù)打開，將innodb_stats_auto_recalc參數(shù)進行關(guān)閉。

innodb_stats_persistent_sample_pages

控制收集統(tǒng)計信息時采樣的page數(shù)量，默認是20。收集的page數(shù)量越多，每次收集統(tǒng)計信息的實際則越長，但是統(tǒng)計信息也相對比較準(zhǔn)確。

我們可以在創(chuàng)建表的時候?qū)Σ煌谋碇付ú煌膒age數(shù)量、是否將統(tǒng)計信息持久化到磁盤上、是否自動收集統(tǒng)計信息

	

		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

此參數(shù)在5.6.5版本之前是默認開啟的，設(shè)置此參數(shù)后當(dāng)我們執(zhí)行show index 或者 show table status 或者訪問INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS表時就會收集統(tǒng)計信息，但是這樣可能會導(dǎo)致執(zhí)行計劃改變。 

在以前當(dāng)表中記錄變化超過1/16就會收集統(tǒng)計信息，但是現(xiàn)在如果設(shè)置了innodb_stats_persistent就不會有這樣的說法了。

innodb_stats_include_delete_marked

5.6.35版本中新增的參數(shù)，就是在未提交的事務(wù)中如果我們刪除了記錄，收集統(tǒng)計信息的時候是排查這些刪除了的記錄的。這樣就可能導(dǎo)致統(tǒng)計信息并不是很準(zhǔn)確，設(shè)置此參數(shù)之后就是收集統(tǒng)計信息的時候包含未提交事務(wù)中被標(biāo)記為已刪除的數(shù)據(jù)。

innodb_stats_method

控制統(tǒng)計信息針對索引中NULL值的算法 

當(dāng)設(shè)置為nulls_equal 所有的NULL值都視為一個value group 
當(dāng)設(shè)置為nulls_unequal每一個NULL值被視為一個value group 
設(shè)置為nulls_ignored時 NULL值被忽略 

這個參數(shù)同事彭許生做過一些測試發(fā)現(xiàn)nulls_equal和nulls_unequal沒有發(fā)現(xiàn)show index中的cardinality有不同的地方，但是如果設(shè)置為nulls_ignored的時候會有所不同。

• 測 試

表結(jié)構(gòu)數(shù)據(jù)

設(shè)置為nulls_ignored

設(shè)置為nulls_unequal

設(shè)置為nulls_equal

推薦配置

• innodb_stats_method 統(tǒng)計信息的自動收集在高并發(fā)情況下可能會帶來性能的抖動，建議將此參數(shù)關(guān)閉。 

• innodb_stats_persistent 建議打開此參數(shù)將統(tǒng)計信息持久化到磁盤上 。

• innodb_stats_include_delete_marked建議設(shè)置開啟，這樣可以針對未提交事務(wù)中刪除的數(shù)據(jù)也收集統(tǒng)計信息 。

• innodb_stats_method經(jīng)過測試和mos查看到的按默認配置就可以，當(dāng)然如果設(shè)置nulls_ignored時候會讓你的語句走到索引，但是效率并不一定是好的。