常用到的SQL优化问题

1.不要使用select *

这个想必大家都知道,因为我们使用select *的时候，数据库需要解析更多字段，权限，属性 ，可能你就有些字段根本就没有使用，而且当数据库的SQL比较复杂，数据量比较大的时候，都会给数据库造成严重的负担；

select * from sc; //尽量不要这么使用

select Sno,Cno,Grade from sc; //正确做法

2. 使用in不要用exists

使用in不推荐exists，要用小数据集推动大数据集

• 比如说我有两张表，一张sc表，一张course表，course表中的数据有100w条，而sc的数据只有1000条；而我想查询出符合条件的course表中数据集
• 如果使用exists:

select  * from course 
where
 EXISTS
 (select * from sc where course.Cno=sc.Cno and sc.Grade>90)  

• 如果使用in：

select  * from course
  where 
course.Cno in(select sc.Cno from sc where  sc.Grade>90)

相比于exists，in的好处:

exists是先全表查询course，先查询100w数据，然后匹配查询sc，这样就会给数据库造成很大的压力；
而in是先查询（）里面的，查询出条件，然后根据条件匹配course表中符合条件的数据集，这样相对于exists而言，会更好的符合SQL优化的原则；

3. 批量插入

如果说我们在java中做批量插入，以下的做法是错误的:

for(Order order:list){
OrderMapper.insert(order);
}

  这样会造成java和数据库的频繁交互，给数据库和java都会造成不小的压力；

• 正确的做法：

//使用Mybatis支持的insertBatch方法
orderMapper.insertBatch(list);

<insert id="insertBatch" parameterType="java.util.List">
    insert into order (field1, field2) values
    <foreach collection="list" item="t" index="index" separator=","> 
        (#{t.field1}, #{t.field2})
    </foreach>
</insert>

4.使用limit做查询限制

一般我们查询，都会做分页查询，但是如果说我们内部需要做一个list或者query接口，虽然可能没有做强制要求，但是我们也可以限制一下一次性返回的数据，不能超过1000条，因为有size，我们才能避免内存泄漏；

select * from course limit  1000

5.分页查询优化

一般我们做分页查询使用limit来实现，但是页数pageNo不断变大，越往后查询效率会越来越慢；
就比如说，一开始是这样：

SELECT * FROM articles 
WHERE category_id = 123 
ORDER BY id 
LIMIT 50, 10;

随着数据量的增加，页数会越来越多，查看后几页的 SQL 就可能类似：

SELECT * FROM articles
 WHERE category_id = 123
  ORDER BY id 
  LIMIT 10000, 10;

 越往后分页，LIMIT 语句的偏移量就会越大，速度也会明显变慢。

那么我们可以通过前端传递给我们上次分页查询的最大的id返回给我们，然后在此数据后面重新做分页查询;

 //这里的10000假设为上次传递给我们最后一条数据的自增主键id
SELECT * FROM articles
 WHERE category_id = 123 and id>10000 
  ORDER BY id 
  LIMIT 0, 10;

7.能过滤更多数据集的条件放在最前面

就比如说，我有一个user表，我想查询家庭地址为常德的，然后没有删除的人员信息：

select * from user where address='常德' and is_delete=0;

这样的好处就是，通过address我们可以过滤出更多的数据集，可能通过第一个条件后，满足条件的数据集就只有几百条，然后再层层过滤，效率会越来越明显；