常见提高SQL执行效率示例

迷南。 2023-09-27 18:29 141阅读 0赞

**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);

![524dce463af5423490e6495b9af81b1b.jpeg][]

**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);

[524dce463af5423490e6495b9af81b1b.jpeg]: https://img-blog.csdnimg.cn/524dce463af5423490e6495b9af81b1b.jpeg