MySQLが得意なこと、不得意なこと(仮)

2021/12/17 Engineers in CARTA vol.2 #MySQL
https://voyagegroup.connpass.com/event/231708/

得意なことというより特異なことを紹介するコーナーになってしまった

Transcript

1. MySQLが得意なこと、苦手なこと(仮) 　 2021/12/17 yoku0825 Engineers in CARTA vol.2 #MySQL
2. TL;DR 1/63
3. MySQLが得意なこと/Oracleの方が得意そうなこと 小さな結果セットを、インデックスを使い切って絞りだすのは得意 インデックスを使い切らずに取り出したり、結果セットがそもそも大きいものはOracleの方が 得意なはず ‐ それはそういうものだと割り切って、「じゃあ如何にMySQLが苦手な処理を得意な 処理の側に手繰り寄せていくか」がMySQLと上手く付き合うのに必要な考え方だと 思う 明示的なテンポラリーテーブルを作るのとかもそう ‐ CREATE TEMPORARY TABLEは（吊るしのMySQLなら） read_only = ON なレプリカでも作成できる ‐ 本気でMySQLを楽しむなら、MySQLは良くも悪くも柔軟すぎるので それを逆手に取ってレプリケーションで上手くゴニョるのが醍醐味 レプリカでインデックス作って/データのサクッとスイッチオーバーするとか、そういうやつ ‐ 2/63
4. ＼こんばんわ／ yoku0825＠とある企業のDBA オラクらない ‐ ポスグらない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 3/63
5. MySQL < Oracle ? Oracleがわからないので真偽はわからない MySQL < NULL は NULL ‐ 「MySQLではこういうのは危ない」は言えるけど、それがOracleでできたかどうか は私はわかりません。。 4/63
6. MySQLにご用心 sql_mode 暗黙の型変換 ネクストキーロック ORDER BY .. LIMIT .. OFFSET AUTO_INCREMENT vs. シーケンス 5/63
7. sql_mode 「ちょっとそれってどうなの？」な動作を許せるようにするフラグ かつては「ちゃんと制限を効かせるようにするフラグ」だけど、今日となっては制限が強い方 がデフォルトなので「敢えて弱くしたい時に」フラグを下ろすのが一般的ではないか ‐ 特にヤバいのは STRICT_TRANS_TABLES と ONLY_FULL_GROUP_BY 6/63
8. STRICT_TRANS_TABLES フツーはINT型にVARCHARな文字列を突っ込めばエラーになるべき mysql> CREATE TABLE t1 (num INT); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t1 VALUES ('one'); ERROR 1366 (HY000): Incorrect integer value: 'one' for column 'num' at row 1 mysql> SELECT * FROM t1; Empty set (0.00 sec) 7/63
9. STRICT_TRANS_TABLES このフラグが下りていると主に「本来キャスト不可能なデータを突っ込もうとして もエラーにならなく」なる mysql> SET SESSION sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES ('one'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------+ | Warning | 1366 | Incorrect integer value: 'one' for column 'num' at row 1 | +---------+------+----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM t1; +------+ | num | +------+ | 0 | +------+ 1 row in set (0.00 sec) 8/63
10. STRICT_TRANS_TABLES 「長さ制約を超えた文字列」もキャスト不可能なデータ mysql> CREATE TABLE t2 (val VARCHAR(2)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t2 VALUES ('two'); ERROR 1406 (22001): Data too long for column 'val' at row 1 mysql> SELECT * FROM t2; Empty set (0.00 sec) 9/63
11. STRICT_TRANS_TABLES 黙って入らない分の文字列が切り捨てられる mysql> SET SESSION sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t2 VALUES ('two'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1265 | Data truncated for column 'val' at row 1 | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM t2; +------+ | val | +------+ | tw | +------+ 1 row in set (0.00 sec) 10/63
12. STRICT_TRANS_TABLES 文字列の '1' と整数の 1 のような可逆なキャストは成功する 暗黙の型変換との兼ね合いに注意… mysql> CREATE TABLE t2 (num INT, val varchar(32), registered datetime); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t2 VALUES ('1', 'one', 11111111); -- 8桁の整数はYYYYMMDDのDATE/DATETI ME型に可逆なキャストがされる Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM t2; +------+------+---------------------+ | num | val | registered | +------+------+---------------------+ | 1 | one | 1111-11-11 00:00:00 | +------+------+---------------------+ 1 row in set (0.00 sec) 11/63
13. ONLY_FULL_GROUP_BY GROUP BY の出てくるクエリは、select_listは全て GROUP BY か集約関数に含まれ るべき continent と indepyear は1対1で紐づかないので、そんなものを許すと（本来）何が返ってく るかわからない ‐ mysql> SELECT continent, COUNT(*), indepyear FROM country GROUP BY continent; ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains n onaggregated column 'world.country.IndepYear' which is not functionally dependent on colu mns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 12/63
14. ONLY_FULL_GROUP_BY mysql> SET SESSION sql_mode = ''; Query OK, 0 rows affected (0.01 sec) mysql> SELECT continent, COUNT(*), indepyear FROM country GROUP BY continent; +---------------+----------+-----------+ | continent | COUNT(*) | indepyear | +---------------+----------+-----------+ | North America | 37 | NULL | | Asia | 51 | 1919 | | Africa | 58 | 1975 | | Europe | 46 | 1912 | | South America | 14 | 1816 | | Oceania | 28 | NULL | | Antarctica | 5 | NULL | +---------------+----------+-----------+ 7 rows in set (0.03 sec) 13/63
15. ONLY_FULL_GROUP_BY このフラグを下ろした時に、 GROUP BY リストにも集約関数にもなっていないやつ は何が表示されているの？ 不定 ‐ ちなみにこの動作をフォローする(?)ために ANY_VALUE という疑似集約関数が提供されている ‐ mysql> SELECT continent, COUNT(*), ANY_VALUE(indepyear) FROM country GROUP BY continent; +---------------+----------+----------------------+ | continent | COUNT(*) | ANY_VALUE(indepyear) | +---------------+----------+----------------------+ | North America | 37 | NULL | | Asia | 51 | 1919 | | Africa | 58 | 1975 | | Europe | 46 | 1912 | | South America | 14 | 1816 | | Oceania | 28 | NULL | | Antarctica | 5 | NULL | +---------------+----------+----------------------+ 7 rows in set (0.01 sec) 14/63
16. ONLY_FULL_GROUP_BY 使うインデックスが変わると結果セットが変わる。 mysql> SELECT continent, ANY_VALUE(name), ANY_VALUE(indepyear) FROM country GROUP BY continent; +---------------+------------------+----------------------+ | continent | ANY_VALUE(name) | ANY_VALUE(indepyear) | +---------------+------------------+----------------------+ | Asia | Hong Kong | NULL | | Europe | Faroe Islands | NULL | | North America | Aruba | NULL | | Africa | Western Sahara | NULL | | Oceania | American Samoa | NULL | | Antarctica | Antarctica | NULL | | South America | Falkland Islands | NULL | +---------------+------------------+----------------------+ 7 rows in set (0.00 sec) mysql> SELECT continent, ANY_VALUE(name), ANY_VALUE(indepyear) FROM country GROUP BY continent; +---------------+-----------------+----------------------+ | continent | ANY_VALUE(name) | ANY_VALUE(indepyear) | +---------------+-----------------+----------------------+ | North America | Aruba | NULL | | Asia | Afghanistan | 1919 | | Africa | Angola | 1975 | | Europe | Albania | 1912 | | South America | Argentina | 1816 | | Oceania | American Samoa | NULL | | Antarctica | Antarctica | NULL | +---------------+-----------------+----------------------+ 7 rows in set (0.00 sec) 15/63
17. ONLY_FULL_GROUP_BY ついでに、集約した行とANY_VALUEの行の一貫性もない mysql> SELECT continent, ANY_VALUE(name), MAX(indepyear) FROM country GROUP BY continent; +---------------+-----------------+----------------+ | continent | ANY_VALUE(name) | MAX(indepyear) | +---------------+-----------------+----------------+ | North America | Aruba | 1983 | <-- Arubaのindepyearは1983ではない | Asia | Afghanistan | 1991 | | Africa | Angola | 1993 | | Europe | Albania | 1993 | | South America | Argentina | 1975 | | Oceania | American Samoa | 1994 | | Antarctica | Antarctica | NULL | +---------------+-----------------+----------------+ 7 rows in set (0.00 sec) mysql> SELECT continent, name, indepyear FROM country WHERE name = 'Aruba'; +---------------+-------+-----------+ | continent | name | indepyear | +---------------+-------+-----------+ | North America | Aruba | NULL | +---------------+-------+-----------+ 1 row in set (0.00 sec) 16/63
18. ONLY_FULL_GROUP_BY たぶんこんなSQLを許す(許していた)のはMySQLくらいだと思うので、Oracle to MySQLの文脈では問題にならないかも知れないけれども MySQLでこのフラグが下りているからといってそういうクエリーを書くと思わぬと ころで足元をすくわれるので注意を… sql_mode, セッション単位で変更できるので押し込んでくるフレームワークもある けれど個人的にはそういうことをして欲しくない… 17/63
19. NO_AUTO_VALUE_ON_ZERO デフォルトでは AUTO_INCREMENT なカラムに0を入れようとすると連番が払い出さ れる mysql> CREATE TABLE t3 (num INT AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t3 VALUES (NULL), (0); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t3; +-----+ | num | +-----+ | 1 | | 2 | +-----+ 2 rows in set (0.00 sec) 18/63
20. NO_AUTO_VALUE_ON_ZERO NO_AUTO_VALUE_ON_ZERO をセットすると0が入れられるようになる というよりは、セットしないと 0 が使えないので使う時は注意… ‐ mysql> SET SESSION sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t3 VALUES (NULL), (0); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t3; +-----+ | num | +-----+ | 0 | | 1 | | 2 | | 3 | +-----+ 4 rows in set (0.00 sec) 19/63
21. MySQLにご用心 sql_mode 暗黙の型変換 ネクストキーロック ORDER BY .. LIMIT .. OFFSET AUTO_INCREMENT vs. シーケンス 20/63
22. 暗黙の型変換 数値の 1 と文字列の '1' はキャスト可能、これは良いとして 真偽値を返す演算をした時に返ってくる 1 は真、 0 は偽 ‐ mysql> SELECT CAST(1 AS SIGNED) = CAST('1' AS CHAR); +---------------------------------------+ | CAST(1 AS SIGNED) = CAST('1' AS CHAR) | +---------------------------------------+ | 1 | +---------------------------------------+ 1 row in set (0.00 sec) 21/63
23. 暗黙の型変換 文字列と数値を比較した時は、文字列に寄せるのではなく 数値に寄せる 不可逆な文字列から数値へのキャストは「数値として読めるところまでを数値として扱う」の で悲惨 ‐ STRICT_TRANS_TABLES がないところだとつらい STRICT_TRANS_TABLES でもSELECTはエラーにならないのでつらい ‐ mysql> SELECT CAST(1 AS SIGNED) = CAST('1Q84' AS CHAR); +------------------------------------------+ | CAST(1 AS SIGNED) = CAST('1Q84' AS CHAR) | +------------------------------------------+ | 1 | +------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '1Q84' | +---------+------+------------------------------------------+ 1 row in set (0.00 sec) 22/63
24. 暗黙の型変換 DATE/DATETIME型と数値を演算すると、数値側に寄せてから演算する mysql> SELECT CURDATE(), CURDATE() + 1, CURDATE() + INTERVAL 1 DAY, CAST(CURDATE() + 1 AS DATE); +------------+---------------+----------------------------+-----------------------------+ | CURDATE() | CURDATE() + 1 | CURDATE() + INTERVAL 1 DAY | CAST(CURDATE() + 1 AS DATE) | +------------+---------------+----------------------------+-----------------------------+ | 2021-12-10 | 20211211 | 2021-12-11 | 2021-12-11 | +------------+---------------+----------------------------+-----------------------------+ 1 row in set (0.00 sec) mysql> SELECT CURDATE(), CURDATE() + 30, CURDATE() + INTERVAL 30 DAY, CAST(CURDATE() + 30 AS DATE); +------------+----------------+-----------------------------+------------------------------+ | CURDATE() | CURDATE() + 30 | CURDATE() + INTERVAL 30 DAY | CAST(CURDATE() + 30 AS DATE) | +------------+----------------+-----------------------------+------------------------------+ | 2021-12-10 | 20211240 | 2022-01-09 | NULL | +------------+----------------+-----------------------------+------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+--------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------+ | Warning | 1292 | Incorrect datetime value: '20211240' | +---------+------+--------------------------------------+ 1 row in set (0.00 sec) 23/63
25. 暗黙の型変換 型変換と言っていいのかどうかはわからないけれど、MySQLの文字列型は「CHARSET とCOLLATIONまで含めて型」みたいな感じ utf8mb4_general_ci の 'あ' と utf8_general_ci の 'あ' はキャストが可能だけれど4バイト 範囲から3バイト範囲への単射なので不可逆 ‐ キャストが発生するとインデックスが使えない ‐ mysql> CREATE TABLE t1 (num INT PRIMARY KEY, val VARCHAR(32) COLLATE utf8mb4_general_ci, KEY(val)); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t2 (num INT PRIMARY KEY, val VARCHAR(32) COLLATE utf8_general_ci, KEY (val)); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE t3 (num INT PRIMARY KEY, val VARCHAR(32) COLLATE utf8mb4_ja_0900_as_c s, KEY(val)); Query OK, 0 rows affected (0.02 sec) 24/63
26. 暗黙の型変換 3バイト範囲(t2)から4バイト範囲(t1)へのキャストは無損失だけど逆がそうではな いので、 WHERE にt1を絞り込める条件があるけれどJOINに使えなくなるので使わ ない mysql> EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val WHERE t1.val LIKE 'a%'; +----+-------------+-------+------------+-------+---------------+------+---------+-----------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+-----------+--------+----------+--------------------------+ | 1 | SIMPLE | t2 | NULL | index | val | val | 99 | NULL | 996424 | 100.00 | Using where; Using index | | 1 | SIMPLE | t1 | NULL | ref | val | val | 131 | d1.t2.val | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+-----------+--------+----------+--------------------------+ 2 rows in set, 2 warnings (0.02 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------+ | Warning | 1739 | Cannot use ref access on index 'val' due to type or collation conversion on field 'val' | | Note | 1003 | /* select#1 */ select `d1`.`t1`.`num` AS `num`,`d1`.`t1`.`val` AS `val`,`d1`.`t2`.`num` AS `num`,`d1`.`t2`.`val` AS `val` from `d1`.`t1` join `d1`.`t2` where ((`d1`.`t1`.`val` li ke 'a%') and (`d1`.`t1`.`val` = `d1`.`t2`.`val`)) | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------+ 2 rows in set (0.00 sec) 25/63
27. 暗黙の型変換 utf8mb4同士の別COLLATIONは「どっちのCOLLATIONを優先すべきか」が判定できず にそのままでは転ける COLLATE句で固定することはできるけど酷いことになったりもする ‐ mysql> EXPLAIN SELECT * FROM t1 JOIN t3 ON t1.val = t3.val WHERE t1.val LIKE 'a%'; ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_ja_0900_as _cs,IMPLICIT) for operation '=' mysql> EXPLAIN SELECT * FROM t1 JOIN t3 ON t1.val = t3.val COLLATE utf8mb4_unicode_ci WHERE t1.val LIKE 'a%'; +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+- ---------+---------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+- ---------+---------------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | range | val | val | 131 | NULL | 125750 | 100.00 | Using where; Using index | | 1 | SIMPLE | t3 | NULL | index | val | val | 131 | NULL | 996424 | 100.00 | Using where; Using index; Using join buffer (hash join) | +----+-------------+-------+------------+-------+---------------+------+---------+------+--------+- ---------+---------------------------------------------------------+ 2 rows in set, 3 warnings (0.02 sec) 26/63
28. MySQLにご用心 sql_mode 暗黙の型変換 ネクストキーロック ORDER BY .. LIMIT .. OFFSET AUTO_INCREMENT vs. シーケンス 27/63
29. ネクストキーロック トランザクション分離レベルが(デフォルトの)REPEATABLE-READ以上の時に使う ロック READ-COMMITTEDに設定している場合は使わない ‐ どうやらMySQLは「ネクストキーロックこそ lock REC 、レコードのみのロックは lock REC not GAP 」と思っているフシがあるので、ロック解析をする時の用語の 違いに気を付けて 28/63
30. ネクストキーロック たとえばこんなテーブル PRIMARY KEY(name) ‐ KEY(age) ‐ KEY(gender) ‐ mysql> SELECT * FROM user; +----------------------+------+--------+ | name | age | gender | +----------------------+------+--------+ | yoku0825 | 38 | M | | yoku0825のムスメ | 6 | F | | yoku0825の妻 | NULL | F | | you0825のせがれ | 11 | M | +----------------------+------+--------+ 4 rows in set (0.00 sec) 29/63
31. ネクストキーロック infimum .. 無限小を表すレコード supremum .. 無限大を表すレコード [gap] .. レコードとレコードの隙間 30/63
32. ネクストキーロック REC .. そのレコードと手前のレコードの間のギャップ REC not GAP .. そのレコードそのもの GAP .. そのレコードの手前のギャップ 31/63
33. ネクストキーロック UPDATE user SET age = 39 WHERE name = 'yoku0825' トランザクション分離レベルにかかわらずPK引き ‐ 32/63
34. ネクストキーロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = 12 WHERE age = 11 REPEATABLE-READで非ユニークなインデックスを引いたUPDATE ‐ 33/63
35. ネクストキーロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = 12 WHERE age = 11 REPEATABLE-READで非ユニークなインデックスを引いたUPDATE ‐ 34/63
36. ネクストキーロック (6, yoku0825のムスメ) の直後から (38, yoku0825) の直前までがロックされている mysql> INSERT INTO user VALUES ('けんつ', 24, 'M'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO user VALUES ('けんつ', 10, 'M'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO user VALUES ('けんつ', 5, 'M'); Query OK, 1 row affected (0.00 sec) 35/63
37. ネクストキーロック (6, yoku0825のムスメ) の直後から (38, yoku0825) の直前までがロックされている mysql80 14> INSERT INTO user VALUES ('yoku0824のムスメ', 6, 'F'); Query OK, 1 row affected (0.01 sec) mysql80 14> INSERT INTO user VALUES ('yoku0826のムスメ', 6, 'F'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql80 14> INSERT INTO user VALUES ('yoku0824', 38, 'M'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql80 14> INSERT INTO user VALUES ('yoku0826', 38, 'M'); Query OK, 1 row affected (0.00 sec) 36/63
38. ネクストキーロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = age + 1 WHERE gender = 'F' REPEATABLE-READで非ユニークなインデックスを引いたUPDATEその2 ‐ 37/63
39. ネクストキーロック transaction_isolation= REPEATABLE-READ && UPDATE user SET age = age + 1 WHERE gender = 'F' REPEATABLE-READで非ユニークなインデックスを引いたUPDATEその2 ‐ 38/63
40. ネクストキーロック infimumから gender = M && name < yoku0825 がロックされている mysql> INSERT INTO user VALUES ('yoku0824のムスメ?', NULL, 'F'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO user VALUES ('yoku0824', NULL, 'M'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> INSERT INTO user VALUES ('yoku0826', NULL, 'M'); Query OK, 1 row affected (0.00 sec) 39/63
41. ネクストキーロック InnoDBにおいてはインデックスはルックアップの高速化のみならず「ロックの粒度 そのもの」でもある READ-COMMITTEDだとこれは成り立たない ‐ インデックスは丁寧に貼りましょう… 40/63
42. MySQLにご用心 sql_mode 暗黙の型変換 ネクストキーロック ORDER BY .. LIMIT .. OFFSET AUTO_INCREMENT vs. シーケンス 41/63
43. ORDER BY .. LIMIT .. OFFSET Oracleだと OFFSET m ROWS FETCH FIRST n ROWS ONLY ? MySQLの場合は LIMIT n OFFSET m とか LIMIT m, n Oracleは知らないけどMySQLのOFFSETは0オリジン ‐ ユニークキーで ORDER BY していない限り、 LIMIT .. OFFSET .. で返ってくる行 は「不定」 WHEREとかとの兼ね合いで選ばれたインデックスで「見つかった順」にしか返さないので、使う インデックスが変わると戻ってくる行が変わる ‐ ユニークでない限り、 ORDER BY c1 ASC, c2 ASC でc1もc2も同じレコード同士がどういう順番 で返ってくるかはわからない ‐ 42/63
44. WHEREとORDER BY LIMITの連携 // WHERE continent = 'Asia' ORDER BY gnp ASC LIMIT 5 $asia= $index_continent_population["Asia"]; foreach ($asia as $row) { array_push($sort_buffer, $row); } uasort($sort_buffer, ..); // ソートバッファのソート // ソートが終わってからでないと LIMIT 5 が確定しない var_dump(array($sort_buffer[0], $sort_buffer[1], $sort_buffer[2], $sort_buffer[3], $sort_ buffer[4])); 43/63
45. WHEREとORDER BY LIMITの連携 // WHERE continent = 'Asia' ORDER BY population ASC LIMIT 5 $n= 0; $result_buffer= []; foreach ($index_continent_population["Asia"] as $population => $code) { // populationの順番に並んでいることが保証されている array_push($result_buffer, $code); // 事前にトップ5が確定しているので、LIMIT 5を満たしたらループを抜けられる if (++$n == 5) break; } var_dump($result_buffer); 44/63
46. ORDER BY .. LIMIT .. OFFSET ORDER BY狙いのキーを使わない場合の処理 WHERE , HAVING でフィルタ後の件数が多ければ多いほどレスポンスが悪化していく 逆を言うとソートは常に最後までやってしまうので、OFFSETがいくら深くても性能にはそれほど寄与しないことが多 い（ sort_buffer_size をあふれるくらいまでいくと強く影響する） ‐ LIMIT .. OFFSET .. は「結果セットの転送に使うネットワーク使用量」くらいにしか寄与しな い ‐ 45/63
47. ORDER BY .. LIMIT .. OFFSET ORDER BY狙いのキーを使った場合の処理の順番 LIMIT n OFFSET m の場合、カーソルを合わせたインデックスレコードの先頭から n+(m+1)行 ぶん読み出して、先頭 m+1 行を捨てる ‐ OFFSET が深くなれば深くなるほど線形に近くレスポンスタイムが劣化していく 特にどうもこれselect_listを一緒に処理してから捨てているらしく、カバリングインデックスを使ったサブクエリ と組み合わせると速くなることが多い ‐ 46/63
48. ORDER BY .. LIMIT .. OFFSET SELECT * FROM t1 ORDER BY num LIMIT 10 OFFSET $n; OFFSET 0 : 0.016209 OFFSET 1000000 : 0.444942 OFFSET 2000000 : 0.755690 OFFSET 3000000 : 0.811017 OFFSET 4000000 : 1.192051 OFFSET 5000000 : 1.358769 OFFSET 6000000 : 1.617762 OFFSET 7000000 : 1.806320 OFFSET 8000000 : 2.227395 OFFSET 9000000 : 3.315964 OFFSET 10000000 : 3.497472 47/63
49. ORDER BY .. LIMIT .. OFFSET 典型的な解決策は、 ORDER BY のカラムの最後の値を(App側で)おぼえておいて、 それをWHERE句に追加してOFFSETを使わないこと SELECT * FROM t1 WHERE num > ? /* 前回の最後の値 */ ORDER BY num LIMIT 10; OFFSET 0 : 0.000568 OFFSET 1000000 : 0.000338 OFFSET 2000000 : 0.000278 OFFSET 3000000 : 0.000301 OFFSET 4000000 : 0.000282 OFFSET 5000000 : 0.000263 OFFSET 6000000 : 0.000273 OFFSET 7000000 : 0.000304 OFFSET 8000000 : 0.000261 OFFSET 9000000 : 0.000259 OFFSET 10000000 : 0.000288 ただし ORDER BY のカラムがユニークでない場合、ページネーションで参照されな いレコードが出てきたりするので採用できないことも 48/63
50. ORDER BY .. LIMIT .. OFFSET 特にselect_listが長い場合、サブクエリで多少高速化できる SELECT t1.* FROM t1 JOIN (SELECT num FROM t1 ORDER BY num LIMIT 10 OFFSET $n) AS picker O N t1.num = picker.num ORDER BY num; OFFSET 0 : 0.020770 OFFSET 1000000 : 0.515550 OFFSET 2000000 : 0.674445 OFFSET 3000000 : 0.641850 OFFSET 4000000 : 0.698979 OFFSET 5000000 : 0.864459 OFFSET 6000000 : 1.039336 OFFSET 7000000 : 1.223000 OFFSET 8000000 : 1.605361 OFFSET 9000000 : 2.517832 OFFSET 10000000 : 2.647445 49/63
51. ORDER BY .. LIMIT .. OFFSET 焼け石に水に見えなくもないけれど効果はある SELECT * FROM t1 ORDER BY num LIMIT 10 OFFSET $n; OFFSET 0 : 0.016209 OFFSET 1000000 : 0.444942 OFFSET 2000000 : 0.755690 OFFSET 3000000 : 0.811017 OFFSET 4000000 : 1.192051 OFFSET 5000000 : 1.358769 OFFSET 6000000 : 1.617762 OFFSET 7000000 : 1.806320 OFFSET 8000000 : 2.227395 OFFSET 9000000 : 3.315964 OFFSET 10000000 : 3.497472 50/63
52. MySQLにご用心 sql_mode 暗黙の型変換 ネクストキーロック ORDER BY .. LIMIT .. OFFSET AUTO_INCREMENT vs. シーケンス 51/63
53. AUTO_INCREMENT vs. シーケンス DELETE / ROLLBACKで欠番するのはOracleも一緒 MySQLは INSERT .. SELECT .. や INSERT .. ON DUPLICATE KEY UPDATE .. や REPLACE INTO であっさり起こる 8.0のデフォルトは、INSERT .. SELECT .. を多重で走らせると、AUTOINCが互い違いに払い出 されることがある設定 日々の覚書: innodb_autoinc_lock_mode = 1 vs 2 でバルクインサートが競合した時のAUTO_INCREMENTの挙動が違う はなし ‐ 52/63
54. AUTO_INCREMENT vs. シーケンス MySQL 5.7とそれ以前では、mysqldの再起動でAUTOINCが巻き戻ったように見えるこ とがある mysqldの起動時に、各テーブルの MAX(auto_increment_column) + 1 で次に払い出される AUTOINCが再計算される 何なら実はALTER TABLEでもオペレーションによっては再計算される Bug#94797 ‐ INSERT .. + DELETE .. で消えたはずの行の番号が再利用されることがある ‐ 8.0でこれは起こらなくなったけれども挙動としてはインコンパチなのでそれはそれで注意 ‐ 53/63
55. AUTO_INCREMENT vs. シーケンス InnoDBの各セカンダリーインデックスはPRIMARY KEYの値をリーフに格納するの で、InnoDBでPRIMARY KEYをAUTO_INCREMENT にすると容量効率が良い 逆にたとえばURLのQueryStringみたいなのが本物の一意識別子だったりするとしても、それを PRIMARY KEYにするとものすごく容量を持っていかれる ‐ PRIMARY KEYは AUTO_INCREMENT にしておいて、本来の一意識別子を NOT NULL UNIQUE に落と すと両立できる ‐ PRIMARY KEY以外, PRIMARY KEYでも先頭以外のカラムで AUTO_INCREMENT を使った り、BIGINT UNSIGNEDの限界近くまで使ったりはあんまり想定されていなさそう MySQL Bugs: #84186: mysqlpump couldn’t restore auto_increment column with secondary key ‐ MySQL Bugs: #87926: Incorrect auto_increment ids issued when close to ULLONG_MAX ‐ MySQL Bugs: #105310: slave stops with HA_ERR_KEY_NOT_FOUND when update the auto_increment column ‐ 54/63
56. MySQLにご用心 sql_mode 暗黙の型変換 ネクストキーロック ORDER BY .. LIMIT .. OFFSET AUTO_INCREMENT vs. シーケンス 55/63
57. MySQLにご用心 sql_mode エラーが出たからといって安易にAppから変更してはいけない ‐ 5.7とそれ以降のデフォルトにしてようやく「フツー」 ‐ 暗黙の型変換 ネクストキーロック ORDER BY .. LIMIT .. OFFSET AUTO_INCREMENT vs. シーケンス 56/63
58. MySQLにご用心 sql_mode 暗黙の型変換 残念ながらどうやっても無効化できない ‐ 静的型がある言語ならPrepared Statementにbindする時点でバリデーションできる…？ ‐ 取り敢えず「日付型に +- 」はOracle使いには稀にだがよくあるらしい ‐ ネクストキーロック ORDER BY .. LIMIT .. OFFSET AUTO_INCREMENT vs. シーケンス 57/63
59. MySQLにご用心 sql_mode 暗黙の型変換 ネクストキーロック ルックアップだけじゃなくロックの粒度だったりする。インデックスは丁寧に ‐ ORDER BY .. LIMIT .. OFFSET AUTO_INCREMENT vs. シーケンス 58/63
60. MySQLにご用心 sql_mode 暗黙の型変換 ネクストキーロック ORDER BY .. LIMIT .. OFFSET 基本的には OFFSET を使わずに、欲しい行を引っ張ってこられるような仕組みを考える ‐ AUTO_INCREMENT vs. シーケンス 59/63
61. MySQLにご用心 sql_mode 暗黙の型変換 ネクストキーロック ORDER BY .. LIMIT .. OFFSET AUTO_INCREMENT vs. シーケンス 使い心地はまあまあ違うけれど、これはアプリからは比較的透過的に見えるかしら… ‐ 巻き戻りには注意！！ ‐ 60/63
62. MySQLが得意なこと/Oracleの方が得意そうなこと 小さな結果セットを、インデックスを使い切って絞りだすのは得意 インデックスを使い切らずに取り出したり、結果セットがそもそも大きいものはOracleの方が 得意なはず ‐ それはそういうものだと割り切って、「じゃあ如何にMySQLが苦手な処理を得意な 処理の側に手繰り寄せていくか」がMySQLと上手く付き合うのに必要な考え方だと 思う 明示的なテンポラリーテーブルを作るのとかもそう ‐ CREATE TEMPORARY TABLEは（吊るしのMySQLなら） read_only = ON なレプリカでも作成できる ‐ 本気でMySQLを楽しむなら、MySQLは良くも悪くも柔軟すぎるので それを逆手に取ってレプリケーションで上手くゴニョるのが醍醐味 レプリカでインデックス作って/データのサクッとスイッチオーバーするとか、そういうやつ ‐ 61/63
63. 参考 MySQLとインデックスとPHPer MySQLとインデックスと私 MySQLのALTER TABLEについて少々 磯野ー、MySQLのロック競合を表示しようぜー Shooting a trouble of MySQL Dive into MySQL Error 62/63
64. Any Questions and/or Suggestions? 63/63