Slow Query 対策 - コンテンツの前後リンク

背景

例えば日記などのコンテンツのように、ユーザーとコンテンツが1対Nで存在するケースで、任意のコンテンツで前後のコンテンツのリンクを出したい。そのためには、あるコンテンツのIDの近傍データにアクセスしてタイトルなどを取ってくる必要がある。

想定するテーブルは下記の通り。

CREATE TABLE `diary` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `title` varchar(100) NOT NULL,
  `body` text NOT NULL,
  `post_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1` (`user_id`,`post_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

idx_1は任意のユーザーの日記を更新順で取得するためのものである。ユーザーの日記一覧で必要になるクエリで利用する。

user_idのばらつきにより既存クエリでは使われるインデックスが変わってくるので、今回のケースではユーザー数が1000、1ユーザーあたり約1000件の日記を保持するケースで考える。

既存クエリ

次の日記を取得する場合では、閲覧中の日記IDよりも多いものをWHERE句で限定して、IDでソートして最小のものを1件取得していた。

SELECT id, user_id, title FROM diary WHERE user_id = 1 AND id > 498740 ORDER BY id ASC LIMIT 1;

EXPLAIN結果は次の通り。

mysql> EXPLAIN SELECT id, user_id, title FROM diary WHERE user_id = 1 AND id > 498740 ORDER BY id ASC LIMIT 1;
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | diary | ref  | idx_1         | idx_1 | 4       | const | 1005 | Using where; Using filesort |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------+

filesortが発生しており、これがソート対象の件数によってはSlow Queryになることがある。

改善案

サブクエリを利用して、指定したIDより大きくて最小のものを求め、それをWHERE句で指定するようにする。

SELECT id, user_id, title FROM diary WHERE id = 
(SELECT MIN(id) FROM diary WHERE user_id = 1 AND id > 498740);

EXPLAIN結果は次の通り。

mysql> EXPLAIN SELECT id, user_id, title FROM diary WHERE id = 
    -> (SELECT MIN(id) FROM diary WHERE user_id = 1 AND id > 498740);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | diary | const | PRIMARY       | PRIMARY | 4       | const |    1 |                          |
|  2 | SUBQUERY    | diary | ref   | PRIMARY,idx_1 | idx_1   | 4       |       | 1005 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+

filesortが消えた

ベンチマーク

下記コードで上記2パターンについてベンチマークを行い比較した。一番古い日記から順番に次の日記へ辿っていくケースである。

use strict;
use warnings;

use DBI;
use Benchmark qw(:all);

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

my $sth1 = $dbh->prepare(q{
    SELECT id, user_id, title FROM diary FORCE INDEX(idx_1) WHERE user_id = ? AND id > ? ORDER BY id ASC LIMIT 1
});

my $sth2 = $dbh->prepare(q{
    SELECT id, user_id, title FROM diary WHERE id = 
    (SELECT MIN(id) FROM diary WHERE user_id = ? AND id > ?)
});

my $user_id = 1;
my $start_id = do {
    my ($row) = $dbh->selectrow_array('SELECT MIN(id) FROM diary WHERE user_id = ?', undef, $user_id);
    +$row;
};

cmpthese(10, {
    before => sub {
        my $id = $start_id;

        while ( $id ) {
            $sth1->execute($user_id, $id);
            my $row = $sth1->fetch;
            $id = $row ? $row->[0] : undef;
        }
    },
    after => sub {
        my $id = $start_id;

        while ( $id ) {
            $sth2->execute($user_id, $id);
            my $row = $sth2->fetch;
            $id = $row ? $row->[0] : undef;
        }
    },
});

結果を以下に示す。

         Rate before  after
before 17.2/s     --   -17%
after  20.8/s    21%     --

改善案の方が高速になった。

余談1

実は、(user_id, id)のインデックスを張ればどちらも高速化される。特に既存クエリはfilesortが発生しなくなるので、修正の必要がなくなる。

         Rate  after before
after  35.7/s     --    -4%
before 37.2/s     4%     --

ただ、今回遭遇した現場では、既に100近くのDBにshardingされており、総レコード数も数千万のオーダーのため、ALTER文が実行しづらい状況にあった。

余談2

前後リンクはSEO対策の一環で、検索クローラーの回遊性向上のために必要になった。しかし、普段ユーザーがアクセスしないデータにまでアクセスが発生するようになったため、I/Oがかなり増えたのが痛い。リンクに日記のタイトルを出さなくてもよいのであれば、色々ごまかし方はあるのだが…。