文章目录
一、避免使用select
- 在实际的业务场景中,我们可能只需要一两列,不需要select * 去查询,因为这样网络IO数据传输的时间,会占用内存和消耗cpu资源。
- 优化sql
# 需要用多少列,就写多少列 select [column1],[column2],......from TableName;
- 优化sql
二、使用union all 代替 union
- 使用union关键字获取到数据都是排重后的数据;而使用union all 可以获取到全部数据,包括重复的数据。数据排重的过程需要数据遍历、排序和比较,这样会更耗时和消耗cpu资源。
- 优化sql
select [column1],[column2] from table1 union all select [column1],[column2] from table2;
- 优化sql
三、避免大表驱动小表
- 使用小表的数据集驱动大表的数据集,例如:小表: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;
- 优化后的代码如下:
加粗样式