优化MYSQL查询语句——各个语法的优化建议

参考了 深入浅出MYSQL --第2版

SQL尽量不要使用SELECT * ，只使用必要的字段

一、OR 
1、对于 OR 两边的字段，如果有一个字段是非索引字段，那么另一个也不会走索引
2、最好的优化方式是，OR 的各个字段单独建立索引，这样会进行index_merge 也就是UNION 操作，效率较高

二、分页 limit ?.?
   select id ,ziduan1 from file order by time limit 50,5
     这个会做全表扫描
    优化：
    select a.id ,a.ziduan1 from file a inner join(select id from file order by time limit 50,5) b on a.id = b.id
     这样会走index扫描，效率可以有所提高

三、GROUP BY
   默认情况下，MYSQL对group 的字段会进行排序（order by），因此即使加上ORDER by 相同的字段，也不会影响性能
   explain select id,time from file group by time
  extra: using temoary;using filesort  
  这个filesort就是排序 
   优化：
   如果不需要排序的话，可以指定：ORDER BY NULL
   explain select id,time from file group by time order by null

四、ORDER BY
   1、尽量使用覆盖索引，WHERE 条件和ORDER BY 使用相同的索引，并且ORDER BY 的顺序和索引顺序相同，并且ORDER BY 的字段都是升序或者都是降序
    2、ORDER BY 字段使用聚簇索引
    3、适当扩大 max_length_for_sort_data, sort_buffer_size

五、子查询优化
  1、尽量使用JOIN来替代子查询，JOIN更有效率
  2、NOT IN优化
   查询A表中在B表中不存在的ID
   select id from customer where id not in (select id from payment)
   这个语句只能使用一个表的索引
   优化：
   select id from customer a left join payment b on a.id=b.id where b.id is null

六、like 优化 
   以%开头的LIKE查询不能使用B-Tree索引
   select * from vehicle where hphm like '%M12%'
   可以考虑优化：
   select * from (select hphm from vehicle where hphm like '%M12%') a, vehicle b where a.hphm = b.hphm