今回は、
はじめに
optimizer_
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: 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)
値はフラグの集合で、
例として、index_フラグの動作をoffにする場合は以下のように記述します。
mysql> SET SESSION optimizer_switch='index_merge=off';
また、
MySQL 8.0で追加されたフラグ
MySQL 8.
- prefer_
ordering_ index - derived_
condition_ pushdown - use_
invisible_ indexes - skip_
scan - hash_
join - subquery_
to_ derived - hypergraph_
optimizer
これらのフラグとその動作について紹介したいと思います。
prefer_ordering_index
このフラグはMySQL 8.
prefer_
しかし、
ちなみに、
それでは、
mysql> CREATE TABLE `article` ( `article_no` bigint NOT NULL AUTO_INCREMENT, `countrycode` varchar(3) DEFAULT NULL, `create_at` datetime DEFAULT NULL, PRIMARY KEY (`article_no`), KEY `countrycode` (`countrycode`), KEY `create_at` (`create_at`) ) ENGINE=InnoDB
countrycodeカラムのデータ分布はJPに大きく偏っています。
mysql> SELECT countrycode,COUNT(*) FROM article GROUP BY 1 ORDER BY 2 DESC; +-------------+----------+ | countrycode | COUNT(*) | +-------------+----------+ | JP | 1000000 | | CN | 5000 | | KR | 5000 | | VN | 5000 | +-------------+----------+
このテーブルに対して、
EXPLAIN SELECT * FROM article WHERE countrycode='KR' ORDER BY create_at DESC LIMIT 100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: article
partitions: NULL
type: index
possible_keys: countrycode
key: create_at
key_len: 6
ref: NULL
rows: 5392
filtered: 1.85
Extra: Using where; Backward index scan
すると、countrycode='KR'で十分に絞り込める
create_countrycode='KR'のレコード100件がすぐに見つかれば速いです。しかし、countrycode='KR'のレコードが降順から辿って一番最後の方にあると、100 rows in set (1.でした。
そのため、
EXPLAIN SELECT /*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */ * FROM article WHERE countrycode='KR' ORDER BY create_at DESC LIMIT 100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: article
partitions: NULL
type: ref
possible_keys: countrycode
key: countrycode
key_len: 15
ref: const
rows: 19412
filtered: 100.00
Extra: Using filesort
countrycodeのインデックスで解決されました。
Extra: Using filesortからソート処理が実施されていますが、100 rows in set (0.でした。countrycodeのインデックスを使用したほうが効果的だとわかります。
また、countrycode='JP'の条件であれば、
- prefer_
ordering_ index=on （create_ atインデックスを使用） → 100 rows in set (0.01 sec) - prefer_
ordering_ index=off （countrycodeインデックスを使用）→ 100 rows in set (3.88 sec)
このように、
ちなみに、(countrycode,create_のマルチカラムインデックスを作成すれば、
derived_condition_pushdown
このフラグはMySQL 8.
先ほどのarticleテーブルを使って説明します。また、optimizer_に設定しています。では、
mysql> EXPLAIN SELECT * FROM (SELECT * FROM article) a WHERE a.create_at > now() - INTERVAL 10 MINUTE;
派生テーブルは(SELECT * FROM article)の部分で、a.になります。
derived_
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: article
partitions: NULL
type: range
possible_keys: create_at
key: create_at
key_len: 6
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
derived_
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1046777
filtered: 33.33
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: article
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1046777
filtered: 100.00
Extra: NULL
derived_
derived_Extra: Using whereから、
よって、
SELECT * FROM (SELECT * FROM article WHERE a.create_at > now() - INTERVAL 10 MINUTE) a;
use_invisible_indexes
このフラグはMySQL 8.
Invisible Indexesについては第110回 Invisible Indexesを使って気軽にチューニングを始めてみるで紹介されているのでご参照ください。
skip_scan
このフラグはMySQL 8.
ここでは説明しないので、
その他フラグ
その他の追加されたフラグは変更する必要はない、
hash_join
このフラグをMySQL 8.
subquery_to_derived
このフラグはMySQL 8.
- ON
（有効） にしてもパフォーマンスが目立って向上することはありません。 - 主にテストでの使用を目的としています。
hypergraph_optimizer
このフラグは現在は使用できません。マニュアルによると、
まとめ
今回紹介した内容は、
また、