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

テーブル作成とデータ投入

[unix] シェルを使って、テストデータ生成でテストデータを作ったので、SQLiteにデータを投入してみよう。

まずはテーブルのDDLを作成する。

$ cat create_table.sql
create table if not exists Test1
  ( id integer primary key
  , name text
  , last_modified text
  );

create index if not exists idx_Test1_name on Test1
  ( 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') \
  >Test1.csv
$ head -n5 Test1.csv
1,wD2M1VUI,1984-01-03 16:59:03
2,uMrdDmwF,2004-07-20 07:20:24
3,mu6mZqq8,2011-01-26 11:22:16
4,ek8CdYNF,2010-12-26 21:16:54
5,H4nHbVWH,1984-12-17 17:02:02

これで準備ができたので、データを投入する。

$ sqlite3 sample.sqlite3
sqlite> .tables
sqlite> .indexes
sqlite> .read create_table.sql
sqlite> .tables
Test1
sqlite> .indexes
idx_Test1_name
sqlite> select count(*) from Test1;
count(*)  
----------
0      
sqlite> .separator ,
sqlite> .import Test1.csv Test1
sqlite> select count(*) from Test1;
count(*)  
----------
10000      

idが5000の行を取り出してみよう。

sqlite> select * from Test1 where id = 5000;
id          name        last_modified      
----------  ----------  -------------------
5000        Bn2y7SPy    2007-07-04 16:12:26

explain query plan

nameを指定した検索でインデックスが使われているか確認。
実行計画を表示するにはexplain query planを使う。

sqlite> explain query plan select * from Test1 where name = 'Bn2y7SPy';
selectid    order       from        detail                                                
----------  ----------  ----------  ------------------------------------------------------
0           0           0           SEARCH TABLE Test1 USING INDEX idx_Test1_name (name=?)

SEARCH TABLE Test1 USING INDEX idx_Test1_name (name=?)と表示されているのがわかる。

インデックスなしの場合

試しにインデックスを削除してみると。

sqlite> drop index idx_Test1_name;
sqlite> explain query plan select * from Test1 where name = 'Bn2y7SPy';
selectid    order       from        detail          
----------  ----------  ----------  ----------------
0           0           0           SCAN TABLE Test1

今度はSCAN TABLE Test1になっているのがわかる。

実行計画の見方はここで説明されている。

EXPLAIN QUERY PLAN

selectid, order, fromの意味はそれぞれ以下の通り。

• selectid サブクエリのレベル
• order テーブル結合のネストレベル
• from from句内の出現順序