@
- 1. æåºç两ç§æ¹å¼
- 2. ç´¢å¼æåº2.1 æ¡ä¾ä¸2.2 æ¡ä¾äº2.3 æ¡ä¾ä¸2.4 æ¡ä¾å2.5 æ¡ä¾äº2.6 æ¡ä¾å 2.7 æ¡ä¾ä¸2.8 æ¡ä¾å «
- 3. å ¶ä»æ åµ3.1 å¤è¡¨èæ¥3.2 order by null
- 4. å°ç»
åé¢è·å°ä¼ä¼´ä»¬å享çç´¢å¼ç¸å ³çå 容ï¼åºæ¬ä¸é½æ¯å¨ where åå¥ä¸ä½¿ç¨ç´¢å¼ï¼å®é ä¸ï¼ç´¢å¼ä¹è¿æå¦å¤ä¸ä¸ªå¤§çç¨å¤ï¼é£å°±æ¯å¨æåºä¸ä½¿ç¨ç´¢å¼ï¼ä»å¤©æ们就æ¥èèè¿ä¸ªè¯é¢ã
1. æåºç两ç§æ¹å¼
MySQL ä¸æ³ç»æ¥è¯¢ç»ææåºï¼æ们åªéè¦æ¥ä¸ä¸ª order by å³å¯ï¼SQL å¾ç®åï¼åºå±å®ç°èµ·æ¥æ´ä½ä¸æ¥è¯´ï¼æ两ç§ä¸åçæè·¯ï¼
- filesortï¼ææ¶åæ们ä¹å°ä¹ç§°ä¸ºæ件æåºï¼è¿ä¸ªååææ¶åä¼ç»æ们ä¸äºè¯¯è§£ï¼è®©äººä»¥ä¸ºæ¯å¨ç£çä¸è¿è¡æåºçï¼ç¶èå®é ä¸å¹¶ä¸ä¸å®ï¼æ°æ®éæ¯è¾å°çæ¶åï¼ç´æ¥å¨å åä¸è¿è¡æåºå°±è¡äºï¼åªæå½å¨å åä¸æ æ³å®ææåºçæ¶åï¼æä¼ç¨å°ç£çæ件ã
- ç´¢å¼æåºï¼ç±äº InnoDB ä¸çç´¢å¼æ¯æç § B+Tree çå½¢å¼å°æ°æ®ç»ç»å¨ä¸èµ·çï¼B+Tree ä¸æ°æ®æ¬èº«å°±æ¯æåºçï¼æ以å¦æè½å¤å©ç¨å¥½ç´¢å¼ï¼æåºçäºæ å°±ä¼äºåååã
ä¸å ±å°±è¿ä¸¤ç§æåºçæ¹å¼ï¼å°ä¼ä¼´ä»¬ä¹åç°äºï¼å¦ææ们çç´¢å¼è®¾è®¡æ¯è¾åçï¼æç»è½å¤æç §ç¬¬ 2 ç§æ¹å¼è¿è¡æåºï¼é£è¯å®æ¯æ好ä¸è¿äºã
ä¸è¿è¿ééè¦æ³¨æä¸ä¸ªç»èï¼ç¬¬äºç§æåºæ¹å¼å¿«æä¸ä¸ªåæï¼é£å°±æ¯ä¸éè¦å表ï¼å¦ææ¥è¯¢çè¿ç¨ä¸éè¦å表ï¼é£ä¹ç¬¬äºç§æ¹å¼å°±ä¸ä¸å®å¿«äºãåå ä¹ç®åï¼
- å¦æä¸éè¦å表ï¼ä¹å°±æ¯æ们æ³è¦æ¥è¯¢çæ°æ®é½å¨ç´¢å¼æ ä¸ï¼ç´¢å¼æ ä¸çæ°æ®æ¬èº«åé½æ¯æç §é¡ºåºåå¨çï¼é£ä¹æ¥å°æ°æ®ç´æ¥è¿åå³å¯ï¼æ¬èº«å°±æ¯æåºçã
- å¦ææ¥è¯¢çæ¶åï¼ç´¢å¼æ ä¸å¹¶æ²¡ææ们æ³è¦çå段ï¼é£ä¹å°±éè¦å表ï¼å°ä¼ä¼´ä»¬ç¥éï¼å表åºæ¬ä¸é½æ¯éæº IO äºï¼å 为å表çæ¶åï¼ä¸»é®å¼å¹¶ä¸ä¸å®è¿ç»ï¼æ¤æ¶æçå°±ä¼ä½ä¸äºãé£ä¹è¿ä¸ªæ¶å第äºç§æåºæ¹å¼çæ§è½å°±ä¸ä¸å®å¼ºäºç¬¬ä¸ç§äºï¼å½ç¶ï¼è¿å¹¶æ åºå®ç»è®ºï¼è¿æ¯è¦ç»åå ·ä½æ åµåæï¼è¿éæåªæ¯åè¯å°ä¼ä¼´ä»¬æåç§å¯è½çæ åµã
2. ç´¢å¼æåº
å¦ææ们æ³ç¨ä¸ç´¢å¼æåºï¼é£ä¹éè¦æ»¡è¶³åªäºæ¡ä»¶å¢ï¼
è¿æ¯ä»¥æ们ä¸ç¯æç« çæ°æ®ä¸ºä¾ï¼å设ææå¦ä¸è¡¨ç»æï¼
CREATE TABLE `user` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`age` int DEFAULT NULL,
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`gender` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_prop_index` (`username`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
è¿ä¸ªè¡¨ä¸æä¸ä¸ªèåç´¢å¼ï¼èåç´¢å¼çå段å å« usernameãage å address ä¸ä¸ªã
表ä¸çæ°æ®å¦ä¸ï¼
id(主é®) username age address gender 1 ab 99 æ·±å³ ç· 2 bw 95 天津 ç· 3 cx 93 æ·±å³ ç· 4 bc 80 ä¸æµ· 女 5 bg 85 éåº å¥³ 6 ac 98 å¹¿å· ç· 7 bw 99 æµ·å£ å¥³ 8 ck 90 æ·±å³ ç· 9 cc 92 æ¦æ± ç· 10 af 88 å京 女
è¿æ¯å设 usernameãageãaddress ä¸ä¸ªå段ç»æèåç´¢å¼ï¼B+Tree å¦ä¸ï¼
å°ä¼ä¼´ä»¬å°±æ³æ³ï¼æä¹æ ·æ¥è¯¢ï¼æ¥åºæ¥çç»ææ¯æåºçï¼
ç»å¤§å®¶ 1 åéæ»ç»ä¸ä¸ã
æ们æ¥æ¢³çä¸ï¼åªæå½ç´¢å¼ç顺åºå order by åå¥ç顺åºå®å ¨ä¸è´ï¼å¹¶ä¸ææåçæåºæ¹åä¹é½ä¸è´çæ åµä¸ï¼MySQL æè½éè¿ç´¢å¼æ¥å¯¹ç»æè¿è¡æåºï¼åæ¶ï¼å¦ææ¯èåç´¢å¼ï¼order by åå¥ä¹éè¦æ»¡è¶³æå·¦å¹é ååã
æ举å 个ä¾åã
2.1 æ¡ä¾ä¸
å æ¥çå¦ä¸ SQLï¼
select address from user order by username;
è¿ä¸ªæ¯æ¥è¯¢ address å段ï¼æ ¹æ® username è¿è¡æåºãå¾ææ¾ï¼æ们æ³è¦ç address å段就åå¨äºè¿ä¸ªèåç´¢å¼ç B+Tree ä¸ï¼å¹¶ä¸è¿ä¸ªèåç´¢å¼ç B+Tree å°±æ¯æç § username è¿è¡ååºæåºçï¼æ以è¿ä¸ª SQL å°±å¯ä»¥éè¿ç´¢å¼è¿è¡æåºï¼å¦ä¸å¾ï¼
type:index 就说æäº MySQL 使ç¨äºç´¢å¼æ«ææ¥è¿è¡æåºçã
2.2 æ¡ä¾äº
åæ¥çä¸é¢è¿æ¡ SQLï¼
select address from user order by username asc,age desc\G
è¿ä¸ª SQL è¿æ¯æ¥è¯¢ address å段ï¼æ¯æ ¹æ® username å age è¿è¡æåºçï¼å ¶ä¸ username æ¯æç §ååºæåºï¼age åæ¯æç §ååºæåºï¼å°ä¼ä¼´ä»¬æ³æ³ï¼å¨åé¢è¿ä¸ªèåç´¢å¼ç B+Tree ä¸ï¼username æ¯ååºç没é®é¢ï¼å½ username ç¸åçæ¶åï¼age ä¹æ¯æç §ååºæåºçï¼ä½æ¯ SQL ä¸å´è¦ä¸ä¸ªååºä¸ä¸ªååºï¼æ¾ç¶ä»ç´¢å¼æ ä¸æ¿å°çæ°æ®æ æ³æ»¡è¶³è¿æ ·çæ¡ä»¶ï¼æ以è¿ä¸ªæ¥è¯¢å¹¶ä¸ä¼ä½¿ç¨ç´¢å¼æåºï¼å¦ä¸å¾ï¼
Extra ä¸ç Using filesort 就说æäºè¿ééè¦æ件æåºï¼æ æ³éè¿ç´¢å¼æåºå®æéæ±ã
2.3 æ¡ä¾ä¸
åæ¥çå¦ä¸ SQLï¼
select address from user order by username desc
è¿ä¸ª SQL å 2.1 å°èç SQL ç¸æ¯å°±æ¯æåºç顺åºåäºï¼ç¬¬ä¸ä¸ª SQL 没æå顺åºï¼é»è®¤å°±æ¯ååºï¼è¿ä¸ªéè¾¹åäºæ¯æç §ååºæ¥æåãB+Tree ä¸ç username æ¯ååºï¼é£ä¹è¿ä¸ªè½ç¨å°ç´¢å¼æåºåï¼è¿ä¸ªæ¯å¯ä»¥ä½¿ç¨å°ç´¢å¼æåºçï¼å¨ MySQL5.7 ä¸ï¼æ§è¡è®¡åå¦ä¸ï¼
å¨ MySQL8.x ä¸ï¼æ§è¡è®¡åå¦ä¸ï¼
å°ä¼ä¼´ä»¬çå°ï¼åºå«å¨äº Extra ä¸å¤äºä¸ä¸ª Backward index scanã
è¿æ¯å¥ææå¢ï¼
å¨ MySQL8 ä¹åï¼ç´¢å¼æ¯å¯ä»¥è¢«ååæ«æçï¼ä½æ¯ååæ«ææçä¼ä½ä¸äºï¼æ以å°ä¼ä¼´ä»¬çå°ï¼å¨ MySQL5.7 ä¸ç¨å°äºç´¢å¼æåºï¼èä¸ä¹æ²¡è¯´å ¶ä»çï¼è¿å ¶å®å°±æ¯ç´¢å¼ååæ«æäºã
ä» MySQL8 å¼å§ï¼ç´¢å¼å®ä¹æ¶åçéåºå ³é®å DESC å°ä¸å被忽ç¥ï¼ç´¢å¼æ å¨åå¨æ°æ®çæ¶åå¯ä»¥éåºåå¨äºï¼è¿æ ·å¨å°æ¥æ¥è¯¢çæ¶åæ«æç´¢å¼å°±å¯ä»¥æç §æ£åæ«æäºï¼æ£åæ«ææçç¸å¯¹äºååæ«ææçä¼é«ä¸äºã
è¿åææ¥ä¸¾ä¸ªä¾å说æé®é¢ãå设ææå¦ä¸å建表ç SQLï¼
CREATE TABLE t (
c1 INT, c2 INT,
INDEX idx1 (c1 ASC, c2 ASC),
INDEX idx2 (c1 ASC, c2 DESC),
INDEX idx3 (c1 DESC, c2 ASC),
INDEX idx4 (c1 DESC, c2 DESC)
);
å½æå¨ MySQL5.7 ä¸æ§è¡å¦ä¸ SQL ä¹åï¼åæ¥æ¥ç表çå®ä¹ï¼ç»æå¦ä¸ï¼
å¯ä»¥çå°ï¼è½ç¶æå¨æ§è¡çæ¶åå®äºç´¢å¼å段ç顺åºï¼ä½æ¯è¿ä¸ªé¡ºåºå®é ä¸æ¯è¢«å¿½ç¥äºã
åæ¥çç MySQL8 ä¸æ§è¡ä¹åçç»æï¼
å¯ä»¥çå°ï¼å¨ MySQL8 ä¸ï¼ç´¢å¼å®ä¹æ¶å段ç顺åºè¢«ä¿çäºãè¿å°è¯äºæ们åé¢æ说ç没æé®é¢ã
æåï¼åå°æ们çé®é¢ï¼Backward index scan 表示ä¼åå¨å¨æ¥è¯¢çæ¶åå°è½å¤ä½¿ç¨éåºç´¢å¼ã
2.4 æ¡ä¾å
åæ¥çå¦ä¸ SQLï¼
select gender from user where username='ab' order by age
è¿ä¸ª SQL ä¸å·²ç»ç» username æå®äºå ·ä½çå¼äºï¼å¨åé¢ç B+Tree ä¸ï¼å½ username å·²ç»ç¡®å®çæ¶åï¼é£ä¹æ¥ä¸æ¥å°±æ¯æç § age æåºçï¼å¦æ age ç¸ååæ¯æç § address æåºï¼æ以ä¸é¢è¿ä¸ª SQL æ¯å¯ä»¥éè¿ç´¢å¼æåºçï¼
2.5 æ¡ä¾äº
åæ¥çå¦ä¸ SQLï¼
select gender from user where username='ab' order by address
è¿ä¸ª SQL ä¸ username ä¹æ¯ç»æå®äºå ·ä½çå¼äºï¼ä½æ¯æåºå´æ¯æç § address æåºçï¼å°ä¼ä¼´ä»¬ç¥éï¼å½ username ç¡®å®åï¼é¦å æ¯æç § age æåºï¼å ¶æ¬¡ææ¯æç § address æåºï¼æ以ï¼å¯¹äºä¸é¢è¿ä¸ª SQLï¼ä»ç´¢å¼æ ä¸è¯»ååºæ¥çæ°æ®ï¼é¡ºåºå¹¶ä¸ä¸å®æ¯æç § address æçï¼æ以ä¸é¢è¿ä¸ª SQL æ æ³ç¨å°ç´¢å¼æåºï¼
2.6 æ¡ä¾å
åæ¥çä¸é¢è¿ä¸ª SQLï¼
select gender from user where username like 'a%' order by age
è¿ä¸ª SQL ä¸çæ¥è¯¢æ¡ä»¶ username æ¯èå´æç´¢ï¼å½ username æ¯èå´æç´¢çæ¶åï¼å°±æ æ³ä¿è¯ç¸åºç age æ¯æåºçäºï¼æ以è¿ä¸ª SQL ä¹æ æ³ä½¿ç¨ç´¢å¼æåºï¼
å¦å¤éè¦æ³¨æçæ¯ï¼åæ¥è¯¢æ¡ä»¶ä¸ç IN å BETWEEN è¿æ ·çå ³é®åï¼ä¹ç®æ¯èå´æç´¢ï¼å¦æ where åå¥ä¸åºç°è¿äºå ³é®åï¼ä¹æ¯æå¯è½å¯¼è´æ æ³ä½¿ç¨ç´¢å¼æåºçã
2.7 æ¡ä¾ä¸
åæ¥çä¸é¢è¿ä¸ª SQLï¼
select gender from user where username like 'a%' order by username,age
è¿ä¸ªè½ç¶ username ä¹æ¯æç §èå´æç´¢ï¼ä½æ¯æç»æåºçæ¶åå´æ¯æç § username å age æåºçï¼æç §èå´æç´¢æ¿åºæ¥ç username å age æ¬èº«å°±æ¯æåºçï¼æ以è¿éä¹å¯ä»¥ä½¿ç¨ç´¢å¼æåºï¼
2.8 æ¡ä¾å «
åæ¥çä¸é¢è¿ä¸ª SQLï¼
select gender from user where username like 'a%' order by username,gender
è¿ä¸ª SQL å°±ä¸ç¨å¤è¯´äºï¼æåºå段ä¸åºç°äºç´¢å¼ä¹å¤çåï¼é£è¯å®æ²¡æ³ä½¿ç¨ç´¢å¼æåºäºï¼
æ»ä¹ï¼å°±æ¯å½æä»¬æ ¹æ® where åå¥ä¸çæ¡ä»¶ä» B+Tree ä¸å®ä½å°æ°æ®ä¹åï¼å®ä½å°çè¿ä¸ªæ°æ®ç©¶ç«æ¯å¦æåºï¼å¦ææåºä¸æ¯ SQL ä¸è¦æ±ç顺åºï¼å°±è½ä½¿ç¨ç´¢å¼æåºï¼å¦åå°±ä¸å¯ä»¥ã
ç°å¨æ们åæ¥åè¿å¤´çä¸ä¸ä¸å¼å§çç»è®ºï¼å¤§å®¶è¿ä¸ªæ¶ååºè¯¥å°±å¥½ç解äºï¼
åªæå½ç´¢å¼ç顺åºå order by åå¥ç顺åºå®å ¨ä¸è´ï¼å¹¶ä¸ææåçæåºæ¹åä¹é½ä¸è´çæ åµä¸ï¼MySQL æè½éè¿ç´¢å¼æ¥å¯¹ç»æè¿è¡æåºï¼åæ¶ï¼å¦ææ¯èåç´¢å¼ï¼order by åå¥ä¹éè¦æ»¡è¶³æå·¦å¹é ååã
3. å ¶ä»æ åµ
3.1 å¤è¡¨èæ¥
å½æ们å¨æ¥è¯¢çæ¶åæ¯å¤è¡¨è¿æ¥æ¥è¯¢æ¶ï¼å¦æç¨å°äºæåºï¼é£ä¹ order by åå¥ä¸æ¶åå°çå段ï¼å¿ é¡»å ¨é¨å¨ç¬¬ä¸ä¸ªè¡¨ä¸ï¼æ¤æ¶æä¼ç¨å°ç´¢å¼æåºã
æ¾å¥ä¸¾ä¸ä¸ª TienChin 项ç®ä¸çä¾åï¼TienChin ä¸æä¸ä¸ªæ´»å¨æ¸ é表 tienchin_channelï¼è¿æä¸ä¸ªæ´»å¨è¡¨ tienchin_activityï¼æ´»å¨è¡¨ä¸å¼ç¨å°äºæ¸ é表ç idï¼æ们æ¥åå¦ä¸ä¸ä¸ªå¤è¡¨èåæ¥è¯¢ï¼
select ta.name from tienchin_activity ta inner join tienchin_channel tc using(`channel_id`)
æ们æ¥çä¸è¿ä¸ª SQL çæ§è¡è®¡åï¼
å¯ä»¥çå°ï¼å¨è¿ä¸ªæ¥è¯¢ä¸ï¼ä¼åå¨å° ta 表ä½ä¸ºäºç¬¬ä¸å¼ 表ï¼tc 表ä½ä¸ºäºç¬¬äºå¼ 表ï¼é£ä¹æ ¹æ®åé¢çç»è®ºï¼å¦æ使ç¨ç¬¬ä¸ä¸ªè¡¨ä¸çç´¢å¼æåºï¼å°±ä¼ç¨å°ç´¢å¼æåºï¼ç¬¬äºå¼ 表çåç¨ä¸äºï¼æ们æ¥éªè¯ä¸ä¸ã
å¯ä»¥çå°ï¼å¦ææ¯ç¬¬ä¸å¼ 表çç´¢å¼ï¼å°±ç¨å°äºç´¢å¼æåºï¼å¦ææ¯ç¬¬äºå¼ 表çç´¢å¼ï¼å°±æ²¡æç¨å°ç´¢å¼æåºï¼å¦æä¸¤å¼ è¡¨çç´¢å¼é½ç¨äºï¼ä¹ä¸ä¼ä½¿ç¨ç´¢å¼æåºã
3.2 order by null
è¿æä¸ç§ç¹æ®çæ åµå°±æ¯ order by nullï¼ä¸ç¥éæ没æå°ä¼ä¼´è§å°è¿æ人è¿æ ·åï¼
å¨ MySQL8 ä¹åï¼é»è®¤ä¼æç § group by çå段è¿è¡æåºï¼æ¤æ¶å ä¸ order by null å°±æ¯åè¯ MySQLï¼ä¸ç¨å¸®ææåºäºï¼ç´æ¥è¿åç»æå°±è¡äºï¼å 为å¦æä¸å order by nullï¼åå¯è½ä¼è¿è¡ filesort æåºï¼éä½æ¥è¯¢æçã
ä¸è¿ä» MySQL8 å¼å§ï¼é»è®¤å·²ç»ä¸ä¼æç § group by å段æåºäºï¼æ以è¿å¥ç°å¨å ¶å®å¯ä»¥ä¸ç¨åäºã
4. å°ç»
好å¦ï¼å ³äº MySQL ä¸çç´¢å¼æåºå°±åå°ä¼ä¼´ä»¬èè¿ä¹å¤ï¼å¸æ大家é½æææ¶è·ï½