java 获取mysql表的大小_如何获取MySQL数据库表的大小？

回答(15)

2 years ago

最后计算数据库的总大小：

(SELECT

table_name AS `Table`,

round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`

FROM information_schema.TABLES

WHERE table_schema = "$DB_NAME"

)

UNION ALL

(SELECT

'TOTAL:',

SUM(round(((data_length + index_length) / 1024 / 1024), 2) )

FROM information_schema.TABLES

WHERE table_schema = "$DB_NAME"

)

2 years ago

SELECT TABLE_NAME AS "Table Name",

table_rows AS "Quant of Rows", ROUND( (

data_length + index_length

) /1024, 2 ) AS "Total Size Kb"

FROM information_schema.TABLES

WHERE information_schema.TABLES.table_schema = 'YOUR SCHEMA NAME/DATABASE NAME HERE'

LIMIT 0 , 30

您可以从“ information_schema ” - > SCHEMATA table - >“ SCHEMA_NAME ”列获取模式名称

Additional 您可以按照以下方式获得 size of the mysql databases .

SELECT table_schema "DB Name",

Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"

FROM information_schema.tables

GROUP BY table_schema;

Result

DB Name | DB Size in MB

mydatabase_wrdp 39.1

information_schema 0.0

2 years ago

如果要查询使用当前选定的数据库 . 只需复制粘贴此查询即可 . (无需修改)

SELECT table_name ,

round(((data_length + index_length) / 1024 / 1024), 2) as SIZE_MB

FROM information_schema.TABLES

WHERE table_schema = DATABASE() ORDER BY SIZE_MB DESC;

2 years ago

这应该在mysql中测试，而不是postgresql SELECT table_schema, # "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) # "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;

2 years ago

您可以使用此查询来显示表的大小(尽管您需要先替换变量)：

SELECT

table_name AS `Table`,

round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`

FROM information_schema.TABLES

WHERE table_schema = "$DB_NAME"

AND table_name = "$TABLE_NAME";

或者此查询列出每个数据库中每个表的大小，最大的第一个：

SELECT

table_schema as `Database`,

table_name AS `Table`,

round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`

FROM information_schema.TABLES

ORDER BY (data_length + index_length) DESC;

2 years ago

假设您的数据库名称是 "news_alert". 然后此查询将显示数据库中所有表的大小 .

Size of all tables:

SELECT

TABLE_NAME AS `Table`,

ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)`

FROM

information_schema.TABLES

WHERE

TABLE_SCHEMA = "news_alert"

ORDER BY

(DATA_LENGTH + INDEX_LENGTH)

DESC;

Output:

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

| Table | Size (MB) |

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

| news | 0.08 |

| keyword | 0.02 |

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

2 rows in set (0.00 sec)

For specific table:

SELECT

TABLE_NAME AS `Table`,

ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)`

FROM

information_schema.TABLES

WHERE

TABLE_SCHEMA = "news_alert"

AND

TABLE_NAME = "news"

ORDER BY

(DATA_LENGTH + INDEX_LENGTH)

DESC;

Output:

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

| Table | Size (MB) |

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

| news | 0.08 |

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

1 row in set (0.00 sec)

2 years ago

如果您有 ssh 访问权限，您可能只想尝试 du -hc /var/lib/mysql (或 my.cnf 中设置的不同 datadir ) .

2 years ago

SELECT

table_name AS "Table",

round(((data_length + index_length) / 1024 / 1024), 2) as size

FROM information_schema.TABLES

WHERE table_schema = "YOUR_DATABASE_NAME"

ORDER BY size DESC;

这会对大小进行排序(DB大小，以MB为单位) .

2 years ago

以下是使用bash命令行解决此问题的另一种方法 .

for i in mysql -NB -e 'show databases'; do echo $i; mysql -e "SELECT table_name AS 'Tables', round(((data_length+index_length)/1024/1024),2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema =\"$i\" ORDER BY (data_length + index_length) DESC" ; done

2 years ago

使用Workbench可以轻松获取许多信息：

右键单击模式名称，然后单击“模式检查器” .

在结果窗口中，您有许多选项卡 . 第一个“信息”选项卡显示了以MB为单位的数据库大小的粗略估计 .

第二个选项卡“Tables”显示每个表的数据长度和其他详细信息 .

2 years ago

如果您使用的是phpmyadmin，那么只需转到表结构即可

例如

Space usage

Data 1.5 MiB

Index 0 B

Total 1.5 Mi

2 years ago

尝试以下shell命令(将 DB_NAME 替换为您的数据库名称)：

mysql -uroot <<

对于Drupal / drush解决方案，请检查以下示例脚本，该脚本将显示正在使用的最大表：

#!/bin/sh

DB_NAME=$(drush status --fields=db-name --field-labels=0 | tr -d '\r\n ')

drush sqlq "SELECT table_name AS 'Tables', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"${DB_NAME}\" ORDER BY (data_length + index_length) DESC;" | head -n20

2 years ago

SELECT TABLE_NAME AS table_name,

table_rows AS QuantofRows,

ROUND((data_length + index_length) /1024, 2 ) AS total_size_kb

FROM information_schema.TABLES

WHERE information_schema.TABLES.table_schema = 'db'

ORDER BY (data_length + index_length) DESC;

以上所有2都是在mysql上测试的

2 years ago

另一种显示行数和占用空间以及按顺序排序的方法 .

SELECT

table_schema as `Database`,

table_name AS `Table`,

table_rows AS "Quant of Rows",

round(((data_length + index_length) / 1024 / 1024/ 1024), 2) `Size in GB`

FROM information_schema.TABLES

WHERE table_schema = 'yourDatabaseName'

ORDER BY (data_length + index_length) DESC;

您必须在此查询中替换的唯一字符串是“yourDatabaseName” .

2 years ago

改编自ChapMic的回答以满足我的特殊需求 .

仅指定数据库名称，然后按降序对所有表进行排序 - 从所选数据库中的LARGEST到SMALLEST表 . 只需要替换1个变量=您的数据库名称 .

SELECT

table_name AS `Table`,

round(((data_length + index_length) / 1024 / 1024), 2) AS `size`

FROM information_schema.TABLES

WHERE table_schema = "YOUR_DATABASE_NAME_HERE"

ORDER BY size DESC;