数据库方面总结

2019-11-03 08:34:55

字体：大中小

来源：转载

供稿：网友

SQL性能优化对于程序员来说是一个很大的挑战，因为我们往往会遇到这样的问题：当我们开发完一个项目，感觉功能上自己测试的体验真的很不错，但真正项目上线后，随着数据的海量增加，系统的客户体验越来越差。当然除过框架，代码不够合理外，主要还是SQL没能优化，而导致系统越来越慢。

       因为我在一个小公司，所以什么都干，我觉得有时候治标得治本！有以下几个问题得注意：

      1、数据库表的设计要合理，特别是主键的设计，如果表的数据量非常大，主键的设计不要让有实际意义，就像ROWID一样，比如SQLServer的GUID，Hibernate的UUID等。当然一些数据字典表可以灵活处理了，不必考虑非得是物理主键了。主键设计中，一般也不要用复合主键。

      2、合理的加索引。索引是加快我们数据查询速度的一把利器，是一种很好的手段。但不要见什么字段都加吧。索引的原理就像书的目录一样，如果你书的目录几乎全是一个名字，你可以想象以下，你自己根据目录找具体内容时能快吗？索引不一定要力求必须唯一，但也不要相同的记录太多了。再就是，索引加多了，TEMP表空间会增大，将表导出在导入另一个库时，由于索引也会降低导入的效率，此时你也会发现UNDOTBS01表空间异常的大。所以索引是一把双刃剑，一定要合理的应用。

     3、我曾经在网上看到过好过关于SQL优化的很专业的文章，但我感觉并没有因此而使自己能够在项目中用到这些。反而是自己在项目进行的过程中，不断地实验发现一些基本的原理。我自己认为SQL优化的原理只有一个那就是尽可能的缩小查询范围，这样效率肯定会有提高，而且Oracle本身对我们所写的SQL能够优化，所以我们要做的就是尽可能的缩小查询范围，说到这我想大家一定会想到我说的加索引是提高查询速度的一把利器，的确它只是一种手段，它也源于缩小查询范围的原理。

      大多数需要优化的SQL都是多表连接的查询，而多表连接也包括横向连接，纵向连接，我们使用最多的就是纵向连接。横向连接一般是指两个表的字段结构基本一样，要把一个表的数据某些记录变成另一个表的一些记录，即Rows+Rows。纵向连接即我们从A表取一些要查询的字段，从B表取一些要查询的字段，然后将A、B表取出来的表用公共的部分纵向连接起来，即Columns+Columns。

      横向连接语句： select a.column1,a.column2 from tableA a union all select b.column1,b.column2 from tableB b

注意，横向连接时，列个数必须相同，而且相对应的字段列数据类型必须相同。其实你就可以把要union的表认为是一个是另一个复制，完全相同。有人可能会问了，要是我要合并的列的的确有不同的列啊，或者有一列根本就没有，那你可以采用一下方式

      select d.dname,d.loc from dept1 d  union all select '' dname, e.loc  from dept e，看“'' dname”，我们不难发现，你可以找个替代品啊，用空字符串代替没有的字段，这样就可以合并了。

      纵向连接语句： select a.column1,a.column2 from tableA a full outer join select b.column3,b.column4 from tableB b on a.aid=b.bid where ......，这是全外连接格式。这个速度的确挺快，但查询可能你不喜欢，因为有些结果行可能你根本不想看到。一般情况下我们left outer join,right outer join用的比较多，这两个的区别就是left outer join以on后面处在左边的连接字段所对应表为主，right outer join刚好相对。当然你也可以使用left join, right join。在使用过程中还是发现外连接相对快些。

      要加快纵向连接查询效率，办法就是嵌套查询。以下是项目中实际的一个例子：

select c.customerid,c.receivedmoney,c.tollcollector,c.receiveddate,c.yearmonth,c.receivedlatefee,

c.receivedfee,c.receivedappend,c.jmman,c.jmmoney,c.name,d.chargeint from

(select  a.customerid,a.receivedmoney,a.tollcollector,a.receiveddate,a.yearmonth,a.receivedlatefee,

a.receivedfee,a.receivedappend,a.jmman,a.jmmoney,b.name from

(select rf.customerid,rf.receivedmoney,rf.tollcollector,rf.receiveddate,rf.yearmonth,rf.receivedlatefee,

rf.receivedfee,rf.receivedappend,rf.jmman,rf.jmmoney from sf_receivedfee rf where

rf.electriccompanyid='1000000001' and rf.dealsign=0 and rf.yearmonth in(200811,200901,200903,200804,200805,200806,200807)

and rf.customerid=1000052545) a left outer join (select xe.employeeid,xe.name from xt_employee xe) b on a.tollcollector=b.employeeid)

c left outer join (select cp.chargeint,cp.customerid from sf_chargePRotocol cp where cp.customerid=1000052545) d

on c.customerid=d.customerid

你可以看到，这个例子中，我们先从每个表用几乎同样的条件过滤出我们需要的记录，再将记录合并，我实际使用中发现这比直接做链接查询要快将近60多倍。虽然很丑，而且很难读懂，但的确解决了SQL性能问题。它运用的原理依然是先缩小范围，然后再进行连接查询，如果我们连接再过滤，那么这其中相当于两个表合并，然后再根据条件取数据，速度可想而知。

上一篇：SQL语法总结

下一篇：sqlserver 存储过程例子