mysql sql优化之expain

21312190p

                      explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

1. id

  SELECT识别符。这是SELECT查询序列号。查询序号即为sql语句执行的顺序

2.select_type

  select类型，它有以下几种值

        2.1 simple 它表示简单的select,没有union和子查询

        2.2 primary 最外面的select,在有子查询的语句中，最外面的select查询就是primary,上图中就是这样

        2.3 union  带有union语句的第二个sql或者说是后面那一个sql使用union.
        2.4 dependent union    UNION中的第二个或后面的SELECT语句，取决于外面的查询
        2.5 union result        UNION的结果

3 table

  输出的行所用的表

4 type        （重要）

        连接类型。有多个参数，先从最佳类型到最差类型介绍 重要且困难

        4.1 system        表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计

        4.2 const         表最多有一个匹配行，const用于比较primary key 或者unique索引.因为只匹配一行数据，所以很快
                                一定是用到primary key 或者unique，并且只检索出两条数据的 情况下才会是const
        4.3 eq_ref        对于eq_ref的解释:对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY
                                eq_ref可以用于使用=比较带索引的列。看下面的语句explain select * from uchome_spacefield,uchome_space where uchome_spacefield.uid = uchome_space.uid
                                (第二个语句从前面表的行中读取一行，用到UNIQUE或PRIMARY KEY索引)
        4.4 ref         对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。
                                果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。
        4.5 ref_or_null 该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

上面这五种情况都是很理想的索引使用情况

        4.6 index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

        4.9 range 给定范围内的检索，使用一个索引来检查行。看下面两条语句

                explain select * from uchome_space where uid in (1,2)

                explain select * from uchome_space where groupid in (1,2)

                uid有索引，groupid没有索引，结果是第一条语句的联接类型是range,第二个是ALL.
        4.10 index    该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
                                （也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
        4.11  ALL  对于每个来自于先前的表的行组合，进行完整的表扫描。
                        如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

5 possible_keys 提示使用哪个索引会在该表中找到行，不太重要

6 keys         MYSQL使用的索引，（重要）

7 key_len         MYSQL使用的索引长度

8 ref           ref列显示使用哪个列或常数与key一起从表中选择行。

9 rows         显示MYSQL执行查询的行数，数值越大越不好，说明没有用好索引  （重要）

10 Extra  该列包含MySQL解决查询的详细信息。 （重要）

        10.1 Distinct     MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行
        10.2 Not exists
        10.3 range checked for each record

        没有找到合适的索引

        10.4 using filesort  （需要优化）
        10.5 using index         否使用了索引
        10.6 using temporary  （需要优化）

        出现using temporary就说明语句需要优化了，举个例子来说


EXPLAIN SELECT ads.id FROM ads, city WHERE   city.city_id = 8005   AND ads.status = 'online'   AND city.ads_id=ads.id ORDER BY ads.id desc

id  select_type  table   type    possible_keys   key      key_len  ref                     rows  filtered  Extra                          
------  -----------  ------  ------  --------------  -------  -------  --------------------  ------  --------  -------------------------------
     1  SIMPLE       city    ref     ads_id,city_id  city_id  4        const                   2838    100.00  Using temporary; Using filesort
     1  SIMPLE       ads     eq_ref  PRIMARY         PRIMARY  4        city.ads_id       1    100.00  Using where   



这条语句会使用using temporary,而下面这条语句则不会


EXPLAIN SELECT ads.id FROM ads, city WHERE   city.city_id = 8005   AND ads.status = 'online'   AND city.ads_id=ads.id ORDER BY city.ads_id desc

id  select_type  table   type    possible_keys   key      key_len  ref                     rows  filtered  Extra                     
------  -----------  ------  ------  --------------  -------  -------  --------------------  ------  --------  ---------------------------
     1  SIMPLE       city    ref     ads_id,city_id  city_id  4        const                   2838    100.00  Using where; Using filesort
     1  SIMPLE       ads    eq_ref  PRIMARY         PRIMARY  4        city.ads_id       1    100.00  Using where   

它俩之间只是一个order by不同，MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。EXPLAIN 结果中，第一行出现的表就是驱动表（Important!）以上两个查询语句，驱动表都是 city，如上面的执行计划所示！

对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序（Important!）因此，order by ads.id desc 时，就要先 using temporary 了！


驱动表的定义
当进行多表连接查询时， [驱动表] 的定义为：
1）指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]；
2）未指定联接条件时，行数少的表为[驱动表]（Important!）。
永远用小结果集驱动大结果集 当不确定是用哪种类型的join时，让mysql优化器自动去判断，我们只需写select * from t1,t2 where t1.field = t2.field

        10.7 using where
                        WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，
                （这个说明不是很理解，因为很多很多语句都会有where条件，而type为all或index只能说明检索的数据多，useing where不是很重要，但是很常见）



如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。

        10.8 Using sort_union(...), Using union(...),Using intersect(...) 这些函数说明如何为index_merge联接类型合并索引扫描

        10.9 Using index for group-by        类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。