MySQLチューニング #MySQL

はじめに

前回（Linux基礎）に続き、会社の課題図書を毎週1冊のペースで読んで、何かしらアウトプットするという活動を個人的に勝手にやっています。（上司との 1on1 の中で宣言しました）

今回は、現場で使える MySQL を読んで、すべてをまとめるのは非効率なので、「パフォーマンスチューニング」についてのみ取り上げたいなと思います。

この本自体は、MySQL初心者向きで、MySQLの概要を把握するには最適な書籍です。
※ただ出版されたのが、2008年でMySQL 5.0についてまでしかの記載しかないので、そのあたりはを踏まえてもアーキテクチャやレプリケーションの仕組みなどバージョンによってそれほど大きな変更やブレのない、概要のテーマのみを抑えるのがベストだと思います。

単体チューニングとシステムチューニング

チューニングは、「単体チューニング」と「システムチューニング」に大別できる
単体チューニング
- 個々のSQL文の実行性能を改善（インデックス作成、SQL文修正など）
- 目標：①SQL発行回数の最小化 ②論理I/Oの最小化
- ツール：mysqldumpslow,EXPLAIN,mysql_explain_log
システムチューニング
- MySQLの初期化パラメータやOSのカーネルパラメータの設定による改善
- 目標：各種リソースの最小化、最適化
- ツール：SHOW STATUS,mytop,sarやiotop

単体チューニング

スロークエリ発見し特定して、そのクエリを解析して、改善させるのが大きな流れ

[特定] スロークエリログによる特定

スロークエリとは？
- 実行に一定時間以上かかったSQL文
- テーブルのフルスキャンを行ったSQL文
スロークエリログ出力設定方法
- log-slow-queries（スロークエリログに出力先指定）

 log-slow-queries=/hoge/hoeghoge/log/slow.log

long_query_time（設定以上のクエリをスロークエリログに出力する）

 long_query_time=3

log-queries-not-using-indexes（フルスキャンを実行したSQL文をスロークエリログに出力する）
- 一時的な負荷によって処理時間がかかるなど、スロークエリのすべてが悪いとは限らないため、log-queries-not-using-indexesによる合せ技がベスト
フルスキャンには、下記の２つある
- 全表検索（フルテーブルスキャン）

   select * from tables1;

全索引検索（フルインデックススキャン）

   select count(*) from tables1;

[解析] スロークエリログによる特定

mysqldumpshowによる解析
- mysqldumpshow：スロークエリログ内の同じSQL文を集計し、各SQL文を平均実行時間の長い順番に並べ替えてくれる
EXPLAINによる実行計画確認
- EXPLAINの使い方
  select文の前に"EXPLAIN"をつけるだけ

    mysql > EXPLAIN select count(*) from tables1;

EXPLAIN実行の結果、確認すべきポイントは、type,rows,keyの３つ。
- type：実行計画の種類。ALLだとインデックス使用されず。indexだとインデックスが使用されて検索されている。
- rows：読み込まれたレコード数。
- key：使用するインデックス。NULLの場合、使用されず。
INSERT / UPDATE / DELETE文については、ダミーのSELECT文に書き換えてEXPALINを実行するのがいい
mysql_expalin_logによる解析
ログ（スロークエリログではない）内からSELECT文とUPDATE文を抽出して、SELECT文に変換して、EXPLAINを実行して、要約して問題のSQL文を表示してくれる（めっちゃ便利！！！）

[改善]チューニング

FORCE INDEX句で主キー以外のインデックス指定

select count(*) from tables1;

などでテーブル全体の件数を取得場合は、

select count(*) from tables1 FORCE INDEX <主キー以外のインデックス>;

のほうが早い。

理由：レコードの中身（列値）を読み込む必要がないSQL文の場合、MySQLのクラスタインデックスの性質上、インデックスだけを読み込む全索引検索のほうが、テーブル全体を読み込む全表検索よりも早いため。
主キーインデックスの場合、主キーと一緒に列値も読み込んでしまう。
- MySQLのオプティマイザはあまりあてにならない？からFORCE INDEXを使う
- 文字コード変換しない
  文字コード変換の処理に時間がかかるので、クライアント側とサーバ側で文字コードを揃える

システムチューニング

クエリキャッシュ
- query_cach_size のパラメータをチューニングする
  例 query_cach_size=50M
- アプリケーション側で、SQL文の書き方を統一する。大文字、小文字を統一など。。。（でも今どき新規の開発では生のSQL文をアプリケーションに書いているところないですよね。。。）
クエリキャッシュヒット率
- 下記の方法で統計情報を取得して分析する。
  mysql > SHOW STATUS LIKE 'Qcache%';
- 計算式
  Qcache_hits / ( Qcache_hits + Qcache_inserts + Qcache_not_cached )
コネクションチューニング
- max_connection をチューニングして最適化する
- thread_cache_size をチューニングして切断下コネクションのキャッシュをチューニングする
mytopによる統計情報解析
- MySQL版topコマンドのイメージ
- SELECT / INSERT / UPDATE / DELETE 文の実行回数の比率、現在のセッション数などいろんな情報を表示

最後に

今回エントリーしてみて感じたこと

MySQLとOracle、PostgreSQLの３つについて比較し、体系的に知識として身につけたい（身につけるべき）と思いました。
- なので、番外編でエントリーしようと思います。
本読む --> 知識を整理する(Qiitaにエントリーして)　にとどまっているので、実際に実機を触ってみて "机上と実機をリンクさせる" 作業をした上で、Qiitaにエントリーしていくべきだと思いました。
- こっちは次回以降のエントリーで実践していこうと思います。