有人告诉我,这show table status
在查找数据库大小时很有用,但我得到的是一团乱麻。有没有办法改变这个命令以获取更少的信息,或者有没有其他方法可以查找数据库或表的大小?
答案1
您想要的两个字段是。
数据长度
这是表内数据的大小(以字节为单位)。
索引长度
这是表索引的大小(以字节为单位)。
您可以将输出限制为具有数据库的一个表。
SHOW TABLE STATUS LIKE 'tablename';
您无法从输出中删除其他列。您可能会发现通过垂直打印输出行,从控制台读取输出会更容易。只需将“ ;
”替换为“ \G
”。
答案2
我有一组相当疯狂的查询可以很好地解决这个问题。我大约 2 年前写了这些,它们效果很好。我仍然用它们向客户报告。
查询按存储引擎分组的数据库大小(以 MB 为单位)
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR('KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (
SELECT engine,SUM(data_length) DSize,
SUM(index_length) ISize,
SUM(data_length+index_length) TSize
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL
GROUP BY engine WITH ROLLUP) B,
(SELECT 2 pw) A
ORDER BY TSize;
查询以数据库大小(按 MB 分组)
SELECT DBName,
CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Total Size"
FROM (
SELECT IFNULL(DB,'All Databases') DBName,
SUM(DSize) SDSize,
SUM(XSize) SXSize,
SUM(TSize) STSize
FROM (
SELECT table_schema DB,
data_length DSize,
index_length XSize,
data_length+index_length TSize
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP
) AA,
(SELECT 2 pw) BB
ORDER BY (SDSize+SXSize);
查询按数据库和存储引擎分组的数据库大小(以 MB 为单位)
SELECT Statistic,DataSize "Data Size",
IndexSize "Index Size",
TableSize "Table Size"
FROM (
SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,
"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') TableSize
FROM (SELECT table_schema,engine,
SUM(data_length) DSize,
SUM(index_length) ISize,
SUM(data_length+index_length) TSize
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL
GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 2 pw) A) AA
ORDER BY schemaname, schema_score,engine_score;
这三个查询都有一个共同点:一个简单的 SELECT 查询(SELECT 2 pw)。
pw 代表幂,即 1024 的幂。您可以调整查询以提供具有不同单位的数据库大小:
(SELECT 0 pw) --reports the Database Size in Bytes
(SELECT 1 pw) --reports the Database Size in Kilobytes
(SELECT 2 pw) --reports the Database Size in Megabytes
(SELECT 3 pw) --reports the Database Size in Gigabytes
(SELECT 4 pw) --reports the Database Size in Terabytes
(SELECT 5 pw) --reports the Database Size in Petabytes (email me if you reach this size)
尝试一下吧!!!
答案3
找到一个脚本可以做你想做的事情 -
http://www.modwest.com/help/kb6-199.html
<?
mysql_connect("db.modwest.com", "username", "password");
mysql_select_db("yourdb");
$result = mysql_query("show table status");
$size = 0;
$out = "";
while($row = mysql_fetch_array($result)) {
$size += $row["Data_length"];
$out .= $row["Name"] .": ".
round(($row["Data_length"]/1024)/1024, 2) ."<br>\n";
}
$size = round(($size/1024)/1024, 1);
echo $out ."<br>\n";
echo "Total MySQL db size: $size";
?>
答案4
尝试这个脚本:
SELECT
table_schema AS DataBase_Name
,ROUND(sum( data_length + index_length ) / 1024 /1024,1) AS OccupiedSize_inMB
,ROUND(sum( data_free )/ 1024 / 1024,1) AS FreeSpace_inMB
FROM information_schema.TABLES
GROUP BY table_schema ;