前言
通常情况下，获取InnoDB索引的大小通常的方法是show table status，但是如果想获取指定的索引大小呢？
通常情况下我们想看索引大小的话，用的是
show table status like ""\G
AI 代码解读
例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)
AI 代码解读
根据官方手册的说法
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.
AI 代码解读
对于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)
AI 代码解读
在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)
AI 代码解读
这个用法对分区表同样适用：
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)
AI 代码解读
参考
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/
