2018-07-20

実践ハイパフォーマンスMySQLメモ

はじめに

実践ハイパフォーマンスMySQLを読む中で、知らなかった単語や気になったことのメモ
この書籍では5.5について書かれているが、最新は5.7なので異なる点がいくつかあるのだろうと懸念はある。
とはいえ、根本的な仕組みはそうそう変わるものではないだろう。

1章

MySQLの論理アーキテクチャ

MySQLは3つのレイヤーに分けることができる
1. 接続の処理、認証、セキュリティといったネットワークベースのほとんどのクラインアント/サーバーツールで必要となるレイヤー
  - 1クライアント1スレッドが割り当てられ、スレッドは1つのコアまたはCPUに関連づけられる
  - サーバーはスレッドをキャッシュするので、新たな接続の度にスレッドの作成・削除は不要
2. クエリ解析、分析、最適化、キャッシュ、組み込み関数などの中枢ともいえるレイヤー
  - クエリのパース・書き換え、テーブル読み取り順序の決定、使用するインデックスの選択など
3. ストレージエンジンのレイヤー
  - InnoDBがおすすめ
    - 行ロック(MVCCは下記参照)
    - クラッシュセーフ
    - トランザクショナルストレージ
  - MyISAMは、テーブルロック、notクラッシュセーフ、notトランザクショナル
MVCC(MultiVersionConcurrencyControl)
- InnoDBの行ロックで単純な行ロックと異なっている

クラッシュセーフとは

プロセスやマシンが正常に動作しなくなって再起動した後でも以前の状態に戻って処理を再開できるということ

2章

ベンチマークについて

各ベンチマークツールについて、ベンチマークの手法などについての説明
知識として持っておくものではなく、実際にベンチマークする時にこの章を手がかりに進めていくのがいいと思う。
- 普段の業務で使わず忘れてしまうので

3章

プロファイルについて

パフォーマンスを最適化するにあたって必要なことが示されている
この章も2章同様、その存在や概要を把握しておくに留め、必要になった段階でその詳細に入るとよい

4章

最適なデータ型

可能な限り小さいデータ型を選択する
- 例えば選手の背番号とかであれば、int(4バイト)ではなくて、tinyint(1バイト)でいいよね。
単純なデータ型を選択する
- 文字列型よりも整数型の比較コストが低い。
- 例えば、IPは整数型に直すのがよい
可能であればNULLを使用しない
- インデックスや値の比較を複雑にするのでできるだけ避けるべき
- とはいえ、意味不明なデフォルト定数を用いるくらいならNULLでいい
外部キーになりうる値には同じデータ型を使おう
IDにはできるだけ整数を使おう。コストを下げる

正規化と非正規化

テーブルを正規化した結果、インデックスが上手く利用できない場合には非正規化もあり。
- その場合には、参照頻度と更新が煩雑になる手間のトレードオフになるということを認識しよう

5章

プレフィックスインデックスの適切な設定

非常に長い文字列にインデックスをつける場合、最初の数文字にインデックスをつけることで記憶域を節約し、パフォーマンスを改善できるかも。
- 下記の方法で最適なプレフィックスの文字数を確認できる

mysql> SELECT COUNT(DISTINCT カラム) / COUNT(*) FROM テーブル;

=> 文字列全体でインデックスを貼ったときの選択性を出す
   選択性 - カラム内の個別値の数 (カーディナリティー) をテーブル内のレコード数で割ったもの
=> 結果の値が高ければ高いほどSELECT時に除外できる件数が多くなり望ましい(カラムの値の重複が少ないので、SELECTされる行も少ない)

mysql> SELECT COUNT(DISTINCT LEFT(カラム, 1)) / COUNT(*) AS 1,
    -> SELECT COUNT(DISTINCT LEFT(カラム, 2)) / COUNT(*) AS 2,
    -> SELECT COUNT(DISTINCT LEFT(カラム, 3)) / COUNT(*) AS 3,
    -> SELECT COUNT(DISTINCT LEFT(カラム, 4)) / COUNT(*) AS 4
    -> FROM テーブル;

=> 各プレフィックスの文字数ごとの選択性を出して、文字列全体でインデックスを貼った時の値に近しい値が出たところの文字数をプレフィックスにする

複数列のインデックス

適切な列の順序でインデックスを設定しよう
- 何が適切かは実際に発行するクエリ、インデックスに設定される各列の値の重複が少ない(カーディナリティが高い)ものを選択しよう

カバリングインデックスとセカンダリインデックス

別記事にまとめる

範囲条件について

WHERE句で範囲条件を指定できる方法は下記のように2つあり、EXPAINのtypeではどちらもrangeになるが、インデックスにおいて違いが存在する
- IN()を使ってリストで指定する方法
  - 1クエリの中に複数用いても、インデックスを使用することができる
- 不等号やBETWEENで範囲を指定する方法
  - 1クエリの中に複数使用した場合、インデックスが効かなくなってしまう
どちらも範囲条件を指定するものではあるが、前者の場合、等値条件を複数指定しているからだと考えられる

選択性は低いが常に検索条件やSELECTで指定されるカラムのインデックス戦略

インデックスのプレフィックスにIN()で指定する
- 例) プロフィールを取ってくる場合は、sexをクエリに含めることが多いだろう。
  その場合、sexは選択性が低い(m,wの2通りだと考えられるから)ので、インデックスからは除きたくなるかもしれない。
  しかし、インデックスのプレフィックスにsexをつけることで、カバリングインデックスにしたり、Using whereの状態を免れることができるだろう。
  全性別を指定する場合にはIN('m','w')で検索することで、上記したように他の範囲条件とも併用することもできる

まとめ

単一行へのルックアップを回避しよう
- テーブルアクセスしての検索?
ファイルソートを回避しよう
インデックスのみを使用するアクセスを利用できるようなインデックスとクエリを選択するようにしよう

6章

スロークエリの原因

アプリケーションが必要以上に多くのデータを取得していないか
MySQLサーバーが必要以上に多くの行を解析していないか
- 下記3項目を確認
  - 応答時間
    - 処理時間と待ち時間(I/O処理の完了や行のロックなど)からなるので正確な計測は難しい
  - 調査される行と返される行の数
  - 調査される行の数とアクセスタイプ
    - EXPLAINの記事参照

クエリ再構築

1度に処理するのは1000行など、クエリを分割する
JOINされているクエリを複数クエリに分割する
- ロックの競合が少なくなる可能性がある
- 結合するテーブルのうち、変更頻度が高いものが1つだけの場合など、他のクエリでクエリキャッシュを使えるようになる

クエリがどのように実行されるか

大まかな流れ。強調した部分は下で簡単に補足・解説

クライアントがSQL ステートメントをサーバーに送信
サーバーがクエリキャッシュをチェック。ヒットしたらキャッシュの結果を返す。なければ3へ
サーバーがSQL ステートメントを解析し、前処理を行い、最適化してクエリ実行プランを作成
クエリ実行エンジンがストレージエンジンAPIを呼び出し、クエリ実行プランを実行する
クエリ最適化プロセス(括弧内は処理する主体)
- 解析(パーサー)
  - クエリをトークンに分解し、それらをもとに解析ツリーを構築
  - MySQLのSQL文法を使ってクエリを解釈し、検証する
- 前処理(プリプロセッサ)
  - パーサーで解決できないセマンティクスをチェックする
    - テーブルと列が存在することを確認し、名前とエイリアスを解決して列参照を明確にするなど
    - セマンティクスの意味については、ここなど参照
- 最適化(クエリオプティマイザ)
  - 最もコストの低いクエリ実行プランに変換。ただし、メモリキャッシュ、他クエリの影響などは考慮されない
  - 下記のような最適化を行う
    - 結合の並び替え
    - OUTER JOINからINNER JOINへの変換
    - 代数的等値ルールの適用
      - 例) (5=5 and a>5) は、a>5 のみに
    - COUNT、MAX、MINの最適化
      - インデックスを有効活用すればテーブル内の全データをみなくてもよくなる
    - 定数式の評価と縮小
    - カバリングインデックス
    - サブクエリの最適化
    - 早期終了
      - auto_increment の id に -1 で検索をかけるような不可能な条件などは最適化段階で終了する

第7章

業務で使われている、パーティション、プリペアドステートメント、クエリキャッシュ

パーティション

(主に)特定カラムの範囲で、物理的にデータを区切る。
- WHERE句でその範囲が除外された時には、オプティマイザはそのテーブルを考慮しない

プリペアドステートメント

流れは下記の通り
1. [クライアント]プレペアドステートメントをサーバーに送信
2. [サーバー]解析/処理して、部分的に最適化されたクエリを表す構造を格納し、クライアントにステートメントハンドルを返す
3. [クライアント]ステートメントハンドルを指定することで何度もそのクエリを繰り返し実行できる
プリペアドステートメントが効率がよい可能性がある理由
- サーバーはクエリ解析を一度だけ行えばよい
- サーバーは部分的なクエリ実行プランをキャッシュするため、クエリの最適化ステップを一度だけ実行すればよい
- バイナリプロトコルを通じてパラメータを送る方が、ASCIIテキストよりも効率がよい
- 実行のたび、クエリテキスト全体を送るのではなくパラメータだけを送ればよいのでネットワークトラフィックが少なくなる
- パラメータを直接サーバー上のバッファに格納するため、サーバーがメモリ内で値をコピーする必要がある

クエリキャッシュ

クエリキャッシュとは
- まずSELECT文の完全な結果セットをキャッシュする。
  後で全く同じクエリが投げられ、キャッシュされたデータが有効な場合にはクエリの解析・最適化・実行をスキップして、結果をキャッシュ内容から返却できる
クエリキャッシュの注意点
- キャッシュヒットの確認
  - キャッシュヒット確認時、クエリの解析、正規化、パラメータ化を行わない。
    なので、大文字、小文字、スペース等も全てが一致した場合にのみヒットする
  - 結果を生成したクエリが決定的でない限りキャッシュされない(CURRENT_DATE()などの関数が入っている場合などはキャッシュされない)
    ただし、SELECT時にキャッシュの確認は行われる
- クエリキャッシュはいくつかの点でオーバーヘッドになる
  - 読み込みクエリは開始する前にキャッシュをチェックしなければならない
  - クエリがキャッシュ可能で、まだキャッシュに存在しなければ、結果を生成してから格納する
  - 書き込みクエリが変更するテーブルを使用するクエリがあれば、そのキャッシュエントリを無効にしなければならない。

参考

2018-07-14

DockerとかCI試す用のアプリ作ったよ

Dockerで何かアウトプットをしておきたかったのと、
CircleCIを使ってみたかったので簡単な、本当に簡単なアプリケーションを作った。
githubに置いておいた。
使い方はリンク内のINSTALLATION AND LAUNCHを参照のこと。

このアプリケーションでCIを試す

CIについてやCicleCIの登録についてなどは、こちらのブログを見て頂くとよいかと。
github、CircleCIにこのアプリケーションを登録して、CIが機能しているかを確認するにはテストコードの値を変えてみると分かりやすい。
例えばプロジェクト内のcalculator/calculator_test.goを下記のように変更してgithubにpushするとCircleCIから失敗しましたよ、ってメールが来る。

  7 func TestAdd(t *testing.T) {
  8     tests := []struct {
  9         left     int
 10         right    int
 11         expected int
 12     }{
 13         {1, 2, 3},
 14         {-1, 2, 1},
 15         {0, 0, 0},
 +          {1, 1, 20},      // 1 + 1 = 20 を期待するテストになっているので失敗する!!
 16     }
 17
 18     for _, tt := range tests {
 19         sum := Add(tt.left, tt.right)
 20         if sum != tt.expected {
 21             t.Errorf("%d is expected. got=%d", tt.expected, sum)
 22         }
 23     }
 24 }

上記の変更をpushした後、修正した箇所を削除して再びpushするとエラーが修正されたよ、ってメールが来る。
なお、ブランチはmasterのままでも別ブランチを切ってもどちらでもよい。

アプリの見所

エラー時の表示は必見

2018-06-30

Athenaで元データのカラム数とテーブル定義が一致しない時どのような挙動になるか

概要

ある日のデータからカラムが追加されることになった。
その時、Athenaではどのようなデータが表示されることになるのだろうか

テスト準備

csvファイルを2つ作成(Athenaに対応しているファイル形式なら何でもよい)
- まずは下記カラムのファイルをここで作成した

price	name
int	string

上記カラムのファイルの末尾に、country(string)のカラムを追加したcsvファイルを作成
S3
- 特定バケットにディレクトリを2つ作成(dir=1, dir=2など、Athenaのパーティンションで使えるような名前で)
  - 1つのディレクトリ(hr=1)には、カラム追加前のcsvファイルを置く
  - もう1つのディレクトリ(hr=2)には、カラム追加後のcsvファイルを置く
- まとめると、それぞれのディレクトリのファイルには下記のカラムが存在する

hr1	hr2
price, name	price, name, country

Athena
- カラム追加前後、それぞれのテーブルを作成する
- Load partitionsを実行して、Athenaにパーティションを認識させる

テスト内容と結果

テーブル毎にSELECTするカラムと読み取るパーティション、その実行結果を出すと下記の通り

追加前のカラムが定義されているテーブル(price, name)

SELECTするカラム / 読み取るパーティション	hr1(price,name)	hr2(price,name,country)	hr1 と hr2 両方
price, name	price,nameの値が取得可能	price,nameの値が取得できる	price,nameの値が取得できる
price, name, country	countryが定義されていないのでエラー	countryが定義されていないのでエラー	countryが定義されていないのでエラー

追加後のカラムが定義されているテーブル(price, name, country)

SELECTするカラム / 読み取るパーティション	hr1(price,name)	hr2(price,name,country)	hr1 と hr2 両方
price, name	price,nameの値が取得可能	price,nameの値が取得できる	price,nameの値が取得できる
price, name, country	price, nameの値を取得 countryは空白で出力	price, nameの値を取得	price, nameの値を取得データにcountryがあれば取得、なければ空白

まとめ

ある時点から元データのカラムは増やしてよい
それに合わせてAthenaのテーブルカラムも増やしてよい
- 元データに存在しないカラムをSELECTしても、空白で出力されるだけでエラーにならない
- 空白があってもそのカラムに対してSUM()等の関数は実行できる

2018-06-22

AWS 認定ソリューションアーキテクトに合格するまで

AWS認定ソリューションアーキテクト – アソシエイト (2018 年 2 月リリース)を取った。　　どんなことやったかとかを書いておく。

前提レベル

エンジニア歴1年半
普段ちょこちょこAWSサービスに触るものの自分で環境構築することはほぼない
使ってたりちょこっとでも設定したことあるのは大体こんな感じだった。
- VPC、 EC2、 S3、 CloudFront、 ELB、 Route 53、 RDS(Aurora)、 SQS、 DynamoDB、 Athena、 Elastic Cache
いろいろ使ってはいたけど詳細は把握していなかったし、EFS、Glacier、Auto Scaling、 Lambda なんかは名前は知っているかなーくらいの素人に毛が生えたレベル。

やったこと

本は下2冊を時には手を動かしながら読み進める。特に上の本は必読と言っていい。
- 合格対策 AWS認定ソリューションアーキテクト - アソシエイト
  - ちょっと古い本なので、最新情報が抜けていたりするけど基本的なことは変わっていないので十二分に役立つ
  - 2週くらい
- Amazon Web Services 業務システム設計・移行ガイド
  - オンプレからクラウドへの移行という趣旨ではあるけど、実際に手を動かしてやってみるような部分も多く、役に立った。
  - 関係ない部分も多いので取捨選択が必要。特にアカウント管理、オンプレとの連携部分は流し読みでよい。
AWS クラウドサービス活用資料集を読む
- 下記サービスはマストで読んだ方がいいかと
  - VPC
  - S3
  - Glacier
  - EC2
  - ELB
  - Auto Scaling
  - IAM
  - CloudWatch
  - DynamoDB
  - SQS
  - RDS
- 下記も読んどこう
  - KMS
  - SNS
  - Lambda
  - Redshift
実際にAWSでいろいろやってみる
- VPC
  - プライベートサブネットのルートテーブルのデフォルトゲートウェイにNATゲートウェイを指定してみるとか(これ頻出!!)
  - ユーザーデータ入れてEC2立ち上げてみる
  - Auto Scaling は特に使ってみた方がいいかな。
模試
- 2000円かかってしまうが、問題の感じを掴むのに受けておいた方がよい。

試験本番

困った時の選択肢はこれ!!
- DynamoDBは拡張性・可用性高いよ。でも大きすぎるデータは入れれないから、そういう時はメタデータだけを入れよう
- プライベートサブネットでNATゲートウェイ使って外のリソースにアクセスできるようにする
- EFSを使ってEC2間でリソース共有

受けて思ったこと

解答で頻出するサービスや構成から、AWSの考えるベストプラクティクスが感じ取れる
高可用性・拡張性・セキュリティ(リソースにアクセスできるのは最低限にする)などがそのベストプラクティスの根本にある
無料枠あるし、ちょっと使ってみるくらいだったら安いからどんどんインスタンス起動してみよう。
会社でAWSを使っているなら、どのサービスがどんな設定で使われているかとか、ネートワーク構成とかを改善点を考えながら見てみると業務理解も深まり勉強にもなって一石二鳥。

2018-06-16

MySQLのEXPAINの見方

EXPLAINとは

クエリオプティマイザがクエリをどのように実行するのかを解明する手段
- どのインデックス使ってるのかなーとか確認する
実際に実行するとこんな感じになる

mysql> EXPLAIN SELECT * FROM covering_test WHERE key1 = 1;
+----+-------------+---------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | covering_test | NULL       | ref  | key1          | key1 | 4       | const |    2 |   100.00 | NULL  |
+----+-------------+---------------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

EXPLAINをどう使うの？

下記2点を達成できるようEXPLAINを読み解いていこう

書き換える前と後でクエリの実行結果が同じになる

EXPLAINがよりよい実行計画を表示する

各カラムについて

特に重要なのはtypeカラム

id

各SELECT文を識別するための通し番号で実行順序

select_type

クエリの種類を表す

SIMPLE
- クエリにサブクエリやユニオンが含まれていない
  - クエリがSIMPLEってわけではない!
- 上の例の通り
SUBQUERY
- SELECTリストのサブクエリに含まれている(FROM句ではない)SELECT文
DERIVED
- FROM句のサブクエリに含まれているSELECT文
  - MySQLはサブクエリを再帰的に実行して一時テーブルに配置する
  - サーバーはこの一時テーブルを「派生テーブル」と呼ぶが、それはサブクエリから派生しているから
PRIMARY, UNION, UNION_RESULT
- PRIMARY - UNIONの1つ目のSELECT文であることを示す
- UNION - UNIONの2つ目以降のSELECT文であることを示す
- UNION_RESULT - UNIONの無名の一時テーブルから結果を取得するためのSELECT文であることを示す

table

アクセスする対象のテーブル
結合がどの順番で行われているかもこのカラムを上から見ていくと分かる
クラスタインデックスやカバリングインデックスで実際のテーブルにアクセスしない場合でも表示されるようだ

partitions

使われているpartitionを示す
使われていない場合はNULL

type

MySQLがテーブル内の行を検索する方法

ALL
- テーブルスキャンと呼ばれるもの
- インデックスが全く使われず、テーブルを最初から最後までスキャンしなければならない
- これが出た場合、改善が必要と思われる
index
- カバリングインデックス(ExtraカラムにUsing indexと表示される)とそれ以外で異なる
  - カバリングインデックスの場合
    - インデックスツリーのみが全て読み込まれるのでALLより速い
  - それ以外の場合
    - インデックスからの読み取りを利用して、インデックス順でデータ行を参照する。高コスト
range
- インデックスを利用した範囲検索
- WHERE句にBETWEENまたは不等号が来ている場合
ref
- ユニーク（PRIMARYまたはUNIQUE）でないインデックスを使った等価検索（WHERE key = value）
eq_ref, const
- PRIARY KEYまたはUNIQUE KEY(単一行であることが分かっている)が使われている
- eq_refはJOINのとき、constはそれ以外の時という違いがある

possible_keys

利用可能なインデックスの候補

key

実際に利用されたキー

key_len

選択されたキーの長さ
- intだったら4バイトとか
- 複合インデックスを使った場合には2つのキーの長さの合算値
短い方が高速

ref

キーと比較されている値やカラムの種類
- 定数の場合にはconst
- joinの場合には、結合する相手側のテーブルで検索条件として利用されているカラム

rows

フェッチされる行数の見積もり。あくまで見積もりなので正確な行数ではない
- ただし、サブクエリ(DERIVED)の場合は、実際に実行しないと以降の見積もりができないので正確な値が出される
フェッチ行が全て結果行として返却されるわけではない。
- フェッチされた行からWHERE句などで絞り込みが行われる場合もある
JOINする場合は、各クエリのrowsの値の積がフェッチされる行の見積もりになる

filtered

テーブル条件によってフィルタ処理されるテーブル行の推定の割合
typeがRANGE, index, rage, index_mergeの時、利用されるようだがよく分かっていない

Extra

上記カラム以外の情報が出力される
その他っぽいけどとても大事

Using index
- カバリングインデックスが使用されているので、テーブルアクセスが発生していない
Using where
- テーブルから行をフェッチした後に絞り込みが行われている
- フェッチ時点で行数制限したいので、WHERE句でインデックスを適切に利用する等何らかの改良の余地があると思われる
Using temporary
- GROUP BY や ORDER BY でソートするために一時テーブルを使用することを意味する
Using filesort
- インデックスでのソートではなく、ソートアルゴリズムが使われている
Range checked for each record (index map: N)
- JOINにおいてrangeまたはindex_mergeが利用される

参考

2018-06-16

アルゴリズムの計算量について

1年ちょい前に基本的なソートと探索は研修で実装したんだけど(written in Perl)
その頃はなんだか分からないまま実装してた感あったので計算量もちゃんと意識やってみる
それにあたって計算量の基本の確認

計算量の評価

計算量の評価は時間計算量と領域計算量の2つから成る

時間計算量
- プログラムの実行に必要な時間の評価
  - CPUをどれだけ使うか
領域計算量
- プログラムの実行に必要な記憶領域の評価
  - メモリをどれだけ使うか

時間計算量が問題になることが多いので、計算量といわれるとき大抵は時間計算量

O表記法

オーダ表記法
O(n)やO(n²)のように表記する
nを入力値とし、nを用いた()内の式に計算量が比例することを表す
- 例1) O(n)の場合
  - 入力値nが5倍になれば計算量も5倍になる
- 例2) O(n²)の場合
  - 入力値nが5倍になれば計算量は(5²=)25倍になる

参考

プログラミングコンテスト攻略のためのアルゴリズムとデータ構造
特集！知らないと損をする計算量の話は、アルゴリズムとデータ構造を学ぶモチベーションを高めてくれた

2018-06-15

VPCのメインルートテーブルに関してすごい勘違いをしていた

背景

AWSソリューションアーキテクトの勉強も兼ねてVPCの勉強をしていて、どーしても分からないなぁとなっていた。
VPC自体にルートテーブルを設定して何に使うんだ?と。
サブネットに設定されてあるルートテーブルでええやん?と。

結論

VPCに設定したルートテーブルはメインルートテーブルと呼ばれ、ルートテーブルに明示的に関連付けられていない全てのサブネットの全てのルーティングを制御する。
- つまり、VPCに設定されているわけではない!!

備考

上記の通り、明示的に設定していない場合、サブネットのルートテーブルはメインルートテーブルになるのです。
この状態でVPCのメインルートテーブルを変更してしまうと、明示的にルートテーブルが設定されていないサブネットのルートテーブルも変わってしまう!
パブリックサブネットがプライベートサブネットに、またはその逆が起こってしまう可能性があるのだ。