速查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)