aretan.jp

チューニングのアプローチ¶

DBチューニング（全体最適）
SQLチューニング（個別最適）

１問題となるSQLの特定
２実行計画や稼働統計の確認
３SQLチューニング実施

SQLチューニングの基本¶

スロークエリログ
　log-slow-queries
　long-query-time (0.5 -> 500ms)
　log-queries-not-using-indexes
　log-output (テーブルに出力できる)
SHOW FULL PROCESSLIST
mysqldumpslow
　スロークエリログの集計ができる
　サマれるのでとても便利
MySQL Workbench
　ビジュアルExplainが使える
パフォーマンススキーマ
　sysスキーマ
　　5.7からデフォルト作成
　　パフォーマンススキーマ情報量が大きい事より
　　statement_analysis：SQL毎の集計ビュー
　　innodb_lock_waits：ロックしているトランザクションを特定
　累積でたまり続けるので、注意してください。truncateして。
　　CALL sys.ps_truncate_all_tables(FALSE);
　MySQL Query Analyzer（EE版で提供）はサイコー！
　　スロークエリログと比べてサイコー！
　　MySQL Enterprise Monitorの一部

実行計画（Query Execution Plan）¶

実行計画はオプティマイザが作成する
実行計画はExplainで確認可能
コストベースのオプティマイザを採用している
　コストに基づいて、最適な実行計画を作成する
　オプティマイザがより良い実行計画を作成できるようにチューニングする
　　統計情報（コスト計算する時に参照するテーブル）
　　　行数、ページ数、カーディナリティ
　　　テーブル内のデータが10%更新で再計算
EXPLAIN
　アクセス方法、インデックス、フェッチする行数、ファイルソート
　5.7新：FOR CONNECTION connection_idで実行中のクエリに実行できる
　SHOW WARNINGSでオプティマイザが書き換えたSQLが確認できる
　使用例　細かいのはスライド43参照
　　possible_keys　使える可能性のあるインデックス
　　type all　スキャンが走ってる
　　Using where　とってきたデータからwhereしてる
　DEPENDENTやUNCACHABLEとかは、なんども実行される可能性ある
　望ましい：system > const > eq_ref > ref > ... > range > index > ALL
　Using filesort, temporaryとかを解決してこ
　FORMAT=JSONすると、実はTEXTより情報が多い
　WorkbenchだとVisualEXPLAIN機能で良い感じにみれる！
SHOW STATUS
　FLUSH STATUS; query; SHOW STATUS;
　Created_tmp_disk_tablesがあったら一時テーブルの領域広げよ
　　tmp_table_size, max_heap_table_size　両方上げる
　やっぱりWorkbenchだとええかんじに見れる（Quary Stats）

チューニングの実施¶

インデックスの活用
　少量のデータを高速に取り出せる
　大量データにアクセスする場合は使わないほうが高速（全件取得）
　UPDATEでインデックスが使えてないとテーブルロックしちゃう
複数テーブルのJOIN
　小さなテーブルから順番にJOINするのが基本
　３テーブル以上JOINする時は小さい結果のものから
　MySQLはネステッドループジョインしかない
サブクエリに関する補足
　5.6から最適化が強化されている
　サブクエリをJOINに書き換えなくてもパフォーマンスは良い
インデックスの考慮事項
　インデックス付けすぎない
　　key(a, b)があるならkey(a)は削除
　　カーディナリティが低い列にインデックス付けない
　サイズの小さなインデックスを活用する
　　プレフィックス　index(name(8))
　MySQLはインデックス内で順序が先の列のみ利用可能
　　key(a, b)はwhere b=5はインデックス使わない
　　複合インデックスの定義の順番は意味がある
　インデックスはそのまま参照しないとインデックス使えない
　　where a*100>=90じゃダメ、wherea=>0.9
　covering indexesは高速（インデックスのみの検索でいける）
　5.7新：Generated Columnsで関数インデックスを作成可能
　OPTIMIZE TABLEでインデックス再構築できる
　5.0以降なら複数のインデックスを利用できる（古い話に踊らされるな）
オプティマイザヒント
　インデックスヒント
　　USE INDEX：これを使ってほしい
　　FORCE INDEX：テーブルスキャンは絶対しない
　　IGNORE INDEX：特定のインデックスを使用しない
　STRAIGHT_JOINヒント
　　結合の順番を指定できる
　　駆動表が内部表より小さいほうが効率的
　　結果セットが小さいほうが効率的
　5.7新：オプティマイザヒント
　　SELECT /*+ HINT1(args) HINT2(args) */ ... FROM ...
　　optimizer_switchよりも細かい粒度で指定できる
　　MAX_EXECUTION_TIMEとかも指定できる
　　既存ヒントのリプレース（統一）
　商用版だとスキーマ設計のレビューとかチューニングもサポート対象

オプティマイザ詳細解説¶

5.6以降　統計情報永続化
　サーバー再起動しても統計情報は消えない
　サンプリングページ数の拡張
5.7新：コストを変更できる
インデックスが使えない例
　LIKEの後方一致
　関数を使用している
　複合インデックスの最初の列が使用されていない
　データ型が一致していない
　キャラクタセット、コラーションが一致していない
5.6以降：Index Condition Pushdown
　条件付きでストレージから取ってこれる
　idx(a,b,c) where a=100 and c=100の時にcの条件付きでストレージに行く
5.7新：INに複数列を指定した場合の最適化
　where (a, b) in ((1, 1), (2, 2));
5.7新：クエリーリライトプラグイン
パーティショニングは見ておいて

スローログまずはみるとか
5.7からstatement_analysisみりゃいい
events_sjtatements_historyとかも便利

filesortって書いててもtmp_tableだけで実行できてりゃ遅くないよね
diskに変換されてるのがあかんやーつー
イベントっていうのをみるととてもいいかんじ

5.7
グループレプリケーション
全文検索CJK対応
JSONオプション

Facebookがエンタープライズ版
サーバーはフリーとエンタープライズ一緒
サポートとか付加価値が強いってこと
事例紹介：スクエニ・ソニー　PDF公開されてるので見てね
優秀なエンジニアはいるがメインに注力したいのでエンタープライズ
３０日間お試しってのもあるので、エンタープライズモニター試すのもあり
ベストプラクティスアドバイザーという機能もある
innodb_flush_log_at_trx_commitの設定ができる（スレーブに設定すると有効）
とかこういうの教えてくれる
MySQL Query Analyzerはスロークエリログより便利
バッチの前のときだけソートバッファ広げて実行するとか有効
sysschemaでUnused indexとか見れる
sys.schema_table_statics_with_bufferでinnodbのメモリの中身見れる
チューニングに力さきたくないって人はコンサルティングサポート
ソースコードレベルでサポート可能
MySQLサポートの特徴
　物理サーバー単位課金
　クラウドは相談してください
Oracle GoldenGateでMySQLにレプリケーション
セキュリティ・コンプライアンス対策にもなる