首页 > 代码库 > 怎么计算一个具体InnoDB的索引大小
怎么计算一个具体InnoDB的索引大小
一般情况下，我们看表信息可以用这个命令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: 143310848Max_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 sizeFROM mysql.innodb_index_statsWHERE 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: 0mysql> show create table t\G*************************** 1. row *************************** Table: tCreate 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 sizeFROM mysql.innodb_index_statsWHERE 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 sizeFROM mysql.innodb_index_statsWHERE 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/
怎么计算一个具体InnoDB的索引大小
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。