MySQL事務(wù)與存儲引擎

3.1-數(shù)據(jù)庫事務(wù)

事務(wù)的定義

一系列有序的數(shù)據(jù)庫操作。

• 要么全部成功
• 要么全部回退到操作前的狀態(tài)
• 中間狀態(tài)對其他連接不可見

事務(wù)的基本操作

-- 開啟一個(gè)事務(wù)
start transaction;
-- 或者使用(非標(biāo)準(zhǔn)sql)
begin;
insert into t values (1, 1, 1);
-- 事務(wù)結(jié)束，插入成功
commit;

begin;
insert into t values (2, 1, 1);
insert into t values (3, 1, 1);
insert into t values (4, 1, 1);
-- 事務(wù)結(jié)束建丧，沒有插入數(shù)據(jù)
rollback;

begin;

insert into t values (1, 1, 1);
savepoint a1;
insert into t values (2, 1, 1);
-- 回滾到指定的保存點(diǎn)
rollback to a1;
commit;

自動提交

• autocommit可以在session級別設(shè)置
• 每個(gè)DML操作都自動提交
• DDL永遠(yuǎn)都是自動提交狼电，無法通過rollback回滾

事務(wù)的四個(gè)基本屬性(ACID)

• 原子性(Atomicity)
  一個(gè)事務(wù)包含多個(gè)操作姐帚，這些操作要么全部執(zhí)行旦部，要么全都不執(zhí)行在扰。實(shí)現(xiàn)事務(wù)的原子性霉晕，要支持回滾操作庭再，在某個(gè)操作失敗后（數(shù)據(jù)庫或應(yīng)用發(fā)生異常），回滾到事務(wù)執(zhí)行之前的狀態(tài)牺堰。未提交的事務(wù)都應(yīng)該被回滾拄轻。

• 一致性(Consistency)

• 數(shù)據(jù)的正確性，合理性伟葫，完整性

• 數(shù)據(jù)一致性要符合應(yīng)用應(yīng)該符合的規(guī)則：余額不為負(fù)／交易對象必須先有賬號
  ／用戶賬號不重復(fù)

• 事務(wù)的結(jié)果需要滿足數(shù)據(jù)的一致性約束

• 隔離性(Isolation)
  并發(fā)的事務(wù)是相互隔離的恨搓。數(shù)據(jù)庫的某一事務(wù)在提交完成前，中間的任何數(shù)據(jù)變化對其他的事務(wù)都是不可見的筏养。
  即一個(gè)事務(wù)內(nèi)部的操作及正在操作的數(shù)據(jù)必須封鎖起來奶卓，不被其它企圖進(jìn)行修改的事務(wù)看到（假如并發(fā)交叉執(zhí)行的多個(gè)事務(wù)任意操縱相同的共享對象，可能引起異常）

• 持久性(Durability)
  發(fā)生故障時(shí)撼玄，確保已提交事務(wù)的更新不能丟失夺姑。
  一旦事務(wù)提交完成，對數(shù)據(jù)庫中數(shù)據(jù)的影響必須是永久性的（數(shù)據(jù)成功寫入磁盤 即 持久化成功）掌猛。

實(shí)現(xiàn) 事務(wù)的持久化

• 數(shù)據(jù)文件持久化
  • 隨機(jī)同步刷新(慢)
• 事務(wù)日志持久化與實(shí)例恢復(fù)
  • 順序同步刷新(快) -> 事務(wù)日志
  • 隨機(jī)異步刷新 -> 磁盤
  • 事務(wù)日志 -> 磁盤(實(shí)例恢復(fù))

數(shù)據(jù)庫隔離現(xiàn)象

數(shù)據(jù)庫隔離等級

只有在事務(wù)提交后，其更新結(jié)果才會被其他事務(wù)看見慕蔚。

 ：在一個(gè)事務(wù)中丐黄，對于同一份數(shù)據(jù)的讀取結(jié)果總是相同的，無論是否有其他事務(wù)對這份數(shù)據(jù)進(jìn)行操作

MySQL的事務(wù)隔離級別

• InnoDB默認(rèn)標(biāo)記為可重復(fù)讀
• InnoDB并不是標(biāo)準(zhǔn)定義上的課重復(fù)讀
• InnoDB默認(rèn)在可重復(fù)讀的基礎(chǔ)上避免幻讀

MySQL事務(wù)隔離級別設(shè)置

• 可在global/session/下個(gè)事務(wù)孔飒，級別分別進(jìn)行設(shè)置
• 建議使用Read committed(同Oracle)
• 或者建議使用默認(rèn)的Repeatable read

set tx_isolation = ''
-- 設(shè)置隔離級別

事務(wù)與并發(fā)寫

• 某個(gè)正在更新的記錄再提交或回滾前不能被其他事務(wù)同時(shí)更新

事務(wù)回滾的實(shí)現(xiàn)

• 回滾段(rollback segment)與數(shù)據(jù)前像

3.2-存儲引擎概述

MySQL程序?qū)哟渭軜?gòu)

./sorence/images/01.jpg

MySQL存儲引擎

• 有多種可選方案灌闺，可插拔，可修改存儲引擎
• 基于表選擇使用何種存儲引擎

主要存儲引擎

InnoDB存儲引擎

• 索引組織表
• 支持事務(wù)
• 支持行級鎖
• 數(shù)據(jù)塊緩存
• 日志持久化
• 穩(wěn)定可靠，性能好父腕，線上盡量使用InnoDB

MyISAM存儲引擎

• 堆表
• 不支持事務(wù)
• 只維護(hù)索引緩存池毡熏，表數(shù)據(jù)緩存交給操作系統(tǒng)
• 鎖粒度較大
• 數(shù)據(jù)文件可以直接拷貝，偶爾可能會用上
• 不建議線上業(yè)務(wù)數(shù)據(jù)使用

MWMORY存儲引擎

• 數(shù)據(jù)全內(nèi)存存放侣诵，無法持久化
• 性能較高
• 不支持事務(wù)
• 適合偶爾作為臨時(shí)表使用
• create temporary table tmp (id int) engine = memory ;

BLACKHOLE存儲引擎

• 數(shù)據(jù)不作任何存儲
• 利用MySQL Replicate痢法，充當(dāng)日志服務(wù)器
• 在MySQL Replicate環(huán)境中充當(dāng)代理主

TokuDB

• 分形樹存儲結(jié)構(gòu)
• 支持事務(wù)
• 行鎖
• 壓縮效率較高
• 適合大批量insert的場景

MySQL Cluster

• 多主分布式集群
• 數(shù)據(jù)節(jié)點(diǎn)間冗余，高可用
• 支持事務(wù)
• 設(shè)計(jì)上易于擴(kuò)展
• 面向未來杜顺，線上慎用

改變表的存儲引擎

alter table m ENGINE=innodb;

3.3-InnoDB存儲引擎

InnoDB存儲引擎體系架構(gòu)

/sorence/images/02.png

InnoDB相關(guān)的磁盤文件

InnoDB系統(tǒng)表空間文件

• ibdata1里存放了什么:
  • 回滾段
  • 所有InnoDB表元數(shù)據(jù)信息
  • Double Write, Insert buffer dump等等....
• 自動擴(kuò)展機(jī)制

InnoDB與磁盤文件有關(guān)的參數(shù)

InnoDB數(shù)據(jù)文件存儲結(jié)構(gòu)

• 索引組織表(聚簇表)
• 根據(jù)表邏輯主鍵排序
• 數(shù)據(jù)節(jié)點(diǎn)每頁16K
• 根據(jù)主鍵尋址速度很快
• 主鍵值遞增的insert插入效率較好
• 主鍵值隨機(jī)insert插入效率差
• 因此财搁，InnoDB表必須指定主鍵，建議使用自增數(shù)字

InnoDB數(shù)據(jù)塊緩存池

• 數(shù)據(jù)的讀寫需要經(jīng)過緩存
• 數(shù)據(jù)以整頁(16K)為單位讀取到緩存中
• 緩存中的數(shù)據(jù)以LRU策略換出
• IO效率高躬络，性能好

InnoDB Buffer Pool相關(guān)參數(shù)

InnoDB數(shù)據(jù)持久化與事務(wù)日志

• 事務(wù)日志實(shí)時(shí)持久化
• 內(nèi)存變化數(shù)據(jù)(臟數(shù)據(jù))增量異步刷出到磁盤
• 實(shí)例故障靠重放日志恢復(fù)
• 性能好尖奔，可靠，恢復(fù)快

InnoDB日志持久化相關(guān)參數(shù)

InnoDB行級鎖

• 寫不阻塞讀
• 不同行間的寫互相不阻塞
• 并發(fā)性能好

InnoDB與事務(wù)ACID

• 事務(wù)ACID特性完整支持
  • 回滾段失敗回滾
  • 支持主外鍵約束
  • 事務(wù)版本+回滾段=MVCC
  • 事務(wù)日志持久化
• 默認(rèn)可重復(fù)讀隔離級別馁菜，可以調(diào)整

3.4-InnoDB事務(wù)鎖

什么是計(jì)算機(jī)程序鎖

• 計(jì)算機(jī)程序鎖
  • 控制對共享資源進(jìn)行并發(fā)訪問
  • 保護(hù)數(shù)據(jù)的完整性和一致性

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

• 分為兩個(gè)大類

• 我們主要關(guān)心的是事務(wù)鎖

數(shù)據(jù)庫事務(wù)并發(fā)

• 對同一行記錄的修改必須串行化

事務(wù)鎖粒度

• 行鎖
  • InnoDB, Oracle
• 頁鎖
  • SQL Server
• 表鎖
  • MyISAM, Memory
• 鎖升級

InnoDB存儲引擎中的鎖模式與粒度

• 四種基本鎖模式
  • 共享鎖(S) - 讀鎖 - 行鎖
  • 排他鎖(X) - 寫鎖 - 行鎖
  • 意向共享鎖(IS) - 表級
  • 意向排他鎖(IX) - 表級
• 意向鎖
  • 意向鎖總是自動先加茴扁，并且意向鎖自動加自動釋放
  • 意向鎖提示數(shù)據(jù)庫這個(gè)session將要在接下來施加何種鎖
  • 意向鎖和X/S鎖級別不同，除了阻塞全表級別的X/S鎖外其他任何鎖

InnoDB鎖模式互斥

/sorence/images/03.png

數(shù)據(jù)庫加鎖操作

• 一般的select語句不加任何鎖汪疮，也不會被任何事物鎖阻塞
  • 讀的隔離性由MVCC確保
• S鎖
  • 手動：select * from tb_test lock in share mode;
  • 自動：insert前
• X鎖
  • 手動：select * from tb_test lock for update;
  • 自動：update峭火，delete前

InnoDB行鎖的實(shí)現(xiàn)

• 通過索引項(xiàng)加鎖實(shí)現(xiàn)
  • 只有條件走索引才能實(shí)現(xiàn)行級鎖
  • 索引上有重復(fù)值，可能鎖住多個(gè)記錄
  • 查詢有多個(gè)索引可以走智嚷，可以對不同索引加鎖
  • 是否對索引加鎖實(shí)際上取決于MySQL執(zhí)行計(jì)劃
• 自增主鍵做條件更新卖丸，性能最好

沒有索引的話會對整張表加鎖。

InnoDB的gap lock

• 什么是幻讀
• gap lock消滅幻讀
  • InnoDB消滅幻讀僅僅為了確保statement模式replicate的主從一致性
• 小心gap lock
• 自增主鍵做條件更新盏道，性能最好

死鎖

• 什么是死鎖

  • A稍浆、B兩個(gè)事務(wù)，A先更新t1猜嘱，同時(shí)B更新t2衅枫，A再更新t2，B再更新t1就發(fā)生了死鎖泉坐。

• 死鎖數(shù)據(jù)庫自動解決

  • 數(shù)據(jù)庫挑選沖突事務(wù)中回滾代價(jià)較小的事務(wù)回滾

• 死鎖預(yù)防

  • 單表死鎖可以根據(jù)批量更新里的更新條件排序
  • 可能沖突的跨表事務(wù)盡量避免并發(fā)
  • 盡量縮短事務(wù)長度

業(yè)務(wù)邏輯加鎖

• 業(yè)務(wù)流程中的悲觀鎖

  • 任何的并發(fā)修改都有可能造成我們的業(yè)務(wù)邏輯最終的錯(cuò)誤为鳄，在事務(wù)流程中一開始就加鎖裳仆，最后釋放

• 如何縮短鎖的時(shí)間