[Go] "MySQL monitor" view MySQL library size, table size, index size

This article transferred from: http://blog.sina.com.cn/s/blog_4c197d420101fbl9.html

View the size of all libraries mysql> use information_schema;database changedmysql> select concat (Round (sum (data_length/1024/1024), 2 ), ' MB ') as data  from tables;+----------+| Data     |+----------+| 104.21MB |+----------+1 row in Set (0.11 sec)   View the size of the specified library mysql> select concat (Round (sum (data_length/1024/1024), 2) , ' MB ') as data  from TABLES where table_schema= ' Jishi '; +---------+| Data    |+---------+| 26.17MB |+---------+1 row in Set (0.01 sec)   View the size of the specified table for the specified library mysql> select concat (Round (sum (data_length/1024/1024), 2), ' MB ') as data  from TABLES where table_schema= ' Jishi ' and table_name= ' A_ya '; +--------+| Data   |+--------+| 0.02MB |+--------+1 row in Set (0.00 sec)   View the index size of the specified library mysql> SELECT CONCAT (ROUND (SUM (index_length)/(1024*1024), 2 ), ' MB ') as ' total Index Size ' from TABLES  where table_schema = ' Jishi ';  +------------------+| Total Index Size |+------------------+| 0.94 MB           |+------------------+1 row in Set (0.01 sec)   View the index size of the specified table for the specified library  mysql> SELECT CONCAT (ROUND (SUM (index_ Length)/(1024*1024), 2), ' MB ') as ' total Index Size ' from TABLES  where table_schema = ' test ' and Table_name= ' A_yuse R ';  +------------------+| Total Index Size |+------------------+| 21.84 MB         |+------------------+1 row in Set (0.00 sec) Mysql> Show CREATE TABLE test.a_yuser\g;*************************** 1. Row ***************************       table:a_yusercreate table:create Table ' A _yuser ' (   ' email ' varchar () NOT NULL default ',   ' user_name ' varchar "NOT null default ', &nbs P; key ' cc ' (' Email ' (5)),   key ' CCB ' (' User_name ' (5)),   key ' CCBC ' (' Email ' (5), ' User_name ' ( 5))) Engine=myisam DEFAULT charset=utf81 row in Set (0.00 sec)  error: no query specified mysql> Select C Ount (*) from test.a_yuser;+----------+|COUNT (*) |+----------+|  1073607 |+----------+1 row in Set (0.00 sec)  mysql>  View the case in a library ...mysql>  select CONCAT (table _schema, '. ', table_name) as ' table name ', CONCAT (ROUND (table_rows/1000000,4), ' M ') as ' number of rows ', CONCAT (ROUND (data _length/(1024*1024*1024), 4), ' G ') as ' Data size ', CONCAT (ROUND (index_length/(1024*1024*1024), 4), ' G ') as ' Index Size ', CONCAT (ROUND ((data_length+index_length)/(1024*1024*1024), 4), ' G ') as ' total ' from INFORMATION_SCHEMA. TABLES WHERE table_schema like ' test ', +---------------+----------------+-----------+------------+---------+| Table Name    | Number of Rows | Data Size | Index Size | Total   |+---------------+----------------+-----------+------------+---------+| TEST.A_BR     | 0.4625M        | 0.0259G   | 0.0171G    | 0.0431G | | TEST.A_SKUCLR | 0.7099M        | 0.0660G   | 0.0259G     | 0.0919G | | Test.a_yuser  | 1.0736M        | 0.0497G   | 0.0213G    | 0.0710G | | Test.test     | 0.0000M        | 0.0000G   | 0.0000G    | 0.0000G |+---------------+----------------+-----------+------------+---------+4 rows in Set (0.13 sec)

[Go] "MySQL monitor" view MySQL library size, table size, index size