Go to Qiita Advent Calendar 2024 Top
LoginSignup
45
39

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@hrokmim(hrok mim)

MySQL 5.6で追加されたICPを追ってみました。

Last updated at Posted at 2013-12-19

この記事はMySQL Casual Advent Calendar 2013 on Zusaarの19日目です。

yokuさんの記事「日々の覚書: あなたのMySQL 5.6トレンド力をチェックする15の質問」を見て、新しく加わったオプティマイザのことをちゃんと調べていなかったと思いまして、改めて調べてみました。

まず、どういう種類があるでしょうか。例として、ひとまず5つあるようです。

  1. Index Condition Pushdown(ICP)の追加
  2. BKA-Joinの追加
  3. Multi-Range Read(MRR)の追加
  4. FROM句サブクエリーの最適化
  5. 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さんが担当です。

45
39
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
45
39

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?