MySQLでサブクエリで先に集計してクエリの実行を高速化

サブクエリを用いたクエリの最適化

巨大なテーブルをFull Table Scanして件数を集計する場合に、JOINの少ないサブクエリで先に集計して後から他のテーブルをJOINすると、クエリの実行を高速化できる場合があります。
特にJOINするテーブルが多い時には実行時間に大きな差が出ます。
もちろん、データやINDEXの張り方にも依存するで、クエリを書き換える前にEXPLAINで必ずRows_examinedなどが減ることを確認することは必須です。
この記事では、huge_rows_tableに大量の行があり、他のテーブルの行数はそれほどない場合を想定したクエリ例を示します。

想定するテーブル

CREATE TABLE `huge_rows_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `group_id_1` bigint(20) NOT NULL,
  `group_id_2` bigint(20) NOT NULL,
  `check_flg` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `IDX_group_id_1` (`group_id_1`),
  KEY `IDX_group_id_2` (`group_id_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

CREATE TABLE `group_table_1` (
  `id` bigint(20) NOT NULL,
  `name` varchar(500) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

CREATE TABLE `group_table_2` (
  `id` bigint(20) NOT NULL,
  `name` varchar(500) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

クエリ

# 元クエリ
SELECT
  gt1.name AS group_name_1,
  gt2.name AS group_name_2,
  COUNT(DISTINCT hrt.id) AS count
FROM huge_rows_table AS hrt
INNER JOIN group_table_1 AS gt1
   ON hrt.group_id_1 = gt1.id
INNER JOIN group_table_2 AS gt2
   ON hrt.group_id_2 = gt2.id
WHERE hrt.check_flg = 0
GROUP BY gt1.id, gt2.id
;

# サブクエリで先に集計したバージョン
SELECT
  gt1.name AS group_name_1,
  gt2.name AS group_name_2,
  tmp.count
FROM
(
SELECT
  hrt.group_id_1
  hrt.group_id_2
  COUNT(DISTINCT hrt.id) AS count
FROM huge_rows_table AS hrt
WHERE hrt.check_flg = 0
GROUP BY hrt.group_id_1, hrt.group_id_2
) AS tmp
STRAIGHT_JOIN group_table_1 AS gt1 # [状況次第] STRAIGHT_JOINは集計したtmpテーブルの後にJOINするのを強制するため。
   ON tmp.group_id_1 = gt1.id
STRAIGHT_JOIN group_table_2 AS gt2 # [状況次第] STRAIGHT_JOINは集計したtmpテーブルの後にJOINするのを強制するため。
   ON tmp.group_id_2 = gt2.id
GROUP BY gt1.id, gt2.id　 # [状況次第] 再度GROUP BYやORDER BYで順序を整える必要があればつける。
;

# 補足
# 上記のテーブル構成では、huge_rows_tableのgroup_id_1, group_id_2, check_flgにindexを作成すれば、
# INDEXが利用され、FULL TABLE SCANが発生しないので高速になる可能性が高いです。