[mysql]どういうSQL文のとき、どういうインデックスが使われるのか整理
2016/11/07
mysqlのチューニングをしていて、どうしてもインデックスが、どの指定の時に何が使われるのかが分からなかったので整理。
１.こんな感じのアクセスログのテーブルがあって
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE `tracking_logs_test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `member` varchar(255) DEFAULT NULL, `visit` int(11) DEFAULT NULL, `page_view` int(11) DEFAULT NULL, `date` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `member` (`member`,`visit`,`page_view`,`date`) ) |
100万件ぐらいデータをぶち込む。
今回は、
member
page
date
の3項目について検索するので、それらにインデックスを張る。
一つずつ、二つずつ、三つずつ、で順番も代えて以下のとおり。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
■[1個] ALTER TABLE tracking_logs_test ADD INDEX idx_date(date); ALTER TABLE tracking_logs_test ADD INDEX idx_member (member); ALTER TABLE tracking_logs_test ADD INDEX idx_page(page(255)); ■[2個] ALTER TABLE tracking_logs_test ADD INDEX idx_date_member(date,member); ALTER TABLE tracking_logs_test ADD INDEX idx_member_date (member,date); ALTER TABLE tracking_logs_test ADD INDEX idx_date_page(date,page(255)); ALTER TABLE tracking_logs_test ADD INDEX idx_page_date(page(255),date); ALTER TABLE tracking_logs_test ADD INDEX idx_member_page(member,page(255)); ALTER TABLE tracking_logs_test ADD INDEX idx_page_member(page(255),member); ■3個 ALTER TABLE tracking_logs_test ADD INDEX idx_date_page_member(date,page(255),member); ALTER TABLE tracking_logs_test ADD INDEX idx_date_member_page(date,member,page(255)); ALTER TABLE tracking_logs_test ADD INDEX idx_member_date_page(member, date, page(255)); ALTER TABLE tracking_logs_test ADD INDEX idx_member_page_date(member,page(255),date); ALTER TABLE tracking_logs_test ADD INDEX idx_page_member_date(page(255),member,date); ALTER TABLE tracking_logs_test ADD INDEX idx_page_date_member(page(255),date,member); |
※pageはtext型なので、前方255byteまでしかindexがはれない。
取得項目はmemberとして、
でGroup BYを利用したときと、そうでないとき、どのインデックスが使われるか確認。
1.単純検索
「Whereの条件順とindexのkeyの順が一致していれば、それが使われる」らしい。。
取得項目とかいろいろ絡んでいるようだが、ひとまず検証。
(1)＝での絞り込み で 順番を変更して
1.条件項目 date,member,page それぞれ個別
|
1 2 3 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE date between '2015-09-14' AND '2015-10-10'; |
利用インデックス:date_member
|
1 2 3 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE page = 'testpage' |
利用インデックス:idx_page_date
|
1 2 3 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE member = 'test_user' |
利用インデックス:member
ここはイメージどおり。pageの場合だけ複合インデックスが利用されているのは、なぜじゃろう。。
2.条件項目 date ,member
(1) date ⇒ member の順
|
1 2 3 4 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE date between '2015-09-14' AND '2015-10-10' AND member = 'testuser'; |
利用インデックス:member
(2) member ,dateの順
|
1 2 3 4 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE member = 'testuser' AND date between '2015-09-14' AND '2015-10-10' |
⇒利用インデックス:member
3.条件項目 date, page
(1)date_page順
|
1 2 3 4 5 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE date between '2015-09-14' AND '2015-10-10' AND page = '/page' |
⇒利用インデックス:idx_page_member
(2)page_date順
|
1 2 3 4 5 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE page = '/news' AND date between '2015-09-14' AND '2015-10-10' |
⇒利用インデックス:idx_page_member
あれー、順番も項目も関係なさそう..
4.条件項目 date, page,member
(1)page date member順
|
1 2 3 4 5 6 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE page = '/news' AND date between '2015-09-14' AND '2015-10-10' AND member = 'test_user'; |
⇒利用インデックス:member
(2)page,member,date順
|
1 2 3 4 5 6 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE page = '/news' AND member = 'test_user' AND date between '2015-09-14' AND '2015-10-10' |
⇒利用インデックス:member
(3)date page member 順
|
1 2 3 4 5 6 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE date between '2015-09-14' AND '2015-10-10' AND page = '/news' AND member = 'test_user'; |
⇒利用インデックス:member
(4)date member page 順
|
1 2 3 4 5 6 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE date between '2015-09-14' AND '2015-10-10' AND member = 'test_user' AND page = '/news'; |
⇒利用インデック:member
(5)member,page,date順
|
1 2 3 4 5 6 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE member = 'test_user' AND date between '2015-09-14' AND '2015-10-10' AND page = '/news'; |
⇒利用インデックス:member
(6)member,date,page順
|
1 2 3 4 5 6 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE member = 'test_user' AND page = '/news' AND date between '2015-09-14' AND '2015-10-10'; |
⇒利用インデックス:member
中間まとめ
思っていたのと正直全然違う。
複数項目で条件指定しても、複合インデックスを使ってくれない。
使わない方が早いという算段なのか。トータル件数やカーディナリティの問題だろうか。
ヤバイな分からん。
なんかindexに対する理解が違うのだろう。。。。
(2) Like ‘%’ を使った場合の確認
条件のマッチ率が高すぎると、インデックスが使われずフルスキャンになるとのこと。
欲しいデータ次第ではあるのだけれど、、、一応確認。
そういうときは条件をつけない方がマシということなのだろうか。
(1)あまりマッチしない条件
|
1 2 3 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE page LIKE '/testtest%' |
idx_page_date
(2)大半がマッチする条件
|
1 2 3 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE page LIKE '/%' |
利用インデックス:NULL
あらー
複合したときは??
|
1 2 3 |
EXPLAIN SELECT member FROM tracking_logs_test WHERE page LIKE '/%' |
利用インデックス:idx_page_member
お、使われる。LIKE条件が%になるからと言って、むりやり検索対象項目を外すとかはしなくて良さそうだと。
2.Group BYの利用
|
1 2 3 |
EXPLAIN SELECT member, count(*) FROM tracking_logs_test GROUP BY member; |
利用インデックス:member
Group Byで指定した項目でインデックスが利用される。
Group Byで複数指定した場合は？
|
1 2 3 |
EXPLAIN SELECT member, count(*) FROM tracking_logs_test GROUP BY member, date |
利用インデックス:idx_member_date
を、初めてmember_dateが使われた。
順番代えると
|
1 2 3 |
EXPLAIN SELECT member, count(*) FROM tracking_logs_test GROUP BY date, member |
idx_date_memberが使われた。ここはイメージどおりだ。
ただし、もう1項目足すと、インデックスが使われなくなった。データが多すぎ..?
Whereを追加した場合は?
|
1 2 3 4 |
EXPLAIN SELECT member, count(*) FROM tracking_logs_test WHERE date between '2015-09-14' AND '2015-10-10' GROUP BY member |
利用インデックス:idx_date_member
ただし、にっくき「Using temporary; Using filesort」も発生。
どういうこっちゃ。
★ここではWHEREのキーとGroup BYのキーどちらがインデックス上先にあるべきかという事が確認できたか。whereのが先。
もう一つwhereを足してみると..
|
1 2 3 4 |
SELECT member, count(*) FROM tracking_logs_test WHERE date between '2015-09-14' AND '2015-10-10' AND page = '/testtest%' GROUP BY member; |
利用インデックス: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 とは
|
1 2 |
Using filesortはインデックスでのソートとは異なるソートが発生した場合。 Using temporary はfilesortが発生した結果、メモリ不足でディスクを利用している。 |
参考:http://nippondanji.blogspot.jp/2009/03/using-filesort.html
・SELECT …. WHERE aaa IN(サブクエリ)はindexが使われない。JOINで対応するのがよろしい。あるいはプログラムでまわす。ただしプログラムでまわすとN+1問題（件数分＋１のSQLを発行することになり、データ量依存で処理が重くなる）があるので、やっぱりJOIN。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
## こんな感じのINを利用したSQLは... SELECT id, sum(aaaa) FROM AAAA WHERE id IN( サブクエリ ) GROUP BY id ; ##こんな感じのJOINに変更 SELECT id, sum(aaa) FROM AAAA aa INNER JOIN (サブクエリ) sq ON aa.id = sq.id GROUP BY id; ##これで条件を満たすレコードに絞りこんで集計が出来る。 |
関連記事
-
-
[sql][mysql]URLのパラーメータ削除 + index.html の 削除
よく忘れるのでメモ。 ログを集計するときに、パラメータを削除したり、URLの/と …
-
-
mysql 内部結合・外部結合違い inner join,left join,right join
参考サイト]http://mathemathiko.hatenablog.com …
-
-
[mysql]なぜかindexが使われない、、、クォーテーションで囲っている？
whereの対象列にindexを張っている。 しかし、検索時にやたら重いことがあ …
-
-
[mysql]集合差を求める場合の処理速度比較(NOT IN(), NOT EXISTS, LEFT JOIN … IS NULL)
http://qiita.com/Hiraku/items/718 …
-
-
【MySQL】query_logを取得した場合の速度影響
あくまでWEB画面表示について。 環境は下記。 Linux CentOs7.1 …