データベース【サイボウズ新人研修2025】

Transcript

1. 開発運用研修 データベース データベースと銘打っていますがMySQLの話です 2025/04/17 yoku0825
2. ＼こんにちは／ yoku0825＠とある企業のDBAだったもの オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitterだったもの: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会副代表 ‐ MySQL Casual ‐ bozumanではだいたいGaroonのShare Customer Support板にいます 余裕がある時は “MySQL” で全件検索して勝手にコメントをつけたり ‐ 1/77
3. おしながき 手元のDocker環境でmysqldを起動する トランザクションのSerializabilityについて軽く考える 2/77
4. MySQL #とは https://www.mysql.com/jp/ 3/77
5. MySQL #とは クライアント/サーバ型のRDBMS Garoonとかkintoneとかがクライアント側 ‐ MySQLサーバはNecoの上にいてMOCOで管理されている（はず） ‐ 得意なジャンル(?)は「高頻度な少量のフェッチ、少量の更新」 大量フェッチ、大量更新は苦手 ‐ 大袈裟に「SQL喋れるKVM」と揶揄されることも ‐ 4/77
6. はじめてのMySQL root@% がパスワードなしなので手元で試すくらいで… docker run -d --restart=on-failure \ -e MYSQL_ALLOW_EMPTY_PASSWORD=1 \ -e MYSQL_ROOT_PASSWORD="""" \ -e MYSQL_ROOT_HOST=""%"" \ container-registry.oracle.com/mysql/community-server:8.4.5 \ --gtid-mode=ON --enforce-gtid-consistency=ON 5/77
7. はじめてのMySQL 10秒くらい待つ docker logs コンテナID [Entrypoint] MySQL Docker Image 8.4.5-1.2.21-server [Entrypoint] Initializing database 2025-04-21T04:21:50.528592Z 0 [System] [MY-015017] [Server] MySQL Server I nitialization - start. 2025-04-21T04:21:50.530156Z 0 [System] [MY-013169] [Server] /usr/sbin/mys qld (mysqld 8.4.5) initializing of server in progress as process 17 .. [Entrypoint] MySQL init process done. Ready for start up. .. 2025-04-21T04:21:59.400336Z 0 [System] [MY-010931] [Server] /usr/sbin/mys qld: ready for connections. Version: '8.4.5' socket: '/var/lib/mysql/mys ql.sock' port: 3306 MySQL Community Server - GPL. 6/77
8. はじめてのMySQL docker inspect コンテナID | jq -r '.[0].NetworkSettings.Networks[].IPAddres s' 172.17.0.2 mysql -h IPアドレス -uroot SELECT @@version; +-----------+ | @@version | +-----------+ | 8.4.5 | +-----------+ 1 row in set (0.00 sec) 7/77
9. はじめてのMySQL CREATE DATABASE d1; use d1 CREATE TABLE t1 (num INT PRIMARY KEY, val VARCHAR(32) NOT NULL, UNIQUE KE Y uidx_val(val)); INSERT INTO t1 (num, val) VALUES (1, 'one'); INSERT INTO t1 (num, val) VALUES (2, 'two'); INSERT INTO t1 (num, val) VALUES (3, 'three'); 8/77
10. はじめてのMySQL SELECT * FROM t1; +-----+-------+ | num | val | +-----+-------+ | 1 | one | | 3 | three | <--- threeが先? | 2 | two | +-----+-------+ 9/77
11. はじめてのMySQL SELECT * FROM t1 ORDER BY num; +-----+-------+ | num | val | +-----+-------+ | 1 | one | | 2 | two | | 3 | three | +-----+-------+ 10/77
12. はじめてのMySQL CREATE DATABASE CREATE TABLE INSERT SELECT 11/77
13. はじめてのMySQL mysql> help CREATE DATABASE Name: 'CREATE DATABASE' Description: Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ... create_option: [DEFAULT] { CHARACTER SET [=] charset_name | COLLATE [=] collation_name | ENCRYPTION [=] {'Y' | 'N'} } CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE. 12/77
14. はじめてのMySQL MySQLでだけできる / MySQLではできない方言もあるのでドキュメントに当たる（か、今日日 なら生成AI的な何かに聞く）のがいいと思います MySQL :: MySQL 8.4 Reference Manual :: 15 SQL Statements 13/77
15. いわゆるCRUD Create: INSERT INTO .. Read: SELECT .. FROM .. Update: UPDATE .. SET .. Delete: DELETE FROM .. 14/77
16. いわゆるCRUD よくドキュメントを見てみると <snip> Single-table syntax: UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET assignment_list [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] <snip> Multiple-table syntax: 15/77
17. いわゆるCRUD たとえば UPDATE の直後にくるキーワードは table_reference 単一のテーブルである必要はない ‐ UPDATE t1 AS normal_t1 JOIN t1 AS another_t1 ON normal_t1.num = another_t 1.num SET normal_t1.num = normal_t1.num + another_t1.num WHERE another_t1. num = 3; SELECT * FROM t1 ORDER BY num; +-----+-------+ | num | val | +-----+-------+ | 1 | one | | 2 | two | | 6 | three | +-----+-------+ 16/77
18. いわゆるCRUD DELETE はもっとクセがある <snip> Single-Table Syntax DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias] [PARTITION (partition_name [, partition_name] ...)] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] <snip> Multiple-Table Syntax DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... 17/77
19. いわゆるCRUD SELECT .. FROM テーブル名 とかよく言われるやつの「テーブル名」は「テーブル名」じゃない ことがある MySQL用語的には「テーブルリファレンス」 ‐ INSERT INTO .. の後ろ側も「テーブルリファレンス」であって VALUES (..) が「テーブルリ ファレンスのコンストラクタ」 なので INSERT INTO .. から VALUES の「代わりに」 SELECT が来ても成立する ‐ なので INSERT INTO .. SELECT .. の時は VALUES が「ない」 SELECT 自体がテーブルリファレンスを返すから ‐ 18/77
20. テーブルとテーブルリファレンス このあたりの考え方を拡張していくと インデックスを強制するDELETE文 とかちょっとした小ネ タが広がる EXPLAIN DELETE t1 FROM t1 WHERE num = 6; +----+-------------+-------+------------+-------+---------------+--------- +---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------- +---------+-------+------+----------+-------+ | 1 | DELETE | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+--------- +---------+-------+------+----------+-------+ 19/77
21. テーブルとテーブルリファレンス EXPLAIN DELETE FROM t1 IGNORE INDEX(PRIMARY) WHERE num = 6; ### DELETE FROMの あとはテーブル名が来ないといけないのでシンタックスエラー ERROR 1064 (42000): You have an error in your SQL syntax; check the manual th at corresponds to your MySQL server version for the right syntax to use near ' IGNORE INDEX(PRIMARY) WHERE num = 6' at line 1 EXPLAIN DELETE t1 FROM t1 IGNORE INDEX(PRIMARY) WHERE num = 6; ### インデック スヒントを含んだテーブル指定は「テーブルリファレンス」 +----+-------------+-------+------------+------+---------------+------+------- --+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_l en | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+------- --+------+------+----------+-------------+ | 1 | DELETE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+------- --+------+------+----------+-------------+ 20/77
22. 余談 なお、今日日 インデックスを強制するDELETE文 を書くならオプティマイザヒントの方が楽 {USE|FORCE|IGNORE} INDEX はMySQL方言で非推奨の流れだし ‐ 今はできるんですよ、って昔から使ってる人に言いたいだけ ‐ EXPLAIN DELETE /*+ NO_INDEX(t1 PRIMARY) */ t1 FROM t1 WHERE num = 6; +----+-------------+-------+------------+------+---------------+------+--- ------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | ke y_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+--- ------+------+------+----------+-------------+ | 1 | DELETE | t1 | NULL | ALL | NULL | NULL | NU LL | NULL | 3 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+--- ------+------+------+----------+-------------+ 21/77
23. いわゆるCRUD リテラルを与えそうなところも実は「1行1列のテーブルリファレンス」で良かったりするのでサブクエリの 可能性は無限大 サブクエリ部分で無駄に1回FROM句サブクエリを挟んでいるのはMySQLの制約 ‐ 楽しいけど必ずしも上手く動くとは言えない。主にパフォーマンス方面。 ‐ DELETE FROM t1 WHERE num = (SELECT * FROM (SELECT MAX(num) FROM t1) AS du mmy); SELECT * FROM t1 ORDER BY num; +-----+-----+ | num | val | +-----+-----+ | 1 | one | | 2 | two | +-----+-----+ 22/77
24. いわゆるCRUD 特定のケースを除いて SELECT はロックフリー SELECT .. FOR SHARE, SELECT .. FOR UPDATE a. transaction_isolation = SERIALIZABLE b. INSERT は対応するユニークキーとPrimary Keyの値をロック UPDATE / DELETE は「WHERE句の解決に使ったインデックス」と対応するPrimary Keyの 値をロック ### ターミナル1 BEGIN; INSERT INTO t1 VALUES (3, 'three'), (4, 'four'), (5, 'five'); ### COMMITせずに次へ 23/77
25. いわゆるCRUD 特定のケースを除いて SELECT はロックフリー SELECT .. FOR SHARE, SELECT .. FOR UPDATE a. transaction_isolation = SERIALIZABLE b. INSERT は対応するユニークキーとPrimary Keyの値をロック UPDATE / DELETE は「WHERE句の解決に使ったインデックス」と対応するPrimary Keyの 値をロック ### ターミナル2 BEGIN; SELECT * FROM t1 ORDER BY num; ### 何もつけないSELECTはロックフリー +-----+-----+ | num | val | +-----+-----+ 24/77
26. いわゆるCRUD 特定のケースを除いて SELECT はロックフリー SELECT .. FOR SHARE, SELECT .. FOR UPDATE a. transaction_isolation = SERIALIZABLE b. INSERT は対応するユニークキーとPrimary Keyの値をロック UPDATE / DELETE は「WHERE句の解決に使ったインデックス」と対応するPrimary Keyの 値をロック ### ターミナル2 SET SESSION innodb_lock_wait_timeout = 1; SELECT * FROM t1 WHERE num = 3 FOR UPDATE; ### num = 3はINSERTにロックされて いる INSERT INTO t1 VALUES (3, 'drei'); ### ↑同様 INSERT INTO t1 VALUES (6, 'six'); ### num = 6はロックされてないので 25/77
27. いわゆるCRUD 特定のケースを除いて SELECT はロックフリー SELECT .. FOR SHARE, SELECT .. FOR UPDATE a. transaction_isolation = SERIALIZABLE b. INSERT は対応するユニークキーとPrimary Keyの値をロック UPDATE / DELETE は「WHERE句の解決に使ったインデックス」と対応するPrimary Keyの 値をロック ### ターミナル2 DELETE FROM t1 WHERE val = 'five'; ### val = 'five' に紐づく num = 5 がINSERTにロックされている UPDATE t1 SET val = 'five' WHERE num = 1; ### num = 1はロックされていないが v al = 'five' がロックされている UPDATE t1 SET val = 'eins' WHERE num = 1; ### num = 1もval = 'eins' もロッ クされていないので成功 ### ちなみにvalがユニークキーでない場合は2つ目のUPDATEが成功する 26/77
28. 行(?) ロック 2021年(4年前!)の開運研修に置いてきました MySQLとインデックスと私 ‐ 途中で例に現れる「けんつ」さんはこちらです https://bozuman.cybozu.com/k/#/space/3518/thread/83288 ‐ 27/77
29. transaction_isolation = SERIALIZABLE トランザクション分離レベルというやつ MySQL :: MySQL 8.4 Reference Manual :: 15.3.7 SET TRANSACTION Statement ‐ 同時実行されるトランザクション同士が「単一スレッドで逐次実行された時と同じ結果になる (Serializabilityを持つ)」ことを保証したいのが SERIALIZABLE なお transaction_isolation = SERIALIZABLE は(規格上)必ずしも常に「単一スレッドで逐次 実行された時と同じ結果になる」わけではないらしい Isolation Levelの階層 #ポエム \- Qiita 一人トランザクション技術 \- Qiita Advent Calendar 2016 \- Qiita ‐ 28/77
30. トランザクション分離レベル 29/77
31. トランザクション分離レベル 30/77
32. Serializabilityを保つなら(1) 31/77
33. Serializabilityを保つなら(1) 32/77
34. Serializabilityを保つなら(1) 33/77
35. Serializabilityを保つなら(1) 34/77
36. Serializabilityを保つなら(1) 35/77
37. Serializabilityを保つなら(1) 36/77
38. Serializabilityを保つなら(1) 37/77
39. Serializabilityを保つなら(1) 38/77
40. Serializabilityを保つなら(1) 39/77
41. Serializabilityを保つなら(1) 40/77
42. Serializabilityを保つなら(1) 41/77
43. Serializabilityを保つなら(1) 42/77
44. Serializabilityを保つなら(1) 43/77
45. または 44/77
46. Serializabilityを保つなら(2) 45/77
47. Serializabilityを保つなら(2) 46/77
48. Serializabilityを保つなら(2) 47/77
49. Serializabilityを保つなら(2) 48/77
50. Serializabilityを保つなら(2) 49/77
51. Serializabilityを保つなら(2) 50/77
52. Serializabilityを保つなら(2) 51/77
53. Serializabilityを保つなら(2) 52/77
54. Serializabilityを保つなら(2) 53/77
55. Serializabilityを保つなら(2) 54/77
56. Serializabilityを保つなら(2) 55/77
57. Serializabilityを保つなら(2) 56/77
58. Serializabilityを保つなら(2) 57/77
59. Serializabilityを保つ なら、どっちでもいいけど どっちかになるようにスケ ジュールしないといけない 58/77
60. 現実問題 59/77
61. 現実問題 60/77
62. 現実問題 61/77
63. 現実問題 62/77
64. ここでパターン2のSerializabilityしか選択肢がなくなる 63/77
65. パターン2のSerializabilityを保てるか 64/77
66. パターン2のSerializabilityを保てるか 65/77
67. パターン2のSerializabilityを保てるか 66/77
68. パターン2のSerializabilityを保てるか 67/77
69. パターン2のSerializabilityを保てるか 68/77
70. パターン2のSerializabilityを保てるか 69/77
71. パターン2のSerializabilityを保てるか 70/77
72. パターン2のSerializabilityを保てるか 71/77
73. やった直列 になった！ 72/77
74. ただしこれは transaction_isolation = REPEATABLE_READ の動作 73/77
75. transaction_isolation = SERIALIZABLE の時はこうなる 74/77
76. ちなみに transaction_isolation = READ_COMMITTED の時はこうなる 75/77
77. transaction_isolation = SERIALIZABLE transaction_isolation = SERIALIZABLE だけがSerializabilityを保つとは限らな い 相手のトランザクションのパターンによる ‐ どんなトランザクションでもSerializabilityに近付けるフレームワークだと考えると良さそう ‐ SELECT が暗黙に共有ロックを取るようになる kintoneは transaction_isolation = SERIALIZABLE と transaction_isolation = READ_COMMITTED を使い分けていたはず 興味がない人は「SELECT(だけじゃないけど)の時のロックの動作を変えるアノテーション」とでも思っておいてOK ‐ Garoonは transaction_isolation = READ_COMMITTED と、 SELECT .. FOR {SHARE|UPDATE} で直列化したいところだけ直列化する方式だったはず 76/77
78. Any Question and/or Suggestion? 77/77