mysql之explain调优

各个列的解释

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

     type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL      一般来说，得保证查询至少达到range级别，最好能达到ref。
|  ALL              |  全表扫描

|  index            |  索引全扫描

|  range            |  索引范围扫描，常用语<,<=,>=,between等操作

|  ref                |  使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中，即哪些列或常量被用于查找索引列上的值

|  eq_ref           |  类似ref，区别在于使用的是唯一索引，使用主键/唯一索引的关联查询。对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。

|  const/system  |  单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询

|  null                |  MySQL不访问任何表或索引，直接返回

 由上至下，效率越来越高

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。但坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

extraextra

1. Using index：用了覆盖索引

2. Using index condition：用了条件索引（索引下推）

3. Using where：从索引查出来数据后继续用where条件过滤

4. Using join buffer (Block Nested Loop)：join的时候利用了join buffer（优化策略：去除外连接、增大join buffer大小）

5. Using filesort：用了文件排序，排序的时候没有用到索引

    优化方法：
    1、修改逻辑，不在mysql中使用order by而是在应用中自己进行排序。
    2、使用mysql索引，将待排序的内容放到索引中，直接利用索引的排序。
   

6. Using temporary：用了临时表（优化策略：增加条件以减少结果集、增加索引，思路就是要么减少待排序的数量，要么提前排好序）

7. Start temporary, End temporary：子查询的时候，可以优化成半连接，但是使用的是通过临时表来去重

8. FirstMatch(tbl_name)：子查询的时候，可以优化成半连接，但是使用的是直接进行数据比较来去重

    亲测：
   
    group by会导致Using temporary; Using filesort，将分组字段使用索引即可解决
   
    order by会导致Using filesort，建立索引。数据量占大部分的情况下也会放弃使用索引。官网优化
   
    需要注意的是：由于 Using filesort是使用算法在 内存中进行排序，MySQL对于排序的记录的大小也是有做限制：max_length_for_sort_data，默认为1024。
   

导致索引失效的原因

	1. 随着表的增长，where条件出来的结果集数据太多，大于数据总量的15%，使得索引失效（会导致CBO计算走索引花费大于走全表）

	2. 统计信息失效，需要重新搜集统计信息

	3. 索引本身失效，需要重建索引

	在查询条件上没有使用引导列

	查询的数量是大表的大部分，应该是30％以上

	查询小表,或者返回值大概在10%以上

	隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误，varchar字段结果给出

	由于表的字段值超过定义的长度

	like "%_" 百分号在前

	单独引用复合索引里非第一位置的索引列

	字符型字段为数字时在where条件里不添加引号

	对索引列进行运算.需要建立函数索引

	not in ,not exist

	当变量采用的是time变量，而表的字段采用的是date变量时.或相反情况

	B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走