测试环境
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执行方式效率更好。