Slide 1
Slide 1 text
MySQLが得意なこと、苦手なこと(仮)
2021/12/17
yoku0825
Engineers in CARTA vol.2 #MySQL
Slide 2
Slide 2 text
TL;DR
1/63
Slide 3
Slide 3 text
MySQLが得意なこと/Oracleの方が得意そうなこと
小さな結果セットを、インデックスを使い切って絞りだすのは得意
インデックスを使い切らずに取り出したり、結果セットがそもそも大きいものはOracleの方が
得意なはず
‐
それはそういうものだと割り切って、「じゃあ如何にMySQLが苦手な処理を得意な
処理の側に手繰り寄せていくか」がMySQLと上手く付き合うのに必要な考え方だと
思う
明示的なテンポラリーテーブルを作るのとかもそう
‐
CREATE TEMPORARY TABLEは（吊るしのMySQLなら） read_only = ON なレプリカでも作成できる
‐
本気でMySQLを楽しむなら、MySQLは良くも悪くも柔軟すぎるので
それを逆手に取ってレプリケーションで上手くゴニョるのが醍醐味
レプリカでインデックス作って/データのサクッとスイッチオーバーするとか、そういうやつ
‐
2/63
Slide 4
Slide 4 text
＼こんばんわ／
yoku0825＠とある企業のDBA
オラクらない
‐
ポスグらない
‐
マイエスキューエる
‐
生息域
Twitter: @yoku0825
‐
Blog: 日々の覚書
‐
日本MySQLユーザ会
‐
MySQL Casual
‐
3/63
Slide 5
Slide 5 text
MySQL < Oracle ?
Oracleがわからないので真偽はわからない
MySQL < NULL は NULL
‐
「MySQLではこういうのは危ない」は言えるけど、それがOracleでできたかどうか
は私はわかりません。。
4/63
Slide 6
Slide 6 text
MySQLにご用心
sql_mode
暗黙の型変換
ネクストキーロック
ORDER BY .. LIMIT .. OFFSET
AUTO_INCREMENT vs. シーケンス
5/63
Slide 7
Slide 7 text
sql_mode
「ちょっとそれってどうなの？」な動作を許せるようにするフラグ
かつては「ちゃんと制限を効かせるようにするフラグ」だけど、今日となっては制限が強い方
がデフォルトなので「敢えて弱くしたい時に」フラグを下ろすのが一般的ではないか
‐
特にヤバいのは STRICT_TRANS_TABLES と ONLY_FULL_GROUP_BY
6/63
Slide 8
Slide 8 text
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
Slide 9
Slide 9 text
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
Slide 10
Slide 10 text
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
Slide 11
Slide 11 text
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
Slide 12
Slide 12 text
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
Slide 13
Slide 13 text
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
Slide 14
Slide 14 text
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
Slide 15
Slide 15 text
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
Slide 16
Slide 16 text
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
Slide 17
Slide 17 text
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
Slide 18
Slide 18 text
ONLY_FULL_GROUP_BY
たぶんこんなSQLを許す(許していた)のはMySQLくらいだと思うので、Oracle to
MySQLの文脈では問題にならないかも知れないけれども
MySQLでこのフラグが下りているからといってそういうクエリーを書くと思わぬと
ころで足元をすくわれるので注意を…
sql_mode, セッション単位で変更できるので押し込んでくるフレームワークもある
けれど個人的にはそういうことをして欲しくない…
17/63
Slide 19
Slide 19 text
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
Slide 20
Slide 20 text
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
Slide 21
Slide 21 text
MySQLにご用心
sql_mode
暗黙の型変換
ネクストキーロック
ORDER BY .. LIMIT .. OFFSET
AUTO_INCREMENT vs. シーケンス
20/63
Slide 22
Slide 22 text
暗黙の型変換
数値の 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
Slide 23
Slide 23 text
暗黙の型変換
文字列と数値を比較した時は、文字列に寄せるのではなく 数値に寄せる
不可逆な文字列から数値へのキャストは「数値として読めるところまでを数値として扱う」の
で悲惨
‐
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
Slide 24
Slide 24 text
暗黙の型変換
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
Slide 25
Slide 25 text
暗黙の型変換
型変換と言っていいのかどうかはわからないけれど、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
Slide 26
Slide 26 text
暗黙の型変換
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
Slide 27
Slide 27 text
暗黙の型変換
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
Slide 28
Slide 28 text
MySQLにご用心
sql_mode
暗黙の型変換
ネクストキーロック
ORDER BY .. LIMIT .. OFFSET
AUTO_INCREMENT vs. シーケンス
27/63
Slide 29
Slide 29 text
ネクストキーロック
トランザクション分離レベルが(デフォルトの)REPEATABLE-READ以上の時に使う
ロック
READ-COMMITTEDに設定している場合は使わない
‐
どうやらMySQLは「ネクストキーロックこそ lock REC 、レコードのみのロックは
lock REC not GAP 」と思っているフシがあるので、ロック解析をする時の用語の
違いに気を付けて
28/63
Slide 30
Slide 30 text
ネクストキーロック
たとえばこんなテーブル
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
Slide 31
Slide 31 text
ネクストキーロック
infimum .. 無限小を表すレコード
supremum .. 無限大を表すレコード
[gap] .. レコードとレコードの隙間
30/63
Slide 32
Slide 32 text
ネクストキーロック
REC .. そのレコードと手前のレコードの間のギャップ
REC not GAP .. そのレコードそのもの
GAP .. そのレコードの手前のギャップ
31/63
Slide 33
Slide 33 text
ネクストキーロック
UPDATE user SET age = 39 WHERE name = 'yoku0825'
トランザクション分離レベルにかかわらずPK引き
‐
32/63
Slide 34
Slide 34 text
ネクストキーロック
transaction_isolation= REPEATABLE-READ && UPDATE user SET age = 12 WHERE age
= 11
REPEATABLE-READで非ユニークなインデックスを引いたUPDATE
‐
33/63
Slide 35
Slide 35 text
ネクストキーロック
transaction_isolation= REPEATABLE-READ && UPDATE user SET age = 12 WHERE age
= 11
REPEATABLE-READで非ユニークなインデックスを引いたUPDATE
‐
34/63
Slide 36
Slide 36 text
ネクストキーロック
(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
Slide 37
Slide 37 text
ネクストキーロック
(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
Slide 38
Slide 38 text
ネクストキーロック
transaction_isolation= REPEATABLE-READ && UPDATE user SET age = age + 1 WHERE
gender = 'F'
REPEATABLE-READで非ユニークなインデックスを引いたUPDATEその2
‐
37/63
Slide 39
Slide 39 text
ネクストキーロック
transaction_isolation= REPEATABLE-READ && UPDATE user SET age = age + 1 WHERE
gender = 'F'
REPEATABLE-READで非ユニークなインデックスを引いたUPDATEその2
‐
38/63
Slide 40
Slide 40 text
ネクストキーロック
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
Slide 41
Slide 41 text
ネクストキーロック
InnoDBにおいてはインデックスはルックアップの高速化のみならず「ロックの粒度
そのもの」でもある
READ-COMMITTEDだとこれは成り立たない
‐
インデックスは丁寧に貼りましょう…
40/63
Slide 42
Slide 42 text
MySQLにご用心
sql_mode
暗黙の型変換
ネクストキーロック
ORDER BY .. LIMIT .. OFFSET
AUTO_INCREMENT vs. シーケンス
41/63
Slide 43
Slide 43 text
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
Slide 44
Slide 44 text
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
Slide 45
Slide 45 text
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
Slide 46
Slide 46 text
ORDER BY .. LIMIT .. OFFSET
ORDER BY狙いのキーを使わない場合の処理
WHERE , HAVING でフィルタ後の件数が多ければ多いほどレスポンスが悪化していく
逆を言うとソートは常に最後までやってしまうので、OFFSETがいくら深くても性能にはそれほど寄与しないことが多
い（ sort_buffer_size をあふれるくらいまでいくと強く影響する）
‐
LIMIT .. OFFSET .. は「結果セットの転送に使うネットワーク使用量」くらいにしか寄与しな
い
‐
45/63
Slide 47
Slide 47 text
ORDER BY .. LIMIT .. OFFSET
ORDER BY狙いのキーを使った場合の処理の順番
LIMIT n OFFSET m の場合、カーソルを合わせたインデックスレコードの先頭から n+(m+1)行
ぶん読み出して、先頭 m+1 行を捨てる
‐
OFFSET が深くなれば深くなるほど線形に近くレスポンスタイムが劣化していく
特にどうもこれselect_listを一緒に処理してから捨てているらしく、カバリングインデックスを使ったサブクエリ
と組み合わせると速くなることが多い
‐
46/63
Slide 48
Slide 48 text
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
Slide 49
Slide 49 text
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
Slide 50
Slide 50 text
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
Slide 51
Slide 51 text
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
Slide 52
Slide 52 text
MySQLにご用心
sql_mode
暗黙の型変換
ネクストキーロック
ORDER BY .. LIMIT .. OFFSET
AUTO_INCREMENT vs. シーケンス
51/63
Slide 53
Slide 53 text
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
Slide 54
Slide 54 text
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
Slide 55
Slide 55 text
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
Slide 56
Slide 56 text
MySQLにご用心
sql_mode
暗黙の型変換
ネクストキーロック
ORDER BY .. LIMIT .. OFFSET
AUTO_INCREMENT vs. シーケンス
55/63
Slide 57
Slide 57 text
MySQLにご用心
sql_mode
エラーが出たからといって安易にAppから変更してはいけない
‐
5.7とそれ以降のデフォルトにしてようやく「フツー」
‐
暗黙の型変換
ネクストキーロック
ORDER BY .. LIMIT .. OFFSET
AUTO_INCREMENT vs. シーケンス
56/63
Slide 58
Slide 58 text
MySQLにご用心
sql_mode
暗黙の型変換
残念ながらどうやっても無効化できない
‐
静的型がある言語ならPrepared Statementにbindする時点でバリデーションできる…？
‐
取り敢えず「日付型に +- 」はOracle使いには稀にだがよくあるらしい
‐
ネクストキーロック
ORDER BY .. LIMIT .. OFFSET
AUTO_INCREMENT vs. シーケンス
57/63
Slide 59
Slide 59 text
MySQLにご用心
sql_mode
暗黙の型変換
ネクストキーロック
ルックアップだけじゃなくロックの粒度だったりする。インデックスは丁寧に
‐
ORDER BY .. LIMIT .. OFFSET
AUTO_INCREMENT vs. シーケンス
58/63
Slide 60
Slide 60 text
MySQLにご用心
sql_mode
暗黙の型変換
ネクストキーロック
ORDER BY .. LIMIT .. OFFSET
基本的には OFFSET を使わずに、欲しい行を引っ張ってこられるような仕組みを考える
‐
AUTO_INCREMENT vs. シーケンス
59/63
Slide 61
Slide 61 text
MySQLにご用心
sql_mode
暗黙の型変換
ネクストキーロック
ORDER BY .. LIMIT .. OFFSET
AUTO_INCREMENT vs. シーケンス
使い心地はまあまあ違うけれど、これはアプリからは比較的透過的に見えるかしら…
‐
巻き戻りには注意！！
‐
60/63
Slide 62
Slide 62 text
MySQLが得意なこと/Oracleの方が得意そうなこと
小さな結果セットを、インデックスを使い切って絞りだすのは得意
インデックスを使い切らずに取り出したり、結果セットがそもそも大きいものはOracleの方が
得意なはず
‐
それはそういうものだと割り切って、「じゃあ如何にMySQLが苦手な処理を得意な
処理の側に手繰り寄せていくか」がMySQLと上手く付き合うのに必要な考え方だと
思う
明示的なテンポラリーテーブルを作るのとかもそう
‐
CREATE TEMPORARY TABLEは（吊るしのMySQLなら） read_only = ON なレプリカでも作成できる
‐
本気でMySQLを楽しむなら、MySQLは良くも悪くも柔軟すぎるので
それを逆手に取ってレプリケーションで上手くゴニョるのが醍醐味
レプリカでインデックス作って/データのサクッとスイッチオーバーするとか、そういうやつ
‐
61/63
Slide 63
Slide 63 text
参考
MySQLとインデックスとPHPer
MySQLとインデックスと私
MySQLのALTER TABLEについて少々
磯野ー、MySQLのロック競合を表示しようぜー
Shooting a trouble of MySQL
Dive into MySQL Error
62/63
Slide 64
Slide 64 text
Any Questions
and/or
Suggestions?
63/63