MySQL中的統計資訊相關引數介紹

統計資訊的作用

上週同事在客戶現場遇到了由於統計資訊的原因，導致應用資料遷移時間過慢，整個遷移差點失敗。關鍵時刻同事發現測試環境與生產環境SQL語句執行計劃不一致，立刻收集統計資訊才保證遷移得以正常完成。 
統計資訊對於SQL的執行時間有重要的影響，統計資訊的不準確會導致SQL的執行計劃不準確，從而致使SQL執行時間變慢，Oracle DBA非常瞭解統計資訊的收集規則，同樣在MySQL中也有相關的引數去控制統計資訊。

相關引數

innodb_stats_auto_recalc

控制innodb是否自動收集統計資訊，預設是開啟的。當表中資料變化超過%10時候，就會重新計算統計資訊。引數的生效依賴於建表時指定innodb_stats_persistent是開啟的或CREATE TABLE , ALTER TABLE 時指定STATS_PERSISTENT=1取樣page的個數透過引數innodb_stats_persistent_sample_pages來控制。

• 測試驗證

建立一張測試表，並在表上建立一個索引:

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

透過mysql.innodb_index_stats可以檢視索引最後收集統計資訊的時間,這裡的聚集索引我們刪除先不用去看，只看自己建立的二級索引

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

我們手工往表中插入資料，讓資料的變化超過%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時間發生了變化

	

		[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

控制是否將統計資訊持久到磁碟當中，設定此引數之後我們就不需要實時去收集統計資訊了，因為實時收集統計資訊在高併發下可能會造成一定的效能上影響，並且會導致執行計劃有所不同。建議是將此引數開啟，將innodb_stats_auto_recalc引數進行關閉。

innodb_stats_persistent_sample_pages

控制收集統計資訊時取樣的page數量，預設是20。收集的page數量越多，每次收集統計資訊的實際則越長，但是統計資訊也相對比較準確。

我們可以在建立表的時候對不同的表指定不同的page數量、是否將統計資訊持久化到磁碟上、是否自動收集統計資訊

	

		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

此引數在5.6.5版本之前是預設開啟的，設定此引數後當我們執行show index 或者 show table status 或者訪問INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS表時就會收集統計資訊，但是這樣可能會導致執行計劃改變。 

在以前當表中記錄變化超過1/16就會收集統計資訊，但是現在如果設定了innodb_stats_persistent就不會有這樣的說法了。

innodb_stats_include_delete_marked

5.6.35版本中新增的引數，就是在未提交的事務中如果我們刪除了記錄，收集統計資訊的時候是排查這些刪除了的記錄的。這樣就可能導致統計資訊並不是很準確，設定此引數之後就是收集統計資訊的時候包含未提交事務中被標記為已刪除的資料。

innodb_stats_method

控制統計資訊針對索引中NULL值的演算法 

當設定為nulls_equal 所有的NULL值都視為一個value group 
當設定為nulls_unequal每一個NULL值被視為一個value group 
設定為nulls_ignored時 NULL值被忽略 

這個引數同事彭許生做過一些測試發現nulls_equal和nulls_unequal沒有發現show index中的cardinality有不同的地方，但是如果設定為nulls_ignored的時候會有所不同。

• 測 試

表結構資料

設定為nulls_ignored

設定為nulls_unequal

設定為nulls_equal

推薦配置

• innodb_stats_method 統計資訊的自動收集在高併發情況下可能會帶來效能的抖動，建議將此引數關閉。 

• innodb_stats_persistent 建議開啟此引數將統計資訊持久化到磁碟上 。

• innodb_stats_include_delete_marked建議設定開啟，這樣可以針對未提交事務中刪除的資料也收集統計資訊 。

• innodb_stats_method經過測試和mos檢視到的按預設配置就可以，當然如果設定nulls_ignored時候會讓你的語句走到索引，但是效率並不一定是好的。