聊聊sql优化的15个小技巧，太赞了( 二 ) _sql优化

5 多用limit有时候，我们需要查询某些数据中的第一条，比如：查询某个用户下的第一个订单，想看看他第一次的首单时间。
反例：
select id, create_datefrom order where user_id=123 order by create_date asc;根据用户id查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单集合。然后在代码中，获取第一个元素的数据，即首单的数据，就能获取首单时间。
List<Order> list = orderMapper.getOrderList();Order order = list.get(0);虽说这种做法在功能上没有问题，但它的效率非常不高，需要先查询出所有的数据，有点浪费资源。
那么，如何优化呢？
正例：
select id, create_datefrom order where user_id=123 order by create_date asc limit 1;使用limit 1，只返回该用户下单时间最小的那一条数据即可。

此外，在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在sql语句最后加上limit 。

例如：
update order set status=0,edit_time=now(3) where id>=100 and id<200 limit 100;这样即使误操作，比如把id搞错了，也不会对太多的数据造成影响。
6 in中值太多对于批量查询接口，我们通常会使用in关键字过滤出数据。比如：想通过指定的一些id，批量查询出用户信息。
sql语句如下：
select id,name from categorywhere id in (1,2,3...100000000);如果我们不做任何限制，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。
这时该怎么办呢？
select id,name from categorywhere id in (1,2,3...100)limit 500;可以在sql中对数据用limit做限制。
不过我们更多的是要在业务代码中加限制，伪代码如下：

public List<Category> getCategory(List<Long> ids) {if(CollectionUtils.isEmpty(ids)) {return null;}if(ids.size() > 500) {throw new BusinessException("一次最多允许查询500条记录")}return mapper.getCategoryList(ids);}

还有一个方案就是：如果ids超过500条记录，可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回。
不过这只是一个临时方案，不适合于ids实在太多的场景。因为ids太多，即使能快速查出数据，但如果返回的数据量太大了，网络传输也是非常消耗性能的，接口性能始终好不到哪里去。
7 增量查询有时候，我们需要通过远程接口查询数据，然后同步到另外一个数据库。
反例：
select * from user;如果直接获取所有的数据，然后同步过去。这样虽说非常方便，但是带来了一个非常大的问题，就是如果数据很多的话，查询性能会非常差。
这时该怎么办呢？
正例：
select * from user where id>#{lastId} and create_time >= #{lastCreateTime} limit 100;按id和时间升序，每次只同步一批数据，这一批数据只有100条记录。每次同步完成之后，保存这100条数据中最大的id和时间，给同步下一批数据的时候用。
通过这种增量查询的方式，能够提升单次查询的效率。
8 高效的分页有时候，列表页在查询数据时，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理。
在MySQL中分页一般用的limit关键字：
select id,name,age from user limit 10,20;如果表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。
比如现在分页参数变成了：
select id,name,age from user limit 1000000,20;mysql会查到1000020条数据，然后丢弃前面的1000000条，只查后面的20条数据，这个是非常浪费资源的。
那么，这种海量数据该怎么分页呢？
优化sql：
select id,name,age from user where id > 1000000 limit 20;先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的。
还能使用between优化分页。
select id,name,age from user where id between 1000000 and 1000020;需要注意的是between要在唯一索引上分页，不然会出现每页大小不一致的问题。
9 用连接查询代替子查询mysql中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询和连接查询。
子查询的例子如下：
select * from orderwhere user_id in (select id from user where status=1)