1. å½åè§èï¼
- åºåã表åãå段åå¿ é¡»ä½¿ç¨å°ååæ¯ï¼å¹¶éç¨ä¸å线åå²ã[FAQ-1-01]
- åºåã表åãå段åç¦â½è¶ è¿ 32 个å符ã[FAQ-1-02]
- åºåã表åãå段åå¿ é¡»è§åç¥æãå½åä¸ä¸å¡ã产å线çç¸å ³èã
- åºåã表åãå段åç¦æ¢ä½¿ç¨ MySQL ä¿çåã[FAQ-1-03] ãMySQL ä¿çåã
- 临æ¶åºã表åå¿ é¡»ä»¥ tmp 为åç¼ï¼å¹¶ä»¥æ¥æ为åç¼ãä¾å¦ tmp_test01_20180704ã
- å¤ä»½åºã表åå¿ é¡»ä»¥ bak 为åç¼ï¼å¹¶ä»¥æ¥æ为åç¼ãä¾å¦ bak_test01_20180704ã
2. åºç¡è§èï¼
- 使⽤ InnoDB åå¨å¼æã[FAQ-2-01]
- åºè¡¨å符éé»è®¤ä½¿â½¤ UTF8MB4ï¼ç¹æ®æ åµå¯ä»¥ä½¿ç¨ UTF8 çå ¶ä»å符éã[FAQ-2-02]
- ææ表é½éè¦æ·»å 注éï¼é¤ä¸»é®å¤çå ¶ä»å段é½éè¦å¢å 注éãæ¨èé⽤è±ææ ç¹ï¼é¿å åºç°ä¹±ç ã
- ç¦â½å¨æ°æ®åºä¸åå¨å¾çãæ件ç大æ°æ®ã
- æ¯å¼ 表æ°æ®é建议æ§å¶å¨ 5000W 以å ã
- ç¦â½å¨çº¿ä¸åæ°æ®åºååæµè¯ã
- ç¦æ¢ä»æµè¯ãå¼åç¯å¢ç´è¿çº¿ä¸æ°æ®åºã
3. åºè¡¨è®¾è®¡ï¼
- ç¦â½ä½¿ç¨ååºè¡¨ã[FAQ-3-01]
- å°â¼¤å段ã访é®é¢çä½çå段æåå°åç¬ç表ä¸åå¨ï¼å离å·çæ°æ®ã[FAQ-3-02]
- æ¨è使⽤ HASH è¿è¡æ表ï¼è¡¨ååç¼ä½¿ç¨â¼è¿å¶æ°ï¼æ°åå¿ é¡»ä» 0 å¼å§ã
- æâ½ææ¶é´å表é符å YYYY[MM][DD][HH] æ ¼å¼ï¼ä¾å¦ 2018071601ãå¹´ä»½å¿ é¡»ç¨ 4 ä½æ°å表示ãä¾å¦ææ¥æ£è¡¨ user_20180709ãæææ£è¡¨ user_201807ã
- éç¨åéçååºå表çç¥ãä¾å¦ååºå表ãâ¼åºç¾è¡¨çã[FAQ-3-03]
4. å段设计ï¼
- å»ºè®®ä½¿ç¨ UNSIGNED åå¨éè´æ°å¼ã[FAQ-4-01]
- å»ºè®®ä½¿ç¨ INT UNSIGNED åå¨ IPV4ã[FAQ-4-02]
- ⽤ DECIMAL ä»£æ¿ FLOAT å DOUBLE åå¨ç²¾ç¡®æµ®ç¹æ°ãä¾å¦ä¸è´§å¸ãéèç¸å ³çæ°æ®ã
- INT ç±»ååºå®å ç¨ 4 åèåå¨ï¼ä¾å¦ INT(4) ä» ä»£è¡¨æ¾â½°å符宽度为 4 ä½ï¼ä¸ä»£è¡¨åå¨é¿åº¦ã[FAQ-4-03]
- åºåä½¿ç¨ TINYINTãSMALLINTãMEDIUMINTãINTãBIGINT æ°æ®ç±»åãä¾å¦åå¼èå´ä¸º 0 -80 æ¶ï¼ä½¿ç¨ TINYINT UNSIGNEDããæ°æ®ç±»ååå¨ç©ºé´éæ±è¯´æã
- 强çå»ºè®®ä½¿ç¨ TINYINT æ¥ä»£æ¿ ENUM ç±»åã[FAQ-4-04]
- ç¦æ¢ä½¿ç¨ TEXTãBLOB ç±»åã
- ç¦â½å¨æ°æ®åºä¸åå¨ææå¯ç ã[FAQ-4-05]
- ä½¿ç¨ VARBINARY åå¨â¼¤å°åææçåâ»å符串æäºè¿å¶å 容ã[FAQ-4-06]
- 使⽤尽å¯è½â¼©ç VARCHAR å段ãVARCHAR(N) ä¸ç N 表示å符æ°èéåèæ°ã
- åºå使⽤ DATETIME å TIMESTAMPãåå¨å¹´ä½¿ç¨ YEAR ç±»åãåå¨æ¥æä½¿ç¨ DATE ç±»åãåå¨æ¶é´ï¼ç²¾ç¡®å°ç§çè³å¾®å¦ï¼å»ºè®®ä½¿ç¨ TIMESTAMP ç±»åã[FAQ-4-07]
- DATETIME å TIMESTAMP ç±»åå段ç¦æ¢ä½¿ç¨ â0000-00-00 00:00:00â ä½ä¸º DEFAULE å¼ã
- ææå段åå®ä¹ä¸º NOT NULLã[FAQ-4-08]
InnoDB åå¨å段补å 说æ
5. ç´¢å¼è§èï¼
- åå¼ è¡¨ä¸ç´¢å¼æ°éä¸è¶ è¿ 5 个ã
- å个索å¼ä¸çå段æ°ä¸è¶ è¿ 5 个ã
- ç´¢å¼åå¿ é¡»å ¨é¨ä½¿ç¨â¼©åã
-
â¾®å¯â¼ç´¢å¼æç §âidx_å段å称 [å段å称]âè¿â¾å½åãä¾å¦ idx_age_nameã
å¯â¼ç´¢å¼æç §âuniq å段å称 [_å段å称]âè¿è¡å½åãä¾å¦ uniq_age_nameã
- ç»åç´¢å¼å»ºè®®å å«ææå段åï¼è¿â»çå段åå¯ä»¥é⽤缩åå½¢å¼ãä¾å¦ idx_age_name_addã
- è¡¨å¿ é¡»æ主é®ï¼æ¨èä½¿ç¨ UNSIGNED â¾å¢åä½ä¸ºä¸»é®ã[FAQ-5-01]
- å¯ä¸é®ç± 3 个以ä¸å段ç»æï¼å¹¶ä¸å段é½æ¯æ´åæ¶ï¼å¯ä½¿â½¤å¯â¼é®ä½ä¸ºä¸»é®ãå ¶ä»æ åµä¸ï¼å»ºè®®ä½¿â½¤â¾å¢åæåå·å¨ä½ä¸»é®ã
- ç¦â½åä½ç´¢å¼ã[FAQ-5-02]
- ç¦æ¢éå¤ç´¢å¼ã[FAQ-5-03]
- ç¦æ¢ä½¿â½¤å¤é®ã
- è表æ¥è¯¢æ¶ï¼JOIN åçæ°æ®ç±»åå¿ é¡»ç¸åï¼å¹¶ä¸è¦å»ºç«ç´¢å¼ã
- ä¸å¨ä½åºæ°åä¸å»ºç«ç´¢å¼ï¼ä¾å¦âæ§å«âã[FAQ-5-04]
- éæ©åºå度⼤çå建ç«ç´¢å¼ãç»åç´¢å¼ä¸ï¼åºå度⼤çå段æ¾å¨æåã
- 对å符串使ç¨åç¼ç´¢å¼ï¼åç¼ç´¢å¼é¿åº¦ä¸è¶ è¿ 8 个å符ã
- ä¸å¯¹è¿â»ç VARCHAR å段建⽴索å¼ã建议ä¼å èèåç¼ç´¢å¼ï¼ææ·»å CRC32 æ MD5 伪å并建⽴索å¼ã
- åçå建èåç´¢å¼ï¼(aï¼bï¼c) ç¸å½äº (a)ã(aï¼b)ã(aï¼bï¼c)ã
- åç使ç¨è¦çç´¢å¼åå° IOï¼é¿å æåºã[FAQ-5-05]
6. SQL设计è§èï¼
- 使⽤ prepared statementï¼å¯ä»¥æåæ§è½å¹¶é¿å SQL æ³¨å ¥ã
- ⽤ IN ä»£æ¿ ORãSQL è¯å¥ä¸ IN å å«çå¼ä¸åºè¿å¤ï¼åºå°äº 1000 个ã[FAQ-6-01]
- ç¦â½éå¼è½¬æ¢ãæ°å¼ç±»åç¦æ¢å å¼å·ï¼å符串类åå¿ é¡»å å¼å·ã
- é¿å ä½¿ç¨ JOIN å⼦æ¥è¯¢ãå¿ è¦æ¶æ¨èç¨ JOIN 代æ¿åæ¥è¯¢ã
- é¿å å¨ MySQL ä¸è¿â¾æ°å¦è¿ç®åå½æ°è¿ç®ã
- åå°ä¸æ°æ®åºäº¤äºæ¬¡æ°ï¼å°½ééç¨æ¹é SQL è¯å¥ã[FAQ-6-02]
- æåå¤æ SQL 为å¤ä¸ªå° SQLï¼é¿å ⼤äºå¡ã[FAQ-6-03]
- è·å⼤éæ°æ®æ¶ï¼å»ºè®®åæ¹æ¬¡è·åæ°æ®ï¼æ¯æ¬¡è·åæ°æ®å°äº 5000 æ¡ï¼ç»æéåºâ¼©äº 1Mã
- ⽤ UNION ALL ä»£æ¿ UNIONã[FAQ-6-04]
- ç»è®¡â¾æ°ç¨ COUNT(*)ã
- SELECT åªè·åå¿ è¦çå段ï¼ç¦â½ä½¿ç¨ SELECT *ã[FAQ-6-05]
- SQL ä¸é¿å åºç° now()ãrand()ãsysdate()ãcurrent_user() çä¸ç¡®å®ç»æçå½æ°ã[FAQ-6-06]
- INSERT è¯å¥å¿ é¡»æå®å段å表ï¼ç¦â½ä½¿ç¨ INSERT INTO TABLE VAULES()ã
- ç¦â½åæ¡ SQL è¯å¥åæ¶æ´æ°å¤ä¸ªè¡¨ã
- é¿å 使⽤åå¨è¿ç¨ã触åå¨ãè§å¾ãâ¾å®ä¹å½æ°çã[FAQ-6-07]
- 建议使ç¨åççå页æ¹å¼ä»¥æâ¾¼å页æçã[FAQ-6-08]
- ç¦æ¢å¨ä¸»åºä¸æ§â¾åå°ç®¡çåç»è®¡ç±»åè½ç QUERYï¼å¿ è¦æ¶ç³è¯·ç»è®¡ç±»ä»åºã
- ç¨åºåºææè· SQL å¼å¸¸çå¤çæºå¶ï¼å¿ è¦æ¶éè¿ rollback æ¾å¼åæ»ã
- éè¦ SQL å¿ é¡»è¢«ç´¢å¼ï¼updateãdelete ç where æ¡ä»¶åãorder byãgroup byãdistinct å段ãå¤è¡¨ join å段ã
- ç¦æ¢ä½¿ç¨ % å导æ¥è¯¢ï¼ä¾å¦ï¼like â%abcâï¼â½æ³å©â½¤å°ç´¢å¼ã
- ç¦æ¢ä½¿â½¤è´åæ¥è¯¢ï¼ä¾å¦ï¼not inã!=ã<>ãnot likeã
- 使⽤ EXPLAIN å¤æ SQL è¯å¥æ¯å¦åç使ç¨ç´¢å¼ï¼å°½éé¿å extra ååºç°ï¼Using File SortãUsing Temporary çã
- ç¦â½ä½¿â½¤ order by rand()ã[FAQ-6-09]
- ç¦æ¢ä½¿ç¨ INSERT INTO SELECT å UPDATE a,b çå¤è¡¨å ³èç DML è¯å¥æ ¼å¼ã
- ç¦æ¢ä½¿ç¨ SELECT LOCK IN SHARE MODE å SELECT FOR UPDATE çé¢å å ç¨éèµæºçè¯å¥æ ¼å¼ã
7. æä½è¡ä¸ºè§èï¼
- 表ç»æåæ´å¿ é¡»éç¥ DBA è¿â¾å®¡æ ¸ã
- ç¦â½æ SUPER æéçåºâ½¤ç¨åºè´¦å·åå¨ã[FAQ-7-01]
- ç¦â½æ DDLãDCL æéçåºâ½¤ç¨åºè´¦å·åå¨ã
- é⼤项⽬çæ°æ®åºâ½ æ¡éååè®¾è®¡å¿ é¡»æåéç¥ DBA åä¸ã
- æ¹é导â¼ã导åºæ°æ®å¿ é¡»éè¿ DBA å®¡æ ¸ï¼å¹¶å¨æ§è¡è¿ç¨ä¸è§å¯æå¡ã
- æ¹éæ´æ°æ°æ®ï¼å¦ UPDATEãDELETE æä½ï¼å¿ é¡» DBA è¿â¾å®¡æ ¸ï¼å¹¶å¨æ§â¾è¿ç¨ä¸è§å¯æå¡ã
- 产ååºç°éæ°æ®åºå¯¼è´çæ éæ¶ï¼å¦è¢«æ»å»ï¼å¿ é¡»åæ¶é DBAï¼ä¾¿äºç»´æ¤æå¡ç¨³å®ã
- ä¸å¡é¨â»ç¨åºåºç° BUG çå½±åæ°æ®åºæå¡çé®é¢ï¼å¿ é¡»åæ¶éç¥ DBAï¼ä¾¿äºç»´æ¤æå¡ç¨³å®ã
- ä¸å¡é¨â»æ¨â¼´æ´»å¨æä¸çº¿æ°åè½ï¼å¿ é¡»æåéç¥ DBA è¿è¡æå¡å访é®éè¯ä¼°ï¼å¹¶çåºå¿ è¦æ¶é´ä»¥ä¾¿ DBA å®ææ©å®¹ã
- åºç°ä¸å¡é¨é¨â¼ä¸ºè¯¯æä½å¯¼è´æ°æ®ä¸¢å¤±ï¼éè¦æ¢å¤æ°æ®çï¼å¿ 须第ä¸æ¶é´éç¥ DBAï¼å¹¶æä¾åç¡®æ¶é´ç¹ã误æä½è¯å¥çéè¦çº¿ç´¢ã
- æ交线ä¸å»ºè¡¨æ¹è¡¨éæ±ï¼å¿ 须详ç»æ³¨ææ¶åå°çææ SQL è¯å¥ (å æ¬ SELECTãINSERTãDELETEãUPDATE)ï¼ä¾¿äº DBA è¿è¡å®¡æ ¸åä¼åã[FAQ-7-02]
- 对åâ¼ä¸ªè¡¨çå¤æ¬¡ ALTER æä½å¿ é¡»å并为â¼æ¬¡æä½ã[FAQ-7-03]
- ä¸è¦å¨ MySQL æ°æ®åºä¸åæ¾ä¸å¡é»è¾ã[FAQ-7-04]
8. æ°æ®åºè´¦å·è§èï¼
- ä¸å¡ç¨åºå¿ é¡»å®ç°è¯»åå离ï¼è¯»åè´¦å·ä»¥åç¼ â_wâ å½åï¼åªè¯»è´¦å·ä»¥åç¼ â_râ å½åã
- ç¨åºè´¦å·æééµå¾ªæå°åææååï¼åªè½æ¯ SELECTãINSERTãUPDATEãDELETEï¼é¤éç¹æ®æ åµå¯ä»¥æäº CREATE å DROP æéã
- å¼å以åå ¶ä»ä¸å¡äººåçè´¦å·ï¼ä¾å¦ zhangsan01ï¼æé为 SELECT ä¸åªå 许访é®ç³è¯·çåºè¡¨ã
- ä¸åå端平å°ä»¥åè¿ç»´ç¨åºï¼éè¦ä½¿ç¨ä¸åçæ°æ®åºè´¦å·ï¼ä¾å¦å¤ä»½ç¨åºéç¨ mysqlbackup å½åï¼ä¸»ä»åæ¥è´¦å·éç¨ replication å½åçã
- æ°æ®åºææéç¨ç½åå IP æ¹å¼ï¼é¤éç¹æ®æ åµå¦åä¸è½æäºå¸¦ % ç IP èå´ã
- æ°æ®åºææç²åº¦ä¸ºåºçº§å«ï¼é¤éç¹æ®æ åµå¯ä»¥ææå°è¡¨çº§å«ï¼MySQL çç³»ç»åºå¦ mysqlãsys çä¸å¯¹å¤ææã
- æ°æ®åºä¸ä¸å 许æåå为 âdbaâ çè´¦å·ï¼åå§åæ°æ®åºåéè¦å°è¯¥ç¨æ·å é¤ã
- æææ°æ®åºè´¦å·çå¯ç åä¸è½ä¸ºç©ºï¼å¯ç éç¨ 16 ä½å¤§å°ååæ¯åæ°åçç»åã
- æ°æ®åºè´¦å· / å¯ç ä¸å 许å¨é®ä»¶ãä¼ä¸å¾®ä¿¡ç¾¤ã家信群ä¸å ¬å¼ï¼ä¸ç»åç°ï¼DBA ææç«å³è¿è¡éç½®ã
- ç¸åæ°æ®åºè´¦å·çæéå¿ é¡»ä¿æä¸è´ï¼ä¸»ä»æ°æ®åºçè´¦å·ææä¿æä¸è´ã
9. FAQ åéå½ï¼
ãFAQ-1-01ã
åºåã表åãå段åå¿ é¡»ä½¿â½¤å°ååæ¯ï¼å¹¶é⽤ä¸å线åå²ã
a)MySQL æé ç½®åæ° lower_case_table_namesï¼ä¸å¯å¨ææ´æ¹ï¼linux ç³»ç»é»è®¤ä¸º 0ï¼å³åºè¡¨å以å®é æ åµåå¨ï¼â¼¤å°åææãå¦ææ¯ 1ï¼ä»¥â¼©ååå¨ï¼â¼¤å°åä¸ææãå¦ææ¯ 2ï¼ä»¥å®é æ åµåå¨ï¼ä½ä»¥å°åâ½è¾ã
b) å¦æ⼤å°åæ··å使ç¨ï¼å¯è½åå¨ abcï¼Abcï¼ABC çå¤ä¸ªè¡¨å ±åï¼å®¹æ导è´æ··ä¹±ã
c) å段åæ¾â½°åºå⼤⼩åï¼ä½å®é 使ç¨ä¸åºåï¼å³ä¸å¯ä»¥å»ºç«ä¸¤ä¸ªååâ¼æ ·ä½å¤§å°åä¸ä¸æ ·çå段ã
d) 为äºç»â¼è§èï¼åºåã表åãå段å使⽤⼩ååæ¯ã
ãFAQ-1-02ã
åºåã表åãå段åç¦æ¢è¶ è¿ 32 个å符ã
åºåã表åãå段åâ½ææå¤ 64 个å符ï¼ä½ä¸ºäºç»ä¸è§èãæäºè¾¨è¯ä»¥ååå°ä¼ è¾éï¼ç¦â½è¶ è¿ 32 个å符ã
ãFAQ-1-03ã
åºåã表åãå段åç¦æ¢ä½¿ç¨ MySQL ä¿çåã å½åºåã表åãå段åçå±æ§å«æä¿çåæ¶ï¼SQL è¯å¥å¿ 须⽤åå¼å·å¼ç¨å±æ§å称ï¼è¿å°ä½¿å¾ SQL è¯å¥ä¹¦åãSHELL èæ¬ä¸åéç转ä¹çåå¾é常å¤æã
ãFAQ-2-01ã
ä½¿â½¤ç¨ InnoDB åå¨å¼æãInnoDB å¼ææ¯ MySQL5.5 çæ¬ä»¥åçé»è®¤å¼æï¼â½æäºå¡ãè¡çº§éï¼ææ´å¥½çæ°æ®æ¢å¤è½åãæ´å¥½ç并åæ§è½ï¼åæ¶å¯¹å¤æ ¸ã⼤å åãSSD ç硬件â½ææ´å¥½ï¼â½ææ°æ®çå¤ä»½çï¼å æ¤ InnoDB ç¸æ¯ MyISAM æææ¾ä¼å¿ã
ãFAQ-2-02ã
åºè¡¨å符éä½¿ç¨ UTF8MB4ï¼ç¹æ®æ åµå¯ä»¥ä½¿ç¨ UTF8 çå ¶ä»å符éã
a)UTF8 å符éåå¨æ±åå ç¨ 3 个åèï¼UTF8MB4 å符éåå¨æ±åå ç¨ 4 个åèï¼åå¨è±â½å符ååªå ⽤ä¸ä¸ªåèã
b)UTF8 å UTF8MB4 ç»ä¸èä¸éç¨ï¼ä¸ä¼åºç°è½¬ç åºç°ä¹±ç é£é©ã
c)EMOJ ç表æ 符å·çåå¨éæ±ï¼åªè½ä½¿ç¨ UTF8MB4 å符éã
d) ä¸åå符éçåæ®µå ³èæ¶æ æ³ä½¿ç¨ç´¢å¼ã
ãFAQ-3-01ã
ç¦â½ä½¿â½¤ååºè¡¨ãååºè¡¨å¯¹ååºé®æä¸¥æ ¼è¦æ±ï¼ååºè¡¨å¨è¡¨å大åï¼æ§â¾è¡ DDLãSHARDINGãå表æ¢å¤çé½åå¾æ´å å°é¾ãå æ¤ç¦æ¢ä½¿â½¤ååºè¡¨ï¼å¹¶å»ºè®®ä¸å¡ç«¯æå¨ SHARDINGã
ãFAQ-3-02ã
å°â¼¤å段ã访é®é¢çä½çå段æåå°åç¬ç表ä¸åå¨ï¼å离å·çæ°æ®ãæå©äºææå©ç¨ç¼åï¼é²æ¢è¯»å ¥â½ç¨çå·æ°æ®ï¼è¾å°ç£ç IOï¼åæ¶ä¿è¯çæ°æ®å¸¸é©»å åæé«ç¼åå½ä¸çã
ãFAQ-3-03ã
é⽤åéçååºå表çç¥ãä¾å¦ååºå表ãâ¼åºç¾è¡¨çãéç¨åéçååºå表çç¥ï¼æå©äºä¸å¡åå±åæå¿«é对æ°æ®åºè¿â¾â½å¹³æåï¼åæ¶ååºå¯ä»¥ææå©ç¨ MySQL çå¤çº¿ç¨å¹¶è¡å¤å¶ç¹æ§ã
ãFAQ-4-01ã
å»ºè®®ä½¿ç¨ UNSIGNED åå¨â¾®è´æ°å¼ãåæ ·çåèæ°ï¼â¾®è´åå¨çæ°å¼èå´æ´å¤§ãå¦ TINYINT æ符å·ä¸º -128~127ï¼æ 符å·ä¸º 0~255ã
ãFAQ-4-02ã
å»ºè®®ä½¿ç¨ INT UNSIGNED åå¨ IPV4ã ⽤ UNSINGED INT åå¨ IP å°åå ç¨ 4 åèï¼CHAR(15) åå ç¨ 15 åèãå¦å¤ï¼è®¡ç®æºå¤çæ´æ°ç±»åæ¯å符串类åå¿«ãä½¿ç¨ INT UNSIGNED â½½ä¸æ¯ CHAR(15) æ¥åå¨ IPV4 å°åï¼éè¿ MySQL å½æ° inet_ntoa å inet_aton æ¥è¿è¡è½¬åãIPv6 å°å⽬å没æ转åå½æ°ï¼éè¦ä½¿ç¨ DECIMAL æ两个 BIGINT æ¥åå¨ãä¾å¦:
Shell
SELECT INET_ATON('209.207.224.40'); 3520061480 SELECT INET_NTOA(3520061480); 209.207.224.40
12 | SELECT INET_ATON('209.207.224.40'); 3520061480SELECT INET_NTOA(3520061480); 209.207.224.40 |
---|
ãFAQ-4-03ã
INT ç±»ååºå®å ç¨ 4 åèåå¨ï¼ä¾å¦ INT(4)ä» ä»£è¡¨æ¾ç¤ºå符宽度为 4 ä½ï¼ä¸ä»£è¡¨åå¨é¿åº¦ãæ°å¼ç±»åæ¬å·åé¢çæ°ååªæ¯è¡¨ç¤ºå®½åº¦èè·åå¨èå´æ²¡æå ³ç³»ï¼æ¯å¦ INT(3)é»è®¤æ¾ç¤º 3 ä½ï¼ç©ºæ ¼è¡¥é½ï¼è¶ åºæ¶æ£å¸¸æ¾ç¤ºãpythonãjava ç客æ·ç«¯ä¸å ·å¤è¿ä¸ªåè½ã
ãFAQ-4-04ã
强çå»ºè®®ä½¿ç¨ TINYINT æ¥ä»£æ¿ ENUM ç±»åãENUM ç±»åå¨éè¦ä¿®æ¹æå¢å æ举å¼æ¶ï¼éè¦å¨çº¿ DDLï¼ææ¬è¾é«ï¼ENUM åå¼å¦æå«ææ°åç±»åï¼å¯è½ä¼å¼èµ·é»è®¤å¼æ··æ·ããENUM ç±»å说æã
ãFAQ-4-05ã
ç¦â½å¨æ°æ®åºä¸åå¨æâ½å¯ç ãéç¨å å¯å符串åå¨å¯ç ï¼å¹¶ä¿è¯å¯ç ä¸å¯è§£å¯ï¼åæ¶éç¨éæºå符串å å¯ä¿è¯å¯ç å®å ¨ãé²â½æ°æ®åºæ°æ®è¢«å ¬å¸å é¨äººåæé»å®¢è·ååï¼éç¨åå ¸æ»å»çæ¹å¼æ´â¼ç ´è§£ç¨æ·å¯ç ã
ãFAQ-4-06ã
ä½¿â½¤ç¨ VARBINARY åå¨â¼¤å°åææçåâ»å符串æäºè¿å¶å 容ãVARBINARY é»è®¤åºå⼤å°åï¼æ²¡æå符éæ¦å¿µï¼é度快ã
ãFAQ-4-07ã
åºå使⽤ DATETIME å TIMESTAMPãåå¨å¹´ä½¿â½¤ YEAR ç±»åãåå¨â½æä½¿â½¤ç¨ DATE ç±»åãåå¨æ¶é´ (精确å°ç§çè³å¾®å¦) å»ºè®®ä½¿ç¨ TIMESTAMP ç±»åã
DATETIME å TIMESTAMP é½å¯ä»¥ç²¾ç¡®å°ç§ï¼ä¼å éæ© TIMESTAMPï¼å¯ç²¾ç¡®å°å¾®å¦ï¼ï¼å 为 TIMESTAMP åªæ 4 个åèï¼â½½ DATETIME8 个åèãåæ¶ TIMESTAMP å ·æâ¾å¨èµå¼ä»¥åèªå¨æ´æ°çç¹æ§ããTIMESTAMP å段类ååå§åã
ãFAQ-4-08ã
ææå段åå®ä¹ä¸º NOT NULLã
a)对表çæ¯â¼è¡ï¼æ¯ä¸ªä¸º NULL çåé½éè¦é¢å¤ç空é´æ¥æ è¯ã
b)B+ æ ç´¢å¼ä¸ä¼åå¨ NULL å¼ï¼æ以å¦æç´¢å¼å段å¯ä»¥ä¸º NULLï¼ç´¢å¼æçä¼ä¸éã
c)å»ºè®®ç¨ 0ãç¹æ®å¼æç©ºä¸²ä»£æ¿ NULL å¼ã
ãFAQ-5-01ã
è¡¨å¿ é¡»æ主é®ï¼æ¨è使⽤ UNSIGNED èªå¢åä½ä¸ºä¸»é®ã表没æ主é®ï¼INNODB ä¼é»è®¤è®¾ç½®éèç主é®åï¼æ²¡æ主é®ç表å¨å®ä½æ°æ®è¡çæ¶åé常å°é¾ï¼ä¹ä¼éä½åºäºè¡å¤å¶çæçã
ãFAQ-5-02ã
ç¦â½åä½ç´¢å¼ãç´¢å¼æ¯åååï¼ä¼å¢å ç»´æ¤è´æ ï¼å¢â¼¤ IO ååã(a,b,c)ã(a,b)ï¼åè 为åä½ç´¢å¼ãå¯ä»¥å©â½¤åç¼ç´¢å¼æ¥è¾¾å°å éç®çï¼å轻维æ¤è´æ ã
ãFAQ-5-03ã
ç¦â½éå¤ç´¢å¼ã primary key a;uniq index a; éå¤ç´¢å¼å¢å ç»´æ¤è´æ ãå ⽤ç£ç空é´ï¼åæ¶æ²¡æä»»ä½çå¤ã
ãFAQ-5-04ã
ä¸å¨ä½åºæ°åä¸å»ºâ½´ç´¢å¼ï¼ä¾å¦âæ§å«âã大é¨ååºæ¯ä¸ï¼ä½åºæ°åä¸å»ºç«ç´¢å¼ç精确æ¥æ¾ï¼ç¸å¯¹äºä¸å»ºâ½´ç´¢å¼çå ¨è¡¨æ«æ没æä»»ä½ä¼å¿ï¼â½½ä¸å¢â¼¤äº IO è´æ ã
ãFAQ-5-05ã
åç使ç¨è¦çç´¢å¼åå° IOï¼é¿å æåºãè¦çç´¢å¼è½ä»ç´¢å¼ä¸è·åéè¦çææå段ï¼ä»â½½é¿å å表è¿è¡â¼æ¬¡æ¥æ¾ï¼èç IOãInnoDB åå¨å¼æä¸ï¼secondary index(⾮主é®ç´¢å¼ï¼åç§°ä¸ºè¾ å©ç´¢å¼ãâ¼çº§ç´¢å¼)没æç´æ¥åå¨è¡å°åï¼â½½æ¯åå¨ä¸»é®å¼ãå¦æ⽤æ·éè¦æ¥è¯¢ secondary index ä¸æä¸å å«çæ°æ®åï¼åéè¦å éè¿ secondary index æ¥æ¾å°ä¸»é®å¼ï¼ç¶ååéè¿ä¸»é®æ¥è¯¢å°å ¶ä»æ°æ®åï¼å æ¤éè¦æ¥è¯¢ä¸¤æ¬¡ãè¦çç´¢å¼åå¯ä»¥å¨ä¸ä¸ªç´¢å¼ä¸è·åææéè¦çæ°æ®ï¼å æ¤æçè¾é«ã主é®æ¥è¯¢æ¯å¤©ç¶çè¦çç´¢å¼ãä¾å¦ SELECT emailï¼uid FROM user_email WHERE uid=xxï¼å¦æ uid ä¸æ¯ä¸»é®ï¼éå½æ¶åå¯ä»¥å°ç´¢å¼æ·»å 为 index(uidï¼email)ï¼ä»¥è·å¾æ§è½æåã
ãFAQ-6-01ã
⽤ IN ä»£æ¿ ORãSQL è¯å¥ä¸ IN å å«çå¼ä¸åºè¿å¤ï¼åºå°äº 1000 个ãIN æ¯èå´æ¥æ¾ï¼MySQL å é¨ä¼å¯¹ IN çå表å¼è¿è¡æåºåæ¥æ¾ï¼â½ OR æçæ´â¾¼ã
ãFAQ-6-02ã
åå°ä¸æ°æ®åºäº¤äºæ¬¡æ°ï¼å°½ééç¨æ¹é SQL è¯å¥ã使⽤ä¸é¢çè¯å¥æ¥åå°å DB ç交äºæ¬¡æ°:
- a)INSERT ⦠ON DUPLICATE KEY UPDATE
- b)REPLACE INTO
- c)INSERT IGNORE
- d)INSERT INTO VALUES()
ãFAQ-6-03ã
æåå¤æ SQL 为å¤ä¸ªå° SQLï¼é¿å 大äºå¡ã ç®åç SQL 容æ使ç¨å° MySQL ç QUERY CACHEï¼åå°é表æ¶é´ç¹å«æ¯ MyISAMï¼å¯ä»¥ä½¿ç¨å¤æ ¸ CPUã
ãFAQ-6-04ã
⽤ UNION ALL ä»£æ¿ UNIONã UNION ALL ä¸éè¦å¯¹ç»æéåè¿è¡æåºã
ãFAQ-6-05ã
SELECT åªè·åå¿ è¦çå段ï¼ç¦â½ä½¿â½¤ SELECT *ã
åå°ç½ç»å¸¦å®½æ¶èï¼è½ææå©ç¨è¦çç´¢å¼ï¼è¡¨ç»æåæ´å¯¹ç¨åºåºæ¬â½å½±åã
ãFAQ-6-06ã
SQL ä¸é¿å åºç° now()ãrand()ãsysdate()ãcurrent_user()çä¸ç¡®å®ç»æçå½æ°ã
è¯å¥çº§å¤å¶åºæ¯ä¸ï¼å¼èµ·ä¸»ä»æ°æ®ä¸ä¸è´ï¼ä¸ç¡®å®å¼çå½æ°ï¼äº§â½£ç SQL è¯å¥â½æ³å©ç¨ QUERY CACHEã
ãFAQ-6-07ã
é¿å 使⽤åå¨è¿ç¨ã触åå¨ãè§å¾ãèªå®ä¹å½æ°çã è¿äºâ¾¼çº§ç¹æ§ææ§è½é®é¢ï¼ä»¥åæªç¥ BUG è¾å¤ãä¸å¡é»è¾æ¾å°æ°æ®åºä¼é ææ°æ®åºç DDLãSCALE OUTãSHARDING çåå¾æ´å å°é¾ã
ãFAQ-6-08ã
建议使ç¨åççå页⽠å¼ä»¥æâ¾¼åâ»æçã åå¦æ类似ä¸â¾¯åâ»è¯å¥:
Shell
SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;
1 | SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10; |
---|
è¿ç§å页æ¹å¼ä¼å¯¼è´â¼¤éç IOï¼å 为 MySQL 使ç¨çæ¯æå读åçç¥ã æ¨èå页⽠å¼:
Shell
SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10; SELECT * FROM table inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000ï¼10) as t USING(id);
12 | SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10;SELECT * FROM table inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000ï¼10) as t USING(id); |
---|
ãFAQ-6-09ã
ç¦æ¢ä½¿â½¤ order by rand()ã
order by rand() ä¼ä¸ºè¡¨å¢å â¼ä¸ªä¼ªåï¼ç¶å⽤ rand() å½æ°ä¸ºæ¯ä¸è¡æ°æ®è®¡ç®åº rand() å¼ï¼ç¶ååºäºè¯¥è¡æåºï¼è¿é常é½ä¼çæç£çä¸ç临æ¶è¡¨ï¼å æ¤æçé常ä½ã建议å ä½¿ç¨ rand() å½æ°è·å¾éæºç主é®å¼ï¼ç¶åéè¿ä¸»é®è·åæ°æ®ã
ãFAQ-7-01ã
ç¦â½æ SUPER æéçåºç¨ç¨åºè´¦å·åå¨ã å®å ¨ç¬¬â¼ãSUPER æéä¼å¯¼è´ read only 失æï¼å¯¼è´è¾å¤è¯¡å¼é®é¢èä¸å¾é¾è¿½è¸ªã
ãFAQ-7-02ã
æ交线ä¸å»ºè¡¨æ¹è¡¨éæ±ï¼å¿ 须详ç»æ³¨ææ¶åå°çææ SQL è¯å¥ (å æ¬ SELECTãINSERTãDELETEãUPDATE)ï¼ä¾¿äº DBA è¿â¾å®¡æ ¸åä¼åã 并ä¸åªæ¯ SELECT è¯å¥éè¦â½¤å°ç´¢å¼ãUPDATEãDELETE é½éè¦å å®ä½å°æ°æ®æè½æ§è¡åæ´ãå æ¤éè¦ä¸å¡æä¾ææç SQL è¯å¥ä¾¿äº DBA å®¡æ ¸ã
ãFAQ-7-03ã
对åâ¼ä¸ªè¡¨çå¤æ¬¡ ALTER æä½å¿ é¡»å并为ä¸æ¬¡æä½ã MySQL 对表çä¿®æ¹ç»å¤§é¨åæä½é½éè¦é表并é建表ï¼èé表åä¼å¯¹çº¿ä¸ä¸å¡é æå½±åã为åå°è¿ç§å½±åï¼å¿ é¡»æ对表çå¤æ¬¡ ALTER æä½å并为â¼æ¬¡æä½ãä¾å¦ï¼è¦ç»è¡¨ t å¢å â¼ä¸ªå段 bï¼åæ¶ç»å·²æçå段 aa 建⽴索å¼ï¼é常çåæ³å为两æ¥:
Shell
alter table t add column b varchar(10); ç¶åå¢å ç´¢å¼: alter table t add index idx_aa(aa);
123 | alter table t add column b varchar(10);ç¶åå¢å ç´¢å¼:alter table t add index idx_aa(aa); |
---|
æ£ç¡®çåæ³æ¯:
Shell
alter table t add column b varchar(10),add index idx_aa(aa);
1 | alter table t add column b varchar(10),add index idx_aa(aa); |
---|
ãFAQ-7-04ã
ä¸è¦å¨MySQLæ°æ®åºä¸åæ¾ä¸å¡é»è¾ãæ°æ®åºæ¯æç¶æçæå¡ï¼åæ´å¤æèä¸éåº¦æ ¢ï¼å¦ææä¸å¡é»è¾æ¾å°æ°æ®åºä¸ï¼å°ä¼éå¶ä¸å¡çå¿«éåå±ã建议æä¸å¡é»è¾æåï¼æ¾å°å端æä¸é´é»è¾å±ï¼â½½ææ°æ®åºä½ä¸ºåå¨å±ï¼å®ç°é»è¾ä¸åå¨çå离ã
éå½1 æ°æ®åºå»ºè¡¨è¯å¥ç¤ºä¾ï¼
Shell
CREATE TABLE `house_showing_house` ( `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT, `showing_record_id` int(10) unsigned NOT NULL COMMENT '带ç表主é®', `house_code` char(15) NOT NULL COMMENT 'æ¿æºSEç¼å·', `agent_code` int(10) unsigned NOT NULL COMMENT 'ç»çºªäººSEç¼å·', `agent_ucid` int(10) unsigned NOT NULL COMMENT 'ç»çºªäººUCç¼å·', `agent_name` varchar(64) NOT NULL COMMENT 'ç»çºªäººå§å', `customer_code` varbinary(16) NOT NULL COMMENT '客æ·ç¼å·, å¦ææçè¯', `customer_ucid` int(10) unsigned NOT NULL COMMENT '客æ·UCç¼å·', `customer_name` varchar(32) NOT NULL COMMENT '客æ·å§å', `hdic_city_id` smallint(5) unsigned NOT NULL COMMENT '楼çåå ¸ åå¸ID', `hdic_resblock_id` int(10) unsigned NOT NULL COMMENT '楼çåå ¸ 楼çID', `see_time` datetime NOT NULL DEFAULT '1990-01-01 00:00:00' COMMENT '带çæ¶é´', `appid` int(11) NOT NULL COMMENT 'ä¿¡æ¯æ¥æº', `app_pkid` bigint(20) NOT NULL COMMENT 'appç主é®', `satisfaction` tinyint(1) unsigned NOT NULL COMMENT '客æ·æ¯å¦æ»¡æ', `reciprocal_frame` tinyint(4) unsigned DEFAULT '0' COMMENT '1ççæ¬æ·åï¼2ççåæ·åï¼3ççæ¬æ¿', `feedback` varchar(512) NOT NULL COMMENT 'åé¦å 容', `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'ç¶æå¼', `audit_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'å®¡æ ¸ç¶æï¼0å¾ å®¡ï¼1éè¿ï¼2æç»', `create_time` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uniq_customer_code` (`customer_code`), KEY `idx_customer_ucid` (`customer_ucid`), KEY `idx_appid_pkid` (`appid`,`app_pkid`), KEY `idx_customer_name_4` (`customer_name(4)`), KEY `idx_update_time` (`update_time`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='æ¿æºå¸¦çä¿¡æ¯è¡¨';
1234567891011121314151617181920212223242526272829 | CREATE TABLE `house_showing_house` (  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,  `showing_record_id` int(10) unsigned NOT NULL COMMENT '带ç表主é®',  `house_code` char(15) NOT NULL COMMENT 'æ¿æºSEç¼å·',  `agent_code` int(10) unsigned NOT NULL COMMENT 'ç»çºªäººSEç¼å·',  `agent_ucid` int(10) unsigned NOT NULL COMMENT 'ç»çºªäººUCç¼å·',  `agent_name` varchar(64) NOT NULL COMMENT 'ç»çºªäººå§å',  `customer_code` varbinary(16) NOT NULL COMMENT '客æ·ç¼å·, å¦ææçè¯',  `customer_ucid` int(10) unsigned NOT NULL COMMENT '客æ·UCç¼å·',  `customer_name` varchar(32) NOT NULL COMMENT '客æ·å§å',  `hdic_city_id` smallint(5) unsigned NOT NULL COMMENT '楼çåå ¸ åå¸ID',  `hdic_resblock_id` int(10) unsigned NOT NULL COMMENT '楼çåå ¸ 楼çID',  `see_time` datetime NOT NULL DEFAULT '1990-01-01 00:00:00' COMMENT '带çæ¶é´',  `appid` int(11) NOT NULL COMMENT 'ä¿¡æ¯æ¥æº',  `app_pkid` bigint(20) NOT NULL COMMENT 'appç主é®',  `satisfaction` tinyint(1) unsigned NOT NULL COMMENT '客æ·æ¯å¦æ»¡æ',  `reciprocal_frame` tinyint(4) unsigned DEFAULT '0' COMMENT '1ççæ¬æ·åï¼2ççåæ·åï¼3ççæ¬æ¿',  `feedback` varchar(512) NOT NULL COMMENT 'åé¦å 容',  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'ç¶æå¼',  `audit_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'å®¡æ ¸ç¶æï¼0å¾ å®¡ï¼1éè¿ï¼2æç»',  `create_time` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01',  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),  UNIQUE KEY `uniq_customer_code` (`customer_code`),  KEY `idx_customer_ucid` (`customer_ucid`),  KEY `idx_appid_pkid` (`appid`,`app_pkid`),  KEY `idx_customer_name_4` (`customer_name(4)`),  KEY `idx_update_time` (`update_time`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='æ¿æºå¸¦çä¿¡æ¯è¡¨'; |
---|
å欢(1) æèµ