UNIONとCase式による集計の違い

やりたいこと

UNIONとCase式それぞれで集計した時に実行計画にどのような違いが生まれるのかをMySQLで検証する
下記のようにダミーデータを作成して、都道府県別・男女別での合計人口を集計する
なお、この検証はSQL実践入門（第3章）を参考にしている

ダミーデータ作成

Create table If Not Exists Populations (id INT PRIMARY KEY AUTO_INCREMENT, prefecture varchar(255),sex INT, population INT);
ALTER TABLE Populations ADD CONSTRAINT unique_prefecture_sex UNIQUE (prefecture, sex);

INSERT INTO Populations (prefecture, sex, population) VALUES
('東京', 1, 6500000),
('東京', 2, 6800000),
('埼玉', 1, 3600000),
('埼玉', 2, 3700000),
('千葉県', 1, 3100000),
('千葉県', 2, 3200000),
('神奈川', 1, 4500000),
('神奈川', 2, 4600000);

データ確認

SELECT * FROM `Populations`

masatotezuka

UNIONによる集計

SELECT
    prefecture,
    SUM(temp.pop_men) as pop_men,
    SUM(temp.pop_women) as pop_women
FROM (
        SELECT
            prefecture, population as pop_men, NULL as pop_women
        FROM `Populations`
        WHERE
            sex = 1
        UNION
        SELECT
            prefecture, NULL as pop_men, population as pop_women
        FROM `Populations`
        WHERE
            sex = 2
    ) temp
GROUP BY
    prefecture
ORDER BY prefecture

集計結果

実行計画

フルスキャンが２回実行されている

masatotezuka

Case式による集計

SELECT
    prefecture,
    SUM(
        CASE
            WHEN sex = 1 THEN population
            ELSE 0
        END
    ) as pop_men,
    SUM(
        CASE
            WHEN sex = 2 THEN population
            ELSE 0
        END
    ) as pop_women
FROM `Populations`
GROUP BY
    prefecture
ORDER BY prefecture

実行計画

フルインデックススキャンが1回実行されている
UNIONによる集計と比較して、I/Oコストが1/2になっており、Case式による集計のほうがパフォーマンスが高いということがわかる

masatotezuka

I/Oコストの観点から、基本的にUNIONを使用しないほうがパフォーマンスが高いが、UNIONを使ったほうがインデックスを効かせることが可能な場合もある