MYSQL INDEXのまとめ

概要

大規模なデータを管理するためのMYSQL-INDEXについて必要な情報をまとめてみます

PRIMARYKEY / UNIQKEY / INDEXについて

PRIMARYKEYとはそのテーブル内において重複が許されないもので、自動的にINDEXが張られる。

UNIQKEYとはそのテーブル内に置いて重複を許さない。ただし、NOT NULLにしなければNULLの重複は認める。

INDEXとは特定の値を持つレコードを高速に検索するための木構造データ。INDEXを張らないとテーブル全体のデータを検索してしまう。最適化されたINDEXを利用するとテーブルデータを全く参照せずにデータを返却できる。

まとめるとPRIMARYKEY = UNIQKEY + INDEX

複合INDEXについて

複数のカラムに対してのINDEXを作成する事。単一のINDEXより高速な検索ができる。

複合INDEXを利用する場合はカラム順番が重要。例えば複合INDEXの第一KEYがカラムA,第二KEYがカラムBとなっている時、検索条件にAが含まれない場合は当然ながらINDEXは使用されない。これは複合INDEXの数が3つ以上の場合にも同じ事が言え、A,B,Cの順番の複合INDEXの場合、A,Cしか検索条件に含まれない場合はAを指定した時のみのパフォーマンスと同等になる。

部分INDEX

INDEXのデータ容量が気になる場合は部分INDEXを用いることができる。たとえば文字列の前から数ByteまでをINDEXとして利用するなど。

部分INDEXの場合、部分一致した複数のレコードが一致してしまう可能性がある。

INDEXのデータ構造

INDEXの木構造にもいくつか種類がある。InnoDBではBtree,MyISAMでもデフォルトではBtreeを採用。

BtreeINDEX : 一般的なINDEX構造。データのバランスが取れている木構造。

HashINDEX : Hashを利用したINDEX構造

RtreeINDEX : 幾何データ型専用のINDEX構造

INDEXが使われるケース

参考:http://slashdot.jp/journal.pl?op=display&uid=4&id=26710

フィールド値を定数と比較するとき (where name = 'hogehoge')

フィールド値でJOINするとき (where a.name = b.name)

フィールド値の範囲を求めるとき (<,>,between)

LIKE句が文字列から始まるとき (where name like 'hoge%')

min(),max() (複数要素indexの同一first fieldでsecond fieldのmin,max でも有効)

文字列のプレフィクスを基にしたorder by,groupy by

WHERE句の全てのフィールドがインデックスの一部の場合はDBをまったく参照せずに結果が返ってくる

INDEXの選択性(カーディナリティ)

PRIMARYKEYの選択性は最強

行数に対して値のパターンが少ないカラムは選択性が低い(性別など)。逆にユニークに近いものは選択性が高い(更新時間など)。~~WHEREの指定は選択性に優れているものから指定する。選択性が低いものはWHEREで繋げるなら後回しに。~~※これは間違いです。参考:Mysqlのオプティマイザhttp://dev.mysql.com/doc/refman/5.1/ja/controlling-optimizer.html

WHERE句で一緒に利用されるカラムを複合INDEXとして指定する。複合INDEXにする場合は順番に気をつける事。

WHERE句で単独で利用されるカラムはINDEXの最初に指定する。

INDEX使用の注意点

INDEXを利用するとDisk容量が大きくなってしまう

INDEXを利用すると参照は速くなっても更新が遅くなる？

MYSQLでは1Query実行時に1テーブルにつき1INDEXしか使用できない。よって複数のカラムのINDEXとして使用した場合は上に上げた複合INDEXを利用する。

SELECT * などのように取得したいカラムをワイルドカードは原則使わない。

LIKE検索がワイルドカードで始まる場合はINDEXは使われない。特定文字列で始まる場合はINDEXが利用される。

通常INDEXはORDER BYに対しては使われない

Havingを利用するとINDEXが使われない

WHEREとORDER BYのフィールドが違う時にはどちらしか使われない

WHEREの中に関数を使うとINDEXが利用されない

ORDER BYにINDEXが指定されている場合、INDEX内部でデータが既にsortされているので高速

NULLはINDEXに含まれない

explainコマンド

explainはSQLの解析を行うコマンド。以下は実行例
explain SELECT * FROM users AS u LEFT JOIN devices AS d on u.devices_id = d.id WHERE u.user_name = 'yutakikuchi';
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | u     | ref    | index         | index   | 47      | const             |    1 | Using where |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY,index | PRIMARY | 4       | mydb.u.devices_id |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
重要な項目はselect_type, type, possibile_keys, Extra
表示されている項目の説明参考:http://nippondanji.blogspot.com/2009/03/mysqlexplain.html

id/ select_type : どのような種類のQUERYでテーブルが参照されているかを示す

SIMPLE : UNIONやSUBQUERYが無い場合やJOINを行う時。

PRIMARY : UNIONで最も最初のSELECT,SUBQUERYで最も外側にあるSELECT,Mysql6.0以降ではSemi-Join最適化が行われた場合

SUBQUERY : SUBQUERYと親QUERYで相関関係のないSUBQUERYの場合

DEPENDENT SUBQUERY : 相関SUBQUERYのSUBQUERY部分

UNCACHEABLE SUBQUERY : 実行するまで結果が分からないSUBQUERY

DERIVED : FROM句においてSUBQUERYを実行した場合

UNION/UNION RESULT : UNIONを利用した場合

DEPENDENT UNION : SELECTがUNIONの一部でなおかつ2番目以降のSELECTであり、かつSUBQUERYで用いられている場合

UNCACHEABLE UNION : UNCACHEABLE SUBQUERYがUNIONになっている場合、2番目以降のUNIONがUNCACHEABLE UNIONとなる。非常に遅い。

参考はhttp://www.mysqlpracticewiki.com/index.php/Select_type

table : 利用されているテーブル名

type : 対象のテーブルに対してどのような方法でアクセスするかを示す。

const : PRIMARYKEYかUNIQUEIDEXによる検索アクセス。最速。

eq_ref : JOINにおいてPRIMARYKEYかUNIQUEINDEXが利用される時のアクセスタイプ。

ref : UNIQUE(PRIMARY/UNIQUE)でないインデックスを使って等価検索を行った時のタイプ。

index : インデックス全体のスキャン。遅い。改善すべき。

ALL : テーブル全体のスキャン。インデックスが使用されていない。改善すべき。

possible_keys : 利用可能なINDEXとしてのKEY一覧

key :　選択されたKEY。

key_len : 選択されたKEYの長さ。KEYの長さが短いほど高速。

ref : 検索条件でkeyと比較されているカラムの種類。JOIN実行時には結合先の検索条件として利用されているカラムが表示。

rows : 取得が見込まれる行数。

Extras

Extrasカラムで表示されてほしい項目:using index, using where

Extrasカラムで表示されてほしくない項目:using filesort,using temporary

using filesort : レコードをソートして取り出す方法を決定するには、MySQL はパスを余分に実行しなくてはならないことを示す。join type に従ってすべてのレコードをスキャンし、WHERE 条件に一致する全てのレコードに、ソートキー + 行ポインタを格納て、ソートは実行される。その後キーがソートされる。最後に、ソートされた順にレコードが取り出される。

using temporary : クエリの解決に MySQL で結果を保持するテンポラリテーブルの作成が必要であることを示す。これは一般に、GROUP BY を実行したカラムセットと異なるカラムセットに対して ORDER BY を実行した場合に発生する。

using index : インデックスツリーの情報のみを使用してカラム情報がテーブルから取り出され、実際のレコードを読み取るその後の検索を実行する必要がないことを示す。これは、そのテーブルで使用されたカラムがすべて同一インデックスの構成部分である場合に実行できる。

using where : 次のテーブルとの一致が調べられるレコードまたはクライアントに送信されるレコードの限定に WHERE 節が使用されることを示す。この情報がなく、テーブルの型が ALL または index である場合はクエリが正常に実行されないことがある（テーブルのすべてのレコードの取得や検査を意図していない場合）。

参考 : http://www.mysqlpracticewiki.com/index.php/Extra_field

ER図

本記事のSQLテストのために携帯端末情報とその所有者を管理するテーブルを作成する。 ER図は次の通り

利用想定SQL

利用が想定されるSQL。取得したい項目は仮でワイルドカードにしている

SELECT * FROM users WHERE user_name = 'yutakikuchi';
SELECT * FROM users WHERE id = 1;
SELECT * FROM users AS u LEFT JOIN devices AS d on u.devices_id = d.id WHERE u.user_name = 'yutakikuchi';
SELECT * FROM devices WHERE maker = 'SHARP' and model = '912SH';
SELECT * FROM devices WHERE id = 1;

INDEXの作成と状態確認

上で挙げたSQLに対してのIndexを考える。

where句で利用されるカラムに対してはindexを張る

WHERE句がPRIMARYKEYのみの場合はINDEXを張らない

usersテーブルはuser_name,devicesテーブルはmaker,modelの二つ。

indexを張る
ALTER TABLE users ADD INDEX 'index_name' ( user_name )
ALTER TABLE deviced ADD INDEX 'index_name' ( maker,model )
indexの状態を調べる
SHOW INDEX FROM users;
SHOW INDEX FROM devices;
indexの削除
DROP INDEX 'index_name' ON users;
DROP INDEX 'index_name' ON devices;

INDEXのテスト

explainコマンドの結果
- indexを張ったカラムに対するQUERYではselec_typeがSIMPLE
- indexを張ったカラムに対するQUERYではtypeがref,eq_ref
- indexを張ったカラムに対するQUERYではpossible_keys/keysに指定したIndexが利用されている
- indexを張ったカラムに対するQUERYではExtraにUsing whereとなっている
- indexを張ったカラムに対するQUERYでは最適化されていると判断できそう
- Where句にPRIMARYKEYを利用した場合、PRIMARYは唯一の存在なので一番速い

mysql> explain SELECT * FROM users WHERE user_name = 'yutakikuchi';
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | index_users   | index_users | 47      | const |    1 | Using where |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from users where id = 2;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)



mysql> explain SELECT * FROM users AS u LEFT JOIN devices AS d on u.devices_id = d.id WHERE u.user_name = 'yutakikuchi';
+----+-------------+-------+--------+---------------+-------------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key         | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+---------------+-------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | u     | ref    | index_users   | index_users | 47      | const             |    1 | Using where |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY     | 4       | mydb.u.devices_id |    1 |             |
+----+-------------+-------+--------+---------------+-------------+---------+-------------------+------+-------------+
2 rows in set (0.00 sec)


mysql> explain SELECT * FROM devices WHERE maker = 'SHARP' and model = '912SH';
+----+-------------+---------+------+---------------+--------------+---------+-------------+------+-------------+
| id | select_type | table   | type | possible_keys | key          | key_len | ref         | rows | Extra       |
+----+-------------+---------+------+---------------+--------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | devices | ref  | index_device  | index_device | 94      | const,const |    1 | Using where |
+----+-------------+---------+------+---------------+--------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from devices where id = 1;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | devices | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

リンク

MySQL Practice wiki : http://www.mysqlpracticewiki.com/index.php/メインページ
MySQLの最適化 http://slashdot.jp/journal.pl?op=display&uid=4&id=26710

その他

偉大な先輩さんからのアドバイスをいただいたのでメモしておきます。

DATE型は使わずにUNSIGNED INT NOT NULLを使う。

Table名に複数形のsは入れない、idに対してはtablename_idとするなどのルール設定が必要。

Y's note

Web技術・プロダクトマネジメント・そして経営について

MYSQL INDEXのまとめ

概要

PRIMARYKEY / UNIQKEY / INDEXについて

複合INDEXについて

部分INDEX

INDEXのデータ構造

INDEXが使われるケース

INDEXの選択性(カーディナリティ)

INDEX使用の注意点

explainコマンド

ER図

利用想定SQL

INDEXの作成と状態確認

INDEXのテスト

リンク

その他