Size of all tables: Suppose your database or TABLE_SCHEMA name is "news_alert". Then this query will show the size of all tables in the database. 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 the specific table: Suppose your TABLE_NAME is "news". Then SQL query will be- 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)

我使用这个shell脚本来跟踪表大小(以字节和行数为单位)

#!/bin/sh

export MYSQL_PWD=XXXXXXXX
TABLES="table1 table2 table3"

for TABLE in $TABLES;
do
        FILEPATH=/var/lib/mysql/DBNAME/$TABLE.ibd
        TABLESIZE=`wc -c $FILEPATH | awk '{print $1}'`
        #Size in Bytes
        mysql -D scarprd_self -e "INSERT INTO tables_sizes (table_name,table_size,measurement_type) VALUES ('$TABLE', '$TABLESIZE', 'BYTES');"
        #Size in rows
        ROWSCOUNT=$(mysql -D scarprd_self -e "SELECT COUNT(*) AS ROWSCOUNT FROM $TABLE;")
        ROWSCOUNT=${ROWSCOUNT//ROWSCOUNT/}
        mysql -D scarprd_self -e "INSERT INTO tables_sizes (table_name,table_size,measurement_type) VALUES ('$TABLE', '$ROWSCOUNT', 'ROWSCOUNT');"
        mysql -D scarprd_self -e "DELETE FROM tables_sizes WHERE measurement_datetime < TIMESTAMP(DATE_SUB(NOW(), INTERVAL 365 DAY));"
done

它假设有这个MySQL表

CREATE TABLE `tables_sizes` (
  `table_name` VARCHAR(128) NOT NULL,
  `table_size` VARCHAR(25) NOT NULL,
  `measurement_type` VARCHAR(10) NOT NULL CHECK (measurement_type IN ('BYTES','ROWSCOUNT')),
  `measurement_datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()
) ENGINE=INNODB DEFAULT CHARSET=utf8

有一种使用Workbench获取许多信息的简单方法:

右键单击模式名并单击“模式检查器”。 在生成的窗口中有许多选项卡。第一个标签 “信息”显示了数据库大小的粗略估计(以MB为单位)。 第二个选项卡“表”显示每个表的数据长度和其他详细信息。

select x.dbname as db_name, x.table_name as table_name, x.bytesize as the_size from
  (select
     table_schema as dbname,
     sum(index_length+data_length) as bytesize,
     table_name
   from
     information_schema.tables
   group by table_schema
  ) x
where
  x.bytesize > 999999
order by x.bytesize desc;

另一种显示所占用的行数和空间并按其排序的方法。

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”。

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为单位)。