mysql(mariadb)のテーブルサイズとインデックスを一覧で見る
小ネタ過ぎてエントリに上げるのもアレですが、備忘録兼ねて( ・`ω・´)
テーブルサイズ一覧
SELECT table_name, table_rows AS "rows_count", ROUND(data_length/1024/1024,1) AS "table size(MB)", ROUND(index_length/1024/1024,1) AS "index size(MB)" FROM information_schema.tables ORDER BY data_length DESC
table_name | rows_count | table size(MB) | index size(MB) |
---|---|---|---|
articles | 327540 | 10.5 | 5.5 |
users | 2 | 0.0 | 0.0 |
schema_migrations | 2 | 0.0 | 0.0 |
dummy | 0 | 0.0 | 0.0 |
インデックスのサイズも見れる。まあそこそこ使う。
インデックス情報一覧
SELECT * FROM information_schema.statistics WHERE table_schema = "sandbox" ORDER BY table_name, index_name
TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
def | sandbox | articles | 1 | sandbox | ix_user_id | 1 | user_id | A | 2 | NULL | NULL | YES | BTREE | ||
def | sandbox | articles | 0 | sandbox | PRIMARY | 1 | id | A | 327540 | NULL | NULL | BTREE | |||
def | sandbox | dummy | 0 | sandbox | PRIMARY | 1 | id | A | 0 | NULL | NULL | BTREE | |||
def | sandbox | schema_migrations | 0 | sandbox | unique_schema_migrations | 1 | version | A | 2 | NULL | NULL | BTREE | |||
def | sandbox | users | 0 | sandbox | PRIMARY | 1 | id | A | 2 | NULL | NULL | BTREE |
このデータの見方はそのうち