¸ßЧSQL²éѯ֮Ë÷Òý¸²¸Ç(index coverage)
½ñÌì×öSQL ÓÅ»¯£¬²éÕÒÖ´Ðмƻ®Ê±£¬Ö´Ðмƻ®£¬·¢ÏÖ´ËÖ´Ðмƻ®ÓëÒÔÍùµÄ¼Æ»®ÓÐËùÇø±ð£»ÕÒ¼»¥ÁªÍø£¬ÖÕÓÚÕÒһƪÓйØÑо¿±È½ÏÉîÈëµÄÎÄÕ£»
ÔÖ´Ðмƻ®Ê¹ÓõÄÊÇË÷ÒýɨÃ裬ͻȻһÏ»áʹÓÃË÷Òý¸²¸Ç¼¼Êõ£¬Ð§ÂÊ´óÔö£»
SELECT * µÄÕæÏࣺË÷Òý¸²¸Ç(index coverage)
SELECT *µÄЧÂʺÜÔã¸âÂ𣿵±È»£¬ËùÓÐÈ˶¼ÖªµÀÕâÒ»µã£¬µ«ÊÇÎªÊ²Ã´ÄØ£¿
ÊÇÒòΪ·µ»ØÁËÌ«¶àµÄÊý¾Ý£¿
ÕâÊÇÒ»¸öÆÕ±éµÄ»Ø´ð£¬µ«ÎÒ²»ÕâÑùÈÏΪ¡£Èç¹ûÄãµÄÊý¾Ý¿âÉè¼Æ¹æ·¶ºÏÀí£¬ÄÇô´ø¿íÕ¼ÓÃʵ¼ÊÉϷdz£µÄС¡£
ÈÃÎÒÃÇ¿´¿´ÏÂÃæµÄÀý×Ó¡£ÏÂÃæµÄ²éѯ½«»á´ÓAdventureWorks.dbo.TransactionHistoryArchive£¨×ܹ²´óÔ¼Óнü9ÍòÐÐÊý¾Ý£©ÖÐÑ¡Ôñ³ö326ÐÐÊý¾Ý¡£µÚÒ»¸öʹÓÃÁËSELECT * ²éѯ£¬ºóÒ»¸ö²éѯÔòÓÐÃ÷È·µÄ×ֶΡ£
SELECT * from Production.TransactionHistoryArchive
WHERE ReferenceOrderID < 100
SELECT ReferenceOrderLineID from Production.TransactionHistoryArchive
WHERE ReferenceOrderID < 100
ÔÚÕâÖÖÇé¿öÏ£¬Á½ÕßÔÚÍøÂç´ø¿íµÄÇø±ðÖ»ÓÐ15K£¨180K-165K£©£¬´óÔ¼10%µÄ´ø¿í²îÒì¡£µÄÈ·ÖµµÃÈ¥ÓÅ»¯£¬µ«²»»áÓкܴóµÄЧ¹û¡£
SELECT * ½«Ôì³É±í/Ë÷ÒýɨÃè
SELECT * µÄ×î´óÎÊÌâÊǽ«Ó°Ïì²éѯ¼Æ»®¡£SQL ServerÖ÷ҪʹÓÃË÷ÒýÈ¥²éѯÄãÐèÒªµÄÊý¾Ý£¬µ±Ë÷Òý°üÀ¨ËùÓеÄÄãÇëÇó²éѯµÄ×ֶΣ¬SQL Server½«²»ÐèҪȥÔÚ±íÖвéѯ¡£Õâ¸ö¸ÅÄî³Æ×öË÷Òý¸²¸Ç¡£ÔÚÉÏÃæµÄÀý×ÓÖУ¬µÚÒ»¸ö²éѯ½á¹ûÊÇÔÚ¾Û¼¯Ë÷ÒýɨÃèÖУ¬·´¹ýÀ´£¬µÚ¶þ¸öÀý×ÓʹÓÃÁ˸ü¶à¸üÓÐЧÂʵÄË÷ÒýɨÃè¡£ÔÚÕâ¸ö°¸ÀýÖУ¬Ë÷ÒýɨÃè±È¾Û¼¯Ë÷ÒýɨÃè¿ì100±¶ ¡£
³ý·ÇÄãÒѾ½«ÎªÃ¿¸ö×ֶν¨Á¢ÁËË÷Òý£¨ÏÔÈ»²»ÊǸöºÃÖ÷Ò⣩£¬SELECT *ÊDz»Äܹ»ÀûÓõ½Ë÷Òý¸²¸Ç£¬ÄãÖ»ÄÜÈ¥×öɨÃè²Ù×÷£¨·Ç³£µÄûÓÐЧÂÊ£©¡£
Èç¹ûÄãÖ»ÊDzéѯÄãËùÐèÒªµÄ×ֶΣ¬ÄÇÄã¸ü¿ÉÄܵĸ²¸Çµ½ÄãµÄË÷Òý¡£ÎÒÏëÕâ¾ÍÊDz»ÍƼöʹÓÃSELECT *µÄÖ÷ÒªµÄÔÒò¡£
Îȶ¨ÐÔ·½Ãæ
ÔÚά»¤Ò»¸öÓ¦ÓóÌÐòʱ£¬SELECT *Ò²»á´øÀ´Ò»Ð©ÒâÏë²»´óµÄÎÊÌâ¡£Ëü»áÒýÆðÄãµÄ´úÂë·¢ÉúһЩ²»È·¶¨ÐÔ¡£Èç¹ûÄãÔö¼ÓÁËÒ»¸öÐУ¨Òë×¢£ºÎÒ¾õµÃÕâÀïÓ¦¸ÃÊÇ×ֶΣ©µ½Ò»¸ö±íÖУ¬ÄÇôSELECT * ·µ»ØµÄ½á¹ûµ½ÄãµÄÓ¦ÓóÌÐòÖн«»áÔڽṹÉÏ·¢Éú±ä»¯¡£Á¼ºÃµÄÓ¦ÓóÌÐòÓ¦¸ÃÊÇʹÓÃ×Ö¶ÎÃû³ÆµÄ£¬¶ø²»Ó¦¸ÃÊÜ´ËÓ°Ïì¡£µ±Íâ½ç·¢Éú±ä»¯Ê±£¬Á¼ºÃµÄÓ¦ÓóÌÐòÉè¼ÆÒ²Ó¦¸Ã×îС»¯µÄ¸ü¸Ä¡£
Ó¢ÎÄÔ¸å: http://weblogs.asp.net/jgalloway/archive/2007/07/18/the-real-reason-select-queries
Ïà¹ØÎĵµ£º
Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Äã¶¼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâÆªÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉÜʹÓÃSQL ServerµÄÇÏÃÅ£¬Ò²²»ÄÜÌṩһ¸ö°üÖΰٲ¡µÄ·½°¸£¬ÎÒËù×öµÄÊÇ×ܽáһЩ¾Ñé----¹ØÓÚÈçºÎÐγÉÒ»¸öºÃµÄÉè¼Æ¡£Õ ......
Create PROCEDURE [dbo].[PR_addRoles]
@RolesID INT,
@Roles varchar (100),
@ID INT OUT
AS
BEGIN TRY
BEGIN TRAN
INSERT FS_Roles (RolesID,Roles) VALUES (@RolesID,@Roles)
COMMIT TRAN
SET @ID=1
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @ID=0
END CATCH
......
×ªÔØ SQLÖеÄdatetimeÓ÷¨Ïê½â
×ªÔØµØÖ·£ºhttp://blog.sina.com.cn/s/blog_495491770100fq4n.html
2009-07-27 09:45 sql server2000ÖÐʹÓÃconvertÀ´È¡µÃdatetimeÊý¾ÝÀàÐÍÑùʽ
ÈÕÆÚÊý¾Ý¸ñʽµÄ´¦Àí£¬Á½¸öʾÀý£º
CONVERT(varchar(16), ʱ¼äÒ», 20) ½á¹û£º2007-02-01 08:02
ʱ¼äÒ»°ãΪ
getdate()
º¯Êý»òÊý¾Ý±íÀïµÄ ......
´óÐÍϵͳµÄÉú²ú»·¾³£¬Ò»°ãÇé¿öÏ£¬ÎÒÃÇÆÀ¼ÛÒ»Ìõ²éѯÊÇ·ñÓÐЧÂÊ£¬¸ü¶àµÄÊǹØ×¢Âß¼ IO( ÖÁÓÚΪʲô£¬»ØÍ·²¹Ò»Æª ) ¡£ÎÒÃdz£Ëµ£¬“Òª½¨±ëº·µÄË÷Òý”¡¢“Ҫд¸ßЧµÄ SQL ”£¬Æäʵ×îÖÕÄ¿µÄ¾ÍÊÇÔÚÏàͬ½á¹û¼¯Çé¿öÏ£¬¾¡¿ÉÄܼõÉÙÂß¼ IO ¡£
1.1 where Ìõ¼þµÄÁÐÉ϶¼µÃÓÐͳ¼ÆÐÅÏ¢¡£
......
ÉÏ»ØÎÒÃÇ˵µ½ÆÀ¹ÀÒ»ÌõÓï¾äÖ´ÐÐЧÂÊÖ÷Òª¿´Âß¼ IO £¨É¶ÊÇÂß¼ IO £¬É¶ÊÇÎïÀí IO ¼ûÁª»úÎĵµ£©£¬Õâ´ÎÎÒÃǼÌÐø¡£
ÎÒÃÇÏÈ˵˵£¬·µ»Ø¶àÐнá¹ûʱ£¬ÎªÊ²Ã´ SQLServer ÓÐʱ»áÑ¡Ôñ index seek £¬ÓÐʱ»áÑ¡Ôñ index scan ¡£
ÒÔ nonclustered index ΪÀý˵Ã÷¡£
ÏñËùÓеÄË÷Òý B Ê÷Ò»Ñù£¬·Ç¾Û¼¯Ë÷ÒýÊ÷Ò²°üÀ¨ÍêÈ«ÓÉË÷ÒýÊý¾Ý×é³ÉµÄ¸ù½ ......