MySQL で database と table の size を確認する SQL をご紹介します。
全 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;
以上です。