Home » sqlite » [SQLite] SQLiteで実行計画を見てみよう その2

[SQLite] SQLiteで実行計画を見てみよう その2

2017年7月31日月曜日

SQL sqlite

テーブルをもう１つ作成

[SQLite] SQLiteで実行計画を見てみよう その1では結合もサブクエリもないSQLだったので、selectid,order,fromがすべて0の実行計画だった。
テーブルを結合した時の実行計画はどうなるだろうか？

まずはもう１つのテーブルTest2を作成しておく。
$ cat create_table2.sql
create table if not exists Test2
  ( id integer primary key
  , name text
  , last_modified text
  );

create index if not exists idx_Test2_name on Test2
  ( name
  );
$ paste -d, \
  <(seq 10000) \
  <(LC_CTYPE=C tr -dc 'A-Za-z0-9' </dev/urandom |
    fold -w8 | head -n10000) \
  <(LC_CTYPE=C tr -dc '0-9' </dev/urandom |
    fold -w10 | sed -e 's/^0*//' |
    awk '$1<1497711600{ print $1 }' | head -n10000 |
    sed -e 's/^/@/' | date -f- '+%Y-%m-%d %T') \
  >Test2.csv

データベースは前回作成したsample.sqlite3を使う。
$ sqlite3 sample.sqlite3
sqlite> .read create_table2.sql 
sqlite> .separator ,
sqlite> .import Test2.csv Test
sqlite> select name from Test2 where id = 500;
name      
----------
HBnod4xm

同じlast_modifiedの行はあるだろうか？

テーブル結合の場合


sqlite> explain query plan select Test1.id, Test2.id, Test1.name from Test1
   ...> join Test2 on Test1.last_modified = Test2.last_modified;
selectid    order       from        detail                                                                          
----------  ----------  ----------  -------------------------------------------------------------------
0           0           0           SCAN TABLE Test1                                                                
0           1           1           SEARCH TABLE Test2 USING AUTOMATIC COVERING INDEX (last_modified=?)

order(テーブル結合のネストレベル)がTest1:0,Test2:1となっているため、Test1を外側、Test2を内側としたnested loopになっているのがわかる。

The SQLite Query Planner 6.1 Order of tables in a join
に書かれているようにSQLiteでは現状、テーブル結合の実装はnested loopしかない。

また、Test1はSCAN TABLEになっているのに対して、Test2はAUTOMATIC COVERING INDEXになっている。
AUTOMATIC INDEXはSQLの実行時に一時的に作られるインデックスで、今回の場合はTest2.last_modifiedにインデックスがついていないため、内側のループで毎回scanするよりも、最初にTest2.last_modifiedのインデックスを構築してしまった方がコスト的に有利とオプティマイザが判断したようだ。

The SQLite Query Planner 12.0 Automatic Indexes
便利な機能とも言えるけど、頻繁に実行されるSQLの実行計画で AUTOMATIC INDEX が出ていたら、該当のカラムにインデックスの付加を検討すべきかもしれない。

検索条件を追加

where Test2.name = 'HBnod4xm' をSQLの最後につけると、今度はTest1がループの内部に入っていることがわかる。
 
sqlite> explain query plan select Test1.id, Test2.id from Test1
   ...> join Test2 on Test1.last_modified = Test2.last_modified
   ...> where Test2.name = 'HBnod4xm';
selectid    order       from        detail                                                
----------  ----------  ----------  ------------------------------------------------------
0           0           1           SEARCH TABLE Test2 USING INDEX idx_Test2_name (name=?)
0           1           0           SCAN TABLE Test1

今回の場合はTest2の取得レコード数がずっと少なくなることが予想される。
このため、Test2のループを外側にして、コストのかかるTest1のscanが内側でもいいと判断したようだ。

でも、他のDBの実行計画と違って、各命令のコストまでは表示されないので、どこに時間がかかってるかまでは簡単にはわからないなぁ。
時刻:
ラベル: ,