mysql查看库表占用存储空间大小

最新推荐文章于 2025-02-11 14:16:49 发布

原创最新推荐文章于 2025-02-11 14:16:49 发布 · 1.2k 阅读

4 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql

数据库专栏收录该内容

120 篇文章

订阅专栏

本文详细介绍如何使用SQL查询来检查和管理数据库空间，包括查询数据库实例下所有库的总大小、查看每个库的具体大小、获取单个库的详细空间信息、检查单个表的状态以及了解单库下所有表的空间占用情况。

`一、查看该数据库实例下所有库大小的总和，结果是以MB为单位`

1.1 命令

SELECT 
  table_schema,
  SUM(data_length) / 1024 / 1024 AS data_length,
  SUM(index_length) / 1024 / 1024 AS index_length,
  SUM(data_length + index_length) / 1024 / 1024 AS SUM
FROM
  information_schema.tables ;

1.2 结果

+--------------------+---------------+--------------+---------------+
| table_schema       | data_length   | index_length | sum           |
+--------------------+---------------+--------------+---------------+
| information_schema | 2734.92757511 |  86.27539063 | 2821.20296574 |
+--------------------+---------------+--------------+---------------+

`二、查看该实例下每个库的大小`

2.1 命令

SELECT 
  table_schema,
  SUM(data_length + index_length) / 1024 / 1024 AS total_mb,
  SUM(data_length) / 1024 / 1024 AS data_mb,
  SUM(index_length) / 1024 / 1024 AS index_mb,
  COUNT(*) AS TABLES,
  CURDATE() AS today 
FROM
  information_schema.tables 
GROUP BY table_schema 
ORDER BY 2 DESC ;

2.2 结果

+--------------------+---------------+--------------+--------------+--------+------------+
| table_schema       | total_mb      | data_mb      | index_mb     | TABLES | today      |
+--------------------+---------------+--------------+--------------+--------+------------+
| test               | 1116.34375000 | 603.87500000 | 512.46875000 |     20 | 2019-07-04 |
| mysql              |    2.47324657 |   2.25937939 |   0.21386719 |     31 | 2019-07-04 |
| information_schema |    0.15625000 |   0.15625000 |   0.00000000 |     61 | 2019-07-04 |
| sys                |    0.01562500 |   0.01562500 |   0.00000000 |    101 | 2019-07-04 |
| performance_schema |    0.00000000 |   0.00000000 |   0.00000000 |     87 | 2019-07-04 |
+--------------------+---------------+--------------+--------------+--------+------------+

`三、查看单个库的大小`

3.1 命令

SELECT 
  CONCAT(
    TRUNCATE(SUM(data_length) / 1024 / 1024, 2),
    'mb'
  ) AS data_size,
  CONCAT(
    TRUNCATE(SUM(max_data_length) / 1024 / 1024, 2),
    'mb'
  ) AS max_data_size,
  CONCAT(
    TRUNCATE(SUM(data_free) / 1024 / 1024, 2),
    'mb'
  ) AS data_free,
  CONCAT(
    TRUNCATE(SUM(index_length) / 1024 / 1024, 2),
    'mb'
  ) AS index_size 
FROM
  information_schema.tables 
WHERE table_schema = 'database_name';

3.2 结果

+-----------+------------------+-----------+------------+
| data_size | max_data_size    | data_free | index_size |
+-----------+------------------+-----------+------------+
| 2734.40mb | 83483426815.99mb | 14.06mb   | 86.19mb    |
+-----------+------------------+-----------+------------+

`四、查看单个表的状态`

4.1 命令

SHOW TABLE STATUS FROM database_name WHERE NAME = 'table_name' \G

4.2 结果

*************************** 1. row ***************************
           Name: table_name
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-06-19 14:14:56
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

`五、查看单库下所有表的状态`

5.1 命令

SELECT 
  table_name,
  (data_length / 1024 / 1024) AS data_mb,
  (index_length / 1024 / 1024) AS index_mb,
  (
    (data_length + index_length) / 1024 / 1024
  ) AS all_mb,
  table_rows 
FROM
  information_schema.tables
WHERE 
  table_schema = 'database_name';

5.2 结果

+---------------------------+---------------+-------------+---------------+------------+
| table_name                | data_mb       | index_mb    | all_mb        | table_rows |
+---------------------------+---------------+-------------+---------------+------------+
| ss_daccount               |    0.23437500 |  0.10937500 |    0.34375000 |       4481 |
| ss_daccount_log           |    2.48262787 |  0.58496094 |    3.06758881 |      27248 |
| ss_daccount_type          |    0.00025558 |  0.00195313 |    0.00220871 |          8 |
+---------------------------+---------------+-------------+---------------+------------+