Project

General

Profile

MySQL 5.7 SQLチューニング

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

DBチューニング(全体最適)
SQLチューニング(個別最適)

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

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するのが基本
 3テーブル以上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公開されてるので見てね
優秀なエンジニアはいるがメインに注力したいのでエンタープライズ
30日間お試しってのもあるので、エンタープライズモニター試すのもあり
ベストプラクティスアドバイザーという機能もある
innodb_flush_log_at_trx_commitの設定ができる(スレーブに設定すると有効)
とかこういうの教えてくれる
MySQL Query Analyzerはスロークエリログより便利
バッチの前のときだけソートバッファ広げて実行するとか有効
sysschemaでUnused indexとか見れる
sys.schema_table_statics_with_bufferでinnodbのメモリの中身見れる
チューニングに力さきたくないって人はコンサルティングサポート
ソースコードレベルでサポート可能
MySQLサポートの特徴
 物理サーバー単位課金
 クラウドは相談してください
Oracle GoldenGateでMySQLにレプリケーション
セキュリティ・コンプライアンス対策にもなる

Added by aretan 2016-05-30 ago

mysql.png View (14.6 KB) 2018-03-09