複数列indexの順序と選択性

複数列indexの順序を決める時にどういう順番で並べますかっていうと
選択性の高い列から並べるのが原則

それじゃ、最近のDBでセオリー通りのindexとそうじゃない順序のindexでどれくらい差が出るもんなの、というのを単純なケースで試してみる

DB:MariaDB 10.0.11
OS:Ubuntu 14.04
マシン:thinkpad t440p(ssd)

テーブルとindex

テーブルとテストは前回も使用したのを流用する
http://qiita.com/k_o_gj/items/a663692c3389ec7cc29d

CREATE TABLE event_test(
 id          BIGINT AUTO_INCREMENT,
 event_id    INT    NOT NULL    DEFAULT 0,
 user_id     INT    NOT NULL    DEFAULT 0,
 status      INT    NOT NULL    DEFAULT 0,
 create_time DATETIME   NOT NULL,
 update_time DATETIME   NOT NULL,
 delete_time DATETIME   NOT NULL,
 PRIMARY KEY (id),
 KEY (user_id,event_id)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8;

例えば以下のようなクエリを出すとすると、user_idにマッチする行のほうが少ないのでuser_idがindex順序の先にくるべきということになる

select * from event_test where event_id = 4 AND user_id = 38651;

MariaDB [my_test]> select sum(event_id = 4), sum(user_id = 38651) from event_test\G
*************************** 1. row ***************************
   sum(event_id = 4): 2309567
sum(user_id = 38651): 12
1 row in set (8.30 sec)

table全体でみてもuser_idのほうが選択性が高いのでindex順序は(user_id,event_id)のほうが(event_id,user_id)よりgoodということになる

MariaDB [my_test]> select count(distinct event_id)/count(*) AS eid_selectivity, count(distinct user_id)/count(*) AS uid_selectivity, count(*) from event_test\G
*************************** 1. row ***************************
eid_selectivity: 0.0000
uid_selectivity: 0.0866
count(*): 34623542
1 row in set (11.68 sec)


テスト

テストはsysbenchのselect.luaのevent関数を以下のようにちょっと変更して実行
4スレッドで5分間軽い検索をし続ける

function event(thread_id)
   local eid
   eid = sb_rand(0, 1000000)%15
   rs = db_query("SELECT * FROM event_test WHERE event_id=" .. eid .. " AND user_id=" .. sb_rand(0,3000000))
end


./bin/sysbench --test=/tests/db/my_select_test.lua --db-driver=mysql --mysql-user=user --mysql-password=pass --mysql-host=127.0.0.1 --mysql-db=my_test --mysql-table-engine=innodb --num-threads=4 --max-requests=0 --max-time=300 run

結果1

Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            10064763
        write:                           0
        other:                           0
        total:                           10064763
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 10064763 (33549.20 per sec.)
    other operations:                    0      (0.00 per sec.)

General statistics:
    total time:                          300.0001s
    total number of events:              10064763
    total time taken by event execution: 1188.5505s
    response time:
         min:                                  0.07ms
         avg:                                  0.12ms
         max:                                 16.24ms
         approx.  95 percentile:               0.14ms

Threads fairness:
    events (avg/stddev):           2516190.7500/6959.39
    execution time (avg/stddev):   297.1376/0.01

結果2(indexを入れ替えて試す)

KEY (user_id, event_id)をKEY (event_id,user_id)に変更して実行

Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            9831949
        write:                           0
        other:                           0
        total:                           9831949
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 9831949 (32772.17 per sec.)
    other operations:                    0      (0.00 per sec.)

General statistics:
    total time:                          300.0091s
    total number of events:              9831949
    total time taken by event execution: 1188.7031s
    response time:
         min:                                  0.07ms
         avg:                                  0.12ms
         max:                                 54.46ms
         approx.  95 percentile:               0.14ms

Threads fairness:
    events (avg/stddev):           2457987.2500/5045.16
    execution time (avg/stddev):   297.1758/0.01

目を見張るほどの差異はない
単純なケースにおいては順序が実行結果に影響してくることはなさそう