mysql查看表中每个索引的大小

烤乳鸽好好吃啊

已于 2022-08-16 16:18:28 修改

阅读量749

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

于 2022-08-16 16:17:55 首次发布

本文链接：https://blog.csdn.net/kayle1995/article/details/126368662

版权

mysql 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

查看test库中t_test表的大小

SELECT
       sum(stat_value) pages,
       table_name part,
       index_name,
       concat(round((sum(stat_value)/1024/1024),2),'M',' rows') * @@innodb_page_size size
FROM
       mysql.innodb_index_stats
WHERE
           table_name = 't_test'
       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     |
+---------+------------+-----------------+----------+
| 4362496 | t_antirush | GEN_CLUST_INDEX | 68157.44 |
| 1693371 | t_antirush | appidtime       | 26378.24 |
| 1217088 | t_antirush | fdate           | 19005.44 |
| 1132288 | t_antirush | fuin            | 17694.72 |
|  485120 | t_antirush | idxdate         |  7536.64 |
| 1276992 | t_antirush | srvdate         | 19988.48 |
| 1434112 | t_antirush | srvlevel        | 22446.08 |
| 1489600 | t_antirush | srvrtype        | 23265.28 |
| 1007423 | t_antirush | srvs            | 15728.64 |
+---------+------------+-----------------+----------+