MySQL：INDEXの基礎
CyberXの川村といいます。よろしくお願いします！
CyberXではソーシャルアプリ開発を主に行っていますが、そのソーシャルアプリの運用においてサーバートラブルを起こさず、いつでもユーザーの皆さんになるべくストレスを与えずに遊んでもらう事が重要であるのは言うまでもありません。
インフラ強化以前に、プログラマとしては自分が書いたプログラムやDB設計でインフラをいじめないようにしたいものです。。。
そこで今回はMySQLのINDEXに関して、基礎的な内容ではありますが書きたいと思います。
1つのクエリーで1つのテーブルに対し、1つのINDEXしか使われない
MySQLの特徴です。INDEXマージも行われません。
そこで、複合INDEXの貼り方が重要になります。
カーディナリティが高いカラムに貼ると効果が高い
「カーディナリティが高い＝カラムの値の種類がレコード数に比べて多いこと」
要は、よりユニークな値がたくさん含まれるカラムに対して貼ったら効果が高いよ、と言う事です。
0と1しか設定されないようなカラムに貼ってもあまり効果は期待出来ません。
ユーザーID等、ユーザー毎にユニークな値が入るカラムはカーディナリティが高い、ということになります。
複合INDEXちゃんと貼れてる
貼り方が不適切だとINDEXが使われません。
ここで注意しなければならないのが、複合INDEXに含めるカラムの「順番」です。
カラム順にヒットするかどうか見て行くため、ヒットしないカラムが前の方にあると効果激減です。
【例】
カラム
column1, column2, column3, column4, column5
複合INDEX
column1→column2→column3
SQL①
select * from table where column1 = 'aaa' and column2 = 'bbb' and column3 = 'ccc'
⇒INDEXが使用される
SQL②
select * from table where column1 = 'aaa' and column3 = 'ccc'
⇒INDEXが使用されない
SQL③
select * from table where column1 = 'aaa'
⇒INDEXが使用される
SQL④
select * from table where column2 = 'bbb'
⇒INDEXが使用されない
下記に注意して貼ればよいかと。
1.WHERE句でよく一緒に使われるカラム
2.WHERE句に単独で使われることもあるカラムは、インデックスの最初に指定する
3.カーディナリティの高いカラムから順に指定する
貼りまくればいいっていうわけじゃない
INDEXを貼れば貼った分、INSERTとUPDATE速度に悪影響が出ます(INDEXが更新されるため)。
また、INDEXを貼ったカラムの実データ分の容量が使用されるので、貼り過ぎるとディスクを圧迫する原因になりえます。
スマートに、必要な分だけ貼れたらカッコイイ！
Explainの活用
SQLをexplainでチェックして、INDEXが使われているか調べる事が出来ます。
INDEXが使用されるパターン
・フィールド値を定数と比較するとき(WHERE name = "hogehoge" )
・フィールド値全体でJOINするとき( WHERE a.name = b.name )
・フィールド値の範囲を求める時 (>、>=、 <、 <=、 BETWEEN)
・LIKEで文字列の先頭が固定な時(WHERE name like 'hoge%')
・MIN(), MAX()
・WHEREのすべてのフィールドがindexの一部の場合 (DBまったく参照されず)
INDEXが使用されないパターン
・LIKEがワイルドカードで始まる時
・DB全体を読んだ方が早いとMySQLが判断した時
・WHERE と ORDER BYのフィールドが違う時にはどちらかしか使われない
・NULL
・!=、<>(Not Equals)の使用
・列に対する関数や演算子の使用(SELECT * FROM column WHERE val * 2 > 2000)
基礎的な内容ですがミスするとDBに大きな負荷をかけてしまいかねません。
以上のポイントをおさえて、インフラに優しくなりたいですね！
CyberXではソーシャルアプリ開発を主に行っていますが、そのソーシャルアプリの運用においてサーバートラブルを起こさず、いつでもユーザーの皆さんになるべくストレスを与えずに遊んでもらう事が重要であるのは言うまでもありません。
インフラ強化以前に、プログラマとしては自分が書いたプログラムやDB設計でインフラをいじめないようにしたいものです。。。
そこで今回はMySQLのINDEXに関して、基礎的な内容ではありますが書きたいと思います。
1つのクエリーで1つのテーブルに対し、1つのINDEXしか使われないMySQLの特徴です。INDEXマージも行われません。
そこで、複合INDEXの貼り方が重要になります。
カーディナリティが高いカラムに貼ると効果が高い「カーディナリティが高い＝カラムの値の種類がレコード数に比べて多いこと」
要は、よりユニークな値がたくさん含まれるカラムに対して貼ったら効果が高いよ、と言う事です。
0と1しか設定されないようなカラムに貼ってもあまり効果は期待出来ません。
ユーザーID等、ユーザー毎にユニークな値が入るカラムはカーディナリティが高い、ということになります。
複合INDEXちゃんと貼れてる貼り方が不適切だとINDEXが使われません。
ここで注意しなければならないのが、複合INDEXに含めるカラムの「順番」です。
カラム順にヒットするかどうか見て行くため、ヒットしないカラムが前の方にあると効果激減です。
【例】
カラム
column1, column2, column3, column4, column5
複合INDEX
column1→column2→column3
SQL①
select * from table where column1 = 'aaa' and column2 = 'bbb' and column3 = 'ccc'
⇒INDEXが使用される
SQL②
select * from table where column1 = 'aaa' and column3 = 'ccc'
⇒INDEXが使用されない
SQL③
select * from table where column1 = 'aaa'
⇒INDEXが使用される
SQL④
select * from table where column2 = 'bbb'
⇒INDEXが使用されない
下記に注意して貼ればよいかと。
1.WHERE句でよく一緒に使われるカラム
2.WHERE句に単独で使われることもあるカラムは、インデックスの最初に指定する
3.カーディナリティの高いカラムから順に指定する
貼りまくればいいっていうわけじゃないINDEXを貼れば貼った分、INSERTとUPDATE速度に悪影響が出ます(INDEXが更新されるため)。
また、INDEXを貼ったカラムの実データ分の容量が使用されるので、貼り過ぎるとディスクを圧迫する原因になりえます。
スマートに、必要な分だけ貼れたらカッコイイ！
Explainの活用SQLをexplainでチェックして、INDEXが使われているか調べる事が出来ます。
INDEXが使用されるパターン・フィールド値を定数と比較するとき(WHERE name = "hogehoge" )
・フィールド値全体でJOINするとき( WHERE a.name = b.name )
・フィールド値の範囲を求める時 (>、>=、 <、 <=、 BETWEEN)
・LIKEで文字列の先頭が固定な時(WHERE name like 'hoge%')
・MIN(), MAX()
・WHEREのすべてのフィールドがindexの一部の場合 (DBまったく参照されず)
INDEXが使用されないパターン・LIKEがワイルドカードで始まる時
・DB全体を読んだ方が早いとMySQLが判断した時
・WHERE と ORDER BYのフィールドが違う時にはどちらかしか使われない
・NULL
・!=、<>(Not Equals)の使用
・列に対する関数や演算子の使用(SELECT * FROM column WHERE val * 2 > 2000)
基礎的な内容ですがミスするとDBに大きな負荷をかけてしまいかねません。
以上のポイントをおさえて、インフラに優しくなりたいですね！