MySQL统计信息相关表介绍

MySQL中提供了两个表记录统计信息的相关内容,分别是
innodb_table_stats与innodb_index_stats。

innodb_table_stats

这个表里面的内容还是比较好理解

[root@shadow:/home/mysql/qdata_for_mysql 5.7.18-log_Instance1 root@localhost:(none) 14:03:33]>select * from mysql.innodb_table_stats where table_name = 'dhytest';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | dhytest    | 2017-07-13 14:03:32 |     16 |                    1 |                        1 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

重要的列:

last_update 就是最后一次收集统计信息的时间
clustered_index_size 聚集索引的page数量
sum_of_other_index_sizes 非聚集索引的page数量

通过这些信息我们可以算出聚集索引的大小:

[root@shadow:/home/mysql/qdata_for_mysql 5.7.18-log_Instance1 root@localhost:mysql 15:53:31]>select (clustered_index_size*@@innodb_page_size)/1024/1024 as size from mysql.innodb_table_stats where table_name = 'sbtest1';
+--------------+
| size         |
+--------------+
| 107.62500000 |
+--------------+
1 row in set (0.00 sec)

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;

[root@shadow:/home/mysql/qdata_for_mysql 5.7.18-log_Instance1 root@localhost:test 21:17:07]>select * from t1;
+---+---+------+------+------+------+
| 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.00 sec)

[root@shadow:/home/mysql/qdata_for_mysql 5.7.18-log_Instance1 root@localhost:test 21:16:41]>select index_name,stat_name,stat_value,stat_description from mysql.innodb_index_stats where table_name = 't1';
+------------+--------------+------------+-----------------------------------+
| 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.00 sec)

我们主要关注的的列:

stat_value: 显示统计值的大小
stat_description:类型的描述
stat_name:此列显示统计的类型 , 会出现下面这些:
- size:此时stat_value显示索引的page数量
- n_leaf_pages:此时stat_value显示叶子节点的数量
- n_diff_pfxNN:显示索引字段上唯一值的忽略，这里需要特殊说明:
  - 主键索引与唯一索引例如上面结果中index_name = PRIMARY时:
    - index_name = PRIMARY 且 stat_name = n_diff_pfx01 则stat_value代表主键索引中第一列distinct之后的数量，从上面的例子我们看到stat_value是1，因为a这一列的值都是(1)
    - index_name = PRIMARY 且 stat_name = n_diff_pfx02 则stat_value代表主键索引中第一列和第二列distinct之后的数量，从上面的例子我们看到stat_value是5，因为a,b两列存在的值是(1,1)(1,2)(1,3)(1,4)(1,5)
    - stat_description中我们可以看到是那几个列的信息
    - n_diff_pfxNN 以此类推
  - 非唯一索引不同的地方是在原有的列之后会添加上主键索引，这样说可能比较难理解，针对上面查询出来的记过下面详细说明下:
    - 根据表结构定义我们知道i1是一个非唯一索引,是由(c,d)两个列组成的。我们根据上面的结果可以看到除了n_diff_pfx01,n_diff_pfx02又多出来了n_diff_pfx03,n_diff_pfx04 ,通过stat_description我们可以看到n_diff_pfx03,n_diff_pfx04是在原有的(c,d)两列上又多出了 (c,d,a) (c,d,a,b) 这里就是将主键索引添加到了这里。
    - 例如 n_diff_pfx03 的stat_value是2 代表的就是在原有的非唯一索引上添加了主键索引的第一列(a), 这个时候distinct之后的值是2 所存在的值就是: (10,11,1) (10,12,1)

通过这个表我们可以查看索引选择性如何，并且可以看到组合索引中每一列选择性如何，还可以计算索引的大小:

SELECT

       stat_value pages,
       index_name,
       (stat_value * @@innodb_page_size)/1024/1024 size
FROM
       mysql.innodb_index_stats
WHERE
           table_name = 'sbtest1'
       AND database_name = 'sbtest'
       AND stat_description = 'Number of pages in the index'
       AND stat_name = 'size'
GROUP BY
       index_name;

如果是分区表则使用下面的语句:

SELECT
       stat_value pages,
       index_name,
       (sum(stat_value) * @@innodb_page_size)/1024/1024  size
FROM
       mysql.innodb_index_stats
WHERE
           table_name like 't#P%'
       AND database_name = 'test'
       AND stat_description = 'Number of pages in the index'
       AND stat_name = 'size'
GROUP BY
       index_name;