MySQL Tips

MySQLでDBとテーブルのサイズを確認する方法について説明します。

以下のSQLを実行すると確認できます。

■DBサイズ

SELECT table_schema, sum(data_length) /1024/1024 AS SizeSize_MB
FROM information_schema.tables
GROUP BY table_schema
ORDER BY sum(data_length+index_length) DESC;

■Tableサイズ

SELECT table_name
, engine
, table_rows AS TableRowsCount
, avg_row_length AS AvgRowLength
, floor((data_length+index_length)/1024/1024) AS TotalSize_MB
, floor((data_length)/1024/1024) AS DataSize_MB
, floor((index_length)/1024/1024) AS IndexSize_MB
FROM information_schema.tables
WHERE table_schema=database()
ORDER BY (data_length+index_length) DESC;