MySql 知識(shí)點(diǎn)之事務(wù)、索引、鎖原理與用法解析

本文實(shí)例講述了MySql 知識(shí)點(diǎn)之事務(wù)、索引、鎖原理與用法。分享給大家供大家參考，具體如下：

事務(wù)

• 事務(wù)概念

事務(wù)就是一組原子性的SQL查詢，或者說一個(gè)獨(dú)立的工作單元。如果數(shù)據(jù)庫引擎執(zhí)行一組操作語句，那么久執(zhí)行所有的操作，如果其中有任何一條崩潰或其他原因無法執(zhí)行，所有語句將不會(huì)執(zhí)行。也就是說事務(wù)內(nèi)的語句，要么全部執(zhí)行成功，要么全部執(zhí)行失敗。

• 事務(wù)特性ACID
  • 原子性(atomicity)

  一個(gè)事務(wù)被視為最小工作單元，不可拆分，整個(gè)事務(wù)所有的操作要么全部提交成功，要么全部失敗回滾，不可只執(zhí)行部分。

  • 一致性(consistency)

  數(shù)據(jù)庫從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另外一個(gè)一致性的狀態(tài)。數(shù)據(jù)庫某個(gè)狀態(tài)下符合所有的完整性約束的狀態(tài)。

  • 隔離性(isolation)

  通常來說，一個(gè)事務(wù)所做的修改在最終提交前，對(duì)其他事務(wù)是不可見的。此時(shí)應(yīng)該保證各個(gè)事務(wù)要進(jìn)行隔離，事務(wù)之間不可相互干擾。

  • 持久性(durability)

  一旦事務(wù)提交，所有的修改會(huì)永久保存到數(shù)據(jù)庫中。此時(shí)即使系統(tǒng)崩潰，修改的數(shù)據(jù)也不會(huì)丟失。

• 事務(wù)的隔離級(jí)別
  • READ UNCOMMITTED(未提交讀)

  事務(wù)中的修改，即使沒有提交，對(duì)其他事務(wù)也是可見的，事務(wù)可以讀取未提交的數(shù)據(jù)，造成臟讀，也會(huì)造成不可重復(fù)。

  • READ COMMITTED(提交讀)

  大多數(shù)數(shù)據(jù)庫的默認(rèn)級(jí)別是READ COMMITTED（MySQL默認(rèn)REPEATABLE READ），該級(jí)別事務(wù)解決了臟讀，但是會(huì)出現(xiàn)不可重復(fù)讀，因?yàn)閮纱螆?zhí)行同樣的查詢，查詢結(jié)果不一樣。

  • REPEATABLE READ(可重復(fù)讀)

  該級(jí)別解決了臟讀，保證可重復(fù)讀，但是理論上，可重復(fù)讀隔離級(jí)別還是無法解決幻讀，所謂幻讀，指的是黨某個(gè)事物在讀取某個(gè)范圍內(nèi)的記錄時(shí)，另外一個(gè)事務(wù)又在該范圍內(nèi)插入了新的記錄。InnoDB和XtraDB存儲(chǔ)引擎通過多版本并發(fā)控制MVVC解決了幻讀的問題。

  • SERIALIZABLE(可串行化)

  可串行化是隔離最高級(jí)，它強(qiáng)制了事務(wù)串行執(zhí)行，完全避免了幻讀，簡單來說SERIALIZABLE會(huì)在讀取的每一行加鎖，所以會(huì)導(dǎo)致大量的等待超時(shí)和鎖爭用的問題，實(shí)際開發(fā)中很少使用。

索引

• 索引概念

索引是存儲(chǔ)引擎用戶快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)，舉例

SELECT userName FROM user WHERE userId = 1;

如果在userId列上加上索引，則MySQL將使用該索引找到userId的行，也就是說，MySQL先在索引上按值進(jìn)行查找，然后返回所有包含該值的數(shù)據(jù)行。

• 索引方式
  • B-Tree索引

  使用B-Tree數(shù)據(jù)結(jié)構(gòu)來存儲(chǔ)數(shù)據(jù)，大多MySQL引擎都支持該索引。B-Tree索引可以加快訪問數(shù)據(jù)的速度，因?yàn)锽-Tree對(duì)索引列順序組織存儲(chǔ)，范圍查找快。

  • hash索引

  哈希索引基本哈希表實(shí)現(xiàn)，只有精確匹配索引所有列的查詢才有效。對(duì)于每一行數(shù)據(jù)，存儲(chǔ)引擎都會(huì)對(duì)所有的索引列計(jì)算一個(gè)哈希碼，哈希碼值較小。哈希索引將所有的哈希碼存儲(chǔ)在索引中，同時(shí)在哈斯表中保存指向每個(gè)數(shù)據(jù)行的指針。MySQL中只有Memory引擎顯示支持哈希索引。

• 索引類型
  • 普通索引

  主要任務(wù)加快對(duì)數(shù)據(jù)的訪問

  • 唯一索引

  普通索引是允許數(shù)據(jù)重復(fù)的，如果確定了某列數(shù)據(jù)不會(huì)重復(fù)，則可創(chuàng)建唯一索引，唯一索引有兩個(gè)好處，索引更有效：插入新數(shù)據(jù)，如果重復(fù)，MySQL拒絕插入。

  • 主鍵索引

  主鍵本身默認(rèn)創(chuàng)建索引

  • 全文索引

  文本字段上的普通索引只能加快對(duì)出現(xiàn)在字段內(nèi)最前面的字符串進(jìn)行的檢索操作，如果字段里存放的是由幾個(gè)或者多個(gè)單詞構(gòu)成的大段文字，普通索引就不行了，這種場合用全文索引比較合適

  查詢效率：唯一索引>自增主鍵>主鍵

  插入：主鍵>自增主鍵>唯一索引

鎖

在這里我們主要討論下行級(jí)鎖

• 表級(jí)

引擎MyISAM，可以理解為鎖整張表，可以同時(shí)讀，不可以同時(shí)寫。在鎖定期間，其它進(jìn)程無法對(duì)該表進(jìn)行寫操作，如果是寫鎖，則其它進(jìn)程則不允許讀。

• 行級(jí)

引擎INNODB，單獨(dú)一行記錄加鎖，可以同時(shí)讀，不可同時(shí)寫。行級(jí)鎖開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。

• InnoDB鎖行

由于InnoDB預(yù)設(shè)是Row-Level Lock，所以只有[明確]的指定主鍵，MySQL才會(huì)執(zhí)行Row lock，否則MySQL將會(huì)執(zhí)行Table Lock

例1：（明確指定主鍵，并且有此記錄，Row Lock）

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例2: (明確指定主鍵，若查無此記錄，無lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例3: (無主鍵，table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例4: (主鍵不明確，table lock)

SELECT * FROM products WHERE id>'3' FOR UPDATE;

例5: (主鍵不明確，table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE僅適用于InnoDB，且必須在交易區(qū)塊(BEGIN/COMMIT)中才能生效。
注2: 要測試鎖定的狀況，可以利用MySQL的Command Mode ，開二個(gè)視窗來做測試。

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫計(jì)有所幫助。