é¦å
æ¥çç表æ¯å¦æç´¢å¼çå½ä»¤
show index from 表åï¼
çå°ä¸»é®ç´¢å¼ï¼ç´¢å¼ç±»åæ¯BTREE(äºåæ )
æ£æ¯å 为è¿ä¸ªäºåæ ç®æ³ï¼è®©æ¥è¯¢é度快å¾å¤ï¼äºåæ çåçï¼å°±æ¯åæä¸é´çä¸ä¸ªæ°ï¼ç¶åæ大äºè¿ä¸ªæ°çå¾å³è¾¹æï¼å°äºè¿ä¸ªæ°çå°±åå·¦æï¼æ¯æ¬¡ååï¼ç¶åä¾æ¬¡ç±»æ¨ï¼æ¯æ¬¡ååï¼å½¢æä¸ä¸ªæ ç¶ç»æå¾
ä¾å¦ä¸é¢çä¾åï¼æ们ä¸ä½¿ç¨ç´¢å¼çè¯ï¼éè¦æ¥è¯¢11次ææç¼å·ä¸º4çæ°æ®ååºï¼å¦æå ä¸ç´¢å¼ï¼æ们åªéè¦4次就å¯ä»¥ååºã
å¦å¤§å®¶æç¥éçï¼MySQLç®å主è¦æ以ä¸å ç§ç´¢å¼ç±»åï¼FULLTEXTï¼HASHï¼BTREEï¼RTREEã
é£ä¹ï¼è¿å ç§ç´¢å¼æä»ä¹åè½åæ§è½ä¸çä¸åå¢ï¼
FULLTEXT
å³ä¸ºå
¨æç´¢å¼ï¼ç®ååªæMyISAMå¼ææ¯æãå
¶å¯ä»¥å¨CREATE TABLE ï¼ALTER TABLE ï¼CREATE INDEX 使ç¨ï¼ä¸è¿ç®ååªæ CHARãVARCHAR ï¼TEXT åä¸å¯ä»¥å建å
¨æç´¢å¼ãå¼å¾ä¸æçæ¯ï¼å¨æ°æ®éè¾å¤§æ¶åï¼ç°å°æ°æ®æ¾å
¥ä¸ä¸ªæ²¡æå
¨å±ç´¢å¼ç表ä¸ï¼ç¶ååç¨CREATE INDEXå建FULLTEXTç´¢å¼ï¼è¦æ¯å
为ä¸å¼ 表建ç«FULLTEXTç¶ååå°æ°æ®åå
¥çé度快å¾å¤ã
å
¨æç´¢å¼å¹¶ä¸æ¯åMyISAMä¸èµ·è¯ççï¼å®çåºç°æ¯ä¸ºäºè§£å³WHERE name LIKE â%word%"è¿ç±»é对ææ¬ç模ç³æ¥è¯¢æçè¾ä½çé®é¢ãå¨æ²¡æå
¨æç´¢å¼ä¹åï¼è¿æ ·ä¸ä¸ªæ¥è¯¢è¯å¥æ¯è¦è¿è¡éåæ°æ®è¡¨æä½çï¼å¯è§ï¼å¨æ°æ®éè¾å¤§æ¶æ¯æå
¶çèæ¶çï¼å¦æ没æå¼æ¥IOå¤çï¼è¿ç¨å°è¢«ææï¼å¾æµªè´¹æ¶é´ï¼å½ç¶è¿éä¸å¯¹å¼æ¥IOä½è¿ä¸æ¥è®²è§£ï¼æ³äºè§£çç«¥éï¼èªè¡è°·å¥ã
å
¨æç´¢å¼ç使ç¨æ¹æ³å¹¶ä¸å¤æï¼
å建ALTER TABLE table ADD INDEX `FULLINDEX` USING FULLTEXT(`cname1`[,cname2â¦]);
使ç¨SELECT * FROM table WHERE MATCH(cname1[,cname2â¦]) AGAINST ('word' MODE );
å
¶ä¸ï¼ MODE为æ寻æ¹å¼ï¼IN BOOLEAN MODE ï¼IN NATURAL LANGUAGE MODE ï¼IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSIONï¼ã
å
³äºè¿ä¸ç§æ寻æ¹å¼ï¼æå®å¨è¿éä¹ä¸å¤å交代ï¼ç®åå°è¯´ï¼å°±æ¯ï¼å¸å°æ¨¡å¼ï¼å
许wordéå«ä¸äºç¹æ®å符ç¨äºæ è®°ä¸äºå
·ä½çè¦æ±ï¼å¦+表示ä¸å®è¦æï¼-表示ä¸å®æ²¡æï¼*表示éç¨å¹é
符ï¼æ¯ä¸æ¯æ³èµ·äºæ£åï¼ç±»ä¼¼å§ï¼èªç¶è¯è¨æ¨¡å¼ï¼å°±æ¯ç®åçåè¯å¹é
ï¼å«è¡¨è¾¾å¼çèªç¶è¯è¨æ¨¡å¼ï¼å°±æ¯å
ç¨èªç¶è¯è¨æ¨¡å¼å¤çï¼å¯¹è¿åçç»æï¼åè¿è¡è¡¨è¾¾å¼å¹é
ã
对æç´¢å¼æç¨å¾®æç¹äºè§£çåå¦ï¼è¯å®ç¥éåè¯è¿ä¸ªæ¦å¿µï¼FULLTEXTç´¢å¼ä¹æ¯æç
§åè¯åç建ç«ç´¢å¼çã西æä¸ï¼å¤§é¨å为åæ¯æåï¼åè¯å¯ä»¥å¾æ¹ä¾¿çæç
§ç©ºæ ¼è¿è¡åå²ãä½å¾ææ¾ï¼ä¸æä¸è½æç
§è¿ç§æ¹å¼è¿è¡åè¯ãé£åæä¹åå¢ï¼è¿ä¸ªå大家ä»ç»ä¸ä¸ªMysqlçä¸æåè¯æ件Mysqlcftï¼æäºå®ï¼å°±å¯ä»¥å¯¹ä¸æè¿è¡åè¯ï¼æ³äºè§£çåå¦è¯·ç§»æ¥Mysqlcftï¼å½ç¶è¿æå
¶ä»çåè¯æ件å¯ä»¥ä½¿ç¨ã
HASH
Hashè¿ä¸ªè¯ï¼å¯ä»¥è¯´ï¼èªææ们å¼å§ç çé£ä¸å¤©èµ·ï¼å°±å¼å§ä¸åå°è§å°å使ç¨å°äºãå
¶å®ï¼hashå°±æ¯ä¸ç§ï¼key=>valueï¼å½¢å¼çé®å¼å¯¹ï¼å¦æ°å¦ä¸çå½æ°æ å°ï¼å
许å¤ä¸ªkey对åºç¸åçvalueï¼ä½ä¸å
许ä¸ä¸ªkey对åºå¤ä¸ªvalueãæ£æ¯ç±äºè¿ä¸ªç¹æ§ï¼hashå¾éååç´¢å¼ï¼ä¸ºæä¸åæå å建ç«hashç´¢å¼ï¼å°±ä¼å©ç¨è¿ä¸åæå åçå¼éè¿ä¸å®çç®æ³è®¡ç®åºä¸ä¸ªhashå¼ï¼å¯¹åºä¸è¡æå è¡æ°æ®ï¼è¿éå¨æ¦å¿µä¸åå½æ°æ å°æåºå«ï¼ä¸è¦æ··æ·ï¼ãå¨Javaè¯è¨ä¸ï¼æ¯ä¸ªç±»é½æèªå·±çhashcode()æ¹æ³ï¼æ²¡ææ¾ç¤ºå®ä¹çé½ç»§æ¿èªobjectç±»ï¼è¯¥æ¹æ³ä½¿å¾æ¯ä¸ä¸ªå¯¹è±¡é½æ¯å¯ä¸çï¼å¨è¿è¡å¯¹è±¡é´equalæ¯è¾ï¼ååºååä¼ è¾ä¸èµ·å°äºå¾éè¦çä½ç¨ãhashççææ¹æ³æå¾å¤ç§ï¼è¶³å¯ä»¥ä¿è¯hashç çå¯ä¸æ§ï¼ä¾å¦å¨MongoDBä¸ï¼æ¯ä¸ä¸ªdocumenté½æç³»ç»ä¸ºå
¶çæçå¯ä¸çobjectIDï¼å
å«æ¶é´æ³ï¼ä¸»æºæ£åå¼ï¼è¿ç¨PIDï¼åèªå¢IDï¼ä¹æ¯ä¸ç§hashç表ç°ãé¢ï¼æ好åæ¯è¿äº-_-!
ç±äºhashç´¢å¼å¯ä»¥ä¸æ¬¡å®ä½ï¼ä¸éè¦åæ 形索å¼é£æ ·éå±æ¥æ¾,å æ¤å
·ææé«çæçãé£ä¸ºä»ä¹è¿éè¦å
¶ä»çæ 形索å¼å¢ï¼
å¨è¿éæå®å°±ä¸èªå·±æ»ç»äºãå¼ç¨ä¸ååéå
¶ä»å¤§ç¥çæç« ï¼æ¥èª 14çè·¯ çMySQLçbtreeç´¢å¼åhashç´¢å¼çåºå«
ï¼1ï¼Hash ç´¢å¼ä»
ä»
è½æ»¡è¶³"=","IN"å"<=>"æ¥è¯¢ï¼ä¸è½ä½¿ç¨èå´æ¥è¯¢ã
ç±äº Hash ç´¢å¼æ¯è¾çæ¯è¿è¡ Hash è¿ç®ä¹åç Hash å¼ï¼æ以å®åªè½ç¨äºçå¼çè¿æ»¤ï¼ä¸è½ç¨äºåºäºèå´çè¿æ»¤ï¼å 为ç»è¿ç¸åºç Hash ç®æ³å¤çä¹åç Hash å¼ç大å°å
³ç³»ï¼å¹¶ä¸è½ä¿è¯åHashè¿ç®åå®å
¨ä¸æ ·ã
ï¼2ï¼Hash ç´¢å¼æ æ³è¢«ç¨æ¥é¿å
æ°æ®çæåºæä½ã
ç±äº Hash ç´¢å¼ä¸åæ¾çæ¯ç»è¿ Hash 计ç®ä¹åç Hash å¼ï¼èä¸Hashå¼ç大å°å
³ç³»å¹¶ä¸ä¸å®å Hash è¿ç®åçé®å¼å®å
¨ä¸æ ·ï¼æ以æ°æ®åºæ æ³å©ç¨ç´¢å¼çæ°æ®æ¥é¿å
ä»»ä½æåºè¿ç®ï¼
ï¼3ï¼Hash ç´¢å¼ä¸è½å©ç¨é¨åç´¢å¼é®æ¥è¯¢ã
对äºç»åç´¢å¼ï¼Hash ç´¢å¼å¨è®¡ç® Hash å¼çæ¶åæ¯ç»åç´¢å¼é®å并ååä¸èµ·è®¡ç® Hash å¼ï¼èä¸æ¯åç¬è®¡ç® Hash å¼ï¼æ以éè¿ç»åç´¢å¼çåé¢ä¸ä¸ªæå 个索å¼é®è¿è¡æ¥è¯¢çæ¶åï¼Hash ç´¢å¼ä¹æ æ³è¢«å©ç¨ã
ï¼4ï¼Hash ç´¢å¼å¨ä»»ä½æ¶åé½ä¸è½é¿å
表æ«æã
åé¢å·²ç»ç¥éï¼Hash ç´¢å¼æ¯å°ç´¢å¼é®éè¿ Hash è¿ç®ä¹åï¼å° Hashè¿ç®ç»æç Hash å¼åæ对åºçè¡æéä¿¡æ¯åæ¾äºä¸ä¸ª Hash 表ä¸ï¼ç±äºä¸åç´¢å¼é®åå¨ç¸å Hash å¼ï¼æ以å³ä½¿å满足æ个 Hash é®å¼çæ°æ®çè®°å½æ¡æ°ï¼ä¹æ æ³ä» Hash ç´¢å¼ä¸ç´æ¥å®ææ¥è¯¢ï¼è¿æ¯è¦éè¿è®¿é®è¡¨ä¸çå®é
æ°æ®è¿è¡ç¸åºçæ¯è¾ï¼å¹¶å¾å°ç¸åºçç»æã
ï¼5ï¼Hash ç´¢å¼éå°å¤§éHashå¼ç¸ççæ
åµåæ§è½å¹¶ä¸ä¸å®å°±ä¼æ¯B-Treeç´¢å¼é«ã
对äºéæ©æ§æ¯è¾ä½çç´¢å¼é®ï¼å¦æå建 Hash ç´¢å¼ï¼é£ä¹å°ä¼åå¨å¤§éè®°å½æéä¿¡æ¯åäºåä¸ä¸ª Hash å¼ç¸å
³èãè¿æ ·è¦å®ä½æä¸æ¡è®°å½æ¶å°±ä¼é常麻ç¦ï¼ä¼æµªè´¹å¤æ¬¡è¡¨æ°æ®ç访é®ï¼èé ææ´ä½æ§è½ä½ä¸ã
æå®æç¨ä½è¡¥å
ï¼è®²ä¸ä¸HASHç´¢å¼çè¿ç¨ï¼é¡ºä¾¿è§£éä¸ä¸é¢ç第4,5æ¡ï¼
å½æ们为æä¸åææå å建ç«hashç´¢å¼æ¶ï¼ç®åå°±åªæMEMORYå¼ææ¾å¼å°æ¯æè¿ç§ç´¢å¼ï¼ï¼ä¼å¨ç¡¬çä¸çæ类似å¦ä¸çæ件ï¼
hashå¼ åå¨å°å
1db54bc745a1 77#45b5
4bca452157d4 76#4556,77#45ccâ¦
â¦
hashå¼å³ä¸ºéè¿ç¹å®ç®æ³ç±æå®åæ°æ®è®¡ç®åºæ¥ï¼ç£çå°åå³ä¸ºæå¨æ°æ®è¡åå¨å¨ç¡¬çä¸çå°åï¼ä¹æå¯è½æ¯å
¶ä»åå¨å°åï¼å
¶å®MEMORYä¼å°hash表导å
¥å
åï¼ã
è¿æ ·ï¼å½æ们è¿è¡WHERE age = 18 æ¶ï¼ä¼å°18éè¿ç¸åçç®æ³è®¡ç®åºä¸ä¸ªhashå¼==>å¨hash表ä¸æ¾å°å¯¹åºçå¨åå°å==>æ ¹æ®åå¨å°ååå¾æ°æ®ã
æ以ï¼æ¯æ¬¡æ¥è¯¢æ¶é½è¦éåhash表ï¼ç´å°æ¾å°å¯¹åºçhashå¼ï¼å¦ï¼4ï¼ï¼æ°æ®é大äºä¹åï¼hash表ä¹ä¼åå¾åºå¤§èµ·æ¥ï¼æ§è½ä¸éï¼éåèæ¶å¢å ï¼å¦ï¼5ï¼ã
BTREE
BTREEç´¢å¼å°±æ¯ä¸ç§å°ç´¢å¼å¼æä¸å®çç®æ³ï¼åå
¥ä¸ä¸ªæ å½¢çæ°æ®ç»æä¸ï¼ç¸ä¿¡å¦è¿æ°æ®ç»æçç«¥éé½å¯¹å½åå¦ä¹ äºåæ è¿ç§æ°æ®ç»æçç»åè®°å¿ç¹æ°ï¼åæ£æå®æå½æ¶ä¸ºäºè½¯èå¯æ¯è¢«è¿ç©æå¿å¥½å¥½å°æè
¾äºä¸çªï¼ä¸è¿é£æ¬¡èè¯å¥½å没æä¹èè¿ä¸ªãå¦äºåæ ä¸æ ·ï¼æ¯æ¬¡æ¥è¯¢é½æ¯ä»æ çå
¥å£rootå¼å§ï¼ä¾æ¬¡éånodeï¼è·åleafã
BTREEå¨MyISAMéçå½¢å¼åInnodbç¨æä¸å
å¨ Innodbéï¼æ两ç§å½¢æï¼ä¸æ¯primary keyå½¢æï¼å
¶leaf nodeéåæ¾çæ¯æ°æ®ï¼èä¸ä¸ä»
åæ¾äºç´¢å¼é®çæ°æ®ï¼è¿åæ¾äºå
¶ä»å段çæ°æ®ãäºæ¯secondary indexï¼å
¶leaf nodeåæ®éçBTREEå·®ä¸å¤ï¼åªæ¯è¿åæ¾äºæå主é®çä¿¡æ¯.
èå¨MyISAMéï¼ä¸»é®åå
¶ä»ç并没æ太大åºå«ãä¸è¿åInnodbä¸å¤ªä¸æ ·çå°æ¹æ¯å¨MyISAMéï¼leaf nodeéåæ¾çä¸æ¯ä¸»é®çä¿¡æ¯ï¼èæ¯æåæ°æ®æ件éç对åºæ°æ®è¡çä¿¡æ¯.
RTREE
RTREEå¨mysqlå¾å°ä½¿ç¨ï¼ä»
æ¯ægeometryæ°æ®ç±»åï¼æ¯æ该类åçåå¨å¼æåªæMyISAMãBDbãInnoDbãNDbãArchiveå ç§ã
ç¸å¯¹äºBTREEï¼RTREEçä¼å¿å¨äºèå´æ¥æ¾.
åç§ç´¢å¼ç使ç¨æ
åµ
ï¼1ï¼å¯¹äºBTREEè¿ç§Mysqlé»è®¤çç´¢å¼ç±»åï¼å
·ææ®éçéç¨æ§
ï¼2ï¼ç±äºFULLTEXT对ä¸ææ¯æä¸æ¯å¾å¥½ï¼å¨æ²¡ææ件çæ
åµä¸ï¼æ好ä¸è¦ä½¿ç¨ãå
¶å®ï¼ä¸äºå°çå客åºç¨ï¼åªéè¦å¨æ°æ®ééæ¶ï¼ä¸ºå
¶å»ºç«å
³é®åå表ï¼éè¿å
³é®åç´¢å¼ï¼ä¹æ¯ä¸ä¸ªä¸éçæ¹æ³ï¼è³å°æå®ææ¯ç»å¸¸è¿ä¹åçã
ï¼3ï¼å¯¹äºä¸äºæç´¢å¼æ级å«çåºç¨æ¥è¯´ï¼FULLTEXTåæ ·ä¸æ¯ä¸ä¸ªå¥½çå¤çæ¹æ³ï¼Mysqlçå
¨æç´¢å¼å»ºç«çæ件è¿æ¯æ¯è¾å¤§çï¼èä¸æçä¸æ¯å¾é«ï¼å³ä¾¿æ¯ä½¿ç¨äºä¸æåè¯æ件ï¼å¯¹ä¸æåè¯æ¯æä¹åªæ¯ä¸è¬ãçè¦ç¢°å°è¿ç§é®é¢ï¼ApacheçLuceneæ许æ¯ä½ çéæ©ã
ï¼4ï¼æ£æ¯å 为hash表å¨å¤çè¾å°æ°æ®éæ¶å
·ææ å¯æ¯æçç´ çä¼å¿ï¼æ以hashç´¢å¼å¾éååç¼åï¼å
åæ°æ®åºï¼ãå¦mysqlæ°æ®åºçå
åçæ¬Memsqlï¼ä½¿ç¨éå¾å¹¿æ³çç¼åå·¥å
·Mencachedï¼NoSqlæ°æ®åºredisçï¼é½ä½¿ç¨äºhashç´¢å¼è¿ç§å½¢å¼ãå½ç¶ï¼ä¸æ³å¦ä¹ è¿äºä¸è¥¿çè¯MysqlçMEMORYå¼æä¹æ¯å¯ä»¥æ»¡è¶³è¿ç§éæ±çã
温馨提示:答案为网友推荐,仅供参考