ç´¢å¼åè¯
é¦å éè¿ä¸ä¸ªä¾åæ¥ç´è§è®¤è¯ä¸ç´¢å¼å¯¹æ¥è¯¢æççæåãä¾åä¸ä½¿ç¨ç表为 employeesï¼å»ºè¡¨è¯å¥è§éå½ï¼ãå¨ä¸º emp_no å段å ç´¢å¼ä¹åï¼æ¥è¯¢ emp_no 为 401060 çèåä¿¡æ¯ï¼sql å¦ä¸ï¼
æ¥è¯¢ç»æå¦ä¸ï¼
è¿è¡æ¶é´å¦ä¸ï¼
å¨ä¸º emp_no å段å ç´¢å¼ä¹åï¼åéæ°æ§è¡å¦ä¸ sql ï¼
æ¥è¯¢ç»æå¦ä¸ï¼
è¿è¡æ¶é´å¦ä¸ï¼
ä»ååçè¿è¡æ¶é´å¯¹æ¯å¯ä»¥çåºï¼å ç´¢å¼ä¹åï¼æ¥è¯¢æçæäºååçæåãé£ä¹ç´¢å¼å°åºæ¯ä»ä¹ï¼ä¸ºä»ä¹è½å¤æé«æ¥è¯¢æçå¢ï¼
ç°å¨ä»¥æ¥è¯å ¸ä¸ºä¾æ¥è¯´æä¸ç´¢å¼çä½ç¨ãå°å¦çæ¶åï¼æ们é½å¦è¿æä¹æ¥è¯å ¸ãæ¯å¦ï¼è¦æ¥æ¾ç´¢åï¼å¯ä»¥å éè¿æ¼é³æ¥æ¾å°ç´¢åå¨é£ä¸é¡µãå¦ä¸å¾æ示ï¼
ç¶ååå°ç¸åºç页ä¸å»æ¥æ¾ç´¢åã
è¿æ ·ä¾¿å¯ä»¥å¾å¿«æ¾å°ç¸åºçåï¼æ¯æè¯å ¸ä»å¤´ç¿»å°å°¾å¿«å¤äºï¼è¿éçæ¼é³ç®å½ä¾¿ç¸å½äºæ°æ®åºè¡¨ä¸çç´¢å¼ã
æ°æ®åºçç´¢å¼
äºåæ¥æ¾æ
é£ä¹æ°æ®åºè¡¨ä¸çç´¢å¼æ¯æä¹å®ç°çå¢ï¼å ¶å®ï¼æ°æ®åºè¡¨ä¸çç´¢å¼å°±æ¯ä¸ç§æ°æ®ç»æã以 MySQL ç InnoDB åå¨å¼æ为ä¾ï¼å®ä½¿ç¨çæ°æ®ç»ææ¯ B+ æ ã为ä»ä¹ä½¿ç¨ B+ æ è¿ç§æ°æ®ç»æå¢ï¼
å¨è®²è§£ B+ æ ä¹åï¼å æ¥ä»ç»ä¸äºåæ¥æ¾æ³ãäºåæ¥æ¾æ³ä¹ç§°ä¸ºæåæ¥æ¾æ³ï¼ç¨æ¥æ¥æ¾ä¸ç»æåºè®°å½ä¸çæä¸é¡¹è®°å½ãå ¶åºæ¬ææ³æ¯ï¼å å°è¦æ¥æ¾çè®°å½å¼åæåºæ°åä¸ä½äºä¸é´ç¹ä½ç½®çè®°å½å¼è¿è¡æ¯è¾ï¼å¦æå°äºä½äºä¸é´ç¹ä½ç½®è®°å½çå¼ï¼åè¦æ¥æ¾çè®°å½å¼å¨æ°åçå·¦åé¨åï¼å¦å为å³åé¨åãè¿æ ·éè¿ä¸æ¬¡æ¥æ¾ä¾¿å¯ä»¥å°æ¥æ¾åºé´ç¼©å°ä¸åãä¾å¦ï¼æ们æ 3ã5ã8ã13ã21ã34ã55ã89ã144ã233 å个æ°ï¼å设æ们è¦æ¥æ¾ 144 è¿ä¸ªæ°åï¼å ¶æ¥æ¾è¿ç¨å¦ä¸å¾æ示ï¼
ä»å¾ä¸å¯ä»¥çåºï¼ä½¿ç¨ä¸æ¬¡æ¯è¾å°±æ¾å°äº 144 è¿ä¸ªæ°ï¼å¦æç¨é¡ºåºæ¥æ¾åéè¦ 9 次ãåºäºä¸é¢ 10 个æ°åï¼å¦æç¨é¡ºåºæ¥æ¾ï¼åå¹³åæ¥æ¾æ¬¡æ°ä¸º ï¼1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10ï¼/ 10 = 5.5 次ãäºåæ¥æ¾çå¹³åæ¥æ¾æ¬¡æ°ä¸º ï¼4 + 3 + 2 + 4 + 3 + 1 + 4 + 3 + 2 + 3ï¼/ 10 = 2.9 次ãäºåæ¥æ¾å ·ææ¥æ¾é度快ãå¹³åæ§è½å¥½çä¼ç¹ï¼ä½æ¯è¦æ±å¾ æ¥è¡¨ä¸ºæåºè¡¨ï¼å½è¦å表ä¸æå ¥æå é¤è®°å½æ¶ï¼ä»£ä»·æ¯è¾é«ãè¿æ ·ä¾¿åºç°äºäºåæ¥æ¾æ ã对äºä¸é¢ç 10 个æ°åï¼åå¦æ°åç顺åºä¸º 21ã5ã89ã3ã8ã34ã144ã13ã55ã233 å¯ä»¥æ建å¦ä¸äºåæ¥æ¾æ ï¼
å设è¦æ¥æ¾çæ°å¼ä¸º aï¼åå¨ä¸è¿°äºåæ¥æ¾æ ä¸çæ¥æ¾æ¥éª¤ä¸ºï¼
- å¦æ a 大äºæ ¹èç¹ï¼åå¨å³åæ ä¸è¿è¡æ¥æ¾ã
- å¦æ a å°äºæ ¹èç¹ï¼åå¨å·¦åæ ä¸è¿è¡æ¥æ¾ã
- å¦æ a çäºæ ¹èç¹ï¼ä¹å°±æ¯æ¾å°äºè¿ä¸ªèç¹ï¼è¿åæ ¹èç¹å³å¯ã
å¨ä¸è¿°è¿ä¸ªäºåæ ä¸æ¥æ¾å¼çæ大æ¯è¾æ¬¡æ°ä¸º 4 次ï¼è¿å±äºæ¯è¾çæ³æ åµï¼æ¥è¯¢çæ¶é´å¤æ度为 O(logn)ãåå¦ç»åºçæ°å顺åºä¸º 3ã5ã8ã13ã21ã34ã55ã89ã144ã233ï¼åæé çäºåæ¥æ¾æ å¦ä¸ï¼
è¿æ¶åäºåæ¥æ¾æ å·²ç»éåæäºä¸æ¡é¾è¡¨ï¼æ¥æ¾æ°æ®çæ¶é´å¤æ度åæäº O(n)ã为äºè§£å³è¿ä¸ªé®é¢ï¼æåºäºå¹³è¡¡äºåæ¥æ¾æ ï¼AVL æ ï¼ï¼å®å¨äºåæ¥æ¾æ çåºç¡ä¸å¢å äºçº¦æï¼æ¯ä¸ªèç¹çå·¦åæ åå³åæ çé«åº¦å·®ä¸è½è¶ è¿ 1ãåææ建ç第ä¸æ£µäºåæ 便å±äºå¹³è¡¡äºåæ¥æ¾æ ã
ç°å¨æäºå¹³è¡¡äºåæ ï¼æ¯ä¸æ¯å°±æå³çå¯ä»¥ä½¿ç¨å¹³è¡¡äºåæ æ¥ä½ä¸ºç´¢å¼çåå¨ç»æå¢ï¼éä¹ï¼åå æ¯ï¼ç´¢å¼ä¸æ¢åå¨äºå åä¸ï¼è¿è¦åå¨ç£çä¸ãå设æ个æ°æ®è¡¨æ 100 ä¸è¡æ°æ®ï¼å¦æ使ç¨å¹³è¡¡äºåæ æ¥å»ºç´¢å¼ï¼åå¾å°ç平衡äºåæ æ é«ä¸º 20ãä¸æ¬¡æ¥è¯¢å¯è½éè¦è®¿é® 20 个æ°æ®åï¼ä¹å°±æ¯æå¯è½ä¼æ 20 次ç£ç IOãå½åçæºæ¢°ç£çä¸æ¬¡ IO ç大æ¦æ¶é´ä¸º 10msï¼20 次ç£ç IO çæ¶é´ä¸º 200 msãä¹å°±æ¯è¯´ï¼å¯¹äºä¸ä¸ª 100 ä¸è¡ç表ï¼å¦æ使ç¨äºåæ æ¥åå¨ï¼åç¬è®¿é®ä¸ä¸ªè¡å¯è½éè¦ 200 msï¼è¿ä¸ªæ¶é´æ¯ä¸è½æ¥åçã为äºåå°ç£ç IOï¼éè¦éä½æ çé«åº¦ï¼è®©æ åçæ´âç®èâï¼ä¸ºäºè®©æ åâç®èâï¼éè¦å¢å æ¯ä¸ªèç¹çåèç¹çæ°ç®ï¼è¿ä¾¿æ¯ M åæ ï¼M > 2ï¼ãè¿ä»¥åæç 100 ä¸è¡æ°æ®ä¸ºä¾ï¼å¦æ M = 10ï¼åè¿æ¶çæ é«ä¸º 4 ãèèå°æ æ ¹çæ°æ®åæ»æ¯å¨å åä¸ï¼æ¥æ¾ä¸ä¸ªå¼æå¤åªéè®¿é® 3 次ç£çãå ¶å®ï¼æ ç第äºå±ä¹æå¾å¤§æ¦çå¨å åä¸ï¼é£ä¹è®¿é®ç£ççå¹³å次æ°å°±æ´å°äºãM åæ ç±äºå¨è¯»åä¸çæ§è½ä¼ç¹ï¼ä»¥åéé ç£çç访é®æ¨¡å¼ï¼å·²ç»è¢«å¹¿æ³åºç¨å¨æ°æ®åºå¼æä¸äºã
B æ
B æ ä¾¿æ¯ M åæ çä¸ç§ï¼B æ çç»æå¦ä¸å¾æ示ï¼
B æ ä½ä¸ºå¹³è¡¡çå¤è·¯æç´¢æ ï¼å®çæ¯ä¸ä¸ªèç¹æå¤å¯ä»¥å å« M 个åèç¹ï¼M 称为 B æ çé¶ãæ¯ä¸ªç£çåä¸å æ¬äºå ³é®åååèç¹çæéãå¦æä¸ä¸ªç£çåå å«äº N ä¸ªå ³é®åï¼é£ä¹æéæ°å°±æ¯ N + 1ãä¸ä¸ª M é¶ç B æ ï¼M > 2ï¼æ以ä¸çç¹æ§ï¼
- æ ¹èç¹çå¿åæ°çèå´æ¯ [2, M]ã
- æ¯ä¸ªä¸é´èç¹å å« k - 1 ä¸ªå ³é®åå k 个å©åï¼å©åçæ°é = å ³é®åçæ°é + 1ï¼k çåå¼èå´ä¸º [ceil(M/2), M]ã
- å¶åèç¹å å« k - 1 ä¸ªå ³é®åï¼å¶åèç¹æ²¡æå©åï¼ï¼k çåå¼èå´ä¸º [ceil(M/2), M]ã
- å设ä¸é´èç¹çå ³é®å为ï¼key[1], key[2], â¦, key[k - 1]ï¼ä¸å ³é®åæç §ååºæåºï¼å³ k[i] < k[i + 1]ãæ¤æ¶ k - 1 ä¸ªå ³é®åç¸å½äºååäº k 个èå´ï¼ä¹å°±æ¯å¯¹åºç k 个æéï¼å³ä¸ºï¼P[1], P[2], â¦, P[k]ï¼å ¶ä¸ P[1] æåå ³é®åå°äº key[1] çåæ ï¼P[i] æåå ³é®åå±äº ï¼key[i-1], key[i]ï¼çåæ ï¼P[k] æåå ³é®åå¤§äº key[k-1] çåæ ã
- ææå¶åèç¹ä½äºåä¸å±ã
ç°å¨æ们æ¥çä¸å¦ä½ç¨ B æ è¿è¡æ¥æ¾ï¼å设æ们è¦æ¥æ¾å ³é®å 36ï¼åæ¥æ¾æ¥éª¤å¦ä¸ï¼
- å° 36 ä¸æ ¹èç¹æ¯è¾ï¼36 å¤§äº 35 å¾å°æé P3ã
- æ ¹æ®æé P3 æ¾å°ç£çå 4ï¼36 å°äº 65 å¾å°æé P1ã
- æ ¹æ®æé P1 æ¾å°ç£çå 6ï¼ç¶åæ¾å°äºå ³é®å 36ã
ä»æ¥è¯¢çè¿ç¨å¯ä»¥çåºï¼B æ ç¸å¯¹äºå¹³è¡¡äºåæ æ¥è¯´ç£ç I/O æä½æ´å°ï¼å¨æ°æ®æ¥è¯¢ä¸æ¯å¹³è¡¡äºåæ æçè¦é«ã
B+ æ
B+ æ æ¯å¯¹ B æ çæ¹è¿ï¼B+ æ å B æ çå·®å¼å¨ä»¥ä¸å ç¹ï¼
- æ k 个å©åçèç¹å°±æ k ä¸ªå ³é®åã
- éå¶åèç¹çå ³é®åä¹ä¼åæ¶åå¨äºåèç¹ä¸ï¼å¹¶ä¸æ¯å¨åèç¹ä¸ææå ³é®åçæ大ææå°ã
- éå¶åèç¹ä» ç¨äºç´¢å¼ï¼ä¸ä¿åæ°æ®è®°å½ï¼è·è®°å½æå ³çä¿¡æ¯é½æ¾å¨å¶åèç¹ä¸ã
- ææå ³é®åé½å¨å¶åèç¹åºç°ï¼å¶åèç¹ææä¸ä¸ªæåºé¾è¡¨ï¼èä¸å¶åèç¹æ¬èº«æç §å ³é®åç大å°ä»å°å°å¤§é¡ºåºé¾æ¥ã
ä¸é¢ä¾¿æ¯ä¸æ£µ B+ æ ï¼
åå¦æ们è¦æ¥æ¾å ³é®å 60ï¼åæ¥æ¾è¿ç¨ä¸ºï¼
- ä¸æ ¹èç¹è¿è¡æ¯è¾ï¼å¾å°æé P2ã
- 顺çæé P2 æ¥å°ç£çå3ï¼ä¸ç£çå 3 ä¸çå ³é®åè¿è¡æ¯è¾ï¼å¾å°æé P3ã
- 顺çæé P3 æ¥å°ç£çå10ï¼ä¸ç£çå 10 ä¸çå ³é®åè¿è¡æ¯è¾ï¼æ¾å°å ³é®å 60ã
ä»æ¥è¯¢è¿ç¨æ¥çï¼B+ æ å B æ å·®ä¸å¤ï¼ä½æ¯ B+ æ å B æ çæ ¹æ¬å·®å¼å¨äºï¼B+ æ çä¸é´èç¹ä¸ç´æ¥åå¨æ°æ®ãè¿æ ·åç好å¤æ¯ï¼
- B+ æ æ¥è¯¢æçæ´ç¨³å®ï¼å 为æ¯æ¬¡åªæ访é®å°å¶åèç¹æè½æ¾å°å¯¹åºçæ°æ®ï¼èå¨ B æ ä¸ï¼éå¶åèç¹ä¹ä¼åå¨æ°æ®ï¼è¿æ ·å°±ä¼é ææ¥è¯¢æçä¸ç¨³å®çæ åµï¼ææ¶å访é®å°äºéå¶åèç¹å°±å¯ä»¥æ¾å°å ³é®åï¼ææ¶åéè¦è®¿é®å°å¶åèç¹æè½æ¾å°å ³é®åã
- B+ æ çæ¥è¯¢æçæ´é«ï¼å 为 B+ æ éå¸¸æ¯ B æ æ´ç®èï¼æ¥è¯¢æéçç£ç I/O ä¹ä¼æ´å°ãåæ ·çç£ç页大å°ï¼B+ æ å¯ä»¥åå¨æ´å¤çèç¹å ³é®åã
èç°ç´¢å¼åéèç°ç´¢å¼
èç°ç´¢å¼æ¯æç §æ¯å¼ 表ç主é®æé çä¸æ£µ B+ æ ï¼å¶åèç¹ä¸åæ¾çå³ä¸ºæ´å¼ 表çè¡è®°å½æ°æ®ï¼èç°ç´¢å¼çå¶åèç¹ä¹ç§°ä¸ºæ°æ®é¡µãéèç°ç´¢å¼å¶åèç¹å¹¶ä¸å å«è¡è®°å½çå ¨é¨æ°æ®ãå¶åèç¹é¤äºå å«é®å¼ä»¥å¤ï¼æ¯ä¸ªå¶åèç¹ä¸çç´¢å¼è¡ä¸è¿å å«ä¸ä¸ªä¹¦ç¾ã该书ç¾ç¨æ¥åè¯ InnoDB åå¨å¼æåªéå¯ä»¥æ¾å°ä¸ç´¢å¼ç¸å¯¹åºçè¡æ°æ®ãç±äº InnoDB åå¨å¼æ表æ¯ç´¢å¼ç»ç»è¡¨ï¼å æ¤ InnoDB åå¨å¼æçéèç°ç´¢å¼ç书ç¾å°±æ¯ç¸åºçè¡æ°æ®çèç°ç´¢å¼é®ãé£ä¹åºäºèç°ç´¢å¼åéèç°ç´¢å¼çæ¥è¯¢çåºå«å¨åªéå¢ï¼å éè¿ä¸ä¸ªä¾åæ¥ç´è§æåä¸ï¼æ¥è¯¢ emp_no 为 401060 çè®°å½ï¼éè¿å段 emp_no æ¥æ¥è¯¢ï¼sql å¦ä¸ã
æ¥è¯¢ç»æå¦ä¸ï¼
éè¿å段 id æ¥æ¥è¯¢ï¼sql å¦ä¸ï¼
æ¥è¯¢ç»æå¦ä¸ï¼
两ç§æ¥è¯¢æ¹å¼çèæ¶å¦ä¸ï¼
ä»ä¸é¢çç»æï¼å¯ä»¥çåºï¼ä¸¤ç§æ¥è¯¢æ¹å¼çç»ææ¯ç¸åçï¼ä½æ¯åºäºèç°ç´¢å¼çæ¥è¯¢è¦å¿«äºåºäºéèç°ç´¢å¼çæ¥è¯¢ãåæ ·æ¯åºäºç´¢å¼çæ¥è¯¢ï¼æ¥è¯¢ç»æä¹æ¯ç¸åçï¼é£ä¸ºä»ä¹æ¥è¯¢æçä¸ä¸æ ·å¢ï¼ä¸¾ä¸ªä¾åæ¥è¯´æä¸ï¼å设ææ°æ®è¡¨ Tï¼è¡¨ä¸å å«ä¸ä¸ªå段 idãemp_no å genderï¼id 为主é®ï¼å¹¶ä¸å¨ k ä¸æç´¢å¼ãè¡¨ä¸ R1~R5 çå¼åå«ä¸º(3, 300, âMâ)ã(5, 500, âMâ)ã(8, 800, âFâ)ã(13, 1300, âFâ) å (21, 2100, âMâ)ï¼èç°ç´¢å¼åéèç°ç´¢å¼çç´¢å¼æ ç示æå¾å¦ä¸ï¼
å¦ææ¥è¯¢è¯å¥æ¯ select * from T where id = 5ï¼å³ä¸»é®æ¥è¯¢æ¹å¼ï¼ååªéè¦æç´¢èç°ç´¢å¼è¿æ£µ B+ æ ãå¦æè¯å¥æ¯ select * from T where k = 500ï¼å³éèç°ç´¢å¼æ¥è¯¢æ¹å¼ï¼åéè¦å æç´¢éèç°ç´¢å¼æ ï¼å¾å° id çå¼ä¸º 5 ï¼åå°èç°ç´¢å¼æ ä¸æç´¢ä¸æ¬¡ãè¿ä¸ªè¿ç¨ç§°ä¸ºå表ãä¹å°±æ¯è¯´ï¼åºäºéèç°ç´¢å¼çæ¥è¯¢éè¦å¤æ«æä¸æ£µç´¢å¼æ ãå æ¤ï¼æ们å¨åºç¨ä¸åºè¯¥å°½é使ç¨ä¸»é®æ¥è¯¢ã
è¦çç´¢å¼
ä¸ä¸è讲å°ï¼å½ä½¿ç¨éèç°ç´¢å¼æ¥è¯¢æ°æ®æ¶ï¼ç±äºæ¥è¯¢ç»æéè¦çæ°æ®åªå¨ä¸»é®ç´¢å¼ä¸æï¼æ以ä¸å¾ä¸å表ãé£ä¹æ没æå¯è½é¿å å表å¢ï¼å¦ææ¥è¯¢è¯å¥æ¯ select id from T where k = 500ï¼è¿æ¶ååªéè¦æ¥è¯¢ id çå¼ï¼èè¿ä¸ª id çå¼å·²ç»å¨éèç°ç´¢å¼æ ä¸äºï¼å æ¤å¯ä»¥ç´æ¥æä¾æ¥è¯¢ç»æï¼ä¸éè¦å表ãä¹å°±æ¯è¯´ï¼å¨è¿ä¸ªæ¥è¯¢éé¢ï¼éèç°ç´¢å¼å·²ç»è¦çäºæ们çæ¥è¯¢éæ±ï¼ç§°ä¸ºè¦çç´¢å¼ãä¸é¢éè¿ä¸ä¸ªä¾ååæ¥è¯´æä¸è¦çç´¢å¼ãéè¿ emp_no æ¥æ¥è¯¢èåçæ§å«ä¿¡æ¯ï¼sql è¯å¥å¦ä¸ï¼
å½åªå¨ emp_no å段ä¸å»ºç«ç´¢å¼æ¶ï¼æ¥è¯¢ç»æå¦ä¸ï¼
èæ¶å¦ä¸ï¼
为äºç¡®è®¤æ¯å¦ä½¿ç¨äºè¦çç´¢å¼ï¼å¯ä»¥ä½¿ç¨ explain å½ä»¤ã
æ§è¡çç»æå¦ä¸ï¼
å¯ä»¥çå° Extra åçå¼ä¸º Using whereï¼è¿è¯´æ没æ使ç¨è¦çç´¢å¼ã
å½å¨ emp_no å gender å段ä¸å»ºç«èåç´¢å¼æ¶ï¼æ¥è¯¢ç»æå¦ä¸ï¼
èæ¶å¦ä¸:
å½å次æ§è¡ explain å½ä»¤æ¶ï¼ç»æå¦ä¸ï¼
å¯ä»¥çå° Extra åçå¼å«æ Using indexï¼è¿è¯´æå·²ç»ä½¿ç¨äºè¦çç´¢å¼ã使ç¨è¦çç´¢å¼çæ¥è¯¢æçè¦é«äºæ²¡æ使ç¨è¦çç´¢å¼çæ¥è¯¢æçãå¨èè建ç«åä½ç´¢å¼æ¥æ¯æè¦çç´¢å¼æ¶éè¦æè¡¡èèï¼å 为索å¼å段çç»´æ¤æ»æ¯æ代价çã
æå·¦åç¼åå
ä»åé¢çä¾åä¸ï¼å¯ä»¥çåºç´¢å¼çåå¨ç¡®å®å¤§å¤§æé«äºæ¥è¯¢æçï¼é£æ¯ä¸æ¯éè¦ä¸ºæ¯ä¸ªæ¥è¯¢é½è®¾è®¡ä¸ä¸ªç´¢å¼ï¼çæ¡æ¯å¤§å¯ä¸å¿ ãå 为B+ æ è¿ç§ç´¢å¼ç»æï¼ç¬¦åæå·¦åç¼ååï¼å¯ä»¥å©ç¨ç´¢å¼çæå·¦åç¼æ¥å®ä½è®°å½ã
ç°å¨éè¿ (first_name, last_name) è¿ä¸ªèåç´¢å¼æ¥æ´ç´è§ç说æä¸è¿ä¸ªæ¦å¿µã
- æ¥è¯¢ first_name 为 Moonï¼last_name 为 Demke çèåä¿¡æ¯ï¼sql å¦ä¸ï¼
æ¥è¯¢èæ¶å¦ä¸ï¼
æ§è¡ä¸é¢ç explain å½ä»¤ï¼ç»æå¦ä¸ï¼
è¿è¯´ææ¥è¯¢ä½¿ç¨äºç´¢å¼ã
- æ¥è¯¢ first_name 为 Moon çèåä¿¡æ¯ï¼sql å¦ä¸ï¼
æ¥è¯¢èæ¶å¦ä¸ï¼
æ§è¡ä¸é¢ç explain å½ä»¤ï¼ç»æå¦ä¸ï¼
è¿è¯´æåæ ·ä½¿ç¨äºç´¢å¼ã
- æ¥è¯¢ last_name 为 Demke çèåä¿¡æ¯ï¼sql å¦ä¸ï¼
æ¥è¯¢èæ¶å¦ä¸ï¼
æ§è¡ä¸é¢ç explain å½ä»¤ï¼ç»æå¦ä¸ï¼
ç»æ表æ没æ使ç¨ç´¢å¼ã
ç®å建ç«çç´¢å¼ä¸º (first_name, last_name)ï¼ä»ä¸é¢çæ¥è¯¢å¯ä»¥çåºï¼å½åæ¶ä½¿ç¨ first_nameãlast_name æ¥è¯¢ä»¥ååç¬ä½¿ç¨ first_name æ¥è¯¢æ¶ï¼é½å¯ä»¥ä½¿ç¨ä¸ç´¢å¼ï¼å½åç¬ä½¿ç¨ last_name æ¥è¯¢æ¶ï¼æ²¡æ使ç¨ä¸ç´¢å¼ï¼è¿ä¾¿æ¯ç´¢å¼çæå·¦åç¼ååãå 为æå·¦åç¼ååï¼å½å·²ç»æäº (a, b) è¿ä¸ªèåç´¢å¼åï¼ä¸è¬å°±ä¸éè¦åç¬å¨ a ä¸å»ºç«ç´¢å¼äºãå æ¤ï¼å®æèåç´¢å¼ç第ä¸ååæ¯ï¼å¦æéè¿è°æ´é¡ºåºï¼å¯ä»¥å°ç»´æ¤ä¸ä¸ªç´¢å¼ï¼é£ä¹è¿ä¸ªé¡ºåºå¾å¾å°±æ¯éè¦ä¼å èèéç¨çã
- æå·¦åç¼ååä¸ä» éç¨äºèåç´¢å¼çæå·¦ N 个å段ï¼ä¹å¯ä»¥æ¯å符串索å¼çæå·¦ M 个å符ãä¾å¦ï¼åå«æ¥è¯¢ first_name 以 Moo å¼å¤´å以 oon ç»å°¾çèåçä¿¡æ¯ãæ¥è¯¢ä»¥ Moo å¼å¤´ç sql å¦ä¸ï¼
æ¥è¯¢èæ¶å¦ä¸ï¼
æ§è¡ä¸é¢ç explain å½ä»¤ï¼ç»æå¦ä¸ï¼
ç»æ表ææ¥è¯¢ä½¿ç¨ä¸äºç´¢å¼ãæ¥è¯¢ä»¥ oon ç»å°¾ç sql å¦ä¸ï¼
æ¥è¯¢èæ¶å¦ä¸ï¼
æ§è¡ä¸é¢ç explain å½ä»¤ï¼ç»æå¦ä¸ï¼
ç»æ表ææ¥è¯¢æ²¡æ使ç¨ä¸ç´¢å¼ã
ç´¢å¼å建è§è
- ä¸å¡ä¸å ·æå¯ä¸ç¹æ§çå段ï¼å³ä½¿æ¯å¤ä¸ªå段çç»åï¼ä¹å¿ 须建æå¯ä¸ç´¢å¼ã
- é¢ç¹ä½ä¸º where æ¥è¯¢æ¡ä»¶çå段éè¦å建索å¼ï¼å°¤å ¶å¨æ°æ®è¡¨æ¯è¾å¤§çæ åµä¸ã
- 为éè¦ç»å¸¸ group by å order by çåå建索å¼ã
- updateãdelete ç where æ¡ä»¶åï¼ä¸è¬ä¹éè¦å建索å¼ã
- distinct å段éè¦å建索å¼ã
- è¶ è¿ä¸ä¸ªè¡¨ç¦æ¢ joinï¼éè¦ join çå段ï¼æ°æ®ç±»åå¿ é¡»ä¸è´ï¼å¤è¡¨å ³èæ¥è¯¢æ¶ï¼ä¿è¯è¢«å ³èçå段éè¦æç´¢å¼ã
- å¨ varchar å段ä¸å»ºç«ç´¢å¼æ¶ï¼å¿ é¡»æå®ç´¢å¼é¿åº¦ï¼æ²¡å¿ è¦å¯¹å ¨å段建ç«ç´¢å¼ï¼æ ¹æ®å®é ææ¬åºå度å³å®ç´¢å¼é¿åº¦å³å¯ãç´¢å¼çé¿åº¦ååºå度æ¯ä¸å¯¹çç¾ä½ãä¸è¬å¯¹å符串类åæ°æ®ï¼é¿åº¦ä¸º 20 çç´¢å¼ï¼åºå度ä¼é«è¾¾ 90 %以ä¸ï¼å¯ä»¥ä½¿ç¨ count(distinct left (ååï¼ç´¢å¼é¿åº¦)) / count(*) çåºå度æ¥ç¡®å®ã
- æ¥è¯¢æ¶ä¸¥ç¦å·¦æ¨¡ç³æè å ¨æ¨¡ç³ãå¦æéè¦è¯·èµ°æç´¢å¼ææ¥è§£å³ãç´¢å¼æä»¶å ·ææå·¦åç¼å¹é ç¹æ§ï¼å¦æ左边çå¼æªç¡®å®ï¼é£ä¹æ æ³ä½¿ç¨æ¤ç´¢å¼ã
- å©ç¨è¦çç´¢å¼æ¥è¿è¡æ¥è¯¢æä½ï¼é¿å å表ãè¦çç´¢å¼å¹¶ä¸æ¯ç´¢å¼çä¸ç§ï¼èåªæ¯ä¸ç§æ¥è¯¢çææã
- 建ç»åç´¢å¼çæ¶åï¼åºå度æé«çå¨æ左边ã
- é²æ¢å å段类åä¸åé æçéå¼è½¬æ¢ï¼å¯¼è´ç´¢å¼å¤±æã
- å建索å¼æ¶é¿å æ以ä¸æ端误解ï¼
- å®æ»¥å¿ç¼ºï¼è®¤ä¸ºä¸ä¸ªæ¥è¯¢å°±éè¦å»ºä¸ä¸ªç´¢å¼ã
- å®ç¼ºå¿æ»¥ï¼è®¤ä¸ºç´¢å¼ä¼æ¶è空é´ï¼ä¸¥éææ ¢è®°å½çæ´æ°ä»¥åè¡çæ°å¢é度ã
- æµå¶å¯ä¸ç´¢å¼ï¼è®¤ä¸ºä¸å¡çå¯ä¸æ§ä¸å¾éè¦å¨åºç¨å±éè¿âå æ¥åæâæ¹å¼è§£å³ã
ç´¢å¼å¤±æ
- å¦æ对索å¼åè¿è¡äºè¡¨è¾¾å¼è®¡ç®ï¼åä¼å¤±æãè¿æ¯ä»¥ employees 表为ä¾ï¼sql å¦ä¸ï¼
æ¥è¯¢èæ¶å¦ä¸ï¼
éè¿ explain æ¥æ¥çè¿æ¡ sql æ¶ï¼ç»æå¦ä¸ï¼
ä»ç»æå¯ä»¥çåºï¼ç´¢å¼æ¯èµ·ä½ç¨çãå½ sql æ¹æå¦ä¸å½¢å¼æ¶ï¼
æ¥è¯¢èæ¶å¦ä¸ï¼
éè¿ explain æ¥æ¥çè¿æ¡ sql æ¶ï¼ç»æå¦ä¸ï¼
ä»ç»æä¸çåºï¼ç´¢å¼å¤±æäºï¼è¿æ¯å 为æ们éè¦æç´¢å¼å段çå¼é½ååºæ¥ï¼ç¶åä¾æ¬¡è¿è¡è¡¨è¾¾å¼ç计ç®æ¥è¿è¡æ¡ä»¶å¤æï¼å æ¤éç¨çå°±æ¯å ¨è¡¨æ«æçæ¹å¼ï¼è¿è¡æ¶é´ä¹ä¼æ ¢å¥½å¤ã
- å¦æ对索å¼å使ç¨å½æ°ä¹ä¼é æ失æï¼ä»¥ employees 表为ä¾ï¼å¨ first_name ä¸å建索å¼ï¼sql å¦ä¸ï¼
æ¥è¯¢èæ¶å¦ä¸ï¼
éè¿ explain æ¥æ¥çè¿æ¡ sql æ¶ï¼ç»æå¦ä¸ï¼
ä»ç»æå¯ä»¥çåºï¼ç´¢å¼æ¯èµ·ä½ç¨çãå½ sql æ¹æå¦ä¸å½¢å¼æ¶ï¼
æ¥è¯¢èæ¶å¦ä¸ï¼
éè¿ explain æ¥æ¥çè¿æ¡ sql æ¶ï¼ç»æå¦ä¸ï¼
ä»ç»æä¸å¯ä»¥çåºï¼ç´¢å¼å¤±æäºï¼éç¨äºå ¨è¡¨æ«æçæ¹å¼ï¼è¿è¡æ¶é´ä¹æ ¢äºå¥½å¤ã
- å¨ where åå¥ä¸ï¼å¦æå¨ or åçæ¡ä»¶åè¿è¡äºç´¢å¼ï¼èå¨ or åçæ¡ä»¶å没æè¿è¡ç´¢å¼ï¼é£ä¹ç´¢å¼ä¼å¤±æã以 employees 表为ä¾ï¼
å¨ first_name ä¸å建äºç´¢å¼ï¼å¨ last_name ä¸æ²¡æå建索å¼ï¼sql å¦ä¸ï¼
æ¥è¯¢èæ¶å¦ä¸ï¼
éè¿ explain æ¥æ¥çè¿æ¡ sql æ¶ï¼ç»æå¦ä¸ï¼
ä»ç»æä¸å¯ä»¥çåºï¼first_name ç´¢å¼å¹¶æ²¡æèµ·ä½ç¨ã
- å½æä»¬ä½¿ç¨ like è¿è¡æ¨¡ç³æ¥è¯¢çæ¶åï¼åé¢ä¸è½æ¯ %ã以 employees 表为ä¾ï¼å¨ first_name ä¸å建äºç´¢å¼ï¼sql å¦ä¸ï¼
æ¥è¯¢èæ¶å¦ä¸ï¼
éè¿ explain æ¥æ¥çè¿æ¡ sql æ¶ï¼ç»æå¦ä¸ï¼
[å¤é¾å¾ç转å失败,æºç«å¯è½æé²çé¾æºå¶,建议å°å¾çä¿åä¸æ¥ç´æ¥ä¸ä¼ (img-vQ3gB179-1610100010787)(/Users/weisong/Library/Application Support/typora-user-images/image-20200901152040550.png)]
ä»ç»æä¸å¯ä»¥çåºï¼first_name ç´¢å¼å¹¶æ²¡æèµ·ä½ç¨ã
æå
ç´¢å¼å¯¹äºè¯å¥½çæ§è½éå¸¸å ³é®ãå°¤å ¶æ¯å½è¡¨ä¸çæ°æ®éè¶æ¥è¶å¤§æ¶ï¼ç´¢å¼å¯¹æ§è½çå½±åæåéè¦ãå½æ°æ®éè¾å°ä¸è´è½½è¾ä½æ¶ï¼ä¸æ°å½çç´¢å¼å¯¹æ§è½çå½±åå¯è½è¿ä¸ææ¾ï¼ä½æ¯å½æ°æ®ééæ¸å¢å¤§æ¯ï¼æ§è½åä¼æ¥å§ä¸éãç´¢å¼ä¼ååºè¯¥æ¯å¯¹æ¥è¯¢æ§è½ä¼åæææçæ段äºãç´¢å¼è½å¤è½»æå°æ¥è¯¢æ§è½æé«å 个æ°é级ï¼âæä¼âçç´¢å¼ææ¶æ¯ä¸ä¸ªâ好çâç´¢å¼æ§è½è¦å¥½ä¸¤ä¸ªæ°é级ã对äºç´¢å¼åºå±çäºè§£æå©äºæ们ä¼åå建çç´¢å¼ã
éå½
employees ç建表è¯å¥ï¼
CREATE TABLE `employees` (
`id` int NOT NULL AUTO_INCREMENT,
`emp_no` int NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=300025 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;