この記事はMySQL Casual Advent Calendar 2013 on Zusaarの19日目です。
yokuさんの記事「日々の覚書: あなたのMySQL 5.6トレンド力をチェックする15の質問」を見て、新しく加わったオプティマイザのことをちゃんと調べていなかったと思いまして、改めて調べてみました。
まず、どういう種類があるでしょうか。例として、ひとまず5つあるようです。
- Index Condition Pushdown(ICP)の追加
- BKA-Joinの追加
- Multi-Range Read(MRR)の追加
- FROM句サブクエリーの最適化
- Optimizer Traceの追加
これら、Block Nested-Loop(BNLJ)やBKA、ICP etcに関して、nippondanjiさんの記事がわかりやすく説明してくださっています。
ここでは、第一弾(?)としてICPを改めて調べてみます。
今回使用するMySQLは
- mysql-5.6.15-osx10.7-x86_64.tar.gz
環境はMacBook Air (11-inch, Mid 2013)です。mysqlのパラメータは、PC仕様にカスタマイズしています。データはtpcc-mysqlを使っています。tpcc-mysqlについてはtpcc-mysqlによるMySQLのベンチマーク - SH2の日記をご覧ください。
ICPがONになっているかどうか
5.6では、デフォルトONです。下のようにすることで確認できます。OFFにすることもできます。
root@:(none)>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,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)
root@:(none)>set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
root@:(none)>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=off,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)
root@:(none)>set optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)
root@:(none)>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,subquery_materialization_cost_based=on,use_index_extensions=on
ICPを使ってみる。
MySQLでは、1テーブルにつき1つのインデックスしか使用できません。検索条件に複数カラムを使用した場合、コストが掛からない適切なインデックスがひとつ選ばれて、実行されます。短絡的に考えて、一番効率がいいのが(こういうと憚りがありますが)、検索条件のカラム全部にインデックスを付ける、でしょう。(稀にする場合がありますけど)、これだと該当テーブルに対するwhere条件の数だけインデックスが必要となります。インデックスを作成すると、インデックスカラムのための内部テーブルを作ることともなり、更新効率が落ちてしまいます。そこで、ICPの登場です。実際に使ってみます。
テストテーブルとテストSELECT文
CREATE TABLE `orders` (
`o_id` int(11) NOT NULL,
`o_d_id` tinyint(4) NOT NULL,
`o_w_id` smallint(6) NOT NULL,
`o_c_id` int(11) DEFAULT NULL,
`o_entry_d` datetime DEFAULT NULL,
`o_carrier_id` tinyint(4) DEFAULT NULL,
`o_ol_cnt` tinyint(4) DEFAULT NULL,
`o_all_local` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`o_w_id`,`o_d_id`,`o_id`),
KEY `idx_orders` (`o_w_id`,`o_d_id`,`o_c_id`,`o_id`),
CONSTRAINT `fkey_orders_1` FOREIGN KEY (`o_w_id`, `o_d_id`, `o_c_id`) REFERENCES `customer` (`c_w_id`, `c_d_id`, `c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
上記のようなordersテーブルにインデックスを追加します。
create index o_c_id on orders(o_c_id);
つぎのようなselect文を試してみます。
select * from orders where o_c_id=2968 and o_d_id=9;
返ってくる行数は１０行です。
root@:tpcc>select count(*) from orders where o_c_id=2968 and o_d_id=9;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
まず、index_condition_pushdown=offから。
root@:tpcc>explain select * from orders where o_c_id=2968 and o_d_id=9\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: o_c_id
key: o_c_id
key_len: 5
ref: const
rows: 100
Extra: Using where
1 row in set (0.00 sec)
o_c_idキーを使っていますが、o_d_id=9の絞込のためUsing whereとなっています。
root@:tpcc>show status like 'Hand%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 100 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
実際100行読み込まれています。
次に、index_condition_pushdown=onします。
root@:tpcc>explain select * from orders where o_c_id=2968 and o_d_id=9\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: o_c_id
key: o_c_id
key_len: 5
ref: const
rows: 100
Extra: Using index condition
1 row in set (0.00 sec)
Using index conditionなる表示が！！
でもrows: 100となっている?
root@:tpcc>show status like 'Hand%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 10 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
10行しか読まれてない！！
まとめ
ということで、ICPが導入されることによって、これまでマルチカラムインデックスに頼らざるを得なかった複数カラムの条件対策に、新たな戦略を設けることができるようになりました。これで「あのorder byのため、このorder byのため、そうそうgroup byがいったんだ」などなどについて、頭を(少し)悩まされなくなりました。
ICPに関してはMariaDBにも解説が載っています。
明日、12/20はnippondanjiさんが担当です。