mysql查询优化相关技巧

使用EXPLAIN语句检查优化器操作 +----+-------------+----------+-------+---------------+------+---------+------+------+----------------- | id | select_type | table | type | possible_keys | key | key_len| ref | rows | Extra +----+-------------

　　使用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会递归的执行该子查询，并把结果放在临时表中