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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 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)

参考

https://dev.mysql.com/doc/refman/5.7/en/show-table-status.html
http://aadant.com/blog/2014/02/04/how-to-calculate-a-specific-innodb-index-size/

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
存储 算法 关系型数据库
InnoDb行格式、数据页结构、索引底层原理和如何建立索引
InnoDb行格式、数据页结构、索引底层原理和如何建立索引
60 0
|
6月前
|
存储 关系型数据库 MySQL
6.2.2 【MySQL】InnoDB中的索引方案
6.2.2 【MySQL】InnoDB中的索引方案
28 0
|
6月前
|
存储 关系型数据库 MySQL
6.2.3 【MySQL】InnoDB的B+树索引的注意事项
6.2.3 【MySQL】InnoDB的B+树索引的注意事项
47 0
|
2月前
|
存储 SQL 关系型数据库
二十、MySQL技术体系之InnoDB存储引擎的索引
二十、MySQL技术体系之InnoDB存储引擎的索引
50 0
|
7月前
|
存储 算法 关系型数据库
|
3月前
|
存储 关系型数据库 MySQL
MySQL哈希索引以及InnoDB自适应哈希索引
MySQL哈希索引以及InnoDB自适应哈希索引
37 0
MySQL哈希索引以及InnoDB自适应哈希索引
|
3月前
|
存储 SQL 关系型数据库
InnoDB主键索引树和二级索引树
InnoDB主键索引树和二级索引树
32 0
InnoDB主键索引树和二级索引树
|
4月前
|
存储 关系型数据库 数据库
数据库系列课程(15)-MyISAM与InnoDB的索引差异
数据库系列课程(15)-MyISAM与InnoDB的索引差异
14 0
|
4月前
|
关系型数据库 数据库 索引
数据库系列课程(11)-MyISAM和InnoDB对B-Tree索引不同的实现方式
数据库系列课程(11)-MyISAM和InnoDB对B-Tree索引不同的实现方式
16 0
|
5月前
|
存储 SQL 关系型数据库
MySQL第二讲:MySQL innoDB存储引擎中索引原理
MySQL第二讲:MySQL innoDB存储引擎中索引原理
114 0