速查mysql数据大小
速查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)