【SQL高速化】遅いクエリを早くした話

たった１回のDBアクセスに0.1秒くらいかかるクエリがあったので調査した

テーブル定義はこんな感じ

緯度経度が格納された位置テーブルと、エリア名などが格納されたエリアテーブル

そして、この２つのテーブルの中間テーブルがあり、これらを結合して検索を行う

クエリは以下

select C.COORDINATE_ID as COORDINATE_ID,
           A.AREA_ID         as AREA_ID,
           A.AREA_TYPE     as AREA_TYPE,
           A.NAME     as NAME
from COORDINATE C
       inner join AREA_COORDINATES AC on AC.COORDINATE_ID = C.COORDINATE_ID
       inner join AREA A on A.AREA_ID = AC.AREA_ID
  and C.LAT_LNG = #{coordinate}

COORDINATEテーブルを駆動テーブルにして、AREA_COORDINATESテーブルとAREAテーブルを結合して検索している

調査時はよくあるクエリで違和感がなかったが、このクエリの実行プランをみると、AREAテーブルのフルスキャンがかかっていることがわかった

そして、このフルスキャンが実行時間の中で支配的であるので、フルスキャンをやめるようにしたら速くなりそう

内部結合を減らす

AREAテーブルを駆動テーブルにしてみたりと、いろいろ組み換えてみたが特に変わらず、結果的に内部結合を減らすのが効果があった

もともとのSQLでは内部結合を２回行っていたが、これを１回にしてみた

select C.COORDINATE_ID as COORDINATE_ID,
           A.AREA_ID         as AREA_ID,
           A.AREA_TYPE     as AREA_TYPE,
           A.NAME     as NAME
from AREA A
       inner join AREA_COORDINATES AC on A.AREA_ID = AC.AREA_ID
           and AC.COORDINATE_ID = (
               select COORDINATE_ID
               from COORDINATE
              where COORDINATE.LAT_LNG = #{coordinate})

こうすることによりフルスキャンがなくなり、結果0.0001秒で1000倍速くなった