SELECT table_schema "Database", ROUND(SUM(data_length+index_length)/1024/1024,1) "TOTAL MB" , ROUND(SUM(data_length)/1024/1024,1) "DATA MB" , ROUND(SUM(index_length)/1024/1024,1) "INDEX MB"
FROM information_schema.TABLES
GROUP BY 1;
=>
+--------------------+----------+---------+----------+
| Database | TOTAL MB | DATA MB | INDEX MB |
+--------------------+----------+---------+----------+
| DATABASE | 4206.4 | 3964.5 | 241.9 |
| information_schema | 0.0 | 0.0 | 0.0 |
| mysql | 0.6 | 0.5 | 0.1 |
+--------------------+----------+---------+----------+
SELECT table_schema "Database", ROUND(SUM(data_length+index_length)/1024/1024,1) "TOTAL MB" , ROUND(SUM(data_length)/1024/1024,1) "DATA MB" , ROUND(SUM(index_length)/1024/1024,1) "INDEX MB"
FROM information_schema.TABLES
WHERE table_schema = '{데이터베이스명}'
GROUP BY 1;
SELECT table_schema "Database",TABLE_NAME,ENGINE, ROUND(SUM(data_length+index_length)/1024/1024,1) "TOTAL MB" , ROUND(SUM(data_length)/1024/1024,1) "DATA MB" , ROUND(SUM(index_length)/1024/1024,1) "INDEX MB"
FROM information_schema.TABLES
# WHERE table_schema = '{데이터베이스명}'
GROUP BY 1,2;
Database TABLE_NAME ENGINE TOTAL MB DATA MB INDEX MB
-------- -------------------------------- ------ -------- ------- ----------
DBNAME XXXXXXXXXXXXXXXXXXX InnoDB 72.2 56.6 15.6
DBNAME YYYYYYYYYYYYYYY MyISAM 67.7 39.4 28.4
*. 엔진별 사용량SELECT ENGINE,
ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",
ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",
ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",
COUNT(*) "Num Tables"
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema NOT IN ("information_schema", "PERFORMANCE_SCHEMA", "SYS_SCHEMA", "ndbinfo")
GROUP BY ENGINE;
=>
ENGINE Data MB Index MB Total MB Num Tables
------ ------- -------- -------- ------------
(NULL) (NULL) (NULL) (NULL) 1
Aria 1.8 0.3 2.1 24
CSV 0.0 0.0 0.0 2
InnoDB 0.1 0.0 0.1 5
MyISAM 6502.8 2099.5 8602.3 681
