MySQLでインデックスの追加・確認・削除を行う方法

１．１　インデックスとは

インデックスとは、データベース内から特定のデータを検索しやすくするための仕組みです。

インデックスにはいくつか方式がありますが、MySQLで使われているのはB-Treeという方式です。B-Treeの仕組みをざっくりと説明すると、インデックスを貼られたカラムは検索しやすいようツリー構造に加工され、テーブル本体とは別の場所に保存されます。検索する際にはその加工したデータの方を見に行くことで、高速化を図っているのです。

１．２　インデックスを貼るメリット

インデックスを貼るメリットは、先ほど述べたとおり検索の高速化です。適切なカラムにインデックスを貼っておくことにより、インデックスなしの状態よりかなり高速化することができる場合があります。ただしインデックスを貼る対象はなんでもいいというものではなく、どんなSQL文を多用する予定であるかを考え、実行計画を確認しながらきちんと検討する必要があります。

１．３　インデックスを貼るデメリット

インデックスは、前述のとおりテーブル本体とは別の領域に保存されています。つまりインデックスをつけることで、データの保存に必要な領域が増えることになります。

また、新しくデータを追加した際やデータを更新した際には、インデックスも追加・更新する処理が行われることになります。そのため検索系のSQLは高速化できますが、レコードの追加・更新については少し遅くなるというデメリットがあります。

１．４　インデックスを貼る対象を探す「EXPLAIN」

「EXPLAIN」は、実行計画を取得するコマンドです。実行計画を調べたいSQL文の先頭に追加するだけで使用できます。

「possible_key」には、そのSQLを実行する際に有効なインデックスを貼る候補に挙がっているカラム名が表示されます。「key」には、現在インデックスが貼られているカラム名が表示されます。今回実行したデータベースはごく単純でレコードも少ないため、「possible_keys」はNULLとなっています。

２．１　現在のインデックスを確認する「SHOW INDEX」

すでに貼られているインデックスを確認するには、SHOW INDEX文を使います。

２．２　実行例と実行結果

データベース名「db_01」にある「USER_DATA」というテーブルに対して、インデックスの参照を実行してみます。

このテーブルにはまだインデックスは設定されていないため、図のように「PRIMARY（主キー）」のみが表示されました。

３．１　インデックスを追加する「ADD INDEX」

インデックスを追加するには、ALTER TABLE文の「ADD INDEX」を使用します。
基本の書式は、次の通りです。

３．２　実行例と実行結果

先ほどのテーブル「USER_DATA」のカラム「USER_ID」に、インデックスを追加します。

実行後にインデックスを参照すると、「PRIMARY」の下に「index01」が追加されていました。

３．３　複合インデックスを追加する場合

複合インデックスを追加したい場合、対象となるカラム名をカンマで区切って並べるだけでOKです。

３．４　実行例と実行結果

同様に、テーブル「USER_DATA」のカラム「USER_ID」と「USER_NAME」の２つに、複合インデックスを追加します。

インデックスの参照を実行すると、カラム名「USER_ID」と「USER_NAME」として、「index02」が合計２行追加されました。

４．１　インデックスを削除する「DROP INDEX」

インデックスを削除する場合は、ALTER TABLE文の「DROP INDEX」を使用します。

４．２　実行例と実行結果

前項で作成した「index01」を削除するコマンドは、次のようになります。

このように「index01」は削除され、「index02」のみが残りました。

インデックスはSQLチューニングの第一歩ですが、実行計画を使いこなすのはなかなか難しいかと思います。その場合はSQLを実行した際に表示される「検索に要した時間」をチェックしながらインデックスを貼ったり消したりしてみるのが単純ですが一番簡単な方法かと思いますので、試してみて下さい。

次回はMySQLで使用できる文字列結合の方法についてご紹介したいと思います。
よければ合わせてご覧下さい。

関連記事：MySQLで使える文字列結合方法