mysql 索引占用空间_mysql查看索引占用空间

mysql中查看索引占用的存储空间使用下面的sql语句：

select table_name,concat(truncate(data_length/1024/1024,2),'MB') as data_size,concat(truncate(index_length/1024/1024,2),'MB') as index_size

from information_schema.tables where table_name='bi_money_list'

order by index_size desc;

查看单个表的细分索引情况：

SELECT

sum(stat_value) pages,

table_name part,

index_name,

concat(round(sum(stat_value)/1000000,2),'M',' rows') * @@innodb_page_size size

FROM

mysql.innodb_index_stats

WHERE

table_name = 'bi_money_list'

AND database_name = 'bi_longzhu_cmge_s6'

AND stat_description LIKE 'Number of pages in the index'

GROUP BY

table_name, index_name;