MySQLについていろいろ書いてみる(2020年)
重複しているものもあるけど一旦書く
ストレージエンジン
ストレージエンジンとは？
- MySQLはいくつかのコンポーネントを組み合わせてできている
- その中で実際にデータを格納してデータを取得するところはストレージと呼ばれていて選択することが可能
- 他にも全文検索用の
MroongaやオンメモリのMemoryストレージなどがある - それまではMyISAMがデフォルトだった
InnoDB
- MySQL5.5からデフォルトになったストレージエンジン
- トランザクションをサポートしている
- パフォーマンスの懸念があったがどっかのタイミングでMyISAMを抜いた
- 一般的にMySQLの文脈でストレージエンジンに関することを言っていたら大体InnoDBのことを言っていると言っても良い
- この説明も基本的にInnoDBのことについて書く
MyISAM
- MySQL5.4まではデフォルトだったストレージエンジン
- なんとトランザクションをサポートしていなかった
- パフォーマンスは悪くなく敢えてMyISAMを選択することもあったとか
- パフォーマンスでInnoDBに抜かれたことで存在価値がなくなった
- お疲れ様でした
インデックス
B+ツリーとは？
- Bツリーの改良版
- ツリーなのでパフォーマンスは
O(log b n) - ノード連結リストでつながっていることが多いので範囲指定がやりやすい(range検索に有効)
- https://ja.wikipedia.org/wiki/B%2B%E6%9C%A8
クラスタインデックス
- 主キーが設定されていると主キーのインデックスにデータが格納されている
- MySQL(InnoDB)で主キー検索が速いのはこの構造による
セカンダリインデックス
- 主キー以外のインデックスのこと
- 主キー以外なので、インデックスにはインデキシングされた値と主キーが格納されている
- セカンダリインデックスの場合は、2回インデックスの走査が行われる
- ちなみにOracleはインデックスとデータが2つに別れているのでそもそも2回走る
カバリングインデックス
- MySQLに関わらず、クエリで参照される値がインデックスだけで十分な場合、インデックスの値のみで完結するので速い
- パフォーマンスチューニングでよく利用されるテクニックの一つ
インデックスを設定するときの注意点
- インデックスにはコストがかかる
- 単純にインデックスを格納するストレージ
- インデックスを作成するイニシャルコスト(CPU、メモリ、ストレージ)
- 最近(MySQL5.6以降)はオンラインDDLで実行できるのでそこまで気にしていないかも？
- インデックスを維持するコスト
- 特に更新系のクエリを実行するとインデックスの再構築を行う
- インデックスを設定するときは本当に使うのか？本当に必要か？を考えて設定する
ロック
- ここからは特に断りがなければInnoDBかつREPEATABLE READを前提にします
テーブルロック
- ロックの範囲のこと
- テーブル自体をロックする
- これが起こるとパフォーマンスが低下する場合が多くなるべく避ける
行ロック
- ロックの範囲のこと
- 最小のロック単位のためこれになるように工夫する
- 行自体をロックする
- 特定のカラムだけロック、とかはない
ギャップロック
- 範囲指定してロックをかけたときにその間も一緒にかけるロックのこと
- 例えば、実データがID1、4、5とあったとき、ID1〜5でロックを書けるとID1、5はもちろんID4もロックをかけ、ID2、3もINSERTできないようになる
- ギャップロックは存在しないデータに対してもロックをかける
- 空振るとテーブルロック相当のロックをかける
- ギャップロックはインデックスをベースにしてロックをかけているのでインデックスないところをロックしようとしてもテーブルロックになるので注意
ネクストキーロック
- 結構難しい
- 行ロックとギャップロックを組み合わせたもの
- これのおかげでInnoDBではファントムが発生しない
ロックの確認
information_schema.INNODB_TRXロッキングリードとノンロッキングリード
- ロッキングリードは
select ~ for updateやselect ~ for shareなどロックをかけて読み取ること - ノンロッキングリードは通常の読み取り
- MVCCで更新中のトランザクションがある場合に2つのリードの結果が異なることがある
- ノンロッキングリードで読み取られるのは古いバージョンのデータ
- なので、最新のデータが何であろうと、ロックされていようがいまいが構わない
- つまり、最新の情報がほしいときはロッキングリードを用いる必要がある
MVCC
- MultiVersion Concurrency Control
- 日本語では多重バージョン並行処理制御などと呼ばれる
- ロック中に読み取り性能を損なわないために更新前のスナップショットを返す仕組み
- MySQLに限らず、Oracle、PostgreSQLや他の製品にもある
- InnoDBではこれがあるため、ファントムリードが起きないことになっている
- これがあることで別の問題が発生することもある
- https://ja.wikipedia.org/wiki/MultiVersion_Concurrency_Control
トランザクション分離レベル
REPEATABLE READ
- MySQL(InnoDB)のREPEATABLE READでは、ネクストキーロックがあるので事実上、ファントムリードを防ぐのでSERIALIZABLE相当の実装になっている。
- ただし、ロストアップデートをストレージエンジンが防いでくれないので気をつけないといけない(PostgreSQLとかは防いでくれるらしい)
結合の種類
NL
- Nested Loop
- InnoDBにはこれしかない
- 駆動表が大きくなると不利なのでなるべく小さくしましょう
- 要はループの入れ子
- なので一番外のループ回数が少ないと性能は上がる
Hash
- ハッシュテーブルを作成する
- メモリを食う
- インデックスがなくてフルスキャンが必要なときにはパフォーマンスを発揮するらしい
ソートマージ
- 結合する方、される方どちらもソートされている必要がある
- 完全外部結合などをするときに高いパフォーマンスを発揮するらしい
- あんま使われているところを見たことない
- こちらもメモリを食う
実行計画
統計情報
- 実行計画を作成するときに参照される情報
- インデックス使うべきか、とかデータ量からテンポラリファイルを作成するべきか、とかを計画するのに使う
- 基本的にはサンプリングされた情報が入る
- 更新頻度によっては実態と乖離することもある
- これを手動で更新して痛い目をみたことがある(Oracle)
SELECT * FROM mysql.innodb_table_statsで確認できる- https://dev.mysql.com/doc/refman/5.6/ja/innodb-persistent-stats.html
カーディナリティ
- データの多様性
- 限られた種類ならインデックス効いてなくても良いかな
- カバリングインデックスを狙ってインデックス設定するのはあり
ロックの性質
- 行ロックでも書き込みが頻発するなら待ちが発生しやすい
ヒント句
- MySQLにはインデックスヒントを指定することができる
- ただしオススメしない
- 最近のMySQLのオプティマイザはバカじゃないので基本的に任せたほうが良い
- 統計情報が更新されると適切な計画に自動で変更してくれる
- 特にデータが増えたときに適切なインデックスを選択できなくなるリスクがあるので基本的にはおまかせする
- 自分がオプティマイザより賢いという自負があるなら設定すると良い
テーブル設計
正規化
実際的な設計のポイント
- 正しく行ロックをかけていたとしても更新が集中するレコードがある場合は期待値通りのパフォーマンスが出ないことがある
- 例えばカウンターとか。単純にインクリメントしていくやつ
- そういう場合は、レコード追加で定期的に集計とかで代用できないか考える
敢えて正規化を崩す
- JOINのコストを減らしたいということだと思われる
- データを冗長に持つことを許容する？
- 多分その延長線上にNoSQLがある気がする
- 少なくとも普通のWebサービスでそこまでのトレードオフを迫られることはないのでは？
サンプル
準備
docker-compose.yml
version: '3'
services:
mysql:
image: mysql:5.7
command: --default-authentication-plugin=mysql_native_password
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: test
MYSQL_USER: user
MYSQL_PASSWORD: password
TZ: 'Asia/Tokyo'
ports:
- 3306:3306
volumes:
- ./my.cnf:/etc/mysql/conf.d/my.cnf
my.cnf
[mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci [client] default-character-set=utf8mb4
$ docker-compose exec mysql bash
データ準備
create table if not exists user(
id int unsigned not null AUTO_INCREMENT,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
);
insert into user (name) values ("user1"), ("user2"), ("user3"), ("user4"), ("user5");
-- ID=4 を削除
delete from user
where id = 4;
パターン1：trx1でインデックスを指定してUPDATE、trx2ではINSERTできる
| NO | trx1 | trx2 |
|---|---|---|
| 1 | begin; | |
| 2 | -- データを確認 select * from user; |
|
| 3 | update user set name = 'user5-1' where id = 5; |
|
| 4 | -- データを確認 select * from user |
|
| 5 | begin; | |
| 6 | -- データを確認(id=5のnameはname5) select * from user; |
|
| 7 | -- 成功 insert into user (id, name) values(4, "name4"); |
|
| 8 | -- 成功 insert into user (name) values("name6"); |
|
| 9 | -- データを確認(id=5のnameはname5) select * from user; |
|
| 10 | -- データを確認(id=4,6は見えない) select * from user; |
最終的なロックの状態
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1872
trx_state: RUNNING
trx_started: 2020-03-15 12:10:17
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 7
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 1871
trx_state: RUNNING
trx_started: 2020-03-15 12:10:16
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
mysql>
もちろんこの状態でtrx1がID=4へのINSERTをしてもロックされている
パターン2：trx1でインデックスを設定していないカラムを指定してUPDATE、trx2でINSERTできない→テーブルロックがかかる
| NO | trx1 | trx2 |
|---|---|---|
| 1 | begin; | |
| 2 | -- データを確認 select * from user; |
|
| 3 | update user set name = 'user5-1' where name = 'name5'; |
|
| 4 | -- データを確認 select * from user |
|
| 5 | begin; | |
| 6 | -- データを確認(id=5のnameはname5) select * from user; |
|
| 7 | -- 待ち insert into user (id, name) values(4, "name4"); |
|
| 8 | -- 待ち insert into user (name) values("name6"); |
|
| 9 | -- データを確認(id=5のnameはname5) select * from user; |
最終的なロックの状態
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1883
trx_state: LOCK WAIT
trx_started: 2020-03-15 16:10:49
trx_requested_lock_id: 1883:26:3:10
trx_wait_started: 2020-03-15 16:10:54
trx_weight: 2
trx_mysql_thread_id: 7
trx_query: insert into user (id, name) values(4, "name4")
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 1882
trx_state: RUNNING
trx_started: 2020-03-15 16:10:39
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 5
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
mysql>
ここでtrx_rows_lockedが5なのはテーブルロックがかかったため実データの4件+ギャップロックの1件(削除したID=4)のデータと思われる
パターン3：trx1でインデックスを範囲指定してUPDATE、trx2ではINSERTできるものとできないものがある
| NO | trx1 | trx2 |
|---|---|---|
| 1 | begin; | |
| 2 | -- データを確認 select * from user; |
|
| 3 | update user set name = concat(name, '-1') where id > 1 and id < 5; |
|
| 4 | -- データを確認 select * from user; |
|
| 5 | begin; | |
| 6 | -- データを確認 select * from user; |
|
| 7 | -- 待ち insert into user (id, name) values(4, "name4"); |
|
| 8 | -- 成功 insert into user (name) values("name6"); |
|
| 9 | -- データを確認 select * from user; |
最終的なロックの状態
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1887
trx_state: RUNNING
trx_started: 2020-03-15 16:19:55
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 7
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 1886
trx_state: RUNNING
trx_started: 2020-03-15 16:19:24
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 3
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
mysql>
- これはギャップロックによるもの
- trx_id=1886の
trx_rows_lockedが3になっているのはID=2,3,4のこと- ID=4は存在しないがギャップロックによりロック対象になる
- whereで指定した範囲は2〜4だったので最終行にINSERTできた
パターン4：trx1でインデックスを範囲指定してUPDATE、trx2ではINSERTできない
| NO | trx1 | trx2 |
|---|---|---|
| 1 | begin; | |
| 2 | -- データを確認 select * from user; |
|
| 3 | update user set name = concat(name, '-1') where id > 1 and id <= 5; |
|
| 4 | -- データを確認 select * from user; |
|
| 5 | begin; | |
| 6 | -- データを確認 select * from user; |
|
| 7 | -- 待ち insert into user (id, name) values(4, "name4"); |
|
| 8 | -- 待ち insert into user (name) values("name6"); |
|
| 9 | -- データを確認 select * from user; |
最終的なロックの状態
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1890
trx_state: RUNNING
trx_started: 2020-03-15 16:25:20
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 1
trx_mysql_thread_id: 7
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 1136
trx_rows_locked: 2
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 1889
trx_state: RUNNING
trx_started: 2020-03-15 16:24:52
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 5
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 4
trx_rows_modified: 3
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
mysql>
ロッキングリードとノンロッキングリード
| NO | trx1 | trx2 |
|---|---|---|
| 1 | mysql> begin; Query OK, 0 rows affected (0.01 sec) |
|
| 2 | mysql> select * from user\G 1. row id: 1 name: user1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.01 sec) |
|
| 3 | mysql> begin; Query OK, 0 rows affected (0.00 sec) |
|
| 4 | mysql> select * from user\G 1. row id: 1 name: user1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.01 sec) |
|
| 5 | mysql> update user -> set name = concat(name, '-1') -> where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
|
| 6 | mysql> select * from user\G 1. row id: 1 name: user1-1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.01 sec) |
|
| 7 | mysql> commit; Query OK, 0 rows affected (0.01 sec) |
|
| 8 | mysql> select * from user\G 1. row id: 1 name: user1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.01 sec) |
|
| 9 | mysql> select * from user for update\G 1. row id: 1 name: user1-1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.00 sec) |
- ロッキングリードとノンロッキングリードだと結果が異なる
- 本当にずれると困る値などはトランザクション中はロッキングリードでロックしておくとtrx2では更新待ちになって不整合が起きにくい
- ちなみにこのまま更新処理を行うとこうなる
- 多分想定していない結果になっているはずで変数として一度プログラムに読み込んで計算した結果をUPDATEとかすると期待値とずれることがあるだろう(ロストアップロード)
| NO | trx1 | trx2 |
|---|---|---|
| 10 | mysql> update user -> set name = concat(name, '-1') -> where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
|
| 11 | mysql> select * from user\G 1. row id: 1 name: user1-1-1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.00 sec) |
