mysql 表空间及索引的查看方法

1.查看索引

（1）单位是gb

select concat(round(sum(index_length)/(1024*1024*1024), 2), ' gb') as 'total index size' from information_schema.tables where table_schema like 'database';
+------------------+
| total index size |
+------------------+
| 1.70 gb |
+------------------+

（2）单位是mb

select concat(round(sum(index_length)/(1024*1024), 2), ' mb') as 'total index size' from information_schema.tables where table_schema like 'database';

其中“database”为你所要查看的数据库



2.查看表空间

select concat(round(sum(data_length)/(1024*1024*1024), 2), ' gb') as 'total data size'
from information_schema.tables where table_schema like 'database';
+-----------------+
| total data size |
+-----------------+
| 3.01 gb |
+-----------------+

3.查看数据库中所有表的信息

select concat(table_schema,'.',table_name) as 'table name',
concat(round(table_rows/1000000,2),'m') as 'number of rows',
concat(round(data_length/(1024*1024*1024),2),'g') as 'data size',
concat(round(index_length/(1024*1024*1024),2),'g') as 'index size' ,
concat(round((data_length+index_length)/(1024*1024*1024),2),'g') as'total'from information_schema.tables where table_schema like 'database';