Slow Query 対策 - シーケンシャルリードを行いたい

背景

数百万行のデータを保持するMySQLから、定時でSolrのインデックス構築用にMySQLから全行の取得を行っていた。

ただし、サービスインしているためクエリ一発で取得するのは負荷のためNGであり、繰り返し一定行数データを取得する必要があった。

今回、既存クエリでSlow Queryが発生していたため、対応が必要になった。

今回の説明のため、下記の単純なテーブルを例に挙げる。 また、ベンチマークの取得に時間がかかりすぎたのでレコード数は20万に抑えた。

CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

既存クエリ

LIMIT句でoffsetを指定しながら1000件ずつ取得していた。

SELECT * FROM user ORDER BY id ASC LIMIT 10000,1000

EXPLAINの結果は次の通り。

mysql> EXPLAIN SELECT * FROM user ORDER BY id ASC LIMIT 10000,1000;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 4       | NULL | 11000 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------+

offsetで取ってくる順序を保証しないと、繰り返し実行時に重複してデータを取得する可能性があるので、ORDER句は必須。

上記の通りフルインデックススキャンになっている。

offset位置までのシーケンシャルリード＋取得件数分のリードが発生するため、offsetが大きくなればなるほど遅くなる。

改善案1 - BETWEENを使って範囲検索を狙う

BETWEENを使った範囲検索であれば、ソートも不要になりかなり高速になる。

SELECT * FROM user WHERE id BETWEEN 10001 AND 11000

EXPLAIN結果

mysql> EXPLAIN SELECT * FROM user WHERE id BETWEEN 10001 AND 11000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | range | PRIMARY       | PRIMARY | 4       | NULL |  999 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

改善案2 - WHERE句で開始位置を限定する

サービス上で削除が頻繁に行われる場合はデータが虫食いになるので、BETWEENだと毎回期待した件数分を取得できるとは限らない。

そのため、繰り返し数が多くなる傾向があり、これを嫌う開発者も存在する。

そういった場合を考慮するのであれば、WHERE句で開始位置を指定してORDER、LIMITを使うと範囲検索になるので、既存よりは高速化できる。

SELECT * FROM user WHERE id > 10000 ORDER BY id ASC LIMIT 1000

EXPLAIN結果

mysql> EXPLAIN SELECT * FROM user WHERE id > 10000 ORDER BY id ASC LIMIT 1000;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | user  | range | PRIMARY       | PRIMARY | 4       | NULL | 100155 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

ベンチマーク

下記コードで上記3パターンについてベンチマークを行い比較した。

use strict;
use warnings;

use DBI;
use Benchmark qw(:all);

my $dbh = DBI->connect('dbi:mysql:sota', 'root', '');

my $sth1 = $dbh->prepare(q{
    SELECT * FROM user ORDER BY id ASC LIMIT ?, ?
});

my $sth2 = $dbh->prepare(q{
    SELECT * FROM user WHERE id BETWEEN ? AND ?
});

my $sth3 = $dbh->prepare(q{
    SELECT * FROM user WHERE id > ? ORDER BY id ASC LIMIT ?
});

cmpthese(100, {
    before => sub {
        foreach my $i ( 0 .. 199 ) {
            my $start = $i * 1000;
            $sth1->execute($start, 1000);
        }
    },
    after1 => sub {
        foreach my $i ( 0 .. 199 ) {
            my $start = $i * 1000 + 1;
            my $end = ( $i + 1 ) * 1000;
            $sth2->execute($start, $end);
        }
    },
    after2 => sub {
        foreach my $i ( 0 .. 199 ) {
            my $start = $i * 1000;
            $sth3->execute($start, 1000);
        }
    },
});

結果を以下に示す。

         Rate    1    2    3
before 20.6/s   -- -34% -34%
after1 31.2/s  51%   --  -0%
after2 31.2/s  51%   0%   --

既存クエリが最も遅く、実行中にSlow Queryが多発していた。

改善案1、改善案2は今回はそれほど差は出なかった。 もっとレコード数が多い場合は改善案1と改善案2に差が見られるかもしれない。