常见提高SQL执行效率示例

1、减少与数据库的交互：

        当传参是一个list集合时，通过遍历查询数据库会造成数据库压力过大，这样查询效率就比较低，应该减少与数据库的交互来提高查询效率：(这里的objId为“ljydl”,"ljtpfl")

QueryWrapper<IndexInfoDto> ew = new QueryWrapper<>();
ew.in("i.OBJ_ID",qo.getObjIds());
List<IndexInfoDto> reatimePowerInfo = baseMapper.getIndexInfoById(ew);
Map<String, List<IndexInfoDto>> map=reatimePowerInfo.stream().collect(Collectors.groupingBy(IndexInfoDto::getObjId));

@Select("select i.OBJ_ID,i.OBJ_NAME,i.OBJ_VALUE from index_info i ${ew.customSqlSegment}")
List<IndexInfoDto> getIndexInfoById(@Param(Constants.WRAPPER) QueryWrapper<IndexInfoDto> ew);

 2、优化查询条件：

        在对同一个表使用多个条进行查询时，应将条件强的，即按照条件返回结果集的行数少的条件放在前面。减少下一个查询条件进行查询时。扫描的行数，提高查询效率。

//原始语句
SELECT *
FROM orders
WHERE order_date > '2022-01-01' AND order_status = 'completed';

//优化后的查询语句
SELECT *
FROM orders
WHERE order_status = 'completed' AND order_date > '2022-01-01';

3、小表驱动大表，用小表的数据集驱动大表的数据集：

        假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。此时我们想要查询所有有效的用户下过的订单列表。

（1）可以使用in关键字实现：

select 字段,字段,字段,字段 from order where user_id in (select id from user where status=1)

（2）使用exists关键字实现：

select  字段,字段,字段,字段 字段,字段,字段,字段 字段,字段,字段,字段 from order 
where exists (select 1 from user where order.user_id = user.id and status=1)

前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适，原因如下：

        如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。

        如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

        这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。

in 适用于左边大表，右边小表。

exists 适用于左边小表，右边大表。

4、使用 JOIN 替代子查询：

//原查询语句
SELECT *
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE order_date > '2022-01-01'
);

//优化后查询语句
SELECT customers.*
FROM customers
JOIN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date > '2022-01-01'
) AS recent_orders
ON customers.customer_id = recent_orders.customer_id;

5、避免使用 SELECT *

        在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存、cpu或者IO的数据传输时间。

        select *不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。

6、批量操作

        如果有一批数据经过业务处理之后，需要插入数据，该怎么办，我们不可能通过循环一条条的插入数据库，因为每次远程请求数据库，是会消耗一定性能的。而如果我们的代码需要请求多次数据库，才能完成本次业务功能，势必会消耗更多的性能。优化方式如下：

orderMapper.insertBatch(List<User> users): 

insert into order(id,code,user_id)  values(123,'001',100),(124,'002',100),(125,'003',101);

<insert id="insertOrderList">
        insert into order(id,code,user_id) values
        <foreach item="item" index="index" collection="list" separator=",">
            (#{item.id},#{item.code},#{item.user_id})
        </foreach>
</insert>

7、多用limit

        只返回我们想要的某一条或者某几条数据即可。在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在sql语句最后加上limit。

8、提升group  by 的执行效率

        我们有很多业务场景需要使用group by关键字，它主要的功能是去重和分组。通常它会跟having一起配合使用，表示分组后再根据一定的条件过滤数据。

select user_id,user_name from order 
group by user_id having user_id <= 200; 

        这种写法性能不好，它先把所有的订单根据用户id分组之后，再去过滤用户id大于等于200的用户。分组是一个相对耗时的操作，我们可以先缩小数据的范围之后，再分组，这样执行效率就会进一步提高。

select user_id,user_name from order
where user_id <= 200 
group by user_id 

        其实这是一种思路，不仅限于group by的优化。我们的sql语句在做一些耗时的操作之前，应尽可能缩小数据范围，这样能提升sql整体的性能。

9、插入更新操作（存在即更新、反之则插入）

@Insert("INSERT INTO info(OBJ_ID, CAPACITY) VALUES (#{map.objId},#{map.capacity}) ON DUPLICATE KEY UPDATE CAPACITY = values(CAPACITY)" )
int insertOrUpdateInfo(@Param("map") Map<String,Object> map);