SQL高性能编程基础篇

 

SQL高性能编程基础

先分享一句话：高性能是一点一点的省出来的

在写SQL编程的时候，肯定有很多同学喜欢写select * from tableName 之类的代码吧，但是当数据库表有千万条数据的时候，使用select * 会很影响SQL效率的。关于SQL高性能编程的总结如下：

一：按需所取

对于

select * from tableName

可以改为

Select 需要的字段1，需要的字段2，…from tableName where…

就我个人理解，我觉得在这里可以这样理解，在查询的时候写出来的条件尽可能的精确，确保查询效率。按需索取范围其实很广，相似的还有like关键字的使用，按需更新之类的。这里有一个要注意的，比如尽量使用left join代替join

 

二．可重复执行

我们写的SQL语句，尽可能保持能够重复执行

         比如我这里有一条SQL语句。

 

Create  table x

(

    Myid uniqueidentifier default newId()  primary key ,

    CONTENT varchar(40)

)

但是只能运行一次，就不能运行两次了，为了确保可以重复执行，可以这样写

--请注意为什么我这里不是SELECT * FROM sysobjects...

IF NOT EXISTS(SELECT 1 FROM sysobjects WHERE id = OBJECT_ID('x') AND xtype = 'U')

BEGIN

    Create  table x

(

    Myid uniqueidentifier default newId()  primary key ,

    CONTENT varchar(40)

)

END

 

三．合理的利用索引

         从时间上来讲：

         Order by 聚集索引列 > order by 非聚集索引列

四．SARGS原则

         用限制搜索的一个操作，因为他通常是指一个特定的匹配，一个范围内的匹配或者两个AND连接。

一般来说推荐使用union 两张表代替条件A OR B。

当查询和提取超大量的数据的时候，影响数据查找时间最大的因素不是数据表上的查找，而是物理上的I/O操作。

Select top 20 from

(

Select 10000 from table

where neibuyonghu='办公室'order by gid desc

) as a

Order by gid asc

 

例如：

Select a.guid , a.age ,a.name from table a where a.guid= 0 or a.age>1

 

推荐写为：

         Select a.guid , a.age ,a.name from table a where a.guid= 0

         Union

         Select a.guid , a.age ,a.name from table a where a.age >1

补充一点：使用IN 和OR的时候容易引起全表扫描。

不满足SARG形式的语句：

         NOT ,NOT IN ,<>,!= ,!< ,!>, NOT EXSIT ,NOT LIKE等

 

 

分享一个demo

数据更新避免表扫描

Ø  问题代码：

strFtModeSQL = "Update p_bzItem SET FtMode='" & strFtMode & "' WHERE itemtype='核算科目' AND charIndex('" & strItemCodeNew & ".',ItemCode)=1"

Ø  原因分析：

更新语句中使用了charIndex的方式来作为查询条件，在ItemCode字段没建索引的情况下，必须进行全表扫描，建了索引也不能有效利用，必须进行索引扫描，效率较低；而且由于更新时还会加排它锁，造成其它用户阻塞。

Ø  指引原则：

数据更新操作也需要考虑查询条件，让其遵循SARGs，使查询条件能有效利用索引，减少锁的时间，避免进行表扫描或聚集索引扫描。

Ø  解决方案：

‘调整charIndex的条件过滤方式为like ‘xx%’的方式，使其原有的聚集索引扫描算法变为聚集索引查找算法 strFtModeSQL = "Update p_bzItem SET FtMode='" & strFtMode & "' WHERE itemtype='核算科目' AND ItemCode like '" & strItemCodeNew & ".%'”

对于这个问题，like应该会引起全表扫描，但是利用到了索引列。

 

 

五．谨慎使用排序功能

         案例：在一张A表left join B表的时候，要选取前20条数据，最后进行排序的时候order by A.字段 , b.字段  ，这样的执行顺序是两张表做好拼接了之后才会开始排序，对效率的影响很大。

一般的建议是，推荐选取主表的字段，如果使用左连接，则推荐将排序的字段冗余到主表中。这样可以充分利用索引。