ä»ä¹æ¯ç´¢å¼ï¼
å¨å ³ç³»æ°æ®åºä¸ï¼ç´¢å¼æ¯ä¸ç§åç¬çãç©çç对æ°æ®åºè¡¨ä¸ä¸åæå¤åçå¼è¿è¡æåºçä¸ç§åå¨ç»æï¼å®æ¯æ个表ä¸ä¸åæè¥å¹²åå¼çéååç¸åºçæå表ä¸ç©çæ è¯è¿äºå¼çæ°æ®é¡µçé»è¾æéæ¸ åãç´¢å¼çä½ç¨ç¸å½äºå¾ä¹¦çç®å½ï¼å¯ä»¥æ ¹æ®ç®å½ä¸ç页ç å¿«éæ¾å°æéçå 容ã
è½å®ç°å¿«éå®ä½æ°æ®çä¸ç§åå¨ç»æï¼å ¶è®¾è®¡ææ³æ¯ä»¥ç©ºé´æ¢æ¶é´ã
ç´¢å¼çåç±»
æãæ°æ®ç»æãåç±»ï¼B+treeç´¢å¼ãHashç´¢å¼ãFull-textç´¢å¼ã
æãç©çåå¨ãåç±»ï¼èç°ç´¢å¼ï¼ä¸»é®ç´¢å¼ï¼ãäºçº§ç´¢å¼ï¼è¾ å©ç´¢å¼ï¼ã
æãå段ç¹æ§ãåç±»ï¼ä¸»é®ç´¢å¼ãå¯ä¸ç´¢å¼ãæ®éç´¢å¼ãåç¼ç´¢å¼ã
æãå段个æ°ãåç±»ï¼ååç´¢å¼ãèåç´¢å¼ã
MySQLå¦ä½å®ç°çç´¢å¼æºå¶
è¿ä¸ªè¯é¢æ¯è¾å¤§ï¼å¨MySQLä¸æä¸åçåå¨å¼ææ¯å¦åInnoDB MyISAM Memory çç,æ¯ä¸ç§åå¨å¼æå¨å ¶å é¨å®ç°ç´¢å¼æºå¶çåçä¹ææä¸åãå¨MySQL5.5ä¹åé»è®¤çå°±æ¯InnoDBï¼å¹¶ä¸æ¯ç®å使ç¨æ广æ³çMySQLæ°æ®å¼æï¼é£æ们就以InnoDB为ä¾å±å¼è®²è®²ã
âå¦æ说æ们å¨è¡¨ä¸æ100æ¡æ°æ®ï¼èæ们è¦æ¾åºæ们éè¦çæ°æ®ï¼æåªäºåæ³ï¼
- â æ们æ¯ä¸æ¯å¯ä»¥æç §ä¸ç§é¡ºåºçæ¹å¼ä¸æ¡ä¸æ¡å¾ä¸å»æç´¢ï¼ç´å°å¹é å°æ们éè¦çæ°æ®ï¼è¿æ¯ä¸ç§æ¹æ¡å¨æ¶é´å¤æ度ä¸æ¯O(N)ï¼è½è¯´æçå·®ä½ä¹è½ç¨ã
- â äºåæ¥æ¾æ³ä¹æ¯ä¸ç§å¸¸ç¨çæ¯è¾é«æçæ¥è¯¢ç®æ³ï¼å®çæç´¢æç为O(log(N)),è½è¯´æ¥æ¾æçæ¯æ¯é¡ºåºæ¥æ¾é«äºä¸å°ï¼ä½æ¯å®æ两个åææ¡ä»¶ï¼å¿ é¡»ç¨é¡ºåºåå¨ç»ææ¯å¦æ°ç»ï¼ç¬¬äºä¸ªæ¯å¿ é¡»æç §å ³é®åè¿è¡æåºæåºï¼ä»å°å°å¤§ï¼ã
- â åå¸æ¥æ¾ï¼åå¸æ¥æ¾çç¹æ§æ¯è½å¤åå°ç´æ¥å®åï¼å ¶æçæ éæ¥è¿äºO(1)ï¼åå³äºå²çªçæ°éãä½æ¯æ£å表æ°æ®æ¯æ åºåå¨çï¼æåºè¦èªå·±åï¼ç¬¬äºä¸ªæ¯æ£å表è¿è¦æ©å®¹èæ¶é¿ï¼éå°æ£åå²çªæ§è½ä¸ç¨³å®ã
- â Bæ /B+æ æ¥æ¾çå¤æ度æ¯O(log2(N)), é£ä¹è¿ä¹æ¯InnoDBéç¨çæ°æ®ç»æï¼å¨æ¥æ¾æçä¸çé常é«çï¼ç®æ³å ·ä½çåçå¨åé¢ä»ç»ã
为ä»ä¹InnoDBè¦ä½¿ç¨B+æ ä½ä¸ºç´¢å¼ç»æï¼
InnoDBçç´¢å¼åMyISAMçç´¢å¼æä»ä¹åºå«ï¼
é¦å InnoDBåMyISAMé½æ¯ä½¿ç¨çB+æ å®ç°çï¼ä½æ¯InnoDB使ç¨çæ¯èç°ç´¢å¼èMyISAM使ç¨çæ¯éèç°ç´¢å¼ï¼èç°ç´¢å¼æ ¹æ®ä¸»é®å建ä¸é¢B+æ ï¼å¶åèç¹ååæ¾çæ¯æ°æ®è¡è®°å½ï¼ä¹å¯ä»¥æå¶åç»ç¹ç§°ä¸ºæ°æ®é¡µãéä¿ç¹æ¥è¯´å°±æ¯ææ°æ®åç´¢å¼åå¨åä¸ä¸ªåï¼æ¾å°äºç´¢å¼ä¹å°±æ¾å°äºæ°æ®ã
- å 为å¶åç»ç¹å°ç´¢å¼åæ°æ®æ¾å¨ä¸èµ·ï¼å°±å³å®äºèç°ç´¢å¼çå¯ä¸æ§ï¼ä¸å¼ 表éé¢åªè½æä¸ä¸ªèç°ç´¢å¼ã
- InnoDBå¼æé»è®¤å°ä¸»é®è®¾ç½®ä¸ºèç°ç´¢å¼ï¼ä½å¦æ没æ设置主é®ï¼é£ä¹InnoDBå°ä¼éæ©é空çå¯ä¸ç´¢å¼ä½ä¸ºä»£æ¿ï¼å¦æ没æè¿æ ·çç´¢å¼ï¼InnoDBå°ä¼å®ä¸ä¸ªéå¼ä¸»é®ä½ä¸ºèç°ç´¢å¼ã
- å 为èç°ç´¢å¼ç¹æ®çç©çç»ææå³å®ï¼å¶åç»ç¹å°ç´¢å¼åæ°æ®åæ¾å¨ä¸èµ·ï¼å¨è·åæ°æ®çé度ä¸æ¯æ¯éèç°ç´¢å¼å¿«çã
- èç°ç´¢å¼æ°æ®çåå¨æ¯æåºçï¼å¨è¿è¡æåºæ¥æ¾åèå´æ¥æ¾çé度ä¹æ¯é常快çã
- â ï¸ ä¹æ£å 为æåºæ§ï¼å¨æ°æ®æå ¥æ¶æç §ä¸»é®ç顺åºæå ¥æ¯æå¿«çï¼å¦åå°±ä¼åºç°é¡µåè£çé®é¢ï¼ä¸¥éå½±åæ§è½ã对äºInnoDBæ们ä¸è¬éç¨èªå¢ä½ä¸ºä¸»é®IDã
- 第äºä¸ªé®é¢ä¸»é®æ好ä¸è¦è¿è¡æ´æ°ï¼ä¿®æ¹ä¸»é®ç代价é常大ï¼ä¸ºäºä¿ææåºæ§ä¼å¯¼è´æ´æ°çè¡ç§»å¨ï¼ä¸è¬æ¥è¯´æ们é常设置为主é®ä¸å¯æ´æ°ã
ï¸å¨è¿é¨ååªä»ç»InnoDBåMyISAM主é®ç´¢å¼çä¸åï¼è¾ å©ç´¢å¼åé¢å¨è¯´
èéèç°ç´¢å¼æ¯å°ç´¢å¼åæ°æ®åå¼åå¨ï¼é£ä¹å¨è®¿é®æ°æ®çæ¶åå°±éè¦2次æ¥æ¾ï¼ä½æ¯åInnoDBçéèç°é¨åè¿æ¯ææåºå«ãInnoDBæ¯éè¦æ¥æ¾2次æ ï¼å æ¥æ¾è¾ å©ç´¢å¼æ ï¼åæ¥æ¾èç°ç´¢å¼æ ï¼è¿ä¸ªè¿ç¨ä¹å«å表ï¼ãèMyISAMç主é®ç´¢å¼å¶åç»ç¹çåå¨çé¨åè¿æ¯ææåºå«ãInnoDBä¸åå¨çæ¯ç´¢å¼åèç°ç´¢å¼IDï¼ä½æ¯MyISAMä¸åå¨çæ¯ç´¢å¼åæ°æ®è¡çå°åï¼åªè¦å®ä½å°±å¯ä»¥è·åå°ã
å ¶å®çå°è¿ä¸ªé¨åä¼æä¸ä¸ªçæï¼é£å°±æ¯InnoDBçèç°ç´¢å¼æ¯MyISAMç主é®å¿«ï¼é£ä¸ºä»ä¹ä¼è®¤ä¸ºMyISAMæ¥è¯¢æçæ¯InnoDBå¿«å¢ï¼
- 第ä¸ç¹ï¼å¯¹äºä¸¤è åå¨å¼æççæ§è½åæä¸è½åªç主é®ç´¢å¼ï¼æ们ä¹è¦ççè¾ å©ç´¢å¼ï¼å头æ们ä»ç»è¿InnoDBè¾ å©ç´¢å¼ä¼åå¨ä¸ä¸ªå表çè¿ç¨ãèMyISAMçè¾ å©ç´¢å¼å主é®ç´¢å¼çåçæ¯ä¸æ ·çï¼å¹¶æ²¡æä»ä¹åºå«ã
- (éç¹) InnoDB对MVCCçæ¯æï¼äºç©æ¯æ¯è¾å½±åæ§è½çï¼å°±ç®ä½ 没ç¨ä½æ¯ä¹çä¸äºæ£æ¥åç»´æ¤ï¼èMyISAMè¿åå´æ²¡æè¿æ¹é¢çå½±åï¼å ·ä½MVCC详解å°å¨åé¢ç« èæè¿°ã
å¦æä¸ä¸ªè¡¨æ²¡æ主é®ç´¢å¼é£è¿ä¼å建B+æ åï¼
çæ¡æ¯ä¼çï¼ï¼ï¼
InnoDBæ¯MySQLä¸çä¸ç§åå¨å¼æï¼å®ä¼ä¸ºæ¯ä¸ªè¡¨å建ä¸ä¸ªä¸»é®ç´¢å¼ãå¦æ表没ææç¡®ç主é®ç´¢å¼ï¼InnoDBä¼ä½¿ç¨ä¸ä¸ªéèçãèªå¨çæç主é®æ¥å建索å¼ãè¿ä¸ªéèç主é®ç´¢å¼ä½¿ç¨çå°±æ¯B+æ ç»æãå æ¤ï¼å¨InnoDBä¸ï¼å³ä½¿è¡¨æ²¡ææç¡®ç主é®ç´¢å¼ï¼ä¹ä¼å建ä¸ä¸ªB+æ ç´¢å¼ã
ç´¢å¼çä¼ç¼ºç¹æ¯ä»ä¹ï¼
æ°æ®æ¯åå¨å¨ç£çä¸çï¼æä½ç³»ç»è¯»åç£ççæå°åä½æ¯åï¼å¦æ没æç´¢å¼ï¼ä¼å è½½ææçæ°æ®å°å åï¼ä¾æ¬¡è¿è¡æ£ç´¢ï¼å è½½çæ»æ°æ®ä¼å¾å¤ï¼ç£çIOå¤ã
å¦ææäºç´¢å¼ï¼ä¼ä»¥å¦å·ä¸ºkeyå建索å¼ï¼MySQLéç¨B+æ ç»æåå¨ï¼ä¸æ¹é¢å è½½çæ°æ®åªæå¦å·å主é®IDï¼å¦ä¸æ¹ä¾¿éç¨äºå¤å平衡æ ï¼å®ä½å°æå®å¦å·ä¼å¾å¿«ï¼æ ¹æ®å ³èçIDå¯ä»¥å¿«éå®ä½å°å¯¹åºè¡çæ°æ®ï¼æ以æ£ç´¢çé度ä¼å¾å¿«ï¼å 为å è½½çæ»æ°æ®å¾å°ï¼ç£çIOå°ã
å¯è§ï¼ç´¢å¼å¯ä»¥å¤§å¤§åå°æ£ç´¢æ°æ®çèå´ãåå°ç£çIOï¼ä½¿æ¥è¯¢é度å¾å¿«ï¼å 为ç£çIOæ¯å¾æ ¢çï¼æ¯ç±å®ç硬件ç»æå³å®çã
â ä¼ç¹
- ç´¢å¼è½å¤æé«æ°æ®æ£ç´¢çæçï¼éä½æ°æ®åºçIOææ¬ã
- éè¿å建å¯ä¸æ§ç´¢å¼ï¼å¯ä»¥ä¿è¯æ°æ®åºè¡¨ä¸æ¯ä¸è¡æ°æ®çå¯ä¸æ§ï¼å建å¯ä¸ç´¢å¼
- å¨ä½¿ç¨åç»åæåºåå¥è¿è¡æ°æ®æ£ç´¢æ¶ï¼åæ ·å¯ä»¥æ¾èåå°æ¥è¯¢ä¸åç»åæåºçæ¶é´
- å é两个表ä¹é´çè¿æ¥ï¼ä¸è¬æ¯å¨å¤é®ä¸å建索å¼
â 缺ç¹
- éè¦å ç¨ç©ç空é´,建ç«çç´¢å¼è¶å¤éè¦ç空é´è¶å¤§
- å建索å¼åç»´æ¤ç´¢å¼è¦èè´¹æ¶é´ï¼è¿ç§æ¶é´éçæ°æ®éçå¢å èå¢å
- ä¼éä½è¡¨çå¢å æ¹çæçï¼å 为æ¯æ¬¡å¢å æ¹ç´¢å¼éè¦è¿è¡å¨æç»´æ¤ï¼å¯¼è´æ¶é´åé¿
使ç¨ç´¢å¼ä¸å®è½æåæçåï¼ï¼ä»ä¹æ¶åéåå建索å¼ï¼ä»ä¹æ¶åä¸éåå建索å¼ï¼ï¼
çæ¡æ¯ä¸ä¸å®ï¼ä»»ä½äºç©æ们é½åºè¯¥è¾©è¯ççï¼ç¥éå ¶è¿è¡é»è¾ä»èå©ç¨å ¶ä¼ç¹ï¼å°½éé¿å¼å®ç缺ç¹ãå¨ä¸é¢æ们已ç»å大家ä»ç»äºè¿äºç´¢å¼å¸¦æ¥çä¼ç¼ºç¹ï¼é£æ¥ä¸æ¥å°±å大家å享å 个建索å¼çæ示ã
- 对äºæ¥è¯¢ä¸ä½¿ç¨çå°çå段尽éä¸è¦å建索å¼ï¼å建索å¼æ¯æææ¬çï¼ç©ºé´å ç¨ãå建åç»´æ¤ææ¬ãå¢å æ¹æçéä½ã
- 对äºæ°æ®å¯åº¦å°çåä¹ä¸å»ºè®®å建索å¼ï¼å 为InnoDBä¸ç´¢å¼çB+æ æå³å®çï¼ä½ è½å¸¦æ¥çæçæåé常æéãï¼ä½æ¯ä¹æä¾å¤ï¼ä¸¾ä¸ªä¾åæ举å¼ï¼1ï¼2ï¼3ï¼ï¼å¤´ä¸¤ä¸ªå æ¯ç¾åä¹1%ï¼ç¬¬ä¸ä¸ªå æ¯99%ï¼å¹¶ä¸å¤´ä¸¤ä¸ªæç´¢å æ¯æ¯ç¬¬ä¸ä¸ªé«å¾å¤ï¼é£ä¹æ¯å¯ä»¥å»ºè®®å ç´¢å¼çï¼ãInnoDBçè¾ å©ç´¢å¼æ¯åå¨å表çï¼å¦ææ°æ®å¯åº¦è¿å°ï¼é£ä¹æ§è½å¯è½è¿ä¸å¦å ¨è¡¨æ«ãåä¸é¢è¿ç§åºæ¯å ·æç¹æ®æ§ï¼ä¹è¯´æä¸ä¸ªéçï¼å¨å¤§å¤æ°åºæ¯ä¸å»ºè®®å¯è½éç¨ï¼ä½æ¯ä¹æä¸éç¨çæ¶åï¼æ们ä¸è¦æè¿ç§å»ºè®®å½ä½éå¾ã
å¦ä½æ¥çä¸ä¸ªè¡¨çç´¢å¼ï¼
â¬ï¸ ä¸ä»£ç â¬ï¸
show index from table_name (表å)
æåªäºæ åµä¼å¯¼è´ç´¢å¼å¤±æï¼
è¿ä¸ªé®é¢è¦åçæ¬åçï¼ï¼ï¼çæ¬ä¸åå¯è½ä¼å¯¼è´ç´¢å¼å¤±æçåºæ¯ä¹ä¸åï¼ç´æ¥ç»çæ¡çé½æ¯èæµæ°ï¼ï¼ï¼
è¿éåçåºäºææ°MySQL8çæ¬ï¼MySQL8失æç以åçæ¬ä¹å¤±æï¼MySQL8ä¸å¤±æçï¼ä»¥åå¯è½ä¼å¤±æã
- 使ç¨like并ä¸æ¯å·¦è¾¹å¸¦%, å³è¾¹å¯ä»¥å¸¦ä¼èµ°ç´¢å¼ï¼ä½æ¯å¹¶ä¸ç»å¯¹ï¼è¯¦ç»è§£éçä¸é¢likeä¸é¢åæï¼
- éå¼ç±»å转æ¢ï¼ç´¢å¼å段ä¸æ¡ä»¶æå ³èå段çç±»åä¸ä¸è´ãï¼æ¯å¦ä½ çå段æ¯intï¼ä½ ç¨å符串æ¹å¼å»æ¥è¯¢ä¼å¯¼è´ç´¢å¼å¤±æï¼ã
- å¨whereæ¡ä»¶éé¢å¯¹ç´¢å¼å使ç¨è¿ç®æè 使ç¨å½æ°ã
- 使ç¨ORä¸åå¨éç´¢å¼å
- å¨whereæ¡ä»¶ä¸ä¸¤ååæ¯è¾ä¼å¯¼è´ç´¢å¼å¤±æ
- 使ç¨INå¯è½ä¸ä¼èµ°ç´¢å¼ï¼MySQLç¯å¢åéeq_range_index_dive_limitçå¼å¯¹INè¯æ³æå¾å¤§å½±åï¼è¯¥åæ°è¡¨ç¤ºä½¿ç¨ç´¢å¼æ åµä¸INä¸åæ°çæ大æ°éãMySQL 5.7.3以åä¹åççæ¬ä¸ï¼eq_range_index_dive_limitçé»è®¤å¼ä¸º10ï¼ä¹åççæ¬é»è®¤å¼ä¸º200ãæ们æ¿MySQL8.0.19举ä¾ï¼eq_range_index_dive_limit=200表示å½IN (...)ä¸çå¼ >200个æ¶ï¼è¯¥æ¥è¯¢ä¸å®ä¸ä¼èµ°ç´¢å¼ã<=200åå¯è½ç¨å°ç´¢å¼ãï¼
- 使ç¨é主é®èå´æ¡ä»¶æ¥è¯¢æ¶ï¼é¨åæ åµç´¢å¼å¤±æ ã
- 使ç¨order byå¯è½ä¼å¯¼è´ç´¢å¼å¤±æ
- is null is not null â å¯è½ä¼å¯¼è´ç´¢å¼å¤±æ
å¦æ表ä¸æå段为NULL ç´¢å¼æ¯å¦ä¼å¤±æï¼
é¦å 讲çæ¡ä¸ä¸å®ãå³ä½¿æ们使ç¨is null æè is not null å®å ¶å®é½æ¯ä¼èµ°ç´¢å¼çãé£ä¸ºä»ä¹ä¼æè¿æ ·çè¨è®ºå¢ï¼è¿éé¦å å°±å¾æ¥è®²è®²NULLå¼æ¯æä¹å¨è®°å½ä¸åå¨çï¼åæ¯æä¹å¨B+æ ä¸åå¨çå¢ã
é£ä¹å¨InnoDBä¸å为èç°ç´¢å¼åéèç°ç´¢å¼ä¸¤ç§ï¼èç°ç´¢å¼æ¬èº«æ¯ä¸å 许记å½ä¸ºç©ºçï¼æ以å¯ä»¥ä¸ä¸ç¨èèï¼é£ä¹å°±å©ä¸éèç°ç´¢å¼ä¹å°±æ¯æ们çè¾ å©ç´¢å¼ã
é£æ¢ç¶IS NULLãIS NOT NULLã!=è¿äºæ¡ä»¶é½å¯è½ä½¿ç¨å°ç´¢å¼ï¼é£å°åºä»ä¹æ¶åç´¢å¼ï¼ä»ä¹æ¶åéç¨å ¨è¡¨æ«æå¢ï¼
é¦å æ们å¾ç¥é两个ä¸è¥¿ï¼ç¬¬ä¸ä¸ªå¨InnoDBå¼ææ¯å¦ä½åå¨NULLå¼çï¼ç¬¬äºä¸ªé®é¢æ¯ç´¢å¼æ¯å¦ä½åå¨NULLå¼çï¼è¿æ ·æ们æè½ä»æ ¹ä¸ç解NULLå¨ä»ä¹åºæ¯èµ°ç´¢å¼ï¼å¨ä»ä¹åºæ¯ä¸èµ°ç´¢å¼ã
1â£ï¸ å¨InnoDBå¼ææ¯å¦ä½åå¨NULLå¼çï¼
InnoDBå¼æéè¿ä½¿ç¨ä¸ä¸ªç¹æ®çå¼æ¥è¡¨ç¤ºnullï¼è¿ä¸ªå¼é常被称为"null bitmap"ãnull bitmapæ¯ä¸ä¸ªäºè¿å¶ä½åºåï¼ç¨æ¥æ 记表ä¸æ¯ä¸ä¸ªåæ¯å¦ä¸ºnullãå½null bitmapä¸å¯¹åºçä½ä¸º1æ¶ï¼è¡¨ç¤ºå¯¹åºçå为nullï¼å½null bitmapä¸å¯¹åºçä½ä¸º0æ¶ï¼è¡¨ç¤ºå¯¹åºçåä¸ä¸ºnullãå¨å®é åå¨æ¶ï¼InnoDBå¼æä¼å°null bitmapä½ä¸ºè¡è®°å½çä¸é¨åï¼åå¨å¨è¡è®°å½çå¼å¤´ï¼è¿æ ·å¯ä»¥å¨è¯»åè¡è®°å½æ¶å¿«éå¤ææ¯ä¸ªåæ¯å¦ä¸ºnullã
ä»å¤´å¼å§è¯´ç解起æ¥ä¼æ¯è¾å®¹æï¼ç解äºç¬å 表空é´æ件就æ´å®¹æç解è¡æ ¼å¼äºï¼æ¥çå¾ä¸çï¼
å½æ们å建表çæ¶åé»è®¤ä¼å建ä¸ä¸ª*.idb æ件ï¼è¿ä¸ªæ件å称为ç¬å 表空é´æ件ï¼å®æ¯ç±æ®µãåºã页ãè¡ç»æãInnoDBåå¨å¼æç¬å 表空é´å¤§è´å¦ä¸å¾ï¼
Segment(表空é´) æ¯ç±å个段ï¼segmentï¼ç»æçï¼æ®µæ¯ç±å¤ä¸ªåºï¼extentï¼ç»æçã段ä¸è¬å为æ°æ®æ®µãç´¢å¼æ®µååæ»æ®µçã
- æ°æ®æ®µ åæ¾ B + æ çå¶åèç¹çåºçéå
- ç´¢å¼æ®µ åæ¾ B + æ çéå¶åèç¹çåºçéå
- åæ»æ®µ åæ¾çæ¯åæ»æ°æ®çåºçéåï¼ MVCCå°±æ¯å©ç¨äºåæ»æ®µå®ç°äºå¤çæ¬æ¥è¯¢æ°æ®
Extent(åº) å¨è¡¨ä¸æ°æ®é大çæ¶åï¼ä¸ºæ个索å¼åé 空é´çæ¶åå°±ä¸åæç §é¡µä¸ºåä½åé äºï¼èæ¯æç §åºï¼extentï¼ä¸ºåä½åé ãæ¯ä¸ªåºç大å°ä¸º 1MBï¼å¯¹äº 16KB ç页æ¥è¯´ï¼è¿ç»ç 64 个页ä¼è¢«å为ä¸ä¸ªåºï¼è¿æ ·å°±ä½¿å¾é¾è¡¨ä¸ç¸é»ç页çç©çä½ç½®ä¹ç¸é»ï¼å°±è½ä½¿ç¨é¡ºåº I/O äº ã
ï¼æ们ç¥é InnoDB åå¨å¼ææ¯ç¨ B+ æ æ¥ç»ç»æ°æ®çãB+ æ ä¸æ¯ä¸å±é½æ¯éè¿ååé¾è¡¨è¿æ¥èµ·æ¥çï¼å¦ææ¯ä»¥é¡µä¸ºåä½æ¥åé åå¨ç©ºé´ï¼é£ä¹é¾è¡¨ä¸ç¸é»ç两个页ä¹é´çç©çä½ç½®å¹¶ä¸æ¯è¿ç»çï¼å¯è½ç¦»å¾é常è¿ï¼é£ä¹ç£çæ¥è¯¢æ¶å°±ä¼æ大éçéæºI/Oï¼éæº I/O æ¯éå¸¸æ ¢çã解å³è¿ä¸ªé®é¢ä¹å¾ç®åï¼å°±æ¯è®©é¾è¡¨ä¸ç¸é»ç页çç©çä½ç½®ä¹ç¸é»ï¼è¿æ ·å°±å¯ä»¥ä½¿ç¨é¡ºåº I/O äºï¼é£ä¹å¨èå´æ¥è¯¢ï¼æ«æå¶åèç¹ï¼çæ¶åæ§è½å°±ä¼å¾é«ãï¼
Page(页) è®°å½æ¯æç §è¡æ¥åå¨çï¼ä½æ¯æ°æ®åºç读å并ä¸ä»¥ãè¡ã为åä½ï¼å¦åä¸æ¬¡è¯»åï¼ä¹å°±æ¯ä¸æ¬¡ I/O æä½ï¼åªè½å¤çä¸è¡æ°æ®ï¼æçä¼é常ä½ã
å æ¤ï¼InnoDB çæ°æ®æ¯æã页ã为åä½æ¥è¯»åçï¼ä¹å°±æ¯è¯´ï¼å½éè¦è¯»ä¸æ¡è®°å½çæ¶åï¼å¹¶ä¸æ¯å°è¿ä¸ªè¡è®°å½ä»ç£ç读åºæ¥ï¼èæ¯ä»¥é¡µä¸ºåä½ï¼å°å ¶æ´ä½è¯»å ¥å åã
é»è®¤æ¯ä¸ªé¡µç大å°ä¸º 16KBï¼ä¹å°±æ¯æå¤è½ä¿è¯ 16KB çè¿ç»åå¨ç©ºé´ã
é¡µæ¯ InnoDB åå¨å¼æç£ç管ççæå°åå ï¼æå³çæ°æ®åºæ¯æ¬¡è¯»åé½æ¯ä»¥ 16KB 为åä½çï¼ä¸æ¬¡æå°ä»ç£çä¸è¯»å 16K çå 容å°å åä¸ï¼ä¸æ¬¡æå°æå åä¸ç 16K å 容å·æ°å°ç£çä¸ã
页çç±»åæå¾å¤ï¼å¸¸è§çææ°æ®é¡µãundo æ¥å¿é¡µã溢åºé¡µççãæ°æ®è¡¨ä¸çè¡è®°å½æ¯ç¨ãæ°æ®é¡µãæ¥ç®¡ççï¼æ°æ®é¡µçç»æè¿éæå°±ä¸è®²ç»è¯´äºï¼æ»ä¹ç¥é表ä¸çè®°å½åå¨å¨ãæ°æ®é¡µãéé¢å°±è¡ã
Row(è¡) æ°æ®åºè¡¨ä¸çè®°å½é½æ¯æè¡ï¼rowï¼è¿è¡åæ¾çï¼æ¯è¡è®°å½æ ¹æ®ä¸åçè¡æ ¼å¼ï¼æä¸åçåå¨ç»æã
éç¹æ¥äºï¼ï¼ï¼
InnoDB æä¾äº 4 ç§è¡æ ¼å¼ï¼åå«æ¯ RedundantãCompactãDynamicå Compressed è¡æ ¼å¼ã
- Redundant æ¯å¾å¤èçè¡æ ¼å¼äºï¼ MySQL 5.0 çæ¬ä¹åç¨çè¡æ ¼å¼ï¼ç°å¨åºæ¬æ²¡äººç¨äºï¼é£å°±ä¸å±å¼è¯¦è®²äºã
- MySQL 5.0 ä¹åå¼å ¥äº Compact è¡è®°å½åå¨æ¹å¼ï¼ç±äº Redundant ä¸æ¯ä¸ç§ç´§åçè¡æ ¼å¼ï¼èéç¨æ´ä¸ºç´§åçCompact ï¼è®¾è®¡çåè¡·å°±æ¯ä¸ºäºè®©ä¸ä¸ªæ°æ®é¡µä¸å¯ä»¥åæ¾æ´å¤çè¡è®°å½ï¼ä» MySQL 5.1 çæ¬ä¹åï¼è¡æ ¼å¼é»è®¤è®¾ç½®æ Compactã
- Dynamic å Compressed 两个é½æ¯ç´§åçè¡æ ¼å¼ï¼å®ä»¬çè¡æ ¼å¼é½å Compact å·®ä¸å¤ï¼å 为é½æ¯åºäº Compact æ¹è¿ä¸ç¹ä¸è¥¿ãä» MySQL5.7 çæ¬ä¹åï¼é»è®¤ä½¿ç¨ Dynamic è¡æ ¼å¼ã
é£ä¹æ们æ¥ççCompactéé¢é¿ä»ä¹æ ·ï¼å 混个è¸çã
è¿éç®åä»ç»ä¸ä¸ï¼Compactè¡æ ¼å¼å ¶ä»å 容åé¢åç¬åºä¸ä¸ªç« èä»ç»ã
- NULLå¼å表ï¼æ¬é®é¢ä»ç»éç¹ï¼
- 表ä¸çæäºåå¯è½ä¼åå¨ NULL å¼ï¼å¦ææè¿äº NULL å¼é½æ¾å°è®°å½ççå®æ°æ®ä¸ä¼æ¯è¾æµªè´¹ç©ºé´ï¼æ以 Compact è¡æ ¼å¼æè¿äºå¼ä¸º NULL çååå¨å° NULLå¼å表ä¸ãå¦æåå¨å 许 NULL å¼çåï¼åæ¯ä¸ªå对åºä¸ä¸ªäºè¿å¶ä½ï¼bitï¼ï¼äºè¿å¶ä½æç §åç顺åºéåºæåã
- äºè¿å¶ä½çå¼ä¸º1æ¶ï¼ä»£è¡¨è¯¥åçå¼ä¸ºNULLãäºè¿å¶ä½çå¼ä¸º0æ¶ï¼ä»£è¡¨è¯¥åçå¼ä¸ä¸ºNULLãå¦å¤ï¼NULL å¼åè¡¨å¿ é¡»ç¨æ´æ°ä¸ªåèçä½è¡¨ç¤ºï¼1åè8ä½ï¼ï¼å¦æ使ç¨çäºè¿å¶ä½ä¸ªæ°ä¸è¶³æ´æ°ä¸ªåèï¼åå¨åèçé«ä½è¡¥ 0ã
- å½ç¶NULL å¼å表ä¹ä¸æ¯å¿ é¡»çãå½æ°æ®è¡¨çå段é½å®ä¹æ NOT NULL çæ¶åï¼è¿æ¶å表éçè¡æ ¼å¼å°±ä¸ä¼æ NULL å¼å表äºãæ以å¨è®¾è®¡æ°æ®åºè¡¨çæ¶åï¼é常é½æ¯å»ºè®®å°å段设置为 NOT NULLï¼è¿æ ·å¯ä»¥èç 1 åèç空é´ï¼NULL å¼å表å ç¨ 1 åè空é´ï¼ã
- ãNULL å¼å表ãç空é´ä¸æ¯åºå® 1 åèçãå½ä¸æ¡è®°å½æ 9 个å段å¼é½æ¯ NULLï¼é£ä¹å°±ä¼å建 2 åè空é´çãNULL å¼å表ãï¼ä»¥æ¤ç±»æ¨ã
2â£ï¸ ç´¢å¼æ¯å¦ä½åå¨NULLå¼çï¼
æ们ç¥éInnoDBå¼æä¸æç §ç©çåå¨çä¸åå为èç°ç´¢å¼åéèç°ç´¢å¼ï¼èç°ç´¢å¼ä¹å°±æ¯ä¸»é®ç´¢å¼ï¼é£ä¹æ¯ä¸å 许为空çãé£å°±ä¸åæ们æ¬é®é¢ç讨论èå´ï¼æ们éç¹æ¥ççéèç°ç´¢å¼ï¼éèç°ç´¢å¼æ¯å 许å¼ä¸ºç©ºçã
å¨InnoDBä¸éèç°ç´¢å¼æ¯éè¿B+æ çæ¹å¼è¿è¡åå¨ç
ä»å¾ä¸å¯ä»¥çåºï¼å¯¹äºs1表çäºçº§ç´¢å¼idx_key1æ¥è¯´ï¼å¼ä¸ºNULLçäºçº§ç´¢å¼è®°å½é½è¢«æ¾å¨äºB+æ çæ左边ï¼è¿æ¯å 为设计InnoDBç大åæè¿æ ·çè§å®ï¼
We define the SQL null to be the smallest possible value of a field.
ä¹å°±æ¯è¯´ä»ä»¬æSQLä¸çNULLå¼è®¤ä¸ºæ¯åä¸æå°çå¼ãå¨éè¿äºçº§ç´¢å¼idx_key1对åºçB+æ å¿«éå®ä½å°å¶åèç¹ä¸ç¬¦åæ¡ä»¶çæ左边çé£æ¡è®°å½åï¼ä¹å°±æ¯æ¬ä¾ä¸idå¼ä¸º521çé£æ¡è®°å½ä¹åï¼å°±å¯ä»¥é¡ºçæ¯æ¡è®°å½é½æçnext_recordå±æ§æ²¿çç±è®°å½ç»æçååé¾è¡¨å»è·åè®°å½äºï¼ç´å°ææ¡è®°å½çkey1åä¸ä¸ºNULLã
3â£ï¸ æ们äºè§£äºä¸é¢ç两个é®é¢ä¹åï¼æ们就å¯ä»¥æ¥ççï¼ä½¿ä¸ä½¿ç¨ç´¢å¼çä¾æ®æ¯ä»ä¹äº
å®é ä¸æ¥è¯´æ们ç¨is null is not null â è¿äºæ¡ä»¶é½æ¯è½èµ°ç´¢å¼çï¼é£ä»ä¹æ¶åèµ°ç´¢å¼ä»ä¹æ¶åèµ°å ¨è¡¨æ«æå¢ï¼
æ»ç»èµ·æ¥å°±æ¯ä¸¤ä¸ªåï¼ææ¬ï¼ï¼ï¼
å¦ä½å»åº¦éææ¬è®¡ç®ä½¿ç¨æ个索å¼æ§è¡æ¥è¯¢çææ¬å°±é常å¤æäºï¼å±å¼è®²è¿ä¸ªè¯é¢å°±åä¸ä¸æ¥äºï¼åé¢èèåç¬åä¸ä¸ªç¯å¹ å»è®²ã
è¿éæ»ç»æ§è®²è®²ï¼ç¬¬ä¸ä¸ªï¼è¯»åäºçº§ç´¢å¼è®°å½çææ¬ï¼ç¬¬äºï¼å°äºçº§ç´¢å¼è®°å½æ§è¡å表æä½ï¼ä¹å°±æ¯å°èç°ç´¢å¼ä¸æ¾å°å®æ´çç¨æ·è®°å½æä½æä»åºçææ¬ã
è¦æ«æçäºçº§ç´¢å¼è®°å½æ¡æ°è¶å¤ï¼é£ä¹éè¦æ§è¡çå表æä½ç次æ°ä¹å°±è¶å¤ï¼è¾¾å°äºæ个æ¯ä¾æ¶ï¼ä½¿ç¨äºçº§ç´¢å¼æ§è¡æ¥è¯¢çææ¬ä¹å°±è¶ è¿äºå ¨è¡¨æ«æçææ¬ï¼ä¸¾ä¸ä¸ªæ端çä¾åï¼æ¯æ¹è¯´è¦æ«æçå ¨é¨çäºçº§ç´¢å¼è®°å½ï¼é£å°±è¦å¯¹æ¯æ¡è®°å½æ§è¡ä¸éå表æä½ï¼èªç¶ä¸å¦ç´æ¥æ«æèç°ç´¢å¼æ¥çå¿«ï¼
æ以MySQLä¼åå¨å¨çæ£æ§è¡æ¥è¯¢ä¹åï¼å¯¹äºæ¯ä¸ªå¯è½ä½¿ç¨å°çç´¢å¼æ¥è¯´ï¼é½ä¼é¢å 计ç®ä¸ä¸éè¦æ«æçäºçº§ç´¢å¼è®°å½çæ°éï¼æ¯æ¹è¯´å¯¹äºä¸è¾¹è¿ä¸ªæ¥è¯¢ï¼
SELECT * FROM s1 WHERE key1 IS NULL;
ä¼åå¨ä¼åæåºæ¤æ¥è¯¢åªéè¦æ¥æ¾key1å¼ä¸ºNULLçè®°å½ï¼ç¶å访é®ä¸ä¸äºçº§ç´¢å¼idx_key1ï¼çä¸ä¸å¼ä¸ºNULLçè®°å½æå¤å°ï¼å¦æ符åæ¡ä»¶çäºçº§ç´¢å¼è®°å½æ°éè¾å°ï¼é£ä¹ç»è®¡ç»ææ¯ç²¾ç¡®çï¼å¦æ太å¤çè¯ï¼ä¼éç¨ä¸å®çæ段计ç®ä¸ä¸ªæ¨¡ç³çå¼ï¼å½ç¶ç®æ³ä¹æ¯è¾éº»ç¦ï¼æ们就ä¸å±å¼è¯´äºï¼ï¼è¿ç§å¨æ¥è¯¢çæ£æ§è¡åä¼åå¨å°±çå 访é®ç´¢å¼æ¥è®¡ç®éè¦æ«æçç´¢å¼è®°å½æ°éçæ¹å¼ç§°ä¹ä¸ºindex diveãå½ç¶ï¼å¯¹äºæäºæ¥è¯¢ï¼æ¯æ¹è¯´WHEREåå¥ä¸æINæ¡ä»¶ï¼å¹¶ä¸INæ¡ä»¶ä¸å å«è®¸å¤åæ°çè¯ï¼æ¯æ¹è¯´è¿æ ·ï¼
SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');
è¿æ ·çè¯éè¦ç»è®¡çkey1å¼æå¨çåºé´å°±å¤ªå¤äºï¼è¿æ ·å°±ä¸è½éç¨index diveçæ¹å¼å»çæ£ç访é®äºçº§ç´¢å¼idx_key1ï¼èæ¯éè¦éç¨ä¹åå¨èå°é产ççä¸äºç»è®¡æ°æ®å»ä¼°ç®å¹é çäºçº§ç´¢å¼è®°å½æå¤å°æ¡ï¼å¾æ¾ç¶æ ¹æ®ç»è®¡æ°æ®å»ä¼°ç®è®°å½æ¡æ°æ¯index diveçæ¹å¼ç²¾ç¡®æ§å·®äºå¾å¤ï¼ã
åæ£ä¸è®ºéç¨index diveè¿æ¯ä¾æ®ç»è®¡æ°æ®ä¼°ç®ï¼æç»è¦å¾å°ä¸ä¸ªéè¦æ«æçäºçº§ç´¢å¼è®°å½æ¡æ°ï¼å¦æè¿ä¸ªæ¡æ°å æ´ä¸ªè®°å½æ¡æ°çæ¯ä¾ç¹å«å¤§ï¼é£ä¹å°±è¶åäºä½¿ç¨å ¨è¡¨æ«ææ§è¡æ¥è¯¢ï¼å¦åè¶åäºä½¿ç¨è¿ä¸ªç´¢å¼æ§è¡æ¥è¯¢ã
ç解äºè¿ä¸ªä¹å°±å¥½ç解为ä»ä¹å¨WHEREåå¥ä¸åºç°IS NULLãIS NOT NULLã!=è¿äºæ¡ä»¶ä»ç¶å¯ä»¥ä½¿ç¨ç´¢å¼ï¼æ¬è´¨ä¸é½æ¯ä¼åå¨å»è®¡ç®ä¸ä¸å¯¹åºçäºçº§ç´¢å¼æ°éå ææè®°å½æ°éçæ¯å¼èå·²ã
大家å¯ä»¥çå°ï¼MySQLä¸å³å®ä½¿ä¸ä½¿ç¨æ个索å¼æ§è¡æ¥è¯¢çä¾æ®å¾ç®åï¼å°±æ¯ææ¬å¤ä¸å¤å°ãèä¸æ¯æ¯å¦å¨WHEREåå¥ä¸ç¨äºIS NULLãIS NOT NULLã!=è¿äºæ¡ä»¶ã大家以åä¹å¤å¤è¾è°£å§ï¼æ²¡é£ä¹å¤æï¼åªæ¯ä¸ä¸ªææ¬èå·²ã
为ä»ä¹LIKE以%å¼å¤´ç´¢å¼ä¼å¤±æï¼
é¦å ççB+æ æ¯å¦ä½æ¥æ¾æ°æ®çï¼
æ¥æ¾æ°æ®æ¶ï¼MySQLä¼ä»æ ¹èç¹å¼å§ï¼æç §ä»å·¦å°å³ç顺åºæ¯è¾æ¥è¯¢æ¡ä»¶åèç¹ä¸çé®å¼ãå¦ææ¥è¯¢æ¡ä»¶å°äºèç¹ä¸çé®å¼ï¼åè·³å°è¯¥èç¹çå·¦åèç¹ç»§ç»æ¥æ¾ï¼å¦ææ¥è¯¢æ¡ä»¶å¤§äºèç¹ä¸çé®å¼ï¼åè·³å°è¯¥èç¹çå³åèç¹ç»§ç»æ¥æ¾ï¼å¦ææ¥è¯¢æ¡ä»¶çäºèç¹ä¸çé®å¼ï¼å继ç»æ¥æ¾è¯¥èç¹çä¸ä¸ä¸ªèç¹ã
æ¯å¦è¯´ææä¸é¢è¿æ¡SQLï¼
select * from `user` where nickname like '%å¥';
å¦ææ°æ®åºä¸åå¨åå¥ åå¥ è¥¿å¥ ä¸å¥ ï¼é£ä¹å¨B+æ ä¸æç´¢çæçåå ¨è¡¨æ«æè¿æä»ä¹åºå«å¢ï¼
æèµ°èç°ç´¢å¼å ¨è¡¨æ«æè¿ä¸ç¨å表ã
æåå¨æ©å±è®²ä¸ä¸ªç¹ï¼å ¶å®ä¸ä¸å®ä¼å¯¼è´ç´¢å¼å¤±æã举个ä¾åï¼
create table `user`(
id int primary key auto_increment,
name varchar(20),
index idx_name(name),
);
// é£ä¹è¿ç§æ
åµæ¯ä¼èµ°ç´¢å¼çã
select id,name from `user` where name like '%å¥';
为ä»ä¹è¯´ä¸é¢çä¾åä¼èµ°ç´¢å¼å¢ï¼
é¦å æ们éè¦æ¥è¯¢çid name è¿ä¸¤ä¸ªå段æ¯ä¸æ¯é½å¨æ们çè¾ å©ç´¢å¼ä¸ï¼å¶åèç¹æ¯ä¸æ¯åçç´¢å¼å¼å主é®å¼ï¼æ以æ们åªè¦æ¥è¾ å©ç´¢å¼å°±å¯ä»¥ç´æ¥æ¿å°æ们çéè¦çç»æäºï¼é£ä¹è¿ä¸ªå«åç´¢å¼è¦çãæ们è§å¯æ§è¡è®¡åä¼åç°å®çæ¥è¯¢çº§å«æ¯index ï¼å ¶å®ä¹æ¯å ¨è¡¨éåäºè¾ å©ç´¢å¼ã
第äºä¸ªé®é¢æ¥äºï¼é£ä¸ºä»ä¹å°±è¦èµ°è¾ å©ç´¢å¼èä¸æ¯èµ°å ¨è¡¨æ«æå¢ï¼
å ä¸ºè¾ å©ç´¢å¼ä¸è®°å½çä¸è¥¿æ¯ä¸»é®ç´¢å¼å°äºå¾å¤ï¼åªæç´¢å¼å¼å主é®å¼ï¼ä½æ¯ä¸»é®ç´¢å¼ä¸å°±å å«äºï¼å ¶ä»å¼ãäºç©IDãMVCCçåæµæéççãåå ä¸ç´¢å¼è¦çä¸ç¨å表ï¼ä¼åå¨å°±è®¤ä¸ºç´æ¥éåè¾ å©ç´¢å¼çæçé«äºä¸»é®ç´¢å¼ã
ä»ä¹æ¯ç´¢å¼è¦çï¼
ç´¢å¼è¦çï¼Index Coveringï¼æ¯æéè¿å¨ç´¢å¼ä¸å å«æææ¥è¯¢è¯å¥ä¸æéçåï¼å¯ä»¥é¿å 对表ä¸çæ°æ®è¿è¡é¢å¤ç访é®ï¼ä»èæé«æ¥è¯¢æçã(é¿å äºå表æä½)
ä¾å¦ï¼å¯¹äºä¸ä¸ªæ¥è¯¢è¯å¥ï¼
SELECT col1, col2, col3 FROM table WHERE col1 = x AND col2 = y
å¦æå¨table表ä¸å»ºç«äºä¸ä¸ªç´¢å¼ï¼å å«col1ãcol2åcol3ä¸åï¼é£ä¹MySQLå¯ä»¥éè¿ç´¢å¼å®ä½å°ç¬¦åæ¡ä»¶çæ°æ®ï¼å¹¶å¨ç´¢å¼ä¸æåcol1ãcol2åcol3åçå¼ï¼æ é对表ä¸çæ°æ®è¿è¡é¢å¤ç访é®ãè¿ç§æ¹å¼å°±å«åç´¢å¼è¦çã
ç´¢å¼è¦çè½å¤æ¾èæé«æ¥è¯¢æçï¼å æ¤å¨å»ºç«ç´¢å¼æ¶åºå°½éèèå å«æ¥è¯¢è¯å¥ä¸æéçææåã
ä»ä¹æ¯èç°ç´¢å¼ï¼
èç°ç´¢å¼æ¯ä¸ç§ç¹æ®çç´¢å¼ï¼å®å°æ°æ®åå¨å¨ç´¢å¼æ çå¶åèç¹ä¸ãè¿ç§ç´¢å¼æ¹å¼çä¼ç¹æ¯ï¼å¨æ¥è¯¢æ°æ®æ¶å¯ä»¥åå°ä¸æ¬¡æ¥è¯¢ï¼å 为æ¥è¯¢ç´¢å¼æ çåæ¶å°±è½è·åå°æ°æ®ãèç°ç´¢å¼ç缺ç¹æ¯ï¼å 为æ°æ®åå¨å¨ç´¢å¼æ ä¸ï¼æ以对æ°æ®è¿è¡ä¿®æ¹æå é¤æä½æ¶éè¦æ´æ°ç´¢å¼æ ï¼è¿ä¼å¢å ç³»ç»çå¼éã
èç°ç´¢å¼ä¸éèéç´¢å¼çç¹ç¹æ¯ä»ä¹ï¼
å¨InnoDBä¸èç°ç´¢å¼åéèç°ç´¢å¼å®é ä¸æ¯ç©ç空é´åå¨æ¹å¼çä¸ä¸ªä¸åã
èç°ç´¢å¼
- èç°ç´¢å¼å°æ°æ®åå¨å¨ç´¢å¼æ çå¶åèç¹ä¸ã
- èç°ç´¢å¼å¯ä»¥åå°ä¸æ¬¡æ¥è¯¢ï¼å 为æ¥è¯¢ç´¢å¼æ çåæ¶å°±è½è·åå°æ°æ®ã
- èç°ç´¢å¼ç缺ç¹æ¯ï¼å¯¹æ°æ®è¿è¡ä¿®æ¹æå é¤æä½æ¶éè¦æ´æ°ç´¢å¼æ ï¼ä¼å¢å ç³»ç»çå¼éã
- èç°ç´¢å¼é常ç¨äºæ°æ®åºç³»ç»ä¸ï¼ä¸»è¦ç¨äºæé«æ¥è¯¢æçã
éèç°ç´¢å¼ï¼å称äºçº§ç´¢å¼ / è¾ å©ç´¢å¼ï¼
- éèç°ç´¢å¼ä¸å°æ°æ®åå¨å¨ç´¢å¼æ çå¶åèç¹ä¸ï¼èæ¯åå¨å¨æ°æ®é¡µä¸ã
- éèç°ç´¢å¼å¨æ¥è¯¢æ°æ®æ¶éè¦ä¸¤æ¬¡æ¥è¯¢ï¼ä¸æ¬¡æ¥è¯¢ç´¢å¼æ ï¼è·åæ°æ®é¡µçå°åï¼åéè¿æ°æ®é¡µçå°åæ¥è¯¢æ°æ®ï¼é常æ åµä¸æ¥è¯´æ¯çï¼ä½å¦æç´¢å¼è¦ççè¯å®é ä¸æ¯ä¸ç¨å表çï¼ã
- éèç°ç´¢å¼çä¼ç¹æ¯ï¼å¯¹æ°æ®è¿è¡ä¿®æ¹æå é¤æä½æ¶ä¸éè¦æ´æ°ç´¢å¼æ ï¼åå°äºç³»ç»çå¼éã
- éèç°ç´¢å¼é常ç¨äºæ°æ®åºç³»ç»ä¸ï¼ä¸»è¦ç¨äºæé«æ°æ®æ´æ°åå é¤æä½çæçã
èç°ç´¢å¼ä¸éèç°ç´¢å¼b+æ å®ç°æä»ä¹åºå«ï¼
ç»åâèç°ç´¢å¼ä¸éèéç´¢å¼çç¹ç¹æ¯ä»ä¹ï¼âå ä¸ä¸å¾å°±æç½äº
ä¸ä¸ªè¡¨ä¸å¯ä»¥æå¤ä¸ªï¼éï¼èç°ç´¢å¼åï¼
å¯ä»¥ï¼è¿é¢å®¹ææ··æ·èç°åéèç°ï¼èç°åªè½æä¸ä¸ªï¼ä½æ¯éèç°å¯ä»¥æå¾å¤ï¼å 为èç°æ¯åæ°æ®åæ¾å¨ä¸èµ·çï¼ä½æ¯éèç°æ¯åç¬çãï¼åæ¶è¿é¢å¯ä»¥ç»åä¸é¢ä¸¤ä¸ªé®é¢åçï¼
éèç°ç´¢å¼ä¸ºä»ä¹ä¸åæ°æ®å°åå¼èåå¨ä¸»é®ï¼
æ们ç¥éå¨MyISAMå¼æä¸æ¯æ²¡æèç°ç´¢å¼ï¼é½æ¯åçè¾ å©ç´¢å¼ãä½æ¯åInnoDBä¸åçæ¯åå¨çï¼å®æ¯åå¨ç´¢å¼å¼åæ°æ®å°åï¼èæ们InnoDBä¸åå¨çæ¯ä¸»é®IDã
æ们è¦è®°ä½ç¥éä¸ä¸ªç¹ï¼æ°æ®æ¯ä¼ä¸æåå¨çï¼é£ä¹å®çä¸ä¸ªå°åä¹æ¯ä¼è·çä¸æåå¨ï¼å¦æç´æ¥åå¨å°åï¼ä¸æ¬¡æ¾å°çæ°æ®å¯è½å°±ä¸æ¯åæ¥çæ°æ®äºãå¦æè¦è§£å³è¿ä¸ªé®é¢çè¯ï¼ææ¬æ¯é常é«çãæ¯æ¬¡æ°æ®åå¨é½éè¦è¿è¡è°æ´ã
ä¸ä¸ªb+æ ä¸å¤§æ¦è½åæ¾å¤å°æ¡ç´¢å¼è®°å½ï¼
ä»ä¹æ¯Hashç´¢å¼ï¼
åå¸ç´¢å¼ï¼hash indexï¼åºäºåå¸è¡¨å®ç°ãåå¸ç´¢å¼éè¿Hashç®æ³å°æ°æ®åºçç´¢å¼åæ°æ®è½¬æ¢æå®é¿çåå¸ç ä½ä¸ºkeyï¼å°è¿æ¡æ°æ®çè¡çå°åä½ä¸ºvalueä¸å¹¶åå ¥Hash表ç对åºä½ç½®ã
å¨MySQLä¸ï¼åªæMemeoryå¼ææ¾å¼çæ¯æåå¸ç´¢å¼ï¼è¿ä¹æ¯Memoryå¼æ表çé»è®¤ç´¢å¼ç»æï¼Memeoryåæ¶ä¹æ¯æB-Treeç´¢å¼ã并ä¸ï¼Memoryå¼ææ¯æéå¯ä¸åå¸ç´¢å¼ï¼å¦æå¤ä¸ªåçåå¸å¼ç¸åï¼æè åçäºHash碰æï¼ï¼ç´¢å¼ä¼å¨å¯¹åºHashé®ä¸ä»¥é¾è¡¨å½¢å¼åå¨å¤ä¸ªè®°å½å°åã
åå¸ç´¢å¼è¿æå¦ä¸ç¹ç¹ï¼
- åå¸ç´¢å¼ä¸æ¯æé¨åç´¢å¼åçå¹é æ¥æ¾ï¼å 为åå¸ç´¢å¼å§ç»æ¯ä½¿ç¨ç´¢å¼åçå ¨é¨å 容æ¥è®¡ç®åå¸å¼çãä¾å¦ï¼å¨æ°æ®åï¼Aï¼Bï¼ä¸å»ºç«åå¸ç´¢å¼ï¼å¦ææ¥è¯¢åªææ°æ®åAï¼åæ æ³ä½¿ç¨è¯¥ç´¢å¼ã
- åå¸ç´¢å¼å ·æåå¸è¡¨çç¹æ§ï¼å æ¤åªæ精确å¹é ææåçæ¥è¯¢å¯¹äºåå¸ç´¢å¼æææï¼æ¯å¦=ã<>ãIN(ï¼å 为æ°æ®çåå¨æ¯æ åºç)ï¼ä¸æ æ³ä½¿ç¨ä»»ä½èå´æ¥è¯¢ã
- å 为æ°æ®çåå¨æ¯æ åºçï¼åå¸ç´¢å¼è¿æ æ³ç¨äºæåºã
- 对äºç²¾ç¡®æ¥è¯¢ï¼ååå¸ç´¢å¼æçå¾é«ï¼æ¶é´å¤æ度为O(1)ï¼é¤éæå¾å¤åå¸å²çªï¼ä¸åçç´¢å¼åæç¸åçåå¸å¼ï¼ï¼å¦æåçåå¸å²çªï¼ååå¨å¼æå¿ é¡»éåé¾è¡¨ä¸çæææ°æ®æéï¼éè¡æ¯è¾ï¼ç´å°æ¾å°ææ符åæ¡ä»¶çè¡ãåå¸å²çªè¶å¤ï¼ä»£ä»·å°±è¶å¤§ï¼
InnoDBå°åºæ¯ä¸æ¯æåå¸ç´¢å¼ï¼
对äºInnoDBçåå¸ç´¢å¼ï¼ç¡®åçåºè¯¥è¿ä¹è¯´ï¼
- InnoDBç¨æ·æ æ³æå¨å建åå¸ç´¢å¼ï¼è¿ä¸å±ä¸è¯´ï¼InnoDBç¡®å®ä¸æ¯æåå¸ç´¢å¼;
- InnoDBä¼èªè°ä¼(self-tuning)ï¼å¦æå¤å®å»ºç«èªéåºåå¸ç´¢å¼(Adaptive Hash Index, AHI)ï¼è½å¤æåæ¥è¯¢æçï¼InnoDBèªå·±ä¼å»ºç«ç¸å ³åå¸ç´¢å¼ï¼è¿ä¸å±ä¸è¯´ï¼InnoDBåæ¯æ¯æåå¸ç´¢å¼ç;
é£ä»ä¹æ¯èªéåºåå¸ç´¢å¼(Adaptive Hash Index, AHI)å¢?
1ãèªéåºå³æ们ä¸éè¦èªå·±å¤çï¼å½InnoDBå¼ææ ¹æ®æ¥è¯¢ç»è®¡åç°æä¸æ¥è¯¢æ»¡è¶³hashç´¢å¼çæ°æ®ç»æç¹ç¹ï¼å°±ä¼ç»å ¶å»ºç«ä¸ä¸ªhashç´¢å¼ï¼
2ãhashç´¢å¼åºå±çæ°æ®ç»ææ¯æ£å表ï¼Hash表ï¼ï¼å ¶æ°æ®ç¹ç¹å°±æ¯æ¯è¾éåå¨å åä¸ä½¿ç¨ï¼èªéåºHashç´¢å¼åå¨äºInnoDBæ¶æä¸çç¼åä¸ï¼ä¸åå¨äºç£çæ¶æä¸ï¼.
ä»ä¹æ¯ç´¢å¼ä¸æ¨ï¼
ç´¢å¼ä¸æ¨ï¼INDEX CONDITION PUSHDOWNï¼ç®ç§° ICPï¼æ¯å¨ MySQL 5.6 é对æ«æäºçº§ç´¢å¼çä¸é¡¹ä¼åæ¹è¿ãæ»çæ¥è¯´æ¯éè¿æç´¢å¼è¿æ»¤æ¡ä»¶ä¸æ¨å°åå¨å¼æï¼æ¥åå° MySQL åå¨å¼æ访é®åºè¡¨ç次æ°ä»¥å MySQL æå¡å±è®¿é®åå¨å¼æç次æ°ãICP éç¨äº MYISAM å INNODBï¼æ¬ç¯çå 容åªåºäº INNODBã
å¨è®²è¿ä¸ªææ¯ä¹åä½ å¾å¯¹mysqlæ¶ææä¸ä¸ªç®åç认è¯ï¼è§ä¸å¾
- MySQL æå¡å±ï¼ä¹å°±æ¯ SERVER å±ï¼ç¨æ¥è§£æ SQL çè¯æ³ãè¯ä¹ãçææ¥è¯¢è®¡åãæ¥ç®¡ä» MySQL åå¨å¼æå±ä¸æ¨çæ°æ®è¿è¡äºæ¬¡è¿æ»¤ççã
- MySQL åå¨å¼æå±ï¼æç § MySQL æå¡å±ä¸åç请æ±ï¼éè¿ç´¢å¼æè å ¨è¡¨æ«æçæ¹å¼ææ°æ®ä¸ä¼ å° MySQL æå¡å±ã
- MySQL ç´¢å¼æ«æï¼æ ¹æ®æå®ç´¢å¼è¿æ»¤æ¡ä»¶ï¼éåç´¢å¼æ¾å°ç´¢å¼é®å¯¹åºç主é®å¼åå表è¿æ»¤å©ä½è¿æ»¤æ¡ä»¶ã
- MySQL ç´¢å¼è¿æ»¤ï¼éè¿ç´¢å¼æ«æ并ä¸åºäºç´¢å¼è¿è¡äºæ¬¡æ¡ä»¶è¿æ»¤ååå表ã
- 使ç¨ç´¢å¼ä¸æ¨å®ç°
ç´¢å¼ä¸æ¨ç使ç¨æ¡ä»¶
- ICPç®æ æ¯åå°å ¨è¡è®°å½è¯»åï¼ä»èåå°IO æä½ï¼åªè½ç¨äºéèç°ç´¢å¼ãèç°ç´¢å¼æ¬èº«å å«ç表æ°æ®ï¼ä¹å°±ä¸åå¨ä¸æ¨ä¸è¯´ã
- åªè½ç¨äºrangeã refã eq_refãref_or_null访é®æ¹æ³ï¼
- where æ¡ä»¶ä¸æ¯ç¨ and èé or çæ¶åã
- ICPéç¨äºååºè¡¨ã
- ICPä¸æ¯æåºäºèæåä¸å»ºç«çç´¢å¼ï¼æ¯å¦è¯´å½æ°ç´¢å¼
- ICPä¸æ¯æå¼ç¨åæ¥è¯¢ä½ä¸ºæ¡ä»¶ã
- ICPä¸æ¯æåå¨å½æ°ä½ä¸ºæ¡ä»¶ï¼å 为åå¨å¼ææ æ³è°ç¨åå¨å½æ°ã
ç´¢å¼ä¸æ¨ç¸å ³è¯å¥
# æ¥çç´¢å¼ä¸æ¨æ¯å¦å¼å¯
select @@optimizer_switch
# å¼å¯ç´¢å¼ä¸æ¨
set optimizer_switch="index_condition_pushdown=on";
# å
³éç´¢å¼ä¸æ¨
set optimizer_switch="index_condition_pushdown=off";
ä»ä¹æ¯å¯ä¸ç´¢å¼ï¼
讲起æ¥é常ç®åï¼å ¶å®å "æ®éç´¢å¼"类似ï¼ä¸åçå°±æ¯ï¼ç´¢å¼åçå¼å¿ é¡»å¯ä¸ï¼ä½å 许æ空å¼ã å¯ä»¥æ¯ååå¯ä¸ç´¢å¼ï¼ä¹å¯ä»¥æ¯èåå¯ä¸ç´¢å¼ã
- æ大çæç¨å°±æ¯ç¡®ä¿åå ¥æ°æ®åºçæ°æ®æ¯å¯ä¸å¼ã
ä»ä¹æ¶ååºè¯¥ä½¿ç¨å¯ä¸ç´¢å¼å¢ï¼
æ们åé¢è®²äºå¯ä¸ç´¢å¼æ大ç好å¤å°±æ¯è½ä¿è¯å¯ä¸æ§ãç似没ä»ä¹å¤ªå¤§çä»·å¼ï¼å¯è½å°±ä¼æåå¦è¯´ï¼æä¸å¡å±åä¸ä¸ªéå¤æ£æ¥ä¸å°±å¥½äºãé®é¢å°±å¨è¿ä¸ªå°æ¹ï¼âä¸å¡æ¯æ æ³ç¡®ä¿å¯ä¸æ§çâï¼é¤éä½ è¯´ä½ ç代ç 没æBUGãå¾å¤æ¶åä¸å¡åºæ¯éè¦ä¿è¯å¯ä¸æ§ï¼å¦æä¸å¨æ°æ®åºå éå¶çè¯ï¼æ»æä¸å¤©ä¼åºç°èæ°æ®ã
é£åæåå¦å°±è¯´äºï¼æ¢ç¶ä½ ä¸æ³éå¤ä½ å¯ä»¥ä½¿ç¨ä¸»é®ç´¢å¼ãè¿ä¸ªåçä¹å¾æææã
- æ们确å®å¯ä»¥éè¿ä¸»é®ç´¢å¼æ¥ä¿è¯å¯ä¸ï¼ä½æ¯ï¼å¦æä½ çæ°æ®ä¸è½ä¿è¯æåºæå ¥ãæ¯å¦è¯´èº«ä»½è¯å段ï¼ä½ å¦æç¨èº«ä»½è¯å段ä½ä¸ºä¸»é®çè¯ï¼ä¼å¯¼è´æ¥è¯¢æçéä½ã
- å¯ä¸ç´¢å¼è¿æä¸ä¸ªå¥½å¤å°±æ¯å¯ä»¥ä¸ºç©ºï¼çå®çä¸å¡åºæ¯è¯å®æ¯å¯ä»¥ä¿è¯èº«ä»½è¯ä¸ºç©ºçï¼å¦æ没æç»å®èº«ä»½è¯å°±ä¸è®©æ³¨å好åä¹æç¹è¯´ä¸è¿å»ã
èç°ç´¢å¼çåçå°±ä¸å¨è¿éç»è®²äºï¼ä¼æä¸ä¸ªåç¬çç« èæ¥ä»ç»ã
å¯ä¸ç´¢å¼æ¯å¦ä¼å½±åæ§è½å¢ï¼
æ们éè¿åæ®éç´¢å¼æ¥åä¸ä¸ªå¯¹æ¯ï¼ææ¥è¯¢åæå ¥ä¸¤ä¸ªåºæ¯ã
é¦å 第ä¸ä¸ªæ°æ®æ¥è¯¢ï¼ä¸è¬æ åµä¸æ¥è¯´ç´¢å¼æ¯éè¿B+æ ä»æ ¹èç¹å¼å§å±åºéåå°å¶åç»ç¹ï¼æ°æ®é¡µå é¨éè¿äºåæç´¢ã
- æ®éç´¢å¼ æ¥å°æ»¡è¶³æ¡ä»¶ç第ä¸æ¡è®°å½ï¼ç»§ç»æ¥æ¾ä¸ä¸æ¡è®°å½ï¼ç´å°æ¾å°ä¸æ»¡è¶³æ¡ä»¶çè®°å½
- å¯ä¸ç´¢å¼ æ¥å°ç¬¬ä¸ä¸ªæ»¡è¶³æ¡ä»¶çè®°å½ï¼å°±åæ¢æç´¢ã
InnoDB å®æ¯ä»¥æ°æ®é¡µä¸ºåä½è¿è¡è¯»åçï¼æ们读ä¸æ¡è®°å½ï¼å¹¶ä¸æ¯ä»ç£çå è½½ä¸æ¡è®°å½ï¼èæ¯ä»¥é¡µä¸ºåä½æ´ä½è¯»å°å åéé¢æ¥çã
æ®éç´¢å¼æ¯å¯ä¸ç´¢å¼å°±å¤äºä¸æ¬¡æ¥æ¾åå¤æä¸ä¸æ¡è®°å½çæä½ï¼ä¹å°±æ¯ä¸æ¬¡æé寻æ¾æ°æ®åä¸æ¬¡è®¡ç®ãå½ç¶è¿æä¸ç§ç¹æ®æ åµï¼è¯»åå°çè¿æ¡æ°æ®æ£å¥½æ¯æ°æ®é¡µçæåä¸æ¡ï¼ä½æ¯è¿ç§æ¦çä¹æ¯é常ä½ï¼å ä¹å¯ä»¥å¿½ç¥ä¸è®¡ã
æ´ä½çä¸æ¥çä¸å»æ§è½å·®è·å¹¶ä¸å¤§å¯¹å§ã
æ¥ç第äºä¸ªæ´æ°çæ§è½ï¼æ们æç §ä¸é¢å¾ä¸çä¾åå¨2å6ä¹é´æå ¥ä¸ä¸ª3ã
å¨å åä¸
- æ®éç´¢å¼ æ¾å°2å6ä¹é´çä½ç½® âæå ¥å¼â ç»æ
- å¯ä¸ç´¢å¼ æ¾å°2å6ä¹é´çä½ç½® â**å½å¤ææ没æå²çª**â æå ¥å¼â ç»æ
ä¸å¨å åä¸
- æ®éç´¢å¼ å°æ´æ°è®°å½å¨change buffer â ç»æ
- å¯ä¸ç´¢å¼ å°æ°æ®é¡µè¯»å ¥å åâå½å¤æå°æ²¡æå²çªâæå ¥å¼âç»æ
æ°æ®è¯»åå°å åæ¶åäºéæºIO访é®ï¼è¿æ¯å¨æ°æ®åºéé¢ææ¬æé«çæä½ä¹ä¸ï¼èchange buffer å°±å¯ä»¥åå°è¿ç§éæºç£ç访é®ï¼æ以æ§è½æ示æ¯è¾ææ¾ãæ以å¨è¿ä¸åæ¥è¯´ï¼å¦æ两è å¨ä¸å¡åºæ¯ä¸é½è½æ»¡è¶³æ¶å¯ä»¥ä¼å èè使ç¨æ®éç´¢å¼ã
ä»ä¹æ¯èåç´¢å¼ï¼ç»åç´¢å¼ï¼å¤åç´¢å¼ï¼
æ们å¨ç´¢å¼å顾çæ¶åå大家对索å¼åäºä¸ä¸ªå类对å§ï¼æç §å段个æ°æ¥åçè¯ï¼å°±å为äºååç´¢å¼åç»åç´¢å¼å¯¹å§ãé£ä¹ä»ä»¬ä¹é´çç¹ç¹æ¯ä»ä¹å¢ï¼æ们æ¥ç
- ååç´¢å¼ ä¸ä¸ªç´¢å¼åªå å«äºä¸ä¸ªåï¼ä¸ä¸ªè¡¨éé¢å¯ä»¥æå¤ä¸ªååç´¢å¼ï¼ä½æ¯è¿ä¸å«ç»åç´¢å¼ã
- ç»åç´¢å¼ï¼èåç´¢å¼ & å¤åç´¢å¼ï¼ä¸ä¸ªç´¢å¼å å«å¤ä¸ªåã
çä¸å»æè§è¿ç»åç´¢å¼å¹¶æ²¡æ太大ä½ç¨æ¯å§ï¼æä¸ä¸ªåå·²ç»æä¸ä¸ªç´¢å¼äºï¼æè¿è¦è¿ç»åç´¢å¼å¹²åï¼
çç¸å¾å¾ä¸é£ä¹ç®åï¼é¦å æ们å¾æ¿è®¤æ们çä¸å¡ååä¸åï¼æ们çæ¥è¯¢è¯å¥æ¡ä»¶è¯å®æ¯é常å¤çã
- é«æç å¦æ说åªæååç´¢å¼ï¼é£å°±ä¼æ¶åå¤æ¬¡äºçº§ç´¢å¼æ æ¥æ¾ï¼åå ä¸å表ï¼æ§è½ç¸å¯¹äºèåç´¢å¼æ¥è¯´æ¯æ¯è¾ä½çã
- åå°å¼é æ们è¦è®°å¾å建索å¼æ¯åå¨ç©ºé´å¼éçï¼å¯¹äºå¤§æ°æ®éç表ï¼ä½¿ç¨èåç´¢å¼ä¼éä½ç©ºé´å¼éã
- ç´¢å¼è¦ç å¦æç»åç´¢å¼ç´¢å¼å¼å·²ç»æ»¡è¶³äºæ们çæ¥è¯¢æ¡ä»¶ï¼é£ä¹å°±ä¸ä¼è¿è¡å表ï¼ç´æ¥è¿åã
ä½æ¯æ们æç §æ们çæ¥è¯¢æ¡ä»¶å»å建ä¸ä¸ªèåç´¢å¼çè¯ï¼å°±é¿å äºä¸é¢çé®é¢ãé£ä¹èåç´¢å¼æ¯æä¹å·¥ä½çå¢ï¼
è¿éæ¶åå°äºä¸ä¸ªéç¹ï¼å«åæå·¦åç¼ï¼ç®åç解就æ¯åªä¼ä»æ左边å¼å§ç»åï¼ç»åç´¢å¼ç第ä¸ä¸ªåæ®µå¿ é¡»åºç°å¨æ¥è¯¢ç»å¥ä¸ï¼è¿ä¸è½è·³è·ï¼åªæè¿æ ·æè½è®©ç´¢å¼çæï¼æ¯å¦è¯´ææ¥è¯¢æ¡ä»¶éé¢æç»åç´¢å¼éé¢ç第äºä¸ªå段ï¼é£ä¹ä¹æ¯ä¸ä¼èµ°ç»åç´¢å¼çã举个ä¾å
// å设ç»usernameï¼ageå建äºç»åç´¢å¼
// è¿ä¸¤ç§æ
åµæ¯ä¼èµ°ç´¢å¼ç
select username,age from user where username = 'å¼ ä¸' and age = 18;
select * from user where username = 'å¼ ä¸';
// è¿ç§æ¯ä¸ä¼èµ°ç´¢å¼ç
select * from user where age = 18;
select * from user where city = 'å京' and age = 18;
å¤åç´¢å¼å建æ¶å段顺åºä¸ä¸æ ·ä½¿ç¨ææä¸æ ·åï¼
// ç¹æ®æ
åµï¼è¿ç§ä¹æ¯ä¼èµ°ç´¢å¼çï¼è½ç¶æçageå¨åé¢ï¼usernameå¨åé¢ã
// ååä¸æ¯ææå·¦åç¼å¹é
åï¼ä¸ºä»ä¹æ¾å°ç¬¬äºä½ä¹å¯ä»¥å¢ï¼
// è½è¯´é¡ºåºä¸ä¸è´ï¼ä½æ¯å¨SQLæ§è¡è¿ç¨ä¸ï¼æ ¹æ®æ¥è¯¢æ¡ä»¶å½ä¸ç´¢å¼ï¼
// æ 论æusernameå¨ä¸å¨åé¢ï¼é½ä¼æç
§usernameå»è¿è¡ç´¢å¼æ¥æ¾ã
select * from user where age = 18 and username = 'å¼ ä¸';
使ç¨Order Byæ¶è½å¦éè¿ç´¢å¼æåºï¼
æ们ç¥éå¨å¾å¤åºæ¯ä¸ä¼å¯¼è´ç´¢å¼å¤±æï¼æ¯å¦è¯´æ²¡æéµå¾ªB+æ çæå·¦å¹é ååï¼ä½æ¯ä¹æä¸äºæ åµæ¯éµå¾ªäºæå·¦å¹é ååä½æ¯è¿æ¯æ²¡æèµ°ç´¢å¼ï¼è¿éæ们使ç¨order byè¿è¡æåºçæ¶åå°±æä¸èµ°ç´¢å¼çæ åµï¼é£ä¹å¸¦å¤§å®¶æ¥åæä¸ä¸
drop table if exists `user`;
drop table if exists `user_example`;
create table `user`(
`id` int primary key comment '主é®ID',
`card_id` int comment '身份è¯',
`nickname` varchar(10) comment 'æµç§°',
`age` int not null comment 'å¹´é¾',
key `card_id` (`card_id`)
) engine=InnoDB default charset=utf8mb4;
// è¿éæ们ææ对card_id建好äºååç´¢å¼ï¼é£ä¸ºä»ä¹ä¸èµ°ç´¢å¼å¢ï¼
select * from `user` order by card_id
- å¦æç´¢å¼è¦çæ¯å¯ä»¥èµ°ç´¢å¼ç
- å¦æ带ä¸ç´¢å¼æ¡ä»¶æ¯å¯ä»¥èµ°ç´¢å¼ç
éè¿ç´¢å¼æåºå é¨æµç¨æ¯ä»ä¹å¢ï¼
explain select nickname,card_id,age from user order by card_id;
æ们å¨äºè§£mysqlåºå±æ¯æä¹æåºçä¹åï¼æ们å æ¥äºè§£ä¸ä¸ä¸ä¸ªæ¦å¿µ sort buffer .
é¦å mysqlä¼ä¸ºæ¯ä¸ä¸ªçº¿ç¨é½åé ä¸ä¸ªåºå®å¤§å°çsort buffer ç¨äºæåºãå®æ¯ä¸ä¸ªå ·æé»è¾æ¦å¿µçå ååºåï¼æ们å¯ä»¥éè¿sort_buffer_sizeåæ°æ¥æ§å¶ï¼é»è®¤å¼æ¯256kb ã
// è¾å
¥æ¥çæï¼å°å¯ä»¥è®¾ç½®ä¸º 32Kï¼æ大å¯ä»¥è®¾ç½®ä¸º 4Gã
show variables like 'sort_buffer_size';
ç±äºsort buffer 大å°æ¯ä¸ä¸ªåºå®çï¼ä½æ¯æä»¬å¾ æåºçæ°æ®éå®ä¸æ¯ï¼æä»¥æ ¹æ®å®ä»¬ä¹é´çä¸ä¸ªå·®å¼å¢ï¼å°±å为äºå é¨æåºåå¤é¨æåº
- å½å¾ æåºçæ°æ®éå°äºçäºsort buffer æ¶ï¼é£æ们çsort bufferå°±è½å¤å®¹çº³ï¼MySQLå°±å¯ä»¥ç´æ¥å¨å åéé¢æåºå°±è¡äºï¼å é¨æåºä½¿ç¨çæåºç®æ³æ¯å¿«æ
- å½å¾ æåºçæ°æ®é大äºsort buffer æ¶ï¼é£æ们çsort buffer å°±ä¸å¤ç¨äºå¯¹å§ãè¿ä¸ªæ¶åMySQLå°±å¾è¦åå©å¤é¨æ件æ¥è¿è¡æåºäºãå°å¾ æåºæ°æ®ææå¤ä¸ªå°æ件ï¼å¯¹å个å°æ件è¿è¡æåºï¼æååæ±æ»æä¸ä¸ªæåºçæ件ï¼å¤é¨æåºä½¿ç¨çç®æ³æ¶å½å¹¶æåº
æ们æ¥èèrow_idæåº
å大家说ä¸ä¸ªè¿ä¸ªåæ°max_length_for_sort_data ï¼å¨æ们MySQLä¸ä¸é¨æ§å¶ç¨æ·æåºçè¡æ°æ®é¿åº¦åæ°ãé»è®¤æ¯4096ï¼ä¹å°±æ¯è¯´å¦æè¶ è¿äºè¿ä¸ªé¿åº¦MySQLå°±ä¼èªå¨å级ærow_idç®æ³ã
// é»è®¤max_length_for_sort_dataç大å°ä¸º4096åè
show variables like 'max_length_for_sort_data';
row_idæåºçææ³å°±æ¯æä¸éè¦çæ°æ®ä¸æ¾å°sort_bufferä¸ï¼è®©sort_bufferä¸åªåæ¾éè¦æåºçå段ã
举个ä¾åï¼
explain select nickname,card_id,age from user order by card_id;
æ们åé¢è¯´å°äºsort bufferï¼å¨sort bufferéé¢è¿è¡æåºçæ°æ®æ¯æ们selectçå ¨é¨å段ï¼æ以å½æ们æ¥è¯¢çå段è¶å¤ï¼é£ä¹sort bufferè½å®¹çº³çæ°æ®éä¹å°±è¶å°ãèéè¿row_idæåºå°±åªä¼åæ¾row_id å段åæåºç¸å ³çå段ãå ¶ä½çå段çæåºå®æä¹åéè¿ä¸»é®IDè¿è¡å表æ¿ã
group by åç»å order by å¨ç´¢å¼ä½¿ç¨ä¸æä»ä¹ä¸ååï¼
没ä»ä¹å¤ªå¤§çå·®å¼group byå®é æ¯å è¿è¡æåºï¼åè¿è¡åç»ãæ以éµå¾ªorder byçç´¢å¼æºå¶ã
æåï¼ç¦å©ï¼
å°ç¼æååå¤äºä¸ä»½JAVAé¢è¯ææ°å «è¡æ+é å¥è§é¢è®²è§£ï¼æå ´è¶£çå¯ä»¥åå°ç§ä¿¡ãç¬è®°ãè·åï¼