スポンサーリンク

MySQL 全テーブルの統計情報を再作成するの作成方法

スポンサーリンク

MySQLではテーブルに（行の10%を超える）変更が加えられた場合、該当テーブルの統計情報（カーディナリティ）が再作成されます（構成オプションinnodb_stats_auto_recalcがONの場合。デフォルトでON）。今回は、手動で全テーブルの統計情報を再計算する方法を紹介します。MySQLには全テーブルの統計情報を再作成するコマンドは無く、１テーブルずつ「ANALYZE TABLE テーブル名」を実行する必要があります。今回紹介する方法はストアドプロシージャで全テーブル名を取得して、ANALYZE TABLEを実行する方法です。

全テーブルの登録情報を再作成するストアドプロシージャ

ストアドプロシージャはこんな感じになります。

CREATE PROCEDURE PROC_ANALYZE_TABLE()
BEGIN

    DECLARE DONE INT DEFAULT FALSE;
    DECLARE SQL_COMMAND VARCHAR(255);

    -- 全テーブルを取得してANALYZE TABLE文を作成するカーソル
    DECLARE CUR_TAB CURSOR FOR
    SELECT CONCAT_WS(' ', 'ANALYZE TABLE',  T1.TABLE_NAME)
      FROM INFORMATION_SCHEMA.TABLES AS T1
     WHERE T1.TABLE_SCHEMA = DATABASE()
       AND T1.TABLE_TYPE = 'BASE TABLE'
    ;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;

    OPEN CUR_TAB;

    SET DONE = FALSE;
    read_loop: LOOP
        FETCH CUR_TAB INTO SQL_COMMAND;
        
        IF DONE THEN
            LEAVE read_loop;
        END IF;

        -- カーソルで取得したANALYZE TABLE文を動的SQLで実行
        SET @stmt = SQL_COMMAND;
        PREPARE STMT FROM @stmt;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;

    END LOOP;

    CLOSE CUR_TAB;

END;

中身は以下の事を行っています。

  1. INFORMATION_SCHEMA.TABLESより現在のデータベースに登録されているテーブル名を取得し、「ANALYZE TABLE」 + テーブル名の文字列を作成
  2. 作成した文字列を動的SQLにて実行

実行

ストアドプロシージャなので、実行する為にはストアドプロシージャをDBに登録する必要があります。ただ、１度実行すると不要な為、ストアドプロシージャの作成=>ストアドプロシージャの実行=>ストアドプロシージャの削除を行うスクリプトを作成します。

以下のコードをPROC_ANALYZE_TABLE.sqlの名前で保存してください。

DELIMITER ;;
-- ストアド削除
DROP PROCEDURE IF EXISTS PROC_ANALYZE_TABLE;
;;

-- ストアド作成
CREATE PROCEDURE PROC_ANALYZE_TABLE()
BEGIN

    DECLARE DONE INT DEFAULT FALSE;
    DECLARE SQL_COMMAND VARCHAR(255);

    -- 全テーブルを取得してANALYZE TABLE文を作成するカーソル
    DECLARE CUR_TAB CURSOR FOR
    SELECT CONCAT_WS(' ', 'ANALYZE TABLE',  T1.TABLE_NAME)
      FROM INFORMATION_SCHEMA.TABLES AS T1
     WHERE T1.TABLE_SCHEMA = DATABASE()
       AND T1.TABLE_TYPE = 'BASE TABLE'
    ;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;

    OPEN CUR_TAB;

    SET DONE = FALSE;
    read_loop: LOOP
        FETCH CUR_TAB INTO SQL_COMMAND;
        
        IF DONE THEN
            LEAVE read_loop;
        END IF;

        -- カーソルで取得したANALYZE TABLE文を動的SQLで実行
        SET @stmt = SQL_COMMAND;
        PREPARE STMT FROM @stmt;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;

    END LOOP;

    CLOSE CUR_TAB;

END;
;;

-- ストアド実行
CALL PROC_ANALYZE_TABLE();
;;

-- ストアド削除
DROP PROCEDURE IF EXISTS PROC_ANALYZE_TABLE;
;;

DELIMITER ;

※念のため、最初にストアドプロシージャが登録されていたら削除する処理をいれています。

PROC_ANALYZE_TABLE.sql が置いてあるフォルダで以下のコマンドを実行します。

mysql -u<<ユーザ名>> -p<<パスワード>> -h <<DBサーバー名>> -P <<ポート番号>> -D <<DB名>> < PROC_ANALYZE_TABLE.sql

実行例

DB_AデータベースにはTABLE_AとTABLE_Bの２つのテーブルが登録されています。

因みにPowerShellの場合、標準入力の方法が異なるので以下の様なコマンドになります。

cat PROC_ANALYZE_TABLE.sql | mysql -u<<ユーザ名>> -p<<パスワード>> -h <<DBサーバー名>> -P <<ポート番号>> -D <<DB名>>

実行例

注意点

紹介したコマンドはパスワードを直接コマンドに埋め込んでいますのでコマンドの履歴に残ります。履歴に残るのが嫌な場合は「-p<<パスワード>>」を「-p」に変更してください。実行後、パスワードの入力が求められるようになり、コマンドの履歴には残りません。

スポンサーリンク

コメント