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 にインデックスが設定されていてもテーブルロック(+ネクストキーロック)が発生する
    • ただし、これは実行計画に依存するので試して見ないとわからない
    • 発生しうるのは update と deleteの2つ(insertは whereがないので対象外)
    • 同じ条件で selectの実行計画を見るか、更新系クエリでも実行計画が取れるのでそれで見ても良い

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

  • ロッキングリードは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

    結合の種類

    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