MySQL事務(wù)

MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說，在一個(gè)商城系統(tǒng)中，用戶執(zhí)行購買操作，那么用戶訂單中應(yīng)該加一條，庫存要減一條，如果這兩步由于意外只進(jìn)行了其中一步那么就會(huì)發(fā)生很大的問題。而事務(wù)可以很好的解決這個(gè)問題。

事務(wù)是數(shù)據(jù)庫處理操作，其中執(zhí)行就好像它是一個(gè)單一的一組有序的工作單元。換言之在組內(nèi)每個(gè)單獨(dú)的操作是成功的，那么一個(gè)事務(wù)才是完整的。如果事務(wù)中的任何操作失敗，整個(gè)事務(wù)將失敗。

事務(wù)性質(zhì)：

原子性：確保工作單位中所有操作都成功完成；否則，事務(wù)被中止，在失敗時(shí)會(huì)回滾到事務(wù)操作以前的狀態(tài)。

一致性：可確保數(shù)據(jù)庫在正確的更改狀態(tài)進(jìn)行一個(gè)成功的提交事務(wù)。

隔離性：使事務(wù)相互獨(dú)立的操作。

持久性：確保了提交事務(wù)的結(jié)果或系統(tǒng)故障情況下仍然存在作用。

TCL(事務(wù)控制語言)：

begin;

操作;commit;

BEGIN或STARTTRANSACTION; #顯式地開啟一個(gè)事務(wù)COMMIT;或COMMIT WORK; #二者等階。COMMIT會(huì)提交事務(wù)并使已對數(shù)據(jù)庫進(jìn)行的所有修改成為永久性的。未COMMIT的操作都存放在內(nèi)存中，僅當(dāng)前客戶端可以查看到，其他客戶端看不到，當(dāng)前客戶端關(guān)閉后就清空了ROLLBACK;或ROLLBACK WORK; #二者等階。回滾會(huì)結(jié)束用戶的事務(wù)，并撤銷正在進(jìn)行的所有未提交的修改SET AUTOCOMMIT=0#禁止自動(dòng)提交 隱式開啟事務(wù)SET AUTOCOMMIT=1 #開啟自動(dòng)提交

事務(wù)并發(fā)的問題：

1.臟讀：事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)，然后B回滾操作，那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)

2.不可重復(fù)讀：事務(wù)A多次讀取同一數(shù)據(jù)，事務(wù)B在事務(wù)A多次讀取過程中，對數(shù)據(jù)作了更新并提交 ，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時(shí)結(jié)果不一致

3.幻讀：系統(tǒng)管理員A將數(shù)據(jù)庫中所有學(xué)生的成績從具體分?jǐn)?shù)改為ABCDE等級，但是系統(tǒng)管理員B就在這個(gè)時(shí)候插入了一條具體分?jǐn)?shù)的記錄，當(dāng)系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條沒有改過來，就好像　發(fā)生了幻覺一樣

各個(gè)隔離級別情況：

#查看隔離級別select @@session.tx_isolation;

#設(shè)置隔離級別set session transaction isolation level read uncommitted

隔離級別

臟讀可能性

不可重復(fù)讀可能性

幻讀可能性

未提交讀 READ UNCOMMITED

是

是

是

不可重復(fù)讀 READ COMMITED

否

是

是

可重復(fù)讀 REPEATABLE READ

否

否

是

串行化 SERIALIZABLE

否

否

否

數(shù)據(jù)庫鎖

mysql不同存儲(chǔ)引擎支持不同鎖機(jī)制，innodb支持表行級鎖默認(rèn)行級鎖，memory采用表級鎖，bdb采用頁面鎖支持表級鎖。

表級鎖：開銷小，加鎖快，不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。不支持事務(wù)。

頁面鎖：開銷和加鎖時(shí)間介于前后者之間，會(huì)出現(xiàn)死鎖；鎖定粒度介于前后者之間，并發(fā)度一般。

行級鎖：開銷大，加鎖慢，會(huì)出現(xiàn)死鎖；鎖定粒度小，發(fā)生鎖的沖突概率最低，并發(fā)度也最高。

表級鎖：兩種模式 共享鎖(讀鎖)與獨(dú)占鎖(寫鎖，排他鎖)，表級鎖引擎：MyISAM MEMORY

共享鎖：在讀的時(shí)候上鎖，所有人都可以訪問不阻塞其他用戶對同一表讀請求，但阻塞同一表的寫操作包括自己；自己如果加了讀鎖，更新訪問其他表會(huì)提示錯(cuò)誤；加了讀鎖之后不能再加寫鎖

獨(dú)占鎖：上鎖之后其他人不能訪問，阻塞其他用戶對同一表的讀和寫操作，獨(dú)占鎖優(yōu)先級別高于共享鎖；自己加了寫鎖可以讀寫表中記錄，但更新訪問其他表都會(huì)提示錯(cuò)誤

MyISAM在執(zhí)行查詢語句時(shí)會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行更新操作前加寫鎖，這個(gè)過程一般不需要用戶干預(yù)。

#加鎖

lock tables table_nameread [local];lock tables table_name write [local];

#多表加鎖

lock tables table_name[table_name] read [local];lock tables table_name [table_name] write [local];

#釋放鎖

unlock tables;

#查詢表級鎖爭用情況

show statuslike 'table%';

show statuslike '%lock%';當(dāng)waited immediate值比較大是說明阻塞嚴(yán)重

show processlist; #查看哪些sql在在等待鎖

showopentables; #當(dāng)前被鎖住的表以及鎖的次數(shù)

#并發(fā)插入

myisam存儲(chǔ)引擎有一個(gè)系統(tǒng)變量concurrent_insert.專門用以控制其并發(fā)插入行為，其值為NEVER0、AUTO1(默認(rèn))、ALAWAYS2。0：不允許并發(fā)插入 ，1：如果表中沒有空洞(表中沒有被刪除的行)myisam允許在一個(gè)進(jìn)程讀表的同時(shí)，另一個(gè)進(jìn)程從表尾插入記錄，2：無論表中有沒有空洞，都允許在表尾插入記錄

#讀寫鎖優(yōu)先級

max_write_lock_count=1#設(shè)置寫鎖的最多次數(shù),當(dāng)系統(tǒng)處理一個(gè)寫操作后就會(huì)暫停寫操作給讀操作執(zhí)行機(jī)會(huì)

#降低寫操作優(yōu)先級，給讀操作更高優(yōu)先級

low_priority_updates=1 sql_low_priority_updates=1 在用寫操作時(shí)要加low_priority關(guān)鍵字#視場景而定，讀場景更重要或更多時(shí)如此設(shè)置

如何優(yōu)化表所？

concurrent_insert設(shè)置2，總是允許并發(fā)插入，但是要定期OPTIMIZE TABLE整理空間碎片;視情況設(shè)置寫優(yōu)先級；視情況設(shè)置寫內(nèi)存，解決批量插入數(shù)據(jù)(如新聞系統(tǒng)更新)場景中。

行級鎖：引擎InnoDB，模式包含 共享鎖(S)，排它鎖(X)，意向共享鎖(IS)，意向排它鎖(IX) 如果一個(gè)事務(wù)請求的鎖模式與當(dāng)前的鎖兼容，innoDB就將請求的鎖授予該事物；反之，如果兩者不兼容，該事物就要等待鎖釋放

行級鎖特點(diǎn)：innoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，只有通過索引條件(例如id)檢索數(shù)據(jù)，innoDB才使用行級鎖，佛則將使用表鎖；意向鎖是innoDB自動(dòng)加的，不需要用戶干預(yù)，對于寫操作(insert update delete)innodb會(huì)自動(dòng)給涉及數(shù)據(jù)加排它鎖，對于select InnoDB不會(huì)加任何鎖

排它鎖(X)意向排它鎖(IX)共享鎖(S)意向共享鎖(IS)

排它鎖(X)

沖突

沖突

沖突

沖突

意向排它鎖(IX)

沖突

兼容

沖突

兼容

共享鎖(S)

沖突

沖突

兼容

兼容

意向共享鎖(IS)

沖突

兼容

兼容

兼容

#加鎖SELECT * FROM table_name WHERE ... LOCK INSHARE MODE #主動(dòng)添加共享鎖(S)SELECT * FROM table_name WHERE ... FOR UPDATE#主動(dòng)添加獨(dú)占鎖(X)

#釋放鎖commit;或rollback;

當(dāng)使用行排他鎖寫數(shù)據(jù)時(shí)，其他人無法操作本條數(shù)據(jù)；

當(dāng)給一條數(shù)據(jù)添加了排它鎖，其他人對這條數(shù)據(jù)沒有任何權(quán)限，但并不影響其他人對其他數(shù)據(jù)操作；

在InnoDB默認(rèn)的隔離方式下，操作者不提交，操作數(shù)據(jù)只保存在內(nèi)存里，另一用戶可以查詢，查詢到的是舊值；

即使字段家里索引在使用時(shí)自己變了類型，索引失效會(huì)加表鎖；

間隙鎖：比如有124三個(gè)數(shù)據(jù)，操作>1的數(shù)據(jù)，此時(shí)添加3或者5都是不可以的，所以要明確范圍防止間隙鎖

如何優(yōu)化行級鎖：

盡量使用較低的隔離級別(新手忽略)；精心設(shè)計(jì)索引，并盡量使用索引訪問數(shù)據(jù)，使加鎖更精確從而減少鎖沖突的機(jī)會(huì)

選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的幾率也小

給記錄集手動(dòng)加鎖時(shí)，最好一次性請求足夠級別的鎖

盡量使用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對并發(fā)插入的影響

對于一些特定事務(wù)，可以使用表鎖提高速度并減少死鎖可能

數(shù)據(jù)庫優(yōu)化操作

優(yōu)化成本 硬件>系統(tǒng)配置>數(shù)據(jù)庫表結(jié)構(gòu)>SQL語句及索引

優(yōu)化效果 SQL語句及索引

MySQL邏輯架構(gòu)：

客戶端->連接線程處理->查詢緩存、分析器、優(yōu)化器->存儲(chǔ)引擎

索引底層實(shí)現(xiàn)：B樹

myisam存儲(chǔ)是數(shù)據(jù)的地址 innodb存儲(chǔ)的是索引值，所以索引不宜過長

explain參數(shù)詳解：

select語句執(zhí)行順序：執(zhí)行順序：先where...group by ... having 再 select ... from ... 再 distinct ... order by ... limit ...

#使用方式:

explainselect * fromdemo;

#參數(shù):

#id 執(zhí)行順序

id相同時(shí)順序從被查詢表數(shù)據(jù)量少至多(都一樣的話按照書寫順序)，子查詢時(shí)id由外到里自增，先執(zhí)行大的

#select_type 查詢中每個(gè)select子句的類型

SIMPLE:簡單SELECT(不使用UNION或子查詢)PRIMARY:最外面的SELECTUNION:UNION中的第二個(gè)或后面的SELECT語句

DEPENDENTUNION:UNION中的第二個(gè)或后面的SELECT語句,取決于外面的查詢UNION RESULT:UNION的結(jié)果

SUBQUERY:子查詢中的第一個(gè)SELECT

DEPENDENT SUBQUERY:子查詢中的第一個(gè)SELECT,取決于外面的查詢

DERIVED:派生表(FROM子句的子查詢)

#table本次查詢的表名，或派生表

#type mysql在表中的訪問類型ALL: 遍歷全表，目標(biāo)不帶索引<

index: 遍歷全表索引樹,不能用in會(huì)使索引失效

NULL#possible_keys 本查詢可能用的索引

#key本查詢真實(shí)用的索引

#key_len 索引在內(nèi)存中占的長度(輕易不要給varchar加索引)

#ref 指定的條件類型

#rows 當(dāng)前語句查到的行數(shù)

#ExtraDistinct:MySQL發(fā)現(xiàn)第1個(gè)匹配行后,停止為當(dāng)前的行組合搜索更多的行。Not exists:MySQL能夠?qū)Σ樵冞M(jìn)行LEFT JOIN優(yōu)化,發(fā)現(xiàn)1個(gè)匹配LEFT JOIN標(biāo)準(zhǔn)的行后,不再為前面的的行組合在該表內(nèi)檢查更多的行。

range checkedfor each record (indexmap: ):MySQL沒有發(fā)現(xiàn)好的可以使用的索引,但發(fā)現(xiàn)如果來自前面的表的列值已知,可能部分索引可以使用。

Using filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行。

Usingindex:從只使用索引樹中的信息而不需要進(jìn)一步搜索讀取實(shí)際的行來檢索表中的列信息，代表性能不錯(cuò)

Usingtemporary:為了解決查詢,MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來容納結(jié)果。

Usingwhere:WHERE子句用于限制哪一個(gè)行匹配下一個(gè)表或發(fā)送到客戶，性能一般

Using sort_union(...), Usingunion(...), Using intersect(...):這些函數(shù)說明如何為index_merge聯(lián)接類型合并索引掃描。

Usingindex for group-by:類似于訪問表的Using index方式,Using index for group-by表示MySQL發(fā)現(xiàn)了一個(gè)索引,可以用來查 詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜索硬盤訪問實(shí)際的表。

IMPOSSIBLE :不可能的where語句如where id=1 and id=2

mysql優(yōu)化方法：

1.通過使用explain命令分析sql語句的運(yùn)行效率

2.通過開啟慢查詢?nèi)罩静榭葱事膕ql語句

單多表sql優(yōu)化手段：

給使用頻繁的字段加索引，調(diào)整索引順序最佳左前綴原則，刪除多余干擾索引，調(diào)整查詢條件對索引有干擾的語句放最后

多表sql額外優(yōu)化手段：

1.小表驅(qū)動(dòng)大表(小表在左邊，where小表.x=大表.y)

2.left join 給左邊表加索引，right join 給右邊表加索引

注意：

1.不要將索引作為函數(shù)參數(shù)或表達(dá)式的一部分，這樣會(huì)讓索引失效;索引不要進(jìn)行類型轉(zhuǎn)化否則失效

2.復(fù)合索引應(yīng)該遵循最佳左前綴，不要用or,in,!= < >關(guān)鍵字否則失效

3.及時(shí)刪除冗長，不常用的索引

4.like查詢時(shí)盡量不要使用左邊%引起索引失效

系統(tǒng)級別優(yōu)化：

主從復(fù)制，讀寫分離，負(fù)載均衡

其他優(yōu)化：

選盡量小的數(shù)據(jù)類型，列設(shè)置not null，加unsigned不允許加正負(fù)這樣可以使正數(shù)上線多一倍，存儲(chǔ)時(shí)間最好用TIMESTAMP使用4個(gè)字節(jié)存儲(chǔ)，大多數(shù)情況下沒有枚舉類型的必要，表的列不要太不要超過10個(gè)字段多影響內(nèi)存數(shù)據(jù)類型小而簡單

與50位技術(shù)專家面對面20年技術(shù)見證，附贈(zèng)技術(shù)全景圖

總結(jié)

以上是生活随笔為你收集整理的mysql 事务 注意 优化_MySQL入门详解——事务、锁、优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。