äºè®¡ç®ç°å¨åå±åæ¯å¾å¥½ï¼å¾å¤äººé½ä¼éæ©äºè®¡ç®ãä½æ¯å¦å®äºè®¡ç®å¦ä½é¢è¯æåä¹æ¯éè¦çä¸ç¯ãè¿æ¬¡å¨æ¬æ就为大家带æ¥äºè®¡ç®é¢è¯åçæ¡ï¼å¸æè½å¤å¯¹å¤§å®¶æ¾å·¥ä½çå¾ç¨ææå©çã
ä»äºèç½å ¬å¸çé¢è¯è¿ç¨æ¥çï¼æ±èè å¿ é¡»æ³¨æ以ä¸å ç¹ï¼
1ã计ç®æºç½ç»åæ°æ®åºçç¸å ³ç¥è¯æ¯ææäºèç½å ¬å¸åºç¡ç¥è¯èå¯çéè¦æ¹é¢ï¼ä¸è¿°ä¹ç»åºäºä¸å°é¢è¯é¢ï¼å¾å¤å ¬å¸çé¢ç®é½å¾ç¸åï¼è¿ä¹åºæ¬ç»åºäºéç¹ã
2ãæ§æ ¼æµè¯è¢«è¶æ¥è¶å¤çäºèç½å ¬å¸çº³å ¥èå¯èå´ï¼æäºé®é¢çèµ·æ¥ä¼¼ä¹æ¯æ£å¸¸èå¯ï¼ä½å¾å¤æ¶åå¯ä»¥çåºä¸ä¸ªäººçæ§æ ¼ï¼å ¬å¸åºæ¬é½æ´å欢ä¹è§åä¸ãæ度积æçæ±èè ã
3ãå¦æåºç¡é¢è¯æ²¡é®é¢ï¼å¤æ°äºèç½å ¬å¸ç人åä¼è®©æ±èè æå°åå¹´æä¸å¹´çå·¥èµæµæ°´ï¼æ以åä½æ±èè å¨èªèµè¿ä»¶äºä¸å¤§å¯ä»¥ç´æ¥è¯´åºåå²æ°æ®åæææ°æ®ï¼é åæ¯ä¸å¯åçã
ä¸é¢ç»å¤§å®¶å享ä¸äºå®ç¨çé¢è¯é¢ï¼
1MySQLçå¤å¶åç以åæµç¨
åºæ¬åçæµç¨ï¼3个线ç¨ä»¥åä¹é´çå ³èï¼
ï¼1ï¼ä¸»ï¼binlog线ç¨ââè®°å½ä¸æææ¹åäºæ°æ®åºæ°æ®çè¯å¥ï¼æ¾è¿masterä¸çbinlogä¸ï¼
ï¼2ï¼ä»ï¼io线ç¨ââå¨ä½¿ç¨start slave ä¹åï¼è´è´£ä»masterä¸æå binlog å 容ï¼æ¾è¿ èªå·±çrelay logä¸ï¼
ï¼3ï¼ä»ï¼sqlæ§è¡çº¿ç¨ââæ§è¡relay logä¸çè¯å¥ï¼
2MySQLä¸myisamä¸innodbçåºå«ï¼è³å°5ç¹
(1)ãé®5ç¹ä¸åï¼
1>.InnoDBæ¯æäºç©ï¼èMyISAMä¸æ¯æäºç©
2>.InnoDBæ¯æè¡çº§éï¼èMyISAMæ¯æ表级é
3>.InnoDBæ¯æMVCC, èMyISAMä¸æ¯æ
4>.InnoDBæ¯æå¤é®ï¼èMyISAMä¸æ¯æ
5>.InnoDBä¸æ¯æå ¨æç´¢å¼ï¼èMyISAMæ¯æã
(2)ãinnodbå¼æç4大ç¹æ§ï¼
æå ¥ç¼å²ï¼insert buffer)ï¼
äºæ¬¡å(double write)ï¼
èªéåºåå¸ç´¢å¼(ahi)ï¼
é¢è¯»(read ahead)ã
(3)ã2è selectcount(*)åªä¸ªæ´å¿«ï¼ä¸ºä»ä¹
myisamæ´å¿«ï¼å 为myisamå é¨ç»´æ¤äºä¸ä¸ªè®¡æ°å¨ï¼å¯ä»¥ç´æ¥è°åã
3MySQLä¸varcharä¸charçåºå«ä»¥åvarchar(50)ä¸ç50代表ç涵ä¹
(1)ãvarcharä¸charçåºå«
charæ¯ä¸ç§åºå®é¿åº¦çç±»åï¼varcharåæ¯ä¸ç§å¯åé¿åº¦çç±»åã
(2)ãvarchar(50)ä¸50ç涵ä¹
æå¤åæ¾50个å符ï¼varchar(50)å(200)åå¨helloæå 空é´ä¸æ ·ï¼ä½åè å¨æåºæ¶ä¼æ¶èæ´å¤å åï¼å 为order by coléç¨fixed_length计ç®colé¿åº¦(memoryå¼æä¹ä¸æ ·)ã
(3)ãintï¼20ï¼ä¸20ç涵ä¹
æ¯ææ¾ç¤ºå符çé¿åº¦
ä½è¦å åæ°çï¼æ大为255ï¼æ¯å¦å®æ¯è®°å½è¡æ°çid,æå ¥10ç¬èµæï¼å®å°±æ¾ç¤º00000000001 ~~~00000000010ï¼å½å符çä½æ°è¶ è¿11,å®ä¹åªæ¾ç¤º11ä½ï¼å¦æä½ æ²¡æå é£ä¸ªè®©å®æªæ»¡11ä½å°±åé¢å 0çåæ°ï¼å®ä¸ä¼å¨åé¢å 0
20表示æ大æ¾ç¤ºå®½åº¦ä¸º20ï¼ä½ä»å 4åèåå¨ï¼åå¨èå´ä¸åï¼
(4)ãmysql为ä»ä¹è¿ä¹è®¾è®¡
对大å¤æ°åºç¨æ²¡ææä¹ï¼åªæ¯è§å®ä¸äºå·¥å ·ç¨æ¥æ¾ç¤ºå符ç个æ°ï¼int(1)åint(20)åå¨å计ç®åä¸æ ·ã
4é®äºinnodbçäºå¡ä¸æ¥å¿çå®ç°æ¹å¼
(1)ãæå¤å°ç§æ¥å¿ï¼
é误æ¥å¿ï¼è®°å½åºéä¿¡æ¯ï¼ä¹è®°å½ä¸äºè¦åä¿¡æ¯æè æ£ç¡®çä¿¡æ¯ã
æ¥è¯¢æ¥å¿ï¼è®°å½ææ对æ°æ®åºè¯·æ±çä¿¡æ¯ï¼ä¸è®ºè¿äºè¯·æ±æ¯å¦å¾å°äºæ£ç¡®çæ§è¡ã
æ ¢æ¥è¯¢æ¥å¿ï¼è®¾ç½®ä¸ä¸ªéå¼ï¼å°è¿è¡æ¶é´è¶ è¿è¯¥å¼çææSQLè¯å¥é½è®°å½å°æ ¢æ¥è¯¢çæ¥å¿æ件ä¸ã
äºè¿å¶æ¥å¿ï¼è®°å½å¯¹æ°æ®åºæ§è¡æ´æ¹çæææä½ã
ä¸ç»§æ¥å¿ã
äºå¡æ¥å¿ã
(2)ãäºç©ç4ç§é离级å«
é离级å«
读æªæ交(RU)
读已æ交(RC)
å¯éå¤è¯»(RR)
串è¡
(3)ãäºå¡æ¯å¦ä½éè¿æ¥å¿æ¥å®ç°çï¼è¯´å¾è¶æ·±å ¥è¶å¥½ã
äºå¡æ¥å¿æ¯éè¿redoåinnodbçåå¨å¼ææ¥å¿ç¼å²ï¼Innodb log bufferï¼æ¥å®ç°çï¼å½å¼å§ä¸ä¸ªäºå¡çæ¶åï¼ä¼è®°å½è¯¥äºå¡çlsn(log sequence number)å·; å½äºå¡æ§è¡æ¶ï¼ä¼å¾InnoDBåå¨å¼æçæ¥å¿ã
çæ¥å¿ç¼åéé¢æå ¥äºå¡æ¥å¿ï¼å½äºå¡æ交æ¶ï¼å¿ é¡»å°åå¨å¼æçæ¥å¿ç¼å²åå ¥ç£çï¼éè¿innodb_flush_log_at_trx_commitæ¥æ§å¶ï¼ï¼ä¹å°±æ¯åæ°æ®åï¼éè¦å åæ¥å¿ãè¿ç§æ¹å¼ç§°ä¸ºâé¢åæ¥å¿æ¹å¼âã
5é®äºMySQL binlogçå ç§æ¥å¿å½å ¥æ ¼å¼ä»¥ååºå«
(1)ãbinlogçæ¥å¿æ ¼å¼çç§ç±»ååå«ï¼
(2)ãéç¨åºæ¯ï¼
(3)ãç»å第ä¸ä¸ªé®é¢ï¼æ¯ä¸ç§æ¥å¿æ ¼å¼å¨å¤å¶ä¸çä¼å£ã
1.Statementï¼æ¯ä¸æ¡ä¼ä¿®æ¹æ°æ®çsqlé½ä¼è®°å½å¨binlogä¸ã
ä¼ç¹ï¼ä¸éè¦è®°å½æ¯ä¸è¡çååï¼åå°äºbinlogæ¥å¿éï¼è约äºIOï¼æé«æ§è½ã(ç¸æ¯rowè½è约å¤å°æ§è½ ä¸æ¥å¿éï¼è¿ä¸ªåå³äºåºç¨çSQLæ åµï¼æ£å¸¸åä¸æ¡è®°å½ä¿®æ¹æè æå ¥rowæ ¼å¼æ产ççæ¥å¿éè¿å°äºStatement产ççæ¥å¿éï¼ä½æ¯èèå°å¦æå¸¦æ¡ ä»¶çupdateæä½ï¼ä»¥åæ´è¡¨å é¤ï¼alter表çæä½ï¼ROWæ ¼å¼ä¼äº§ç大éæ¥å¿ï¼å æ¤å¨èèæ¯å¦ä½¿ç¨ROWæ ¼å¼æ¥å¿æ¶åºè¯¥è·æ®åºç¨çå®é æ åµï¼å ¶æ 产ççæ¥å¿éä¼å¢å å¤å°ï¼ä»¥å带æ¥çIOæ§è½é®é¢ã)
缺ç¹ï¼ç±äºè®°å½çåªæ¯æ§è¡è¯å¥ï¼ä¸ºäºè¿äºè¯å¥è½å¨slaveä¸æ£ç¡®è¿è¡ï¼å æ¤è¿å¿ 须记å½æ¯æ¡è¯å¥å¨æ§è¡çæ¶åç ä¸äºç¸å ³ä¿¡æ¯ï¼ä»¥ä¿è¯ææè¯å¥è½å¨slaveå¾å°åå¨master端æ§è¡æ¶åç¸å çç»æãå¦å¤mysql çå¤å¶,åä¸äºç¹å®å½æ°åè½ï¼slaveå¯ä¸masterä¸è¦ä¿æä¸è´ä¼æå¾å¤ç¸å ³é®é¢(å¦sleep()å½æ°ï¼ last_insert_id()ï¼ä»¥åuser-defined functions(udf)ä¼åºç°é®é¢).
使ç¨ä»¥ä¸å½æ°çè¯å¥ä¹æ æ³è¢«å¤å¶ï¼
- LOAD_FILE()
- UUID()
- USER()
- FOUND_ROWS()
- SYSDATE() (é¤éå¯å¨æ¶å¯ç¨äº --sysdate-is-now é项)
åæ¶å¨INSERT â¦SELECT ä¼äº§çæ¯ RBR æ´å¤çè¡çº§é
2.Row:ä¸è®°å½sqlè¯å¥ä¸ä¸æç¸å ³ä¿¡æ¯ï¼ä» ä¿ååªæ¡è®°å½è¢«ä¿®æ¹ã
ä¼ç¹ï¼ binlogä¸å¯ä»¥ä¸è®°å½æ§è¡çsqlè¯å¥çä¸ä¸æç¸å ³çä¿¡æ¯ï¼ä» éè¦è®°å½é£ä¸æ¡è®°å½è¢«ä¿®æ¹æä»ä¹äºãæ以rowlevelçæ¥å¿å 容ä¼éå¸¸æ¸ æ¥çè®°å½ä¸ æ¯ä¸è¡æ°æ®ä¿®æ¹çç»èãèä¸ä¸ä¼åºç°æäºç¹å®æ åµä¸çåå¨è¿ç¨ï¼æfunctionï¼ä»¥åtriggerçè°ç¨å触åæ æ³è¢«æ£ç¡®å¤å¶çé®é¢
缺ç¹:ææçæ§è¡çè¯å¥å½è®°å½å°æ¥å¿ä¸çæ¶åï¼é½å°ä»¥æ¯è¡è®°å½çä¿®æ¹æ¥è®°å½ï¼è¿æ ·å¯è½ä¼äº§ç大éçæ¥å¿å 容,æ¯ å¦ä¸æ¡updateè¯å¥ï¼ä¿®æ¹å¤æ¡è®°å½ï¼åbinlogä¸æ¯ä¸æ¡ä¿®æ¹é½ä¼æè®°å½ï¼è¿æ ·é æbinlogæ¥å¿éä¼å¾å¤§ï¼ç¹å«æ¯å½æ§è¡alter tableä¹ç±»çè¯å¥çæ¶åï¼ç±äºè¡¨ç»æä¿®æ¹ï¼æ¯æ¡è®°å½é½åçæ¹åï¼é£ä¹è¯¥è¡¨æ¯ä¸æ¡è®°å½é½ä¼è®°å½å°æ¥å¿ä¸ã
3.Mixedlevel: æ¯ä»¥ä¸ä¸¤ç§levelçæ··å使ç¨ï¼ä¸è¬çè¯å¥ä¿®æ¹ä½¿ç¨statmentæ ¼å¼ä¿åbinlogï¼å¦ä¸äºå½æ°ï¼statementæ æ³å®æ主ä»å¤å¶çæä½ï¼å éç¨rowæ ¼å¼ä¿åbinlog,MySQLä¼æ ¹æ®æ§è¡çæ¯ä¸æ¡å ·ä½çsqlè¯å¥æ¥åºåå¯¹å¾ è®°å½çæ¥å¿å½¢å¼ï¼ä¹å°±æ¯å¨StatementåRowä¹é´éæ© ä¸ç§.æ°çæ¬çMySQLä¸érow level模å¼ä¹è¢«åäºä¼åï¼å¹¶ä¸æ¯ææçä¿®æ¹é½ä¼ä»¥row levelæ¥è®°å½ï¼åéå°è¡¨ç»æåæ´çæ¶åå°±ä¼ä»¥statement模å¼æ¥è®°å½ãè³äºupdateæè deleteçä¿®æ¹æ°æ®çè¯å¥ï¼è¿æ¯ä¼è®°å½ææè¡ç åæ´ã
6é®äºä¸MySQLæ°æ®åºcpué£åå°500%çè¯ä»æä¹å¤çï¼
(1)ã没æç»éªçï¼å¯ä»¥ä¸é®ï¼
(2)ãæç»éªçï¼é®ä»ä»¬çå¤çæè·¯ã
ååºææè¿ç¨ show processlist è§å¯ææè¿ç¨ å¤ç§æ²¡æç¶æååç(å¹²æ)
æ¥çè¶ æ¶æ¥å¿æè é误æ¥å¿ (åäºå å¹´å¼å,ä¸è¬ä¼æ¯æ¥è¯¢ä»¥å大æ¹éçæå ¥ä¼å¯¼è´cpuä¸i/oä¸æ¶¨,å½ç¶ä¸æé¤ç½ç»ç¶æçªç¶æäº,导è´ä¸ä¸ªè¯·æ±æå¡å¨åªæ¥åå°ä¸åï¼æ¯å¦whereåå¥æå页åå¥æ²¡æåé,å½ç¶çä¸æ¬¡è¢«åç»å)
7sqlä¼å
(1)ãexplainåºæ¥çåç§itemçæä¹ï¼
select_type
表示æ¥è¯¢ä¸æ¯ä¸ªselectåå¥çç±»å
type
表示MySQLå¨è¡¨ä¸æ¾å°æéè¡çæ¹å¼ï¼å称â访é®ç±»åâ
possible_keys
æåºMySQLè½ä½¿ç¨åªä¸ªç´¢å¼å¨è¡¨ä¸æ¾å°è¡ï¼æ¥è¯¢æ¶åå°çå段ä¸è¥åå¨ç´¢å¼ï¼å该索å¼å°è¢«ååºï¼ä½ä¸ä¸å®è¢«æ¥è¯¢ä½¿ç¨
key
æ¾ç¤ºMySQLå¨æ¥è¯¢ä¸å®é 使ç¨çç´¢å¼ï¼è¥æ²¡æ使ç¨ç´¢å¼ï¼æ¾ç¤ºä¸ºNULL
key_len
表示索å¼ä¸ä½¿ç¨çåèæ°ï¼å¯éè¿è¯¥å计ç®æ¥è¯¢ä¸ä½¿ç¨çç´¢å¼çé¿åº¦
ref
表示ä¸è¿°è¡¨çè¿æ¥å¹é æ¡ä»¶ï¼å³åªäºåæ常é被ç¨äºæ¥æ¾ç´¢å¼åä¸çå¼
Extra
å å«ä¸éåå¨å ¶ä»åä¸æ¾ç¤ºä½ååéè¦çé¢å¤ä¿¡æ¯ã
(2)ãprofileçæä¹ä»¥å使ç¨åºæ¯ï¼
æ¥è¯¢å° SQL ä¼æ§è¡å¤å°æ¶é´, 并çåº CPU/Memory 使ç¨é, æ§è¡è¿ç¨ä¸ Systemlock, Table lock è±å¤å°æ¶é´ççã
8å¤ä»½è®¡åï¼mysqldump以åxtranbackupçå®ç°åç
(1)ãå¤ä»½è®¡åï¼
è¿éæ¯ä¸ªå ¬å¸é½ä¸ä¸æ ·ï¼æ¨å«è¯´é£ç§1å°æ¶1å ¨å¤ä»ä¹çå°±è¡
(2)ãå¤ä»½æ¢å¤æ¶é´ï¼
è¿éè·æºå¨ï¼å°¤å ¶æ¯ç¡¬ççéçæå ³ç³»ï¼ä»¥ä¸å举å ä¸ªä» ä¾åè
20Gç2åéï¼mysqldumpï¼
80Gç30åé(mysqldump)
111Gç30åéï¼mysqldump)
288Gç3å°æ¶ï¼xtra)
3Tç4å°æ¶ï¼xtra)
é»è¾å¯¼å ¥æ¶é´ä¸è¬æ¯å¤ä»½æ¶é´ç5å以ä¸
(3)ãxtrabackupå®ç°åç
å¨InnoDBå é¨ä¼ç»´æ¤ä¸ä¸ªredoæ¥å¿æ件ï¼æ们ä¹å¯ä»¥å«åäºå¡æ¥å¿æ件ãäºå¡æ¥å¿ä¼åå¨æ¯ä¸ä¸ªInnoDB表æ°æ®çè®°å½ä¿®æ¹ãå½InnoDBå¯å¨æ¶ï¼InnoDBä¼æ£æ¥æ°æ®æ件åäºå¡æ¥å¿ï¼å¹¶æ§è¡ä¸¤ä¸ªæ¥éª¤ï¼å®åºç¨ï¼åæ»ï¼å·²ç»æ交çäºå¡æ¥å¿å°æ°æ®æ件ï¼å¹¶å°ä¿®æ¹è¿ä½æ²¡ææ交çæ°æ®è¿è¡åæ»æä½ã
9mysqldumpä¸å¤ä»½åºæ¥çsqlï¼å¦æææ³sqlæ件ä¸ï¼ä¸è¡åªæä¸ä¸ªinsertâ¦value()çè¯ï¼æä¹åï¼å¦æå¤ä»½éè¦å¸¦ä¸masterçå¤å¶ç¹ä¿¡æ¯æä¹åï¼
âskip-extended-insert
[[email protected] ~]# mysqldump -uroot -p helei --skip-extended-insert
Enter password:
KEY
idx_c1
( c1
),
KEY
idx_c2
( c2
)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;
;
â Dumping data for table helei
helei
LOCK TABLES
helei
WRITE;
;
INSERT INTO
helei
VALUES (1,32,37,38,â2016-10-18 06:19:24â,âsusususususususususususuâ);
INSERT INTO
helei
VALUES (2,37,46,21,â2016-10-18 06:19:24â,âsusususususuâ);
INSERT INTO
helei
VALUES (3,21,5,14,â2016-10-18 06:19:24â,âsusuâ);
10500å°dbï¼å¨æå¿«æ¶é´ä¹å éå¯
puppetï¼dsh
11innodbç读ååæ°ä¼å
(1)ã读ååæ°
global buffer pool以å local bufferï¼
(2)ãåå ¥åæ°ï¼
innodb_flush_log_at_trx_commit
innodb_buffer_pool_size
(3)ãä¸IOç¸å ³çåæ°ï¼
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0
(4)ãç¼ååæ°ä»¥åç¼åçéç¨åºæ¯ã
query cache/query_cache_type
并ä¸æ¯ææ表é½éå使ç¨query cacheãé æquery cache失æçåå 主è¦æ¯ç¸åºçtableåçäºåæ´
第ä¸ä¸ªï¼è¯»æä½å¤çè¯ççæ¯ä¾ï¼ç®åæ¥è¯´ï¼å¦ææ¯ç¨æ·æ¸ å表ï¼æè 说æ¯æ°æ®æ¯ä¾æ¯è¾åºå®ï¼æ¯å¦è¯´ååå表ï¼æ¯å¯ä»¥æå¼çï¼åææ¯è¿äºåºæ¯è¾éä¸ï¼æ°æ®åºä¸çå®å¡æ¯è¾å°ã
第äºä¸ªï¼æ们âè¡éªâçæ¶åï¼æ¯å¦è¯´æ们ç«æ çæ¶ååæµï¼æquery cacheæå¼ï¼è¿æ¯è½æ¶å°qpsæ¿å¢çææï¼å½ç¶åæ示å端çè¿æ¥æ± ä»ä¹çé½é ç½®ä¸æ ·ã大é¨åæ åµä¸å¦æåå ¥çå± å¤ï¼è®¿é®é并ä¸å¤ï¼é£ä¹å°±ä¸è¦æå¼ï¼ä¾å¦ç¤¾äº¤ç½ç«çï¼10%ç人产çå 容ï¼å ¶ä½ç90%é½å¨æ¶è´¹ï¼æå¼è¿æ¯ææå¾å¥½çï¼ä½æ¯ä½ å¦ææ¯qqæ¶æ¯ï¼æè è天ï¼é£å°±å¾è¦å½ã
第ä¸ä¸ªï¼å°ç½ç«æè 没æé«å¹¶åçæ æè°ï¼é«å¹¶åä¸ï¼ä¼çå° å¾å¤ qcache é çå¾ ï¼æ以ä¸è¬é«å¹¶åä¸ï¼ä¸å»ºè®®æå¼query cacheã
12ä½ æ¯å¦ä½çæ§ä½ 们çæ°æ®åºçï¼ä½ 们çæ ¢æ¥å¿é½æ¯æä¹æ¥è¯¢çï¼
çæ§çå·¥å ·æå¾å¤ï¼ä¾å¦zabbixï¼lepusï¼æè¿éç¨çæ¯lepusã
13ä½ æ¯å¦åè¿ä¸»ä»ä¸è´æ§æ ¡éªï¼å¦ææï¼æä¹åçï¼å¦æ没æï¼ä½ æç®æä¹åï¼
主ä»ä¸è´æ§æ ¡éªæå¤ç§å·¥å · ä¾å¦checksumãmysqldiffãpt-table-checksumçã
14ä½ ä»¬æ°æ®åºæ¯å¦æ¯æemoji表æ ï¼å¦æä¸æ¯æï¼å¦ä½æä½ï¼
å¦ææ¯utf8å符éçè¯ï¼éè¦å级è³utf8_mb4æ¹å¯æ¯æã
15ä½ æ¯å¦ä½ç»´æ¤æ°æ®åºçæ°æ®åå ¸çï¼
è¿ä¸ªå¤§å®¶ç»´æ¤çæ¹æ³é½ä¸åï¼ä¸è¬æ¯ç´æ¥å¨ç产åºè¿è¡æ³¨éï¼å©ç¨å·¥å ·å¯¼åºæexcelæ¹ä¾¿æµéã
16ä½ ä»¬æ¯å¦æå¼åè§èï¼å¦ææï¼å¦ä½æ§è¡ç
æï¼å¼åè§èç½ä¸æå¾å¤äºï¼å¯ä»¥èªå·±ççæ»ç»ä¸ã
17表ä¸æ大å段X(ä¾å¦ï¼textç±»å)ï¼ä¸å段Xä¸ä¼ç»å¸¸æ´æ°ï¼ä»¥è¯»ä¸ºä¸ºä¸»ï¼è¯·é®
(1)ãæ¨æ¯éæ©ææå表ï¼è¿æ¯ç»§ç»æ¾ä¸èµ·ï¼
(2)ãååºæ¨è¿æ ·éæ©ççç±ã
çï¼æ带æ¥çé®é¢ï¼è¿æ¥æ¶è + åå¨æå空é´ï¼ä¸æå¯è½å¸¦æ¥çé®é¢ï¼æ¥è¯¢æ§è½ï¼
å¦æè½å®¹å¿æå带æ¥ç空é´é®é¢,æçè¯æ好åç»å¸¸è¦æ¥è¯¢ç表ç主é®å¨ç©çç»æä¸æ¾ç½®å¨ä¸èµ·(ååº) 顺åºIO,åå°è¿æ¥æ¶è,æåè¿æ¯ä¸ä¸ªææ¬ååå ä¸ä¸ä¸ªå ¨æç´¢å¼æ¥å°½éæµæ¶è¿æ¥æ¶èã
å¦æè½å®¹å¿ä¸æå带æ¥çæ¥è¯¢æ§è½æ失çè¯:ä¸é¢çæ¹æ¡å¨æ个æè´æ¡ä»¶ä¸è¯å®ä¼åºç°é®é¢,é£ä¹ä¸æå°±æ¯æ好çéæ©ã
18MySQLä¸InnoDBå¼æçè¡éæ¯éè¿å å¨ä»ä¹ä¸å®æ(æ称å®ç°)çï¼ä¸ºä»ä¹æ¯è¿æ ·åçï¼
çï¼InnoDBæ¯åºäºç´¢å¼æ¥å®æè¡é
ä¾: select * from tab_with_index where id = 1 for update;
for update å¯ä»¥æ ¹æ®æ¡ä»¶æ¥å®æè¡ééå®,å¹¶ä¸ id æ¯æç´¢å¼é®çå,
å¦æ id ä¸æ¯ç´¢å¼é®é£ä¹InnoDBå°å®æ表é,并åå°æ ä»è°èµ·
19å¦ä½ä»mysqldump产ççå ¨åºå¤ä»½ä¸åªæ¢å¤æä¸ä¸ªåºãæä¸å¼ 表ï¼
å¨Mysqldumpå®æ¹å·¥å ·ä¸ï¼å¦ä½åªæ¢å¤æ个åºå¢ï¼
å ¨åºå¤ä»½
[[email protected] ~]# mysqldump -uroot -p --single-transaction -A --master-data=2 >dump.sql
åªè¿åerpåºçå 容
[[email protected] ~]# mysql -uroot -pMANAGER erp --one-database <dump.sql
å¯ä»¥çåºè¿é主è¦ç¨å°çåæ°æ¯âone-databaseç®å-oçåæ°ï¼æ大æ¹ä¾¿äºæ们çæ¢å¤çµæ´»æ§ã
é£ä¹å¦ä½ä»å ¨åºå¤ä»½ä¸æ½åæå¼ è¡¨å¢ï¼å ¨åºæ¢å¤ï¼åæ¢å¤æå¼ è¡¨å°åºè¿å¯ä»¥ï¼å¤§åºå°±å¾éº»ç¦äºï¼é£æ们å¯ä»¥å©ç¨æ£å表达å¼æ¥è¿è¡å¿«éæ½åï¼å ·ä½å®ç°æ¹æ³å¦ä¸ï¼
ä»å ¨åºå¤ä»½ä¸æ½ååºt表ç表ç»æ
[[email protected] ~]# sed -eâ/./{H;$!d;}â -e âx;/CREATE TABLE
t
/!d;qâ dump.sql
DROP TABLE IF EXISTS
t
;
;
;
CREATE TABLE
t
(
id
int(10) NOT NULL AUTO_INCREMENT,
age
tinyint(4) NOT NULL DEFAULT â0â,
name
varchar(30) NOT NULL DEFAULT ââ,
PRIMARY KEY (
id
)
) ENGINE=InnoDBAUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
;
ä»å ¨åºå¤ä»½ä¸æ½ååºt表çå 容
[[email protected] ~]# grepâINSERT INTO
t
â dump.sql
INSERT INTO
t
VALUES (0,0,ââ),(1,0,âaaâ),(2,0,âbbbâ),(3,25,âheleiâ);
20å¼æ¾æ§é®é¢ï¼æ®è¯´æ¯è ¾è®¯ç
ä¸ä¸ª6亿ç表aï¼ä¸ä¸ª3亿ç表bï¼éè¿å¤é´tidå ³èï¼ä½ å¦ä½æå¿«çæ¥è¯¢åºæ»¡è¶³æ¡ä»¶ç第50000å°ç¬¬50200ä¸çè¿200æ¡æ°æ®è®°å½ã
1ãå¦æA表TIDæ¯èªå¢é¿,并ä¸æ¯è¿ç»ç,B表çID为索å¼
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
2ãå¦æA表çTIDä¸æ¯è¿ç»ç,é£ä¹å°±éè¦ä½¿ç¨è¦çç´¢å¼.TIDè¦ä¹æ¯ä¸»é®,è¦ä¹æ¯è¾ å©ç´¢å¼,B表IDä¹éè¦æç´¢å¼ã
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;