MySQL统计信息系列

最新推荐文章于 2024-05-29 22:50:15 发布

coco3600

最新推荐文章于 2024-05-29 22:50:15 发布

阅读量176

点赞数

文章标签：数据库面试

MySQL统计信息系列

Mysql的统计信息官方文档： https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

--统计信息可以持久化保存在数据库的表中

--持久化保存的条件：参数innodb_stats_persistent=on(默认为on) 或者建表时加上 STATS_PERSISTENT=1


mysql> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

--持久化保存的表为：mysql.innodb_table_stats 和 mysql.innodb_index_stats


mysql> desc mysql.innodb_table_stats;
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field                    | Type                | Null | Key | Default           | Extra                       |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+   
| database_name            | varchar(64)         | NO   | PRI | NULL              |                             |    --Database name
| table_name               | varchar(199)        | NO   | PRI | NULL              |                             |    --Table name, partition name, or subpartition name
| last_update              | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |    --A timestamp indicating the last time that InnoDB updated this row
| n_rows                   | bigint(20) unsigned | NO   |     | NULL              |                             |    --The number of rows in the table
| clustered_index_size     | bigint(20) unsigned | NO   |     | NULL              |                             |    --The size of the primary index, in pages
| sum_of_other_index_sizes | bigint(20) unsigned | NO   |     | NULL              |                             |    --The total size of other (non-primary) indexes, in pages
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.11 sec)
mysql>  desc mysql.innodb_index_stats ;
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field            | Type                | Null | Key | Default           | Extra                       |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name    | varchar(64)         | NO   | PRI | NULL              |                             |    --Database name
| table_name       | varchar(199)        | NO   | PRI | NULL              |                             |    --Table name, partition name, or subpartition name
| index_name       | varchar(64)         | NO   | PRI | NULL              |                             |    --Index name
| last_update      | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |    --A timestamp indicating the last time that InnoDB updated this row
| stat_name        | varchar(64)         | NO   | PRI | NULL              |                             |    --The name of the statistic, whose value is reported in the stat_value column
| stat_value       | bigint(20) unsigned | NO   |     | NULL              |                             |    --The value of the statistic that is named in stat_name column
| sample_size      | bigint(20) unsigned | YES  |     | NULL              |                             |    --The number of pages sampled for the estimate provided in the stat_value column
| stat_description | varchar(1024)       | NO   |     | NULL              |                             |    --Description of the statistic that is named in the stat_name column
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

--统计信息是否重新收集(10%行数据变化就自动收集)，设置innodb_stats_auto_recalc=on(默认为on)

--当数据变化超过10%后，会延迟几秒才收集，如果想立即收集统计信息可手动执行 ANALYZE TABLE

--当添加新的索引到已有的表中、或者在已有表中增加，删除列时索引的统计信息都会添加到mysql.innodb_index_stats表中，忽略innodb_stats_auto_recalc参数是否生效


mysql> show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

--收集统计信息时，随机抽取的索引页数，innodb_stats_persistent_sample_pages，默认20

--增加innodb_stats_persistent_sample_pages会使得收集时间变长，增加IO


mysql> show variables like 'innodb_stats_persistent_sample_pages';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent_sample_pages | 20    |
+--------------------------------------+-------+
1 row in set (0.01 sec)
---------------------

--建立测试表


mysql> CREATE TABLE test_stat (
    -> a INT, b INT, c INT, d INT, e INT, f INT,
    -> PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.33 sec)
 
mysql> insert into test_stat values (1,1,10,11,100,101),(1,2,10,11,200,102),(1,3,10,11,100,103),(1,4,10,12,200,104),(1,5,10,12,100,105);
Query OK, 5 rows affected (0.15 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from test_stat;
+---+---+------+------+------+------+
| a | b | c    | d    | e    | f    |
+---+---+------+------+------+------+
| 1 | 1 |   10 |   11 |  100 |  101 |
| 1 | 2 |   10 |   11 |  200 |  102 |
| 1 | 3 |   10 |   11 |  100 |  103 |
| 1 | 4 |   10 |   12 |  200 |  104 |
| 1 | 5 |   10 |   12 |  100 |  105 |
+---+---+------+------+------+------+
5 rows in set (0.03 sec)

--分析表


mysql> ANALYZE TABLE test_stat;
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| flydb.test_stat | analyze | status   | OK       |
+-----------------+---------+----------+----------+
1 row in set (0.03 sec)

--查看表统计信息


mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name like 'test_stat';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| flydb         | test_stat  | 2018-11-07 14:46:57 |      5 |                    1 |                        2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

--查看索引统计信息


mysql> SELECT index_name, stat_name, stat_value, stat_description
         FROM mysql.innodb_index_stats 
        WHERE table_name like 'test_stat';
+------------+--------------+------------+-----------------------------------+
| index_name | stat_name    | stat_value | stat_description                  |
+------------+--------------+------------+-----------------------------------+
| PRIMARY    | n_diff_pfx01 |          1 | a                                 |
| PRIMARY    | n_diff_pfx02 |          5 | a,b                               |
| PRIMARY    | n_leaf_pages |          1 | Number of leaf pages in the index |
| PRIMARY    | size         |          1 | Number of pages in the index      |
| i1         | n_diff_pfx01 |          1 | c                                 |
| i1         | n_diff_pfx02 |          2 | c,d                               |
| i1         | n_diff_pfx03 |          2 | c,d,a                             |
| i1         | n_diff_pfx04 |          5 | c,d,a,b                           |
| i1         | n_leaf_pages |          1 | Number of leaf pages in the index |
| i1         | size         |          1 | Number of pages in the index      |
| i2uniq     | n_diff_pfx01 |          2 | e                                 |
| i2uniq     | n_diff_pfx02 |          5 | e,f                               |
| i2uniq     | n_leaf_pages |          1 | Number of leaf pages in the index |
| i2uniq     | size         |          1 | Number of pages in the index      |
+------------+--------------+------------+-----------------------------------+
14 rows in set (0.02 sec)

--说明

The stat_name column shows the following types of statistics:

* size: Where stat_name=size, the stat_value column displays the total number of pages in the index.

* n_leaf_pages: Where stat_name=n_leaf_pages, the stat_value column displays the number of leaf pages in the index.

* n_diff_pfxNN: Where stat_name=n_diff_pfx01, the stat_value column displays the number of distinct values in the first column of the index. Where stat_name=n_diff_pfx02, the stat_value column displays the number of distinct values in the first two columns of the index, and so on. Additionally, where stat_name=n_diff_pfxNN, the stat_description column shows a comma separated list of the index columns that are counted.

--计算大小


mysql> SELECT SUM(stat_value) pages, index_name,
              SUM(stat_value)*@@innodb_page_size size
         FROM mysql.innodb_index_stats 
        WHERE table_name='test_stat'
          AND stat_name = 'size' 
        GROUP BY index_name;
+-------+------------+-------+
| pages | index_name | size  |
+-------+------------+-------+
|     1 | PRIMARY    | 16384 |
|     1 | i1         | 16384 |
|     1 | i2uniq     | 16384 |
+-------+------------+-------+
3 rows in set (0.13 sec)
---------------------

MySQL统计信息简介

MySQL执行SQL会经过SQL解析和查询优化的过程，解析器将SQL分解成数据结构并传递到后续步骤，查询优化器发现执行SQL查询的最佳方案、生成执行计划。查询优化器决定SQL如何执行，依赖于数据库的统计信息，下面我们介绍MySQL 5.7中innodb统计信息的相关内容。

MySQL统计信息的存储分为两种，非持久化和持久化统计信息。

一、非持久化统计信息

非持久化统计信息存储在内存里，如果数据库重启，统计信息将丢失。有两种方式可以设置为非持久化统计信息：

  1    全局变量，  
  INNODB_   STATS_PERSISTENT   =OFF  
  2 CREATE/ALTER   表的参数，  
  STATS_PERSISTENT=0  

非持久化统计信息在以下情况会被自动更新：

 1   执行  ANALYZE TABLE 
 2 innodb_stats_on_metadata=ON  情况下，执  SHOW TABLE STATUS, SHOW INDEX,   查询   INFORMATION_SCHEMA下的TABLES, STATISTICS 
 3   启用  --auto-rehash  功能情况下，使用  mysql client  登录  
 4   表第一次被打开  
 5   距上一次更新统计信息，表  1/16  的数据被修改  

非持久化统计信息的缺点显而易见，数据库重启后如果大量表开始更新统计信息，会对实例造成很大影响，所以目前都会使用持久化统计信息。

二、持久化统计信息

5.6.6开始，MySQL默认使用了持久化统计信息，即 INNODB_STATS_PERSISTENT=ON ，持久化统计信息保存在表 mysql.innodb_table_stats 和 mysql.innodb_index_stats 。

持久化统计信息在以下情况会被自动更新：

  1    INNODB_   STATS   _AUTO_RECALC=ON  
  情况下，表中   10%   的数据被修改  
  2    增加新的索引    

innodb_table_stats 是表的统计信息， innodb_index_stats 是索引的统计信息，各字段含义如下：

innodb_table_stats
database_name	数据库名
table_name	表名
last_update	统计信息最后一次更新时间
n_rows	表的行数
clustered_index_size	聚集索引的页的数量
sum_of_other_index_sizes	其他索引的页的数量

innodb_index_stats
database_name	数据库名
table_name	表名
index_name	索引名
last_update	统计信息最后一次更新时间
stat_name	统计信息名
stat_value	统计信息的值
sample_size	采样大小
stat_description	类型说明

为更好的理解 innodb_index_stats ，建一张测试表做说明：

CREATE TABLE t1 ( a INT, b INT, c INT, d INT, e INT, f INT,PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f)) ENGINE=INNODB;

写入数据如下：

0?wx_fmt=png

查看t1表的统计信息，需主要关注 stat_name 和 stat_value 字段

0?wx_fmt=png

stat_name=size 时： stat_value 表示索引的页的数量

stat_name=n_leaf_pages 时： stat_value 表示叶子节点的数量

stat_name=n_diff_pfxNN 时： stat_value 表示索引字段上唯一值的数量，此处做一下具体说明：

1、 n_diff_pfx01 表示索引第一列 distinct 之后的数量，如 PRIMARY 的a列，只有一个值1，所以 index_name='PRIMARY' and stat_name='n_diff_pfx01' 时， stat_value=1 。

2、 n_diff_pfx02 表示索引前两列 distinct 之后的数量，如 i2uniq 的 e,f 列，有4个值，所以 index_name='i2uniq' and stat_name='n_diff_pfx02' 时， stat_value=4 。

3、对于非唯一索引，会在原有列之后加上主键索引，如 index_name=’i1’ and stat_name=’n_diff_pfx03’ ，在原索引列c,d后加了主键列 a，(c,d,a) 的 distinct 结果为2。

了解了 stat_name 和 stat_value 的具体含义，就可以协助我们排查SQL执行时为什么没有使用合适的索引，例如某个索引 n_diff_pfxNN 的 stat_value 远小于实际值，查询优化器认为该索引选择度较差，就有可能导致使用错误的索引。

三、统计信息不准确的处理

我们查看执行计划，发现未使用正确的索引，如果是 innodb_index_stats 中统计信息差别较大引起，可通过以下方式处理：

1、手动更新统计信息，注意执行过程中会加读锁：

ANALYZE TABLE TABLE_NAME ;

2、如果更新后统计信息仍不准确，可考虑增加表采样的数据页，两种方式可以修改：

a) 全局变量 INNODB_STATS_PERSISTENT_SAMPLE_PAGES ，默认为20；

b) 单个表可以指定该表的采样：

ALTER TABLE TABLE_NAME STATS_SAMPLE_PAGES=40 ;

经测试，此处 STATS_SAMPLE_PAGES 的最大值是65535，超出会报错。

目前MySQL并没有提供直方图的功能，某些情况下（如数据分布不均）仅仅更新统计信息不一定能得到准确的执行计划，只能通过 index hint 的方式指定索引。新版本8.0会增加直方图功能，让我们期待MySQL越来越强大的功能吧！

About Me

  ........................................................................................................................  
  ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除  
  ● 本文在itpub（  http://blog.itpub.net/26736162  ）、博客园（  http://www.cnblogs.com/lhrbest  ）和个人weixin公众号（  xiaomaimiaolhr  ）上有同步更新  
  ● 本文itpub地址：  http://blog.itpub.net/26736162   
  ● 本文博客园地址：  http://www.cnblogs.com/lhrbest   
  ● 本文pdf版、个人简介及小麦苗云盘地址：  http://blog.itpub.net/26736162/viewspace-1624453/   
  ● 数据库笔试面试题库及解答：  http://blog.itpub.net/26736162/viewspace-2134706/   
  ● DBA宝典今日头条号地址：  http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826   
  ........................................................................................................................  
  ● QQ群号：  230161599  （满）  、618766405   
  ● weixin群：可加我weixin，我拉大家进群，非诚勿扰  
  ● 联系我请加QQ好友  （   646634621   ）  ，注明添加缘由  
  ● 于 2019-05-01 06:00 ~ 2019-05-30 24:00 在魔都完成  
  ● 最新修改时间：2019-05-01 06:00 ~ 2019-05-30 24:00  
  ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解  
  ● 版权所有，欢迎分享本文，转载请保留出处  
  ........................................................................................................................  
  ●  小麦苗的微店  ：  https://weidian.com/s/793741433?wfr=c&ifr=shopdetail   
  ●  小麦苗出版的数据库类丛书  ：  http://blog.itpub.net/26736162/viewspace-2142121/   
  ●  小麦苗OCP、OCM、高可用网络班  ：  http://blog.itpub.net/26736162/viewspace-2148098/   
  ●  小麦苗腾讯课堂主页  ：  https://lhr.ke.qq.com/   
  ........................................................................................................................  
 使用   weixin客户端   扫描下面的二维码来关注小麦苗的weixin公众号（  xiaomaimiaolhr  ）及QQ群（DBA宝典）、添加小麦苗weixin，  学习最实用的数据库技术。  
 
  ........................................................................................................................  