¸ßЧ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,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQL,ÒÔÏÂΪ±ÊÕßѧϰ¡¢ÕªÂ¼¡¢²¢»ã×ܲ¿·Ö×ÊÁÏÓë´ó¼Ò·ÖÏí£¡
£¨1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLE µÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom× ......
SQL Enlight 1.5 Æƽ⠵ڶþ°æ ÊÕ²Ø
ÆƽâÉùÃ÷£ºÎÒµÄÆƽâ½öÓÃÓÚÑо¿£¬ÇëÎðÓÃÓÚÉÌÒµÓÃ;£¬ÐèҪʹÓÃÇ빺ÂòÕý°æÈí¼þ¡£
¿É¶ñµÄUbitSoft£¬ÎÒµÄÆƽâ³öÀ´»¹Ã»¼¸Ì죬ËûµÄ³ÌÐò¾Í¸Ä±äÁËÑéÖ¤Âß¼£¬ËäÈ»ÎÒ¾õµÃSQL EnlightµÄ¹¦Äܲ»ÊǷdz£Ç¿´ó£¬µ«ÊÇËûµÄ·ÀÆƽ⹦Äܵ¹ÊÇÏÂÁ˲»ÉÙ¹¦·ò£¬³ýÁ˺ËÐÄ´úÂëÓÃvc++.net±àдµÄnative cod ......
SELECT
(case when a.colorder=1 then d.name else '' end)±íÃû,
a.colorder ×Ö¶ÎÐòºÅ,
a.name ×Ö¶ÎÃû,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity ......
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
......
1 --ÏÂÃæµÄ´úÂëÉú³É³¤¶ÈΪ8µÄ±àºÅ£¬±àºÅÒÔBH¿ªÍ·£¬ÆäÓà6λΪÁ÷Ë®ºÅ¡£
2 --µÃµ½Ð±àºÅµÄº¯Êý
3 CREATE FUNCTION f_NextBH()
4 RETURNS char(8)
5 AS
6 BEGIN
7 RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) from tb WITH(XLOCK,PAGLOCK))
8 END
9 GO
10
11 --ÔÚ±íÖÐ ......