ChatGPTにSQLチューニングさせてみた

ChatpGPT（モデルはGPT-4を利用）にシンプルなSELECT文とテーブル・インデックス定義を与えてSQLチューニングの案出しをしてもらいました。

ちなみに、プロンプトやChain of Thought などの工夫は一切せず、シンプルに質問をぶつけています。

以下、注意事項。

• 実務利用と比べるとシンプルすぎるのでお遊びの範囲を超えていません。
• どのチューニング案が適切かは多くの要素（例えば以下）が関わってくるので、一概に判断できず実際に測定を行い確認する必要があります。
  • データ量やその分布
  • ハードウェアやRDBMSの種類・バージョンなどの環境
  • 性能要件（何秒以内のレスポンスが必要か、同時実行数はいくつかなど）

ChatGPTへの質問とその回答

1. 単純なインデックスが不足しているケース

質問

以下のSQL文の性能を改善するにはどうしたらよいでしょうか。

select
	customer_id,
	customer_name,
	address_1,
	address_2
from
	customers
where
	telephone_number = '0123-45-6789';

テーブル・インデックスの情報は以下です。

create table customers (
	customer_id integer primary key,
	customer_name varchar(100),
	address_1 varchar(100),
	address_2 varchar(100),
	telephone_number varchar(20)
);

回答

感想

これぐらいは流石にできますね。2. と3. は蛇足な気もしますが。

2. 複合インデックスが不足しているケース

質問

以下のSQL文の性能を改善するにはどうしたらよいでしょうか。

select
	item_id,
	item_name
from
	items
where
	supplier_id = '123'
and
	item_category_id = 456;

テーブル・インデックスの情報は以下です。

create table items (
	item_id integer primary key,
	item_name varchar(100),
	item_category_id integer,
	supplier_id varchar(10)
);

回答

感想

インデックスの提案に関しては、個別のインデックスを提案した後に、該当クエリに最適化された複合インデックスを提案できていますね。もちろん、supplier_id と item_category_id のカーディナリティーによっては、単独インデックスでも問題がない（その方が好ましい）ケースもあります。

次善策の 2. に関しては、サブクエリや相関サブクエリは関係ないと思うんですけどね。

3. カラム値に関数を適用しインデックスが利用できないケース

質問

以下のSQL文の性能を改善するにはどうしたらよいでしょうか。

select
	customer_id,
	customer_name
from
	customers
where
	to_char(birth_day, 'YYYY-MM') = '1980-01'

テーブル・インデックスの情報は以下です。

create table customers (
	customer_id integer primary key,
	customer_name varchar(100),
	birth_day date
);

create index idx_customers_01 on customers(birth_day);

回答

感想

カラム値 birth_day に関数 to_char を適用した結果で絞り込んでいるため索引が使えないケースです。SQL 文を書き直すという典型的な対策は回答できていますね。関数インデックスも提案してくれても良いかなと思いましたが、それは良し悪しですね。

次善策の 2. に関しては、customer_name もインデックスに含めていればカバリングインデックスになって効果もあるのでしょうが、そうなっていないのでイマイチですね。

4. 暗黙型変換によりインデックスが利用できないケース

質問

以下のSQL文の性能を改善するにはどうしたらよいでしょうか。

select
	item_id,
	item_name
from
	items
where
	supplier_id = 123;

テーブル・インデックスの情報は以下です。

create table items (
	item_id integer primary key,
	item_name varchar(100),
	item_category_id integer,
	supplier_id varchar(10)
);

create index idx_item_01 on items (supplier_id);

回答

感想

RDBMS の挙動に依存する部分でもあるのですが、カラム supplier が文字列型であるにも関わらず、where 句では数字リテラルを条件に渡しているため、インデックスが利用できないケースを想定しています。

SQL 文内の 123 を '123' に直してくれることを想定していましたが、回答ではそもそもテーブル定義の方を直せと言ってますね。影響範囲が大きい修正なのでできれば採用したくない選択肢ですが、そこまで考慮に入れることは流石に難しいですかね。

ちなみに、alter column 句を見て、RDBMS を指定することを忘れたなぁと気付きましたが、このままいきます。

5. インデックスのカラム順番が不適切なケース

質問

以下のSQL文の性能を改善するにはどうしたらよいでしょうか。

select
	s.item_id,
	s.sales_date,
	s.sales_qty
from
	sales s
where
	sales date between '2023-01-01' and '2023-01-31'
and
	s.customer_id = 123456;

テーブル・インデックスの情報は以下です。

create table sales (
	sales_id integer primary key,
	sales_date date,
	customer_id integer,
	item_id integer,
	sales_qty integer
);

create index idx_sales_01 on sales(sales_date, customer_id);

回答

感想

等価条件と範囲条件を組み合わせた検索の場合、等価条件に利用するカラムを先頭にしないとインデックスが効率的に使えないケースを問うています。問題なくカラムの順番は考慮に入れられているようですね。単純にカーディナリティーから判断しただけかもしれませんが。

後、カバリングインデックスを提案すること多くない？

6. ジョイン条件に関するインデックス不足のケース

質問

以下のSQL文の性能を改善するにはどうしたらよいでしょうか。

select
	i.item_id,
	s.sales_date,
	s.sales_qty
from
	sales s
inner join
	items i on (s.item_id = i.item_id)
where
	i.item_category_id = 123;

テーブル・インデックスの情報は以下です。

create table sales (
	sales_id integer primary key,
	sales_date date,
	item_id integer,
	sales_qty integer
);

create table items (
	item_id integer primary key,
	item_name varchar(100),
	item_category_id integer
);

create index idx_items_01 on items(item_category_id);

回答

感想

ジョインを効率的に行うインデックスの提案も問題なくできてます。

ちなみに、3. は普通の RDBMS ならあまり効果はないでしょうね（これぐらいの最適化は内部で勝手にやってくれる）。

7. ジョインの順番を意識したインデックスが不足しているケース

質問

以下のSQL文の性能を改善するにはどうしたらよいでしょうか。

select
	s.sales_id,
	c.customer_name,
	i.item_name,
	s.sales_qty
from
	sales s
inner join
	customers c on (c.customer_id = s.customer_id)
inner join
	items i on (i.item_id = s.item_id)
where
	c.prefecture_id = 14
and
	i.item_name = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

テーブル・インデックスの情報は以下です。

create table sales (
	sales_id integer primary key,
	sales_date date,
	customer_id integer,
	item_id integer,
	sales_qty integer
);

create index idx_sales_01 on sales(customer_id);
create index idx_sales_02 on sales(item_id);

create table customers (
	customer_id integer primary key,
	customer_name varchar(100),
	prefecture_id integer
);

create table items (
	item_id integer primary key,
	item_name varchar(100),
	item_category_id integer
);

回答

感想

このケースは、質問で十分に情報を与えられてないのですが、

• c.prefecture_id = 14 は都道府県による絞り込みであり、都道府県はカーディナリティーが 47 以下とあまり高くない。（14 は ISO 都道府県コードで神奈川県を指すので更に絞り込めない）
• 一方、i.item_name = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' は（それなりに商品があれば）カーディナリティーが高いと考えられる。（特定の商品だけ大人気なケースもあるので一概には言えませんが）

を考慮すると、items ⇒ sales ⇒ customers と結合することが好ましいと考えられ、該当の SQL 文だけを考えるならインデックスとしては items.item_nameだけでも十分なんですが、ChatGPT は customers.prefecture_id にもインデックスを作成することを提案していますね。

インデックス付与は更新処理には足かせになるので最小限にしたいところですが、こればかりは難しそうですね。追加でカーディナリティーの情報やインデックス作成を最小限にしたいという情報をインプットすれば誘導はできそうですが、その誘導ができるだけの知識がユーザーにあるなら ChatGPT 不要なのではとなりますし。

あと、ChatGPT はカバリングインデックス好き過ぎだろという気もするのですが、2. で提案されているインデックスはカバリングになってないですね。

8. 先頭N件の絞り込みにインデックスが最適でないケース

質問

以下のSQL文の性能を改善するにはどうしたらよいでしょうか。

select
	s.item_id,
	s.sales_date,
	s.sales_qty
from
	sales s
where
	s.customer_id = 123456;
order by
	s.sales_date desc
fetch first 50 rows only;

テーブル・インデックスの情報は以下です。

create table sales (
	sales_id integer primary key,
	sales_date date,
	customer_id integer,
	item_id integer,
	sales_qty integer
);

create index idx_sales_01 on sales(customer_id);

回答

感想

EC 系サイトでよく問題になる販売履歴を参照するクエリを想定しています。販売履歴は利用歴が長いユーザーだと大量になり、かつクラスタリングファクターが低くなりやすいので、先頭 N 件の絞り込みに関してはソート対象のカラムをインデックスに含めておくことが常套手段です。ちゃんとそれを提案できていますね。

なぜここまで好んで提案していたカバリングインデックスをここで提案してないのかは興味深いですが。

まとめ

今回質問した SQL チューニングは非常にシンプルなものですが、ほとんど妥当な回答ができていますね（次善策には怪しいものも含まれていますが）。

あとは、データ量や分布、どういう対策が取りえるかなどを適切に渡せれば更に回答をよくできるのではと思いますが。ただ、それはユーザー側にとってはハードルが高いので、逆にそういう情報をくれと ChatGPT を誘導できるプロンプトを作ることができれば面白いかもしれません。

あと、ChatGPT カバリングインデックス好き過ぎだろ。