Oracle11gでのLIMIT,OFFSETするクエリをMySQL8のwindow関数で利用してみる
諸事情でWindow関数を触る機会があって、ちょっと前にyokuさんに教えてもらったWindow関数ネタを思い出したので１つ。
Oracle11gにはMySQLでおなじみ？のLIMIT,OFFSETがない。
そこで（いろいろやり方はあるけど）window関数を使ってあらわしたりする。
例えば id(number), value(varchar2) のテーブルならこんな感じ。
SELECT * FROM
(
SELECT row_number() over order by id asc) as row_number,
value
FROM t1
) a
WHERE a.row_number between 500 and 600;
MySQL8からwindow関数が追加され、row_number()とかもつかえるのでこのクエリをそのままMySQLで使えるか検討してみる。 環境はMySQL8.0.13。データは前回のブログで使った100万行くらい入ってるシンプルなテーブルのやつ
mysql [d1]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`value` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
mysql [d1]> SELECT COUNT(1) FROM t1;
+----------+
| COUNT(1) |
+----------+
| 1011000 |
+----------+
1 row in set (0.15 sec)
mysql [d1]> SELECT * FROM
-> (
-> SELECT row_number() over (order by id) as row_no,
-> value
-> FROM t1
-> ) a
-> WHERE a.row_no between 500 and 600;
+--------+------------+
| row_no | value |
+--------+------------+
| 500 | 1zwujbeNGW |
| 501 | qz8df1RHcc |
| 502 | XKH92CeXGa |
| 503 | zWc7JQeOH0 |
| 504 | AkwB5KClKD |
| 505 | rP1ZUAOc80 |
~~
| 595 | oDdB7ZR1jD |
| 596 | kqUuhKOELK |
| 597 | o4mAu0y2KG |
| 598 | 5e0EbD9OXW |
| 599 | yUyRUrOy9d |
| 600 | 5J4wVaIKOJ |
+--------+------------+
101 rows in set (0.91 sec)
お、ちゃんと出力された。SQL的には（当たり前だけど）問題ないっぽい。 これでOracleのテーブルをMySQLに移行するときのSQLの書き換える量がへりますね。（めでたしめでたし。
ただ、0.91 secはやっぱ遅い気がする。
mysql [d1]> SELECT id, value FROM t1 ORDER BY id LIMIT 101 OFFSET 499; +-----+------------+ | id | value | +-----+------------+ | 500 | 1zwujbeNGW | | 501 | qz8df1RHcc | | 502 | XKH92CeXGa | | 503 | zWc7JQeOH0 | | 504 | AkwB5KClKD | | 505 | rP1ZUAOc80 | | 506 | WJSjD0NWJb | | 507 | 3j6lvvGYvH | ~~ | 598 | 5e0EbD9OXW | | 599 | yUyRUrOy9d | | 600 | 5J4wVaIKOJ | +-----+------------+ 101 rows in set (0.00 sec)
。。。ですよね〜。
ということでOFFSETを深くしてprofilingをとってみる。
mysql [d1]> SELECT * FROM
-> (
-> SELECT row_number() over (order by id) as row_no,
-> value
-> FROM t1
-> ) a
-> WHERE a.row_no between 1000000 and 1000010;
+---------+------------+
| row_no | value |
+---------+------------+
| 1000000 | 5UGdDjX6sI |
| 1000001 | 1BmxMSJMNO |
| 1000002 | fRA4G96KoZ |
| 1000003 | 0PEhFBERn3 |
| 1000004 | x9N5k3eez5 |
| 1000005 | KeIyt1RHX5 |
| 1000006 | BaDGNWrvPj |
| 1000007 | hnpbYAQtKW |
| 1000008 | XI1a2RAy0G |
| 1000009 | ZmnI8XobRb |
| 1000010 | N6XXZnw3mo |
+---------+------------+
11 rows in set (1.01 sec)
mysql [d1]> show profile;
+----------------------------+----------+
| Status | Duration |
+----------------------------+----------+
| starting | 0.000550 |
| checking permissions | 0.000024 |
| Opening tables | 0.000390 |
| init | 0.000034 |
| System lock | 0.000066 |
| optimizing | 0.000010 |
| optimizing | 0.000007 |
| statistics | 0.000054 |
| preparing | 0.000033 |
| Creating tmp table | 0.000058 |
| statistics | 0.000023 |
| preparing | 0.000015 |
| executing | 0.000032 |
| Sending data | 0.000015 |
| executing | 0.000004 |
| Sending data | 0.000008 |
| Creating sort index | 0.973423 |
| end | 0.000040 |
| query end | 0.000014 |
| waiting for handler commit | 0.000030 |
| query end | 0.000017 |
| removing tmp table | 0.028293 |
| query end | 0.004465 |
| removing tmp table | 0.000023 |
| query end | 0.000015 |
| closing tables | 0.000041 |
| freeing items | 0.000094 |
| cleaning up | 0.000094 |
+----------------------------+----------+
28 rows in set, 1 warning (0.00 sec)
Warning (Code 1287): 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead
mysql [d1]> SELECT id, value FROM t1 ORDER BY id LIMIT 11 OFFSET 999999;
+---------+------------+
| id | value |
+---------+------------+
| 1000000 | 5UGdDjX6sI |
| 1000001 | 1BmxMSJMNO |
| 1000002 | fRA4G96KoZ |
| 1000003 | 0PEhFBERn3 |
| 1000004 | x9N5k3eez5 |
| 1000005 | KeIyt1RHX5 |
| 1000006 | BaDGNWrvPj |
| 1000007 | hnpbYAQtKW |
| 1000008 | XI1a2RAy0G |
| 1000009 | ZmnI8XobRb |
+---------+------------+
10 rows in set (0.22 sec)
mysql [d1]> SELECT id, value FROM t1 ORDER BY id LIMIT 11 OFFSET 999999;
+---------+------------+
| id | value |
+---------+------------+
| 1000000 | 5UGdDjX6sI |
| 1000001 | 1BmxMSJMNO |
| 1000002 | fRA4G96KoZ |
| 1000003 | 0PEhFBERn3 |
| 1000004 | x9N5k3eez5 |
| 1000005 | KeIyt1RHX5 |
| 1000006 | BaDGNWrvPj |
| 1000007 | hnpbYAQtKW |
| 1000008 | XI1a2RAy0G |
| 1000009 | ZmnI8XobRb |
| 1000010 | N6XXZnw3mo |
+---------+------------+
11 rows in set (0.19 sec)
mysql [d1]> show profile;
+----------------------------+----------+
| Status | Duration |
+----------------------------+----------+
| starting | 0.000265 |
| checking permissions | 0.000019 |
| Opening tables | 0.000135 |
| init | 0.000022 |
| System lock | 0.000065 |
| optimizing | 0.000011 |
| statistics | 0.000046 |
| preparing | 0.000031 |
| Sorting result | 0.000009 |
| executing | 0.000008 |
| Sending data | 0.189200 |
| end | 0.000031 |
| query end | 0.000009 |
| waiting for handler commit | 0.000016 |
| query end | 0.000014 |
| closing tables | 0.000020 |
| freeing items | 0.000038 |
| cleaning up | 0.000048 |
+----------------------------+----------+
18 rows in set, 1 warning (0.00 sec)
Warning (Code 1287): 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead
window関数の利用するするほうはCreating tmp tableしてCreating sort indexしてるがこのsort indexが重いらしい。 件数がすくないとたいしたことないかもしれないけど無難にlimitに書き換えてあげたほうがよいかもしれない。
（書いてから気づいたけどbetweenじゃなくて普通にid > ? and id <= ?ってすればよかった）
