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時候會讓你的語句走到索引，但是效率并不一定是好的。