速查mysql数据大小
# 1、查看所有数据库大小
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_size from information_schema.TABLES;
+-----------+
| data_size |
+-----------+
| 2085.96MB |
+-----------+
1 row in set (0.00 sec)
# 2、查看指定数据库大小
mysql> select table_schema,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_size from information_schema.TABLES where table_schema in ('db_test','dbtest_59') group by table_schema order by data_size desc;
+--------------+-----------+
| table_schema | data_size |
+--------------+-----------+
| dbtest_59 | 2060.28MB |
| db_test | 0.27MB |
+--------------+-----------+
2 rows in set (0.00 sec)
# 3、查看指定单表大小
mysql> select table_schema,table_name,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_size from information_schema.TABLES where table_schema='dbtest_59' group by table_name desc order by data_size desc;
+--------------+---------------------------------+-----------+
| table_schema | table_name | data_size |
+--------------+---------------------------------+-----------+
| dbtest_59 | testbl_auto_schedul_studet_info | 2057.00MB |
| dbtest_59 | testb_admin | 2.52MB |
| dbtest_59 | testb_rbac_role_user | 0.38MB |
| dbtest_59 | testb_rbac_role_access | 0.36MB |
| dbtest_59 | testb_rbac_role | 0.02MB |
| dbtest_59 | dy_students_info | 0.02MB |
+--------------+---------------------------------+-----------+
6 rows in set (0.00 sec)
# 4、查询数据库大小
select table_schema,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_size,table_name,table_rows from information_schema.TABLES where table_schema IN('dbtest_59') group by table_name order by table_rows desc;
mysql> select table_schema,concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_size,table_name,table_rows from information_schema.TABLES where table_schema IN('dbtest_59') group by table_name order by table_rows desc;
+--------------+-----------+---------------------------------+------------+
| table_schema | data_size | table_name | table_rows |
+--------------+-----------+---------------------------------+------------+
| dbtest_59 | 2057.00MB | fudao_auto_schedule_course_info | 1497236 |
| dbtest_59 | 2.52MB | fudao_admin | 10406 |
| dbtest_59 | 0.38MB | fudao_rbac_role_user | 7778 |
| dbtest_59 | 0.36MB | fudao_rbac_role_access | 3534 |
| dbtest_59 | 0.02MB | fudao_rbac_role | 70 |
| dbtest_59 | 0.02MB | dy_homework_info | 0 |
+--------------+-----------+---------------------------------+------------+
6 rows in set (0.00 sec)