怎么计算一个具体InnoDB的索引大小

发布网友 发布时间：2022-05-01 13:45

我来回答

共2个回答

懂视网 时间：2022-05-01 18:07

而这里的都是预估值，我们可以通过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 tG
*************************** 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/ 

怎么计算一个具体InnoDB的索引大小

标签：secondary   options   rip   show   engine   信息   compact   index   rds   

热心网友 时间：2022-05-01 15:15