CHAR列への数値リテラル指定による全表走査と複合インデックスの列順
ある受注管理システムの受注テーブルは約500万行で、主キーはorder_idである。顧客IDはCHAR(10)型のcustomer_id列に格納され、B-treeインデックスが1つある。顧客数は約1万人で、1顧客あたり平均500件の受注がある。アプリケーションの検索画面では、WHERE customer_id = 12345 AND order_date >= '2024-04-01' のように、customer_idを数値リテラルで、order_dateを範囲条件で指定する。実行計画を確認すると、customer_idのインデックスが使われず、全表走査になっている。また、order_dateだけの条件では全体の約5%が該当する。DBMSはPostgreSQL系で、暗黙の型変換が発生している。customer_idには先頭に空白が入る古いデータが混在しており、アプリではtrimせずに検索している。更新頻度は1日約2万件、夜間バッチで数百件単位のINSERTがあり、その間は検索の応答時間がさらに2秒悪化する。現在の応答時間は平均8秒で、目標は1秒以内である。開発チームは、アプリ修正、複合インデックス追加、テーブル分割を検討している。
1. 全表走査になる原因を、型変換とインデックスの観点から説明せよ。また、アプリ側で最初に修正すべき点を述べよ。
Read the worked answer
customer_idはCHAR(10)型であるのに、検索条件で数値リテラル12345を渡しているため、DBMSが列側を数値に変換する暗黙の型変換を起こしている。列に関数や変換を適用すると、通常のB-treeインデックスは使えず、全表走査になる。最初に修正すべきは、アプリでcustomer_idを文字列リテラル'12345'としてバインドし、列側の型変換をなくすことである。併せて、先頭空白を含む古いデータはtrimして格納するか、検索時に正規化する。ただし、trim関数を列に適用すると再びインデックスが使えなくなるため、データクレンジングを優先する。
2. 複合インデックスを追加する場合、列順をどうするか。customer_idとorder_dateの選択率から理由を述べよ。
Read the worked answer
複合インデックスは(customer_id, order_date)の順とする。customer_idは等値条件で、1万人の顧客から1人に絞るため選択率が高く、平均500行に絞れる。order_dateは範囲条件で全体の5%（25万行）に該当する。B-treeでは、等値条件の列を先に置くと、その範囲内で次の列の範囲検索が効率的に働く。逆順の(order_date, customer_id)では、まず25万行を走査してからcustomer_idで絞ることになり、効果が小さい。また、検索列を両方含む複合インデックスは、カバリングインデックスとしても機能し得る。
3. アプリ修正、複合インデックス追加、テーブル分割の優先順位と効果を比較せよ。
Read the worked answer
優先順位は、①アプリ修正（文字列バインドとデータクレンジング）、②複合インデックス追加、③テーブル分割である。①は追加コストがほぼゼロで、型変換を除去するだけで既存のcustomer_idインデックスが使える可能性がある。②は500万行でも構築時間は比較的小さく、等値＋範囲の検索を大幅に改善する。③はデータ量が数億行や保持期間の要件がある場合に有効で、500万行では運用複雑性とバッチ影響の方が大きい。まず①と②を実施し、夜間バッチ中の応答を再測定してから③を検討する。