mysql表空间信息和索引查看

在mysql快捷操作Navicat for Mysql下操作：

表信息在information_schema库下的TABLES表中：

查询所有数据的大小：

select concat(round(sum(DATA_LENGTH/1024/1024),2), 'MB') as data from TABLES

查看指定数据库的表的大小：

 select concat(round(sum (DATA_LENGTH/1024/1024),2), 'MB') as data from TABLES where table_schema= 'test' and table_name= a;

查看索引

单位是GB：

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size'

单位是MB：

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB')  AS 'Total Index Size' FROM information_schema.TABLES  WHERE table_schema LIKE 'database';

在linux下操作：

命令：show databases;

use information_schema

select concat(round(sum(DATA_LENGTH/1024/1024),2), 'MB') as data from TABLES where table_schema= 'wzc' ;