[MySQL] database と table の size を確認する
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; |
以上です。