H2のEXPLAINを試してみる。

データベースのパフォーマンスチューニングで、SQLのチューニング(インデックスの設定含む)は一番効果が大きい。

それでいて、SQLのチューニングはポータビリティが高い。メモリの設定、ディスクの設定、パラメータの設定はデータベースごとにまったく異なる(基礎的な考え方は同じではあるけれども)。それに比べると、SQLのチューニングはどのデータベースでも同じようなもの(Oracleのヒント句を使ったチューニングなんかは独特だと思うけど)

で、SQLのチューニングを行う上で、重要なのが実行計画。だいたいどのデータベースも実行計画を確認するためのExplainコマンドが用意されている。H2にもある。

まず、以下のようなテーブルを作る。担当者、商品、売り上げ。

create table staff (id int not null primary key, lname varchar, fname varchar, item_id int);
create table item(id int not null primary key, code char(10), type int, name varchar, price decimal(5));
create table sales(id int not null primary key, sales_date date, item_id int, amount int);

-- index
create index ind_staff on staff(lname);
create index ind_item on item(code,type);

-- test
create table noprimary(id int, name varchar);

h2のオプティマイザはコストベースらしいので、データ量やカーディナリティを見るんだと思うが、とりあえず、データなしで。

sql> explain select * from staff;
PLAN
SELECT STAFF.ID, STAFF.LNAME, STAFF.FNAME, STAFF.ITEM_ID
FROM PUBLIC.STAFF /* PUBLIC.STAFF.tableScan */
(1 row, 108 ms)

h2では内部的には通常の文の部分はUpperCaseされる。あと、スキーマを指定していないので、スキーマはデフォルトのPUBLICが補完されていて、カラム名も展開されている。まずこの様に正規化されるようだ、で、実行計画的には、STAFFテーブルに対して、テーブルスキャンが実行されているのがわかる。

sql> explain select * from staff where id = 1;
PLAN
SELECT STAFF.ID, STAFF.LNAME, STAFF.FNAME, STAFF.ITEM_ID
FROM PUBLIC.STAFF /* PUBLIC.PRIMARY_KEY_4: ID = 1 */
WHERE ID = 1
(1 row, 11 ms)

単純にプライマリキーを指定した場合。PUBLIC.PRIMARY_KEY_4 はおそらくPrimary Keyに対して自動で生成された索引の名称。

sql> explain select * from staff join item on staff.item_id = item.id;
PLAN
SELECT STAFF.ID, STAFF.LNAME, STAFF.FNAME, STAFF.ITEM_ID, ITEM.ID, ITEM.CODE, ITEM.TYPE, ITEM.NAME, ITEM.PRICE
FROM PUBLIC.STAFF /* PUBLIC.STAFF.tableScan */
INNER JOIN PUBLIC.ITEM /* PUBLIC.PRIMARY_KEY_2: ID = STAFF.ITEM_ID AND ID = STAFF.ITEM_ID */ ON 1=1
WHERE STAFF.ITEM_ID = ITEM.ID
(1 row, 18 ms)

内部結合。担当者と商品を結合。担当者側のitem_idが外部キーにもなっておらず、索引も張られていないので、担当者はテーブルスキャン(※後で気がついたが、そもそも担当者の絞り込み条件がないのでテーブルスキャンなのは当たり前)。商品側はPUBLIC.PRIMARY_KEY_2なので、索引が使用されている。正規化の部分で、joinがinnner joinになっていたり、on句の部分が、1=1で置き換えられ、結合条件が、where句の方に回っているのがちょっと面白い。

そういえば、create tableのマニュアルみてたら、h2はDDLを発行するとコミットがかかる。DB2はdrop tableしてしまったあとでも、rollbackでテーブルが戻ってくるという素敵仕様で、あれはいいなぁ、と思う。あんまり使わないけど。他にそういうデータベースあるんだろうか。Derbyとか?

じゃ、外部キーを付けてみる。

alter table staff add constraint fk_staff_item foreign key (item_id) references item(id);

外部キーに限らず制約の名称は省略不可みたい。仕事でやるときにはきちんとつけた方がいいと思うけど、省略できるようになっていてもいいと思う。

sql> explain select * from staff join item on staff.item_id = item.id;
PLAN
SELECT STAFF.ID, STAFF.LNAME, STAFF.FNAME, STAFF.ITEM_ID, ITEM.ID, ITEM.CODE, ITEM.TYPE, ITEM.NAME, ITEM.PRICE
FROM PUBLIC.STAFF /* PUBLIC.STAFF.tableScan */
INNER JOIN PUBLIC.ITEM /* PUBLIC.PRIMARY_KEY_2: ID = STAFF.ITEM_ID AND ID = STAFF.ITEM_ID */ ON 1=1
WHERE STAFF.ITEM_ID = ITEM.ID
(1 row, 10 ms)

あれ、特に変わらない...と思ったら、staff側に条件ないんだからそりゃそうか。

sql> explain select * from staff join item on staff.item_id = item.id where staff.item_id = 1;
PLAN
SELECT STAFF.ID, STAFF.LNAME, STAFF.FNAME, STAFF.ITEM_ID, ITEM.ID, ITEM.CODE, ITEM.TYPE, ITEM.NAME, ITEM.PRICE
FROM PUBLIC.ITEM /* PUBLIC.PRIMARY_KEY_2: ID = 1 */ /* WHERE ITEM.ID = 1 */
INNER JOIN PUBLIC.STAFF /* PUBLIC.FK_STAFF_ITEM_INDEX_4: ITEM_ID = 1 AND ITEM_ID = ITEM.ID */ ON 1=1
WHERE ((STAFF.ITEM_ID = 1) AND (STAFF.ITEM_ID = ITEM.ID)) AND (ITEM.ID = 1)
(1 row, 32 ms)

結構変わってる。商品に担当者を結合する形になり、staff.item_id = 1 と staff.item_id = item.idから導きだされたのか、ITEM.ID = 1なんてのが追加?されている。

さっきの外部キーを落としてみると、担当者に商品を結合する形になるんだろうか。

sql> alter table staff drop constraint fk_staff_item;
sql> explain select * from staff join item on staff.item_id = item.id where staff.item_id = 1;
PLAN
SELECT STAFF.ID, STAFF.LNAME, STAFF.FNAME, STAFF.ITEM_ID, ITEM.ID, ITEM.CODE, ITEM.TYPE, ITEM.NAME, ITEM.PRICE
FROM PUBLIC.ITEM /* PUBLIC.PRIMARY_KEY_2: ID = 1 */ /* WHERE ITEM.ID = 1 */
INNER JOIN PUBLIC.STAFF /* PUBLIC.STAFF.tableScan */ ON 1=1
WHERE ((STAFF.ITEM_ID = 1) AND (STAFF.ITEM_ID = ITEM.ID)) AND (ITEM.ID = 1)
(1 row, 6 ms)

ならない。主キーが強い、ということなんだろうか。fk_staff_itemの索引を見ていた部分が、テーブルスキャンに変わった(そりゃそうだ)だけで、特に変わりはない。

今度は複合索引。

sql> explain select * from item where code = 'AB123' and type = 1; 
PLAN
SELECT ITEM.ID, ITEM.CODE, ITEM.TYPE, ITEM.NAME, ITEM.PRICE
FROM PUBLIC.ITEM /* PUBLIC.IND_ITEM: CODE = 'AB123' AND TYPE = 1 */
WHERE (CODE = 'AB123') AND (TYPE = 1)
(1 row, 9 ms)

ちゃんと使われている。じゃ、地味に複合索引に指定されたのと逆にする。

sql> explain select * from item where type = 1 and code = 'AB123';
PLAN
SELECT ITEM.ID, ITEM.CODE, ITEM.TYPE, ITEM.NAME, ITEM.PRICE
FROM PUBLIC.ITEM /* PUBLIC.IND_ITEM: TYPE = 1 AND CODE = 'AB123' */
WHERE (TYPE = 1) AND (CODE = 'AB123')
(1 row, 1 ms)

お。賢い。ちゃんと使われてる。一昔前はOracleもダメだったりしたのに。

複合索引の一部分(先頭)を指定。

sql> explain select * from item where code = 'AB123';
PLAN
SELECT ITEM.ID, ITEM.CODE, ITEM.TYPE, ITEM.NAME, ITEM.PRICE
FROM PUBLIC.ITEM /* PUBLIC.IND_ITEM: CODE = 'AB123' */
WHERE CODE = 'AB123'
(1 row, 1 ms)

使われてる。

複合索引の一部分(先頭でないもの)を指定。

sql> explain select * from item where type = 1;
PLAN
SELECT ITEM.ID, ITEM.CODE, ITEM.TYPE, ITEM.NAME, ITEM.PRICE
FROM PUBLIC.ITEM /* PUBLIC.ITEM.tableScan */
WHERE TYPE = 1
(1 row, 2 ms)

さすがに使われない。この場合は索引見ないほうがよさそうだから、まぁ、そうなんだろう。

文字列に対するレンジスキャンはどうか。betweenは正規化されたら消えそうだな。

sql> explain select * from staff where lname between 'ABC' and 'XYZ';
PLAN
SELECT STAFF.ID, STAFF.LNAME, STAFF.FNAME, STAFF.ITEM_ID
FROM PUBLIC.STAFF /* PUBLIC.IND_STAFF: LNAME >= 'ABC' AND LNAME <= 'XYZ' */
WHERE (LNAME >= 'ABC') AND (LNAME <= 'XYZ')
(1 row, 9 ms)

やっぱりbetweenは書き換えられている。そして、レンジスキャンであっても、索引は使われている。

likeだとどうか。前方一致、後方一致、部分一致を見てみる。まず前方一致。

sql> explain select * from staff where lname like 'ABC%';
PLAN
SELECT STAFF.ID, STAFF.LNAME, STAFF.FNAME, STAFF.ITEM_ID
FROM PUBLIC.STAFF /* PUBLIC.IND_STAFF: LNAME >= 'ABC' AND LNAME < 'ABD' */
WHERE LNAME LIKE 'ABC%'
(1 row, 4 ms)

あ。likeの前方一致だとこうなるのか。考えた事なかった。はー。なるほど。ちゃんと索引が使われてる。

とすると、日本語だとどうか、というと...

sql> explain select * from staff where lname like 'あ%';
PLAN
SELECT STAFF.ID, STAFF.LNAME, STAFF.FNAME, STAFF.ITEM_ID
FROM PUBLIC.STAFF /* PUBLIC.IND_STAFF: LNAME >= STRINGDECODE('\u3042') AND LNAME < STRINGDECODE('\u3043') */
WHERE LNAME LIKE STRINGDECODE('\u3042%')
(1 row, 1 ms)

まぁ、そうですよね、という。

後方一致と、部分一致はどうか。

sql> explain select * from staff where lname like '%ABC';
PLAN
SELECT STAFF.ID, STAFF.LNAME, STAFF.FNAME, STAFF.ITEM_ID
FROM PUBLIC.STAFF /* PUBLIC.STAFF.tableScan */
WHERE LNAME LIKE '%ABC'
(1 row, 8 ms)

sql> explain select * from staff where lname like '%ABC%';
PLAN
SELECT STAFF.ID, STAFF.LNAME, STAFF.FNAME, STAFF.ITEM_ID
FROM PUBLIC.STAFF /* PUBLIC.STAFF.tableScan */
WHERE LNAME LIKE '%ABC%'
(1 row, 5 ms)

まぁ、使われない、と。

データが入っていない状態なので、実用上どうか、という話に対する答えにはならないのですが、ある程度思った通りの動きはしそうだな、という感じ。