文系プログラマによるTIPSブログ

文系プログラマ脳の私が開発現場で学んだ事やプログラミングのTIPSをまとめています。

MySQLのoffsetの罠:その2:どれくらい遅いか検証

MySQLのoffset limitの検索がいかに遅いかの検証です〜


f:id:treeapps:20180418131549p:plain

www.bunkei-programmer.net

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してたらどうなってしまうのか・・・