H2のEXPLAINを試してみる2

前回の続き。

h2では、count(*)で条件なしの単一テーブルでのカウントの際には、テーブルにアクセスしないので速い、とのこと。

sql> explain select count(*) from noprimary;
PLAN
SELECT COUNT(*)
FROM PUBLIC.NOPRIMARY /* PUBLIC.NOPRIMARY.tableScan */
/* direct lookup */
(1 row, 0 ms)
sql> explain select count(*) from noprimary where id = 1;
PLAN
SELECT COUNT(*)
FROM PUBLIC.NOPRIMARY /* PUBLIC.NOPRIMARY.tableScan */
WHERE ID = 1
(1 row, 9 ms)

確かに、where句がない場合のカウントでは direct lookupというメッセージが出ている。まぁ、速いんだろう。*1試しにprimary key項目でカウントした場合はdirect lookupにはならないようなので、全行カウントする場合には、count(*)でやるべき。

複数の索引は組み合わされるのか。新しいテーブルを作る。

create table hoge(id int not null primary key, type_a varchar, type_b varchar, type_c varchar);
create index on hoge(type_a);
create index on hoge(type_b);
create unique index on hoge(type_c); 

SQL実行。

sql> explain select * from hoge where type_a = 'a' and type_b ='b';
PLAN
SELECT HOGE.ID, HOGE.TYPE_A, HOGE.TYPE_B, HOGE.TYPE_C
FROM PUBLIC.HOGE /* PUBLIC.INDEX_2: TYPE_A = 'a' */
WHERE (TYPE_A = 'a') AND (TYPE_B = 'b')
(1 row, 5 ms)

むむ。ここではtype_aに対する索引しか使われていない。と思ったら、マニュアルにちゃんと書いてある。

If a table has multiple indexes, sometimes more than one index could be used. Example: if there is a table TEST(ID, NAME, FIRSTNAME) and an index on each column, then two indexes could be used for the query SELECT * FROM TEST WHERE NAME='A' AND FIRSTNAME='B', the index on NAME or the index on FIRSTNAME. It is not possible to use both indexes at the same time. Which index is used depends on the selectivity of the column.

http://www.h2database.com/html/performance.html

どっちが使われるかが、selectivitiy of the columnによって決まる、と。

sql> explain select * from hoge where type_a = 'a' and type_c ='c';
PLAN
SELECT HOGE.ID, HOGE.TYPE_A, HOGE.TYPE_B, HOGE.TYPE_C
FROM PUBLIC.HOGE /* PUBLIC.INDEX_21E: TYPE_C = 'c' */
WHERE (TYPE_A = 'a') AND (TYPE_C = 'c')
(1 row, 1 ms)

データ入れてテストしてみる前に、一応、ユニーク索引の列も混ぜてみたら、ちゃんとユニーク索引の列が使われた。

で、データ投入。

drop table hoge;
create table hoge(id int not null primary key, type_a varchar, type_b varchar, type_c varchar);
create index on hoge(type_a);
create index on hoge(type_b);
create unique index on hoge(type_c); 
create sequence hoge_id;
create sequence hoge_c;
insert into hoge values (hoge_id.nextval,'1','A',hoge_c.nextval);
insert into hoge values (hoge_id.nextval,'2','B',hoge_c.nextval);
insert into hoge values (hoge_id.nextval,'3','C',hoge_c.nextval);
insert into hoge values (hoge_id.nextval,'1','D',hoge_c.nextval);
insert into hoge values (hoge_id.nextval,'2','E',hoge_c.nextval);
insert into hoge values (hoge_id.nextval,'3','F',hoge_c.nextval);
insert into hoge values (hoge_id.nextval,'1','G',hoge_c.nextval);
insert into hoge values (hoge_id.nextval,'2','H',hoge_c.nextval);
insert into hoge values (hoge_id.nextval,'3','I',hoge_c.nextval);

はい、実行。

sql> explain select * from hoge where type_a = '2' and type_b = 'C';
PLAN
SELECT HOGE.ID, HOGE.TYPE_A, HOGE.TYPE_B, HOGE.TYPE_C
FROM PUBLIC.HOGE /* PUBLIC.INDEX_2: TYPE_A = '2' */
WHERE (TYPE_A = '2') AND (TYPE_B = 'C')
(1 row, 53 ms)
sql> analyze;
(Update count: 0, 6 ms)
sql> explain select * from hoge where type_a = '2' and type_b = 'C';
PLAN
SELECT HOGE.ID, HOGE.TYPE_A, HOGE.TYPE_B, HOGE.TYPE_C
FROM PUBLIC.HOGE /* PUBLIC.INDEX_21: TYPE_B = 'C' */
WHERE (TYPE_A = '2') AND (TYPE_B = 'C')
(1 row, 13 ms)

analyzeの前後で、使用されている索引が異なっている（よりカーディナリティの高い索引が使われるようになっている)

複数の索引が使われないのはちょっとなーという気はしますが、そういうのは複合索引でカバーできるので、まぁ、というところでしょうか。

とはいえ、複合索引を作成して、その中の部分の項目でアクセスした場合、先頭の項目が含まれていないと索引を使用しないようなので、索引の設計には気をつける必要がある、ということですね。

drop table hoge;
create table hoge(id int not null primary key, type_a varchar, type_b varchar, type_c varchar);
create index on hoge(type_a, type_b, type_c);

sql> explain select * from hoge where type_b = 'a';
PLAN
SELECT HOGE.ID, HOGE.TYPE_A, HOGE.TYPE_B, HOGE.TYPE_C
FROM PUBLIC.HOGE /* PUBLIC.HOGE.tableScan */
WHERE TYPE_B = 'a'
(1 row, 52 ms)
sql> explain select * from hoge where type_b = 'a' and type_c = 'a';
PLAN
SELECT HOGE.ID, HOGE.TYPE_A, HOGE.TYPE_B, HOGE.TYPE_C
FROM PUBLIC.HOGE /* PUBLIC.HOGE.tableScan */
WHERE ((TYPE_B = 'a') AND (TYPE_C = 'a')) AND (TYPE_B = TYPE_C)
(1 row, 3 ms)
sql> explain select * from hoge where type_a = 'a' and type_c = 'c';
PLAN
SELECT HOGE.ID, HOGE.TYPE_A, HOGE.TYPE_B, HOGE.TYPE_C
FROM PUBLIC.HOGE /* PUBLIC.INDEX_2: TYPE_A = 'a' AND TYPE_C = 'c' */
WHERE (TYPE_A = 'a') AND (TYPE_C = 'c')
(1 row, 6 ms)

で、マニュアル（というか公式サイト）によると、src/test/org/h2/samples/optimizations.sqlにいくつかサンプルがあるので...ということなので、そこを読んでみる。

あー、SYSTEM_RANGEというテーブルがあるのね。

select x from system_range(1,10000000000);

ちょっと便利。先ほどのカーディナリティの異なる列を作るところも下記の様にかける。

drop table hoge;
create table hoge(id int not null primary key, type_a int, type_b int, type_c int);
create index on hoge(type_a);
create index on hoge(type_b);
create unique index on hoge(type_c); 
insert into hoge select x,mod(x,3),mod(x,5),x from system_range(1,100);
explain select * from hoge where type_a = 222 and type_b = 333;
analyze;
explain select * from hoge where type_a = 222 and type_b = 333;

あとは、ソートの際にも索引が利用される事がある、とか、in(a,b)でも索引が利用される等。(昔某DBの案件でorで書くと索引が使われず遅いとか(今もそういうケースがある?)で、select * from X where Y = a union all select * from X where Y = b見たいに書いているのを見たことある)

*1:H2ではテーブルのメタデータに現在のそのテーブルの総行数が書いてあるので速いというかなんというか