MYSQL查询数据大小、索引大小、InnoDB表可用空间大小

1. 选择information_schema数据库（存储其他数据库信息）

   mysql> use information_schema;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Database changed
   

    

2. 查询所有数据库

   mysql> SELECT concat(round(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_SIZE, concat(round(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS INDEX_SIZE, concat(round(SUM(DATA_FREE / 1024 / 1024), 2), 'MB') AS DATA_FREE FROM tables;
   +-----------+------------+-----------+
   | DATA_SIZE | INDEX_SIZE | DATA_FREE |
   +-----------+------------+-----------+
   | 4066.25MB | 2919.14MB  | 101.00MB  |
   +-----------+------------+-----------+
   1 row in set (0.02 sec)
   

    

3. 查询指定数据库

   mysql> SELECT concat(round(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_LENGTH, concat(round(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_INDEX, concat(round(SUM(DATA_FREE / 1024 / 1024), 2), 'MB') AS DATA_FREE FROM TABLES WHERE table_schema = 'shop';
   +-------------+------------+-----------+
   | DATA_LENGTH | DATA_INDEX | DATA_FREE |
   +-------------+------------+-----------+
   | 4062.64MB   | 2916.97MB  | 13.00MB   |
   +-------------+------------+-----------+
   1 row in set (0.00 sec)
   

    

4. 查询指定表（数据库名shop）

   mysql> SELECT concat(round(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_LENGTH, concat(round(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_INDEX, concat(round(SUM(DATA_FREE / 1024 / 1024), 2), 'MB') AS DATA_FREE FROM TABLES WHERE table_schema = 'shop' AND table_name = 'tb_goods';
   +-------------+------------+-----------+
   | DATA_LENGTH | DATA_INDEX | DATA_FREE |
   +-------------+------------+-----------+
   | 1.52MB      | 0.08MB     | 4.00MB    |
   +-------------+------------+-----------+
   1 row in set (0.00 sec)
   

    

5. 查询指定表 （数据库名shop,表名tb_goods）

   mysql> SELECT concat(round(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_LENGTH, concat(round(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS DATA_INDEX, concat(round(SUM(DATA_FREE / 1024 / 1024), 2), 'MB') AS DATA_FREE FROM TABLES WHERE table_schema = 'shop' AND table_name = 'tb_goods';
   +-------------+------------+-----------+
   | DATA_LENGTH | DATA_INDEX | DATA_FREE |
   +-------------+------------+-----------+
   | 1.52MB      | 0.08MB     | 4.00MB    |
   +-------------+------------+-----------+
   1 row in set (0.00 sec)