MySql 知識點之事務、索引、鎖原理與用法解析

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

事務

• 事務概念

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

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

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

  • 一致性(consistency)

  數據庫從一個一致性的狀態轉換到另外一個一致性的狀態。數據庫某個狀態下符合所有的完整性約束的狀態。

  • 隔離性(isolation)

  通常來說，一個事務所做的修改在最終提交前，對其他事務是不可見的。此時應該保證各個事務要進行隔離，事務之間不可相互干擾。

  • 持久性(durability)

  一旦事務提交，所有的修改會永久保存到數據庫中。此時即使系統崩潰，修改的數據也不會丟失。

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

  事務中的修改，即使沒有提交，對其他事務也是可見的，事務可以讀取未提交的數據，造成臟讀，也會造成不可重復。

  • READ COMMITTED(提交讀)

  大多數數據庫的默認級別是READ COMMITTED（MySQL默認REPEATABLE READ），該級別事務解決了臟讀，但是會出現不可重復讀，因為兩次執行同樣的查詢，查詢結果不一樣。

  • REPEATABLE READ(可重復讀)

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

  • SERIALIZABLE(可串行化)

  可串行化是隔離最高級，它強制了事務串行執行，完全避免了幻讀，簡單來說SERIALIZABLE會在讀取的每一行加鎖，所以會導致大量的等待超時和鎖爭用的問題，實際開發中很少使用。

索引

• 索引概念

索引是存儲引擎用戶快速找到記錄的一種數據結構，舉例

SELECT userName FROM user WHERE userId = 1;

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

• 索引方式
  • B-Tree索引

  使用B-Tree數據結構來存儲數據，大多MySQL引擎都支持該索引。B-Tree索引可以加快訪問數據的速度，因為B-Tree對索引列順序組織存儲，范圍查找快。

  • hash索引

  哈希索引基本哈希表實現，只有精確匹配索引所有列的查詢才有效。對于每一行數據，存儲引擎都會對所有的索引列計算一個哈希碼，哈希碼值較小。哈希索引將所有的哈希碼存儲在索引中，同時在哈斯表中保存指向每個數據行的指針。MySQL中只有Memory引擎顯示支持哈希索引。

• 索引類型
  • 普通索引

  主要任務加快對數據的訪問

  • 唯一索引

  普通索引是允許數據重復的，如果確定了某列數據不會重復，則可創建唯一索引，唯一索引有兩個好處，索引更有效：插入新數據，如果重復，MySQL拒絕插入。

  • 主鍵索引

  主鍵本身默認創建索引

  • 全文索引

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

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

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

鎖

在這里我們主要討論下行級鎖

• 表級

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

• 行級

引擎INNODB，單獨一行記錄加鎖，可以同時讀，不可同時寫。行級鎖開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。

• InnoDB鎖行

由于InnoDB預設是Row-Level Lock，所以只有[明確]的指定主鍵，MySQL才會執行Row lock，否則MySQL將會執行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，且必須在交易區塊(BEGIN/COMMIT)中才能生效。
注2: 要測試鎖定的狀況，可以利用MySQL的Command Mode ，開二個視窗來做測試。

更多關于MySQL相關內容感興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL常用函數大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數據庫鎖相關技巧匯總》

希望本文所述對大家MySQL數據庫計有所幫助。