不要这样写SQL 改掉这些坏习惯

发表于:2018-10-26 10:51

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:会技术的葛大爷    来源:今日头条

分享:
  SQL是作为一个程序员接触得非常多的一种语言,但是,很多时候,我们会发现,有些SQL的执行效率异常的差,造成了数据库的负担。我们通过分析这些有问题的SQL,就可以发现很多我们平时在写SQL的时候忽略的问题。
  今天,我们就来讲一下这些需要改掉的坏习惯。
  尽量少用负向条件查询
  假设我们有一个Order表,表中有一个字段是Status,这个字段有4个值,分别是0=待支付、1=待发货、2=待收货、3=已完成。
  这时,我们要查询所有已经支付的订单,很多人就会写这样的SQL:
  select * from Order where Status != 0
  这就是一个不好的习惯了。负向条件查询(例如:!=、not in、not exists)都是不能使用索引的,当Order表中的数据到达一定量级时,这个查询的效率会急剧的下降。
   
  所以,正确的写法应该是:
  select * from Order where Status in (1,2,3)
  尽量少用前导模糊查询
  假设我们现在要根据用户的订单号(OrderNo)查询用户的订单,如果是直接通过SQL查询的话,尽量不要使用前导模糊查询,也就是:
  select * from Order where OrderNo like '%param'
  或者
  select * from Order where OrderNo like '%param%'
  因为,前导模糊查询是无法命中索引的,所以,会整个数据库去检索,效率相当的差,而非前导模糊查询则是可以使用索引的。
   
  因此,我们尽量不要把通配符放在前面,改成下面这样:
  select * from Order where OrderNo like 'param%'
  
  尽量不要在条件字段上进行运算
  假设,现在有一个需求,是要查询2018年全年的订单数据,我们就需要通过创建时间(CreateTime)来进行检索,但是,有些程序员就喜欢这样写SQL: 
select * from Order where Year(CreateTime)=2018
  然后,每次执行时就会发现,查询的速度异常的慢,导致了大量的请求挂起甚至超时。这是因为,我们即使在CreateTime上建立了索引,但是,如果使用了运算函数,查询一样会进行全表的检索。
  
  所以,我们可以改成这样:
  select * from Order where CreateTime > '2018-1-1 00:00:00'
  当查询允许Null值的列时,需要特别注意
  我们在创建表的字段时,如果这个字段需要作为索引时,尽量不要允许Null。因为,单列索引不会存Null值,复合索引不存所有索引列都为Null的值,所以如果列允许为Null,可能会得到“不符合预期”的结果集。
  例如:我们有一个User表,其中有UserName字段记录了用户的名字,并且添加了索引。
  
   
  现在我们执行了这样一个查询:
  select * from User where UserName != '小倩'
  但结果是这样的
   
  
   
  那位UserName为Null的数据并没有能包括进来。因此,如果我们想要包含这个用户的话,最好能够设置一个默认值。
  复合索引,使用时要注意顺序
  登录,肯定是我们使用得最多的一个查询了,为了保证效率,我们为LoginID和Password加上了复合索引。
  当我们使用
  select * from User where LoginID = '{LoginID}' and Password = '{Password}'select * from User where Password = '{Password}' and LoginID = '{LoginID}'
  查询时,都是能够准备的命中索引。当我们使用:
  select * from User where LoginID = '{LoginID}'
  查询时,也是能够命中索引的。但是,当我们使用
  select * from User where Password = '{Password}'
  查询时,确无法命中索引,这是什么原因呢?
  这是由于,复合索引对于查询的顺序是非常的铭感的,所以,符合索引中包含了几种规则,其中就有全列匹配和最左前缀匹配。
  当所有列都能够匹配时,虽然查询的顺序上有不同,但是查询优化器会将顺序进行调整,以满足适合索引的顺序,所以,顺序的颠倒是没有问题的。
  
  但是,如果所有列不能匹配时,就必须满足最左前缀匹配了,也就是,必须按照从左到右的顺序进行排列。因此,当我们建立是索引是
  结果唯一时,别闷着
  通常,我们设计User表时,并不会把LoginID作为主键,但是,LoginID确会在业务逻辑中验证唯一性,因此,如果使用
  select * from User where LoginID = '{LoginID}'
  查询时,结果一定只有一条。但是,数据库是不知道的,即使找到了这唯一的一条结果,他也会一直继续,直到扫描完所有的数据。
  因此,在执行这样的查询时,我们可以优化一下,改成:
  select * from User where LoginID = '{LoginID}' limit 1
  这样,当查询到结果时,就不会再继续了。
  最后,上面所有的例子都是坑
  尽量少用或别用Select *,我们的查询其实都是有目的的,就好像登录一样,我们其实只需要知道有结果返回就行了,使用select count(0)就可以了,但是我们使用select * 的话,就会消耗大量无效的数据库内存。
   
   上文内容不用于商业目的,如涉及知识产权问题,请权利人联系博为峰小编(021-64471599-8017),我们将立即处理。
《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号