Webサイト制作コースのお申し込みはこちら Webサイト制作コースのお申し込みはこちら

MySQLのインデックスの使い方について、サンプルSQLを紹介しながらまとめています。

以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

MySQLのインデックス作成方法

MySQLのインデックス作成の構文は以下のとおりです。

ALTER TABLE employees ADD INDEX `birth_date_index` (`birth_date`);

上記のSQLは、employees(社員)テーブルのbirth_date(誕生日)カラムにbirth_date_indexという名前でインデックスを設定します。

適切にインデックスを設定することによって、テーブルの検索やソートのスピードが速くなります。

SELECT 	first_name, 
	last_name, 
	birth_date 
FROM 	employees a 
WHERE 	birth_date = (
		select 	min(birth_date) 
		from 	employees b
	)

上記は、employees(社員)テーブルから、誕生日が最小=最高齢の社員のfirst_name(姓名の名)、last_name(姓名の姓)、birth_date(誕生日)を取得するSQLです。

実行するとこうなります。

実行結果

インデックスを指定していない場合は「6 total, Query took 0.1616 seconds.」と表示されますが、上記インデックスを設定すると「6 total, Query took 0.0004 seconds.」となりました。

404倍の速度差が出ています。

インデックスの種類

MySQLではPRIMARY、UNIQUE、INDEX、FULLTEXT、SPATIALの5種類のインデックスが設定可能です。

SQL内の「ADD INDEX」の部分を「ADD UNIQUE」などに差し替えることでインデックス種類の指定ができます。

PRIMARYは、プライマリキーのインデックスです。プライマリキー指定をすると自動的に設定されます。

UNIQUEは、ユニークキー、外部キー用のインデックスです。

INDEXは一般のインデックスです。基本は「ADD INDEX」でインデックス指定すると良いでしょう。

FULLTEXTは、全文検索用のインデックスです。不定長のtextカラムに設定すると、全文検索の速度が大幅に改善されますが、インデックスに使用する領域を多く使うという欠点もあります。

SPATIALは、空間データ用のインデックスです。空間データとは、都市や山などの立体情報や町や市などの範囲情報で、これらを格納するのに向いている空間データ型に対するのが空間インデックスです。MySQLでは、空間データのソリューション開発に関わる 250 以上の企業、機関、および大学の国際的なコンソーシアムOpen Geospatial Consortium (OGC) の仕様に沿って、空間データが実装されています。

MySQLに設定済みのインデックスを確認するには

show index from employees

実行するとこうなります。

実行結果

一見してもインデックスのタイプがわからないので、インデックス名(Key_name)にインデックス種類がわかるような命名規則で名前をつけるか、コメントに記載するのが良いでしょう。

MySQLのインデックスの使い方

複数のインデックスを設定するには

下記の要領で複数カラムにインデックスを指定できます。

ALTER TABLE `employees` 
ADD INDEX `birth_hire_index` (`birth_date`, `hire_date`);

あ上記SQLは、employees(社員)テーブルに、birth_date、hire_dateの順でbirth_hire_indexという名前をインデックスを設定するSQLです。

インデックスの再構築方法

照合順序の修正をおこなった場合など、インデックスを再設定するには、単純にインデックスをドロップしてから再作成します。

あまりにもインデックスの数が多すぎて手動で対応しきれない場合は、データベースをリロードする方法もあります。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 2.11.4 テーブルまたはインデックスの再作成または修復

コマンドラインから以下を実行します。

mysqldump データベース名 > dump.sql
mysql データベース名 < dump.sql

いったんデータベースを全削除してから、データを含めて再作成するため、リロード中はデータベースへのアクセスはできなくなります。

データベースサイズが大きすぎるとファイルにダンプすることができないケースがあるため、比較的サイズが小さめのデータベースに対しては有効です。

mysqldumpに-dオプションを指定すると、構造情報のみがダンプできるため、ダンプ内容のうちインデックス部分のみを編集して、インデックス再生成用スクリプトにする方法もあります。

インデックスを削除するには

以下の構文で、インデックスを削除できます。

ALTER TABLE employees DROP INDEX firstname_index;`

上記SQLは、employees(社員)テーブルから、firstname_indexという名前のインデックスをドロップします。

インデックスが効いているかどうか確認するには

「インデックスを設定したはずなのに、SQLが遅い…」という場合は、せっかく設定したインデックスが使われていないケースがあります。

explainを実行して、インデックスが使われているかどうかを確認しましょう。SQLの先頭に「explain」をつけて実行するだけです。

explain SELECT 	first_name, 
	last_name, 
	birth_date 
FROM 	employees a 
WHERE 	birth_date = (
		select 	min(birth_date) 
		from 	employees b
	)

実行すると、こうなります。

メインクエリ(select_type=PRIMARY)の検索タイプはref(参照型)で、使用可能なキー(possible_keys)はbirth_date_index、最適化の結果使用されたキー(key)はbirth_date_indexということがわかります。サブクエリのExtraに表示されている「Select tables optimized away」は、「最適化するまでもない」となります。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット

簡易的な見方としては「type」が「ALL」になっていたら、テーブルフルスキャンでインデックスが使われていない遅いクエリなので、見直しが必要だと覚えておくと良いでしょう。

まとめ

ポテパンダの一言メモ
  • インデックスを設定すると、クエリによっては検索速度が大幅に改善する
  • インデックスは5種類ある。一般のインデックスには「ADD INDEX」を使用する
  • 設定済みインデックスは、show index from テーブル名で確認できる
  • インデックスの再構築は、データベースのダンプを利用する
  • インデックスが有効に働いているかどうかは、explainで確認可能

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。