一般情况下，我们看表信息可以用这个命令show table status：
mysql> show table status like 't'\G
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 4186170
Avg_row_length: 34
Data_length: 143310848
Max_data_length: 0
Index_length: 146030592
Data_free: 6291456
Auto_increment: NULL
Create_time: 2014-02-04 15:40:54
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
而这里的都是预估值，我们可以通过ANALYZE TABLE获取精确的值：
Data_length: 143310848, 136Mb clustered index size.
Index_length: 146030592, 139Mb secondary index size.
比如这个有3个索引：1个自动生成的聚簇索引和2个普通索引：
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
在5.6我们有更加明确的方式来知道索引的大小：
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 = 't'
AND database_name = 'test'
AND stat_description = 'Number of pages in the index'
GROUP BY
index_name; +-------+-----------------+-----------+
| pages | index_name | size |
+-------+-----------------+-----------+
| 8747 | GEN_CLUST_INDEX | 143310848 |
| 4456 | a | 73007104 |
| 4457 | b | 73023488 |
+-------+-----------------+-----------+
3 rows in set (0.00 sec)
那么在分区表中该如何获得索引的大小呢？
mysql> alter table t partition by key(c) partitions 4;
Query OK, 4194308 rows affected (44.03 sec)
Records: 4194308 Duplicates: 0 Warnings: 0 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)
参考资料：
http://aadant.com/blog/2014/02/04/how-to-calculate-a-specific-innodb-index-size/