如何计算指定的InnoDB索引大小

2018-01-25 4389

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介： 前言通常情况下，获取InnoDB索引的大小通常的方法是show table status，但是如果想获取指定的索引大小呢？通常情况下我们想看索引大小的话，用的是 show table status like ""\G 例1： mysql> show create table sbtest1\G *************************** 1.

前言

通常情况下，获取InnoDB索引的大小通常的方法是show table status，但是如果想获取指定的索引大小呢？

通常情况下我们想看索引大小的话，用的是

show table status like ""\G

例1：

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(500) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `is_used` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`),
  KEY `idx_is_used` (`is_used`),
  KEY `idx_gmt_create` (`gmt_create`)
) ENGINE=InnoDB AUTO_INCREMENT=69313841 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show table status like "sbtest1"\G
*************************** 1. row ***************************
           Name: sbtest1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 25926320
 Avg_row_length: 279
    Data_length: 7254032384
Max_data_length: 0
   Index_length: 1293697024
      Data_free: 3145728
 Auto_increment: 69313841
    Create_time: 2018-01-19 14:53:11
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> show create table sbtest2\G
*************************** 1. row ***************************
       Table: sbtest2
Create Table: CREATE TABLE `sbtest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` varchar(500) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `is_used` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=200000001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show table status like "sbtest2"\G
*************************** 1. row ***************************
           Name: sbtest2
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 197101536
 Avg_row_length: 241
    Data_length: 47530901504
Max_data_length: 0
   Index_length: 3117400064
      Data_free: 5242880
 Auto_increment: 200000001
    Create_time: 2018-01-18 14:40:45
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

根据官方手册的说法

Index_length

For MyISAM, Index_length is the length of the index file, in bytes.

For InnoDB, Index_length is the approximate amount of memory allocated for non-clustered indexes, in bytes. Specifically, it is the sum of non-clustered index sizes, in pages, multiplied by the InnoDB page size.

对于InnoDB表，Index_length是非聚簇索引的大小总和。
那如果想知道某个索引的大小该怎么办呢？有篇文章分享了一个方法：

mysql> SELECT
    ->        sum(stat_value) pages,
    ->        index_name,
    ->        sum(stat_value) * @@innodb_page_size size
    -> FROM
    ->        mysql.innodb_index_stats
    -> WHERE
    ->            table_name = 'sbtest1'
    ->        AND database_name = 'test'
    ->        AND stat_description = 'Number of pages in the index'
    -> GROUP BY
    ->        index_name;
+--------+----------------+------------+
| pages  | index_name     | size       |
+--------+----------------+------------+
| 442751 | PRIMARY        | 7254032384 |
|  26940 | idx_gmt_create |  441384960 |
|  26940 | idx_is_used    |  441384960 |
|  25081 | k_1            |  410927104 |
+--------+----------------+------------+
4 rows in set (0.00 sec)
mysql> select 441384960+441384960+410927104;
+-------------------------------+
| 441384960+441384960+410927104 |
+-------------------------------+
|                    1293697024 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->        sum(stat_value) pages,
    ->        index_name,
    ->        sum(stat_value) * @@innodb_page_size size
    -> FROM
    ->        mysql.innodb_index_stats
    -> WHERE
    ->            table_name = 'sbtest2'
    ->        AND database_name = 'test'
    ->        AND stat_description = 'Number of pages in the index'
    -> GROUP BY
    ->        index_name;
+---------+------------+-------------+
| pages   | index_name | size        |
+---------+------------+-------------+
| 2901056 | PRIMARY    | 47530901504 |
|  190271 | k_1        |  3117400064 |
+---------+------------+-------------+
2 rows in set (0.00 sec)

在MySQL 5.6版本，表和索引相关的数据保存在mysql.innodb_table_stats and mysql.innodb_index_stats，这样MySQL可以快速的计算出结果。
如果想获取精确结果的话，最好先：

mysql> analyze table sbtest1;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test.sbtest1 | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> analyze table sbtest2;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test.sbtest2 | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (1.10 sec)

这个用法对分区表同样适用：

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` smallint(6) DEFAULT NULL,
  `b` smallint(6) DEFAULT NULL,
  `c` smallint(6) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (c)
PARTITIONS 4 */
1 row in set (0.01 sec)

ANALYZE TABLE t;

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

+-------+-----------------+-----------+
| pages | index_name      | size      |
+-------+-----------------+-----------+
|  8848 | GEN_CLUST_INDEX | 144965632 |
|  5004 | a               |  81985536 |
|  5004 | b               |  81985536 |
+-------+-----------------+-----------+
3 rows in set (0.00 sec)

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

+-------+--------+-----------------+----------+
| pages | part   | index_name      | size     |
+-------+--------+-----------------+----------+
|  2212 | t#P#p0 | GEN_CLUST_INDEX | 36241408 |
|  1251 | t#P#p0 | a               | 20496384 |
|  1251 | t#P#p0 | b               | 20496384 |
|  2212 | t#P#p1 | GEN_CLUST_INDEX | 36241408 |
|  1251 | t#P#p1 | a               | 20496384 |
|  1251 | t#P#p1 | b               | 20496384 |
|  2212 | t#P#p2 | GEN_CLUST_INDEX | 36241408 |
|  1251 | t#P#p2 | a               | 20496384 |
|  1251 | t#P#p2 | b               | 20496384 |
|  2212 | t#P#p3 | GEN_CLUST_INDEX | 36241408 |
|  1251 | t#P#p3 | a               | 20496384 |
|  1251 | t#P#p3 | b               | 20496384 |
+-------+--------+-----------------+----------+
12 rows in set (0.00 sec)