华山论剑第1式-SQL Server查询优化!_华山论剑查询密码-CSDN博客

本期主题: SQL Server查询优化.
查询优化是一个比较常见的主题,
在实际的生产应用中,大数据量的查询会消耗服务器大量的资源,
如何合理地优化查询,提高系统性能,
是每个系统架构人员,系统开发热源,系统维护人员以及数据库管理人员共同关心的话题.
本期就此做一下讨论.
欢迎大家积极参与.:)
先贴几篇作为抛砖引玉.
如何实现优化查询.
http://topic.csdn.net/u/20080416/08/24b62a73-ab5a-4927-97af-81e7e790445d.html
索引的基本原理，以及数据是如何被访问的
http://topic.csdn.net/u/20080521/15/c5ee330e-596d-4957-8032-5bb9a80a9218.html
100分，求sqlserver 中提高查询性能的方法!越全越好！
http://topic.csdn.net/u/20080624/10/0ac05d43-b285-49f0-85b2-73e0d989d2ae.html
深度探索:Clustered Index Scan vs Table Scan
http://topic.csdn.net/u/20080707/09/3c64cc80-5f7a-4116-8942-81c6deae9c07.html
五种提高 SQL 性能的方法
http://dev.csdn.net/author/griefforyou/082b9b29299e4584b78bf6f7ccb57c0b.html
数据库的查询优化技术
http://bbs.chinaunix.net/viewthread.php?tid=83481

查询优化
http://msdn.microsoft.com/zh-cn/library/ms176005.aspx

分析查询
http://msdn.microsoft.com/zh-cn/library/ms191227.aspx

查找缺失索引
http://msdn.microsoft.com/zh-cn/library/ms345417.aspx

查询优化建议
http://msdn.microsoft.com/zh-cn/library/ms188722.aspx

高级查询优化概念
 http://msdn.microsoft.com/zh-cn/library/ms191426.aspx

一些Blogs和英文资料.
--------------------------------------
Tips, Tricks, and Advice from the SQL Server Query Optimization Team
http://blogs.msdn.com/QueryOptTeam/
http://blogs.msdn.com/sqlqueryprocessing/default.aspx

SQL Server 2000 Query Tuning
http://msdn.microsoft.com/en-us/library/aa178417(SQL.80).aspx
Analyzing a Query
http://msdn.microsoft.com/en-us/library/aa178303(SQL.80).aspx
Query Tuning Recommendations
http://msdn.microsoft.com/en-us/library/aa178391(SQL.80).aspx
Advanced Query Tuning Concepts
http://msdn.microsoft.com/en-us/library/aa178578(SQL.80).aspx

Some "Hints" for Mastering SQL Tuning
http://www.devx.com/dbzone/Article/26995

Transact-SQL Query Performance Tuning Tips
http://www.dotnetheaven.com/UploadFile/skrishnasamy/SQLPerformanceTunning03112005044423AM/SQLPerformanceTunning.aspx

SQL Server 2005 Query Tuning
http://msdn.microsoft.com/en-us/library/ms176005.aspx

Analyzing a Query
http://msdn.microsoft.com/en-us/library/ms191227.aspx

Finding Missing Indexes
http://msdn.microsoft.com/en-us/library/ms345417.aspx

Query Tuning Recommendations
http://msdn.microsoft.com/en-us/library/ms188722.aspx

Advanced Query Tuning Concepts
http://msdn.microsoft.com/en-us/library/ms191426.aspx

Deeper insight into unused indexes for SQL Server 2005
http://www.mssqltips.com/tip.asp?tip=1545

Use Missing-Index Groups for Query Tuning
http://www.sqlmag.com/Article/ArticleID/95220/sql_server_95220.html

2 Stored Procedures to Tune Your Indexes
http://www.sqlmag.com/Article/ArticleID/98019/98019.html?feed=articleLink

What I Wish Developers Knew About SQL Server (Presentation)
http://www.sqlteam.com/article/what-i-wish-developers-knew-about-sql-server-presentation.
This is the latest version of this presentation including all demonstration scripts. The presentation has been converted to a PDF to make viewing easier. The presentation provides an overview of query plans, query tuning tips, nulls, error handling and transactions.
the presentation and demo scripts.
http://www.sqlteam.com/downloads/WhatIWishPresentation.zip

Avoid enclosing Indexed Columns in a Function in the WHERE clause
http://www.sqlteam.com/article/avoid-enclosing-indexed-columns-in-a-function-in-the-where-clause

Introduction to Parameterization in SQL Server
http://www.sqlteam.com/article/introduction-to-parameterization-in-sql-server

Best Practices for Query Parameterization
http://www.sqlteam.com/article/best-practices-for-query-parameterization

Query Parameterization and Plan Cache
http://blogs.msdn.com/sqlprogrammability/archive/2007/01/11/4-0-query-parameterization.aspx
This comes to us from the SQL Server Programmability and API Team Blog. They write "Using parameters or parameter markers in queries increases the ability of SQL Server to reuse compiled plans. There are two places where parameterization of queries can be done: on the client side application (or mid tier) or on the server side."

Analyzing and Optimizing T-SQL Query Performance on Microsoft SQL Server using SET and DBCC (PDF)
http://www.sqlteam.com/article/analyzing-and-optimizing-t-sql-query-performance-on-microsoft-sql-server-using-set-and-dbcc-pdf

Five Ways to Rev up Your SQL Performance
http://msdn.microsoft.com/en-us/magazine/cc301622.aspx
这篇文章已经有中译本(五种提高 SQL 性能的方法)

最后推荐一本调优的书
Inside SQL Server 2005: Query Tuning and Optimization
http://download.csdn.net/source/421022

1、    用程序中，保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；能够分开的操作尽量分开处理，提高每次的响应速度；在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；算法的结构尽量简单；在查询时，不要过多地使用通配符如SELECT * FROM T1语句，要用到几列就选择几列如：SELECT COL1,COL2 FROM T1；在可能的情况下尽量限制尽量结果集行数如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的数据的。不要在应用中使用数据库游标，游标是非常有用的工具，但比使用常规的、面向集的SQL语句需要更大的开销；按照特定顺序提取数据的查找。

2、   避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:
SELECT name FROM employee WHERE salary ＞ 60000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。

3、   尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
SELECT * FROM T1 WHERE F1/2=100
应改为:
SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’
应改为:
SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’

SELECT member_number, first_name, last_name FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
应改为:
SELECT member_number, first_name, last_name FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

4、   避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如:
SELECT id FROM employee WHERE id != 'B%'
优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

5、      尽量使用数字型字段，一部分开发人员和数据库管理人员喜欢把包含数值信息的字段
设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

6、      合理使用EXISTS,NOT EXISTS子句。如下所示：
1.SELECT SUM(T1.C1)FROM T1 WHERE(
(SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)
2.SELECT SUM(T1.C1) FROM T1WHERE EXISTS(
SELECT * FROM T2 WHERE T2.C2=T1.C2)
两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。
如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS代替。如：
IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')
可以写成：
IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

经常需要写一个T_SQL语句比较一个父结果集和子结果集，从而找到是否存在在父结果集中有而在子结果集中没有的记录，如：
1.SELECT a.hdr_key FROM hdr_tbl a---- tbl a 表示tbl用别名a代替
WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)

2.SELECT a.hdr_key FROM hdr_tbl a
LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL

3.SELECT hdr_key FROM hdr_tbl
WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)
      三种写法都可以得到同样正确的结果，但是效率依次降低。

7、      尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。
见如下例子：
SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * FROM T1 WHERE NAME LIKE ‘L%’
即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。

8、      分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在 WHERE 子句中将连接条件完整的写上，有可能大大提高查询速度。
例：
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO
第二句将比第一句执行快得多。

9、       消除对大型表行数据的顺序存取
      尽管在所有的检查列上都有索引，但某些形式的WHERE子句强迫优化器使用顺序存取。如：
SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR
order_num=1008
解决办法可以使用并集来避免顺序存取：
SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT ＊ FROM orders WHERE order_num=1008
这样就能利用索引路径处理查询。

10、    避免困难的正规表达式
      LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT * FROM customer WHERE zipcode LIKE “98_ _ _”
即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如
果把语句改为SELECT ＊ FROM customer WHERE zipcode >“98000”，在执行查询
时就会利用索引来查询，显然会大大提高速度。
11、    使用视图加速查询
把表的一个子集进行排序并创建视图，有时能加速查询。它有助于避免多重排序
操作，而且在其他方面还能简化优化器的工作。例如：
SELECT cust.name，rcvbles.balance，……other columns
FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
AND cust.postcode>“98000”
ORDER BY cust.name
如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个
视图中，并按客户的名字进行排序：
CREATE VIEW DBO.V_CUST_RCVLBES
AS
SELECT cust.name，rcvbles.balance，……other columns
FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
ORDER BY cust.name

然后以下面的方式在视图中查询：
SELECT ＊ FROM V_CUST_RCVLBES
WHERE postcode>“98000”
视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘
I/O，所以查询工作量可以得到大幅减少。

12、    能够用BETWEEN的就不要用IN
SELECT * FROM T1 WHERE ID IN (10,11,12,13,14)
改成：
SELECT * FROM T1 WHERE ID BETWEEN 10 AND 14
因为IN会使系统无法使用索引,而只能直接搜索表中的数据。

13、    DISTINCT的就不用GROUP BY
      SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
      可改为：
      SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10


14、      部分利用索引
      1.SELECT employeeID, firstname, lastname
FROM names
WHERE dept = 'prod' or city = 'Orlando' or division = 'food'

      2.SELECT employeeID, firstname, lastname FROM names WHERE dept = 'prod'
UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE city = 'Orlando'
UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE division = 'food'
如果dept 列建有索引则查询2可以部分利用索引，查询1则不能。

15、      能用UNION ALL就不要用UNION
UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源

16、      不要写一些不做任何事的查询
如：SELECT COL1 FROM T1 WHERE 1=0
    SELECT COL1 FROM T1 WHERE COL1=1 AND COL1=2
这类死码不会返回任何结果集，但是会消耗系统资源。

17、     尽量不要用SELECT INTO语句。
SELECT INOT 语句会导致表锁定，阻止其他用户访问该表。

18、必要时强制查询优化器使用某个索引
     SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45)
改成：
SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)
则查询优化器将会强行利用索引IX_ProcessID 执行查询。

19、     虽然UPDATE、DELETE语句的写法基本固定，但是还是对UPDATE语句给点建议：
a)    尽量不要修改主键字段。
b)    当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。
c)    尽量最小化对于含有UPDATE触发器的表的UPDATE操作。
d)    避免UPDATE将要复制到其他数据库的列。
e)    避免UPDATE建有很多索引的列。
f)    避免UPDATE在WHERE子句条件中的列。

首先，优化的目的是为了更高效、更合理的利用现有资源，在不同资源下优化的方向是不同的。
再者，比如索引本身就是为了高效查询，一般用的原则如：在经常进行连接，但是没有指定为外键的列、在频繁进行排序或分组的列、在条件表达式中经常用到的不同值较多的列上建立检索....,不同值少的列上就不要建立索引（比如性别字段，使用了不但不会提高查询效率，反而可能会影响更新速度，有时影响还是很严重的）。这些原则也仅是一般性指导原则，索引一旦建立，在以后的SQL语句就得注意回避一些影响使用索引的语句及使用条件的顺序等。。。

宏观地说,
1.做群集,做负载均衡.
2.做磁盘整列
3.对数据库进行优化,如分文件组,分文件,分区,分表,分区视图等等.
4.对网络进行优化,采用SAN架构,光纤技术等等.

微观地说,
就是前面帖子提到的一些优化的方式,
对查询语句,对索引进行优化.

不知道说的对不对哈,见笑了
----------------------------------------------------------------------------------------
感觉
最大得优化是需求优化
其次是设计优化
再次是算法优化
然后是系统优化
最后才是硬件优化
不知道是不是歪理，但我个人总结工作总结是符合这个顺序滴。
而在每一种优化中均存在宏观优化和微观优化。