mysql数据库大小_sql语句查看MySQL数据库大小

SQL语句查看MySQL数据库大小

> SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024 "Database Size in MB"

FROM information_schema.TABLES

GROUP BY table_schema;

+--------------------+---------------------+

| Database Name      | Database Size in MB |

+--------------------+---------------------+

| o2o                |         12.13803768 |

| hellodb            |          0.09472656 |

| information_schema |          0.15625000 |

| jumpserver         |          1.46875000 |

| mysql              |          0.75833321 |

| performance_schema |          0.00000000 |

+--------------------+---------------------+

6 rows in set (0.04 sec)

查看MySQL表大小

例子：查看hellodb数据库，只显示出zabbix库中最大的10个表

> SELECT table_name AS "Tables",round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"

FROM information_schema.TABLES

WHERE table_schema = "www_ttlsa_com"

ORDER BY (data_length + index_length) DESC;

+----------+------------+

| Tables   | Size in MB |

+----------+------------+

| classes  |       0.02 |

| toc      |       0.02 |

| teachers |       0.02 |

| students |       0.02 |

| scores   |       0.02 |

| courses  |       0.02 |

| t2       |       0.00 |

+----------+------------+

7 rows in set (0.00 sec)

找出前10的表大小

> SELECT CONCAT(table_schema, '.', table_name),

CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,

CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,

CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,

CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,

ROUND(index_length / data_length, 2) idxfrac

FROM information_schema.TABLES

ORDER BY data_length + index_length DESC

LIMIT 10;

+---------------------------------------+-------+-------+-------+------------+---------+

| CONCAT(table_schema, '.', table_name) | rows  | DATA  | idx   | total_size | idxfrac |

+---------------------------------------+-------+-------+-------+------------+---------+

| zabbix.history_uint                   | 1.97M | 0.12G | 0.05G | 0.17G      |    0.44 |

| zabbix.history                        | 1.60M | 0.09G | 0.04G | 0.13G      |    0.47 |

| XX_Mobile.banklisttest                | 0.06M | 0.01G | 0.00G | 0.01G      |    0.00 |

| zabbix.events                         | 0.03M | 0.00G | 0.00G | 0.01G      |    1.60 |

| XXXXfenxiao.pigcms_wechat_group_list  | 0.04M | 0.00G | 0.00G | 0.00G      |    0.15 |

| XXXXfenxiao.pigcms_behavior           | 0.03M | 0.00G | 0.00G | 0.00G      |    0.60 |

| b2b.ecs_stats                         | 0.05M | 0.00G | 0.00G | 0.00G      |    0.13 |

| XX_Mobile.create_sn                   | 0.02M | 0.00G | 0.00G | 0.00G      |    0.58 |

| b2b.ecs_goods                         | 0.00M | 0.00G | 0.00G | 0.00G      |    0.04 |

| zabbix.trends_uint                    | 0.02M | 0.00G | 0.00G | 0.00G      |    0.00 |

+---------------------------------------+-------+-------+-------+------------+---------+

10 rows in set (0.14 sec)