(1)主é®ç´¢å¼ å®æ¯ä¸ç§ç¹æ®çå¯ä¸ç´¢å¼ï¼ä¸å
许æ空å¼ãä¸è¬æ¯å¨å»ºè¡¨çæ¶ååæ¶å建主é®ç´¢å¼ï¼ å¤å¶ä»£ç 代ç å¦ä¸: CREATE TABLE user( id int unsigned not null auto_increment, name varchar(50) not null, email varchar(40) not null, primary key (id) ); (2)æ®éç´¢å¼ è¿æ¯æåºæ¬çç´¢å¼ï¼å®æ²¡æä»»ä½éå¶ãå建æ¹å¼ï¼ å¤å¶ä»£ç 代ç å¦ä¸: create index idx_name on user( name(20) ); mysqlæ¯æåç¼ç´¢å¼ï¼ä¸è¬å§åä¸ä¼è¶
è¿20个å符ï¼æ以æ们è¿é建ç«ç´¢å¼çæ¶åéå®äºé¿åº¦20ï¼è¿æ ·å¯ä»¥èçç´¢å¼æä»¶å¤§å° (3)å¯ä¸ç´¢å¼ å®ä¸åé¢çæ®éç´¢å¼ç±»ä¼¼ï¼ä¸åçå°±æ¯ï¼ç´¢å¼åçå¼å¿
é¡»å¯ä¸ï¼ä½å
许æ空å¼ãå¦ææ¯ç»åç´¢å¼ï¼ååå¼çç»åå¿
é¡»å¯ä¸ãå建æ¹å¼ï¼ å¤å¶ä»£ç 代ç å¦ä¸: CREATE UNIQUE INDEX idx_email ON user( email ); (4)å
¨æç´¢å¼ MySQLæ¯æå
¨æç´¢å¼åæç´¢åè½ãMySQLä¸çå
¨æç´¢å¼ç±»å为FULLTEXTçç´¢å¼ã FULLTEXT ç´¢å¼ä»
å¯ç¨äº MyISAMè¡¨ï¼ å¤å¶ä»£ç 代ç å¦ä¸: CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) ); mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database'); æ¥è¯¢ç»æï¼ +----+-------------------+------------------------------------------+ | id | title | body | +----+-------------------+------------------------------------------+ | 5 | MySQL vs. YourSQL | In the following database comparison ... | | 1 | MySQL Tutorial | DBMS stands for DataBase ... | +----+-------------------+------------------------------------------+ 2 rows in set (0.00 sec) MATCH()å½æ°å¯¹äºä¸ä¸ªå符串æ§è¡èµæåºå
çèªç¶è¯è¨æç´¢ãä¸ä¸ªèµæåºå°±æ¯1å¥1个æ2个å
å«å¨FULLTEXTå
çåãæç´¢å符串ä½ä¸ºå¯¹AGAINST()çåæ°è被ç»å®ã对äºè¡¨ä¸çæ¯ä¸è¡, MATCH() è¿åä¸ä¸ªç¸å
³å¼ï¼å³, æç´¢å符串å MATCH()表ä¸æå®åä¸è¯¥è¡æåä¹é´çä¸ä¸ªç¸ä¼¼æ§åº¦éã (5)å¤åç´¢å¼ å¤å¶ä»£ç 代ç å¦ä¸: CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) ); nameç´¢å¼æ¯ä¸ä¸ªå¯¹last_nameåfirst_nameçç´¢å¼ãç´¢å¼å¯ä»¥ç¨äºä¸ºlast_nameï¼æè
为last_nameåfirst_nameå¨å·²ç¥èå´å
æå®å¼çæ¥è¯¢ãå æ¤ï¼nameç´¢å¼ç¨äºä¸é¢çæ¥è¯¢ï¼ SELECT * FROM test WHERE last_name='Widenius'; SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael'; ä½æ¯ä¸è½ç¨äºSELECT * FROM test WHERE first_name='Michael';è¿æ¯å 为MySQLç»åç´¢å¼ä¸ºâæå·¦åç¼âçç»æ,ç®åçç解就æ¯åªä»æå·¦é¢çå¼å§ç»åã 3.å¨ä»ä¹æ
åµä¸ä½¿ç¨ç´¢å¼ (1)为æç´¢å段建索å¼,å¦æå¨ä½ ç表ä¸ï¼æ个åæ®µä½ ç»å¸¸ç¨æ¥åæç´¢ï¼é£ä¹ï¼è¯·ä¸ºå
¶å»ºç«ç´¢å¼å§ãä¸è¬æ¥è¯´ï¼å¨WHEREåJOINä¸åºç°çåéè¦å»ºç«ç´¢å¼ä»¥æé«æ¥è¯¢é度ã ä¾å¦ä»fps表ï¼è¡¨ä¸ænameå段ï¼ä¸æ£ç´¢å§å为"ææ¦"çäººï¼ ä¸é¢ç¨explainæ¥è§£éæ§è¡å»ºç«ç´¢å¼åæªå»ºç«ç´¢å¼çåºå«ï¼ a.æªå»ºç«ç´¢å¼å å¤å¶ä»£ç 代ç å¦ä¸: explain select name from fps where name="ææ¦"; [SQL] select name from fps where name="ææ¦"; å½±åçæ°æ®æ : 0 æ¶é´: 0.003ms b.建ç«ç´¢å¼å å¤å¶ä»£ç 代ç å¦ä¸: create index idx_name on fps( name ); explain select name from fps where name="ææ¦"; [SQL] select name from fps where name="ææ¦"; å½±åçæ°æ®æ : 0 æ¶é´: 0.001ms (2)ä¸é¢æ们就æ¥ççè¿ä¸ªEXPLAINåæç»æçå«ä¹ã tableï¼è¿æ¯è¡¨çååã typeï¼è¿æ¥æä½çç±»åãä¸é¢æ¯MySQLææ¡£å
³äºrefè¿æ¥ç±»åç说æï¼ â对äºæ¯ä¸ªæ¥èªäºåé¢ç表çè¡ç»åï¼æææå¹é
ç´¢å¼å¼çè¡å°ä»è¿å¼ 表ä¸è¯»åãå¦æèæ¥åªä½¿ç¨é®çæ左边çåç¼ï¼æå¦æé®ä¸æ¯ UNIQUEæPRIMARY KEYï¼æ¢å¥è¯è¯´ï¼å¦æèæ¥ä¸è½åºäºå
³é®åéæ©å个è¡çè¯ï¼ï¼å使ç¨refãå¦æ使ç¨çé®ä»
ä»
å¹é
å°éè¡ï¼è¯¥èæ¥ ç±»åæ¯ä¸éçãâ å¨æ¬ä¾ä¸ï¼ç±äºç´¢å¼ä¸æ¯UNIQUEç±»åï¼refæ¯æ们è½å¤å¾å°çæ好è¿æ¥ç±»åã å¦æEXPLAINæ¾ç¤ºè¿æ¥ç±»åæ¯âALLâï¼èä¸ä½ 并ä¸æ³ä»è¡¨éé¢éæ©åºå¤§å¤æ°è®°å½ï¼é£ä¹MySQLçæä½æçå°é常ä½ï¼å 为å®è¦æ«ææ´ä¸ªè¡¨ãä½ å¯ä»¥å å
¥æ´å¤çç´¢å¼æ¥è§£å³è¿ä¸ªé®é¢ãé¢ç¥æ´å¤ä¿¡æ¯ï¼è¯·åè§MySQLçæå说æã possible_keysï¼ å¯è½å¯ä»¥å©ç¨çç´¢å¼çååãè¿éçç´¢å¼ååæ¯å建索å¼æ¶æå®çç´¢å¼æµç§°ï¼å¦æç´¢å¼æ²¡ææµç§°ï¼åé»è®¤æ¾ç¤ºçæ¯ç´¢å¼ä¸ç¬¬ä¸ä¸ªåçåå ï¼å¨æ¬ä¾ä¸ï¼å®æ¯âidx_nameâï¼ã Keyï¼ å®æ¾ç¤ºäºMySQLå®é
使ç¨çç´¢å¼çååãå¦æå®ä¸ºç©ºï¼æNULLï¼ï¼åMySQLä¸ä½¿ç¨ç´¢å¼ã key_lenï¼ ç´¢å¼ä¸è¢«ä½¿ç¨é¨åçé¿åº¦ï¼ä»¥åè计ã refï¼ å®æ¾ç¤ºçæ¯åçååï¼æåè¯âconstâï¼ï¼MySQLå°æ ¹æ®è¿äºåæ¥éæ©è¡ãå¨æ¬ä¾ä¸ï¼MySQLæ ¹æ®ä¸ä¸ªå¸¸ééæ©è¡ã rowsï¼ MySQLæ认为çå®å¨æ¾å°æ£ç¡®çç»æä¹åå¿
é¡»æ«æçè®°å½æ°ãæ¾ç¶ï¼è¿éæçæ³çæ°åå°±æ¯1ã æ¬ä¾ä¸æªç´¢å¼åéåçè®°å½æ°ä¸º1041ï¼è建ç«ç´¢å¼å为1 Extraï¼ è¿éå¯è½åºç°è®¸å¤ä¸åçé项ï¼å
¶ä¸å¤§å¤æ°å°å¯¹æ¥è¯¢äº§çè´é¢å½±åãå¨æ¬ä¾ä¸ï¼MySQLåªæ¯æéæ们å®å°ç¨using whereï¼using indexåå¥éå¶æç´¢ç»æéã 4.æ常ç¨çåå¨å¼æï¼ (1)Myisamåå¨å¼æï¼æ¯ä¸ªMyisamå¨ç£çä¸åå¨æä¸ä¸ªæ件ãæ件åé½å表åç¸åï¼æ©å±ååå«ä¸º.frmï¼åå¨è¡¨å®ä¹ï¼ã.MYDï¼åå¨æ°æ®ï¼ã.MYIï¼åå¨ç´¢å¼ï¼ãæ°æ®æ件åç´¢å¼æ件å¯ä»¥æ¾ç½®å¨ä¸åç®å½ï¼å¹³ååå¸ioï¼è·å¾æ´å¿«çé度ã对åå¨å¤§å°æ²¡æéå¶ï¼MySQLæ°æ®åºçæ大ææ表尺寸é常æ¯ç±æä½ç³»ç»å¯¹æ件大å°çéå¶å³å®ç, (2)InnoDBåå¨å¼æï¼å
·ææ交ãåæ»ãå¥æºæ¢å¤è½åçäºå¡å®å
¨ãä¸Myisamç¸æ¯ï¼InnoDBçåæçå·®ä¸äºå¹¶ä¸ä¼å ç¨æ´å¤çç£ç空é´ä»¥ä¿çæ°æ®åç´¢å¼ã (3)å¦ä½éæ©åéçå¼æ ä¸é¢æ¯å¸¸ç¨åå¨å¼æéç¨çç¯å¢ï¼ Myisamï¼å®æ¯å¨Webãæ°æ®ä»å¨åå
¶ä»åºç¨ç¯å¢ä¸æ常使ç¨çåå¨å¼æ; InnoDBï¼ç¨äºäºå¡å¤çåºç¨ç¨åºï¼å
·ææ´å¤ç¹æ§ï¼å
æ¬ACIDäºå¡ç¹æ§ã
温馨提示:答案为网友推荐,仅供参考