9
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

複数列indexの順序と選択性

Posted at

複数列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

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

9
11
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
9
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?