![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MySQLとPostgreSQLの基本的な実行プランについてまとめてみた
実際のチューニングに関しては、色々と参考になるドキュメントがあったので、そちらを参考にExplainの結果をもとにチューニングして頂ければ思います。
MySQL, PostgreSQLそれぞれ,コストベースに実行プランを作成し殆どのケースで最適な実行プランを作成し、最も少ないコストで適切にデータを抽出してくれる。
MySQLもPostgreSQLも細かく設定が出来るようになっているが、細かくチューニングする検証コストを考えると、SQLやインデックスを追加・変更した方が高速化出来るケースが多々あるので、あまり細かくパラメータを変更して実行プランをコントロールするケースは少ないかと思います。ただ、バージョンアップ等を実施した時に、Optimizerの設定が変わって早くなったり、遅くなったりするクエリーもあるので、処理の重要度など必要に応じてオプティマイザーの設定を下位互換にすることなともあるかと思います。何はともあれ、まずはExplainを見てデータベースの判断および処理方法を確認し、意図したものか？意図しない場合はどのように対応するか検討してみましょう。
root@localhost [mysql]> show global variables like '%innodb_stat%'; +--------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------+-------------+ | innodb_stats_auto_recalc | ON | | innodb_stats_include_delete_marked | OFF | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_transient_sample_pages | 8 | | innodb_status_output | OFF | | innodb_status_output_locks | OFF | +--------------------------------------+-------------+ 9 rows in set (0.00 sec) root@localhost [mysql]>
postgres=# select name,setting,unit,context,category,short_desc from pg_settings where name like '%cost%';
name | setting | unit | context | category | short_desc
------------------------------+---------+------+---------+-----------------------------------------------+--------------------------------------------------------------------------------------------------------------------
autovacuum_vacuum_cost_delay | 2 | ms | sighup | 自動VACUUM | 自動VACUUM用のミリ秒単位のコストベースのVACUUM処理の遅延時間です。
autovacuum_vacuum_cost_limit | -1 | | sighup | 自動VACUUM | 自動VACUUM用のVACUUM処理を一時休止させるまでに使用できるコスト。
cpu_index_tuple_cost | 0.005 | | user | 問い合わせのチューニング / プランナコスト定数 | インデックススキャンにおける一つのインデックスエントリの処理についてプランナで使用する見積もりコストを設定。
cpu_operator_cost | 0.0025 | | user | 問い合わせのチューニング / プランナコスト定数 | 一つの演算子または関数の処理についてプランナで使用する見積もりコストを設定。
cpu_tuple_cost | 0.01 | | user | 問い合わせのチューニング / プランナコスト定数 | 一つのタプル(行)の処理についてプランナで使用する見積もりコストを設定。
jit_above_cost | 100000 | | user | 問い合わせのチューニング / プランナコスト定数 | 問い合わせがこの値より高コストであればJITコンパイルを実行します。
jit_inline_above_cost | 500000 | | user | 問い合わせのチューニング / プランナコスト定数 | 問い合わせがこの値より高コストであればJITコンパイルされた関数をインライン化します。
jit_optimize_above_cost | 500000 | | user | 問い合わせのチューニング / プランナコスト定数 | 問い合わせがこの値より高コストであればJITコンパイルされた関数を最適化します。
parallel_setup_cost | 1000 | | user | 問い合わせのチューニング / プランナコスト定数 | 並列問い合わせ実行のためのワーカプロセスの起動についてプランナで使用する見積もりコストを設定。
parallel_tuple_cost | 0.1 | | user | 問い合わせのチューニング / プランナコスト定数 | 並列処理ワーカからマスタバックエンドへの一つのタプル(行)の受け渡しについてプランナが使用する見積もりコストを設定。
random_page_cost | 4 | | user | 問い合わせのチューニング / プランナコスト定数 | ひと続きでは読み込めないディスクページについてプランナで使用する見積もりコストを設定。
seq_page_cost | 1 | | user | 問い合わせのチューニング / プランナコスト定数 | ひと続きに読み込むディスクページについてプランナで使用する見積もりコストを設定。
vacuum_cost_delay | 0 | ms | user | 使用リソース / コストベースvacuum遅延 | ミリ秒単位のコストベースのVACUUM処理の遅延時間です。
vacuum_cost_limit | 200 | | user | 使用リソース / コストベースvacuum遅延 | VACUUM処理を一時休止させるまでに使用できるコスト。
vacuum_cost_page_dirty | 20 | | user | 使用リソース / コストベースvacuum遅延 | VACUUM処理が1つのページをダーティにした際に課すコスト。
vacuum_cost_page_hit | 1 | | user | 使用リソース / コストベースvacuum遅延 | バッファキャッシュにある1つのページをVACUUM処理する際のコスト。
vacuum_cost_page_miss | 10 | | user | 使用リソース / コストベースvacuum遅延 | バッファキャッシュにない1つのページをVACUUM処理する際のコスト。
(17 行)
Explain Sample
PostgreSQLの場合 (例：Hash Join)
PostgreSQLには、Marge JOIN, Hash JOIN, Nested LoopのJOINの方法があり、オプティマイザーが最適なJOIN方式を選択してJOINが実行されます。
app=# explain select * from mem2 join memo using(id);
QUERY PLAN
-----------------------------------------------------------------------
Hash Join (cost=598.00..623.67 rows=660 width=116)
Hash Cond: (mem2.id = memo.id)
-> Seq Scan on mem2 (cost=0.00..16.60 rows=660 width=94)
-> Hash (cost=348.00..348.00 rows=20000 width=26)
-> Seq Scan on memo (cost=0.00..348.00 rows=20000 width=26)
(5 行)
app=# explain select * from memo join mem2 on memo.id = mem2.id;
QUERY PLAN
-----------------------------------------------------------------------
Hash Join (cost=598.00..623.67 rows=660 width=120)
Hash Cond: (mem2.id = memo.id)
-> Seq Scan on mem2 (cost=0.00..16.60 rows=660 width=94)
-> Hash (cost=348.00..348.00 rows=20000 width=26)
-> Seq Scan on memo (cost=0.00..348.00 rows=20000 width=26)
(5 行)
app=#
MySQLの場合 (例:Nested loop)
MySQLはNested Loopを利用してJOINを実行 (MySQL8.0.18以降:Hash Joinをサポート)
Nested Loopの場合は、駆動表（外部表）のデータを出来るだけ少なくして、内部表のテーブルにインデックスが張ってあれば他のJOINの方式よりも高速化可能。
root@localhost [confirm]> explain select * from mem2 join memo using(id);
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| 1 | SIMPLE | memo | NULL | ALL | PRIMARY | NULL | NULL | NULL | 22 | 100.00 | NULL |
| 1 | SIMPLE | mem2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | confirm.memo.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
root@localhost [confirm]> explain select * from memo join mem2 on memo.id = mem2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| 1 | SIMPLE | memo | NULL | ALL | PRIMARY | NULL | NULL | NULL | 22 | 100.00 | NULL |
| 1 | SIMPLE | mem2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | confirm.memo.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
root@localhost [confirm]> explain select * from mem2 straight_join memo using(id);
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| 1 | SIMPLE | mem2 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 100.00 | NULL |
| 1 | SIMPLE | memo | NULL | eq_ref | PRIMARY | PRIMARY | 4 | confirm.mem2.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
root@localhost [confirm]> explain format=tree select * from mem2 straight_join memo using(id);
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=12.25 rows=10)
-> Table scan on mem2 (cost=1.25 rows=10)
-> Single-row index lookup on memo using PRIMARY (id=mem2.id) (cost=1.01 rows=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL8.0.18からはHash Joinもサポートされています。
Hash joins have been implemented as a way of executing inner equi-joins in MySQL. For example, a query such as this one can be executed as a hash join beginning with this release.
A hash join requires no index for execution. In most cases, a hash join is more efficient than the block-nested loop algorithm previously used for equi-joins without indexes.
By default, beginning with this release, a hash join is used whenever a join includes at least one equi-join condition. This preference can be overridden by setting the hash_join optimizer switch to off, or by using the NO_HASH_JOIN optimizer hint. In addition, you can control the amount of memory used by a hash join by setting join_buffer_size. A join whose memory requirement exceeds this amount is executed on disk; an on-disk hash join uses a number of disk files and may not be executable if this number exceeds open_files_limit.
A hash join cannot be employed if the join conditions for any pair of joined tables do not include at least one equi-join condition among all join conditions used. A hash join is used for a Cartesian product—that is, a join that specifies no join conditions at all.
You can see whether hash joins have been used to optimize a query in the output of EXPLAIN FORMAT=TREE or EXPLAIN ANALYZE.
In addition, inner joins using hash joins can now also take advantage of Batched Key Access (BKA). Outer joins still allocate the entire join buffer.
For more information, see Hash Join Optimization.
参照：https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html
MySQL8.0.20で更にHASH JOINコンディションが拡張されています。
Hash joins are now used any time a nested block loop would be employed. This means that hash joins can be used for the following types of queries:
Inner non-equi-joins
Semijoins
Antijoins
Left outer joins
Right outer joins
This builds on work done for MySQL 8.0.18, and removes a limitation in the implementation such that a hash join could be used only with a query having at least one equi-join condition. In addition, both inner and outer joins (including semijoins and antijoins) can now employ batched key access (BKA), which allocates join buffer memory incrementally so that individual queries need not use up large amounts of resources that they do not actually require for resolution. For more information, see Batched Key Access Joins.
This fix completes the task of replacing the executor used in previous versions of MySQL with the iterator executor, including replacement of the old index subquery engines that governed queries of the form WHERE value IN (SELECT column FROM table WHERE condition) for those IN queries which have not been converted into semijoins, as well as queries materialized into the same form, which depended on internals from the old executor.
For more information and examples, see Hash Join Optimization. (Bug #30528604, Bug #30473261, Bug #30912972)
参照：https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html
基本的な統計情報とExplainのまとめ
