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 | 導出条件プッシュダウン最適化 |
- derived_merge
derived_mergeがONの場合は以下の様な更新処理ではderived tableが作成される。
- semijoin (anti join)
semijoinフラグは準結合を使用するかどうかを制御します。 MySQL 8.0.17 以降、これはアンチ結合にも適用されます。
- condition_fanout_filter
遅くなるケースもあるので、処理がおそくなったらOFFにしてみる事も検討して見ると良いかと思います。
- 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; | プランナがシーケンシャルスキャン計画型を使用することを無効にします。 |
※ SETオプションを設定しても、必ずしも利用しなくなる訳では有りません。
参照: 19.7.1. プランナメソッド設定
14.3. 明示的なJOIN句でプランナを制御する
その他参考: