1.查看所有库大小

  1. SELECT
  2.     table_schema AS '数据库',
  3.     sum(table_rows) AS '记录数',
  4.     sum(
  5.         TRUNCATE (data_length / 1024 / 1024, 2)
  6.     ) AS '数据容量(MB)',
  7.     sum(
  8.         TRUNCATE (index_length / 1024 / 1024, 2)
  9.     ) AS '索引容量(MB)'
  10. FROM
  11.     information_schema. TABLES
  12. GROUP BY
  13.     table_schema
  14. ORDER BY
  15.     sum(data_length) DESC,
  16.     sum(index_length) DESC;
  20. +--------------------+-----------+------------------+------------------+
  21. | 数据库             | 记录数    | 数据容量(MB)     | 索引容量(MB)     |
  22. +--------------------+-----------+------------------+------------------+
  23. | cok_db1            |  72137886 |         16776.96 |         10350.47 |
  24. | cok_db1_test       |    133689 |            52.21 |            25.17 |
  25. | cokdb_global       |    134266 |            29.67 |             1.18 |
  26. | mysql              |      4961 |             1.87 |             0.00 |
  27. | cokdb_pay          |       525 |             0.26 |             0.00 |
  28. | information_schema |      NULL |             0.10 |             0.00 |
  29. | test               |         1 |             0.06 |             0.00 |
  30. | innodb_memcache    |         4 |             0.03 |             0.00 |
  31. | performance_schema |   1357095 |             0.00 |             0.00 |
  32. | sys                |      NULL |             NULL |             NULL |
  33. +--------------------+-----------+------------------+------------------+
  34. 10 rows in set (0.94 sec)

2.查看所有数据库各表容量大小

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE (data_length / 1024 / 1024, 2) AS '数据容量(MB)',
    TRUNCATE (index_length / 1024 / 1024, 2) AS '索引容量(MB)'
FROM
    information_schema. TABLES
ORDER BY
    data_length DESC,
    index_length DESC;

3.查看指定数据库容量大小

SELECT
    table_schema AS '数据库',
    sum(table_rows) AS '记录数',
    sum(
        TRUNCATE (data_length / 1024 / 1024, 2)
    ) AS '数据容量(MB)',
    sum(
        TRUNCATE (index_length / 1024 / 1024, 2)
    ) AS '索引容量(MB)'
FROM
    information_schema. TABLES
WHERE
    table_schema = 'cok_db1';

+-----------+-----------+------------------+------------------+
| 数据库    | 记录数    | 数据容量(MB)     | 索引容量(MB)     |
+-----------+-----------+------------------+------------------+
| cok_db1   |  72137887 |         16776.96 |         10350.47 |
+-----------+-----------+------------------+------------------+
1 row in set (0.04 sec)

4.查看mysql库各表容量大小

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE (data_length / 1024 / 1024, 2) AS '数据容量(MB)',
    TRUNCATE (index_length / 1024 / 1024, 2) AS '索引容量(MB)'
FROM
    information_schema. TABLES
WHERE
    table_schema = 'cok_db1'
ORDER BY
    data_length DESC,
    index_length DESC;

image.png