Statistics MySQL database, table size

1. View all library sizes under the DB instance, and the result is in megabytes

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. View the size of each library under this instance

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. View the size of a single library

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. View the status of a single table

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. View the status of all tables under the library

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 |+---------------------------+---------------+-------------+---------------+------------+

　

Statistics MySQL database, table size