æçåå®¢ï¼ https://www.luozhiyun.com/archives/273
InnoDB页
å°æ°æ®åå为è¥å¹²ä¸ªé¡µï¼ä»¥é¡µä½ä¸ºç£çåå åä¹é´äº¤äºçåºæ¬åä½ï¼InnoDBä¸é¡µç大å°ä¸è¬ä¸º 16 KBãä¹å°±æ¯å¨ä¸è¬æ åµä¸ï¼ä¸æ¬¡æå°ä»ç£çä¸è¯»å16KBçå 容å°å åä¸ï¼ä¸æ¬¡æå°æå åä¸ç16KBå 容å·æ°å°ç£çä¸ã
InnoDBè¡æ ¼å¼
æ们平æ¶æ¯ä»¥è®°å½ä¸ºåä½æ¥å表ä¸æå ¥æ°æ®çï¼è¿äºè®°å½å¨ç£çä¸çåæ¾æ¹å¼ä¹è¢«ç§°ä¸ºè¡æ ¼å¼æè è®°å½æ ¼å¼ã
è®°å½çé¢å¤ä¿¡æ¯
è®°å½çé¢å¤ä¿¡æ¯ï¼åå«æ¯åé¿å段é¿åº¦å表ãNULLå¼å表åè®°å½å¤´ä¿¡æ¯
1.åå«æ¯åé¿å段é¿åº¦å表
MySQLä¸æ¯å¦VARCHAR(M)ãVARBINARY(M)ãåç§TEXTç±»åï¼åç§BLOBç±»åè¿äºæ°æ®ç±»åçå称为åé¿å段ï¼åé¿å段ä¸åå¨å¤å°åèçæ°æ®æ¯ä¸åºå®çã
æ以è¿äºåé¿å段å ç¨çåå¨ç©ºé´å为两é¨åï¼çæ£çæ°æ®å 容ãå ç¨çåèæ°
å¨è¡æ ¼å¼ä¸ï¼æææåé¿å段ççå®æ°æ®å ç¨çåèé¿åº¦é½åæ¾å¨è®°å½çå¼å¤´é¨ä½ï¼ä»èå½¢æä¸ä¸ªåé¿å段é¿åº¦å表ï¼ååé¿å段æ°æ®å ç¨çåèæ°æç §åç顺åºéåºåæ¾ ã
åé¿å段é¿åº¦å表ä¸åªåå¨å¼ä¸º éNULL çåå 容å ç¨çé¿åº¦ï¼å¼ä¸º NULL çåçé¿åº¦æ¯ä¸å¨åç ã
并ä¸æ¯ææè®°å½é½æè¿ä¸ª åé¿å段é¿åº¦å表 é¨åï¼æ¯æ¹è¯´è¡¨ä¸ææçåé½ä¸æ¯åé¿çæ°æ®ç±»åçè¯ï¼è¿ä¸é¨åå°±ä¸éè¦æã
2.NULLå¼å表
表ä¸çæäºåå¯è½åå¨NULLå¼ï¼å¦ææè¿äºNULLå¼é½æ¾å°è®°å½ççå®æ°æ®ä¸åå¨ä¼å¾å å°æ¹ï¼æ以Compactè¡æ ¼å¼æè¿äºå¼ä¸ºNULLçåç»ä¸ç®¡çèµ·æ¥ï¼åå¨å°NULLå¼å表ä¸ï¼å®çå¤çè¿ç¨æ¯è¿æ ·çï¼
- é¦å ç»è®¡è¡¨ä¸å 许åå¨NULLçåæåªäºã
- å¦æ表ä¸æ²¡æå 许åå¨ NULL çåï¼å NULLå¼å表 ä¹ä¸åå¨äº
- è®°å½å¤´ä¿¡æ¯
å
å建ä¸ä¸ªè¡¨ï¼
mysql> CREATE TABLE page_demo(
-> c1 INT,
-> c2 INT,
-> c3 VARCHAR(10000),
-> PRIMARY KEY (c1)
-> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.03 sec)
主é®ççæçç¥
InnoDB表对主é®ççæçç¥ï¼ä¼å 使ç¨ç¨æ·èªå®ä¹ä¸»é®ä½ä¸ºä¸»é®ï¼å¦æç¨æ·æ²¡æå®ä¹ä¸»é®ï¼åéåä¸ä¸ªUniqueé®ä½ä¸ºä¸»é®ï¼å¦æ表ä¸è¿Uniqueé®é½æ²¡æå®ä¹çè¯ï¼åInnoDBä¼ä¸ºè¡¨é»è®¤æ·»å ä¸ä¸ªå为row_idçéèåä½ä¸ºä¸»é®ã
è¿ä¸ªæ°å建çpage_demo表æ3个åï¼å ¶ä¸c1åc2åæ¯ç¨æ¥åå¨æ´æ°çï¼c3åæ¯ç¨æ¥åå¨å符串çãéè¦æ³¨æçæ¯ï¼æ们æ c1 åæå®ä¸ºä¸»é®ï¼æ以å¨å ·ä½çè¡æ ¼å¼ä¸InnoDBå°±æ²¡å¿ è¦ä¸ºæ们å»å建é£ä¸ªæè°ç row_id éèåäºã
ç®ååçè¡æ ¼å¼ç¤ºæå¾å°±æ¯è¿æ ·ï¼
InnoDBæ°æ®é¡µç»æ
æ°æ®é¡µä»£è¡¨çè¿å16KB大å°çåå¨ç©ºé´å¯ä»¥è¢«åå为å¤ä¸ªé¨åï¼ä¸åé¨åæä¸åçåè½ï¼å个é¨åå¦å¾æ示ï¼
æ¯å½æ们æå ¥ä¸æ¡è®°å½ï¼é½ä¼ä»Free Spaceé¨åï¼ä¹å°±æ¯å°æªä½¿ç¨çåå¨ç©ºé´ä¸ç³è¯·ä¸ä¸ªè®°å½å¤§å°ç空é´ååå°User Recordsé¨åï¼å½Free Spaceé¨åç空é´å ¨é¨è¢«User Recordsé¨åæ¿ä»£æä¹åï¼ä¹å°±æå³çè¿ä¸ªé¡µä½¿ç¨å®äºï¼å¦æè¿ææ°çè®°å½æå ¥çè¯ï¼å°±éè¦å»ç³è¯·æ°ç页äºï¼è¿ä¸ªè¿ç¨çå¾ç¤ºå¦ä¸ï¼
è¿éæ们继ç»ä¸é¢çpage_demoä¾åï¼
å
å建ä¸ä¸ªè¡¨ï¼
mysql> CREATE TABLE page_demo(
-> c1 INT,
-> c2 INT,
-> c3 VARCHAR(10000),
-> PRIMARY KEY (c1)
-> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.03 sec)
åpage_demo表ä¸æå
¥å æ¡è®°å½ï¼
mysql> INSERT INTO page_demo VALUES(1, 100, 'aaaa'),
(2, 200, 'bbbb'),
(3, 300, 'cccc'),
(4, 400, 'dddd');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
é£ä¹ï¼è¿äºè®°å½ç示æå¾å°±æ¯ï¼
-
delete_mask
è¿ä¸ªå±æ§æ è®°çå½åè®°å½æ¯å¦è¢«å é¤ã
è¿äºè¢«å é¤çè®°å½ä¹æ以ä¸ç«å³ä»ç£çä¸ç§»é¤ï¼æ¯å 为移é¤å®ä»¬ä¹åæå ¶ä»çè®°å½å¨ç£çä¸éæ°æåéè¦æ§è½æ¶èï¼æ以åªæ¯æä¸ä¸ªå é¤æ è®°èå·²ã
ææ被å é¤æçè®°å½é½ä¼ç»æä¸ä¸ªæè°çåå¾é¾è¡¨ï¼å¨è¿ä¸ªé¾è¡¨ä¸çè®°å½å ç¨ç空é´ç§°ä¹ä¸ºæè°çå¯éç¨ç©ºé´ï¼ä¹åå¦æææ°è®°å½æå ¥å°è¡¨ä¸çè¯ï¼å¯è½æè¿äºè¢«å é¤çè®°å½å ç¨çåå¨ç©ºé´è¦çæã
-
min_rec_mask
B+æ çæ¯å±éå¶åèç¹ä¸çæå°è®°å½é½ä¼æ·»å 该æ è®°ï¼min_rec_maskå¼é½æ¯0ï¼æå³çå®ä»¬é½ä¸æ¯B+æ çéå¶åèç¹ä¸çæå°è®°å½ã
-
n_owned
å¨é¡µç®å½åç»æ¶ä½¿ç¨ï¼æ¯ä¸ªç»çæåä¸æ¡è®°å½ï¼ä¹å°±æ¯ç»å æ大çé£æ¡è®°å½ï¼ç头信æ¯ä¸çn_ownedå±æ§è¡¨ç¤ºè¯¥è®°å½æ¥æå¤å°æ¡è®°å½ï¼ä¹å°±æ¯è¯¥ç»å å ±æå æ¡è®°å½ã
-
heap_no
è¿ä¸ªå±æ§è¡¨ç¤ºå½åè®°å½å¨æ¬é¡µä¸çä½ç½®ï¼ä»å¾ä¸å¯ä»¥çåºæ¥ï¼æ们æå ¥ç4æ¡è®°å½å¨æ¬é¡µä¸çä½ç½®åå«æ¯ï¼2ã3ã4ã5ã
heap_noå¼ä¸º0å1çè®°å½ï¼ç§°ä¸ºä¼ªè®°å½æè èæè®°å½ãè¿ä¸¤ä¸ªä¼ªè®°å½ä¸ä¸ªä»£è¡¨æå°è®°å½ï¼ä¸ä¸ªä»£è¡¨æ大记å½ã
-
record_type
è¿ä¸ªå±æ§è¡¨ç¤ºå½åè®°å½çç±»åï¼ä¸å ±æ4ç§ç±»åçè®°å½ï¼0表示æ®éè®°å½ï¼1表示B+æ éå¶èç¹è®°å½ï¼2表示æå°è®°å½ï¼3表示æ大记å½ã
-
next_record
å®è¡¨ç¤ºä»å½åè®°å½ççå®æ°æ®å°ä¸ä¸æ¡è®°å½ççå®æ°æ®çå°åå移éãæ¯æ¹è¯´ç¬¬ä¸æ¡è®°å½çnext_recordå¼ä¸º32ï¼æå³çä»ç¬¬ä¸æ¡è®°å½ççå®æ°æ®çå°åå¤ååæ¾32个åè便æ¯ä¸ä¸æ¡è®°å½ççå®æ°æ®ã
ä¸ä¸æ¡è®°å½æå¾å¹¶ä¸æ¯æç §æ们æå ¥é¡ºåºçä¸ä¸æ¡è®°å½ï¼èæ¯æç §ä¸»é®å¼ç±å°å°å¤§ç顺åºçä¸ä¸æ¡è®°å½ãèä¸è§å® Infimumè®°å½ï¼ä¹å°±æ¯æå°è®°å½ï¼ çä¸ä¸æ¡è®°å½å°±æ¯æ¬é¡µä¸ä¸»é®å¼æå°çç¨æ·è®°å½ï¼èæ¬é¡µä¸ä¸»é®å¼æ大çç¨æ·è®°å½çä¸ä¸æ¡è®°å½å°±æ¯ Supremumè®°å½ï¼ä¹å°±æ¯æ大记å½ï¼
ä»å¾ä¸å¯ä»¥çåºæ¥ï¼æ们çè®°å½æç §ä¸»é®ä»å°å°å¤§ç顺åºå½¢æäºä¸ä¸ªåé¾è¡¨ã
å¦æä»ä¸å é¤æä¸æ¡è®°å½ï¼è¿ä¸ªé¾è¡¨ä¹æ¯ä¼è·çååçï¼æ¯å¦æ们æ第2æ¡è®°å½å æï¼
mysql> DELETE FROM page_demo WHERE c1 = 2;
Query OK, 1 row affected (0.02 sec)
* 第2æ¡è®°å½å¹¶æ²¡æä»åå¨ç©ºé´ä¸ç§»é¤ï¼èæ¯æ该æ¡è®°å½çdelete_maskå¼è®¾ç½®ä¸º1ã
* 第2æ¡è®°å½çnext_recordå¼å为äº0ï¼æå³ç该记å½æ²¡æä¸ä¸æ¡è®°å½äºã
* 第1æ¡è®°å½çnext_recordæåäºç¬¬3æ¡è®°å½ã
* è¿æä¸ç¹ä½ å¯è½å¿½ç¥äºï¼å°±æ¯æ大记å½çn_ownedå¼ä»5åæäº4
å 为主é®å¼ä¸º2çè®°å½è¢«æ们å æäºï¼ä½æ¯åå¨ç©ºé´å´æ²¡æåæ¶ï¼å¦ææ们å次æè¿æ¡è®°å½æå ¥å°è¡¨ä¸ï¼
mysql> INSERT INTO page_demo VALUES(2, 200, 'bbbb');
Query OK, 1 row affected (0.00 sec)
Page Directoryï¼é¡µç®å½ï¼
ç°å¨æ们äºè§£äºè®°å½å¨é¡µä¸æç §ä¸»é®å¼ç±å°å°å¤§é¡ºåºä¸²èæä¸ä¸ªåé¾è¡¨ï¼é£å¦ææ们æ³æ ¹æ®ä¸»é®å¼æ¥æ¾é¡µä¸çææ¡è®°å½è¯¥ååå¢ï¼
设计InnoDBç大å们为æ们çè®°å½ä¹å¶ä½äºä¸ä¸ªç±»ä¼¼çç®å½ï¼ä»ä»¬çå¶ä½è¿ç¨æ¯è¿æ ·çï¼
- å°æææ£å¸¸çè®°å½ï¼å æ¬æ大åæå°è®°å½ï¼ä¸å æ¬æ 记为已å é¤çè®°å½ï¼åå为å 个ç»ã
- æ¯ä¸ªç»çæåä¸æ¡è®°å½ï¼ä¹å°±æ¯ç»å æ大çé£æ¡è®°å½ï¼ç头信æ¯ä¸çn_ownedå±æ§è¡¨ç¤ºè¯¥è®°å½æ¥æå¤å°æ¡è®°å½ï¼ä¹å°±æ¯è¯¥ç»å å ±æå æ¡è®°å½
- å°æ¯ä¸ªç»çæåä¸æ¡è®°å½çå°åå移éåç¬æååºæ¥ï¼ç¨ä½æ¥æ¾ã
注æï¼è¿ä¸ªé¡µç®å½æ¯ä¸ºä¸»é®æå¡çã
对äºæå°è®°å½æå¨çåç»åªè½æ 1 æ¡è®°å½ï¼æ大记å½æå¨çåç»æ¥æçè®°å½æ¡æ°åªè½å¨ 1-8 æ¡ä¹é´ï¼å©ä¸çåç»ä¸è®°å½çæ¡æ°èå´åªè½å¨æ¯ 4-8 æ¡ä¹é´ã
åç»æ¯æç §ä¸è¾¹çæ¥éª¤è¿è¡ï¼
- åå§æ åµä¸ä¸ä¸ªæ°æ®é¡µéåªææå°è®°å½åæ大记å½ä¸¤æ¡è®°å½ï¼å®ä»¬åå±äºä¸¤ä¸ªåç»ã
- ä¹åæ¯æå ¥ä¸æ¡è®°å½ï¼é½ä¼ä»é¡µç®å½ä¸æ¾å°ä¸»é®å¼æ¯æ¬è®°å½ç主é®å¼å¤§å¹¶ä¸å·®å¼æå°ç槽ï¼ç¶åæ该槽对åºçè®°å½çn_ownedå¼å 1ï¼è¡¨ç¤ºæ¬ç»å åæ·»å äºä¸æ¡è®°å½ï¼ç´å°è¯¥ç»ä¸çè®°å½æ°çäº8个ã
- å¨ä¸ä¸ªç»ä¸çè®°å½æ°çäº8个ååæå ¥ä¸æ¡è®°å½æ¶ï¼ä¼å°ç»ä¸çè®°å½æåæ两个ç»ï¼ä¸ä¸ªç»ä¸4æ¡è®°å½ï¼å¦ä¸ä¸ª5æ¡è®°å½ãè¿ä¸ªè¿ç¨ä¼å¨é¡µç®å½ä¸æ°å¢ä¸ä¸ªæ§½æ¥è®°å½è¿ä¸ªæ°å¢åç»ä¸æ大çé£æ¡è®°å½çå移éã
æ们åæ·»å 12æ¡è®°å½ççææï¼
mysql> INSERT INTO page_demo VALUES(5, 500, 'eeee'), (6, 600, 'ffff'), (7, 700, 'gggg'), (8, 800, 'hhhh'), (9, 900, 'iiii'), (10, 1000, 'jjjj'), (11, 1100, 'kkkk'), (12, 1200, 'llll'), (13, 1300, 'mmmm'), (14, 1400, 'nnnn'), (15, 1500, 'oooo'), (16, 1600, 'pppp');
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
å 为æ16æ¡è®°å½çå ¨é¨ä¿¡æ¯é½ç»å¨ä¸å¼ å¾é太å å°æ¹ï¼è®©äººç¼è±ç¼ä¹±çï¼æ以åªä¿çäºç¨æ·è®°å½å¤´ä¿¡æ¯ä¸çn_ownedånext_recordå±æ§ã
å 为å个槽代表çè®°å½ç主é®å¼é½æ¯ä»å°å°å¤§æåºçï¼æ以æ们å¯ä»¥ä½¿ç¨æè°çäºåæ³æ¥è¿è¡å¿«éæ¥æ¾ã
æ以å¨ä¸ä¸ªæ°æ®é¡µä¸æ¥æ¾æå®ä¸»é®å¼çè®°å½çè¿ç¨å为两æ¥ï¼
- éè¿äºåæ³ç¡®å®è¯¥è®°å½æå¨ç槽ï¼å¹¶æ¾å°è¯¥æ§½æå¨åç»ä¸ä¸»é®å¼æå°çé£æ¡è®°å½ã
- éè¿è®°å½çnext_recordå±æ§éå该槽æå¨çç»ä¸çå个记å½ã
注æï¼è¥æ¥å°æ°æ®å¨æ§½2çåç»ä¸ï¼ç±äºæ§½2æ¯æåæåä¸ä¸ªè®°å½ï¼æ以éè¦åä¸æ¾ä¸ä¸ªæ§½ä½ï¼å®ä½å°ä¸ä¸ä¸ªæ§½ä½æåä¸è¡ï¼ç¶åååä¸æ¾ã
File Headerï¼æ件头é¨ï¼
File Headeré对åç§ç±»åç页é½éç¨ï¼ä¹å°±æ¯è¯´ä¸åç±»åç页é½ä¼ä»¥File Headerä½ä¸ºç¬¬ä¸ä¸ªç»æé¨åï¼å®æè¿°äºä¸äºé对åç§é¡µé½éç¨çä¸äºä¿¡æ¯ï¼æ¯æ¹è¯´è¿ä¸ªé¡µçç¼å·æ¯å¤å°ï¼å®çä¸ä¸ä¸ªé¡µãä¸ä¸ä¸ªé¡µæ¯è°çã
FIL_PAGE_OFFSET
æ¯ä¸ä¸ªé¡µé½æä¸ä¸ªåç¬ç页å·ï¼å°±è·ä½ ç身份è¯å·ç ä¸æ ·ï¼InnoDBéè¿é¡µå·æ¥å¯ä»¥å¯ä¸å®ä½ä¸ä¸ªé¡µã
FIL_PAGE_PREVåFIL_PAGE_NEXT
FIL_PAGE_PREVåFIL_PAGE_NEXTå°±åå«ä»£è¡¨æ¬é¡µçä¸ä¸ä¸ªåä¸ä¸ä¸ªé¡µç页å·ãè¿æ ·éè¿å»ºç«ä¸ä¸ªååé¾è¡¨æ许许å¤å¤ç页就é½ä¸²èèµ·æ¥äº
B+æ ç´¢å¼
InnoDBæ°æ®é¡µç主è¦ç»æé¨åãå个æ°æ®é¡µå¯ä»¥ç»æä¸ä¸ªååé¾è¡¨ï¼èæ¯ä¸ªæ°æ®é¡µä¸çè®°å½ä¼æç §ä¸»é®å¼ä»å°å°å¤§ç顺åºç»æä¸ä¸ªååé¾è¡¨ï¼æ¯ä¸ªæ°æ®é¡µé½ä¼ä¸ºåå¨å¨å®éè¾¹å¿çè®°å½çæä¸ä¸ªé¡µç®å½ãåéè¿ä¸»é®æ¥æ¾ææ¡è®°å½çæ¶åå¯ä»¥å¨é¡µç®å½ä¸ä½¿ç¨äºåæ³å¿«éå®ä½å°å¯¹åºç槽ã
å¨ä¸ä¸ªé¡µä¸çæ¥æ¾
-
以主é®ä¸ºæç´¢æ¡ä»¶
è¿ä¸ªæ¥æ¾è¿ç¨æ们已ç»å¾çæäºï¼å¯ä»¥å¨é¡µç®å½ä¸ä½¿ç¨äºåæ³å¿«éå®ä½å°å¯¹åºç槽ï¼ç¶ååéå该槽对åºåç»ä¸çè®°å½å³å¯å¿«éæ¾å°æå®çè®°å½ã
-
ä»¥å ¶ä»åä½ä¸ºæç´¢æ¡ä»¶
对é主é®åçæ¥æ¾çè¿ç¨å¯å°±ä¸è¿ä¹å¹¸è¿äºï¼å 为å¨æ°æ®é¡µä¸å¹¶æ²¡æ对é主é®å建ç«æè°ç页ç®å½ï¼æ以æ们æ æ³éè¿äºåæ³å¿«éå®ä½ç¸åºç槽ãè¿ç§æ åµä¸åªè½ä»æå°è®°å½å¼å§ä¾æ¬¡éååé¾è¡¨ä¸çæ¯æ¡è®°å½ï¼ç¶å对æ¯æ¯æ¡è®°å½æ¯ä¸æ¯ç¬¦åæç´¢æ¡ä»¶ã
å¨å¾å¤é¡µä¸æ¥æ¾
- å®ä½å°è®°å½æå¨ç页ã
- ä»æå¨ç页å ä¸æ¥æ¾ç¸åºçè®°å½ã
å¨æ²¡æç´¢å¼çæ åµä¸ï¼ä¸è®ºæ¯æ ¹æ®ä¸»é®åæè å ¶ä»åçå¼è¿è¡æ¥æ¾ï¼ç±äºæ们并ä¸è½å¿«éçå®ä½å°è®°å½æå¨ç页ï¼æ以åªè½ä»ç¬¬ä¸ä¸ªé¡µæ²¿çååé¾è¡¨ä¸ç´å¾ä¸æ¾ï¼å¨æ¯ä¸ä¸ªé¡µä¸æ ¹æ®æ们ååå å¨è¿çæ¥æ¾æ¹å¼å»æ¥æ¾æå®çè®°å½ã
ç´¢å¼
为äºæ äºç顺å©åå±ï¼æ们å 建ä¸ä¸ªè¡¨ï¼
mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.03 sec)
为äºæ们ç解ä¸çæ¹ä¾¿ï¼æ们ç®åäºä¸ä¸index_demo表çè¡æ ¼å¼ç¤ºæå¾ï¼
è®°å½å¤´ä¿¡æ¯çä¸é¡¹å±æ§ï¼è¡¨ç¤ºä¸ä¸æ¡å°åç¸å¯¹äºæ¬æ¡è®°å½çå°åå移é
æä¸äºè®°å½æ¾å°é¡µéè¾¹ç示æå¾å°±æ¯ï¼
ä¸ä¸ªç®åçç´¢å¼æ¹æ¡ï¼
æä»¬ä¸ºæ ¹æ®ä¸»é®å¼å¿«éå®ä½ä¸æ¡è®°å½å¨é¡µä¸çä½ç½®è设ç«ç页ç®å½ï¼ç®å½ä¸è®°å½çæ°æ®é¡µå¿ é¡»ä¸ä¸ä¸ªæ°æ®é¡µä¸ç¨æ·è®°å½ç主é®å¼å¿ 须大äºä¸ä¸ä¸ªé¡µä¸ç¨æ·è®°å½ç主é®å¼ã
æ们è¿ééè¦åä¸ä¸ªå设ï¼å设æ们çæ¯ä¸ªæ°æ®é¡µæå¤è½åæ¾3æ¡è®°å½ï¼å®é ä¸ä¸ä¸ªæ°æ®é¡µé常大ï¼å¯ä»¥åæ¾ä¸å¥½å¤è®°å½ï¼ãæäºè¿ä¸ªå设ä¹åæ们åindex_demo表æå ¥3æ¡è®°å½ï¼
mysql> INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
ä»å¾ä¸å¯ä»¥çåºæ¥ï¼index_demo表ä¸ç3æ¡è®°å½é½è¢«æå ¥å°äºç¼å·ä¸º10çæ°æ®é¡µä¸äºãæ¤æ¶æ们åæ¥æå ¥ä¸æ¡è®°å½ï¼
mysql> INSERT INTO index_demo VALUES(4, 4, 'a');
Query OK, 1 row affected (0.00 sec)
å 为页10æå¤åªè½æ¾3æ¡è®°å½ï¼æ以æ们ä¸å¾ä¸ååé ä¸ä¸ªæ°é¡µï¼
页10ä¸ç¨æ·è®°å½æ大ç主é®å¼æ¯5ï¼è页28ä¸æä¸æ¡è®°å½ç主é®å¼æ¯4ï¼å 为5 > 4ï¼æ以è¿å°±ä¸ç¬¦åä¸ä¸ä¸ªæ°æ®é¡µä¸ç¨æ·è®°å½ç主é®å¼å¿ 须大äºä¸ä¸ä¸ªé¡µä¸ç¨æ·è®°å½ç主é®å¼çè¦æ±ï¼æ以å¨æå ¥ä¸»é®å¼ä¸º4çè®°å½çæ¶åéè¦ä¼´éçä¸æ¬¡è®°å½ç§»å¨ï¼ä¹å°±æ¯æ主é®å¼ä¸º5çè®°å½ç§»å¨å°é¡µ28ä¸ï¼ç¶ååæ主é®å¼ä¸º4çè®°å½æå ¥å°é¡µ10ä¸ï¼è¿ä¸ªè¿ç¨ç示æå¾å¦ä¸ï¼
è¿ä¸ªè¿ç¨å«å页åè£ã
ç±äºæ°æ®é¡µçç¼å·å¯è½å¹¶ä¸æ¯è¿ç»çï¼æ以å¨åindex_demo表ä¸æå ¥è®¸å¤æ¡è®°å½åï¼å¯è½æ¯è¿æ ·çææï¼
å 为è¿äº16KBç页å¨ç©çåå¨ä¸å¯è½å¹¶ä¸æ¨çï¼æ以å¦ææ³ä»è¿ä¹å¤é¡µä¸æ ¹æ®ä¸»é®å¼å¿«éå®ä½æäºè®°å½æå¨ç页ï¼æ们éè¦ç»å®ä»¬å个ç®å½ï¼æ¯ä¸ªé¡µå¯¹åºä¸ä¸ªç®å½é¡¹ï¼æ¯ä¸ªç®å½é¡¹å æ¬ä¸è¾¹ä¸¤ä¸ªé¨åï¼
* 页çç¨æ·è®°å½ä¸æå°ç主é®å¼ï¼æ们ç¨keyæ¥è¡¨ç¤ºã
* 页å·ï¼æ们ç¨page_no表示ã
æ以æ们为ä¸è¾¹å 个页å好çç®å½å°±åè¿æ ·åï¼
æ¯æ¹è¯´æ们æ³æ¾ä¸»é®å¼ä¸º20çè®°å½ï¼å ·ä½æ¥æ¾è¿ç¨å两æ¥ï¼
- å ä»ç®å½é¡¹ä¸æ ¹æ®äºåæ³å¿«éç¡®å®åºä¸»é®å¼ä¸º20çè®°å½å¨ç®å½é¡¹3ä¸ï¼å 为 12 < 20 < 209ï¼ï¼å®å¯¹åºç页æ¯é¡µ9ã
- åæ ¹æ®å边说çå¨é¡µä¸æ¥æ¾è®°å½çæ¹å¼å»é¡µ9ä¸å®ä½å ·ä½çè®°å½ã
è¿ä¸ªç®å½æä¸ä¸ªå«åï¼ç§°ä¸ºç´¢å¼ã
InnoDBä¸çç´¢å¼æ¹æ¡
å¨InnoDBä¸å¤ç¨äºä¹ååå¨ç¨æ·è®°å½çæ°æ®é¡µæ¥åå¨ç®å½é¡¹ï¼ä¸ºäºåç¨æ·è®°å½åä¸ä¸åºåï¼æ们æè¿äºç¨æ¥è¡¨ç¤ºç®å½é¡¹çè®°å½ç§°ä¸ºç®å½é¡¹è®°å½ã
ç¨record_typeæ¥åºåæ®éçç¨æ·è®°å½è¿æ¯ç®å½é¡¹è®°å½ã
ä¸ä¸ªé¡µåªæ16KB大å°ï¼è½åæ¾çç®å½é¡¹è®°å½ä¹æ¯æéçï¼é£å¦æ表ä¸çæ°æ®å¤ªå¤ï¼ä»¥è³äºä¸ä¸ªæ°æ®é¡µä¸è¶³ä»¥åæ¾ææçç®å½é¡¹è®°å½ï¼ä¼åå¤æ´ä¸ä¸ªåå¨ç®å½é¡¹è®°å½ç页ã
å设ä¸ä¸ªåå¨ç®å½é¡¹è®°å½ç页æå¤åªè½åæ¾4æ¡ç®å½é¡¹è®°å½ï¼è¯·æ³¨ææ¯å设å¦ï¼çå®æ åµä¸å¯ä»¥åæ¾å¥½å¤æ¡çï¼ï¼æ以å¦ææ¤æ¶æ们ååä¸å¾ä¸æå ¥ä¸æ¡ä¸»é®å¼ä¸º320çç¨æ·è®°å½çè¯ï¼
å¨è¿ä¸ªæ¥è¯¢æ¥éª¤ç第1æ¥ä¸æ们éè¦å®ä½åå¨ç®å½é¡¹è®°å½ç页ï¼ä½æ¯è¿äºé¡µå¨åå¨ç©ºé´ä¸ä¹å¯è½ä¸æ¨çï¼å¦ææ们表ä¸çæ°æ®é常å¤åä¼äº§çå¾å¤åå¨ç®å½é¡¹è®°å½ç页ï¼é£æ们æä¹æ ¹æ®ä¸»é®å¼å¿«éå®ä½ä¸ä¸ªåå¨ç®å½é¡¹è®°å½ç页å¢ï¼å ¶å®ä¹ç®åï¼ä¸ºè¿äºåå¨ç®å½é¡¹è®°å½ç页åçæä¸ä¸ªæ´é«çº§çç®å½ï¼å°±åæ¯ä¸ä¸ªå¤çº§ç®å½ä¸æ ·ï¼å¤§ç®å½éåµå¥å°ç®å½ï¼å°ç®å½éææ¯å®é çæ°æ®ï¼æ以ç°å¨å个页ç示æå¾å°±æ¯è¿æ ·åï¼
éç表ä¸è®°å½çå¢å ï¼è¿ä¸ªç®å½çå±çº§ä¼ç»§ç»å¢å ï¼å¦æç®åä¸ä¸ï¼é£ä¹æ们å¯ä»¥ç¨ä¸è¾¹è¿ä¸ªå¾æ¥æè¿°å®ï¼
ä»å¾ä¸å¯ä»¥çåºæ¥ï¼æ们çå®é ç¨æ·è®°å½å ¶å®é½åæ¾å¨B+æ çæåºå±çèç¹ä¸ï¼è¿äºèç¹ä¹è¢«ç§°ä¸ºå¶åèç¹æå¶èç¹ï¼å ¶ä½ç¨æ¥åæ¾ç®å½é¡¹çèç¹ç§°ä¸ºéå¶åèç¹æè å èç¹ï¼å ¶ä¸B+æ æä¸è¾¹çé£ä¸ªèç¹ä¹ç§°ä¸ºæ ¹èç¹ã
èç°ç´¢å¼
æ们ä¸è¾¹ä»ç»çB+æ æ¬èº«å°±æ¯ä¸ä¸ªç®å½ï¼æè 说æ¬èº«å°±æ¯ä¸ä¸ªç´¢å¼ãå®æ两个ç¹ç¹ï¼
- 使ç¨è®°å½ä¸»é®å¼ç大å°è¿è¡è®°å½å页çæåº
- B+æ çå¶åèç¹åå¨çæ¯å®æ´çç¨æ·è®°å½ã
æ们æå ·æè¿ä¸¤ç§ç¹æ§çB+æ 称为èç°ç´¢å¼ï¼ææå®æ´çç¨æ·è®°å½é½åæ¾å¨è¿ä¸ªèç°ç´¢å¼çå¶åèç¹å¤ãè¿ç§èç°ç´¢å¼å¹¶ä¸éè¦æ们å¨MySQLè¯å¥ä¸æ¾å¼ç使ç¨INDEXè¯å¥å»å建ï¼åè¾¹ä¼ä»ç»ç´¢å¼ç¸å ³çè¯å¥ï¼ï¼InnoDBåå¨å¼æä¼èªå¨ç为æ们å建èç°ç´¢å¼ãå¦å¤æ趣çä¸ç¹æ¯ï¼å¨InnoDBåå¨å¼æä¸ï¼èç°ç´¢å¼å°±æ¯æ°æ®çåå¨æ¹å¼ï¼ææçç¨æ·è®°å½é½åå¨å¨äºå¶åèç¹ï¼ï¼ä¹å°±æ¯æè°çç´¢å¼å³æ°æ®ï¼æ°æ®å³ç´¢å¼ã
äºçº§ç´¢å¼
è¿ä¸ªB+æ ä¸ä¸è¾¹ä»ç»çèç°ç´¢å¼æå å¤ä¸åï¼
- 使ç¨è®°å½c2åç大å°è¿è¡è®°å½å页çæåºï¼è¿å
æ¬ä¸ä¸ªæ¹é¢çå«ä¹ï¼
- 页å çè®°å½æ¯æç §c2åç大å°é¡ºåºææä¸ä¸ªååé¾è¡¨ã
- å个åæ¾ç¨æ·è®°å½ç页ä¹æ¯æ ¹æ®é¡µä¸è®°å½çc2å大å°é¡ºåºææä¸ä¸ªååé¾è¡¨ã
- åæ¾ç®å½é¡¹è®°å½ç页å为ä¸åçå±æ¬¡ï¼å¨åä¸å±æ¬¡ä¸ç页ä¹æ¯æ ¹æ®é¡µä¸ç®å½é¡¹è®°å½çc2å大å°é¡ºåºææä¸ä¸ªååé¾è¡¨ã
- B+æ çå¶åèç¹åå¨ç并ä¸æ¯å®æ´çç¨æ·è®°å½ï¼èåªæ¯c2å+主é®è¿ä¸¤ä¸ªåçå¼ã
- ç®å½é¡¹è®°å½ä¸ä¸åæ¯ä¸»é®+页å·çæé ï¼èåæäºc2å+页å·çæé ã
以æ¥æ¾c2åçå¼ä¸º4çè®°å½ä¸ºä¾ï¼
- ç¡®å®ç®å½é¡¹è®°å½é¡µï¼ä¹å°±æ¯é¡µ44
-
éè¿ç®å½é¡¹è®°å½é¡µç¡®å®ç¨æ·è®°å½çå®æå¨ç页ã
å¨é¡µ42ä¸å¯ä»¥å¿«éå®ä½å°å®é åå¨ç¨æ·è®°å½ç页ï¼ä½æ¯ç±äºc2å并没æå¯ä¸æ§çº¦æï¼æ以c2åå¼ä¸º4çè®°å½å¯è½åå¸å¨å¤ä¸ªæ°æ®é¡µä¸ï¼åå 为2 < 4 ⤠4ï¼æ以确å®å®é åå¨ç¨æ·è®°å½ç页å¨é¡µ34å页35ä¸ã
- å¨çå®åå¨ç¨æ·è®°å½ç页ä¸å®ä½å°å ·ä½çè®°å½ã
- ä½æ¯è¿ä¸ªB+æ çå¶åèç¹ä¸çè®°å½åªåå¨äºc2åc1ï¼ä¹å°±æ¯ä¸»é®ï¼ä¸¤ä¸ªåï¼æ以æä»¬å¿ é¡»åæ ¹æ®ä¸»é®å¼å»èç°ç´¢å¼ä¸åæ¥æ¾ä¸éå®æ´çç¨æ·è®°å½ãè¿ä¸ªè¿ç¨è¢«ç§°ä¸ºå表ã
å¦ææå®æ´çç¨æ·è®°å½æ¾å°å¶åèç¹æ¯å¯ä»¥ä¸ç¨å表ï¼ä½æ¯å¤ªå å°æ¹äºåï½ç¸å½äºæ¯å»ºç«ä¸æ£µB+æ é½éè¦æææçç¨æ·è®°å½åé½æ·è´ä¸éï¼è¿å°±æç¹å¤ªæµªè´¹åå¨ç©ºé´äºã
èåç´¢å¼
æ们ä¹å¯ä»¥åæ¶ä»¥å¤ä¸ªåç大å°ä½ä¸ºæåºè§åï¼ä¹å°±æ¯åæ¶ä¸ºå¤ä¸ªå建ç«ç´¢å¼ï¼æ¯æ¹è¯´æ们æ³è®©B+æ æç §c2åc3åç大å°è¿è¡æåºï¼
- å æå个记å½å页æç §c2åè¿è¡æåºã
- å¨è®°å½çc2åç¸åçæ åµä¸ï¼éç¨c3åè¿è¡æåº
å¦å¾æ示ï¼æ们éè¦æ³¨æä¸ä¸å ç¹ï¼
- æ¯æ¡ç®å½é¡¹è®°å½é½ç±c2ãc3ã页å·è¿ä¸ä¸ªé¨åç»æï¼åæ¡è®°å½å æç §c2åçå¼è¿è¡æåºï¼å¦æè®°å½çc2åç¸åï¼åæç §c3åçå¼è¿è¡æåºã
- B+æ å¶åèç¹å¤çç¨æ·è®°å½ç±c2ãc3å主é®c1åç»æã
ç´¢å¼ç代价
-
空é´ä¸ç代价
æ¯å»ºç«ä¸ä¸ªç´¢å¼é½è¦ä¸ºå®å»ºç«ä¸æ£µB+æ ï¼æ¯ä¸æ£µB+æ çæ¯ä¸ä¸ªèç¹é½æ¯ä¸ä¸ªæ°æ®é¡µï¼ä¸ä¸ªé¡µé»è®¤ä¼å ç¨16KBçåå¨ç©ºé´
-
æ¶é´ä¸ç代价
æ¯æ¬¡å¯¹è¡¨ä¸çæ°æ®è¿è¡å¢ãå ãæ¹æä½æ¶ï¼é½éè¦å»ä¿®æ¹å个B+æ ç´¢å¼ã
B+æ æ¯å±èç¹é½æ¯æç §ç´¢å¼åçå¼ä»å°å°å¤§ç顺åºæåºèç»æäºååé¾è¡¨ãä¸è®ºæ¯å¶åèç¹ä¸çè®°å½ï¼è¿æ¯å èç¹ä¸çè®°å½ï¼ä¹å°±æ¯ä¸è®ºæ¯ç¨æ·è®°å½è¿æ¯ç®å½é¡¹è®°å½ï¼é½æ¯æç §ç´¢å¼åçå¼ä»å°å°å¤§ç顺åºèå½¢æäºä¸ä¸ªååé¾è¡¨ãèå¢ãå ãæ¹æä½å¯è½ä¼å¯¹èç¹åè®°å½çæåºé æç ´åï¼æ以åå¨å¼æéè¦é¢å¤çæ¶é´è¿è¡ä¸äºè®°å½ç§»ä½ï¼é¡µé¢åè£ã页é¢åæ¶å¥çæä½æ¥ç»´æ¤å¥½èç¹åè®°å½çæåºã
B+æ ç´¢å¼éç¨çæ¡ä»¶
é£ä¹å次建议åè¿å¤´æåè¾¹çå 容çå®äºåæ¥ï¼è¦ä¸ç¶è¯»æç« å¯¹ä½ æ¥è¯´æ¯ä¸ç§æ磨ãé¦å ï¼B+æ ç´¢å¼å¹¶ä¸æ¯ä¸è½çï¼å¹¶ä¸æ¯ææçæ¥è¯¢è¯å¥é½è½ç¨å°æ们建ç«çç´¢å¼ãä¸è¾¹ä»ç»å 个æ们å¯è½ä½¿ç¨B+æ ç´¢å¼æ¥è¿è¡æ¥è¯¢çæ åµã
CREATE TABLE person_info(
id INT NOT NULL auto_increment,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);
person_info表ä¼ä¸ºèç°ç´¢å¼åidx_name_birthday_phone_numberç´¢å¼å»ºç«2棵B+æ ã
å¨è®°å½ç»æä¸åªä¿çnameãbirthdayãphone_numberãidè¿å个åççå®æ°æ®å¼ï¼æ以示æå¾å°±é¿è¿æ ·ï¼
å èç¹ä¸åå¨çæ¯ç®å½é¡¹è®°å½ï¼å¶åèç¹ä¸åå¨çæ¯ç¨æ·è®°å½ï¼ç±äºä¸æ¯èç°ç´¢å¼ï¼æ以ç¨æ·è®°å½æ¯ä¸å®æ´çï¼ç¼ºå°countryåçå¼ï¼ã
- å æç §nameåçå¼è¿è¡æåºã
- å¦ænameåçå¼ç¸åï¼åæç §birthdayåçå¼è¿è¡æåºã
- å¦æbirthdayåçå¼ä¹ç¸åï¼åæç §phone_numberçå¼è¿è¡æåºã
å ¨å¼å¹é
å¦ææ们çæç´¢æ¡ä»¶ä¸çååç´¢å¼åä¸è´çè¯ï¼è¿ç§æ åµå°±ç§°ä¸ºå ¨å¼å¹é ï¼æ¯æ¹è¯´ä¸è¾¹è¿ä¸ªæ¥æ¾è¯å¥ï¼
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';
- å 为B+æ çæ°æ®é¡µåè®°å½å æ¯æç §nameåçå¼è¿è¡æåºçï¼æ以å å¯ä»¥å¾å¿«å®ä½nameåçå¼æ¯Ashburnçè®°å½ä½ç½®ã
- å¨nameåç¸åçè®°å½éåæ¯æç §birthdayåçå¼è¿è¡æåºçï¼æ以å¨nameåçå¼æ¯Ashburnçè®°å½éåå¯ä»¥å¿«éå®ä½birthdayåçå¼æ¯'1990-09-27'çè®°å½ã
- å¦æå¾ä¸å¹¸ï¼nameåbirthdayåçå¼é½æ¯ç¸åçï¼é£è®°å½æ¯æç §phone_numberåçå¼æåºçï¼æ以èåç´¢å¼ä¸çä¸ä¸ªåé½å¯è½è¢«ç¨å°ã
è°æ¢nameãbirthdayãphone_numberè¿å 个æç´¢åç顺åºå¯¹æ¥è¯¢çæ§è¡è¿ç¨æ¯æ²¡æå½±åçã
å¹é 左边çå
SELECT * FROM person_info WHERE name = 'Ashburn';
æè å å«å¤ä¸ªå·¦è¾¹çåä¹è¡ï¼
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';
åªæ左边çåæè½å¹é ï¼ä¸è¾¹çè¯å¥å°±ç¨ä¸å°è¿ä¸ªB+æ ç´¢å¼ï¼
SELECT * FROM person_info WHERE birthday = '1990-09-27';
å 为B+æ çæ°æ®é¡µåè®°å½å æ¯æç §nameåçå¼æåºçï¼å¨nameåçå¼ç¸åçæ åµä¸æ使ç¨birthdayåè¿è¡æåºï¼ä¹å°±æ¯è¯´nameåçå¼ä¸åçè®°å½ä¸birthdayçå¼å¯è½æ¯æ åºçã
å¦ææ们æ³ä½¿ç¨èåç´¢å¼ä¸å°½å¯è½å¤çåï¼æç´¢æ¡ä»¶ä¸çå个åå¿ é¡»æ¯èåç´¢å¼ä¸ä»æ左边è¿ç»çåã
å¹é ååç¼
person_info表ä¸å»ºç«çèåç´¢å¼idx_name_birthday_phone_numberä¼å ç¨nameåçå¼è¿è¡æåºã
ä¹å°±æ¯è¯´è¿äºå符串çån个å符ï¼ä¹å°±æ¯åç¼é½æ¯æ好åºçï¼æ以对äºå符串类åçç´¢å¼åæ¥è¯´ï¼æ们åªå¹é å®çåç¼ä¹æ¯å¯ä»¥å¿«éå®ä½è®°å½çï¼æ¯æ¹è¯´æ们æ³æ¥è¯¢åå以'As'å¼å¤´çè®°å½ï¼é£å°±å¯ä»¥è¿ä¹åæ¥è¯¢è¯å¥ï¼
SELECT * FROM person_info WHERE name LIKE 'As%';
åçï¼è¿æ ·ä¹æ¯æ æ³å¹é çï¼
SELECT * FROM person_info WHERE name LIKE '%As%';
å¹é èå´å¼
ææè®°å½é½æ¯æç §ç´¢å¼åçå¼ä»å°å°å¤§ç顺åºæ好åºçï¼æ以è¿æ大çæ¹ä¾¿æ们æ¥æ¾ç´¢å¼åçå¼å¨æ个èå´å çè®°å½ã
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
ç±äºææè®°å½é½æ¯ç±é¾è¡¨è¿èµ·æ¥çï¼è®°å½ä¹é´ç¨åé¾è¡¨ï¼æ°æ®é¡µä¹é´ç¨åé¾è¡¨ï¼ï¼æ以ä»ä»¬ä¹é´çè®°å½é½å¯ä»¥å¾å®¹æçååºæ¥å½ï½
æ¾å°è¿äºè®°å½ç主é®å¼ï¼åå°èç°ç´¢å¼ä¸å表æ¥æ¾å®æ´çè®°å½ã
å¦æ对å¤ä¸ªååæ¶è¿è¡èå´æ¥æ¾çè¯ï¼åªæ对索å¼æ左边çé£ä¸ªåè¿è¡èå´æ¥æ¾çæ¶åæè½ç¨å°B+æ ç´¢å¼ï¼æ¯æ¹è¯´è¿æ ·ï¼
SELECT * FROM person_info WHERE name > âAsaâ AND name < âBarlowâ
AND birthday > '1980-01-01';
ä¸è¾¹è¿ä¸ªæ¥è¯¢å¯ä»¥åæ两个é¨åï¼
- éè¿æ¡ä»¶name > 'Asa' AND name < 'Barlow'æ¥å¯¹nameè¿è¡èå´ã
- 对è¿äºnameå¼ä¸åçè®°å½ç»§ç»éè¿birthday > '1980-01-01'æ¡ä»¶ç»§ç»è¿æ»¤ã
对äºèåç´¢å¼idx_name_birthday_phone_numberæ¥è¯´ï¼åªè½ç¨å°nameåçé¨åï¼èç¨ä¸å°birthdayåçé¨åï¼å 为åªænameå¼ç¸åçæ åµä¸æè½ç¨birthdayåçå¼è¿è¡æåºï¼èè¿ä¸ªæ¥è¯¢ä¸éè¿nameè¿è¡èå´æ¥æ¾çè®°å½ä¸å¯è½å¹¶ä¸æ¯æç §birthdayåè¿è¡æåºçï¼æ以å¨æç´¢æ¡ä»¶ä¸ç»§ç»ä»¥birthdayåè¿è¡æ¥æ¾æ¶æ¯ç¨ä¸å°è¿ä¸ªB+æ ç´¢å¼çã
精确å¹é æä¸å并èå´å¹é å¦å¤ä¸å
è½ç¶å¯¹å¤ä¸ªåé½è¿è¡èå´æ¥æ¾æ¶åªè½ç¨å°æ左边é£ä¸ªç´¢å¼åï¼ä½æ¯å¦æ左边çåæ¯ç²¾ç¡®æ¥æ¾ï¼åå³è¾¹çåå¯ä»¥è¿è¡èå´æ¥æ¾ï¼æ¯æ¹è¯´è¿æ ·ï¼
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';
ç±äºnameåæ¯ç²¾ç¡®æ¥æ¾ï¼æ以éè¿name = 'Ashburn'æ¡ä»¶æ¥æ¾åå¾å°çç»æçnameå¼é½æ¯ç¸åçï¼å®ä»¬ä¼åæç §birthdayçå¼è¿è¡æåºãæ以æ¤æ¶å¯¹birthdayåè¿è¡èå´æ¥æ¾æ¯å¯ä»¥ç¨å°B+æ ç´¢å¼çã
phone_number > '15100000000'ï¼éè¿birthdayçèå´æ¥æ¾çè®°å½çbirthdayçå¼å¯è½ä¸åï¼æ以è¿ä¸ªæ¡ä»¶æ æ³åå©ç¨B+æ ç´¢å¼äºï¼åªè½éåä¸ä¸æ¥æ¥è¯¢å¾å°çè®°å½ã
ç¨äºæåº
æçæ¶åå¯è½æ¥è¯¢çç»æé太大以è³äºä¸è½å¨å åä¸è¿è¡æåºçè¯ï¼è¿å¯è½ææ¶åå©ç£çç空é´æ¥åæ¾ä¸é´ç»æï¼æåºæä½å®æååææ好åºçç»æéè¿åå°å®¢æ·ç«¯ãå¨MySQLä¸ï¼æè¿ç§å¨å åä¸æè ç£çä¸è¿è¡æåºçæ¹å¼ç»ç§°ä¸ºæ件æåºï¼è±æåï¼filesortï¼ã
ä½æ¯å¦æORDER BYåå¥é使ç¨å°äºæ们çç´¢å¼åï¼å°±æå¯è½çå»å¨å åææ件ä¸æåºçæ¥éª¤ï¼æ¯å¦ä¸è¾¹è¿ä¸ªç®åçæ¥è¯¢è¯å¥ï¼
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
å 为è¿ä¸ªB+æ ç´¢å¼æ¬èº«å°±æ¯æç §ä¸è¿°è§åæ好åºçï¼æ以ç´æ¥ä»ç´¢å¼ä¸æåæ°æ®ï¼ç¶åè¿è¡å表æä½ååºè¯¥ç´¢å¼ä¸ä¸å å«çå就好äºã
使ç¨èåç´¢å¼è¿è¡æåºæ³¨æäºé¡¹
ORDER BYçåå¥åè¾¹çåç顺åºä¹å¿ é¡»æç §ç´¢å¼åç顺åºç»åºï¼å¦æç»åºORDER BY phone_number, birthday, nameç顺åºï¼é£ä¹æ¯ç¨ä¸äºB+æ ç´¢å¼ã
ä¸å¯ä»¥ä½¿ç¨ç´¢å¼è¿è¡æåºçå ç§æ åµ
1.ASCãDESCæ··ç¨
对äºä½¿ç¨èåç´¢å¼è¿è¡æåºçåºæ¯ï¼æ们è¦æ±å个æåºåçæåºé¡ºåºæ¯ä¸è´çï¼ä¹å°±æ¯è¦ä¹å个åé½æ¯ASCè§åæåºï¼è¦ä¹é½æ¯DESCè§åæåºã
idx_name_birthday_phone_numberèåç´¢å¼ä¸è®°å½çç»æï¼
- å æç §è®°å½çnameåçå¼è¿è¡ååºæåã
- å¦æè®°å½çnameåçå¼ç¸åï¼åæç §birthdayåçå¼è¿è¡ååºæåã
- å¦æè®°å½çbirthdayåçå¼ç¸åï¼åæç §phone_numberåçå¼è¿è¡ååºæåã
2.WHEREåå¥ä¸åºç°éæåºä½¿ç¨å°çç´¢å¼å
å¦æWHEREåå¥ä¸åºç°äºéæåºä½¿ç¨å°çç´¢å¼åï¼é£ä¹æåºä¾ç¶æ¯ä½¿ç¨ä¸å°ç´¢å¼çï¼æ¯æ¹è¯´è¿æ ·ï¼
SELECT * FROM person_info WHERE country = âChinaâ ORDER BY name LIMIT 10;
è¿ä¸ªæ¥è¯¢åªè½å æ符åæç´¢æ¡ä»¶country = 'China'çè®°å½æååºæ¥ååè¿è¡æåºï¼æ¯ä½¿ç¨ä¸å°ç´¢å¼ã
3.æåºåå å«éåä¸ä¸ªç´¢å¼çå
ææ¶åç¨æ¥æåºçå¤ä¸ªåä¸æ¯ä¸ä¸ªç´¢å¼éçï¼è¿ç§æ åµä¹ä¸è½ä½¿ç¨ç´¢å¼è¿è¡æåºï¼æ¯æ¹è¯´ï¼
SELECT * FROM person_info ORDER BY name, country LIMIT 10;
4.æåºå使ç¨äºå¤æç表达å¼
è¦æ³ä½¿ç¨ç´¢å¼è¿è¡æåºæä½ï¼å¿ é¡»ä¿è¯ç´¢å¼åæ¯ä»¥åç¬åçå½¢å¼åºç°ï¼èä¸æ¯ä¿®é¥°è¿çå½¢å¼ï¼æ¯æ¹è¯´è¿æ ·ï¼
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;
å表ç代价
è¿æ¯ç¨idx_name_birthday_phone_numberç´¢å¼ä¸ºä¾ï¼çä¸è¾¹è¿ä¸ªæ¥è¯¢ï¼
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
å¨ä½¿ç¨idx_name_birthday_phone_numberç´¢å¼è¿è¡æ¥è¯¢æ¶å¤§è´å¯ä»¥å为è¿ä¸¤ä¸ªæ¥éª¤ï¼
- ä»ç´¢å¼idx_name_birthday_phone_number对åºçB+æ ä¸ååºnameå¼å¨Asaï½Barlowä¹é´çç¨æ·è®°å½ã
- ç±äºç´¢å¼idx_name_birthday_phone_number对åºçB+æ ç¨æ·è®°å½ä¸åªå å«nameãbirthdayãphone_numberãidè¿4个å段ï¼èæ¥è¯¢å表æ¯*ï¼æå³çè¦æ¥è¯¢è¡¨ä¸ææå段ï¼ä¹å°±æ¯è¿è¦å æ¬countryå段ãè¿æ¶éè¦æä»ä¸ä¸æ¥ä¸è·åå°çæ¯ä¸æ¡è®°å½çidå段é½å°èç°ç´¢å¼å¯¹åºçB+æ ä¸æ¾å°å®æ´çç¨æ·è®°å½ï¼ä¹å°±æ¯æ们é常æ说çå表ï¼ç¶åæå®æ´çç¨æ·è®°å½è¿åç»æ¥è¯¢ç¨æ·ã
读åç´¢å¼idx_name_birthday_phone_numberæ°æ®ä¸ï¼å¨Asaï½Barlowä¹é´çè®°å½å¨ç£çä¸çåå¨æ¯ç¸è¿çï¼éä¸åå¸å¨ä¸ä¸ªæå 个æ°æ®é¡µä¸ï¼æ们å¯ä»¥å¾å¿«çæè¿äºè¿ççè®°å½ä»ç£çä¸è¯»åºæ¥ï¼è¿ç§è¯»åæ¹å¼æ们ä¹å¯ä»¥ç§°ä¸ºé¡ºåºI/Oã
èè·åå°çè®°å½çidå段çå¼å¯è½å¹¶ä¸ç¸è¿ï¼èå¨èç°ç´¢å¼ä¸è®°å½æ¯æ ¹æ®idï¼ä¹å°±æ¯ä¸»é®ï¼ç顺åºæåçï¼æä»¥æ ¹æ®è¿äºå¹¶ä¸è¿ç»çidå¼å°èç°ç´¢å¼ä¸è®¿é®å®æ´çç¨æ·è®°å½å¯è½åå¸å¨ä¸åçæ°æ®é¡µä¸ï¼è¿æ ·è¯»åå®æ´çç¨æ·è®°å½å¯è½è¦è®¿é®æ´å¤çæ°æ®é¡µï¼è¿ç§è¯»åæ¹å¼æ们ä¹å¯ä»¥ç§°ä¸ºéæºI/Oã
æ以è¿ä¸ªä½¿ç¨ç´¢å¼idx_name_birthday_phone_numberçæ¥è¯¢æè¿ä¹ä¸¤ä¸ªç¹ç¹ï¼
- ä¼ä½¿ç¨å°ä¸¤ä¸ªB+æ ç´¢å¼ï¼ä¸ä¸ªäºçº§ç´¢å¼ï¼ä¸ä¸ªèç°ç´¢å¼ã
- 访é®äºçº§ç´¢å¼ä½¿ç¨é¡ºåºI/Oï¼è®¿é®èç°ç´¢å¼ä½¿ç¨éæºI/Oã
éè¦å表çè®°å½è¶å¤ï¼ä½¿ç¨äºçº§ç´¢å¼çæ§è½å°±è¶ä½ï¼çè³è®©æäºæ¥è¯¢å®æ¿ä½¿ç¨å ¨è¡¨æ«æä¹ä¸ä½¿ç¨äºçº§ç´¢å¼ãæ¯æ¹è¯´nameå¼å¨Asaï½Barlowä¹é´çç¨æ·è®°å½æ°éå å ¨é¨è®°å½æ°é90%以ä¸ï¼é£ä¹å¦æ使ç¨idx_name_birthday_phone_numberç´¢å¼çè¯ï¼æ90%å¤çidå¼éè¦å表ï¼è¿ä¸æ¯ååä¸è®¨å¥½ä¹ï¼è¿ä¸å¦ç´æ¥å»æ«æèç°ç´¢å¼ï¼ä¹å°±æ¯å ¨è¡¨æ«æï¼ã
æ¥è¯¢ä¼åå¨åçå·¥ä½ï¼æ¥è¯¢ä¼åå¨ä¼äºå 对表ä¸çè®°å½è®¡ç®ä¸äºç»è®¡æ°æ®ï¼ç¶ååå©ç¨è¿äºç»è®¡æ°æ®æ ¹æ®æ¥è¯¢çæ¡ä»¶æ¥è®¡ç®ä¸ä¸éè¦å表çè®°å½æ°ï¼éè¦å表çè®°å½æ°è¶å¤ï¼å°±è¶å¾åäºä½¿ç¨å ¨è¡¨æ«æï¼åä¹å¾åäºä½¿ç¨äºçº§ç´¢å¼ + å表çæ¹å¼ã
æ¯æ¹è¯´ä¸è¾¹çæ¥è¯¢å¯ä»¥æ¹åæè¿æ ·ï¼
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' LIMIT 10;
æ·»å äºLIMIT 10çæ¥è¯¢æ´å®¹æ让ä¼åå¨éç¨äºçº§ç´¢å¼ + å表çæ¹å¼è¿è¡æ¥è¯¢ã
è¦çç´¢å¼
为äºå½»åºåå«å表æä½å¸¦æ¥çæ§è½æèï¼æ们建议ï¼æ好å¨æ¥è¯¢å表éåªå å«ç´¢å¼åã
SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow'
å 为æ们åªæ¥è¯¢name, birthday, phone_numberè¿ä¸ä¸ªç´¢å¼åçå¼ï¼æ以å¨éè¿idx_name_birthday_phone_numberç´¢å¼å¾å°ç»æåå°±ä¸å¿ å°èç°ç´¢å¼ä¸åæ¥æ¾è®°å½çå©ä½åï¼ä¹å°±æ¯countryåçå¼äºï¼è¿æ ·å°±çå»äºå表æä½å¸¦æ¥çæ§è½æèã
æ们æè¿ç§åªéè¦ç¨å°ç´¢å¼çæ¥è¯¢æ¹å¼ç§°ä¸ºç´¢å¼è¦çã
æ们å¾ä¸é¼å±ç¨*å·ä½ä¸ºæ¥è¯¢å表ï¼æ好ææ们éè¦æ¥è¯¢çåä¾æ¬¡æ æã
å¦ä½æéç´¢å¼
åªä¸ºç¨äºæç´¢ãæåºæåç»çåå建索å¼
ä¹å°±æ¯è¯´ï¼åªä¸ºåºç°å¨WHEREåå¥ä¸çåãè¿æ¥åå¥ä¸çè¿æ¥åï¼æè åºç°å¨ORDER BYæGROUP BYåå¥ä¸çåå建索å¼ãèåºç°å¨æ¥è¯¢å表ä¸çåå°±æ²¡å¿ è¦å»ºç«ç´¢å¼äº
SELECT birthday, country FROM person_name WHERE name = 'Ashburn';
èèåçåºæ°
è®°å½è¡æ°ä¸å®çæ åµä¸ï¼åçåºæ°è¶å¤§ï¼è¯¥åä¸çå¼è¶åæ£ï¼åçåºæ°è¶å°ï¼è¯¥åä¸çå¼è¶éä¸ã
å设æ个åçåºæ°ä¸º1ï¼ä¹å°±æ¯ææè®°å½å¨è¯¥åä¸çå¼é½ä¸æ ·ï¼é£ä¸ºè¯¥å建ç«ç´¢å¼æ¯æ²¡æç¨çï¼å 为ææå¼é½ä¸æ ·å°±æ æ³æåºï¼æ æ³è¿è¡å¿«éæ¥æ¾äºã
èä¸å¦ææ个建ç«äºäºçº§ç´¢å¼çåçéå¤å¼ç¹å«å¤ï¼é£ä¹ä½¿ç¨è¿ä¸ªäºçº§ç´¢å¼æ¥åºçè®°å½è¿å¯è½è¦åå表æä½ï¼è¿æ ·æ§è½æèå°±æ´å¤§äºã
ç´¢å¼åçç±»åå°½éå°
å¦ææ们æ³è¦å¯¹æ个æ´æ°å建ç«ç´¢å¼çè¯ï¼å¨è¡¨ç¤ºçæ´æ°èå´å 许çæ åµä¸ï¼å°½é让索å¼å使ç¨è¾å°çç±»åã
å 为ï¼
- æ°æ®ç±»åè¶å°ï¼å¨æ¥è¯¢æ¶è¿è¡çæ¯è¾æä½è¶å¿«ï¼è¿æ¯CPUå±æ¬¡çä¸ä¸ï¼
- æ°æ®ç±»åè¶å°ï¼ç´¢å¼å ç¨çåå¨ç©ºé´å°±è¶å°ï¼å¨ä¸ä¸ªæ°æ®é¡µå å°±å¯ä»¥æ¾ä¸æ´å¤çè®°å½ï¼ä»èåå°ç£çI/O带æ¥çæ§è½æèï¼ä¹å°±æå³çå¯ä»¥ææ´å¤çæ°æ®é¡µç¼åå¨å åä¸ï¼ä»èå 快读åæçã
å 为ä¸ä» æ¯èç°ç´¢å¼ä¸ä¼åå¨ä¸»é®å¼ï¼å ¶ä»ææçäºçº§ç´¢å¼çèç¹å¤é½ä¼åå¨ä¸ä»½è®°å½ç主é®å¼ï¼å¦æ主é®éç¨æ´å°çæ°æ®ç±»åï¼ä¹å°±æå³çèçæ´å¤çåå¨ç©ºé´åæ´é«æçI/Oã
让索å¼åå¨æ¯è¾è¡¨è¾¾å¼ä¸åç¬åºç°
å¦ï¼
- WHERE my_col * 2 < 4
- WHERE my_col < 4/2
第1个WHEREåå¥ä¸my_colå并ä¸æ¯ä»¥åç¬åçå½¢å¼åºç°çï¼èæ¯ä»¥my_col * 2è¿æ ·ç表达å¼çå½¢å¼åºç°çï¼åå¨å¼æä¼ä¾æ¬¡éåææçè®°å½ï¼è®¡ç®è¿ä¸ªè¡¨è¾¾å¼çå¼æ¯ä¸æ¯å°äº4ã
å¦æç´¢å¼åå¨æ¯è¾è¡¨è¾¾å¼ä¸ä¸æ¯ä»¥åç¬åçå½¢å¼åºç°ï¼èæ¯ä»¥æ个表达å¼ï¼æè å½æ°è°ç¨å½¢å¼åºç°çè¯ï¼æ¯ç¨ä¸å°ç´¢å¼çã
主é®æå ¥é¡ºåº
对äºä¸ä¸ªä½¿ç¨InnoDBåå¨å¼æç表æ¥è¯´ï¼å¨æ们没ææ¾å¼çå建索å¼æ¶ï¼è¡¨ä¸çæ°æ®å®é ä¸é½æ¯åå¨å¨èç°ç´¢å¼çå¶åèç¹çãèè®°å½åæ¯åå¨å¨æ°æ®é¡µä¸çï¼æ°æ®é¡µåè®°å½åæ¯æç §è®°å½ä¸»é®å¼ä»å°å°å¤§ç顺åºè¿è¡æåºï¼æ以å¦ææ们æå ¥çè®°å½ç主é®å¼æ¯ä¾æ¬¡å¢å¤§çè¯ï¼é£æ们æ¯æ满ä¸ä¸ªæ°æ®é¡µå°±æ¢å°ä¸ä¸ä¸ªæ°æ®é¡µç»§ç»æã
å¦ææ们æå ¥ç主é®å¼å¿½å¤§å¿½å°çè¯ï¼å°±ä¼éè¦é¡µåè£åè®°å½ç§»ä½ï¼æå³çï¼æ§è½æèï¼æ以å¦ææ们æ³å°½éé¿å è¿æ ·æ è°çæ§è½æèï¼æ好让æå ¥çè®°å½ç主é®å¼ä¾æ¬¡éå¢ï¼è¿æ ·å°±ä¸ä¼åçè¿æ ·çæ§è½æèäºã
æ以æ们建议ï¼è®©ä¸»é®å ·æAUTO_INCREMENTï¼è®©åå¨å¼æèªå·±ä¸ºè¡¨çæ主é®ï¼èä¸æ¯æ们æå¨æå ¥ã