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 系统表的时候，会自动更新非持久化统计信息。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2156364/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15498/viewspace-2156364/