åççæç¹å¤è¯·èå¿çå®ã
å¸æè½å¸®å©ä½ è¿è¯·åæ¶é纳谢谢
1äºå¡çåç
äºå¡å°±æ¯å°ä¸ç»SQLè¯å¥æ¾å¨åä¸æ¹æ¬¡å
å»æ§è¡ï¼å¦æä¸ä¸ªSQLè¯å¥åºé,å该æ¹æ¬¡å
çææSQLé½å°è¢«åæ¶æ§è¡ãMySQLäºå¡å¤çåªæ¯æInnoDBåBDBæ°æ®è¡¨ç±»åã
1äºå¡çACIDåå
** 1ï¼Atomicityï¼ååæ§**ï¼ äºå¡æ¯æå°çæ§è¡åä½ï¼ä¸å
许åå²ãååæ§ç¡®ä¿å¨ä½è¦ä¹å
¨é¨å®æï¼è¦ä¹å®å
¨ä¸èµ·ä½ç¨ï¼
2ï¼Consistencyï¼ä¸è´æ§ï¼ æ§è¡äºå¡ååï¼æ°æ®ä¿æä¸è´ï¼
3ï¼Isolationï¼é离æ§ï¼ 并å访é®æ°æ®åºæ¶ï¼ä¸ä¸ªäºå¡ä¸è¢«å
¶ä»äºå¡æå¹²æ°ã
4ï¼Durabilityï¼æä¹
æ§: ä¸ä¸ªäºå¡è¢«æ交ä¹åã对æ°æ®åºä¸æ°æ®çæ¹åæ¯æä¹
çï¼å³ä½¿æ°æ®åºåçæ
éã
1ç¼å²æ± ï¼Buffer Poolï¼
Buffer Poolä¸å
å«äºç£çä¸é¨åæ°æ®é¡µçæ å°ãå½ä»æ°æ®åºè¯»åæ°æ®æ¶ï¼ä¼å
ä»Buffer Poolä¸è¯»åæ°æ®ï¼å¦æBuffer Poolä¸æ²¡æï¼åä»ç£ç读ååæ¾å
¥å°Buffer Poolä¸ãå½åæ°æ®åºåå
¥æ°æ®æ¶ï¼ä¼å
åå
¥å°Buffer Poolä¸ï¼Buffer Poolä¸æ´æ°çæ°æ®ä¼å®æå·æ°å°ç£çä¸ï¼æ¤è¿ç¨ç§°ä¸ºå·èï¼ã
2æ¥å¿ç¼å²åºï¼Log Bufferï¼
å½å¨MySQLä¸å¯¹InnoDB表è¿è¡æ´æ¹æ¶ï¼è¿äºæ´æ¹å½ä»¤é¦å
åå¨å¨InnoDBæ¥å¿ç¼å²åºï¼Log Bufferï¼çå
åä¸ï¼ç¶ååå
¥é常称为éåæ¥å¿ï¼redo logsï¼çInnoDBæ¥å¿æ件ä¸ã
3ååæºå¶ç¼åï¼DoubleWrite Bufferï¼
Doublewrite Bufferæ¯å
±äº«è¡¨ç©ºé´çç©çæ件ç buffer,å
¶å¤§å°æ¯2MB.æ¯ä¸ä¸ªä¸å为äºç2MB空é´ã
å·èæä½å¼å§ä¹æ¶,å
è¿è¡è页**âå¤ä»½â**æä½.å°è页æ°æ®åå
¥ Doublewrite Buffer.
å°Doublewrite Buffer(顺åºIO)åå
¥ç£çæ件ä¸(å
±äº«è¡¨ç©ºé´) è¿è¡å·èæä½.
4åæ»æ¥å¿(Undo Log)
Undo Logè®°å½çæ¯é»è¾æ¥å¿.è®°å½çæ¯äºå¡è¿ç¨ä¸æ¯æ¡æ°æ®çååçæ¬åæ
åµ.
å¨Innodb ç£çæ¶æä¸Undo Log é»è®¤æ¯å
±äº«è¡¨ç©ºé´çç©çæ件çBuffer.
å¨äºå¡å¼å¸¸ä¸æ,æè
主å¨(Rollback)åæ»çè¿ç¨ä¸ ,Innodbåºäº Undo Logè¿è¡æ°æ®æ¤éåæ»,ä¿è¯æ°æ®åå½è³äºå¡å¼å§ç¶æ.
5éåæ¥å¿(Redo Log)
Redo Logé常æçæ¯ç©çæ¥å¿ï¼è®°å½çæ¯æ°æ®é¡µçç©çä¿®æ¹.并ä¸è®°å½è¡è®°å½æ
åµãï¼ä¹å°±æ¯åªè®°å½è¦ååªäºä¿®æ¹ï¼å¹¶ä¸è®°å½ä¿®æ¹çå®ææ
åµï¼ å½æ°æ®åºå®æºéå¯çæ¶åï¼ä¼å°éåæ¥å¿ä¸çå
容æ¢å¤å°æ°æ®åºä¸ã
1ååæ§
Innodbäºå¡çååæ§ä¿è¯,å
å«äºå¡çæ交æºå¶åäºå¡çåæ»æºå¶.å¨Innodbå¼æä¸äºå¡çåæ»æºå¶æ¯ä¾æ åæ»æ¥å¿(Undo Log) è¿è¡åæ»æ°æ®ï¼ä¿è¯æ°æ®åå½è³äºå¡å¼å§ç¶æ.
2é£ä¹ä¸åçé离级å«ï¼é离æ§æ¯å¦ä½å®ç°çï¼ä¸ºä»ä¹ä¸åäºç©é´è½å¤äºä¸å¹²æ°ï¼ çæ¡æ¯ é å MVCCã
3æä¹
æ§
åºäºäºå¡çæ交æºå¶æµç¨æå¯è½åºç°ä¸ç§åºæ¯.
1 æ°æ®å·èæ£å¸¸.ä¸åæ£å¸¸æ交ï¼Redo Log 循ç¯è®°å½.æ°æ®æåè½ç.æä¹
æ§å¾ä»¥ä¿è¯
2æ°æ®å·èçè¿ç¨ä¸åºç°çç³»ç»æå¤å¯¼è´é¡µæè£ç°è±¡ (é¨åå·èæå)ï¼é对页æè£æ
åµ,éç¨Double writeæºå¶è¿è¡ä¿è¯é¡µæè£æ°æ®çæ¢å¤.
3æ°æ®æªåºç°é¡µæè£ç°è±¡,ä¹æ²¡æå·èæåï¼MySQLéè¿Redo Log è¿è¡æ°æ®çæä¹
åå³å¯
4ä¸è´æ§
ä»æ°æ®åºå±é¢ï¼æ°æ®åºéè¿ååæ§ãé离æ§ãæä¹
æ§æ¥ä¿è¯ä¸è´æ§
2äºå¡çé离级å«
Mysql é»è®¤éç¨ç REPEATABLE_READéç¦»çº§å« Oracle é»è®¤éç¨ç READ_COMMITTEDé离级å«
èè¯»ï¼ æä¸ä¸ªäºå¡è¯»åäºå¦å¤ä¸ä¸ªäºå¡æªæ交çæ°æ®ã
ä¸å¯éå¤è¯»ï¼ å¨ä¸ä¸ªäºå¡å
读å表ä¸çæä¸è¡æ°æ®ï¼å¤æ¬¡è¯»åç»æä¸å
è读(幻读)ï¼ æ¯æå¨ä¸ä¸ªäºå¡å
读åå°äºå«çäºå¡æå
¥çæ°æ®ï¼å¯¼è´åå读åä¸ä¸è´ã
2åºæ¬è¯æ³
-- 使ç¨setè¯å¥æ¥æ¹åèªå¨æ交模å¼
SET autocommit = 0; /*å
³é*/
SET autocommit = 1; /*å¼å¯*/
-- 注æ:
--- 1.MySQLä¸é»è®¤æ¯èªå¨æ交
--- 2.使ç¨äºå¡æ¶åºå
å
³éèªå¨æ交
-- å¼å§ä¸ä¸ªäºå¡,æ è®°äºå¡çèµ·å§ç¹
START TRANSACTION
-- æ交ä¸ä¸ªäºå¡ç»æ°æ®åº
COMMIT
-- å°äºå¡åæ»,æ°æ®åå°æ¬æ¬¡äºå¡çåå§ç¶æ
ROLLBACK
-- è¿åMySQLæ°æ®åºçèªå¨æ交
SET autocommit =1;
-- ä¿åç¹
SAVEPOINT ä¿åç¹å称 -- 设置ä¸ä¸ªäºå¡ä¿åç¹
ROLLBACK TO SAVEPOINT ä¿åç¹å称 -- åæ»å°ä¿åç¹
RELEASE SAVEPOINT ä¿åç¹å称 -- å é¤ä¿åç¹
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/*
课å æµè¯é¢ç®
Aå¨çº¿ä¹°ä¸æ¬¾ä»·æ ¼ä¸º500å
åå,ç½ä¸é¶è¡è½¬è´¦.
Açé¶è¡å¡ä½é¢ä¸º2000,ç¶åç»å家Bæ¯ä»500.
å家Bä¸å¼å§çé¶è¡å¡ä½é¢ä¸º10000
å建æ°æ®åºshopåå建表account并æå
¥2æ¡æ°æ®
*/
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 转账å®ç°
SET autocommit = 0; -- å
³éèªå¨æ交
START TRANSACTION; -- å¼å§ä¸ä¸ªäºå¡,æ è®°äºå¡çèµ·å§ç¹
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- æ交äºå¡
# rollback;
SET autocommit = 1; -- æ¢å¤èªå¨æ交
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
3äºå¡å®ç°æ¹å¼-MVCC
1ä»ä¹æ¯MVCC
MVCCæ¯mysqlççå¤çæ¬å¹¶åæ§å¶å³multi-Version Concurrency Controllerï¼mysqlçinnodbå¼ææ¯æMVVCãMVCCæ¯ä¸ºäºå®ç°äºå¡çé离æ§ï¼éè¿çæ¬å·ï¼é¿å
åä¸æ°æ®å¨ä¸åäºå¡é´çç«äºï¼ä½ å¯ä»¥æå®å½æåºäºå¤çæ¬å·çä¸ç§ä¹è§éãå½ç¶ï¼è¿ç§ä¹è§éåªå¨äºå¡çº§å«ä¸ºRRï¼å¯éå¤è¯»ï¼åRCï¼è¯»æ交ï¼çæãMVCCæ大ç好å¤ï¼ç¸ä¿¡ä¹æ¯è³çè½è¯¦ï¼è¯»ä¸å éï¼è¯»åä¸å²çªï¼æ大çå¢å äºç³»ç»ç并åæ§è½ã
2MVCCçå®ç°æºå¶
InnoDBå¨æ¯è¡æ°æ®é½å¢å 两个éèå段ï¼ä¸ä¸ªè®°å½å建ççæ¬å·ï¼ä¸ä¸ªè®°å½å é¤ççæ¬å·ã
å¨å¤çæ¬å¹¶åæ§å¶ä¸ï¼ä¸ºäºä¿è¯æ°æ®æä½å¨å¤çº¿ç¨è¿ç¨ä¸ï¼ä¿è¯äºå¡é离çæºå¶ï¼éä½éç«äºçååï¼ä¿è¯è¾é«ç并åéãå¨æ¯å¼å¯ä¸ä¸ªäºå¡æ¶ï¼ä¼çæä¸ä¸ªäºå¡ççæ¬å·ï¼è¢«æä½çæ°æ®ä¼çæä¸æ¡æ°çæ°æ®è¡ï¼ä¸´æ¶ï¼ï¼ä½æ¯å¨æ交å对å
¶ä»äºå¡æ¯ä¸å¯è§çï¼å¯¹äºæ°æ®çæ´æ°ï¼å
æ¬å¢å æ¹ï¼æä½æåï¼ä¼å°è¿ä¸ªçæ¬å·æ´æ°å°æ°æ®çè¡ä¸ï¼äºå¡æ交æåï¼æ°ççæ¬å·ä¹å°±æ´æ°å°äºæ¤æ°æ®è¡ä¸ãè¿æ ·ä¿è¯äºæ¯ä¸ªäºå¡æä½çæ°æ®ï¼é½æ¯äºä¸å½±åçï¼ä¹ä¸åå¨éçé®é¢ã
3MVCCä¸çCRUD
SELECTï¼
ããå½é离级å«æ¯REPEATABLE READæ¶selectæä½ï¼InnoDBæ¯è¡æ°æ®æ¥ä¿è¯å®ç¬¦å两个æ¡ä»¶ï¼
ãã** 1 äºå¡ççæ¬å· 大äºçäº å建è¡çæ¬å·**
ã ** 2 è¡æ°æ®çå é¤çæ¬ æªå®ä¹ æè
å¤§äº äºå¡çæ¬å·**
ã ãè¡å建çæ¬å· äºå¡çæ¬å· è¡å é¤çæ¬å·ã
ã
INSERTï¼
ããInnoDB为è¿ä¸ªæ°è¡ è®°å½ å½åçç³»ç»çæ¬å·ã
ãã
DELETEï¼
ããInnoDBå°å½åçç³»ç»çæ¬å· 设置为 è¿ä¸è¡çå é¤çæ¬å·ã
ãã
UPDATEï¼
ããInnoDBä¼åä¸ä¸ªè¿è¡æ°æ®çæ°æ·è´ï¼è¿ä¸ªæ·è´ççæ¬ä¸º å½åçç³»ç»çæ¬å·ãå®åæ¶ä¹ä¼å°è¿ä¸ªçæ¬å· åå° æ§è¡çå é¤çæ¬éã
ââââââââââââââââ
çæ声æï¼æ¬æ为CSDNå主ã@Autowireãçååæç« ï¼éµå¾ªCC 4.0 BY-SAçæåè®®ï¼è½¬è½½è¯·éä¸åæåºå¤é¾æ¥åæ¬å£°æã
åæé¾æ¥ï¼
https://blog.csdn.net/zs18753479279/article/details/113933252