PolarDB:サブクエリの最適化

PolarDB クラスターのパラメーターを変更する方法は、コンソールとデータベースセッションで異なります。違いは次のとおりです：

• PolarDB コンソール内

  • 互換性：MySQL 構成ファイルとの互換性を確保するため、PolarDB コンソールの一部のクラスターパラメーターには loose_ プレフィックスが付いています。

  • 操作手順：loose_ プレフィックスが付いているパラメーターを見つけて変更します。

• データベースセッション内 (コマンドラインまたはクライアントを使用)

  • 操作手順：SET コマンドを使用してデータベースセッションでパラメーターを変更する場合は、loose_ プレフィックスを削除し、元のパラメーター名を使用してください。

シナリオ 1：冗長な SELECT ネストの排除

サブクエリが集計関数や式をラップしているだけで、SELECT (SELECT SUM(a) FROM t2) FROM dual のように他の複雑なロジックがない場合、オプティマイザーは外側の SELECT を削除し、コア式を直接実行します。

-- 射影列内
SELECT (SELECT SUM(a) FROM t2) FROM dual;
-- 最適化後
SELECT SUM(`test`.`t2`.`a`) AS `sum(a)` FROM `test`.`t2`

-- HAVING 句内
SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));
-- 最適化後
SELECT SUM(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` HAVING (0 <> count(`testdb`.`t2`.`b`))

1. テストデータの準備：

   DROP TABLE IF EXISTS t2;
   CREATE TABLE t2 (
       id INT PRIMARY KEY AUTO_INCREMENT,
       a INT,
       b INT
   );
   INSERT INTO t2 (a, b) VALUES (10, 100), (20, NULL), (50, 200), (120, NULL);

2. 最適化を無効にしてクエリを実行：まず、セッションでサブクエリ最適化機能を無効にして、最適化前の実行計画を表示します。

   SET simplify_subq_mode = 'OFF';
   EXPLAIN SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));

   SHOW warnings;
   
   -- 結果
   /* select#1 */ select sum(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` having (0 <> (/* select#2 */ select (/* select#3 */ select (/* select#4 */ select count(`testdb`.`t2`.`b`)))))

3. 最適化を有効にしてクエリを実行：次に、セッションでサブクエリ最適化機能を有効にして、同じクエリを実行します。

   SET simplify_subq_mode = 'ON';
   EXPLAIN SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));

   SHOW warnings;
   
   -- 結果
   /* select#1 */ select sum(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` having (0 <> count(`testdb`.`t2`.`b`))

シナリオ 2：[NOT] EXISTS サブクエリの事前評価

オプティマイザーが [NOT] EXISTS サブクエリが常に true (空でない結果) または false (空の結果) と評価されると判断した場合、[NOT] EXISTS 句を TRUE または FALSE に置き換えます。この置き換えにより、サブクエリの実行が回避されます。

-- 空でないセット
SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);
-- 最適化後
SELECT * FROM t1

-- 空のセット、WHERE/HAVING 句=false、または LIMIT 0
SELECT * FROM t1 WHERE EXISTS(SELECT max(a) FROM t2 HAVING 1=2 );
-- 最適化後
SELECT * FROM t1 WHERE false

1. テストデータの準備：

   DROP TABLE IF EXISTS t1;
   DROP TABLE IF EXISTS t2;
   CREATE TABLE t1 (id INT);
   CREATE TABLE t2 (val INT);
   INSERT INTO t1 VALUES (1), (2);

2. 最適化を無効にしてクエリを実行：まず、セッションでサブクエリ最適化機能を無効にして、最適化前の実行計画を表示します。

   SET simplify_subq_mode = 'OFF';
   EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);

   SHOW warnings;
   
   -- 結果
   /* select#1 */ select `testdb`.`t1`.`id` AS `id` from `testdb`.`t1` where exists(/* select#2 */ select max(`testdb`.`t1`.`id`) from `testdb`.`t2`)

3. 最適化を有効にしてクエリを実行：次に、セッションでサブクエリ最適化機能を有効にして、同じクエリを実行します。

   SET simplify_subq_mode = 'ON';
   EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);

   SHOW warnings;
   
   -- 結果
   /* select#1 */ select `testdb`.`t1`.`id` AS `id` from `testdb`.`t1`

シナリオ 3：ANY/ALL サブクエリ内の定数射影への LIMIT 1 の追加

ANY または ALL 句のサブクエリが定数のみをクエリし、列を一切含まない場合、複数の同一行を取得する必要はありません。オプティマイザーは、これらのサブクエリに自動的に LIMIT 1 を追加し、サブクエリ内のテーブルに対する不要な全表スキャンを回避します。

-- 最適化前
SELECT * FROM t1 WHERE a > ANY (SELECT 1 FROM t2);
-- 最適化後
SELECT * FROM t1 WHERE a > ANY (SELECT 1 FROM t2 LIMIT 1);