mysql索引优化

Explain: 使用该关键词可以进行索引分析

关键字：filtered ，partitions ，id ，select_type ，type ，possible_keys ，key ，key_len ，Extra

filtered:rows*filtered/100 算出当前表和其他关联表的行数

partitions:分区表

id：执行的先后顺序，id越大，执行的优先级越高，否则越低

select_type:

    SIMPLE:简单查询

    primary：复杂查询中最外层的select

    suquery：子查询 在select后包起来的查询

    derived：派生查询 在from后包起来的查询

type：标识sql的访问或者关联类型，查找的大概范围

    依次从最优到最差：system>const>eq_ref>ref>range>index>ALL

    一般优化到range级别，最好到ref

    NULL:mysql在优化阶段分解查询，在执行阶段用不着再访问表或者索引，例：在索引中选取最小值，可以单独查找索引来完成，不需要再执行的时候访问表

    system:派生查询的结果只有一行记录

    const:查询条件用的主键或者唯一索引，类似于常量查询（select 1 from dual）

    以上两条查询记录只有一条匹配，则为const,system

    eq_ref：主键关联或者唯一键，使用eq_ref查询效率较高，不用优化

    ref：使用普通索引或者唯一索引的部分前缀就会为ref，非唯一索引

    range：范围查找，使用索引查出范围集，效率略低

    index：扫描全索引就能拿到结果，一般扫描某个二级索引，当查出的结果集在索引里面有，就是使用二级索引，因为二级索引小；如果没有，就是用聚簇索引，因为二级索引需要回表，效率更低

    ALL：走聚簇索引，包含全表的所有数据，全表扫描

possible_keys：存放该表的所有的索引

key：真正执行时用到的索引

key_len：可以看到索引用了哪些字段的长度（3n+2 n表示字段建立时的长度，如果字段允许为空，需要1字节记录是否为空）

Extra:

    查找的所有的结果集在索引树中全都能找到就是覆盖索引，不需要进行回表操作

    using index：覆盖索引

    using where： 用where查找没有走索引，就需要优化

    using index condition：查询的列如果不完全被索引覆盖 ,用到了最左原则

    using temporary： 用到临时表，例：distinct去重，可以优化可以优化为using index，给去重列加索引，就会使用覆盖索引

    using filesort：文件排序（内存排序，文件较大可能进行磁盘排序），可以优化为using index覆盖排序

 

优化：

1.全值匹配：查询条件中能使用联合索引字段越多越好，所需要的rows扫描的更少，效率更快

2.不要再索引列上进行任何操作（譬如：计算）

3.like匹配：‘test%’使用后%，或者使用覆盖索引优化

4.字符串不加单引号也会使索引失效，字段什么类型，结果查询就用什么类型

5.少用or或者in，mysql不一定走索引,数据量多的时候走索引，数据量少会进行全表扫描

 

优化总结：

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

1) order by语句使用索引最左前列。

2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上**order by null禁止排序**。注意，where高于having，能写在where中的限定条件就不要去having限定了。