🏃

SQLチューニングとは宣言型言語でリファクタリングをするということ

もりた

2024/02/29に公開

SQL

クエリチューニング

tech

これはなに

ども、レバテック開発部のもりたです。

タイトルの通りなんですが、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が改善可能なのかを判断します。