Help us understand the problem. What is going on with this article?

複数列indexの順序と選択性

More than 5 years have passed since last update.

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした