駆け足で振り返れ SQLセミナー（チューニング基礎編）

はじめに

去る2020年1月10日、オラクル本社で開催されたMySQL 8.0入門セミナー ～チューニング基礎編、SQLチューニング編～に参加してきました。

今回は、セミナーの概要に沿って、特に強調されていたなと感じた点を掻いつまんでいこうかと思います。

なお、セミナーの資料は下記ページからダウンロードすることが可能です。
(*2020/1時点。要Oracleアカウント登録)

■MySQL 8.0入門セミナー講演資料 (チューニング基礎編、SQLチューニング編)

パフォーマンスチューニングの目的

限られたリソースの中で、最大限のパフォーマンスを出すこと。

パフォーマンスの指標

パフォーマンス指標には以下のものがある。

• スループット(単位時間あたりの処理能力)
• レスポンスタイム(処理を実行してから結果が返ってくるまでの時間)
• スケーラビリティ
• 上記の組み合わせ

レスポンスタイムはキューイングによる遅延+実行時間で構成されるため、パフォーマンスの改善時にはキューイングによる遅延か実行時間のどちらかを改善する必要がある。

なお、システムが飽和状態に近付くとキューイングによる遅延が急激に増大する。(以下の図はセミナー資料より引用)

電話(リクエスト)に対応できるオペレーターが足りなくなり、オペレーターが空くまで待ちが発生する…ようなイメージ。

パフォーマンス箇所を見極めるには、実行時間を計測し、ボトルネックを見極めることが重要。

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

• DBチューニング(全体最適)
  サーバー(特にパラメータ)をチューニングする。
  主にスループット向上につながる。

• SQLチューニング(個別最適)
  個別のSQLのチューニング。
  主にレスポンスタイム向上につながる。

DBチューニング

DBのパラメータを調節してチューニングを行う。
主にデータベース管理者(DBA)が行う領域。

ステータス変数などの情報から稼働状況を確認し、システム変数の設定値が適切か判断・調整する。

サーバー設定の確認

システム変数・ステータス変数の確認

システム変数はオプションファイルで確認・設定ができる。
UNIX: my.cnf
Windows: my.ini

MySQL Serverの動作を監視するためにステータス変数を確認する。
特定のクエリについて調査する場合、

mysql>FLUSH STATUS; <クエリ実行>;
SHOW STATUS;

コマンドラインではなく、GUIでシステム変数やステータス変数を確認したい場合は、MySQL Workbenchを使用すると確認できる。
性能分析のためのsysスキーマがMySQL5.7からデフォルトで実装されている。

MySQL Serverチューニングの流れ

サーバーのコネクション&スレッド

設定ファイル my.cnf

• max_connections(151) ¹
  許容可能なコネクション数を設定する。メモリを消費しきるので、実際のコネクション数に対して多すぎる値にはしないこと。

• thered_cache_size(9)*
  スレッドをコネクションの切断後にもキャッシュする。
  キャッシュすると、クエリが連続する際にクエリ間の同じ処理を使いまわせる。一般的にはmax_connections/3程度。

コネクションスレッド毎のバッファ

• sort_buffer_size(256KB)

ソート用メモリサイズ。メモリ上で収まらなくなったらディスク(ファイル)を使用する。

SHOW STATUSのsort_merge_passesで、ファイルを利用したマージソートのパス数が確認できる。ソートがメモリ上だけでおさまらない場合には要確認。

InnoDB

Storage Enginesについては、基本的にInnoDBのことを考えていればいいらしい。

• innodb_buffer_pool_size(128MB)²

デフォルトだとサイズは小さくなっている。MySQL & InnoDBのみを利用している場合、メインメモリの80%程度を割り当てると良い。

• innodb_log_file_size(48MB)

これもデフォルトだとサイズが小さい。innodb_buffer_pool_sizeの25%~100%程度にすると良い。

• innodb_fike_per_size(ON)

テーブル欄にでOS上のファイルを分ける設定。ONを推奨(デフォルトでもON)。

スキーマのデザイン

はじめに細かい設定を考慮しておくことが大事。

• 正規化/非正規化の検討
• CharじゃなくてVarchar
• INTが使えるところはINT
• NOT NULLを宣言する
• インデックスを貼るときは頭から何文字か(プレフィックス)で貼れないか検討する

SQLチューニング

個別の処理のチューニング。
SQLチューニングによって、数十倍に性能が向上することは珍しくない。
チューニングの流れは以下の通り。

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

1. 問題となるSQLの特定

スロークエリログ

指定した実行時間以上のクエリを出力する。
システム変数slow_query_logを設定すると出力される。

• long_query_time
  出力する実行時間のしきい値。細かすぎると出力されすぎるので、どのくらいのしきい値にするかは都度決定する。

• log_queryes_not_usiong_indexes
  インデックスを貼っていないクエリを出力する。

• mysqldumpslow
  スロークエリログのサマリを出せるツール。ログの出力が多いときにチューニングすべきクエリを特定するのに便利。

[使用例]

mysqldumpslow -s at <スロークエリログファイル名>

show full Processlist

　現在実行にかかっているクエリを特定可能。実行した時点のスナップショットが表示される。

パフォーマンススキーマのThreadsテーブル

　show full Processlist+αの情報が確認できる。また、show full Processlistよりもオーバーヘッドが少ない。

2. 実行計画やSQL実行時の稼働統計などの確認

SQLの実行計画はオプティマイザ³が作成している。
Explainコマンドでオプティマイザが作成した計画を確認できる。

Explain <実行計画を見たいSQL文>;

Explainチェックポイント

Explainを実行すると、下記の項目が出力される。

項目名	説明
ID	クエリのID(テーブルのIDではない)
Select_type	クエリの種類
table	対象のテーブル
partitions	対象のパーティション(パーティションテーブルではない場合NULL)
type	レコードアクセスタイプ
Possible_keys	利用可能なインデックス
key	選択されたインデックス
key_len	選択されたインデックスの長さ
ref	インデックスと比較される列
rows	行数の概算見積もり
filtered	フィルタリングされる行の割合
Extra	追加情報

見るべきポイント(ざっくり)

rows
実際に取り出される行数。これが少ないほど実行時間は短いということになる。

Type
ここの値はかなり重要。

以下の値の場合は優秀。

• const 　結果が1行になるケース
• eq_ref　JOINの場合の最適な結合型

次の値は高速ではないが、クエリによっては仕方がない場合も。

• uniq_subquery　最も遅いSELECTタイプだが、サブクエリの中では最速。
• range　BETWEENや不等号、INを使うと出現。

以下の場合はチューニングを検討した方が良い。

• index　フルインデックススキャン。大抵refまたはeq_refに改善できる。(ただし、ORDER BY+LIMITが入っている場合は出てきても良い、また行数が少ない場合は問題ない。)
• ALL　テーブルフルスキャン。全行を処理する場合以外は避けるべき。

Select_type

[DEPENDENT UNION]および、[DEPENDENT SUBQUERY]は可能な限り避ける。

Extra

• Using index これが出力される場合は理想的。

以下は可能な限り避けた方が良い。

• Using filesort 内部で行をソート処理する。JOINの中で出てきたら非常に注意。
• Using temporary テンポラリテーブルを使用する。遅くなる傾向あり。Unionとか使ってないのに出たら要注意。
• Using where 取り出した行をさらにWhere条件で絞り込む。

なお、MySQL Workbench にはVisual Explainという機能があり、実行計画をフローチャートで確認できる。(画像はMySQLのサイト⁴より。)

3.チューニングの実施

インデックスの活用

インデックスが使われていないクエリはインデックスを使って処理できないか考える。
(ただし、大量データにアクセスする場合⁵はインデックスを使わない方が高速になる。)

インデックスは付けすぎないこと。参照時の性能はあがるが、更新時にはオーバーヘッドになる。
また、カーディナリティ⁶の低いデータにインデックスを付けても役立たない場合が多い。

複数テーブルのJOIN

• JOINの順番と各テーブルに対するアクセスパスが重要
• 小さなテーブルからJOINする。2テーブルでもどちらからアクセスするかで効率は変わる。
• WHERE句での絞込みもどのテーブルに対して実施するのが効率的か、考えて実施する。
• インデックス列はそのまま参照しないと使えない。計算式(where a*100>=90など)は使わないこと。

サブクエリについて

サブクエリが遅かったのは昔の話。MySQL5.6から最適化が強化されている。

オプティマイザの制御

SQLが狙った計画通りにならないとき、オプティマイザの判断を制御することで、実行計画が変化することがある。

インデックスヒント

特定のインデックスを使用するようにオプティマイザに促す。強制力によって3種類ある。FOR句を使ってスコープを指定することもできる。(JOINのため、ORDER BYのため、など)

• USE INDEX 特定のインデックスを使用するように指示を出す
• FORCE INDEX USE INDEXに加え、「テーブルスキャンを選択しない」指示を出す
• IGNORE INDEX 特定のインデックスを使用しないように指示を出す(ここまですることは少ない)

[使用例]

mysql> EXPLAIN
 SELECT *
  FROM world.Country USE INDEX(Cont)
  WHERE Continent='Africa' OR Continent='Asia';

Straght JOINヒント

JOINの順番を指定できる。指定すると、左側のテーブルが右側のテーブルより先に読み取られる。
OUTER JOINの時は使えない。

mysql> EXPLAIN
SELECT CountryLanguage.Language,Country.Name,COUNT(*)
FROM City
    STRAIGHT_JOIN Country
      ON City.CountryCode=Country.Code
    STRAIGHT_JOIN CountryLanguage
      ON City.CountryCode=CountryLanguage.CountryCode
 GROUP BY CountryLanguage.Language,Country.Name
 ORDER BY CountryLanguage.Language;

おわりに

軽く書くつもりが、メモをまとめるだけで、なかなかボリューミーになりました。
書ききれなかった部分に関しては、資料を参照いただくか、実際にセミナー参加していただくのがいいのかなと思います。(Oracleの回し者ではないですが。。。)

基礎だけでもチューニングで考慮できる点はたくさんあることを学びました。
クエリを書く機会はまだまだ多くないですが、たくさんの選択肢を知っておくことで、今後に活かしていけるのではと思いました。

1. カッコ()内はデフォルト値。()*は自動計算でデフォルト値が決まる。 ↩

2. こちらもカッコ()内はデフォルト値。 ↩

3. オプティマイザ(Optimizer)は、SQLの実行計画を作成している。計画はコストに基づいており、最もコストが低いと思われるものを作成する。オプティマイザの判断が必ずしも最適だとは限らない。 ↩

4. MySQL Workbench: パフォーマンス
   https://www.mysql.com/jp/products/workbench/performance/ ↩

5. 表データの全件を取得する場合など ↩

6. カーディナリティ：値が取りうるバリエーションの度合い。たとえば値が「YES」か「NO」2通りしかない場合はカーディナリティは低い。「顧客番号」など様々な値を取る場合はカーディナリティが高いと言える。 ↩