ããå¨è¯å¥½çæ°æ®åºè®¾è®¡åºç¡ä¸ï¼è½ææå°ä½¿ç¨ç´¢å¼æ¯SQL Serveråå¾é«æ§è½çåºç¡ï¼SQL Serveréç¨åºäºä»£ä»·çä¼å模åï¼å®å¯¹æ¯ä¸ä¸ªæ交çæå
³è¡¨çæ¥è¯¢ï¼å³å®æ¯å¦ä½¿ç¨ç´¢å¼æç¨åªä¸ä¸ªç´¢å¼ãå 为æ¥è¯¢æ§è¡ç大é¨åå¼éæ¯ç£çI/Oï¼ä½¿ç¨ç´¢å¼æé«æ§è½çä¸ä¸ªä¸»è¦ç®æ æ¯é¿å
å
¨è¡¨æ«æï¼å 为å
¨è¡¨æ«æéè¦ä»ç£çä¸è¯»è¡¨çæ¯ä¸ä¸ªæ°æ®é¡µï¼å¦ææç´¢å¼æåæ°æ®å¼ï¼åæ¥è¯¢åªé读å 次ç£çå°±å¯ä»¥äºã
ããæ以å¦æ建ç«äºåççç´¢å¼ï¼ä¼åå¨å°±è½å©ç¨ç´¢å¼å éæ°æ®çæ¥è¯¢è¿ç¨ãä½æ¯ï¼ç´¢å¼å¹¶ä¸æ»æ¯æé«ç³»ç»çæ§è½ï¼å¨å¢ãå ãæ¹æä½ä¸ç´¢å¼çåå¨ä¼å¢å ä¸å®çå·¥ä½éï¼å æ¤ï¼å¨éå½çå°æ¹å¢å éå½çç´¢å¼å¹¶ä»ä¸åççå°æ¹å é¤æ¬¡ä¼çç´¢å¼ï¼å°æå©äºä¼åé£äºæ§è½è¾å·®çSQL Serveråºç¨ãå®è·µè¡¨æï¼åççç´¢å¼è®¾è®¡æ¯å»ºç«å¨å¯¹åç§æ¥è¯¢çåæåé¢æµä¸çï¼åªææ£ç¡®å°ä½¿ç´¢å¼ä¸ç¨åºç»åèµ·æ¥,æè½äº§çæä½³çä¼åæ¹æ¡ãæ¬æå°±SQL Serverç´¢å¼çæ§è½é®é¢è¿è¡äºä¸äºåæåå®è·µã
ããä¸ãèç°ç´¢å¼(clustered indexes)ç使ç¨
ããèç°ç´¢å¼æ¯ä¸ç§å¯¹ç£çä¸å®é
æ°æ®éæ°ç»ç»ä»¥ææå®çä¸ä¸ªæå¤ä¸ªåçå¼æåºãç±äºèç°ç´¢å¼çç´¢å¼é¡µé¢æéæåæ°æ®é¡µé¢ï¼æ以使ç¨èç°ç´¢å¼æ¥æ¾æ°æ®å ä¹æ»æ¯æ¯ä½¿ç¨éèç°ç´¢å¼å¿«ãæ¯å¼ 表åªè½å»ºä¸ä¸ªèç°ç´¢å¼ï¼å¹¶ä¸å»ºèç°ç´¢å¼éè¦è³å°ç¸å½è¯¥è¡¨120%çéå 空é´ï¼ä»¥åæ¾è¯¥è¡¨çå¯æ¬åç´¢å¼ä¸é´é¡µã建ç«èç°ç´¢å¼çææ³æ¯ï¼
ãã1ã大å¤æ°è¡¨é½åºè¯¥æèç°ç´¢å¼æ使ç¨ååºæ¥éä½å¯¹è¡¨å°¾é¡µçç«äºï¼å¨ä¸ä¸ªé«äºå¡çç¯å¢ä¸ï¼å¯¹æåä¸é¡µçå°é严éå½±åç³»ç»çååéã
ãã2ãå¨èç°ç´¢å¼ä¸ï¼æ°æ®å¨ç©çä¸æ顺åºæå¨æ°æ®é¡µä¸ï¼éå¤å¼ä¹æå¨ä¸èµ·ï¼å èå¨é£äºå
å«èå´æ£æ¥(betweenã<ã<=ã>ã>=)æ使ç¨group byæorder byçæ¥è¯¢æ¶ï¼ä¸æ¦æ¾å°å
·æèå´ä¸ç¬¬ä¸ä¸ªé®å¼çè¡ï¼å
·æåç»ç´¢å¼å¼çè¡ä¿è¯ç©çä¸æ¯è¿å¨ä¸èµ·èä¸å¿
è¿ä¸æ¥æç´¢ï¼é¿å
äºå¤§èå´æ«æï¼å¯ä»¥å¤§å¤§æé«æ¥è¯¢é度ã
ãã3ãå¨ä¸ä¸ªé¢ç¹åçæå
¥æä½ç表ä¸å»ºç«èç°ç´¢å¼æ¶ï¼ä¸è¦å»ºå¨å
·æåè°ä¸åå¼çå(å¦IDENTITY)ä¸ï¼å¦åä¼ç»å¸¸å¼èµ·å°éå²çªã
ãã4ãå¨èç°ç´¢å¼ä¸ä¸è¦å
å«ç»å¸¸ä¿®æ¹çåï¼å 为ç å¼ä¿®æ¹åï¼æ°æ®è¡å¿
须移å¨å°æ°çä½ç½®ã
ãã5ãéæ©èç°ç´¢å¼åºåºäºwhereåå¥åè¿æ¥æä½çç±»åã
ããèç°ç´¢å¼ç侯éåæ¯ï¼
ãã1ã主é®å,该åå¨whereåå¥ä¸ä½¿ç¨å¹¶ä¸æå
¥æ¯éæºçã
ãã2ãæèå´ååçåï¼å¦pri_order > 100 and pri_order < 200ã
ãã3ãå¨group byæorder byä¸ä½¿ç¨çåã
ãã4ãä¸ç»å¸¸ä¿®æ¹çåã
ãã5ãå¨è¿æ¥æä½ä¸ä½¿ç¨çåã
ããäºãéèç°ç´¢å¼(nonclustered indexes)ç使ç¨
ããSQL Server缺çæ
åµä¸å»ºç«çç´¢å¼æ¯éèç°ç´¢å¼ï¼ç±äºéèç°ç´¢å¼ä¸éæ°ç»ç»è¡¨ä¸çæ°æ®ï¼èæ¯å¯¹æ¯ä¸è¡åå¨ç´¢å¼åå¼å¹¶ç¨ä¸ä¸ªæéæåæ°æ®æå¨ç页é¢ãæ¢å¥è¯è¯´éèç°ç´¢å¼å
·æå¨ç´¢å¼ç»æåæ°æ®æ¬èº«ä¹é´çä¸ä¸ªé¢å¤çº§ãä¸ä¸ªè¡¨å¦æ没æèç°ç´¢å¼æ¶,å¯æ250个éèç°ç´¢å¼ãæ¯ä¸ªéèç°ç´¢å¼æä¾è®¿é®æ°æ®çä¸åæåºé¡ºåºãå¨å»ºç«éèç°ç´¢å¼æ¶ï¼è¦æè¡¡ç´¢å¼å¯¹æ¥è¯¢é度çå å¿«ä¸éä½ä¿®æ¹é度ä¹é´çå©å¼ãå¦å¤ï¼è¿è¦èèè¿äºé®é¢ï¼
ãã1ãç´¢å¼éè¦ä½¿ç¨å¤å°ç©ºé´ã
ãã2ãåéçåæ¯å¦ç¨³å®ã
ãã3ãç´¢å¼é®æ¯å¦ä½éæ©çï¼æ«ææææ¯å¦æ´ä½³ã
ãã4ãæ¯å¦æ许å¤éå¤å¼ã
ãã对æ´æ°é¢ç¹ç表æ¥è¯´ï¼è¡¨ä¸çéèç°ç´¢å¼æ¯èç°ç´¢å¼åæ ¹æ¬æ²¡æç´¢å¼éè¦æ´å¤çé¢å¤å¼éã对移å°æ°é¡µçæ¯ä¸è¡èè¨ï¼æå该æ°æ®çæ¯ä¸ªéèç°ç´¢å¼ç页级è¡ä¹å¿
é¡»æ´æ°ï¼ææ¶å¯è½è¿éè¦ç´¢å¼é¡µçåçãä»ä¸ä¸ªé¡µé¢å é¤æ°æ®çè¿ç¨ä¹ä¼æ类似çå¼éï¼å¦å¤ï¼å é¤è¿ç¨è¿å¿
é¡»ææ°æ®ç§»å°é¡µé¢ä¸é¨ï¼ä»¥ä¿è¯æ°æ®çè¿ç»æ§ãæ以ï¼å»ºç«éèç°ç´¢å¼è¦é常æ
éãéèç°ç´¢å¼å¸¸è¢«ç¨å¨ä»¥ä¸æ
åµï¼
ãã1ãæå常ç¨äºéåå½æ°(å¦Sum,....)ã
ãã2ãæå常ç¨äºjoin,order by,group byã
ãã3ãæ¥å¯»åºçæ°æ®ä¸è¶
è¿è¡¨ä¸æ°æ®éç20%ã
ããä¸ãè¦çç´¢å¼(covering indexes)ç使ç¨
ããè¦çç´¢å¼æ¯æé£äºç´¢å¼é¡¹ä¸å
å«æ¥å¯»æéè¦çå
¨é¨ä¿¡æ¯çéèç°ç´¢å¼ï¼è¿ç§ç´¢å¼ä¹æ以æ¯è¾å¿«ä¹æ£æ¯å 为索å¼é¡µä¸å
å«äºæ¥å¯»æå¿
é¡»çæ°æ®,ä¸éå»è®¿é®æ°æ®é¡µãå¦æéèç°ç´¢å¼ä¸å
å«ç»ææ°æ®,é£ä¹å®çæ¥è¯¢é度å°å¿«äºèç°ç´¢å¼ã
ããä½æ¯ç±äºè¦çç´¢å¼çç´¢å¼é¡¹æ¯è¾å¤,è¦å ç¨æ¯è¾å¤§ç空é´ãèä¸updateæä½ä¼å¼èµ·ç´¢å¼å¼æ¹åãæ以å¦ææ½å¨çè¦çæ¥è¯¢å¹¶ä¸å¸¸ç¨æä¸å¤ªå
³é®ï¼åè¦çç´¢å¼çå¢å åèä¼éä½æ§è½ã
ããåãç´¢å¼çéæ©ææ¯
ããp_detailæ¯ä½æ¿å
¬ç§¯é管çç³»ç»ä¸è®°å½ä¸ªäººæç»ç表ï¼æ890000è¡ï¼è§å¯å¨ä¸åç´¢å¼ä¸çæ¥è¯¢è¿è¡ææï¼æµè¯å¨C/Sç¯å¢ä¸è¿è¡ï¼å®¢æ·æºæ¯IBM PII350(å
å64M),æå¡å¨æ¯DEC Alpha1000A(å
å128M),æ°æ®åºä¸ºSYBASE11.0.3ã
ãã1ã select count(*) from p_detail where
ããop_date>â19990101â and op_date<â
ãã19991231â and pri_surplus1>300
ãã2ã select count(*),sum(pri_surplus1) from p_detail
ããwhere op_date>â19990101â and
ããpay_month betweenâ199908â andâ199912â
ããä¸å»ºä»»ä½ç´¢å¼æ¥è¯¢1 1å15ç§
ããæ¥è¯¢2 1å7ç§
ããå¨op_dateä¸å»ºéèç°ç´¢å¼æ¥è¯¢1 57ç§
ããæ¥è¯¢2 57ç§
ããå¨op_dateä¸å»ºèç°ç´¢å¼æ¥è¯¢1 <1ç§
ããæ¥è¯¢2 52ç§
ããå¨pay_monthãop_dateãpri_surplus1ä¸å»ºç´¢å¼æ¥è¯¢1 34ç§
ããæ¥è¯¢2 <1ç§
ããå¨op_dateãpay_monthãpri_surplus1ä¸å»ºç´¢å¼æ¥è¯¢1 <1ç§
ããæ¥è¯¢2 <1ç§
ããä»ä»¥ä¸æ¥è¯¢ææåæï¼ç´¢å¼çææ ï¼å»ºç«æ¹å¼çä¸åå°ä¼å¯¼è´ä¸åçæ¥è¯¢ææï¼éæ©ä»ä¹æ ·çç´¢å¼åºäºç¨æ·å¯¹æ°æ®çæ¥è¯¢æ¡ä»¶,è¿äºæ¡ä»¶ä½ç°äºwhereä»å¥åjoin表达å¼ä¸ãä¸è¬æ¥è¯´å»ºç«ç´¢å¼çæè·¯æ¯ï¼
ãã(1)主é®æ¶å¸¸ä½ä¸ºwhereåå¥çæ¡ä»¶ï¼åºå¨è¡¨ç主é®åä¸å»ºç«èç°ç´¢å¼ï¼å°¤å
¶å½ç»å¸¸ç¨å®ä½ä¸ºè¿æ¥çæ¶åã
ãã(2)æ大ééå¤å¼ä¸ç»å¸¸æèå´æ¥è¯¢åæåºãåç»åççåï¼æè
é常é¢ç¹å°è¢«è®¿é®çåï¼å¯èè建ç«èç°ç´¢å¼ã
ãã(3)ç»å¸¸åæ¶ååå¤åï¼ä¸æ¯åé½å«æéå¤å¼å¯èè建ç«å¤åç´¢å¼æ¥è¦çä¸ä¸ªæä¸ç»æ¥è¯¢ï¼å¹¶ææ¥è¯¢å¼ç¨æé¢ç¹çåä½ä¸ºå导åï¼å¦æå¯è½å°½é使å
³é®æ¥è¯¢å½¢æè¦çæ¥è¯¢ã
ãã(4)å¦æç¥éç´¢å¼é®çææå¼é½æ¯å¯ä¸çï¼é£ä¹ç¡®ä¿æç´¢å¼å®ä¹æå¯ä¸ç´¢å¼ã
ãã(5)å¨ä¸ä¸ªç»å¸¸åæå
¥æä½ç表ä¸å»ºç´¢å¼æ¶ï¼ä½¿ç¨fillfactor(å¡«å
å å)æ¥åå°é¡µåè£ï¼åæ¶æé«å¹¶å度éä½æ»éçåçãå¦æå¨åªè¯»è¡¨ä¸å»ºç´¢å¼ï¼åå¯ä»¥æfillfactor置为100ã
ãã(6)å¨éæ©ç´¢å¼é®æ¶ï¼è®¾æ³éæ©é£äºéç¨å°æ°æ®ç±»åçåä½ä¸ºé®ä»¥ä½¿æ¯ä¸ªç´¢å¼é¡µè½å¤å®¹çº³å°½å¯è½å¤çç´¢å¼é®åæéï¼éè¿è¿ç§æ¹å¼ï¼å¯ä½¿ä¸ä¸ªæ¥è¯¢å¿
é¡»éåçç´¢å¼é¡µé¢éå°æå°ãæ¤å¤ï¼å°½å¯è½å°ä½¿ç¨æ´æ°ä¸ºé®å¼ï¼å 为å®è½å¤æä¾æ¯ä»»ä½æ°æ®ç±»åé½å¿«ç访é®é度ã
ããäºãç´¢å¼çç»´æ¤
ããä¸é¢è®²å°,æäºä¸åéçç´¢å¼å½±åå°SQL Serverçæ§è½,éçåºç¨ç³»ç»çè¿è¡,æ°æ®ä¸æå°åçåå,å½æ°æ®ååè¾¾å°æä¸ä¸ªç¨åº¦æ¶å°ä¼å½±åå°ç´¢å¼ç使ç¨ãè¿æ¶éè¦ç¨æ·èªå·±æ¥ç»´æ¤ç´¢å¼ãç´¢å¼çç»´æ¤å
æ¬ï¼
ãã1ãé建索å¼
ããéçæ°æ®è¡çæå
¥ãå é¤åæ°æ®é¡µçåè£ï¼æäºç´¢å¼é¡µå¯è½åªå
å«å 页æ°æ®ï¼å¦å¤åºç¨å¨æ§è¡å¤§åI/Oçæ¶åï¼é建éèç°ç´¢å¼å¯ä»¥éä½åçï¼ç»´æ¤å¤§åI/Oçæçãé建索å¼å®é
ä¸æ¯éæ°ç»ç»B-æ 空é´ãå¨ä¸é¢æ
åµä¸éè¦é建索å¼ï¼
ãã(1)æ°æ®å使ç¨æ¨¡å¼å¤§å¹
度ååã
ãã(2)æåºç顺åºåçæ¹åã
ãã(3)è¦è¿è¡å¤§éæå
¥æä½æå·²ç»å®æã
ãã(4)使ç¨å¤§åI/Oçæ¥è¯¢çç£ç读次æ°æ¯é¢æçè¦å¤ã
ãã(5)ç±äºå¤§éæ°æ®ä¿®æ¹ï¼ä½¿å¾æ°æ®é¡µåç´¢å¼é¡µæ²¡æå
å使ç¨è导è´ç©ºé´ç使ç¨è¶
åºä¼°ç®ã
ãã(6)dbccæ£æ¥åºç´¢å¼æé®é¢ã
ããå½é建èç°ç´¢å¼æ¶ï¼è¿å¼ 表çææéèç°ç´¢å¼å°è¢«é建ã
ãã2ãç´¢å¼ç»è®¡ä¿¡æ¯çæ´æ°
ããå½å¨ä¸ä¸ªå
å«æ°æ®ç表ä¸å建索å¼çæ¶åï¼SQL Serverä¼å建åå¸æ°æ®é¡µæ¥åæ¾æå
³ç´¢å¼ç两ç§ç»è®¡ä¿¡æ¯ï¼åå¸è¡¨åå¯åº¦è¡¨ãä¼åå¨å©ç¨è¿ä¸ªé¡µæ¥å¤æ该索å¼å¯¹æ个ç¹å®æ¥è¯¢æ¯å¦æç¨ãä½è¿ä¸ªç»è®¡ä¿¡æ¯å¹¶ä¸å¨æå°éæ°è®¡ç®ãè¿æå³çï¼å½è¡¨çæ°æ®æ¹åä¹åï¼ç»è®¡ä¿¡æ¯æå¯è½æ¯è¿æ¶çï¼ä»èå½±åä¼åå¨è¿½æ±ææå·¥ä½çç®æ ãå æ¤ï¼å¨ä¸é¢æ
åµä¸åºè¯¥è¿è¡update statisticså½ä»¤ï¼
ãã(1)æ°æ®è¡çæå
¥åå é¤ä¿®æ¹äºæ°æ®çåå¸ã
ãã(2)对ç¨truncate tableå é¤æ°æ®ç表ä¸å¢å æ°æ®è¡ã
ãã(3)ä¿®æ¹ç´¢å¼åçå¼ã
ããå
ãç»æè¯
ããå®è·µè¡¨æï¼ä¸æ°å½çç´¢å¼ä¸ä½äºäºæ è¡¥ï¼åèä¼éä½ç³»ç»çæ§è¡æ§è½ãå 为大éçç´¢å¼å¨æå
¥ãä¿®æ¹åå é¤æä½æ¶æ¯æ²¡æç´¢å¼è±è´¹æ´å¤çç³»ç»æ¶é´ãä¾å¦ä¸é¢æ
åµä¸å»ºç«çç´¢å¼æ¯ä¸æ°å½çï¼
ãã1ãå¨æ¥è¯¢ä¸å¾å°æä»ä¸å¼ç¨çåä¸ä¼åçäºç´¢å¼ï¼å 为索å¼å¾å°æä»æ¥ä¸å¿
æç´¢åºäºè¿äºåçè¡ã
ãã2ãåªæ两个æä¸ä¸ªå¼çåï¼å¦ç·æ§å女æ§(æ¯æå¦)ï¼ä»ä¸ä¼ä»ç´¢å¼ä¸å¾å°å¥½å¤ã
ããå¦å¤ï¼é´äºç´¢å¼å å¿«äºæ¥è¯¢é度ï¼ä½åæ
¢äºæ°æ®æ´æ°é度çç¹ç¹ãå¯éè¿å¨ä¸ä¸ªæ®µä¸å»ºè¡¨ï¼èå¨å¦ä¸ä¸ªæ®µä¸å»ºå
¶éèç°ç´¢å¼ï¼èè¿ä¸¤æ®µåå«å¨åç¬çç©ç设å¤ä¸æ¥æ¹åæä½æ§è½ã
温馨提示:答案为网友推荐,仅供参考