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

sql_mode

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 SwitchDefault詳細URL
index_mergeonインデックスマージの最適化
index_merge_uniononインデックスマージの最適化
index_merge_sort_uniononインデックスマージの最適化
index_merge_intersectiononインデックスマージの最適化
engine_condition_pushdownonエンジンコンディションプッシュダウンの最適化 (NDB)
index_condition_pushdownonインデックスコンディションプッシュダウンの最適化 (InnoDB, MyISAM)
mrron8.2.1.11 Multi-Range Read の最適化
mrr_cost_basedon8.2.1.11 Multi-Range Read の最適化
block_nested_looponBlock Nested Loop 結合と Batched Key Access 結合 (8.0.20 以降変更有り)
batched_key_accessoffBlock Nested Loop 結合と Batched Key Access 結合
materializationon実体化を使用したサブクエリーの最適化 (サブクエリーの実体化)
semijoinon準結合変換による IN および EXISTS サブクエリーの最適化 (MySQL 8.0.17以降変更有り)
loosescanon準結合変換による IN および EXISTS サブクエリーの最適化
firstmatchon準結合変換による IN および EXISTS サブクエリーの最適化
duplicateweedouton準結合変換による IN および EXISTS サブクエリーの最適化
subquery_materialization_cost_basedon切り替え可能な最適化の制御
use_index_extensionsonインデックス拡張の使用
condition_fanout_filteron条件フィルタ
derived_mergeonマージまたは実体化を使用した導出テーブル
use_invisible_indexesoff不可視のインデックス
skip_scanonrange の最適化
hash_joinon ハッシュ結合の最適化 ( MySQL 8.0.19以降で変更有り)
subquery_to_derivedoff切り替え可能な最適化
prefer_ordering_indexon切り替え可能な最適化 (MySQL 8.0.21 以降無効化が可能)
hypergraph_optimizeroffMySQL Developer向けなので基本的にはOFF
derived_condition_pushdownon導出条件プッシュダウン最適化

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

  • derived_merge
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にしてみる事も検討して見ると良いかと思います。

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)
use_invisible_indexes
  • 備考: 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
enable_hashjoin
  • join_collapse_limit (Default 8, 1にする事で順番通りにJOINしてくれる)
join_collapse_limit

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 実行計画制御ツール)

カテゴリー:

最近のコメント

表示できるコメントはありません。