数据库sql查询优化

一、避免使用select

• 在实际的业务场景中，我们可能只需要一两列，不需要select * 去查询，因为这样网络IO数据传输的时间，会占用内存和消耗cpu资源。
  • 优化sql

      # 需要用多少列，就写多少列
              select [column1],[column2],......from TableName;
    1
    2

二、使用union all 代替 union

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

             select [column1],[column2] from table1 
             union all 
             select [column1],[column2] from table2;
    1
    2
    3

三、避免大表驱动小表

• 使用小表的数据集驱动大表的数据集，例如：小表：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);
    1
    2
    3

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

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

四、使用批量添加

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

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

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

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

五、多用limit

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

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

六、in 中的值不要太多

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

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

  • 优化后代码如下

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

七、增量查询

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

              select id,name from user where id > #{id} and create_date > #{create_date} limit 100;
    1

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

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

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

  • 表连接查询 [推荐]

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

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

• 使用group by查询，先用条件筛选数据，再用group by 分组查询
  • 优化后的代码如下：

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

  • 反例代码

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

加粗样式