你的位置:首页 > 数据库

[数据库]统计mysql数据库、表大小


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: 401473536Max_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 |+---------------------------+---------------+-------------+---------------+------------+