【MySQL】あるスキーマの中の各テーブルがどれぐらい容量を使っているかを調べる方法

　テストデータを作る時など、なるべ必要最低限の小さなデータベースを作りたい時があります。この記事ではそういった時に使える容量の確認方法を紹介します。

　容量を確かめるSQLは次です。

SELECT 
    table_name, 
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb, -- 総量（MB） 
    ROUND(data_length / 1024 / 1024, 2) AS  data_mb,  -- レコード（MB）
    ROUND(index_length / 1024 / 1024, 2) AS index_mb -- インデックス（MB）
FROM information_schema.TABLES 
WHERE table_schema = '調べたいスキーマの名前'
ORDER BY (data_length + index_length) DESC;

　これを実行すると次のようにテーブル名と使っている容量が分かります。

　注意点として、この値はクエリを実行した時点で直接計算したり測ったりした値ではないということです。現在の正確な値を得たいのであれば次クエリでテーブルの統計情報を更新する必要があります。

ANALYZE TABLE テーブル名; -- テーブル単位で統計情報を更新する
-- スキーマ内のテーブル全体でANALYZE TABLEしたいのであれば
-- 次クエリでANALYZE TABLEクエリの文字列を生成して、それをコピペすると楽
SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '調べたいスキーマの名前';
-- 実行結果
- -ANALYZE TABLE 調べたいスキーマの名前.table1;
- -ANALYZE TABLE 調べたいスキーマの名前.table2;
- -ANALYZE TABLE 調べたいスキーマの名前.table3;
-- ...

　こうすると何時でも各テーブルの容量が分かり、どこの容量を減らしやすそうかなどの目安がつけれます。

　この一連の流れをLaravelのコマンドに落とし込むと次のようになります。

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;

class MySqlShowSize extends Command
{
    protected $signature = 'mysql:show-size
                            {schemaName : スキーマ名}
                            {--skip-analyze : ANALYZE TABLEをしない}';
    protected $description = '指定したスキーマ内のテーブルのサイズ情報を表示します。';

    public function handle(): int
    {
        // スキーマ名をコマンドライン引数から取得
        $schemaName = $this->argument('schemaName');

        // information_schema.TABLES から ANALYZE TABLE コマンドを生成し、各テーブルに対して実行
        \DB::query()->selectRaw("CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') as query")
            ->from('information_schema.TABLES')
            ->where('TABLE_SCHEMA', $schemaName)
            ->get()
            ->each(fn($sql) => \DB::statement($sql->query));

        // スキーマ内の各テーブルのサイズ情報を取得するクエリを実行
        $sizeList = \DB::query()->selectRaw(implode(', ', [
            'table_name', // テーブル名
            'ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb', // 総量（MB）
            'ROUND(data_length / 1024 / 1024, 2) AS data_mb', // データサイズ（MB）
            'ROUND(index_length / 1024 / 1024, 2) AS index_mb', // インデックスサイズ（MB）
        ]))
            ->from('information_schema.TABLES')
            ->where('table_schema', $schemaName)
            ->orderByRaw('(data_length + index_length) DESC') // サイズの大きい順に並べ替え
            ->get()
            ->map(fn(\stdClass $r) => (array)$r); // stdClass を配列に変換

        // テーブルのサイズ情報を整形して表示
        $this->table(['table_name', 'total_mb', 'data_mb', 'index_mb'], $sizeList);

        // コマンドが正常終了したことを示す
        return 0;
    }
}
/************************************************
 * 実行例
 ************************************************/
/*
$ php artisan mysql:show-size test_db --skip-analyze
+--------------------------------------------------------------+----------+---------+----------+
| table_name                                                   | total_mb | data_mb | index_mb |
+--------------------------------------------------------------+----------+---------+----------+
| migrations                                                   | 0.05     | 0.05    | 0.00     |
| accounts                                                     | 0.05     | 0.02    | 0.03     |
+--------------------------------------------------------------+----------+---------+----------+
 */