MySQLのoffset limitの検索がいかに遅いかの検証です〜
MySQLのoffsetは遅いです。主に、offset値を見つけるまでの処理が遅いのです。
せっかくだから、今回はどのくらい遅いか試してみます。
PK1個で1000万件ほど入れてみます。
mysql> desc lotest2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | col1 | bigint(20) | NO | PRI | NULL | | | col2 | varchar(30) | NO | | NULL | | | col3 | varchar(30) | NO | | NULL | | ・・・省略・・・ | col18 | varchar(30) | NO | | NULL | | | col19 | varchar(30) | NO | | NULL | | | col20 | varchar(30) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 20 rows in set (0.08 sec) mysql> select * from lotest; +------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+ | col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11 | col12 | col13 | col14 | col15 | col16 | col17 | col18 | col19 | col20 | +------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+ | 1 | a1 | b1 | c1 | d1 | e1 | f1 | g1 | h1 | i1 | j1 | k1 | l1 | m1 | n1 | o1 | p1 | q1 | r1 | s1 | | 2 | a2 | b2 | c2 | d2 | e2 | f2 | g2 | h2 | i2 | j2 | k2 | l2 | m2 | n2 | o2 | p2 | q2 | r2 | s2 | ・・・・省略・・・・ | 9999999 | a9999999 | b9999999 | c9999999 | d9999999 | e9999999 | f9999999 | g9999999 | h9999999 | i9999999 | | 10000000 | a10000000 | b10000000 | c10000000 | d10000000 | e10000000 | f10000000 | g10000000 | h10000000 | i10000000 | +----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
ここで末尾10件を取得すると、
mysql> select * from lotest limit 10 offset 9999990; +----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | +----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | 9999991 | a9999991 | b9999991 | c9999991 | d9999991 | e9999991 | f9999991 | g9999991 | h9999991 | i9999991 | | 9999992 | a9999992 | b9999992 | c9999992 | d9999992 | e9999992 | f9999992 | g9999992 | h9999992 | i9999992 | | 9999993 | a9999993 | b9999993 | c9999993 | d9999993 | e9999993 | f9999993 | g9999993 | h9999993 | i9999993 | | 9999994 | a9999994 | b9999994 | c9999994 | d9999994 | e9999994 | f9999994 | g9999994 | h9999994 | i9999994 | | 9999995 | a9999995 | b9999995 | c9999995 | d9999995 | e9999995 | f9999995 | g9999995 | h9999995 | i9999995 | | 9999996 | a9999996 | b9999996 | c9999996 | d9999996 | e9999996 | f9999996 | g9999996 | h9999996 | i9999996 | | 9999997 | a9999997 | b9999997 | c9999997 | d9999997 | e9999997 | f9999997 | g9999997 | h9999997 | i9999997 | | 9999998 | a9999998 | b9999998 | c9999998 | d9999998 | e9999998 | f9999998 | g9999998 | h9999998 | i9999998 | | 9999999 | a9999999 | b9999999 | c9999999 | d9999999 | e9999999 | f9999999 | g9999999 | h9999999 | i9999999 | | 10000000 | a10000000 | b10000000 | c10000000 | d10000000 | e10000000 | f10000000 | g10000000 | h10000000 | i10000000 | +----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ 10 rows in set (9.95 sec) mysql> explain select * from lotest limit 10 offset 9999990; +----+-------------+--------+------+---------------+------+---------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+----------+-------+ | 1 | SIMPLE | lotest | ALL | NULL | NULL | NULL | NULL | <span class="deco" style="font-weight:bold;color:orange">10124583</span> | | +----+-------------+--------+------+---------------+------+---------+------+----------+-------+ 1 row in set (0.03 sec)
結果はフルスキャンの9.95秒、やっぱ遅いです。
今度はカラムを10個から20個に増やしてみます。
mysql> select * from lotest limit 10 offset 9999990; +----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11 | col12 | col13 | col14 | col15 | col16 | col17 | col18 | col19 | col20 | +----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | 9999991 | a9999991 | b9999991 | c9999991 | d9999991 | e9999991 | f9999991 | g9999991 | h9999991 | i9999991 | j9999991 | k9999991 | l9999991 | m9999991 | n9999991 | o9999991 | p9999991 | q9999991 | r9999991 | s9999991 | | 9999992 | a9999992 | b9999992 | c9999992 | d9999992 | e9999992 | f9999992 | g9999992 | h9999992 | i9999992 | j9999992 | k9999992 | l9999992 | m9999992 | n9999992 | o9999992 | p9999992 | q9999992 | r9999992 | s9999992 | | 9999993 | a9999993 | b9999993 | c9999993 | d9999993 | e9999993 | f9999993 | g9999993 | h9999993 | i9999993 | j9999993 | k9999993 | l9999993 | m9999993 | n9999993 | o9999993 | p9999993 | q9999993 | r9999993 | s9999993 | | 9999994 | a9999994 | b9999994 | c9999994 | d9999994 | e9999994 | f9999994 | g9999994 | h9999994 | i9999994 | j9999994 | k9999994 | l9999994 | m9999994 | n9999994 | o9999994 | p9999994 | q9999994 | r9999994 | s9999994 | | 9999995 | a9999995 | b9999995 | c9999995 | d9999995 | e9999995 | f9999995 | g9999995 | h9999995 | i9999995 | j9999995 | k9999995 | l9999995 | m9999995 | n9999995 | o9999995 | p9999995 | q9999995 | r9999995 | s9999995 | | 9999996 | a9999996 | b9999996 | c9999996 | d9999996 | e9999996 | f9999996 | g9999996 | h9999996 | i9999996 | j9999996 | k9999996 | l9999996 | m9999996 | n9999996 | o9999996 | p9999996 | q9999996 | r9999996 | s9999996 | | 9999997 | a9999997 | b9999997 | c9999997 | d9999997 | e9999997 | f9999997 | g9999997 | h9999997 | i9999997 | j9999997 | k9999997 | l9999997 | m9999997 | n9999997 | o9999997 | p9999997 | q9999997 | r9999997 | s9999997 | | 9999998 | a9999998 | b9999998 | c9999998 | d9999998 | e9999998 | f9999998 | g9999998 | h9999998 | i9999998 | j9999998 | k9999998 | l9999998 | m9999998 | n9999998 | o9999998 | p9999998 | q9999998 | r9999998 | s9999998 | | 9999999 | a9999999 | b9999999 | c9999999 | d9999999 | e9999999 | f9999999 | g9999999 | h9999999 | i9999999 | j9999999 | k9999999 | l9999999 | m9999999 | n9999999 | o9999999 | p9999999 | q9999999 | r9999999 | s9999999 | | 10000000 | a10000000 | b10000000 | c10000000 | d10000000 | e10000000 | f10000000 | g10000000 | h10000000 | i10000000 | j10000000 | k10000000 | l10000000 | m10000000 | n10000000 | o10000000 | p10000000 | q10000000 | r10000000 | s10000000 | +----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ 10 rows in set (17.42 sec)
17.42秒もかかっています・・・カラム数が100以上あって且つ沢山JOINしてたらどうなってしまうのか・・・
