innodb_stats_persistent - xiaoboluo768/qianjinliangfang GitHub Wiki

• 控制是否将InnoDB索引统计信息持久化磁盘中。
  • 索引统计信息可能会频繁地重新计算统计数据，这可能导致查询执行计划频繁地变化。持久化统计信息的更新只会在执行analyze table语句时才会触发，这样，数据库重启时可以直接读取这个值（从mysql.innodb_index_stats和mysql.innodb_table_stats两个表中读取，这两个表就是统计信息的持久表）
  • 创建表时或修改表定义时，使用CREATE TABLE和ALTER TABLE语句的STATS_PERSISTENT子句来覆盖系统变量innodb_stats_persistent的设置并为各个表设置持久性统计信息，您也可以在创建表之前在全局级别启用innodb_stats_persistent
  • 全局变量，动态变量，布尔型，默认值为ON
• PS：以下给出索引统计信息和表统计信息表中记录的数据，和show table status、show index from tb_name查询的数据做对比，你就知道统计信息中到底记录的是什么东西了

[email protected] Sat Apr 15 11:47:24 2017 11:47:24 [(none)]>select * from mysql.innodb_index_stats limit 1\G;
*************************** 1. row ***************************
   database_name: employees
      table_name: departments
      index_name: PRIMARY
     last_update: 2016-04-04 20:29:30
       stat_name: n_diff_pfx01
      stat_value: 9
     sample_size: 1
stat_description: dept_no
1 row in set (0.00 sec)

ERROR: 
No query specified

[email protected] Sat Apr 15 11:47:30 2017 11:47:30 [(none)]>select * from mysql.innodb_table_stats limit 1\G;
*************************** 1. row ***************************
           database_name: employees
              table_name: departments
             last_update: 2016-04-04 20:29:30
                  n_rows: 9
    clustered_index_size: 1
sum_of_other_index_sizes: 1
1 row in set (0.00 sec)

ERROR: 
No query specified

[email protected] Sat Apr 15 11:47:36 2017 11:47:36 [(none)]>use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
[email protected] Sat Apr 15 11:50:40 2017 11:50:40 [employees]>show table status like 'departments'\G;
*************************** 1. row ***************************
           Name: departments
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9
 Avg_row_length: 1820
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-04-04 20:29:10
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

[email protected] Sat Apr 15 11:50:56 2017 11:50:56 [employees]>show index from departments;
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| departments |          0 | PRIMARY   |            1 | dept_no     | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| departments |          0 | dept_name |            1 | dept_name   | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

---

上一篇：innodb_stats_on_metadata | 下一篇：innodb_stats_persistent_sample_pages