Open6
Mysql ICP
syyドキュメント
参考
インデックスコンディションプッシュダウン
= ICP
コンディション = 状態, 条件
目的
完全なレコード読み込みを減らし、IO操作をへらすこと。
バッファープールへのアクセスを減らすのではなく、ストレージへのアクセスを減らす。
a1_a2_a3の複合indexがあった場合に、
where a1 = 1 and a3 =1の条件があると、通常はa1の条件にしかindexは使用されないが、ICPを使うとa1とa3の条件にindexが使用される。
件数が少ないとICPが使われないこともある。
ICPが使われるとexplainのExtraにUsing index Condition;と表示される
syya1,a2,a3の複合indexがあるテーブル
mysql> show create table a;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a | CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a1` int(11) DEFAULT NULL,
`a2` int(11) DEFAULT NULL,
`a3` int(11) DEFAULT NULL,
`a4` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a1_a2_a3` (`a1`,`a2`,`a3`),
KEY `a1` (`a1`)
) ENGINE=InnoDB AUTO_INCREMENT=131056 DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
a1_a2_a3インデックスを使用する場合、通常はa1,a2,a3の順番でwhereを使用する必要がある。
↓a1_a2_a3が使用される。
select * from a where a1 = 1
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | a | NULL | ref | a1_a2_a3,a1 | a1_a2_a3 | 5 | const | 635 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
select * from a where a1 = 1 and a2 = 1
+----+-------------+-------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | a | NULL | ref | a1_a2_a3,a1 | a1_a2_a3 | 10 | const,const | 5 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
explain select * from a where a1 = 1 and a2 = 1 and a3 = 1
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | a | NULL | ref | a1_a2_a3,a1 | a1_a2_a3 | 15 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
通常はwhere a1 = 1 and a3 = 1のようなindex順に使用できない条件の場合は、a1の条件チェックのみにindexが使用され、a3の条件チェックはフェッチ後のレコードからmysql serverがフィルターをかける。
ここの図が分かりやすい
syyICPの有効/無効とステータス確認
デフォルトで有効になっている。
無効
mysql> set optimizer_switch = "index_condition_pushdown=off";
Query OK, 0 rows affected (0.00 sec)
有効
mysql> set optimizer_switch = "index_condition_pushdown=on";
Query OK, 0 rows affected (0.00 sec)
変な値を入れるとエラー
mysql> set optimizer_switch = "index_condition_pushdown=onaa";
ERROR 1231 (42000): Variable 'optimizer_switch' can't be set to the value of 'index_condition_pushdown=onaa'
確認
index_condition_pushdown=???を見ると分かる。
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=off,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
1 row in set (0.00 sec)
ICP offのとき。
where a1 =1 and a3 = 1となっているので、a1_a2_a3のindexが使用される。
Handler_read_nextを見ると635行フェッチされている。
実際に表示されたのは3行。
ストレージエンジンが635行取得し、mysql serverがa3の条件whereで3件絞り込んで表示した。
635行というのは、a1で絞り込めた行数のこと。
mysql> select @@optimizer_switch ;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> flush status
mysql> select * from a where a1 = 1 and a3 = 1;
+-------+------+------+------+------+
| id | a1 | a2 | a3 | a4 |
+-------+------+------+------+------+
| 17878 | 1 | 41 | 1 | 39 |
| 34543 | 1 | 41 | 1 | NULL |
| 86136 | 1 | 41 | 1 | NULL |
+-------+------+------+------+------+
3 rows in set (0.00 sec)
mysql> show status like 'Handler%';
+----------------------------+-------+
| 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 | 635 |
| 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)
mysql> explain select * from a where a1 = 1 and a3 = 1;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | ref | a1_a2_a3,a1 | a1_a2_a3 | 5 | const | 635 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from a where a1 = 1;
...省略...
| 91608 | 1 | 99 | 94 | NULL |
| 73400 | 1 | 99 | 95 | NULL |
| 19124 | 1 | 100 | 96 | 29 |
| 85152 | 1 | 100 | 96 | NULL |
+-------+------+------+------+------+
635 rows in set (0.01 sec)
ICP onのとき。
where a1 =1 and a3 = 1となっているので、a1_a2_a3のindexが使用される。
本来a1の条件チェックにしかindexは使われないが、ICPの機能によりa3の条件チェックにも使用される。
そのため、Hander_read_nextの値が3になっている。
表示する結果も3件。
explainを見るとExtraがUsing index condition;と表示されている。
onにする。
mysql> set optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@optimizer_switch ;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@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 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a where a1 = 1 and a3 = 1;
+-------+------+------+------+------+
| id | a1 | a2 | a3 | a4 |
+-------+------+------+------+------+
| 17878 | 1 | 41 | 1 | 39 |
| 34543 | 1 | 41 | 1 | NULL |
| 86136 | 1 | 41 | 1 | NULL |
+-------+------+------+------+------+
3 rows in set (0.00 sec)
mysql> show status like 'Handler%';
+----------------------------+-------+
| 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 | 3 |
| 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)
mysql> explain select * from a where a1 = 1 and a3 = 1;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | a | NULL | ref | a1_a2_a3,a1 | a1_a2_a3 | 5 | const | 635 | 10.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
カバリングインデックスのときはICPが使用されない？？？
ExtraにUsing Indexがあるのでカバリングインデックスは使用された。
てっきりHandler_next_readが3になるのかなと思っていた。
mysql> explain select a1, a3 from a where a1 = 1 and a3 = 1;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | a | NULL | ref | a1_a2_a3,a1 | a1_a2_a3 | 5 | const | 635 | 10.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> flush status;
mysql> select a1 from a where a1 = 1 and a3 = 1;
+------+
| a1 |
+------+
| 1 |
| 1 |
| 1 |
+------+
3 rows in set (0.00 sec)
mysql> show status like 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 2 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 4 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 635 |
| 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)