For Want Of A Better Word

[mysql]どういうSQL文のとき、どういうインデックスが使われるのか整理

      2016/11/07

mysqlのチューニングをしていて、どうしてもインデックスが、どの指定の時に何が使われるのかが分からなかったので整理。

 

1.こんな感じのアクセスログのテーブルがあって

100万件ぐらいデータをぶち込む。

 

今回は、

member

page

date

の3項目について検索するので、それらにインデックスを張る。

一つずつ、二つずつ、三つずつ、で順番も代えて以下のとおり。

※pageはtext型なので、前方255byteまでしかindexがはれない。

 

取得項目はmemberとして、

でGroup BYを利用したときと、そうでないとき、どのインデックスが使われるか確認。

 

1.単純検索

「Whereの条件順とindexのkeyの順が一致していれば、それが使われる」らしい。。

取得項目とかいろいろ絡んでいるようだが、ひとまず検証。

 

(1)=での絞り込み で 順番を変更して

1.条件項目 date,member,page それぞれ個別

利用インデックス:date_member

利用インデックス:idx_page_date

 

利用インデックス:member

ここはイメージどおり。pageの場合だけ複合インデックスが利用されているのは、なぜじゃろう。。

 

2.条件項目 date ,member

(1) date ⇒ member の順

利用インデックス:member

(2) member ,dateの順

⇒利用インデックス:member

3.条件項目 date, page

(1)date_page順

⇒利用インデックス:idx_page_member

(2)page_date順

⇒利用インデックス:idx_page_member

 

あれー、順番も項目も関係なさそう..

 

4.条件項目 date, page,member

(1)page date member順

⇒利用インデックス:member

(2)page,member,date順

⇒利用インデックス:member

(3)date page member 順

⇒利用インデックス:member

(4)date member page 順

⇒利用インデック:member

(5)member,page,date順

⇒利用インデックス:member

(6)member,date,page順

⇒利用インデックス:member

 

中間まとめ

思っていたのと正直全然違う。

複数項目で条件指定しても、複合インデックスを使ってくれない。

使わない方が早いという算段なのか。トータル件数やカーディナリティの問題だろうか。

ヤバイな分からん。

なんかindexに対する理解が違うのだろう。。。。

 

(2) Like ‘%’ を使った場合の確認

条件のマッチ率が高すぎると、インデックスが使われずフルスキャンになるとのこと。

欲しいデータ次第ではあるのだけれど、、、一応確認。

そういうときは条件をつけない方がマシということなのだろうか。

(1)あまりマッチしない条件

idx_page_date

(2)大半がマッチする条件

利用インデックス:NULL

あらー

 

複合したときは??

利用インデックス:idx_page_member

お、使われる。LIKE条件が%になるからと言って、むりやり検索対象項目を外すとかはしなくて良さそうだと。

 

2.Group BYの利用

利用インデックス:member

 

Group Byで指定した項目でインデックスが利用される。

Group Byで複数指定した場合は?

利用インデックス:idx_member_date

 

を、初めてmember_dateが使われた。

 

順番代えると

idx_date_memberが使われた。ここはイメージどおりだ。

ただし、もう1項目足すと、インデックスが使われなくなった。データが多すぎ..?

 

Whereを追加した場合は?

利用インデックス:idx_date_member

 

ただし、にっくき「Using temporary; Using filesort」も発生。

どういうこっちゃ。

★ここではWHEREのキーとGroup BYのキーどちらがインデックス上先にあるべきかという事が確認できたか。whereのが先。

もう一つwhereを足してみると..

利用インデックス:idx_page_member

dateが外れて、pageとmemberで。。

pageとdateの順番代えても同じ…

 

まとめ

正直今回の確認では、まとまらなかった。

「whereの順番とindexの順番をそろえれば、インデックスが使われる。」

とういのは浅すぎる考え方の模様。

 

もうちょっと、インデックスの使われ方を調べてみる必要あり。

 

収穫としては、indexはWHERE項目⇒Group By項目の順で作る。

 

という所でよいみたい。

メモ

・Likeはマッチ率が高いとindexが使われない様子..

・viewに対してはUSE INDEX()が指定できない

・indexが使われても   Using temporary; Using filesort  が出る場合がある。

・Using temporary; Using filesort  とは

参考:http://nippondanji.blogspot.jp/2009/03/using-filesort.html

・SELECT …. WHERE aaa IN(サブクエリ)はindexが使われない。JOINで対応するのがよろしい。あるいはプログラムでまわす。ただしプログラムでまわすとN+1問題(件数分+1のSQLを発行することになり、データ量依存で処理が重くなる)があるので、やっぱりJOIN。

 

 

 - mysql

  関連記事

mysql-logo
【PHP】【DB開発メモ】PDO_LIKEが動かん!プレースホルダの誤用

前もはまった気がする…. 成長しない。 PHP: プ& …

mysql-logo
最新phpMyFAQのインストール

【参考】 http://www.phpmyfaq.jp/category/1/1 …

mysql-logo
mysqlnd

PHP5および6用のMySQLのネイティブドライバです。 mysqliで利用して …

mysql-logo
php5.1 apache2 連携用のdllファイル(php5apache2_2.dll)の取得

windowsでphp5.1をapache2で動かしたかったり、その逆の時とか、 …

mysql-logo
dotCloudにPHPMyFAQを入れてみる。

dotCloudにプロジェクトとDBは作った状態から開始。 phpMyFAQは2 …