[MySQL] database と table の size を確認する

MySQL で database と table の size を確認する SQL をご紹介します。

MySQL

全 database の size を確認する SQL

全てのデータベースのサイズを確認するクエリです。

# MB
SELECT table_schema, SUM(data_length+index_length) /1024 /1024 AS MB
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length+index_length) DESC;
 
+--------------------+---------------+
| table_schema       | MB            |
+--------------------+---------------+
| databaseName       | 1376.11649799 |
| mysql              |    0.67155933 |
| information_schema |    0.00878906 |
| performance_schema |    0.00000000 |
+--------------------+---------------+
 
 
# GB
SELECT table_schema, SUM(data_length+index_length) /1024/1024/1024 AS GB
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length+index_length) DESC;
 
+--------------------+----------------+
| table_schema       | GB             |
+--------------------+----------------+
| databaseName       | 1.343863953836 |
| mysql              | 0.000655819662 |
| information_schema | 0.000008583069 |
| performance_schema | 0.000000000000 |
+--------------------+----------------+

table 単位で size を確認する SQL

特定のデータベースにて、テーブル単位でサイズを確認するクエリです。

USE databaseName;
 
SELECT
TABLE_NAME, engine, table_rows AS tbl_rows, avg_row_length AS rlen,  
FLOOR((data_length+index_length)/1024/1024) AS allMB,  # TOTAL SIZE
FLOOR((data_length)/1024/1024) AS dMB,  # DATA SIZE
FLOOR((index_length)/1024/1024) AS iMB   # INDEX SIZE
FROM information_schema.tables  
WHERE table_schema=DATABASE()  
ORDER BY (data_length+index_length) DESC; 
 
 
+-----------------------------+--------+----------+------+-------+------+------+
| TABLE_NAME                  | engine | tbl_rows | rlen | allMB | dMB  | iMB  |
+-----------------------------+--------+----------+------+-------+------+------+
| wp_commentmeta              | MyISAM |  1188843 |  596 |   863 |  824 |   38 |
| wp_comments                 | MyISAM |   312819 | 1067 |   382 |  358 |   24 |
| wp__wsd_plugin_live_traffic | InnoDB |   730696 |  176 |   122 |  122 |    0 |
| wp_posts                    | MyISAM |     2542 | 1910 |     4 |    4 |    0 |
| wp_postmeta                 | MyISAM |    13165 |   55 |     1 |    0 |    0 |
| wp_options                  | MyISAM |      237 | 1410 |     0 |    0 |    0 |
| wp_term_relationships       | MyISAM |     3553 |   21 |     0 |    0 |    0 |
| wp_terms                    | MyISAM |      800 |   46 |     0 |    0 |    0 |
| wp_redirection_logs         | InnoDB |        0 |    0 |     0 |    0 |    0 |
| wp_redirection_items        | InnoDB |        0 |    0 |     0 |    0 |    0 |
| wp_term_taxonomy            | MyISAM |      839 |   39 |     0 |    0 |    0 |
| wp__wsd_plugin_alerts       | InnoDB |       19 | 2586 |     0 |    0 |    0 |
| wp_redirection_modules      | InnoDB |        3 | 5461 |     0 |    0 |    0 |
| wp_redirection_groups       | InnoDB |        2 | 8192 |     0 |    0 |    0 |
| wp_usermeta                 | MyISAM |       29 |  519 |     0 |    0 |    0 |
| wp_sitemeta                 | MyISAM |       26 |  115 |     0 |    0 |    0 |
| wp_site                     | MyISAM |        1 |   28 |     0 |    0 |    0 |
| wp_users                    | MyISAM |        1 |  100 |     0 |    0 |    0 |
| wp_blogs                    | MyISAM |        1 |   48 |     0 |    0 |    0 |
| wp_tweet_urls               | MyISAM |        0 |    0 |     0 |    0 |    0 |
| wp_signups                  | MyISAM |        0 |    0 |     0 |    0 |    0 |
| wp_links                    | MyISAM |        7 |   77 |     0 |    0 |    0 |
| wp_blog_versions            | MyISAM |        0 |    0 |     0 |    0 |    0 |
| wp_registration_log         | MyISAM |        0 |    0 |     0 |    0 |    0 |
+-----------------------------+--------+----------+------+-------+------+------+

stored procedure に SQL を登録する

毎回、長い SQL を実行するのは面倒なのでストアドプロシージャに登録しておくと便利です。

show_table_size() という名前で登録する SQL

delimiter //
CREATE PROCEDURE show_table_size()
BEGIN
SELECT
TABLE_NAME, engine, table_rows AS tbl_rows, avg_row_length AS rlen,  
FLOOR((data_length+index_length)/1024/1024) AS allMB,  # total SIZE
FLOOR((data_length)/1024/1024) AS dMB,  # DATA SIZE
FLOOR((index_length)/1024/1024) AS iMB   # INDEX SIZE
FROM information_schema.tables  
WHERE table_schema=DATABASE()  
ORDER BY (data_length+index_length) DESC; 
END
//
delimiter ;

登録した stored procedure を使う

CALL show_table_size;

以上です。

参考情報