统计mysql说有表的大小_统计mysql数据库、表大小_MySQL

àì夳堔傛蜴生んèń 2023-01-12 11:36 172阅读 0赞

1. 查看该数据库实例下所有库大小，得到的结果是以MB为单位

mysql> select table\_schema,sum(data\_length)/1024/1024 as data\_length,sum(index\_length)/1024/1024

as index\_length,sum(data\_length+index\_length)/1024/1024 as sum from information\_schema.tables;

\+--------------------+---------------+--------------+---------------+

\+--------------------+---------------+--------------+---------------+

| information\_schema | 2734.92757511 | 86.27539063 | 2821.20296574 |

\+--------------------+---------------+--------------+---------------+

2、查看该实例下各个库大小

mysql> select table\_schema, sum(data\_length+index\_length)/1024/1024 as total\_mb,

sum(data\_length)/1024/1024 as data\_mb, sum(index\_length)/1024/1024 as index\_mb,

count(\*) as tables, curdate() as today from information\_schema.tables group by table\_schema order by 2 desc;

\+--------------------+---------------+---------------+-------------+--------+------------+

\+--------------------+---------------+---------------+-------------+--------+------------+

| data\_1234567890 | 2820.59610939 | 2734.39689064 | 86.19921875 | 65 | 2015-11-02 |

| mysql | 0.60579967 | 0.53744030 | 0.06835938 | 14 | 2015-11-02 |

| information\_schema | 0.00781250 | 0.00000000 | 0.00781250 | 35 | 2015-11-02 |

\+--------------------+---------------+---------------+-------------+--------+------------+

3、查看单个库的大小

mysql> select concat(truncate(sum(data\_length)/1024/1024,2),'mb') as data\_size,

concat(truncate(sum(max\_data\_length)/1024/1024,2),'mb') as max\_data\_size,

concat(truncate(sum(data\_free)/1024/1024,2),'mb') as data\_free,

concat(truncate(sum(index\_length)/1024/1024,2),'mb') as index\_size

from information\_schema.tables where table\_schema = 'erongtu\_tyb2014';

\+-----------+------------------+-----------+------------+

\+-----------+------------------+-----------+------------+

| 2734.40mb | 83483426815.99mb | 14.06mb | 86.19mb |

\+-----------+------------------+-----------+------------+

4、查看单个表的状态

mysql> show table status from data\_1234567890 where name = 'data\_1234567890\_ss' G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Name: data\_1234567890\_ss

Engine: InnoDB

Version: 10

Row\_format: Compact

Rows: 840065

Avg\_row\_length: 477

Data\_length: 401473536

Max\_data\_length: 0

Index\_length: 0

Data\_free: 6291456

Auto\_increment: 882251

Create\_time: 2015-09-07 17:24:18

Update\_time: NULL

Check\_time: NULL

Collation: utf8\_general\_ci

Checksum: NULL

Create\_options:

Comment:

1 row in set (0.00 sec)

5、查看单库下所有表的状态

mysql> select table\_name, (data\_length/1024/1024) as data\_mb , (index\_length/1024/1024)

as index\_mb, ((data\_length+index\_length)/1024/1024) as all\_mb, table\_rows

from tables where table\_schema = 'data\_1234567890';

\+---------------------------+---------------+-------------+---------------+------------+

\+---------------------------+---------------+-------------+---------------+------------+

| ss\_daccount | 0.23437500 | 0.10937500 | 0.34375000 | 4481 |

| ss\_daccount\_log | 2.48262787 | 0.58496094 | 3.06758881 | 27248 |

| ss\_daccount\_type | 0.00025558 | 0.00195313 | 0.00220871 | 8 |

| ss\_daccountlog | 221.61502457 | 22.66113281 | 244.27615738 | 1045462 |

| ss\_dactives | 0.00178146 | 0.00195313 | 0.00373459 | 7 |

| ss\_dadmin | 0.00268173 | 0.00195313 | 0.00463486 | 19 |

| ss\_dadmin\_log | 0.36599731 | 0.05175781 | 0.41775513 | 5191 |

| ss\_dadmin\_nav | 0.01562500 | 0.00000000 | 0.01562500 | 46 |

| ss\_dadmin\_role | 0.01562500 | 0.01562500 | 0.03125000 | 5 |

| ss\_dadvertisement | 0.07812500 | 0.00000000 | 0.07812500 | 486 |

| ss\_dadvertisement\_click | 382.87500000 | 0.00000000 | 382.87500000 | 1023350 |

| ss\_dadvertisement\_content | 0.10937500 | 0.01562500 | 0.12500000 | 105 |

| ss\_dapplication | 3.23010635 | 0.02441406 | 3.25452042 | 1859 |

| ss\_dapplication\_bak | 2.25843048 | 0.01269531 | 2.27112579 | 738 |

| ss\_dapplication\_comment | 0.88685226 | 0.05566406 | 0.94251633 | 5454 |

| ss\_dapplication\_material | 0.00187683 | 0.00195313 | 0.00382996 | 16 |

| ss\_dapplication\_user | 0.90316010 | 0.10839844 | 1.01155853 | 8861 |

| ss\_darea | 1.05803299 | 0.88769531 | 1.94572830 | 45051 |

| ss\_dauthentication | 0.00072861 | 0.00195313 | 0.00268173 | 13 |

| ss\_dbbs\_relatedlink | 0.01562500 | 0.00000000 | 0.01562500 | 12 |

| ss\_dborrow | 0.04732895 | 0.00390625 | 0.05123520 | 153 |

| ss\_dborrow\_collection | 27.61576080 | 13.99023438 | 41.60599518 | 221169 |

| ss\_dborrow\_lz | 0.00000000 | 0.00097656 | 0.00097656 | 0 |

| ss\_dborrow\_tender | 24.62931824 | 18.27050781 | 42.89982605 | 147411 |

| ss\_dcash | 6.40177155 | 1.97949219 | 8.38126373 | 42807 |

| ss\_dcp\_block | 0.04687500 | 0.01562500 | 0.06250000 | 37 |

| ss\_dcp\_item | 0.09375000 | 0.00000000 | 0.09375000 | 134 |

| ss\_dedu\_member | 0.01562500 | 0.00000000 | 0.01562500 | 13 |

| ss\_dinfo\_article | 78.76256561 | 1.19433594 | 79.95690155 | 10038 |

| ss\_dinfo\_channel | 0.01562500 | 0.00000000 | 0.01562500 | 56 |

| ss\_dinfo\_comment | 0.32812500 | 0.00000000 | 0.32812500 | 1206 |

| ss\_dinfo\_nav | 0.01562500 | 0.00000000 | 0.01562500 | 27 |

| ss\_dinfo\_p2pdata | 0.90237427 | 0.29101563 | 1.19338989 | 29569 |

| ss\_dinfo\_p2pdata\_0 | 0.04687500 | 0.00000000 | 0.04687500 | 275 |

| ss\_dinfo\_photo | 0.23437500 | 0.00000000 | 0.23437500 | 71 |

| ss\_dinfo\_project | 0.06250000 | 0.00000000 | 0.06250000 | 67 |

| ss\_dinfo\_seekdata | 35.53404236 | 0.64062500 | 36.17466736 | 5002 |

| ss\_dinfo\_tagname | 0.01562500 | 0.00000000 | 0.01562500 | 18 |

| ss\_dinfo\_testinfo | 0.01562500 | 0.00000000 | 0.01562500 | 21 |

| ss\_dinfo\_video | 0.91273117 | 0.00781250 | 0.92054367 | 207 |

| ss\_djiao | 0.01562500 | 0.00000000 | 0.01562500 | 3 |

| ss\_dliberty | 3.79615784 | 0.27734375 | 4.07350159 | 23108 |

| ss\_dliberty\_item | 3.08350754 | 1.24414063 | 4.32764816 | 51113 |

| ss\_dloan | 0.00000000 | 0.00390625 | 0.00390625 | 0 |

| ss\_dmarke | 0.01973343 | 0.00390625 | 0.02363968 | 125 |

| ss\_dmedal | 0.00000000 | 0.00097656 | 0.00097656 | 0 |

| ss\_dmember\_comment | 0.00995636 | 0.00195313 | 0.01190948 | 87 |

| ss\_dmembers | 6.73762321 | 0.33203125 | 7.06965446 | 30717 |

| ss\_dmembers\_identity | 0.10503769 | 0.00976563 | 0.11480331 | 784 |

| ss\_dnewyear | 0.00308609 | 0.00195313 | 0.00503922 | 32 |

| ss\_drecharge | 29.13045883 | 10.23925781 | 39.36971664 | 221220 |

| ss\_dsalon\_click | 0.00586700 | 0.00195313 | 0.00782013 | 31 |

| ss\_dsalon\_comment | 0.07812500 | 0.00000000 | 0.07812500 | 213 |

| ss\_dsalon\_content | 0.14062500 | 0.00000000 | 0.14062500 | 653 |

| ss\_dsalon\_ticket | 0.00093079 | 0.00195313 | 0.00288391 | 20 |

| ss\_dsignin | 0.37500000 | 0.00000000 | 0.37500000 | 7177 |

| ss\_dtemplate | 0.15721893 | 0.03808594 | 0.19530487 | 2838 |

| ss\_dwd\_answers | 2.51562500 | 0.00000000 | 2.51562500 | 5420 |

| ss\_dwd\_classify | 0.01562500 | 0.00000000 | 0.01562500 | 12 |

| ss\_dwd\_collection | 0.01562500 | 0.00000000 | 0.01562500 | 2 |

| ss\_dwd\_questions | 1.51562500 | 0.00000000 | 1.51562500 | 3614 |

| ss\_dwget | 1894.69999695 | 13.02929688 | 1907.72929382 | 1187574 |

| ss\_dzhuanti\_bolanhui | 0.00419235 | 0.00195313 | 0.00614548 | 20 |

| click | 0.00000000 | 0.00097656 | 0.00097656 | 0 |

| ss\_account | 0.14062500 | 0.07812500 | 0.21875000 | 1871 |

\+---------------------------+---------------+-------------+---------------+------------+

统计mysql说有表的大小_统计mysql数据库、表大小_MySQL

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Mysql----查看数据库,表占用磁盘大小

相关表元数据统计 | 统计MySQL数据表大小

相关 Mysql查询数据库表大小

相关统计mysql说有表的大小_统计mysql数据库、表大小_MySQL

相关 mysql 数据库查看表的大小

相关 [SQL Server]数据库大小,表大小及行数统计

相关 Mysql统计库表大小sql整理

相关 MySQL 单表大小

相关查看mysql数据库大小、表大小和最后修改时间

相关 MySQL数据库查看相关库、表大小