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に差が見られるかもしれない。
