數(shù)據(jù)庫MySQL學(xué)習(xí)系列三

三．MYSQL事務(wù)與存儲引擎
3.1-數(shù)據(jù)庫事務(wù)
什么是事務(wù)
?一系列有序的數(shù)據(jù)庫操作：
o要么全部成功
o要么全部回退到操作前的狀態(tài)
o中間狀態(tài)對其他連接不可見
?事務(wù)的基本操作： | 基本操作 | 說明 | | :------------- | :------------- | | start transaction | 開始事務(wù) | | commit | 提交(全部完成) | | rollback | 回滾(回到初始狀態(tài)) |
-- 開啟一個事務(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);-- 回滾到指定的保存點rollback to a1;commit;
自動提交
?autocommit可以在session級別設(shè)置
?每個DML操作都自動提交
?DDL永遠(yuǎn)都是自動提交，無法通過rollback回滾
事務(wù)的四個基本屬性(ACID)
?原子性(Atomicity)
?一致性(Consistency)
?隔離性(Isolation)
?持久性(Durability)
事務(wù)的原子性
?包含在事務(wù)中的操作要么全部被執(zhí)行，要么都不執(zhí)行
?中途數(shù)據(jù)庫或應(yīng)用發(fā)生異常，未提交的事務(wù)都應(yīng)該被回滾
事務(wù)的一致性
?數(shù)據(jù)的正確性，合理性，完整性
?數(shù)據(jù)一致性應(yīng)該符合應(yīng)用需要規(guī)則：
o余額不能是負(fù)數(shù)
o交易對象必須先有賬號
o用戶賬號不能重復(fù)
?事務(wù)的結(jié)果需要滿足數(shù)據(jù)的一致性約束
事物的持久性
?提交完成的事務(wù)對數(shù)據(jù)庫的影響必須是永久性的
o數(shù)據(jù)庫異常不會丟失事務(wù)更新
o通常認(rèn)為成功寫入磁盤的數(shù)據(jù)即為持久化成功
事務(wù)的持久化的實現(xiàn)
?數(shù)據(jù)文件持久化
o隨機同步刷新(慢)
?事務(wù)日志持久化與實例恢復(fù)
o順序同步刷新(快) -> 事務(wù)日志
o隨機異步刷新 -> 磁盤
o事務(wù)日志 -> 磁盤(實例恢復(fù))
事務(wù)的隔離性
?數(shù)據(jù)庫事務(wù)在提交完成前，中間的任何數(shù)據(jù)變化對其他的事務(wù)都是不可見的。
數(shù)據(jù)庫隔離現(xiàn)象
隔離現(xiàn)象 描述
臟讀(Dirty Read) 事務(wù)B讀到事務(wù)A尚未提交的數(shù)據(jù)變更
不可重復(fù)讀(NonRepeatable Read) 事務(wù)B讀取前后兩次讀取一條記錄之間該記錄被事務(wù)A修改并提交，于是事務(wù)B讀到了不一樣的結(jié)果
幻讀(Phantom Read) 事務(wù)B按條件匹配到了若干行記錄并修改。但是由于修改過程中事務(wù)A新插入了符合條件記錄，導(dǎo)致B更新完成后發(fā)現(xiàn)仍有符合條件卻未被更新的記錄。
數(shù)據(jù)庫隔離等級
隔離等級 臟讀 不可重復(fù)讀 幻讀
未提交讀 可能 可能 可能
已提交讀 不可能 可能 可能
可重復(fù)讀 不可能 不可能 可能
可串行化讀 不可能 不可能 不可能
MySQL的事務(wù)隔離級別
?InnoDB默認(rèn)標(biāo)記為可重復(fù)讀
?InnoDB并不是標(biāo)準(zhǔn)定義上的課重復(fù)讀
?InnoDB默認(rèn)在可重復(fù)讀的基礎(chǔ)上避免幻讀
MySQL事務(wù)隔離級別設(shè)置
?可在global/session/下個事務(wù)，級別分別進行設(shè)置
?建議使用Read committed(同Oracle)
?或者建議使用默認(rèn)的Repeatable read
set tx_isolation = ''-- 設(shè)置隔離級別
事務(wù)與并發(fā)寫
?某個正在更新的記錄再提交或回滾前不能被其他事務(wù)同時更新
事務(wù)回滾的實現(xiàn)
?回滾段(rollback segment)與數(shù)據(jù)前像
3.2-存儲引擎概述
MySQL程序?qū)哟渭軜?gòu)

我們提供的服務(wù)有：成都網(wǎng)站設(shè)計、成都做網(wǎng)站、微信公眾號開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、墾利ssl等。為上千多家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的墾利網(wǎng)站制作公司

MySQL存儲引擎
?有多種可選方案，可插拔，可修改存儲引擎
?基于表選擇使用何種存儲引擎
主要存儲引擎
存儲引擎 常用度 支持事務(wù)
InnoDB 主要，推薦 是
MyISAM 古老，偶爾有用，系統(tǒng)表 否
MEMORY 偶爾臨時表有用，純內(nèi)存 否
BLACKHOLE 不用來存放數(shù)據(jù)，個別特殊用處 否
TokuDB 新穎，個別特殊場景有奇效 是
Cluster 新穎，分布式，內(nèi)存，線上不要用 是
InnoDB存儲引擎
?索引組織表
?支持事務(wù)
?支持行級鎖
?數(shù)據(jù)塊緩存
?日志持久化
?穩(wěn)定可靠，性能好，線上盡量使用InnoDB
MyISAM存儲引擎
?堆表
?不支持事務(wù)
?只維護索引緩存池，表數(shù)據(jù)緩存交給操作系統(tǒng)
?鎖粒度較大
?數(shù)據(jù)文件可以直接拷貝，偶爾可能會用上
?不建議線上業(yè)務(wù)數(shù)據(jù)使用
MWMORY存儲引擎
?數(shù)據(jù)全內(nèi)存存放，無法持久化
?性能較高
?不支持事務(wù)
?適合偶爾作為臨時表使用
?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é)點間冗余，高可用
?支持事務(wù)
?設(shè)計上易于擴展
?面向未來，線上慎用
改變表的存儲引擎
alter table m ENGINE=innodb;
3.3-InnoDB存儲引擎
InnoDB存儲引擎體系架構(gòu)

InnoDB相關(guān)的磁盤文件
文件 名稱 數(shù)量 位置
系統(tǒng)表空間 ibdata1 一個實例一個 innodb_data_home_dir
日志文件 ib_logfile0/1 一個實例兩個(可配置) innodb_log_group_home_dir
表定義文件 表名.frm 每張表一個 Schema目錄下
表數(shù)據(jù)文件 表名.ibd 如果innodb_file_per_table = 1, 則每張表一個 Schema目錄下
InnoDB系統(tǒng)表空間文件
?ibdata1里存放了什么:
o回滾段
o所有InnoDB表元數(shù)據(jù)信息
oDouble Write, Insert buffer dump等等....
?自動擴展機制
InnoDB與磁盤文件有關(guān)的參數(shù)
參數(shù) 樣例值 備注
innodb_data_home_dir /data/mysql/node_1 數(shù)據(jù)主目錄
innodb_log_group_home_dir /data/mysql/node_1 一般同上
innodb_data_file_path ibdata1:512M:autoextned 請開啟autoextned
innodb_autoextend_increment 128 MB,勿太大或太小
innodb_file_per_table 1 強烈建議開啟
innodb_log_file_size 100MB 性能相關(guān)
innodb_log_files_in_group 2 性能相關(guān)
InnoDB數(shù)據(jù)文件存儲結(jié)構(gòu)
?索引組織表(聚簇表)
?根據(jù)表邏輯主鍵排序
?數(shù)據(jù)節(jié)點每頁16K
?根據(jù)主鍵尋址速度很快
?主鍵值遞增的insert插入效率較好
?主鍵值隨機insert插入效率差
?因此，InnoDB表必須指定主鍵，建議使用自增數(shù)字
InnoDB數(shù)據(jù)塊緩存池
?數(shù)據(jù)的讀寫需要經(jīng)過緩存
?數(shù)據(jù)以整頁(16K)為單位讀取到緩存中
?緩存中的數(shù)據(jù)以LRU策略換出
?IO效率高，性能好
InnoDB Buffer Pool相關(guān)參數(shù)
參數(shù) 樣例值 備注
innodb_buffer_pool_size 10G 根據(jù)總物理內(nèi)存設(shè)置
InnoDB數(shù)據(jù)持久化與事務(wù)日志
?事務(wù)日志實時持久化
?內(nèi)存變化數(shù)據(jù)(臟數(shù)據(jù))增量異步刷出到磁盤
?實例故障靠重放日志恢復(fù)
?性能好，可靠，恢復(fù)快
InnoDB日志持久化相關(guān)參數(shù)
參數(shù) 樣例值 備注
innodb_flush_log_at_trx_commit 1 可選：0：每隔1s寫入并持久化一次日志。1：每次commit都寫入并持久化日志。2：每次提交日志寫到內(nèi)存，每1s持久化一次
InnoDB行級鎖
?寫不阻塞讀
?不同行間的寫互相不阻塞
?并發(fā)性能好
InnoDB與事務(wù)ACID
?事務(wù)ACID特性完整支持
o回滾段失敗回滾
o支持主外鍵約束
o事務(wù)版本+回滾段=MVCC
o事務(wù)日志持久化
?默認(rèn)可重復(fù)讀隔離級別，可以調(diào)整
3.4-InnoDB事務(wù)鎖
什么是計算機程序鎖
?計算機程序鎖
o控制對共享資源進行并發(fā)訪問
o保護數(shù)據(jù)的完整性和一致性
數(shù)據(jù)庫中的鎖
?分為兩個大類
lock latch/mutex
對象 事務(wù) 線程
保護 數(shù)據(jù)庫邏輯內(nèi)容 內(nèi)存數(shù)據(jù)結(jié)構(gòu)
持續(xù)時間 事務(wù)過程中 臨界資源爭搶
?我們主要關(guān)心的是事務(wù)鎖
數(shù)據(jù)庫事務(wù)并發(fā)
?對同一行記錄的修改必須串行化
事務(wù)鎖粒度
?行鎖
oInnoDB, Oracle
?頁鎖
oSQL Server
?表鎖
oMyISAM, Memory
?鎖升級
InnoDB存儲引擎中的鎖模式與粒度
?四種基本鎖模式
o共享鎖(S) - 讀鎖 - 行鎖
o排他鎖(X) - 寫鎖 - 行鎖
o意向共享鎖(IS) - 表級
o意向排他鎖(IX) - 表級
?意向鎖
o意向鎖總是自動先加，并且意向鎖自動加自動釋放
o意向鎖提示數(shù)據(jù)庫這個session將要在接下來施加何種鎖
o意向鎖和X/S鎖級別不同，除了阻塞全表級別的X/S鎖外其他任何鎖
InnoDB鎖模式互斥

數(shù)據(jù)庫加鎖操作
?一般的select語句不加任何鎖，也不會被任何事物鎖阻塞
o讀的隔離性由MVCC確保
?S鎖
o手動：select from tb_test lock in share mode;
o自動：insert前
?X鎖
o手動：select from tb_test lock for update;
o自動：update，delete前
InnoDB行鎖的實現(xiàn)
?通過索引項加鎖實現(xiàn)
o只有條件走索引才能實現(xiàn)行級鎖
o索引上有重復(fù)值，可能鎖住多個記錄
o查詢有多個索引可以走，可以對不同索引加鎖
o是否對索引加鎖實際上取決于MySQL執(zhí)行計劃
?自增主鍵做條件更新，性能最好
沒有索引的話會對整張表加鎖。
InnoDB的gap lock
?什么是幻讀
?gap lock消滅幻讀
oInnoDB消滅幻讀僅僅為了確保statement模式replicate的主從一致性
?小心gap lock
?自增主鍵做條件更新，性能最好
死鎖
?
什么是死鎖
?
oA、B兩個事務(wù)，A先更新t1，同時B更新t2，A再更新t2，B再更新t1就發(fā)生了死鎖。
?
死鎖數(shù)據(jù)庫自動解決
?
o數(shù)據(jù)庫挑選沖突事務(wù)中回滾代價較小的事務(wù)回滾
?
死鎖預(yù)防
?
o單表死鎖可以根據(jù)批量更新里的更新條件排序
o可能沖突的跨表事務(wù)盡量避免并發(fā)
o盡量縮短事務(wù)長度
業(yè)務(wù)邏輯加鎖
?
業(yè)務(wù)流程中的悲觀鎖
?
o任何的并發(fā)修改都有可能造成我們的業(yè)務(wù)邏輯最終的錯誤，在事務(wù)流程中一開始就加鎖，最后釋放
?
如何縮短鎖的時間
?