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

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

MySQLでlimit offset専用一時テーブルを簡単に生成してページネーション処理を高速化する

変な挙動が有るので、今回はそこをピックアップします〜

f:id:treeapps:20180418131549p:plain

MySQLのlimit offset問題と言えば、誰もが知る有名な性能劣化問題です。

今回はcreate table select構文を利用し、primary keyとauto_incrementの強奪現象を利用して、簡単にこの問題を解消してみようと思います。

環境

検証する前に環境を確認しておきます。

MySQL v5.7(docker上のMySQLです)
CPU Core i9 9900k
MEM 64G
ストレージ SSD 1TB

MySQLのlimit offset問題

ざっくり解説しておくと、

select * from big_table limit 1000000, 1000;

こうすると一見1000件しかデータを参照しないように見えますが、実は100万件取得してから999000件を捨てているため、offset値が大きいほど遅くなるというものです。

よくある解決法

解決方法は大体皆答えが出ていて、その多くは

select * from big_table where id between 0 and 1000;
select * from big_table where id between 1001 and 2000;
select * from big_table where id between 2001 and 3000;

という、betweenでインデックスを効かせる形ですね

ここでありがちな問題があります。betweenするには綺麗に歯抜けのないid列的なものが必要だが、勿論そんなものは無いし既存テーブルを変更したくない場合、どうするかです。

mysqlのcreate tableは実はselectもできてしまうので、今回はこれを利用してさっくりやってみます。

create table select構文

dev.mysql.com

使い方は公式サイト↑参照ですが、最小構成で言うと以下のような事が可能というだけです。

create table fuga select * from hoge;

こうすると、hogeの構造データをコピーしたfugaというテーブルを作成する事ができます。(完全コピーではなくbtree index等はコピーされません)

部分合成

実はこの構文、意外と柔軟な記述ができて、以下のようにhogeの一部だけを持ってきた部分合成も可能です。

合成元となるテーブルは以下の定義とします。

drop table if exists hoge;
create table hoge(
  hoge_id bigint unsigned not null auto_increment comment 'ID',
  hoge_name varchar(100) not null comment '名称',
  primary key(hoge_id)
) engine=innodb charset=utf8mb4;

これからfugaテーブルを作るのですが、hoge_idだけが欲しいので、以下のようにします。

drop table if exists fuga;
create temporary table fuga(
  fuga_id bigint unsigned not null auto_increment comment 'fuga ID',
  primary key(fuga_id)
) engine=innodb charset=utf8mb4 comment '合成!!'
select
    hoge_id
  from
    hoge
  order by
    hoge_id asc
;

こうすると、

mysql> show create table fuga \G
*************************** 1. row ***************************
       Table: fuga
Create Table: CREATE TEMPORARY TABLE `fuga` (
  `fuga_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'fuga ID',
  `hoge_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'ID',
  PRIMARY KEY (`fuga_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='合成!!'

という列を持ったfugaテーブルが作成されます。


待て待て待て!なんか腑に落ちない事があるんだが!?

はい。では順番にその疑問と回答を見ていきましょう。

このfugaテーブルは一体?

これは、limit offset問題をbetweenで解決したいのですが、元のテーブル定義には手を入れたくありません。誰だってそうです。

なので別途採番テーブルを作成する事で、元の定義を壊さず解決しようとしています。

え?なんでfugaテーブルを作る必要が?

必要無い場合もあります。しかし100%そうとは言い切れません。

通常auto_incrementの列は連番が採番されます。しかし、歯抜けの値が入っている場合があります。

可能性としては以下が想定されます。

  • アプリケーション側でdelete insertしてidが飛び飛びになる。
  • auto_increment_increment 設定を変更し、採番値が奇数になるようにしている。(昔レプリケーションで複数台でIDが重複しないようにサーバ1では偶数、サーバ2では奇数、なんて事をしてる事もありました)
  • データ移行で仕方なくid値を100000から始めている。

こんな事が100%無いと言い切れないですし、考えるのも面倒です。なので信頼できる採番値を自分で作ってしまおう、というのがfugaテーブルです。

ん?temporary table?

しれっと「create temporary table fuga」と記述しました。

temporaryを付けると、そのセッションのみ閲覧・操作可能な一時テーブルを作成できます。

テーブルの作成時に TEMPORARY キーワードを使用できます。TEMPORARY テーブルは現在のセッションにのみ表示され、そのセッションが閉じられると自動的に削除されます。つまり、2 つの異なるセッションが同じ一時テーブル名を使用することができ、互いに、または同じ名前の既存の TEMPORARY 以外のテーブルと競合することはありません。(既存のテーブルは、一時テーブルが削除されるまで非表示になります。)一時テーブルを作成するには、CREATE TEMPORARY TABLES 権限が必要です。

https://dev.mysql.com/doc/refman/5.6/ja/create-table.html

create temporary tables権限が必要にはなりますが、他の人に見られずに、更に一時テーブルの削除漏れを無くす事ができるという機能です。

特に一時テーブルの削除漏れは結構致命的で、後になって「何この変なテーブル?削除していいの?」となり「解らん。怖くて消せないよねそれ・・」みたいな負債が貯まる要因にもなるので、極力付けた方がいいと思われます。

パフォーマンスも通常のテーブルと比較して特別大きな劣化は見られません。(そもそもID列が2列あるだけのテーブルですし)

待って、hogeとfugaの両方にPKとauto_incrementあるよね?

そうです。これこそが今回の記事の主題だったのです

もう一度おさらいしてみましょう。

drop table if exists hoge;
create table hoge(
  hoge_id bigint unsigned not null auto_increment comment 'ID',
  hoge_name varchar(100) not null comment '名称',
  primary key(hoge_id)
) engine=innodb charset=utf8mb4;

drop table if exists fuga;
create temporary table fuga(
  fuga_id bigint unsigned not null auto_increment comment 'fuga ID',
  primary key(fuga_id)
) engine=innodb charset=utf8mb4 comment '合成!!'
select
    hoge_id
  from
    hoge
  order by
    hoge_id asc
;

こうすると、以下ができます。

mysql> show create table fuga\G
*************************** 1. row ***************************
       Table: fuga
Create Table: CREATE TEMPORARY TABLE `fuga` (
  `fuga_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'fuga ID',
  `hoge_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'ID',
  PRIMARY KEY (`fuga_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='合成!!'

結果だけ見ると、後発のfuga_idがhoge_idからauto_incrementとprimary keyを強奪している事になりますね。

一体何故こんな事が起きるのかは解っていませんが、この強奪現象がlimit offset問題に利用できるのです

PKとauto_incrementの強奪現象を利用したlimit offsetの解決

繰り返し同じ定義を記述しますがご容赦下さい。

limit offsetしたいテーブル定義とデータの入り方

以下に対して高速にlimit offsetしたいのですが、残念な事にhoge_idが奇数になってしまっています。しかも一見に綺麗に奇数になっているようで、delete insertによってガタガタの歯抜けになっています。

-- テーブルの作成
mysql> create table hoge(
    ->   hoge_id bigint unsigned not null auto_increment comment 'ID',
    ->   hoge_name varchar(100) not null comment '名称',
    ->   primary key(hoge_id)
    -> ) engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

-- 歯抜けのテストデータを1000万件投入
mysql> load data local
    ->   infile '/tmp/test.tsv'
    ->   into table hoge
    ->   character set utf8
    ->   fields
    ->     terminated by '\t'
    ->     enclosed by ''
    ->   lines
    ->     terminated by '\n'
    -> ;
Query OK, 10000000 rows affected (25.99 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

-- テストデータの確認
mysql> select * from hoge limit 10;
+---------+---------------------+
| hoge_id | hoge_name           |
+---------+---------------------+
|       1 | 2019-04-21 00:15:23 |
|       3 | 2019-04-21 00:15:23 |
|       5 | 2019-04-21 00:15:23 |
|       7 | 2019-04-21 00:15:23 |
|       9 | 2019-04-21 00:15:23 |
|      11 | 2019-04-21 00:15:23 |
|      13 | 2019-04-21 00:15:23 |
|      15 | 2019-04-21 00:15:23 |
|      17 | 2019-04-21 00:15:23 |
|      19 | 2019-04-21 00:15:23 |
+---------+---------------------+
10 rows in set (0.00 sec)

別途採番テーブルを作成する

mysql> create temporary table fuga(
    ->   fuga_id bigint unsigned not null auto_increment comment 'fuga ID',
    ->   primary key(fuga_id)
    -> ) engine=innodb charset=utf8mb4 comment '合成!!'
    -> select
    ->     hoge_id
    ->   from
    ->     hoge
    ->   order by
    ->     hoge_id asc
    -> ;
Query OK, 10000000 rows affected (16.75 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

-- 登録されたデータを確認
mysql> select * from fuga limit 10;
+---------+---------+
| fuga_id | hoge_id |
+---------+---------+
|       1 |       1 |
|       2 |       3 |
|       3 |       5 |
|       4 |       7 |
|       5 |       9 |
|       6 |      11 |
|       7 |      13 |
|       8 |      15 |
|       9 |      17 |
|      10 |      19 |
+---------+---------+
10 rows in set (0.00 sec)

fuga_idはauto_incrementなので、自動的にfuga_idにnullがinsertされて採番値が入ったようですね。ちょっと空気を読み過ぎた挙動で不安な感じはします・・・

temporaryテーブルの場合は自セッション以外からは閲覧・操作不能なので、このfuga_idが他セッションによって追加・更新・削除される可能性は0になり、絶対に信頼できる歯抜けのない採番値になります。

hogeとfugaをinner joinしてbetweenする

mysql> select
    ->     h.*
    ->   from
    ->     fuga f
    ->     inner join hoge h
    ->       on f.hoge_id = h.hoge_id
    ->   where
    ->     f.fuga_id between 0 and 10
    -> ;
+---------+---------------------+
| hoge_id | hoge_name           |
+---------+---------------------+
|       1 | 2019-04-21 00:15:23 |
|       3 | 2019-04-21 00:15:23 |
|       5 | 2019-04-21 00:15:23 |
|       7 | 2019-04-21 00:15:23 |
|       9 | 2019-04-21 00:15:23 |
|      11 | 2019-04-21 00:15:23 |
|      13 | 2019-04-21 00:15:23 |
|      15 | 2019-04-21 00:15:23 |
|      17 | 2019-04-21 00:15:23 |
|      19 | 2019-04-21 00:15:23 |
+---------+---------------------+
10 rows in set (0.00 sec)

こんな感じです。後はbetweenの値を足したり引いたりするだけですね!

explainしてみる

mysql> explain
    -> select
    ->     h.*
    ->   from
    ->     fuga f
    ->     inner join hoge h
    ->       on f.hoge_id = h.hoge_id
    ->   where
    ->     f.fuga_id between 0 and 10
    -> ;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | f     | NULL       | range  | PRIMARY       | PRIMARY | 8       | NULL                  |   10 |   100.00 | Using where |
|  1 | SIMPLE      | h     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | kyoritsu_db.f.hoge_id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+

keyがPRIMARYのみの最高の形になりましたね。

現実ではここからjoinして別テーブルのカラムを取得したりフラグ値の考慮等で複雑化しますが、limit offset問題は解決しています。

速度比較をしてみる

通常のlimit offset版
mysql> select * from hoge limit 9999000, 1000;
+----------+---------------------+
| hoge_id  | hoge_name           |
+----------+---------------------+
| 19998001 | 2019-04-21 00:15:29 |
| 19998003 | 2019-04-21 00:15:29 |
・・・略・・・
| 19999997 | 2019-04-21 00:15:29 |
| 19999999 | 2019-04-21 00:15:29 |
+----------+---------------------+
1000 rows in set (1.65 sec)

CPUで無理やりぶん回しているので速く見えますが、それでもたった1000件の取得に 1.65sec もかかっています。

between版
mysql> select
    ->     h.*
    ->   from
    ->     fuga f
    ->     inner join hoge h
    ->       on f.hoge_id = h.hoge_id
    ->   where
    ->     f.fuga_id between 9999001 and 10000000
    -> ;
+----------+---------------------+
| hoge_id  | hoge_name           |
+----------+---------------------+
| 19998001 | 2019-04-21 00:15:29 |
| 19998003 | 2019-04-21 00:15:29 |
・・・略・・・
| 19999997 | 2019-04-21 00:15:29 |
| 19999999 | 2019-04-21 00:15:29 |
+----------+---------------------+
1000 rows in set (0.00 sec)

速過ぎて 0.00sec とか出てしまいました。


fugaをtemporaryテーブルにしていますが、この場合トランザクションを張りっぱなしにする必要がある(トランザクション終了時にtemporaryは消失する)ので、短いトランザクションにしたい場合は通常のcreate tableにし、必ずテーブル削除に「drop table if exists fuge;」をし、安全に削除するようにします。

雑感

記述していて思いましたが、「hoge」「fuga」だとどっちがどっちなのか解らなくなりますね・・・・失敗です。

今回の謎現象であるPKとauto_incrementの強奪現象について公式リファレンスを流し見したのですが、書いてないっぽい???ので、実に謎な挙動です。

もしかしたら将来的にこの強奪仕様がしれっと無くなる可能性があるので、そこは注意していきたいですね。