åè¨
å¦æä½ æ¥è§¦è¿æ°æ®åºï¼åºè¯¥å¬è¯´è¿æäºåä¸å»ºç«ç´¢å¼è½å¤å å¿«æ¥æ¾é度ï¼å¦æç 究æ´æ·±å ¥ä¸ç¹ç人ï¼å¯è½è¿å¬è¯´è¿èåç´¢å¼ï¼é£ä¹ç´¢å¼ä¸ºä»ä¹è½å¤å å¿«æ¥æ¾é度å¢ï¼ä¸é¢è¯´è¯´æçç®åç解ã
ç´¢å¼
è¯æ³ä¸ä¸ï¼æ1~10000è¿10000个æ°åæ乱顺åºåå¨å¨æ°ç»ä¸ï¼å¦æè¦æ¾å°5000è¿ä¸ªæ°åå¨åªï¼é£å°±å¾ä»æ°ç»ç¬¬0个å ç´ å¼å§ï¼ä¸æ¬¡éåæ¾å°5000è¿ä¸ªæ°ï¼è¿æ°å¥½äº1次æ¾å°ï¼è¿æ°ä¸å¥½éè¦æ¥è¯¢10000个æ°ï¼å¯æ¯å¦ææè¿10000个æ°ä½ä¸ºmapçkeyï¼æ¯ä¸ªæ°åå¨æ°ç»ä¸çä½ç½®ä½ä¸ºvalueï¼åå¨å¨mapç»æä¸å¾å¿«å°±è½æ¾å°ï¼é常æ åµä¸è¦æ¯ç´æ¥éåå¿«çå¤ã
å ¶å®è¿éçmapå å½çæ¯ä¸ä¸ªç´¢å¼çä½ç¨ï¼æ们ç¥émapåå¨æ°æ®æ¶ä½¿ç¨æ å½¢ç»æï¼ä¼æ ¹æ®è¦æ¥æ¾çå¼åå½åèç¹æ¯è¾ï¼æ¥ç¡®å®ç»§ç»æ¥æ¾å·¦åæ¯è¿æ¯å³åæ¯ï¼èæ°æ®åºä¸çç´¢å¼å å½çä¹æ¯è¿æ ·çä½ç¨ï¼mysqlä¸çç´¢å¼æ¯BTreeç»æï¼å¤è·¯æç´¢æ ï¼ï¼å°±æ¯å©ç¨å»ºç«ç´¢å¼çåä¸çææå¼å»ºç«äºä¸æ£µæ ï¼éè¿æåºçæ å½¢æ¥æ¾ä¸è¬è¦æ¯å ¨å±æ索快å¤äºå§ï¼
èåç´¢å¼
ç®åäºè§£äºä¸ä¸ç´¢å¼çå«ä¹ï¼é£ä¹ä»ä¹æ¯èåç´¢å¼å¢ï¼å ¶å®mysqlæ°æ®åºä¸çç´¢å¼ä¸æ¢å¯ä»¥å»ºç«å¨ä¸ä¸ªåä¸ï¼å®å¯ä»¥å°ä¸ä¸ªç´¢å¼åæ¶å»ºç«å¨è¯´å¤ä¸ªåä¸ï¼ä¹å°±æ¯æ们æ说çèåç´¢å¼ï¼èåç´¢å¼çä½ç¨ç¹å«å¤§ï¼ææ¶ä¼è¶ è¿ååç´¢å¼ï¼è³äºä»ä¹æ¶å建ç«ååç´¢å¼ï¼ä»ä¹æ¶å建ç«èåç´¢å¼åæ ·æ¯ä¸ªå¾å¤æçé®é¢ï¼å¨æ¤ä¸åæè¿°ãæå ´è¶£ç读è å¯ä»¥èªè¡æç´¢ä¸ä¸ã
æå·¦åå
å½ä½ å¨å¤ä¸ªåä¸å»ºç«ä¸ä¸ªç´¢å¼æ¶ï¼é£ä¹ææ ·çæ¥æ¾æè½å©ç¨ç´¢å¼å å¿«é度å¢ï¼è¯´å°è¿æ们å 建ç«ä¸ä¸ªå¸¦æç´¢å¼çè¡¨æ ¼ï¼å ·ä½çåæä¸ä¸ä»ä¹å«åç´¢å¼çæå·¦ååã
CREATE TABLE IF NOT EXISTS `test_index`(
`id` int() NOT NULL AUTO_INCREMENT,
`a` int() NOT NULL DEFAULT '0',
`b` int() NOT NULL DEFAULT '0',
`c` int() NOT NULL DEFAULT '0',
`data` int() NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `union_index` (`a`,`b`,`c`)
)ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=binary;
åæä¸è¿°å»ºè¡¨è¯å¥ï¼å建äºä¸ä¸ªå为test_indexçæ°æ®åºè¡¨æ ¼ï¼ç¶åå¨aãbãcä¸åä¸å»ºç«äºèåç´¢å¼ï¼èæå·¦ååæçå°±æ¯å½ä½ 建ç«äºè¿æ ·ä¸ä¸ªç´¢å¼çæ¶åï¼éè¿æ¡ä»¶ (a), (a,b), (a,b,c) è¿ä¸ç§æ¡ä»¶æ¥è¯¢çæ¶åé½å¯ä»¥å©ç¨ç´¢å¼å å¿«é度ï¼æ以å¨å»ºç«ç´¢å¼çæ¶åè¦ææ常ç¨çæ¡ä»¶åæ¾å°èåç´¢å¼çæ左边ï¼æ¥ä¸æ¥æ们æ¥éªè¯ä¸ä¸ï¼å·¥å ·å°±æ¯mysqlçexplainå½ä»¤ã
æµè¯ç¯å¢
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
éªè¯è¿ç¨
- é¦å
以åaä½ä¸ºæ¡ä»¶æ¥è¯¢æ°æ®ï¼æ们çå°
表示å¼ç¨æ¥æ¾,type: ref
表示索å¼é¿åº¦ä¸º4ï¼ä¹å°±æ¯å©ç¨ä¸äºç´¢å¼æ¥è¿è¡æ¥æ¾key_len: 4
mysql> explain select data from test_index where a = \G *************************** row *************************** id: select_type: SIMPLE table: test_index partitions: NULL type: ref possible_keys: union_index key: union_index key_len: ref: const rows: filtered: Extra: NULL row in set, warning ( sec)
- ç¶å以åbä½ä¸ºæ¡ä»¶æ¥è¯¢æ°æ®ï¼å¯ä»¥çå°
è¡¨ç¤ºå ¨è¡¨æ¥æ¾,type: ALL
表示没æç´¢å¼ï¼ä¹å°±è¯´æå¦æåªä½¿ç¨bä½ä¸ºæ¥è¯¢æ¡ä»¶ï¼ä¸è½å©ç¨ç´¢å¼æ¥å å¿«æ¥æ¾é度key_len: NULL
mysql> explain select data from test_index where b = \G *************************** row *************************** id: select_type: SIMPLE table: test_index partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: filtered: Extra: Using where row in set, warning ( sec)
- æ¥ç以åcä½ä¸ºæ¡ä»¶æ¥è¯¢æ°æ®ï¼å¯ä»¥çå°
è¡¨ç¤ºå ¨è¡¨æ¥æ¾,type: ALL
表示没æç´¢å¼ï¼æ åµä¸ç¨bä½ä¸ºæ¡ä»¶ä¸æ ·ï¼åªä½¿ç¨cä½ä¸ºæ¥è¯¢æ¡ä»¶ä¹ä¸è½å©ç¨ç´¢å¼æ¥å å¿«æ¥æ¾é度key_len: NULL
mysql> explain select data from test_index where c = \G *************************** row *************************** id: select_type: SIMPLE table: test_index partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: filtered: Extra: Using where row in set, warning ( sec)
- ç°å¨æ¥æµä¸ä¸ä½¿ç¨aãbä½ä¸ºæ¡ä»¶çæ
åµï¼æ们çå°
表示å¼ç¨æ¥æ¾,type: ref
表示索å¼é¿åº¦ä¸º8ï¼ä¹å°±æ¯è¯´æ们å©ç¨ä¸äºaãbèåç´¢å¼æ¥è¿è¡æ¥æ¾key_len: 8
mysql> explain select data from test_index where a = and b = \G *************************** row *************************** id: select_type: SIMPLE table: test_index partitions: NULL type: ref possible_keys: union_index key: union_index key_len: ref: const,const rows: filtered: Extra: NULL row in set, warning ( sec)
- ç´§æ¥çæ¥æµä¸ä¸ä½¿ç¨aãcä½ä¸ºæ¡ä»¶çæ
åµï¼æ们çå°
表示å¼ç¨æ¥æ¾,type: ref
表示索å¼é¿åº¦ä¸º4ï¼è¿å°±å¥æªäºï¼æç §æå·¦ååæ¥è¯´ï¼aãcä¸æ¯ä¸ä¼å»ºç«ç´¢å¼çï¼ä¸ºä»ä¹ä¼æç´¢å¼é¿åº¦å¢ï¼å ¶å®ä¸aãbä¸çç´¢å¼ä¸æ¯è¾æ们就è½åç°ï¼aãcä¸çç´¢å¼é¿åº¦åªæ4ï¼èä¸åç¬çcä¸æ¯æ²¡æç´¢å¼çï¼æ以4åèé¿åº¦çç´¢å¼åªè½æ¯aä¸çï¼ä¹å°±æ¯è¯´è¿ç§æ åµæ们åªä½¿ç¨äºaåä¸çç´¢å¼æ¥è¿è¡æ¥æ¾key_len: 4
mysql> explain select data from test_index where a = and c = \G *************************** row *************************** id: select_type: SIMPLE table: test_index partitions: NULL type: ref possible_keys: union_index key: union_index key_len: ref: const rows: filtered: Extra: Using index condition row in set, warning ( sec)
- 为äºè¿ä¸æ¥éªè¯ä¸é¢çæ³æ³ï¼è¿ä¸æ¬¡æµä¸ä¸ä½¿ç¨bãcä½ä¸ºæ¡ä»¶çæ
åµï¼æ们çå°
è¡¨ç¤ºå ¨è¡¨æ¥æ¾,type: ALL
表示没æç´¢å¼å¯ä»¥ä½¿ç¨ï¼æç §æå·¦ååæ¥è¯´ï¼båä¸æ²¡æç´¢å¼ï¼cåä¸ä¹æ²¡æç´¢å¼ï¼åæ¶bãcçä¸ä¹ä¸åå¨èåç´¢å¼ï¼æ以使ç¨bãcä½ä¸ºæ¥è¯¢æ¡ä»¶æ¶æ æ³å©ç¨èåç´¢å¼key_len: NULL
mysql> explain select data from test_index where b = and c = \G *************************** row *************************** id: select_type: SIMPLE table: test_index partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: filtered: Extra: Using where row in set, warning ( sec)
- æµè¯å®ä¸¤ä¸ªæ¡ä»¶çæ
åµï¼æ¥ä¸æ¥æµè¯ä¸ä¸ä½¿ç¨aãbãcä½ä¸ºæ¡ä»¶çæ
åµï¼æ们çå°
表示å¼ç¨æ¥æ¾,type: ref
表示索å¼é¿åº¦ä¸º12ï¼è¿å®å ¨ç¬¦åèåç´¢å¼çæå·¦ååï¼åæ¶ä½¿ç¨3个æ¡ä»¶æ¥è¯¢å¯ä»¥å©ç¨èåç´¢å¼key_len: 12
mysql> explain select data from test_index where a = and b = and c = \G *************************** row *************************** id: select_type: SIMPLE table: test_index partitions: NULL type: ref possible_keys: union_index key: union_index key_len: ref: const,const,const rows: filtered: Extra: NULL row in set, warning ( sec)
- æµè¯è¿è¡å°ç°å¨ï¼ææµè¯äºææçæ
åµåï¼ä¸æ¯çï¼è¿å¯ä»¥é¢ å顺åºåï¼æåæ¥ä¸ç´ä»¥ä¸ºèåç´¢å¼æ¯æ顺åºçï¼ç»ææµè¯åæåç°ï¼å©ç¨ç´¢å¼çæ¡ä»¶ç¬¦åâ交æ¢å¾âï¼ä¹å°±æ¯ä¸é¢è¿ç§æ
åµä¹è½å©ç¨aãbä¸çèåç´¢å¼ï¼ç´¢å¼é¿åº¦ä¸º8
mysql> explain select data from test_index where b = and a = \G *************************** row *************************** id: select_type: SIMPLE table: test_index partitions: NULL type: ref possible_keys: union_index key: union_index key_len: ref: const,const rows: filtered: Extra: NULL row in set, warning ( sec)
- åæ¥è¯è¯è¿ç§æ
åµï¼æç
§æå·¦ååï¼cä¸æ²¡æ建ç«ç´¢å¼ï¼aä¸æç´¢å¼ï¼cãa没æ建ç«èåç´¢å¼ï¼æ以åªè½ä½¿ç¨aä¸çç´¢å¼è¿è¡æ¥æ¾ï¼ç»æç´¢å¼é¿åº¦åªæ4ï¼éªè¯äºæ们çæ³æ³ï¼èåæ¥è¯¢æ¡ä»¶ä½¿ç¨ç´¢å¼æ¶æ»¡è¶³â交æ¢å¾â
mysql> explain select data from test_index where c = and a = \G *************************** row *************************** id: select_type: SIMPLE table: test_index partitions: NULL type: ref possible_keys: union_index key: union_index key_len: ref: const rows: filtered: Extra: Using index condition row in set, warning ( sec)
- æ¥ä¸æ¥å ç§äº¤æ¢é¡ºåºçæ åµ(c,b)ã(a,c,b)ã(c,b,a)çï¼å¤§å®¶å¯ä»¥èªå·±è¿è¡éªè¯ï¼å°æ¤ä¸ºæ¢ï¼mysqlèåç´¢å¼çæå·¦ååä¹å°±éªè¯ç»æäºï¼
æ»ç»
- èåç´¢å¼çæå·¦ååå°±æ¯å»ºç«ç´¢å¼
æ¶ï¼çäºå»ºç«äº(a)ã(a,b)ã(a,b,c)ä¸ä¸ªç´¢å¼ï¼ä»å½¢å¼ä¸çå°±æ¯ç´¢å¼å左侧èéï¼æ以å«åæå·¦ååï¼å æ¤æ常ç¨çæ¡ä»¶åºè¯¥æ¾å°èåç´¢å¼çç»å·¦ä¾§ãKEY union_index (a,b,c)
- å©ç¨èåç´¢å¼å éæ¥è¯¢æ¶ï¼èåæ¥è¯¢æ¡ä»¶ç¬¦åâ交æ¢å¾âï¼ä¹å°±æ¯
çä»·äºwhere a = 1 and b = 1
ï¼è¿ä¸¤ç§åæ³é½è½å©ç¨ç´¢å¼where b = 1 and a = 1
ãKEY union_index (a,b,c)
- éå°è¿ç§ä¸ç¡®å®çé®é¢è¿æ¯éè¦å®é æµè¯ä¸ä¸ï¼ç®åçè°æ´ä¸ä¸ç´¢å¼é¡ºåºå¯è½ä¼æ大çæåæçå¦ï¼