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

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

メモ

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

望ましい尺度

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

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

1on1で気をつけている7つのこと

みなさんの会社では1on1はやっていますか？

私の会社では会社の仕組みとして1on1をやっています。 1on1って何すれば良いの？という方のために私がメンターとして1on1を運営する中で気をつけていることをまとめたいと思います。

これがきっかけで1on1を始められる方が増えて現場が円滑になれば良いな。

7つのことと書いていますが多分増えます。今は絞り出して7つだった。

1on1の目的

• 業務上困ったことを相談したりする場
• 目標の振り返り、確認の場
• 雑談

基本的には、業務なので業務中心の話をします。とはいえ、他の話をしたり聞いたりして現在どういう状況なのか？ということを知っておくことは大切です。

1on1をする上で気をつけていること

• あなただけの時間ですよ
  • メンターをやる人は多分忙しい、ミーティングも多い、気軽に声をかけるのがはばかられることもある
  • だからこそ、1on1をしているときはあなただけの時間です、というのが重要
  • PCは見ないし、スマホもいじりません(議事録は取るけど)
  • 緊急の場合は必ず一言断ってから
• 必ず閉鎖空間で行う
  • 変な意味ではなく、何を言っても誰にも聞かれない、という空間を作る
  • 恥ずかしいこと、他人に聞かれたくないことを言いやすくなる
    • 例えば、「昇進したいです！」とはオープンな場では言いにくい
  • ミーティングルームが取れない場合は次週にスキップするくらい大切にしている
• 否定しない
  • (なんだかテクニックみたいで嫌だけど、)否定されたら普通は次相談しようとは思わない
  • 否定されたがっている人以外は普通は嫌だ。自分も嫌だ。
• 一緒に考える、提案する
  • 命令されたら業務、業務の一環だけど業務じゃない(表現が難しい)
  • 答えを渡すのではなく一緒に作る。なので同じ問題でも解決策が人それぞれになる
• 毎回決まったフォーマットを利用している
  • 何を聞かれるのかわからないのは不安なので大体なにを聞かれるかを予測できるようにする
  • アジェンダは毎回共有すると良い
• 前回話したことを踏まえた上でアジェンダを作る
  • 前回〇〇について話していたけど今はどう？
  • 一回きりではなく連続していることを意識する
  • というより問題解決が目的なら決着するまで追いかける
  • 不満があるなら現在どうかを確認する
• 同じペースで行う
  • 必要に応じて回数は増やしても減らすことは基本的にない
  • ただしそもそものペースを落とすことはある
  • 業務の一環でやっているので雑談しかしてなくても業務

1on1をやってみて

• メンティーの成長はもちろんメンターも成長する(かもしれない)
• 本当は業務に直接関係ない人ともやると新鮮なんだろうな、とは思う

1on1を始める前に読んだ本

• ここに書いてあることをできるところから実践していった

  シリコンバレー式 最強の育て方 ― 人材マネジメントの新しい常識 1 on1ミーティング―

MySQLについていろいろ書いてみる

まずはRDB共通なものを書いてMySQLのことを挟んでいく。自分用のメモとして眠っていたものを転載。

ACIDとトランザクション

• ACIDとは
  • 原子性 (Atomicity)
  • 一貫性 (Consistency)
  • 分離性 (Isolation)
  • 持続性 (Durability)
• ACIDはトランザクションの概念と密接に結びついている
• トランザクションを保証する概念
• トランザクションは、独立していて、確定すると継続され、ALL OR NOTHINGで一貫しているもの
• 特に複数のテーブルを更新しているときには矛盾が発生する危険がある

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

• ACIDのIのこと
• MySQL(InnoDB)がサポートしているのは以下のレベル
  • SERIALIZABLE
    • 複数のトランザクションが並列実行された場合でも、それぞれが直列に実行された場合と同じ結果が生成されることが保証される
  • REPEATABLE READ
    • 同じセッションの間は、同じ読み取り結果が保証される
  • READ COMMITTED
    • 同じセッションの間でも、他のトランザクションが変更し、かつコミットしたデータが読み取られる
  • READ UNCOMMITTED
    • 同じセッションにおいて、他のトランザクションがコミットしなくても、変更したデータが見える
• ただし、分離レベルによらず、SQL文の実行以外、整合性制約をチェックするときのスキーマ定義の暗黙の読み込みや、参照整合性制約によって実行される参照動作の実行において、P1〜P3(後述)は発生しない
• 上に行くほど、並列性が低い代わりにデータの一貫性、結果の再現性が高い
• 詳細は以下の通り
• ただし、InnoDBは REPEATABLE READでもファントムリードが発生しないことになっている

	ダーティリード	ファジーリード	ファントムリード
READ UNCOMMITTED	発生する	発生する	発生する
READ COMMITTED		発生する	発生する
REPEATABLE READ			発生する
SERIALIZABLE

ここが詳しい https://qiita.com/PruneMazui/items/4135fcf7621869726b4b

• それぞれの分離レベルでSERIALIZABLEから離れれば離れるほど読み取り時に不整合が発生しやすい
• それぞれの読み取り不整合がなんなのか

ダーティリード(P1)

• 別のトランザクションでコミットされてないデータが読み取れる現象
• つまりコミットしなくても読み取れるのでロールバックするとデータが異なることになる

ファジーリード(P2)

• 別のトランザクションで更新されたデータを読むことにより、一貫性がなくなる現象
• つまり自分がコミットする前に、更新されたデータが更新前後で異なる値として取得される

ファントムリード(P3)

• 別のトランザクションで挿入されたデータが見えることにより、一貫性がなくなる現象
• ファジーリードとの違いは更新と挿入

他にも

ダーティーライト(P0)

• トランザクションT1がデータを更新しトランザクションT2がT1がコミットかロールバックを実行する前に同じデータを更新する。このとき、T1とT2のどちらかがロールバックを実行すると正しいデータの値は何であるべきか不明確になる

失われた更新(ロストアップデート)(P4)

• 例外的
• トランザクションT1がデータを読み出す。T2がそのデータを更新する。最後にT1がT2が更新した値の前に基づいてデータを更新しコミットする。T2の更新が消える。

CURSOR STABILITY分離レベル

• というものが存在しているが、READ COMMITTEDのSQLカーソルに対するロック動作なので割愛

ロック

• MySQLには、というよりDBにはロック機構が存在している。理由は、マルチユーザーで単一のリソースにアクセスするのでリソース競合が発生するため。正しく更新(INSERT,UPDATE,DETELE)されるようにするための仕組みがロック
• MySQLのロック
  • ロックの種類( https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html )
    • レコードロック
      • クラスタインデックスを使って取得されるロック
      • 更新対象のレコードのみロックできれば最も理想的なロックになる
    • ギャップロック
      • インデックスレコード間のギャップ、先頭インデックスレコードの前や末尾インデックスレコードのあとのギャップをロックする仕組み
      • キーを指定して以上とかでロックをかけるとほぼテーブルロックとかになる怖いやつ
    • ネクストキーロック
      • ロッキングリードにおいてファントムを防ぐために用いられる仕組み
      • これは行ロックとギャップロックを組み合わせたもの
      • ロック対象の次の値までをロックすることでファントムリードを防ぐ(ファントムリードは別トランザクションで挿入されたデータが見えてしまう現象のため)
      • select ~ for updateでロックした場合、データが挿入できなければファントムリードは防げる。ただし、並列性はSERIALIZABLEほどではないがプレーンなREPEATABLE READよりは下がる
  • 専有レベル(lock mode)
    • 排他ロック(X 多分 exclusive の X)
      • トランザクションは行を更新または削除できます。ほかのトランザクションは、同時にその同じ行でどのようなロックも獲得できない
    • 共有ロック(S 多分 share の S)
      • トランザクションは行を読み取ることができます。複数のトランザクションが同時にその同じ行で S ロックを獲得できる
    • インテンションロック
      • テーブルレベルに適用するタイプのロック
      • よくわからんからあとで調べる

MVCC(multi version concurrency control)

• 読み取り専用のトランザクションにおいて、若干の時差を許容することで一貫性のある読み取りを実現するための仕組み
• InnoDBの専売特許ではなく、Oracle、PostgreSQLにも存在するらしい( https://ja.wikipedia.org/wiki/MultiVersion_Concurrency_Control )
• InnoDBでは、REPEATABLE READ、READ COMMITTEDで利用されている
• InnoDBではこれがあるため、ファントムリードが起きないことになっている
• ただし、このことが別の問題を引き起こしている

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

• MVCCはノンロッキングリードでしか用いられない
• ノンロッキングリードとは？
  • ノンロッキングとは行にロックをかけないという意味
  • ロッキングは行にロックをかける
• これは、 select ~ for updateもしくはselect ~ lock in share modeを使うか否かによって取得される結果が異なるということである
• ノンロッキングリードで読み取られるのは古いバージョンのデータなので、最新のデータが何であろうと、ロックされていようがいまいが構わない。そのような場合でしか、MVCCは用いられない。
• つまりは、MVCCはリード性能を高めるための仕組みであって更新時には問題を起こす可能性がある
• ファントムリード(別のトランザクションで挿入されたデータが見えること)が防げているのはノンロッキングリードのときのみでロックありでリードするロッキングリードの場合はMVCCの対象から外れて最新のデータが見える(つまりはファントムリードが発生する)

インデックス

• InnoDBはクラスタインデックスという構造を採用している
• クラスタインデックスはデータがインデックスのリーフノード上に格納されている
• Oracleみたいにインデックスとデータ領域が分かれているわけではない(今は知らん)
• 主キーのインデックス上にデータが存在している。そのため、主キー検索すると爆速(一回の走査でデータも取れちゃうため)
• あとはセカンダリインデックスというものがある。
• これがいわゆるインデックスというもの。
• 独自に指定して作成するインデックス(create index ~で作成されるインデックス)はすべてこれ
• セカンダリインデックスを使う場合は、セカンダリインデックスを走査してからクラスタインデックスを走査するので効率が悪い
• とは言ってもそういう仕組みのDBだってあるしそこまで問題にはならないのでは？
• 複合インデックスを用いたカバリングインデックスとかを使うと効率上がる(インデックスだけ見れば良いので)
• とは言え、インデックス設定しまくるのは良くないのでご利用は計画的に。(インデックスショットガン)

オンラインDDL

• MySQL5.6から一部のDDLがオンラインで実行できるようなった(書き込みでロックが発生しなくなった)
• 少なくともインデックス作成はオンラインでできる。ただし、IO負荷やCPU負荷は考慮すること
• 力尽きたのであとは以下のリンクを参照してください

dev.mysql.com

docker環境にPythonの実行環境を用意する

Macでgensimが読み込めないエラーが発生していた件で、問題の切り分けのためにDockerを使ってPythonの実行環境を作成してそこで色々試してみた話 結局、ライブラリを最新版より1つ古いものを使うようにすれば良いという結果になったけどクリーンな環境をサクッと用意できるのはDockerのメリットだなーと実感した。

参考にした、というよりほぼこちらの内容のまま(ありがとうございます) qiita.com

Dockerfile

FROM python:3
USER root

RUN apt-get update
RUN apt-get -y install locales && \
    localedef -f UTF-8 -i ja_JP ja_JP.UTF-8
ENV LANG ja_JP.UTF-8
ENV LANGUAGE ja_JP:ja
ENV LC_ALL ja_JP.UTF-8
ENV TZ JST-9
ENV TERM xterm

RUN apt-get install -y vim less
RUN pip install --upgrade pip
RUN pip install --upgrade setuptools

docker-compose.yml

version: '3'
services:
  python3:
    restart: always
    build: .
    container_name: 'python3'
    working_dir: '/root/'
    tty: true
    volumes:
      - ./:/root/

Dockerfileとdocker-compose.ymlがあるディレクトリで以下のコマンドを実行

$ docker-compose up -d --build
$ docker exec -it python3 bash

コンテナの中に入る。 試したかったのはgensimなのでこれだけインストール

$ python -m pip install gensim
# 実際は
$ python -m pip install gensim==3.5.0

Dockerfileに含めておきたいときはこんな感じ

RUN pip install --no-cache-dir gensim==3.5.0

--no-cache-dir オプションがないとValueError: numpy.ufunc has the wrong size, try recompiling. Expected 192, got 216 のエラーが出る