mysql获取内存占用前10_mysql – 如何找出占用数据库最大内存的表？

MyISAM仅为其索引占用内存

要查找在最坏情况下可以使用最多内存的前10个MyISAM表,请尝试以下操作：

SELECT * FROM

(

SELECT table_schema,table_name,index_length

FROM information_schema.tables

WHERE engine='MyISAM' AND

table_schema NOT IN ('information_schema','mysql','performance_schema')

ORDER BY index_length DESC

) LIMIT 10;

InnoDB为其数据和索引占用内存

要查找在最坏情况下可以使用最多内存的前10个InnoDB表,请尝试以下操作：

SELECT * FROM

(

SELECT table_schema,table_name,data_length+index_length tblsize

FROM information_schema.tables

WHERE engine='InnoDB'

ORDER BY index_length DESC

) LIMIT 10;

这是按尺寸递减的前50个表格的另一个显示

SELECT * FROM

(SELECT TN TableName,LPAD(REPLACE(FORMAT(TS/POWER(1024,1),2),',',''),Z,' ') KB,

LPAD(REPLACE(FORMAT(TS/POWER(1024,2),2),',',''),Z,' ') MB,

LPAD(REPLACE(FORMAT(TS/POWER(1024,3),2),',',''),Z,' ') GB

FROM (SELECT CONCAT(table_schema,'.',table_name) TN,

(data_length+index_length) TS FROM information_schema.tables

WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')

AND engine IS NOT NULL) A,(SELECT 13 Z) B ORDER BY TS DESC) MMM LIMIT 50;

如果您有兴趣,我会在MySQL实例上提供有关整个故事的查询

此查询显示Storage Engine以GB为单位占用的磁盘空间量

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 3 pw) A ORDER BY TSize;

此查询显示数据库以GB为单位占用的磁盘空间量

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 3 pw) BB ORDER BY (SDSize+SXSize);

此查询显示Database by Storage Engine以GB为单位占用的磁盘空间量

SELECT 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')

"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 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 3 pw) A ORDER BY TSize;

我发布的前三个查询有一个共同的特性：子查询(SELECT 3 pw)

>如果使用(SELECT 0 pw),则报告以字节为单位>如果使用(SELECT 1 pw),则报告为KiloBytes>如果您使用(SELECT 2 pw),报告在MegaBytes中>如果使用(SELECT 3 pw),则报告为GigaBytes>如果使用(SELECT 4 pw),报告在TeraBytes中>如果您使用(SELECT 5 pw),报告在PetaBytes中(如果您需要,请发布结果！)