メンターをしている後輩くんのソースをレビューしててindexが足りてなかった｡それがなんでダメなのか雑に調べた｡

player_hoge
- id
- player_id
- index_id
- created_at
- updated_at

みたいなテーブルがある｡使われ方はplayer_idとindex_idの2つでselectする機会が多い｡

そのテーブルにplayer_id のみindexが貼ってあった｡こういう場合は player_id と index_id の2つにindexを貼ったほうが早くなる｡はず｡

なので調べるように

index 貼ってない
player_id いindexを貼った場合
player_id, index_id に index を貼った場合

sqlはこんな感じ

DROP TABLE IF EXISTS `no_index`;

CREATE TABLE `no_index` (
  `id` INTEGER unsigned NOT NULL auto_increment,
  `player_id` INTEGER unsigned NOT NULL DEFAULT 0,
  `index_id` INTEGER unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

DROP TABLE IF EXISTS `one_index`;

CREATE TABLE `one_index` (
  `id` INTEGER unsigned NOT NULL auto_increment,
  `player_id` INTEGER unsigned NOT NULL DEFAULT 0,
  `index_id` INTEGER unsigned NOT NULL DEFAULT 0,
  INDEX `player_id_idx` (`player_id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

DROP TABLE IF EXISTS `both_index`;

CREATE TABLE `both_index` (
  `id` INTEGER unsigned NOT NULL auto_increment,
  `player_id` INTEGER unsigned NOT NULL DEFAULT 0,
  `index_id` INTEGER unsigned NOT NULL DEFAULT 0,
  INDEX `player_id_index_id_idx` (`player_id`, `index_id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

使ったデータはこんな感じで作りました｡で調査ようのscriptはこんな感じです

use DBI;
use DBD::mysql;
use Benchmark qw/timethese cmpthese/;

my $d = 'DBI:mysql:index_research';
my $u = 'root';
my $p = '';
my $dbh = DBI->connect($d, $u, $p);

my $result = timethese(10, {
    'player_id index_id use no index' => sub {
        for my $id (1..100) {
            my $player_id = $id * 20;
            my $sql = "SELECT * FROM no_index WHERE player_id = $player_id  AND index_id = $id";
            my $sth = $dbh->prepare($sql);
            $sth->execute;
            $sth->finish;
        }
    },
    'player_id index_id use player_id index' => sub {
        for my $id (1..100) {
            my $player_id = $id * 20;
            my $sql = "SELECT * FROM one_index WHERE player_id = $player_id  AND index_id = $id";
            my $sth = $dbh->prepare($sql);
            $sth->execute;
            $sth->finish;
        }
    },
    'player_id index_id use player_id index_id index' => sub {
        for my $id (1..100) {
            my $player_id = $id * 20;
            my $sql = "SELECT * FROM both_index WHERE player_id = $player_id  AND index_id = $id";
            my $sth = $dbh->prepare($sql);
            $sth->execute;
            $sth->finish;
        }
    },
});

cmpthese $result;

で結果が以下

player_id index_id use no index                 100.0/s                              --                                   -30%                                            -70%
player_id index_id use player_id index            143/s                             43%                                     --                                            -57%
player_id index_id use player_id index_id index   333/s                            233%                                   133%                                              --

やっぱりplayer_id と index_id 両方にindex貼ったほうが早かった｡

じゃあ､ player_id だけ select して取る場合はどちらが早いのかと思ったのでそれも調べた

my $result_only_player_id = timethese(10, {
    'player_id use no index' => sub {
        for my $id (1..100) {
            my $player_id = $id * 20;
            my $sql = "SELECT * FROM no_index WHERE player_id = $player_id";
            my $sth = $dbh->prepare($sql);
            $sth->execute;
            $sth->finish;
        }
    },
    'player_id use player_id index' => sub {
        for my $id (1..100) {
            my $player_id = $id * 20;
            my $sql = "SELECT * FROM one_index WHERE player_id = $player_id";
            my $sth = $dbh->prepare($sql);
            $sth->execute;
            $sth->finish;
        }
    },
    'player_id use player_id index_id index' => sub {
        for my $id (1..100) {
            my $player_id = $id * 20;
            my $sql = "SELECT * FROM both_index WHERE player_id = $player_id";
            my $sth = $dbh->prepare($sql);
            $sth->execute;
            $sth->finish;
        }
    },
});

cmpthese $result_only_player_id;

結果がこちら

player_id use no index                 90.9/s                     --                          -55%                                   -64%
player_id use player_id index           200/s                   120%                            --                                   -20%
player_id use player_id index_id index  250/s                   175%                           25%                                     --

player_id だけの方が早いかと思ったけどそうでもなかった｡データ量がおこまで多くないと違うのかもしれない｡

まとめ

index貼るときはどう使うかも考えて貼ったほうがいいですね｡

@Konboi memo

主に技術に関してつらつらと。

雑にindexの効果を調べた

まとめ