これはなに
ども、レバテック開発部のもりたです。
タイトルの通りなんですが、SQLチューニングってつまり宣言型言語でリファクタリングするってことだよね、というのを書きます。なお具体的なリファクタの方法とか実行計画の読み方とかはスコープ外です（別で書く予定）。
SQLチューニングはリファクタリング
まずSQLチューニングというと、パフォーマンス改善みたいな意味合いが強いと思います。ただ、SQLチューニングは本質的にはリファクタリング的な行為であるはずです。リファクタリングの定義を確認すると、
リファクタリングとは、ソフトウェアの外部の振る舞いを保ったままで、内部の構造を改善していく作業を指します。
引用元: 『リファクタリング 既存のコードを安全に改善する 第2版』
雑にいえば、結果を変えないでコードを改善することです。ここでいう改善とは内部品質を向上させることで、例えば保守容易性であったり、学習容易性であったりを指します。
さて、ここでひとつの転換を導入します。リファクタリングの定義を若干拡大し、内部品質に限らない非機能要件の改善を目的とするとしてみましょう。そのとき、SQLチューニングもクエリの結果を変えずにパフォーマンスの改善を狙っています。つまり結果を変えずにコードを書き換え、非機能要件を改善するという点でリファクタリングSQLチューニングはリファクタリングといえそうです。
SQLにおけるリファクタリングは本来、パフォーマンス改善に限らない非機能要件の改善を目指す行為だったはずですが、SQLがI/Oに近い層を担っているという特性だったり、宣言型言語であり処理の手段については定義しないため、SQLチューニングではもっぱらパフォーマンスの問題を扱うようになったのだと推測します。
SQL”リファクタリング”の変数
パフォーマンスの改善をするとなると、いじることのできる変数は大体決まってきます。SQLにおいては以下の通りです。
- アクセスの回数を変える
- 余分なアクセス回数を減らす。例えば不要なレコードを取得しないようにすることや、インデックスを追加して効率的なアクセスができるようにするなど
- アクセスの方法を変える
- こちらもインデックスを追加して、ディスクアクセスではなくメモリへのアクセスで事足りるようにするなど
さて、ここで問題になるのが、SQLが宣言型言語であるということです。
宣言型言語で定義できるのは、その処理を実行した結果だけです。しかしリファクタリングは、「結果」を変えずに「コード」だけを変更し、非機能要件の向上を図ることでした。わたしたちはSQLを通じて結果にしか口出しできないのに、どうやって手続きの問題に手出しすることができるのでしょうか？
そこで登場するのが実行計画です。実行計画を読むことで、どんな手続きで処理が行われているのかを知ることができます。わたしたちはその処理内容に直接手を加えることは（ほぼ）できないですが、その実行計画を作成しているオプティマイザに対して情報を渡すことで、実行計画を変え、SQLをリファクタできます。
実行計画の読み方と、オプティマイザへのヒントの出し方
実行計画の読み方
実行計画の解説については公式のページがあります。例えばMySQLだとこちら。ただ、こういう表示項目の解説ページだとあるあるですが、項目を眺めていても目が滑って結局どういうことだったのか分かんないな、みたいになりがちです。
そこでおすすめしたいのが奥野幹也さんのブログ「漢のコンピュータ道」に出てくる以下の分類です。
- id/select_type/tableフィールドを見て、どのテーブルがどの順序でアクセスされるのかを知る。これらはクエリの構造を示すフィールドであると言える。サブクエリが含まれている場合にはEXPLAINの表示順とアクセスされる順序が異なる場合があるので気をつける必要がある。
- type/key/ref/rowsフィールドを見て、各テーブルから行がどのようにフェッチされるのかを知る。どのテーブルへのアクセスが最も重いか（クエリの性能の足を引っ張っているのか）を、これらのフィールドから判断することが出来る。
- Extraフィールドを見て、オプティマイザがどのように判断して、各々のテーブルへのアクセスにおいて何を実行しているのかを知る。Extraフィールドはオプティマイザの挙動を示すものであり、クエリの全体像を把握するのに役立つ。
引用元: 漢のコンピュータ道
ここではEXPLAINで表示される項目を3つに分類しています。それぞれ説明すると、どのSELECTの情報なのかを示す名札情報、処理内容を示す情報、そしてEXTRAです。SQLチューニングではこれらの情報から、どのSELECTが改善可能なのかを判断します。
オプティマイザへのヒントの出し方
改善可能なのかSELECTが見つかったら、次は実行計画をつくるオプティマイザにヒントを出してよりよい処理に変えてもらいます。ヒントの出し方はだいたい以下の通りです。
- SQLを書き換える
- 不要なデータへのアクセスを回避する
- インデックスをはる、設定を変更する
- インデックスをはったり設定を変更することで、よりよいアクセス方法を選べるよう環境を整備してあげる
これらをすることで、オプティマイザはより効率的な処理を選べるようになります。これらのヒントを通じてわたしたちは実行計画に口出しすることが可能になります。
おわりに
この記事では宣言型言語特有のリファクタリング手順について解説しました。EXPLAINの読み方は別途記事を書く予定ですが、次に掲げる参考資料もありますので読んでみてください。
Discussion