如何计算 MySQL 中索引的大小

How to figure out size of Indexes in MySQL

提问by Brian G

I want to determine the size of my indexes, they are primary key indexes. This happens to be on mysql cluster but I don't think that is significant.

我想确定我的索引的大小，它们是主键索引。这恰好在 mysql 集群上，但我认为这并不重要。

回答by Josh Warner-Burke

I think this is what you're looking for.

我想这就是你要找的。

show table status from [dbname]

http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

回答by Daniel Zohar

Extending Vajk Hermecz's answer.
This is how you can get all of the indexes size, in megabytes, without the PRIMARY (which is the table itself), ordered by size.

扩展 Vajk Hermecz 的回答。
这就是您如何获得所有索引大小（以兆字节为单位），而无需按大小排序的 PRIMARY（即表本身）。

SELECT database_name, table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size' AND index_name != 'PRIMARY'
ORDER BY size_in_mb DESC;

回答by Vajk Hermecz

If you are using InnoDB tables, you can get the size for individual indexes from mysql.innodb_index_stats. The 'size' stat contains the answer, in pages, so you have to multiply it by the page-size, which is 16K by default.

如果您使用 InnoDB 表，您可以从mysql.innodb_index_stats. 'size' stat 包含答案，以页为单位，因此您必须将其乘以页面大小，默认情况下为 16K。

select database_name, table_name, index_name, stat_value*@@innodb_page_size
from mysql.innodb_index_stats where stat_name='size';

回答by Liam Wheldon

Here's an adaption from some of the above to also give you the percentage of the total index for the table that each index has used, hopefully this will be useful for someone

这是对上述一些内容的改编，还可以为您提供每个索引使用的表的总索引百分比，希望这对某人有用

select 
    database_name, 
    table_name, 
    index_name, 
    round((stat_value*@@innodb_page_size)/1024/1024, 2) SizeMB, 
    round(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))*(stat_value*@@innodb_page_size)), 2) `Percentage`
from mysql.innodb_index_stats iis 
where stat_name='size' 
and table_name = 'TargetTable'
and database_name = 'targetDB'

Example output

示例输出

database_name   table_name  index_name  SizeMB  Percentage
targetDB        TargetTable id          10      55.55
targetDB        TargetTable idLookup    5       27.77
targetDB        TargetTable idTest      3       16.66

Regards Liam

问候利亚姆

回答by Quassnoi

On MyISAM, each index block is 4 KBpage filled up to fill_factorwith index records, each being key length + 4bytes long.

在 上MyISAM，每个索引块都被索引记录4 KB填满页面fill_factor，每一个都是key length + 4字节长。

Fill factoris normally 2/3

Fill factor通常是 2/3

As for InnoDB, the table is always clustered on the PRIMARY KEY, there is no separate PRIMARY KEYindex

至于InnoDB，表总是聚集在 上PRIMARY KEY，没有单独的PRIMARY KEY索引

回答by Peter D

Using phpMyAdmin, when viewing the table structure there is a Details link at the bottom somewhere. Once you click on it it will show you the total size of the indexes you have on the table where it is marked Space Usage.

使用 phpMyAdmin，查看表结构时，底部某处有一个详细信息链接。单击它后，它会显示您在表上的索引的总大小，其中标记了空间使用情况。

I don't think it shows you each index individually though.

我不认为它会单独向您显示每个索引。

回答by Amini

In this article determine how to calculate index size. http://aadant.com/blog/2014/02/04/how-to-calculate-a-specific-innodb-index-size/

在本文中确定如何计算索引大小。 http://aadant.com/blog/2014/02/04/how-to-calculate-a-specific-innodb-index-size/

回答by viggy28

From the MySQL 5.6 reference

来自 MySQL 5.6 参考

SELECT SUM(stat_value) pages, index_name,
SUM(stat_value)*@@innodb_page_size size
FROM mysql.innodb_index_stats WHERE table_name='t1'
AND stat_name = 'size' GROUP BY index_name;