end0tknr's kipple - 新web写経開発

http://d.hatena.ne.jp/end0tknr/ から移転しました

mysqlのinformation_schema.tablesによるテーブルサイズの表示

FAQですね。

information_schema.tables とは

mysql> desc information_schema.tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | YES  |     | NULL    |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(80)         | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+

information_schema.tablesによる table sizeの表示

mysql> select  
    -> table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,  
    -> floor((data_length+index_length)/1024/1024) as allMB,  
    -> floor((data_length)/1024/1024) as dMB,  
    -> floor((index_length)/1024/1024) as iMB  
    -> from information_schema.tables  
    -> where table_schema='$db_name'
    -> order by (data_length+index_length) desc;  
+-------------------------------+--------+----------+--------+-------+------+------+
| table_name                    | engine | tbl_rows | rlen   | allMB | dMB  | iMB  |
+-------------------------------+--------+----------+--------+-------+------+------+
| h_juchuu_meisai               | InnoDB |  1192379 |    166 |   189 |  189 |    0 |
| juchuu_meisai                 | InnoDB |   832510 |    167 |   132 |  132 |    0 |
| osaka_tanka                   | InnoDB |   463869 |    153 |    67 |   67 |    0 |
| hachuu_meisai                 | InnoDB |   357524 |    160 |    54 |   54 |    0 |
| h_hachuu_meisai               | InnoDB |   286451 |    198 |    54 |   54 |    0 |
| tokyo_tanka                   | InnoDB |   324704 |    154 |    47 |   47 |    0 |
| juchuu                        | InnoDB |   106038 |    430 |    47 |   43 |    3 |
| h_juchuu                      | InnoDB |   100298 |    413 |    43 |   39 |    3 |
| heim_tei_info_2               | InnoDB |   101526 |    325 |    31 |   31 |    0 |
| juchuu_summary                | InnoDB |    98031 |    326 |    30 |   30 |    0 |
| hachuu                        | InnoDB |   129771 |    166 |    24 |   20 |    3 |
|  :                            |  :     |       :  |     :  |     : |    : |    : |