MySQL資料容量查詢

• ​​1. 各資料庫容量​​
• ​​2. 指定資料庫各表容量​​
• ​​3. 指定資料庫容量​​
• ​​4. 各資料庫表容量​​

參考文檔: ​

​information_schema​

​ 這裡面存放着資料庫各表基本資訊

1. 各資料庫容量

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 
GROUP BY
  table_schema 
ORDER BY
  sum( data_length ) DESC,
  sum( index_length ) DESC;      

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 
WHERE
  table_schema = 'zabbix' 
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 = 'mysql';      

4. 各資料庫表容量

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;