RDBMSにおけるオプティマイザー設定

by afaik

投稿日: 7月 15, 2022

MySQLもPostgreSQLもコストベースのオプティマイザーがANALYZEによって収集したデータを基に、SQLの処理を最適化して実行してくれますが、必ずしも常にパフォーマンスが最適化される訳では有りません。そんな時にsql_mode等をサーバーやセッション単位で設定して、パフォーマンスを改善出来る場合もあります。但し全てのSQLが遅い場合では無いケースでは、サーバーやセッション単位で設定してしまうと、問題のあったSQLはパフォーマンスが改善しても他の処理が遅くなる場合があります。その為、通常は特定のSQLだけをヒント句を利用して改善するケースが多いかと思います。ヒント句に関しては、また別途詳細をまとめますが、ここでは主にサーバー側の設定を変更した場合のオプションに関して概要をまとめておきます。

In Case of MySQL

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.27    |
+-----------+

MySQLのSQLモードとオプティマイザースイッチ

mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.06 sec)

mysql> show variables like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)

Optimizer Switch	Default	詳細URL
index_merge	on	インデックスマージの最適化
index_merge_union	on	インデックスマージの最適化
index_merge_sort_union	on	インデックスマージの最適化
index_merge_intersection	on	インデックスマージの最適化
engine_condition_pushdown	on	エンジンコンディションプッシュダウンの最適化 (NDB)
index_condition_pushdown	on	インデックスコンディションプッシュダウンの最適化 (InnoDB, MyISAM)
mrr	on	8.2.1.11 Multi-Range Read の最適化
mrr_cost_based	on	8.2.1.11 Multi-Range Read の最適化
block_nested_loop	on	Block Nested Loop 結合と Batched Key Access 結合 (8.0.20 以降変更有り)
batched_key_access	off	Block Nested Loop 結合と Batched Key Access 結合
materialization	on	実体化を使用したサブクエリーの最適化 (サブクエリーの実体化)
semijoin	on	準結合変換による IN および EXISTS サブクエリーの最適化 (MySQL 8.0.17以降変更有り)
loosescan	on	準結合変換による IN および EXISTS サブクエリーの最適化
firstmatch	on	準結合変換による IN および EXISTS サブクエリーの最適化
duplicateweedout	on	準結合変換による IN および EXISTS サブクエリーの最適化
subquery_materialization_cost_based	on	切り替え可能な最適化の制御
use_index_extensions	on	インデックス拡張の使用
condition_fanout_filter	on	条件フィルタ
derived_merge	on	マージまたは実体化を使用した導出テーブル
use_invisible_indexes	off	不可視のインデックス
skip_scan	on	range の最適化
hash_join	on	ハッシュ結合の最適化 ( MySQL 8.0.19以降で変更有り）
subquery_to_derived	off	切り替え可能な最適化
prefer_ordering_index	on	切り替え可能な最適化　（MySQL 8.0.21 以降無効化が可能）
hypergraph_optimizer	off	MySQL Developer向けなので基本的にはOFF
derived_condition_pushdown	on	導出条件プッシュダウン最適化

参照：8.9.2 切り替え可能な最適化

derived_merge

derived_mergeがONの場合は以下の様な更新処理ではderived tableが作成される。

Updated with derived_merge on self reference

semijoin (anti join)

semijoinフラグは準結合を使用するかどうかを制御します。 MySQL 8.0.17 以降、これはアンチ結合にも適用されます。

condition_fanout_filter

遅くなるケースもあるので、処理がおそくなったらOFFにしてみる事も検討して見ると良いかと思います。

Locondo 20190703@inno db_cluster from Shinya Sugiyama

P.17 about condition_fanout_filter

use_invisible_indexes

mysql> ALTER TABLE members ADD INDEX idx_members_salary (salary) INVISIBLE;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `age` int NOT NULL,
  `salary` decimal(10,0) DEFAULT NULL,
  `regist` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_members_salary` (`salary`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

備考: Indexを可視化するには以下の様にALTERするかヒント句で特定処理にのみ利用する事も可能。

mysql> ALTER TABLE members ALTER INDEX idx_members_salary VISIBLE;;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

特定のSQL処理だけオプティマイザー処理を変更したい場合はオプティマイザヒントを利用する事も検討すると良いかと思います。

In Case of PostgreSQL

PostgreSQLに関しては、enable_~というプランナーメソッドを利用してコントロールする事が出来る。

POC=# show enable_hashjoin;
 enable_hashjoin
-----------------
 on
(1 row)

POC=# SET enable_hashjoin TO 'off';
SET
POC=# show enable_hashjoin;
 enable_hashjoin
-----------------
 off
(1 row)

enable_hashjoin

join_collapse_limit (Default 8, 1にする事で順番通りにJOINしてくれる）

SET ENABLE_HASHJOIN to off;	プランナがハッシュ結合計画を使用することを無効にします。
SET ENABLE_BITMAPSCAN to off;	プランナがビットマップスキャン計画型を選択することを無効にします。
SET ENABLE_NESTLOOP to off;	プランナがネステッドループ結合計画を使用することを無効にします。
SET ENABLE_MERGEJOIN to off;	プランナがマージ結合計画を使用を無効にします。
SET ENABLE_SEQSCAN to off;	プランナがシーケンシャルスキャン計画型を使用することを無効にします。

19.7. 問い合わせ計画

※ SETオプションを設定しても、必ずしも利用しなくなる訳では有りません。

参照： 19.7.1. プランナメソッド設定　　　

14.3. 明示的なJOIN句でプランナを制御する

その他参考：

pg_dbms_stats (PostgreSQL の統計情報管理ツール)

pg_hint_plan (PostgreSQL 実行計画制御ツール)

カテゴリー: