スポンサーリンク

MySQLでDBに設定されているインデックスを全て取得しCREATE INDEX構文を出力するSQL

AWSのDatabase Migration Serviceを利用してEC2からRDSにデータ移行したときに各テーブル内のインデックス情報が移行していなかったので、移行元のDBからCREATE INDEX構文を出力するSQLを作成しました。

テーブルに設定されているインデックスは通常

show index from "[table_name]";

といったクエリでテーブルごとに確認しますが、information_schemaからも取得できます。

select
 table_schema,
 table_name,
 index_name,
 column_name,
 seq_in_index
from
 information_schema.statistics
where
 table_schema = "[database_name]"
and index_name != "PRIMARY"
;

上記のSQLをベースにCREATE INDEX構文のSQLを出力するのが以下です。

select
  CONCAT(
    "CREATE INDEX ",
    t1.index_name,
    " ON ",
    t1.table_name, 
    "(",
    (
      select
        GROUP_CONCAT(t2.column_name separator ', ')
      from
        information_schema.statistics as t2
      where
        t2.index_name = t1.index_name
      and	t2.table_name = t1.table_name
      order by
        t2.table_name,
        t2.index_name,
        t2.seq_in_index
    ),
    ");"
  ) as create_index_query
from
  information_schema.statistics as t1
where
  table_schema = "[database_name]"
and	index_name != "PRIMARY"
group by
  t1.table_name,
  t1.index_name
;

手ごろなデータベースがなかったのでwordpressのデータベースで実行しました。

最初マルチカラムインデックスのことを忘れていてカラムの並び順が保障されないようなSQLを作ってしまいましたが、上記のSQLはサブクエリ内のorder byで設定しているので私が知っている限りのインデックス構文には対応していると思います。移行するテーブルが1つ2つであれば手作業で作成してしまっても問題ないですが、大量にある場合はSQLで生成したほうが早いし間違いが起きにくいと思います。めったに使うことはないと思います。

タイトルとURLをコピーしました