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

2016/10/31 2016/11/07

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

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

 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`)
)

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個]
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);

■[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 それぞれ個別

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE date between '2015-09-14' AND '2015-10-10';

EXPLAIN

SELECT member FROM tracking_logs_test

WHERE date between '2015-09-14' AND '2015-10-10';

利用インデックス:date_member

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE page = 'testpage'

EXPLAIN

SELECT member FROM tracking_logs_test

WHERE page = 'testpage'

利用インデックス:idx_page_date

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE member = 'test_user'

EXPLAIN

SELECT member FROM tracking_logs_test

WHERE member = 'test_user'

利用インデックス:member

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

2.条件項目 date ,member

(1) date ⇒ member の順

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE date between '2015-09-14' AND '2015-10-10'
AND member = 'testuser';

EXPLAIN

SELECT member FROM tracking_logs_test

WHERE date between '2015-09-14' AND '2015-10-10'

AND member = 'testuser';

利用インデックス:member

(2) member ,dateの順

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE member = 'testuser'
AND  date between '2015-09-14' AND '2015-10-10'

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順

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE 
  date between '2015-09-14' AND '2015-10-10'
  AND page = '/page'

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順

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE 
 page = '/news'
 AND date between '2015-09-14' AND '2015-10-10'

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順

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE 
 page = '/news'
 AND date between '2015-09-14' AND '2015-10-10'
 AND member = 'test_user';

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順

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE 
 page = '/news'
 AND member = 'test_user'
 AND date between '2015-09-14' AND '2015-10-10'

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 順

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE 
 date between '2015-09-14' AND '2015-10-10'
 AND page = '/news'
 AND member = 'test_user';

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 順

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE 
 date between '2015-09-14' AND '2015-10-10'
 AND member = 'test_user'
 AND page = '/news';

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順

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE 
 member = 'test_user'
 AND date between '2015-09-14' AND '2015-10-10'
 AND page = '/news';

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順

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE 
 member = 'test_user'
 AND page = '/news'
 AND date between '2015-09-14' AND '2015-10-10';

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)あまりマッチしない条件

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE page LIKE  '/testtest%'

EXPLAIN

SELECT member FROM tracking_logs_test

WHERE page LIKE '/testtest%'

idx_page_date

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

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE page LIKE  '/%'

EXPLAIN

SELECT member FROM tracking_logs_test

WHERE page LIKE '/%'

利用インデックス:NULL

あらー

複合したときは??

EXPLAIN 
SELECT member FROM tracking_logs_test 
WHERE page LIKE  '/%'

EXPLAIN

SELECT member FROM tracking_logs_test

WHERE page LIKE '/%'

利用インデックス:idx_page_member

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

2.Group BYの利用

EXPLAIN
SELECT member, count(*) FROM  tracking_logs_test 
GROUP BY member;

EXPLAIN

SELECT member, count(*) FROM tracking_logs_test

GROUP BY member;

利用インデックス:member

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

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

EXPLAIN
SELECT member, count(*) FROM  tracking_logs_test 
GROUP BY member, date

EXPLAIN

SELECT member, count(*) FROM tracking_logs_test

GROUP BY member, date

利用インデックス:idx_member_date

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

順番代えると

EXPLAIN
SELECT member, count(*) FROM  tracking_logs_test 
GROUP BY date, member

EXPLAIN

SELECT member, count(*) FROM tracking_logs_test

GROUP BY date, member

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

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

Whereを追加した場合は?

EXPLAIN
SELECT member, count(*) FROM tracking_logs_test 
WHERE  date between '2015-09-14' AND '2015-10-10'
GROUP BY member

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を足してみると..

SELECT member, count(*) FROM  tracking_logs_test 
WHERE date between '2015-09-14' AND '2015-10-10'
AND page = '/testtest%'
GROUP BY member;

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 とは

Using filesortはインデックスでのソートとは異なるソートが発生した場合。
Using temporary は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。

## こんな感じの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;

##これで条件を満たすレコードに絞りこんで集計が出来る。