前面说了semi-join，这个是在where或者on语句后面，in里面，并且外层的条件必须用and与子查询连接，semi-join的作用就是，不管子查询有多少条数据返回，都不管，外层都只查询出来外层表数据，如果不符合条件，可以用物化表或者in变exists方法优化。还有派生表查询，可以内外合并，不行的话就物化查询。
Semi-join使用条件，派生表优化 (3)—mysql基于规则优化（四十六)
Explain
一条查询语句经过mysql优化器之后，会生成一个执行计划，这个计划展现了接下来具体查询方式，比如多表连接的顺序，mysql贴心的为我们查看提供了explain语句。
mysql> EXPLAIN SELECT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.01 sec)
上面的这个就是执行计划，除了select，我们吧delete，update，insert，都可以用explain查看执行计划，我们先把列熟悉一下。
Id:一个大的查询语句，每个select关键字都对应一个唯一id。
Select_type：select关键字对应的查询类型。
Table：表名。
Partitions：匹配的分区信息。
Type：针对单表的访问方法。
Possible_keys：可能用到的索引。
Key：实际上用到的索引。
Key_len：实际使用的索引长度。
Ref：当使用索引等值查询时，索引列进行等值匹配的对象信息。
Rows：预估需要读取记录条数。
Filted：某个表经过搜索条件过滤后剩余记录条数的百分比。
Extra：一些额外信息。
下面还是用single_table给大家演示，为了方便大家阅读，我们再看一遍表结构：
CREATE TABLE single_table ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), KEY idx_key1 (key1), UNIQUE KEY idx_key2 (key2), KEY idx_key3 (key3), KEY idx_key_part(key_part1, key_part2, key_part3) ) Engine=InnoDB CHARSET=utf8;
执行计划输出各列详情
Table
我们先看一个比较简单的查询语句：
mysql> EXPLAIN SELECT * FROM s1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
因为我们是单表查询，可以看到table是s1。
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ 2 rows in set, 1 warning (0.01 sec)
如果是查询两个表，可以看到table是s1和s2。所以由上可以知道，每一列都是代表单表查询的详细数据。
id
我们都知道每次查询都是有一个select的，
SELECT * FROM s1 WHERE key1 = 'a';
但有的时候也会出现多个select的情况，比如union查询和子查询
SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2); SELECT * FROM s1 UNION SELECT * FROM s2；
mysql规定，每出现一个select就会多一个id，比如下面的sql：
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.03 sec)
就只有一个id，对于链接查询的话，from后面可以跟着多个表，每个表都会对应一条记录，但这些记录是相同的：
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ 2 rows in set, 1 warning (0.01 sec)
上面的结果可以看出，虽然有两条数据，但id是相同的，并且前面的表是驱动表，后面的表是被驱动表，所以从上面可以看出，s1是驱动表，s2是被驱动表。
如果是子查询，则会 每个select对应一个id。
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9688 | 100.00 | Using where | | 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9954 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.02 sec)
可以看到，这两个id是不一样的，但有的时候，子查询会被查询优化器进行重写，然后转成连接查询，
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a'); +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+ | 1 | SIMPLE | s2 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9954 | 10.00 | Using where; Start temporary | | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | xiaohaizi.s2.key3 | 1 | 100.00 | End temporary | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+ 2 rows in set, 1 warning (0.00 sec)
可以看到，虽然有两个select，但两个id都是相同的，但是对于union拉说，还是有点不同
mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
可以看到多了个id为null的列，这是什么呢？是因为union是需要去重的，那他怎么去重呢，就需要创建临时表，所以创建了table为union1,2的临时表，id为null，相对于union all就不需要去重，这种就不会创建临时表
mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9954 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 1 warning (0.01 sec)
