mysql索引

mysql索引详细

一 执行计划

explain

1.字段解释
    a.Id(重要)
        id相同：执行顺序由上至下
        id不同：id值越大优先级越高,越先被执行
2.Type
    当我们发现执行SQL的type是range,index,all就需要对SQL进行优化
3.key_len
    当索引长度越大越好 使用索引字段越多
4.Rows
    扫描行数越少越好
5.Extra(重要)
    a.Using filesort
        意味着order by后面的字段 没有建立索引 使用了手工排序 要你命三千
    b.Using temporary
        当sql中使用了group by 如果后面的字段没有创建索引 就会出现Using temporary，Using filesort 要你命三万
    c.using join buffer
        要你命三千

二 索引优化案例

1.创建函数和大表
2.把表里面的非主键索引进行删除
3.索引的使用原则(单表)
    a.全值匹配我最爱
    b.最佳左前缀法则
        带头大哥不能死 中间兄弟不能断
    c.索引失效的情况有哪些
        索引列不要做任何操作
          使用了不等于
           使用了IS NOT NULL
            like通配符以%开头
            类型转换
             如果使用的是范围查询丢失一部分索引
              如果有范围查询 最好把范围字段放到最后
4.一般性建议
    a.对于单键索引，尽量选择针对当前query过滤性更好的索引(身份证号，手机号)
    b.在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。这样过滤后面数据就会更少
    c.在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引----全值匹配我最爱
    d.在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
    e.书写sql语句时，尽量避免造成索引失效的情况
5.关联表查询优化
    a.当我们使用left join的时候
        驱动表(left join左边的表) 是没有办法避免全表扫描
        被驱动表是可以的(相应字段创建索引)
    b.当我们使用inner join的时候
        mysql会帮你找到有索引的表作为被驱动表
6.总结
    a、保证被驱动表的join字段已经被索引
    b、left join 时，选择小表作为驱动表，大表作为被驱动表。
    3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
    4、子查询尽量不要放在被驱动表，有可能使用不到索引。
    5、能够直接多表关联的尽量直接关联，不用子查询。
7.排序优化
    a.无过滤不索引
    b.顺序错，必排序
    c.方向反必排序
8.分组优化
    b.顺序错，必排序
    c.方向反必排序