达内广州C++学院|c++培训|广州达内科技C++/C#培训|.NET培训|IT培训|达内广州岗顶中心 达内广州C++学院|c++培训|广州达内科技C++/C#培训|.NET培训|IT培训|达内广州岗顶中心
java程序员
 当前位置:主页 > 高端课程 > java程序员 >

数据库查询SQL语句的时候如何写会效率更高?

时间:2019-05-15  来源:未知  作者:广州达内培训

引言

       以前刚开始做项目的时候,开发经验尚浅,遇到问题需求只要把结果查询出来就行,至于查询的效率可能就没有太多考虑,数据少的时候还好,数据一多,效率问题就显现出来了。每次遇到查询比较慢时,项目经理就会问:是不是又用select * 了?查询条件有没有加索引?一语惊醒梦中人,赶紧检查..果然如此!

有时我们写sql语句时,没有考虑它的性能或者完全没有优化意识,虽然能暂时满足基本功能,等到数据量大时,后果可想而知。

下面我们就聊一聊sql优化的一些常见方法:

1、sql语句模型结构优化指导

A. ORDER BY + LIMIT组合的索引优化
   SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT]; sort上简索引
B. WHERE + ORDER BY + LIMIT组合的索引优化
   SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];

   建立一个联合索引(columnX,sort)
C. WHERE+ORDER BY多个栏位+LIMIT
   SELECT * FROM [table] WHERE uid=1 ORDER BY x,y LIMIT 0,10; 建立复合索引(uid,x,y)

2、复合索引(形如(x,y,uid)索引的索引)

  select* from users where area =’beijing’ and age=22;
  建立索引(area,age,salary),其相当于创建了(area,age,salary),(area,age),(area)三个索引,这样称为最佳左前缀特性。

3、like语句优化

  SELECT id FROM A WHERE name like '%abc%'
  由于abc前面用了“%”,因此该查询必然走全表查询,除非必要,否则不要在关键词前加%,优化成如下
  SELECT id FROM A WHERE name like 'abc%'

4、where子句使用 != 或 <> 操作符优化

  在where子句中使用 != 或 <>操作符,索引将被放弃使用,会进行全表查询。
  如SQL:SELECT id FROM A WHERE ID != 5
  优化成:SELECT id FROM A WHERE ID>5 OR ID<5

5、尽量避免在 where 子句中对字段进行 null 值判断(IS NULL 或 IS NOT NULL),因为空判断将导致全表扫描,而不是索引扫描。对于空判断这种情况,可以考       虑对这个列创建数据库默认值。

   在where子句中使用 IS NULL 或 IS NOT NULL 判断,索引将被放弃使用,会进行全表查询。
   如SQL:SELECT id FROM A WHERE num IS NULL 优化成num上设置默认值0,确保表中num没有null值,
   然后SQL为:SELECT id FROM A WHERE num=0

6、where子句使用or的优化

   很多时候使用union all 或 nuin(必要的时候)的方式替换“or”会得到更好的效果。where子句中使用了or,索引将被放弃使用。
   如SQL:SELECT id FROM A WHERE num =10 or num = 20
   优化成:SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20

7、where子句使用IN 或 NOT IN的优化

  in和not in 也要慎用,否则也会导致全表扫描。
  方案一:between替换in
     如SQL:SELECT id FROM A WHERE num in(1,2,3)
     优化成:SELECT id FROM A WHERE num between 1 and 3
  方案二:exist替换in
    如SQL:SELECT id FROM A WHERE num in(select num from b )
    优化成:SELECT id FROM A WHERE num exists(select 1 from B where B.num = A.num)
  方案三:left join替换in
    如SQL:SELECT id FROM A WHERE num in(select num from B)
    优化成:SELECT id FROM A LEFT JOIN B ON A.num = B.num

8、where子句中对字段进行表达式操作的优化

   不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算,否则系统将可能无法正确使用索引。
   A:如SQL:SELECT id FROM A WHERE num/2 = 100
     优化成:SELECT id FROM A WHERE num = 100*2
   B:如SQL:SELECT id FROM A WHERE year(addate) <2016
     优化成:SELECT id FROM A where addate<'2016-01-01'

9、任何地方都不要用SELECT *

select * from table ,用具体的字段列表替换"*",不要返回用不到的字段

10、使用“临时表”暂存中间结果

采用临时表暂存中间结果好处:

      (1)避免程序中多次扫描主表,减少程序执行“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。

      (2)尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

      (3)避免频繁创建和删除临时表,以减少系统资源的浪费。

      (4)尽量避免向客户端返回大数据量,若数据量过大,应考虑相应需求是否合理。

11、limit分页优化 

  总数据有500万左右,以下例子
  select * from wl_tagindex where byname='f' order by id limit 300000,10;   行时间是 3.21s
  优化后:
     select * from (
         select id from wl_tagindex
           where byname='f' order by id limit 300000,10
     ) a
      left join wl_tagindex b on a.id=b.id
  执行时间为 0.11s 速度明显提升。
  这里需要说明的是 这里用到的字段是 byname ,id 需要把这两个字段做复合索引,否则的话效果提升不明显。

12、批量插入优化

   INSERT into person(name,age) values('A',14);
  INSERT into person(name,age) values('B',15);
  INSERT into person(name,age) values('C',16);
  优化为:INSERT into person(name,age) values('A',14),('B',15),('C',16);

13、利用limit 1 、top 1 取得一行

  有时要查询一张表时,你知道只需要看一条记录,你可能去查询一条特殊的记录。可以使用limit 1 或者 top 1 来终止数据库索引继续扫描整个表或索引。
  如SQL:SELECT id FROM A LIKE 'abc%'
  优化为:SELECT id FROM A LIKE 'abc%' limit 1

14、尽量不要使用 BY RAND()命令

BY RAND()是随机显示结果,这个函数可能会为表中每一个独立的行执行BY RAND()命令,这个会消耗处理器的处理能力。

15、排序的索引问题

  Mysql查询只是用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。
  因此数据库默认排序可以符合要求情况下不要使用排序操作;
  尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

16、尽量用 union all 替换 union

  union和union all的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的cpu运算,
  加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候,尽量使用union all而不是union

17、Inner join 和 left join、right join、子查询

  第一:inner join内连接也叫等值连接是,left/rightjoin是外连接。
      SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;
    SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
    SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;
    经过多方面的证实inner join性能比较快,因为inner join是等值连接,或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接,如:
    SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;
   推荐:能用inner join连接尽量使用inner join连接

  第二:子查询的性能又比外连接性能慢,尽量用外连接来替换子查询。


  第三:使用JOIN时候,应该用小的结果驱动大的结果(left join 左边表结果尽量小,如果有条件应该放到左边先处理,right join同理反向),同时尽量把牵涉到多表     联合的查询拆分多个query (多个表查询效率低,容易锁表和阻塞)。如:
    Select * from A left join B on A.id=B.ref_id where A.id>10;
   可以优化为:select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;

18、exist和 in的使用场景

  SELECT * from A WHERE id in (SELECT id from B)
  SELECT * from A WHERE id EXISTS(SELECT 1 from B where A.id= B.id)
  in是在内存 中遍历比较, exist 需要查询数据库,所以当B的数据量比较大时,exists效率优于in.
  in()只执行一次,把B表中的所有id字段缓存起来,之后检查A表的id是否与B表中的id相等,如果id相等则将A表的记录加入到结果集中,直到遍历完A表的所有记录。
  In 操作的流程原理如同以下代码
    List resultSet={};
     Array A=(select * from A);
     Array B=(select id from B);
     for(int i=0;i<A.length;i++) {
      for(int j=0;j<B.length;j++) {
        if(A[i].id==B[j].id) {
          resultSet.add(A[i]);
          break;
        }
      }
    }
   return resultSet;
   可以看出,当B表数据较大时不适合使用in(),因为它会将B表数据全部遍历一次
   如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差。
   再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升。
  结论:in()适合B表比A表数据小的情况

   exist()会执行A.length()次,执行过程如同以下代码
      List resultSet={};
     Array A=(select * from A);
     for(int i=0;i<A.length;i++) {
      if(exists(A[i].id) { //执行select 1 from B where B.id=A.id是否有记录返回
        resultSet.add(A[i]);
      }
    }
   return resultSet;
   当B表比A表数据大时适合使用exists(),因为它没有那么多遍历操作,只需要再执行一次查询就行。
   如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。
   当B表比A表数据大时适合使用exists(),因为它没有那么多遍历操作,只需要再执行一次查询就行。

  如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。

  如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果。

  再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,

  我们都知道查询数据库所消耗的性能更高,而内存比较很快。

  结论:exists()适合B表比A表数据大的情况

  当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用。

19、索引的合理运用

where子句及order by涉及的列尽量建索引,不一定要全部建索引,依业务情形而定。对于多条where子句都用到的列,建议建索引。索引并不是越多越好,

索引固然可以提高相应的select的效率,但同时也降低了insert及update 的效率。

20、先分页再查询

对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。

21、统计的时候尽量标明列

select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。

 可以改为select count(id) from table。

22、列能使用数字型就不要用字符型

尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。

23、列的类型尽量使用可变的varchar

尽可能的使用varchar代替char,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

 暂时先就这些了,后续有补充的在加上!




上一篇:Struts2 源码分析-----工作原理分析
下一篇:Spring Cloud(3):Ribbon的使用

友情链接:
  • 全球最大晶圆代工半导体制造厂,台积电斥资订购艾斯摩尔机器设备
  • 英特尔依然是那个英特尔,且看英特尔的城防体系
  • 支持双 DRAM 内存接口,慧荣企业级 SSD 主控方案披露
  • 在全球被反垄断罚款,冤!高通到底哪里得罪了欧盟?
  • 强强联合!万业、微电子所和芯鑫共同打造全新半导体设备
  • 复旦大学校长称:对于集成电路产业发展,大学应该主动担当
  • 技术再升级!无锡中科芯攻克晶圆级再布线及晶圆级凸点制备关键技
  • 聚力!万业企业设立集成电路装备集团,提供自主可控设备
  • 德州仪器C2000微控制器增强连通性和控制性
  • 英特尔打出降价策略,以免被竞争对手 AMD 打败?
  • 贸易摩擦的闹剧没有赢家,苹果有勇气离开中国吗?
  • 图像信号与视觉处理器的发展趋势
  • 真干快消品定位方案班(第二期)火热开班
  • Java集合 ArrayList原理及使用
  • TDD(测试驱动开发)死了吗?
  • JAVA基础之XML相关
  • javaweb项目搭建ehcache缓存系统
  • 每日一码——字符串统计
  • 一篇文章帮你彻底搞清楚“I/O多路复用”和“异步I/O”的前世今生
  • 九:模板方法模式
  • 十二:命令模式(人员解耦和)
  • Java 转PPT为图片、PDF、SVG、XPS、ODP以及PPT和PPTX互转
  • SpringCloud学习(SPRINGCLOUD微服务实战)一
  • 记一次微信网页授权后获取用户信息并重定向
  • 速途新营销五点实战洞察解码“品效合一”
  • 十一:外观模式详解(Service,action与dao)
  • 手把手教你学会 基于JWT的单点登录
  • mysql锁机制总结,以及优化建议
  • 解决多个版本jar包冲突【jar内包名重命名】
  • 中国首张5G终端电信设备进网许可证 华为Mate 20 X 5G版入网
  • RPC之Thrift
  • 高级Java工程师必备 ----
  • 天猫618期间实物支付GMV增长38.5%
  • 换季了,老板你的库存处理好了吗?
  • 从“618”大数据看中国消费新活力
  • 小米生态链:贵在格局感与收放度
  • CODING 2.0 企业级持续交付解决方案
  • 老铁奇趴“新京济” 快手*京东618战报出炉
  • 中小企业新媒体运营基本技能
  • 上汽大通房车再度携手LINE FRIENDS 魔都巡游顺利开启
  • 华为高端手机国内市场份额超苹果夺得榜首
  • 中国智能制造分析报告
  • iPlus艾加营销助力腾讯广告牵手吴晓波 推进商业IP变现
  • 2019世界新能源汽车大会7月1日将在海南举行
  • 区域酒企如何转型突围
  • 时时彩论坛
  • 五星体育斯诺克
  • 北单比分直播
  • 河北11选5走势图
  • 福建体彩36选7开奖结果
  • 九龙图库下载