table
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 | 9895 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
-- s1 驱动表，s2 被驱动表
mysql> explain select * from s1 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 | 9895 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)
1)、当在from子句中有子查询时，table列是<derivedN>的形式，其中N是子查询的id。这总是“向前引用”，即N指向explain输出中后面的一行。
2)、当有union时，union result 的 table 列包含一个参与union的id列表。这总是“向后引用”，因为union result出现在union中所有参与行之后
id
id：SELECT识别符。这是SELECT的查询序列号
两个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 | 9895 | 100.00 | Using where |
| 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)
子查询和外查询的id相同，因为查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作，所以只会有一个select
mysql> explain select * from s1 where key1 in (select key3 from s2 where commom_field = 'a');
+----+--------------+-------------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where |
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | <subquery2>.key3 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | s2 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 10.00 | Using where |
+----+--------------+-------------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
union 去重 ，<union1,2> 临时表
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 | 9895 | 100.00 | NULL |
| 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 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 如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好
