ざっくり始めるクエリパフォーマンスチューニング

お断り

  • 基本的にはMySQL(InnoDB)を前提にしています
  • ものによりMySQL5.6と5.7の情報が混在しているかも
  • 一部個人的な見解が含まれています

クエリの実行順序

大きい括りでの基本戦略

  • あくまで個人的な意見
  • DBサーバにはDBサーバにしかできないことをやらせる戦略
    • アプリサーバでできるなら任せる
      • 例えば、データのソート、絞り込み
    • 転送量との相談にはなるがざっくり投げてもネットワークに負荷がかからない、アプリサーバのメモリに乗り切る場合はアプリサーバにぶん投げてアプリサーバ上でやる
    • DBサーバのリソースは貴重です
    • DBのキャッシュを有効にしているとキャッシュヒット率が改善されるかもしれません
      • MySQL8.0からキャッシュが無効になったので過度なキャッシュ利用は避けましょう
      • キャッシュは麻薬
    • DBのキャッシュ機能をオフっていてもOSキャッシュが効く可能性がある
    • DBサーバのリソースはDBサーバにしかできないことに使いましょう
      • 基本的には、更新系クエリ優先
      • 読み取りは、レプリケーションを使えばある程度はスケールアウトが可能
      • 更新系の処理で負荷に耐えれない場合は一般的にはスケールアップで対応する
      • スケールアップにも限界がある
      • マルチマスタという手もあるが一般的に運用(障害発生時など)が大変なので避けたほうが良いというスタンス
      • 物理的にDBを分ける垂直分割という手もあるがトランザクションやデータの整合性を担保するのにアプリ側の対応が必要になる場合がある
  • これが絶対正しいわけではない

実行計画のもととなるデータ

実行計画の改善の基本戦略

  • インデックスを活用する
    • インデックス特性を理解して取得する
    • カバリングインデックスが使えて効果的な場合は使う
    • カーディナリティが低い場合は、あえてインデックスを設定しない選択もする
    • 基本的にMySQL(InnoDB)は一つのテーブルにつきインデックスは一つしか使われない
      • ケースにより使われる(sort_union)
  • 不要なデータは取得しない
    • ただしアプリに処理を委ねるときはその限りではない
  • 駆動表は可能な限り小さく
  • オンメモリで完結させる
    • Using temporaryはメモリ上では対応できなくなったという意味で外部装置を使っている
    • いくらSSDが速くなったとはいえメモリに勝てるほどではない
    • 読み込み速度を意識する
    • あくまで目安。実際はSSDはもっと速いものもあるが
名前 読込速度 読込速度(MB/s)
メモリ 10GB/s 10000MB/s
有線ネットワーク 1GB/s 1000MB/s
SSD(シーケンシャルアクセス) 100MB/s 100MB/s
HDD(シーケンシャルアクセス) 100MB/s 100MB/s
SSD(ランダムアクセス) 10MB/s 10MB/s
HDD(ランダムアクセス) 1MB/s 1MB/s

カーディナリティ

  • 多様性のこと
  • 都道府県やフラグ、モードなどデータの種類が限られていること
  • データの種類が限られていることをカーディナリティが低いという
  • 一般的にはカーディナリティが低いとインデックスの効果が薄いと言われる
    • Bツリーでの絞り込み効果が低いため
  • ただし、カバリングインデックスを効かせるためにあえて複合インデックスの一部として設定することもある
    • クラスタインデックス側を参照しなくて良いのでパフォーマンスは向上する

駆動表

インデックス

出力フォーマットの変更

  • MySQL5.6から実行計画はテーブル形式の他にJSON形式でも出力できる
    • explain format=json select ~
  • テーブル形式より細かい粒度の情報が出力される
    • 特にコストが表示されるので実際に何にコストがかかっているかを分解してみることができる
  • それぞれのコストとかの説明とかあると良いよね

実行計画の改善

実行計画の出力イメージ

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
SELECT 識別子。 SELECT 型 出力行のテーブル 一致するパーティション 結合型 選択可能なインデックス 実際に選択されたインデックス 選択されたキーの長さ インデックスと比較されるカラム 調査される行の見積もり テーブル条件によってフィルタ処理される行の割合 追加情報

type

  • レコードへのアクセスタイプ。上のほうが良い
type名 説明 意訳
system systemテーブルの話
const 一致するレコードが1つしかない 1件しかないので高速
eq_ref プライマリーキーもしくはユニークなキーでの結合 1対1でマッチするから高速
ref 上記以外の等価結合 NULLの可能性もあるが1対1でマッチするから高速
fulltext FULLTEXTインデックスが使われる よく知らん
ref_or_null IS NULLを使ったアクセス NULLアクセスはインデックス効かないけど最適化されているので若干マシ
index_merge 2つのキーをマージする 単体のインデックスアクセスよりは高速
unique_subquery サブクエリの最適化パターン。一意キーを選択した場合これになる サブクエリなので一般的な最適化されたアクセスタイプよりは遅い
index_subquery サブクエリの最適化パターン。一意でないキーを選択した場合これになる ユニークではない分、unique_subqueryより遅い
range 範囲指定をした場合でのアクセスタイプ 範囲指定している分アクセス対象が多いので遅め
index インデックスをフルスキャン インデックスをあまり上手く使えてないよ
ALL フルテーブルスキャン 基本的には避ける。ただしデータが小さいテーブルはこれになることもあるしそれが最適なこともある

extra

  • 自分も知らないしかいつまんで
  • 特にこの2つはメモリに乗り切らない可能性があり外部装置に吐き出された瞬間パフォーマンスが悪化する要因になる
  • 特に目くじらを立てて一掃する必要はないが、そもそも不要な処理をさせている可能性があるので使っていることを理解し問題ない判断をした上で使うことをおすすめする
    • ORマッパが吐き出すクエリを基本的には信用しない
extra 説明 意訳
Using filesort ソート処理でクイックソートが実行されている ソートは本当にDB上でする必要がありますか?
Using temporary 一時的にテーブルを作成している 結果が多すぎませんか?もっと小さくできませんか?

その他

  • 実行されたクエリ自体はGeneral Logに出力されるので本番環境とかでも確認は可能
  • ただし、実行時間までは出ない
  • 可能ならslow query logに出力させることで実行時間を知ることができる
    • slow_query_log = 1, long_query_time = 0 と設定すると実行されるクエリがすべて出力される(かしこー)
    • ただし再起動しないと反映されないので注意
  • https://nishinatoshiharu.com/mysql-slow-query-log/

MySQLの権限を絞ってみた

権限設定でカラムレベルというものが存在するらしいのでどういうものか確かめてみた

root権限

mysql> create table if not exists grant_columns(
    ->     id int unsigned not null AUTO_INCREMENT,
    ->     name varchar(255) NOT NULL,
    ->     granted_name varchar(255) NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into grant_columns (name, granted_name) values ('aaa', 'bbb');
Query OK, 1 row affected (0.00 sec)

mysql> create user test_user@localhost identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR test_user@localhost;
+-----------------------------------------------+
| Grants for test_user@localhost                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'localhost' |
+-----------------------------------------------+
1 row in set (0.01 sec)

mysql> GRANT SHOW DATABASES ON test.* TO 'test_user'@'localhost';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> GRANT SHOW DATABASES ON *.* TO 'test_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT select(`granted_name`) ON test.grant_columns TO 'test_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW GRANTS FOR test_user@localhost;
+----------------------------------------------------------------------------------+
| Grants for test_user@localhost                                                   |
+----------------------------------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO 'test_user'@'localhost'                           |
| GRANT SELECT (granted_name) ON `test`.`grant_columns` TO 'test_user'@'localhost' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 

test_user

mysql> use test;
ERROR 1044 (42000): Access denied for user 'test_user'@'localhost' to database 'test'
mysql> SHOW GRANTS FOR test_user;
ERROR 1141 (42000): There is no such grant defined for user 'test_user' on host '%'
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| grant_columns  |
+----------------+
1 row in set (0.00 sec)

mysql> desc grant_columns;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| granted_name | varchar(255) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from grant_columns;
ERROR 1143 (42000): SELECT command denied to user 'test_user'@'localhost' for column 'id' in table 'grant_columns'
mysql> select granted_name from grant_columns;
+--------------+
| granted_name |
+--------------+
| bbb     |
+--------------+
1 row in set (0.00 sec)

mysql> 

descでは他にカラム定義がないように見えるのに*を使うと怒られる(´・ω・`) 「見せる」設定はできるけど「見せない」という設定はできないように見える。

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 updateselect ~ for shareなどロックをかけて読み取ること
  • ノンロッキングリードは通常の読み取り
  • MVCCで更新中のトランザクションがある場合に2つのリードの結果が異なることがある
  • ノンロッキングリードで読み取られるのは古いバージョンのデータ
  • なので、最新のデータが何であろうと、ロックされていようがいまいが構わない
  • つまり、最新の情報がほしいときはロッキングリードを用いる必要がある

    MVCC

  • MultiVersion Concurrency Control
  • 日本語では多重バージョン並行処理制御などと呼ばれる
  • ロック中に読み取り性能を損なわないために更新前のスナップショットを返す仕組み
  • MySQLに限らず、OraclePostgreSQLや他の製品にもある
  • 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> 
  • 範囲指定に最終行のID=5が含まれているのでネクスキーロックが効いて末尾へのINSERTができなくなった

ロッキングリードとノンロッキングリード

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)

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 * from test where id in (1, 2, 3, 4, 5)select * from test where id in (1, 2, 3, 4)でロックする範囲が大きく異なる場合がある
    • この場合では、前者のクエリでフルスキャンが実行されたとすると id にインデックスが設定されていてもテーブルロック(+ネクスキーロック)が発生する
    • ただし、これは実行計画に依存するので試して見ないとわからない
    • 発生しうるのは updatedeleteの2つ(insertwhereがないので対象外)
    • 同じ条件で selectの実行計画を見るか、更新系クエリでも実行計画が取れるのでそれで見ても良い

      ロッキングリードとノンロッキングリード

  • ロッキングリードはselect ~ for updateselect ~ for shareなどロックをかけて読み取ること
  • ノンロッキングリードは通常の読み取り
  • MVCCで更新中のトランザクションがある場合に2つのリードの結果が異なることがある
  • ノンロッキングリードで読み取られるのは古いバージョンのデータ
  • なので、最新のデータが何であろうと、ロックされていようがいまいが構わない
  • つまり、最新の情報がほしいときはロッキングリードを用いる必要がある

    MVCC

  • MultiVersion Concurrency Control
  • 日本語では多重バージョン並行処理制御などと呼ばれる
  • ロック中に読み取り性能を損なわないために更新前のスナップショットを返す仕組み
  • MySQLに限らず、OraclePostgreSQLや他の製品にもある
  • InnoDBではこれがあるため、ファントムリードが起きないことになっている
  • これがあることで別の問題が発生することもある
  • https://ja.wikipedia.org/wiki/MultiVersion_Concurrency_Control

    トランザクション分離レベル

    REPEATABLE READ

    結合の種類

    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

    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

DevOpsがケイパビリティを高める

メモ

開発組織のパフォーマンスを計測

望ましい尺度

  • デリバリのリードタイム
    • 短い方が望ましい
    • 早いタイミングでフィードバックが受けれるため
  • デプロイの頻度
    • 意味するところはバッチサイズ
    • ただし頻度の方が計測しやすいため頻度を採用している
    • リードタイムと頻度はパフォーマンスのテンポを計測
  • サービス復旧の所要時間
    • 信頼性はいかに早く復旧できるか
    • そもそも複雑で失敗は不可避
  • 変更失敗率
    • 信頼性を計測する
    • サービス低下、稼働停止など本番サービスに影響を与えた率

LeanとDevOpsの科学[Accelerate] テクノロジーの戦略的活用が組織変革を加速する