Necessity to use Index - kotaroito's notes

SQLに疎いのでインデックスの有無によりどれくらいレスポンスが違うのか試してみた。
スキーマはこんな感じ。post_dataにはuser_id, post_dateでインデックスを張る。

create table post_data (
post_id    int unsigned not null, #投稿ID
user_id     int unsigned not null, # ユーザID
post_date   int unsigned not null, # 投稿日時
content     varchar(100) not null  # 内容
) type=InnoDB;

alter table post_data
  add primary key (post_id),
  add index i1 (user_id, post_date),
  add index i2 (post_date);


create table post_data2 (
post_id    int unsigned not null, # 投稿ID
user_id     int unsigned not null, # ユーザID
post_date   int unsigned not null, # 投稿日時
content     varchar(100) not null  # 内容
) type=InnoDB;

alter table post_data2
  add primary key (post_id);

データは20万行くらい突っ込むことにする。user_idは1〜10,000の中からランダムに決めるが、公平のためpost_dataとpost_data2には同じuser_idを入れる。
ベンチマークスクリプトは以下。

use Benchmark;

timethese(500, {
        hasIndex => 'getList("post_data");',
        hasNoIndex => 'getList("post_data2");',
});

sub getList {
    my $table = shift;
    my $user_id = int(rand 10000) + 10001;
    my $dbh = getHandle(); #まあ適当に
    my $sth = $dbh_d->prepare(<<"SQL");
        select post, user_id, post_date,content
        from $table where user_id = ? order by post_date desc limit 10
SQL
    $sth->execute($user_id);
    while(my $rHash = $sth->fetchrow_hashref()) {
       #結果を取り出している風
    }
}

結果はベンチマークプログラム使うまでもなし。圧倒的にインデックスがないと遅い。

Benchmark: timing 500 iterations of hasIndex, hasNoIndex...
  hasIndex:  2 wallclock secs ( 0.12 usr +  0.17 sys =  0.29 CPU) @ 1724.14/s (n=500)
            (warning: too few iterations for a reliable count)
hasNoIndex: 329 wallclock secs ( 0.20 usr +  0.33 sys =  0.53 CPU) @ 943.40/s (n=500)

150倍以上違う。こんなのを実サービスでやっちまったら逝ってよし、ですね。
ちなみにwallclock secsは子プロセスまで含めた実時間で、CPUのところはメインプロセスのみの模様（SQLのSelectは含んでいない）。

topしてみると、mysqlでCPUを99%喰ってるｗｗ

top - 16:38:51 up  1:17,  3 users,  load average: 1.39, 0.93, 0.64
Tasks:  96 total,   1 running,  94 sleeping,   1 stopped,   0 zombie
Cpu(s): 55.6% us, 44.4% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.0% si
Mem:    255736k total,   249092k used,     6644k free,    13364k buffers
Swap:   524280k total,        0k used,   524280k free,   113224k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 2705 mysql     16   0  118m  22m 4260 S 98.9  9.1  16:22.41 mysqld

結論

インデックスはきちんと張りましょう。