SQL优化

对于SQL的优化，以及type解释

SQL可能很多时候无从下手，或者业务所致，那真的没办法了，但是也可以从解释着手试一下，具体优化步骤如下。

• EXPLAIN（解释）关键字的使用，在SQL语句的前面加上关键字EXPLAIN进行SQL解释，如下：

                 

• 执行正常查询操作，完成查询后会出现如下视图

            

• ID列代表执行顺序，select_type列代表SQL组成结构，table列代表table，type列代表SQL查询级别，决定性的SQL快慢，key列代表是否使用到索引

 

• ID说明
  说下这里的规则，规则有两种情况，

                 （1）ID列规则为ID相同从上往下，执行顺序，所以这个SQL执行顺序是r表第一，m表第二执行

                 （2）若ID列ID不同则执行顺序是ID大的先执行，ID小的后执行，同时ID相同的情况规则与（1）相同，所以下图的执行顺序是2,2,1分别对应的table列是（r表）->（m表）->（<derived2>表）

                    

• table说明

          table就是表名，重命名的表名，或者临时表名，或者原表名，这里的derivedx(x）x是个数字,我的理解是第几步执行的结果,如<derived2>意思就是第二步执行到衍生表

• • type（重点说明的）
    type有如下几种情况：

    all	index	range	ref	eq_ref	const
    将遍历全表来找到匹配的行	遍历全部索引树来找到行 与all的区别在于all是从硬盘中读 index是从索引中读	检索给定范围的行	查找和扫描的混合体 非唯一索引扫描	唯一索引扫描，常见于主键或唯一索引	索引一次就找到，言外之意就是表中只有一条数据
    6（执行速度最垃圾）	5	4	3	2	1

    一般情况优化到range级别，最好的是ref，eq_ref

  • 所以优化级别是   const->er_ref->ref->range->index->all

 

索引失效概况情况如下：

1. 在索引列上做任何操作（计算，函数，类型转换）
2. 减少select * 操作，尽量使用覆盖索引（索引列和查询列一致，不一致的情况下，覆盖索引的第一个列必须存在）
3. 使用 不等于（！=）或者（<>）的时候无法使用索引，会导致全表扫描
4. is null , is not null 无法使用索引，会导致全表扫描
5. like 以通配符开头('%abc....')MySQL索引则会失效，会变成全表扫描
6. 字符串不加单引号会索引失效

使用or连接会导致索引失效

• 拓展：

           order by 双路排序和单路排序

         引入场景：大家对磁盘都有所了解，特别是机械磁盘：磁盘主要组成部分就是“磁盘”和“磁头”  附图百度搜索得来的图片一张                                                          

         读数据是需要磁头移动找到相应的磁道上面去得到数据滴。

          order by 是排序，分解动作就是查找，排序

          双路排序：Mysql4.1之前，使用双路排序就是先从磁盘读取排序字段，在buffer进行排序，再从磁盘读取其他字段，这里面需要两次IO。

           单路排序：Mysql4.1之后，使用单路排序就是一次性从磁盘读取查询所需要的所有列，按照order by在sort_buffer对它进行排序，并且把随机IO变成了顺序IO，这里存在一个问题，就是如果我们使用的是select *  和使用select 具体所需要的字段其所使用的空间是不同的，后者使用相对较少空间，但若数据量太过庞大，sort_buffer空间又不够，导致的最后结果就还会变成多路排序，官方给出使用增大sort_buffer_size参数的空间可以进一步优化排序。