MySQL 5.7 初心者向けセミナー　～チューニング基礎編、SQLチューニング編～ 20170814

しょー（show） @surumegohan

JSON形式で出力させたEXPLAINをMySQL Workbenchに読み込ませるとビジュアルEXPLAINでわかりやすく見ることができる。 #mysql_jp

しょー（show） @surumegohan

MySQL WorkbenchのQuery StatisticsからSQLチューニング時に確認すべき基本的な情報が・・・クリックするだけで・・・確認できる！！ #mysql_jp

しょー（show） @surumegohan

オプティマイザトレース。オプティマイザが実施している最適化の過程をトレースできる。ここまでくると入門レベルではないですが。。 #mysql_jp

しょー（show） @surumegohan

SHOW STATUS。Created_tmp_disk_tables。一時テーブルをメモリではなくディスク上に作成した回数。 #mysql_jp

しょー（show） @surumegohan

SQLチューニングの基本。インデックスの活用。一番の基本。インデックスを使うことで大量のデータにアクセスする場合に使う。大量じゃない場合はインデックス使わない方がいい場合もある。 UPDATE文でINDEXが使えてない場合、更新対象以外の行もロックしてしまう。 #mysql_jp

しょー（show） @surumegohan

UPDATE文でINDEXが使われてないと（デフォルトでは）実質的にテーブルロックになってしまうので要注意。トランザクション分離レベルをREADCOMMITEDにすればUPDATEのロックの取り方を変更することもできなくはない。状況によりけり。アプリによります #mysql_jp

しょー（show） @surumegohan

トランザクション分離レベルをREADCOMMITEDに変更している場合は処理した行に対してのみロックを取得する。 という文章がいつのまにかマニュアルに追加されていたので一般的に知られていない。アピールしたい。 #mysql_jp

しょー（show） @surumegohan

複数テーブルのJOIN。JOINの順番は小さいテーブル（取り出す行数が少量のテーブル）から順番にJOINするのが基本。2テーブルのJOINでもどちらのテーブルにJOINするかでパフォーマンスが変わる。 #mysql_jp

しょー（show） @surumegohan

5.6からサブクエリのまま投げてもオプティマイザの強化によってJOINに書き換える必要性が低下しました。 #mysql_jp

しょー（show） @surumegohan

インデックスの考慮事項。更新時にオーバヘッドになるのでインデックスをつけすぎない。「SQLアンチパターンのインデックスショットガン」ですね。 #mysql_jp

しょー（show） @surumegohan

重複するようなインデックスは利用しない。 key(a,b)があるならKey(a)は削除。 カーディナリティが低い（取りうる値の種類が少ない）列にはインデックスをつけない。ただし性別で95％が男性で、残り5％の女性を引きたいためにIndexをつける場合はある。 #mysql_jp

しょー（show） @surumegohan

プレフィックスIndex（name(8))などサイズの小さなインデックスを活用する。 MySQLではインデックス内で順序が先の列のみ利用可能なので注意。 #mysql_jp

yoku0825 @yoku0825

@surumegohan varcharでプレフィックス切っちゃうとORDER BYが解決しきれないケースがあること（必ず解決しきれなくなる？ 後で試そ）に注意

yoku0825 @yoku0825

@surumegohan @yyamasaki1 使えなさそうですね（FORCEでも選ばない）

しょー（show） @surumegohan

ユニークなインデックスにはUNIQUE列にはUNIQUEキーワードをつけるとオプティマイザの判断に影響が与えられる。 BTREEインデックスはソートされた結果を返す。そのためインデックスを使ってデータにアクセスすることで内部的にソート処理を省略できる場合がある #mysql_jp

NaruTo@うなぎ食べない教 @KAZAMAI_NaruTo

B+ツリー警察がこっち見てない？ #mysql_jp

しょー（show） @surumegohan

select * from t b = 5 order by c の場合は Key(b,c)にするとよい。bとcの順番も重要！ #mysql_jp

しょー（show） @surumegohan

マルチカラムインデックスを活用する。 複数インデックスを使ってテーブルにアクセスする（MySQLは基本的に1テーブルに使うIndexは1つ） where a=1, and b=3のクエリが頻繁にあるならKey(a,b)でインデックスを作るとよい #mysql_jp

しょー（show） @surumegohan

coverring indexesはインデックスにアクセスするだけでクエリの結果を返せるので高速。 select c ftom t where b=5の場合はkey(b,c)となる。 #mysql_jp

しょー（show） @surumegohan

インデックス列はそのまま参照しないとインデックスが使えない where a*100 > 90 なら　where a>0.9とする。 5.7ならGenerated Columnsを利用することで関数インデックスを作成可能。 #mysql_jp

しょー（show） @surumegohan

OPTIMIZE TABLE...　でインデックスの再編成（最適化）が可能。 「MySQL5.0からインデックスマージが導入されているため、MySQLでは1つのクエリを実行する時、1つのテーブルにつき1つのインデックスしか使用できない」というのは4.1までの話 #mysql_jp

しょー（show） @surumegohan

オプティマイザの制御。 5.7からコストの調整をすることができます。（ただし使用事例は今のところあまり聞かない） ヒントを使うことでオプティマイザに指示をだせる！ #mysql_jp

しょー（show） @surumegohan

ヒントの種類。 まずインデックスヒント。インデックスを使う・使わない。 FOR句を使ってJOINのためOEDER BYのため、GROUP BYのために使うなどのスコープの指定ができる。 #mysql_jp

しょー（show） @surumegohan

インデックスヒント。 USE INDEX：オプティマイザに指示をだすだけ。 FORCE INDEX：テーブルスキャンの方がよかろうがインデックス使う。 #mysql_jp

しょー（show） @surumegohan

STRAIGHT_JOINヒント。 JOINの順番を指定できるヒント。 左側のテーブルが右側のテーブルより先に読み取られる。 OUTER JOINには使えない。 #mysql_jp