华山论剑第1式-SQL Server查询优化!
本期主题: 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
[解决办法]
bs汝
[解决办法]
sf
[解决办法]
接200分
[解决办法]
人齐,可以结帖.
[解决办法]
人齐,可以结帖.
[解决办法]
可以了，一层楼给30分，结了吧
[解决办法]
人齐,可以结帖.
[解决办法]
..
[解决办法]
速度结帖吧和尚..
[解决办法]
sf
[解决办法]
[解决办法]
- SQL code
踩踩.....................
[解决办法]
陆小凤之凤舞九天
[解决办法]
人已全部到齐,整装待发，渴求立刻颁发奖品 :D
[解决办法]
严肃点，我们在打劫呢
[解决办法]
优化宏观论与优化微观论如何有机地结合呢，大师？
[解决办法]
很好,佛
[解决办法]
看看
[解决办法]
做个记号 学习啊
[解决办法]
学习，收藏了
[解决办法]
占位
[解决办法]
[解决办法]
期待
华山论剑第2式
o(∩_∩)o...
[解决办法]
和尚 ，建议把小 梁的回复删除了，一点也不严肃
- SQL code
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*2SELECT * 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 membersWHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21应改为:SELECT member_number, first_name, last_name FROM membersWHERE 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 aLEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL 3.SELECT hdr_key FROM hdr_tblWHERE 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_RCVLBESAS 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_RCVLBESWHERE postcode>“98000” 视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。12、 能够用BETWEEN的就不要用INSELECT * 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, lastnameFROM namesWHERE dept = 'prod' or city = 'Orlando' or division = 'food' 2.SELECT employeeID, firstname, lastname FROM names WHERE dept = 'prod'UNION ALLSELECT employeeID, firstname, lastname FROM names WHERE city = 'Orlando'UNION ALLSELECT employeeID, firstname, lastname FROM names WHERE division = 'food'如果dept 列建有索引则查询2可以部分利用索引，查询1则不能。15、 能用UNION ALL就不要用UNIONUNION 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子句条件中的列。
[解决办法]
谢谢
收藏了
[解决办法]
在顶下
[解决办法]
UP
[解决办法]
学习
[解决办法]
感谢楼主无私的奉献.
[解决办法]
[解决办法]
感谢楼主，无私奉献，加油～
[解决办法]
见到300分大帖一定要顶~
[解决办法]
[解决办法]
希望每次都是300分
[解决办法]
敬仰中
[解决办法]
收藏了，学习了
[解决办法]
收藏了！
根据狙击手的理论，那不是如果在where条件中必须用到函数时不是没办法了啊？
[解决办法]
jietie
[解决办法]
ding
ding
[解决办法]
我喜欢...
[解决办法]
人齐,可以结帖.
[解决办法]
good
[解决办法]
[解决办法]
学习
[解决办法]
路过接分
[解决办法]
学习，收藏！
[解决办法]
[解决办法]
可以讨论吗?????
[解决办法]
收藏！
[解决办法]
ding
[解决办法]
bs
[解决办法]
顶
[解决办法]
学习
[解决办法]
O
於查化要看SQL版本,不能一概而
比如SQL2000的sp4里一部份函的化，05是接又做出化。也就是同的句在不同版本SQL里效率不同
[解决办法]
对我而言，似乎放在疑难问题里更合适
[解决办法]
[解决办法]
[解决办法]
哎，接分收藏，和尚你出出快，我在等我定的
[解决办法]
首先，优化的目的是为了更高效、更合理的利用现有资源，在不同资源下优化的方向是不同的。
再者，比如索引本身就是为了高效查询，一般用的原则如：在经常进行连接，但是没有指定为外键的列、在频繁进行排序或分组的列、在条件表达式中经常用到的不同值较多的列上建立检索....,不同值少的列上就不要建立索引（比如性别字段，使用了不但不会提高查询效率，反而可能会影响更新速度，有时影响还是很严重的）。这些原则也仅是一般性指导原则，索引一旦建立，在以后的SQL语句就得注意回避一些影响使用索引的语句及使用条件的顺序等。。。
[解决办法]
纯路过...
[解决办法]
[解决办法]
踩着大师的脚印往前冲....
[解决办法]
学习，收藏
[解决办法]
好像大家都是在说怎么优化查询
有没有怎么优化插入，删除，更新的分享下呢？
如果数据库要做频繁的插入和更新操作，那对于写SQL语句更新数据库需要注意些什么呢？表结构又要注意些什么？
如果表的字段很多的情况下又该怎么做呢？
这个时候性能瓶颈主要在I/O上，有没有数据库的物理存储，读取，I/O访问等这方面的资料给大家共享下啊，谢谢了
我现在碰到的问题是经常一行的数据超过8k，因为一行可能有三四百个列，都是decimal的数据，只能把一个表拆分成两个表，郁闷得很
[解决办法]
[解决办法]
插入，删除，更新
----------------
这种操作也是分成两部份的，第一步也是要查询,找到需要更新的数据，加上UPDATE锁,
第二步是对上一步找出的数据做更改，加Exclusive锁.
所以，索引的使用就要注意了,如果更新的数据影响到了索引，此时就有额外维护索引的IO出现.
[解决办法]
此处已占位
[解决办法]
[解决办法]
mark
[解决办法]
jf
[解决办法]
[解决办法]
和尚，我爱你！
[解决办法]
最近研究了下网上的关于sql server优化的文章，包括上面贴出的或者提供连接的，发现有很多条似是而非的，或者是对于6.5以前的版本有效的，而在2000以后版本已经由内部的优化器解决了的。
可惜没时间进行整理，争取在奥运前整理出来，贴到blog上共享
[解决办法]
mark，学习
[解决办法]
mark 学习了
[解决办法]
接分专用
[解决办法]
[解决办法]
我认真学习
------解决方案--------------------
学习，收藏！
[解决办法]
搞个chm岂不更好
[解决办法]
[解决办法]
[解决办法]
[解决办法]
（1）查询优化还真是个永恒的主题......
（2）干这个活，就像老中医，得对症下药。不同的应用场景、不同的数量级、不同的运行成本、不同的调优期望值，就得有不同的药方啊。
（3）理论联系实际。花和尚这个几个帖子汇集得好，堪称精品。现对其提出口头表扬一次，等第2式。
[解决办法]
[解决办法]
要查出数据不难，但是要最快地查出数据，就不好说了·