今回は,
はじめに
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_
のマルチカラムインデックスを作成すれば,