统计mysql数据库、表大小_MySQL
- WBOYOriginal
- 2016-05-30 17:10:05970Durchsuche
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; +--------------------+---------------+--------------+---------------+ | table_schema | data_length | index_length | sum | +--------------------+---------------+--------------+---------------+ | 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; +--------------------+---------------+---------------+-------------+--------+------------+ | table_schema | total_mb | data_mb | index_mb | tables | today | +--------------------+---------------+---------------+-------------+--------+------------+ | 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'; +-----------+------------------+-----------+------------+ | data_size | max_data_size | data_free | index_size | +-----------+------------------+-----------+------------+ | 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'; +---------------------------+---------------+-------------+---------------+------------+ | table_name | data_mb | index_mb | all_mb | table_rows | +---------------------------+---------------+-------------+---------------+------------+ | 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 | +---------------------------+---------------+-------------+---------------+------------+
Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：mysql学习记录（二十五）--mysql日志_MySQLNächster Artikel：C#实现MySQL数据库中的blob数据存储_MySQL