使用explain语句检查优化器操作 +----+-------------+----------+-------+---------------+------+---------+------+------+----------------- | id | select_type | table | type | possible_keys | key | key_len| ref | rows | extra +----+-------------+----------+-------+---------------+------+---------+------+------+----------------- | 1 |simple | car_info | range | name | name | 768 | null | 9 | using where; using index | +----+-------------+----------+-------+---------------+------+---------+------+------+----------------
explain输出解释
select_type 有如下几种类型： simple：未使用连接查询或者子查询的简单select语句 explain select * from car_info;
primary：最外层的select语句 explain select * from (select name from car_info where name like '凯迪拉克%') as a;
+----+-------------+------------+-------+---------------+------+---------+------+------+-------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+----+-------------+------------+-------+---------------+------+---------+------+------+---------------
| 1 | primary | | all | null | null | null | null | 9 | |
| 2 | derived | car_info | range | name | name | 768 | null | 9 | using where; using index |
+----+-------------+------------+-------+---------------+------+---------+------+------+---------------
union：union中的第二个，或后面的select语句 explain select name from car_info where id =100 union select name from web_car_brands where id =5; +------+--------------+----------------+-------+---------------+---------+---------+-------+------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +------+--------------+----------------+-------+---------------+---------+---------+-------+------+--- | 1 | primary | car_info | const | primary | primary | 8 | const | 1 | | | 2 | union | web_car_brands | const | primary,id | primary | 4 | const | 1 | | | null | union result |
dependent union：union中的第二个或后面的色了传统语句，取决于外面的查询
mysql> explain select * from t3 where id in (select id from t3 where id=3952602 union all select id from t3) ;
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--
| 1 | primary | t3 | all | null | null | null | null | 1000 | using where
| 2 | dependent subquery | t3 | const | primary,idx_t3_id | primary | 4 | const | 1 | using index |
| 3 | dependent union | t3 | eq_ref | primary,idx_t3_id | primary | 4 | func | 1 | using where; using index |
|null | union result |
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+-
union result：union的结果 explain select name from car_info where id =100 union select name from web_car_brands where id =5; +------+--------------+----------------+-------+---------------+---------+---------+-------+------+----- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +------+--------------+----------------+-------+---------------+---------+---------+-------+------+-- | 1 | primary | car_info | const | primary | primary | 8 | const | 1 | | | 2 | union | web_car_brands | const | primary,id | primary | 4 | const | 1 | | | null | union result |
subquery：子查询中的第一个select语句 explain select name from car_info where id = (select id from web_car_series where id = 5); +----+-------------+----------------+-------+---------------+---------+---------+-------+------+----- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+-------------+----------------+-------+---------------+---------+---------+-------+------+------ | 1 | primary | car_info | const | primary | primary | 8 | const | 1 | | | 2 | subquery | web_car_series | const | primary | primary | 4 | | 1 | using index | +----+-------------+----------------+-------+---------------+---------+---------+-------+------+-----
dependent subquery：子查询中的第一个select，取决于外面的查询
explain select name from car_info where id in (select id from web_car_series where id = 5); +----+--------------------+----------------+-------+---------------+---------+---------+-------+------+- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+--------------------+----------------+-------+---------------+---------+---------+-------+------+ | 1 | primary | car_info | index | null | name | 768 | null | 145 | using where; using index | | 2 | dependent subquery | web_car_series | const | primary | primary | 4 | const | 1 | using index | +----+--------------------+----------------+-------+---------------+---------+---------+-------+-----
derived：在from列表中包含子查询，mysql会递归的执行该子查询，并把结果放在临时表中