数据库sql查询优化-优快云博客

文章目录

一、避免使用select

在实际的业务场景中，我们可能只需要一两列，不需要select * 去查询，因为这样网络IO数据传输的时间，会占用内存和消耗cpu资源。
- 优化sql
```
  # 需要用多少列，就写多少列
          select [column1],[column2],......from TableName;
```

二、使用union all 代替 union

使用union关键字获取到数据都是排重后的数据；而使用union all 可以获取到全部数据，包括重复的数据。数据排重的过程需要数据遍历、排序和比较，这样会更耗时和消耗cpu资源。
- 优化sql
```
         select [column1],[column2] from table1 
         union all 
         select [column1],[column2] from table2;
```

三、避免大表驱动小表

使用小表的数据集驱动大表的数据集，例如：小表：user表有100条数据，大表：order表有10000数据，这时前端需要用户的所有的订单数据：

大表驱动小表的方式

          select * from order where userid in (select userid from user where status = 1);
          或者
          select * from order a where exists (select 1 from user b where a.userid = b.userid);

小表驱动大表的方式[推荐]

          #最优的sql
          select * from user a where  exists (select 1 from order b where a.userid = b.userid);

四、使用批量添加

避免一条一条数据的添加，代码如下：

          foreach(var item : list)
          {
              ## 添加
          }
          #对应的sql
          insert into order (id,order_name,user)
          values(1,'23234','小明');

应该使用批量的添加的方法，代码如下：

      insert into order (id,order_name,user)
          values(1,'23234','小明')(2,'23234','小明2')(3,'23234','小明3');

五、多用limit

在我们开发的过程中，我们需要查询用户最晚的一个订单和首单的时间；

代码如下

          select id,create_date from order where user_id = 123 order by create_date desc limit 1;

六、in 中的值不要太多

对于批量查询的接口，我们经常会用in 来过滤一些数据，但是in中的参数过多，查询性能过慢，很容易造成接口超时；

反例代码如下

          select id,name from user where id ('1','2','3',......,'100');

优化后代码如下

         # 使用limit来闲着查询的数量或者使用分页的方式来控制，这样的性能会达到最佳
           select id,name from user where id ('1','2','3',......,'100') limit 500;

七、增量查询

如果获取全部的数据，然后同步过去，这样虽说很方便，但是查询的性能会很差，所以我们可以使用增量查询，例如：根据ID和时间进行排序，每次只同步一批数据。每次同步完成之后，我们会保存数据中最大的ID和时间，给同步下批的数据使用;通过这种增量查询，能够提升单词查询的性能；
- 代码如下
```
          select id,name from user where id > #{id} and create_date > #{create_date} limit 100;
```

八、使用连接语句代替子查询

当我们查询两张表已以上的数据时，我们经常使用的手段时使用表连接查询和子查询；

子查询

          # 子查询时通过in关键字实现，一个查询语句中的条件落在另一个select查询结果中，程序先运行在嵌套在最内层的语句，再去运行最外层的查询语句。
          select * from user where id in (select user_id from order where status = '1');
          # 优点
            - 简单、结构化
          # 缺点
            - 执行子查询时，内部会创建临时表，查询完毕后会删除临时表数据，这样会消耗性能。

表连接查询 [推荐]

          select * from user left join order on id = order.user_id and status = '1';

九、优化group by提升查询效率

使用group by查询，先用条件筛选数据，再用group by 分组查询

优化后的代码如下：

          select  sex,username from user where age <=18 group by sex,username;

反例代码

          select  sex,username from user  group by  sex,username where age<=18;

加粗样式