Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
11
Help us understand the problem. What is going on with this article?
@k_o_gj

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

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

11
Help us understand the problem. What is going on with this article?
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
k_o_gj

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
11
Help us understand the problem. What is going on with this article?