测试环境
mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.36 | +-----------+ 1 row in set (0.00 sec)
在两表没有任何索引和主键的情况下，MySQL默认选择Maerialized optimization，执行时间为0.41s
mysql> explain
-> select * from dbs t01
-> where t01.dbid in
-> ( select dbid from jbs t02 where id > 100 and id < 1000);
+----+--------------+-------------+--------+---------------+------------+---------+----------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------+--------+---------------+------------+---------+----------------------+--------+-------------+
| 1 | SIMPLE | t01 | ALL | NULL | NULL | NULL | NULL | 144411 | Using where |
| 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 8 | oracle_info.t01.DBID | 1 | NULL |
| 2 | MATERIALIZED | t02 | ALL | NULL | NULL | NULL | NULL | 179525 | Using where |
+----+--------------+-------------+--------+---------------+------------+---------+----------------------+--------+-------------+
3 rows in set (0.00 sec)
mysql> select count(*) from dbs;
+----------+
| count(*) |
+----------+
| 145728 |
+----------+
1 row in set (0.07 sec)
mysql> select count(*) from jbs;
+----------+
| count(*) |
+----------+
| 186368 |
+----------+
1 row in set (0.08 sec)
执行过程：
1.创建基于jbs的临时表，并创建相应的索引id or dbid
2.用临时表和dbs表连接查询获得最后的结果
当MySQL没有有效索引的时候，MySQL materialization 会帮助优化器创建拥有索引的临时表提高MySQL查询引擎的执行效率
现在对jbs表id增加主键约束，测试执行结果时间为：0.38s
mysql> alter table jbs add primary key(id); Query OK, 0 rows affected (5.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from dbs t01 where t01.dbid in ( select dbid from jbs t02 where id > 100 and id < 1000); +----+--------------+-------------+-------+---------------+---------+---------+------+--------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+-------+---------------+---------+---------+------+--------+----------------------------------------------------+ | 1 | SIMPLE | <subquery2> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | | 1 | SIMPLE | t01 | ALL | NULL | NULL | NULL | NULL | 144411 | Using where; Using join buffer (Block Nested Loop) | | 2 | MATERIALIZED | t02 | range | PRIMARY | PRIMARY | 4 | NULL | 213 | Using where | +----+--------------+-------------+-------+---------------+---------+---------+------+--------+----------------------------------------------------+ 3 rows in set (0.00 sec)
对表jbs、dbs列dbid增加索引，测试结果如下：
mysql> create index idx_01 on dbs(dbid); Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index idx_02 on jbs(dbid); Query OK, 0 rows affected (0.88 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from dbs t01 where t01.dbid in ( select dbid from jbs t02 where id > 100 and id < 1000); +----+--------------+-------------+--------+----------------+------------+---------+----------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+----------------+------------+---------+----------------------+--------+-------------+ | 1 | SIMPLE | t01 | ALL | idx_01 | NULL | NULL | NULL | 144411 | Using where | | 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 8 | oracle_info.t01.DBID | 1 | NULL | | 2 | MATERIALIZED | t02 | range | PRIMARY,idx_02 | PRIMARY | 4 | NULL | 213 | Using where | +----+--------------+-------------+--------+----------------+------------+---------+----------------------+--------+-------------+ 3 rows in set (0.00 sec)
execution time 0.29s
优化器参数配置如下：
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,subquery_materialization_cost_based=on,use_index_extensions=on
修改优化器获得如下效果：
mysql> set optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=off,use_index_extensions=off'; Query OK, 0 rows affected (0.00 sec) mysql> explain select * from dbs t01 where t01.dbid in ( select dbid from jbs t02 where id > 100 and id < 1000); +----+-------------+-------+-------+----------------+--------+---------+----------------------+--------+-------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+--------+---------+----------------------+--------+-------------------------------------+ | 1 | SIMPLE | t02 | index | PRIMARY,idx_02 | idx_02 | 8 | NULL | 184904 | Using where; Using index; LooseScan | | 1 | SIMPLE | t01 | ref | idx_01 | idx_01 | 8 | oracle_info.t02.dbid | 3282 | NULL | +----+-------------+-------+-------+----------------+--------+---------+----------------------+--------+-------------------------------------+ 2 rows in set (0.00 sec) execution 0.38s
删除两表的索引，获得如下结果：
mysql> drop index idx_01 on dbs; Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index idx_02 on jbs; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from dbs t01 where t01.dbid in ( select dbid from jbs t02 where id > 100 and id < 1000); +----+-------------+-------+-------+---------------+---------+---------+------+--------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+--------+------------------------------+ | 1 | SIMPLE | t02 | range | PRIMARY | PRIMARY | 4 | NULL | 213 | Using where; Start temporary | | 1 | SIMPLE | t01 | ALL | NULL | NULL | NULL | NULL | 144411 | Using where; End temporary | +----+-------------+-------+-------+---------------+---------+---------+------+--------+------------------------------+ 2 rows in set (0.00 sec) execution time 33s
mysql> select @@optimizer_switch G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=off,use_index_extensions=off
对比第一次执行，matierialization执行方式效率更好。