MySQL5.7統計資訊更新的相關引數解釋和測試

MySQL版本:5.7.21


統計資訊相關引數如下：
mysql> show global variables like '%stats%';
+--------------------------------------+---------------+
| Variable_name                        | Value         |
+--------------------------------------+---------------+
| innodb_stats_auto_recalc             | ON            |
| innodb_stats_include_delete_marked   | OFF           |
| innodb_stats_method                  | nulls_equal   |
| innodb_stats_on_metadata             | ON            |
| innodb_stats_persistent              | OFF           |
| innodb_stats_persistent_sample_pages | 20            |
| innodb_stats_sample_pages            | 8             |
| innodb_stats_transient_sample_pages  | 8             |
| myisam_stats_method                  | nulls_unequal |
+--------------------------------------+---------------+
9 rows in set (0.01 sec)


引數解釋:
innodb_stats_persistent
非持久化統計資訊開關,該選項設定為ON時候，統計資訊會持久化儲存到磁碟中，而不是存在在記憶體中，
如果是非持久化儲存的（存在記憶體中），相應的統計資訊會隨著伺服器的關閉而丟失,MySQL 5.7中預設為on。


innodb_stats_auto_recalc
是否自動觸發更新持久化儲存的表的統計資訊，僅影響持久化儲存的統計資訊的表，閾值是變化的資料超過錶行數的10%。
當一個表索引統計資訊是持久化儲存的(innodb_stats_auto_recalc設定為ON),並且表中資料變化了超過10%，就會自動更新統計資訊，否則不會自動更新。


innodb_stats_persistent_sample_pages
持久化更新統計資訊時候索引頁的取樣頁數,預設值是20


innodb_stats_transient_sample_pages
非持久化更新統計資訊時候索引頁的取樣頁數,預設值是8


innodb_stats_on_metadata
是否自動更新統計資訊，在統計資訊配置為非持久化的時候生效(innodb_stats_persistent=off),
當設定為ON的時候，InnoDB在執show table status 或者訪問INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICS 系統表的時候，會更新非持久化統計資訊（類似於ANALYZE TABLE命令）。


實驗如下：
持久化統計資訊實驗:
innodb_stats_on_metadata=on
innodb_stats_persistent=on
innodb_stats_auto_recalc=on

mysql> use test
Database changed

mysql> set global innodb_stats_persistent=on;
Query OK, 0 rows affected (0.00 sec)


mysql> set global innodb_stats_on_metadata=on;
Query OK, 0 rows affected (0.00 sec)


mysql> set global innodb_stats_auto_recalc=on;
Query OK, 0 rows affected (0.00 sec)




mysql> show table status like 'chenfeng' \G
*************************** 1. row ***************************
           Name: chenfeng
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-06-20 08:39:11
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)


建索引:
mysql> alter table chenfeng add index idx_simhash(simhash);
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table chenfeng add index idx_contextLength(contextLength);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0


檢視table status:
mysql> show table status like 'chenfeng' \G
*************************** 1. row ***************************
           Name: chenfeng
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-06-20 09:02:16
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)


發現Index_length=0，統計資訊沒有自動更新，因為開啟了持久化統計資訊開關innodb_stats_persistent=on，因此必須當一個表中的資料變化了超過10%，才會自動更新統計資訊，否則不會自動更新。


非持久化統計資訊實驗:
mysql> set global innodb_stats_persistent=off;
Query OK, 0 rows affected (0.00 sec)


mysql> set global innodb_stats_on_metadata=on;
Query OK, 0 rows affected (0.00 sec)


加索引:
mysql> alter table chenfeng add index idx_isPush(isPush);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table chenfeng add index idx_noise(noise);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0


檢視table status:
mysql> show table status like 'chenfeng' \G
*************************** 1. row ***************************
           Name: chenfeng
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 65536
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-06-20 09:08:13
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)


發現Index_length有了值，表中的資料沒有任何變化時也自動更新了統計資訊,因為設定了innodb_stats_on_metadata=on和innodb_stats_persistent=off,innodb_stats_on_metadata當設定為ON的時候，InnoDB在執show table status 或者訪問INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICS 系統表的時候，會自動更新非持久化統計資訊。