Mysql數據庫（五）——mysql事務及引擎

• 一、事務
• • 1、事務的概念
  • 2、事務的ACID特點
  • • ①、原子性（Atomicity）
    • ②、一致性（Consistency）
    • ③、隔離性（Isolation）
    • ④、持久性（Durability）
  • 3、事務間的相互影響（間接）
  • 4、事務間的四大隔離
  • 5、事務控制語句
• 二、存儲引擎
• • 1、存儲引擎的概念
  • 2、MyISAM
  • • ①、ISAM的特點
    • ②、MyISAM的特點
    • ③MyISAM 表支持 3 種不同的存儲格式
    • ④、MyISAM適用的生產場景
  • 3、Innodb
  • • ①、Innodb特點
    • ②、Innodb 在磁盤上存儲成三個文件
    • ③、適用生產場景
  • 3、查看系統支持的存儲引擎
  • 4、查看表使用的存儲引擎
  • • 方法一：
    • 方法二：
  • 5、修改存儲引擎
  • • 方法一：alter修改表結構
    • 方法二：修改配置文件
    • 方法三：create創建表時指定存儲引擎
• 總結
• • 1、事務的四大特性
  • 2、事務間會出現間接性影響
  • 3、事務的操作
  • 4、兩大常用引擎 innodb 和 myisam 特點
  • • myisam
    • innodb
  • 面試題：請談論下 innodb 和 myisam 的區別？

一、事務

1、事務的概念

• 事務是一種機制、一個操作序列，包含了一組數據庫操作命令，并且把所有的命令作為一個整體一起向系統提交或撤銷操作請求，即這一組數據庫命令要么都執行，要么都不執行。
• 事務是一個不可分割的工作邏輯單元，在數據庫系統上執行并發操作時，事務是最小的控制單元。
• 事務適用于多用戶同時操作的數據庫系統的場景，如銀行、保險公司及證券交易系統等等。
• 事務是通過事務的整體性以保證數據的一致性。

2、事務的ACID特點

①、原子性（Atomicity）

• 事務是一個完整的操作，事務的各元素是不可分的
• 事務中的所有元素必須作為一個整體提交或回滾
• 如果事務中的任何元素失敗，則整個事務將失敗

②、一致性（Consistency）

• 當事務完成時，數據必須處于一致狀態
• 在事務開始前，數據庫中存儲的數據處于一致狀態
• 在正在進行的事務中，數據可能處于不一致的狀態
• 當事務成功完成時，數據必須再回到已知的一致狀態

③、隔離性（Isolation）

• 對數據進行修改的所有并發事務是彼此隔離的，表明事務必須是獨立的，它不應以任何方式依賴于或影響其他事務（直接影響）
• 修改數據的事務可在另一個使用相同數據的事務開始之前訪問這些數據，或者在另一個使用相同數據的事務結束之后訪問這些數據

④、持久性（Durability）

• 指不管系統是否發生故障，事務處理的結果都是永久的
• 一旦事務被提交，事務的效果會被永久地保留在數據庫中

3、事務間的相互影響（間接）

• 雖然事務有著四大特性，但是還是會出現相互間的間接影響，大概總結為四種：
  • 臟讀：一個事務讀取了另一個事務未提交的數據，而這個數據是有可能回滾的。
  • 不可重復讀：一個事務內兩個相同的查詢卻返回了不同數據。這是由于查詢時系統中其他事務修改的提交而引起的。
  • 幻讀：一個事務對一個表中的數據進行了修改，這種修改涉及到表中的全部數據行。同時，另一個事務也修改這個表中的數據，這種修改是向表中插入一行新數據。那么，操作前一個事務的用戶會發現表中還有沒有修改的數據行，就好象發生了幻覺一樣。
  • 丟失更新：兩個事務同時讀取同一條記錄，A先修改記錄，B也修改記錄（B不知道A修改過），B提交數據后B的修改結果覆蓋了A的修改結果。

4、事務間的四大隔離

隔離說明效果

read uncommitted	讀取尚未提交的數據	不解決臟讀
read committed	讀取已經提交的數據	可以解決臟讀
repeatable read	重復讀取	可以解決臟讀 和 不可重復讀 —mysql默認的
serializable	串行化	可以解決 臟讀 不可重復讀 和 虛讀—相當于鎖表

5、事務控制語句

• 事務默認是自動提交的，當SQL語句提交時事務便自動提交
• 我們日常使用 SQL 語句進行操做之所以可以執行成功，是因為 mysql 實現了自動提交、執行操作，而我們也可以關閉這個“自動提交功能”，也就是說我們只能手動提交，而我們提交前，我們做的修改是在內存中修改的，并不會提交到磁盤，一旦提交到磁盤，那就表示對數據庫中的數據進行了修改，mysql 會把我們修改的對象(表)提交一份副本到內存中，方便我們進行修改，改完之后執行“提交”，就可以直接修改實際數據

begin 或 start transaction 或 set autocommit-0(禁止自動提交)：開啟一個事務commit 或 commitwork：提交事務，并使已對數據庫進行的所有修改變為永久性 注：執行了 commit 之后，才會觸發I/O流進行寫入rollback 或 rollback work：回滾會結束用戶的事務，并撤銷正在進行的所有未能提交的修改savepoint S1：使用savepoint標記。 允許在事務中創建一個回滾點，一個事務中可以有多個savepoint；S1代表回滾點名稱release savepoint 存檔點名稱：刪除存檔點rollback to [savepoint] S1：把事務回滾到標記點set autocommit=0; #禁止自動提交 set autocommit=1; #開啟自動提交，Mysql默認為1 set variVARIABLES like 'autocommit'; #查看Mysql中的AUTOCOMMIT值

二、存儲引擎

1、存儲引擎的概念

• MySQL中的數據用各種不同的技術存儲在文件中，每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術以及配套的功能在MySQL中稱為存儲引擎
• 存儲引擎是MySQL將數據存儲在文件系統中的存儲方式或者存儲格式

2、MyISAM

• MyISAM存儲引擎是MySQL關系型數據庫5.5版本之前默認的存儲引擎，前身是ISAM
• ISAM是一個定義明確且經歷時間考驗的數據表格管理辦法，在設計之時就考慮到數據庫被查詢的次數要遠大于更新的次數

①、ISAM的特點

• ISAM執行讀取操作的速度很快
• 不支持事務處理
• 不占用大量的內存和存儲資源
• 不能夠容錯

②、MyISAM的特點

MyISAM管理非事務表，是ISAM的擴展格式 提供ISAM表里沒有的索引和字段管理的大量功能 MyISAM使用一種表格鎖定的機制，以優化多個并發的讀寫操作 MyISAM提供高速存儲和索引，以及全文搜索能力，受到web開發的青睞

• MyISAM不支持事務，也不支持外鍵
• 訪問速度快
• 對事物完整性沒有要求
• MyISAM在磁盤上存儲成三個文件
  • .frm 文件存儲表結構的定義
  • 數據文件的擴展名為 .MYD (MYData)
  • 索引文件的擴展名是 .MYI (MYIndex)
• 表級鎖定形式，數據在更新時鎖定整個表（不允許兩個人同時操作）
• 數據庫在讀寫過程中相互阻塞。會在數據寫入的過程阻塞用戶數據的讀取，也會在數據讀取的過程中阻塞用戶的數據寫入
• 數據單獨寫入或讀取，速度過程較快且占用資源相對少

③MyISAM 表支持 3 種不同的存儲格式

（1）靜態(固定長度)表 靜態表是默認的存儲格式。靜態表中的字段都是非可變字段，這樣每個記錄都是固定長度的，這種存儲方式的優點是存儲非常迅速，容易緩存，出現故障容易恢復；缺點是占用的空間通常比動態表多。 （2）動態表 動態表包含可變字段，記錄不是固定長度的，這樣存儲的優點是占用空間較少，但是頻繁的更新、刪除記錄會產生碎片，需要定期執行 OPTIMIZE TABLE 語句或 myisamchk -r 命令來改善性能，并且出現故障的時候恢復相對比較困難。 （3）壓縮表 壓縮表由 myisamchk 工具創建，占據非常小的空間，因為每條記錄都是被單獨壓縮的，所以只有非常小的訪問開支。

④、MyISAM適用的生產場景

• 公司業務不需要事務的支持
• 單方面讀取或寫入數據比較多的業務
• MyISAM存儲引擎數據讀寫都比較頻繁場景不適合
• 使用讀寫并發訪問相對較低的業務
• 數據修改相對較少的業務
• 對數據業務一致性要求不是非常高的業務
• 服務器硬件資源相對比較差

3、Innodb

①、Innodb特點

• 支持事務：支持4個事務隔離級別
• 行級鎖定，但是全表掃描仍然會是表級鎖定
• 讀寫阻塞與事務隔離級別相關
• 具有非常高效的緩存屬性：能緩存索引，也能緩存數據
• 表與主鍵以簇的方式存儲
• 支持分區、表空間，類似oracle數據庫
• 支持外鍵約束，5.5以前不支持全文索引，5.5以后的版本支持全文索引
• 對硬件資源要求比較高

因MyISAM注重讀，而Innodb注重寫的功能，因此在表級鎖定進行相關搜索的時候還是有差異的。 例： update table set a=1 where user like ‘%liu%’; Innodb是不會保存表的行數的，因此，使用‘select count(*) from table;’時，需要掃描一遍整表來計算有多少行。 但是MyISAM只需要進行的讀出保存好的行數即可。 注：當count(*)語句包含where語句時，MyISAM也需要掃描整表。對于自增長的字段，Innodb中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立組合索引清空整表時，Innodb時逐行刪除，效率較低。MyISAM是直接重建表。

②、Innodb 在磁盤上存儲成三個文件

• db.opt（表屬性文件）
• 表名.frm（表結構文件）
• 表名.ibd（表數據元數據）

③、適用生產場景

• 業務需要事務的支持
• 行級鎖定對高并發有很好的適應能力，但需確保查詢是通過索引來完成
• 業務數據更新較為頻繁的場景，如：論壇、微博等
• 業務數據一致性要求較高，如：銀行業務
• 硬件設備內存較大（因為事務都先放內存），利用innodb較好的緩存能力來提高內存利用率，減少磁盤IO的壓力

3、查看系統支持的存儲引擎

show engines;

4、查看表使用的存儲引擎

方法一：

show table status from 庫名 where name='表名'\G;

方法二：

use 庫名; show create table 表名;

5、修改存儲引擎

方法一：alter修改表結構

use 庫名; alter table 表名 engine=myisam;

方法二：修改配置文件

vim /etc/my.cnf [mysqld] default-storage-engine=INNODB

方法三：create創建表時指定存儲引擎

use 庫名; create table 表名(字段1 數據類型,...) engine=MyISAM;

總結

1、事務的四大特性

原子性（Atomicity）	事務是一個完整的操作，事務的各元素是不可分的
	事務中的所有元素必須作為一個整體提交或回滾
	如果事務中的任何元素失敗，則整個事務將失敗
一致性（Consistency）	當事務完成時，數據必須處于一致狀態
	在事務開始前，數據庫中存儲的數據處于一致狀態
	在正在進行的事務中，數據可能處于不一致的狀態
	當事務成功完成時，數據必須再回到已知的一致狀態
隔離性（Isolation）	對數據進行修改的所有并發事務是彼此隔離的，表明事務必須是獨立的，它不應以任何方式依賴于或影響其他事務（直接影響）
	修改數據的事務可在另一個使用相同數據的事務開始之前訪問這些數據，或者在另一個使用相同數據的事務結束之后訪問這些數據
持久性（Durability）	指不管系統是否發生故障，事務處理的結果都是永久的
	一旦事務被提交，事務的效果會被永久地保留在數據庫中

2、事務間會出現間接性影響

• 幻讀：一個事務對整表操作時，另一個事務向表內插入數據，那么第一個事物只會負責自己原先所能看到的表情況，而不會管另一個事務的操作，如幻覺一般
• 臟讀：未提交的事務被另一個事務讀取，且事務可回滾
• 不可重復讀：一個事務做相同查詢操作但返回結果不同。因其他事務在做修改操作而引起
• 丟失更新：兩個事務同時修改同一條記錄，則后者事務覆蓋前者操作結果

3、事務的操作

1、begin;（開始事務） ? insert into test values(1,'zhangsan'); ? commit;（結束事務）2、begin; ? insert into test values(1,'zhangsan'); ? savepoint a; ? savepoint b; ? rollback to b; ? rollback to a;? 注意：只能向前回滾，無法向后回滾

4、兩大常用引擎 innodb 和 myisam 特點

myisam

• MyISAM不支持事務，也不支持外鍵
• 訪問速度快
• 對事物完整性沒有要求
• 表級鎖定形式，數據在更新時鎖定整個表（不允許兩個人同時操作）
• 數據庫在讀寫過程中相互阻塞。會在數據寫入的過程阻塞用戶數據的讀取，也會在數據讀取的過程中阻塞用戶的數據寫入
• 數據單獨寫入或讀取，速度過程較快且占用資源相對少

innodb

• 支持事務：支持4個事務隔離級別
• 行級鎖定，但是全表掃描仍然會是表級鎖定
• 讀寫阻塞與事務隔離級別相關
• 具有非常高效的緩存屬性：能緩存索引，也能緩存數據
• 表與主鍵以簇的方式存儲
• 支持分區、表空間，類似oracle數據庫
• 支持外鍵約束，5.5以前不支持全文索引，5.5以后的版本支持全文索引
• 對硬件資源要求比較高

面試題：請談論下 innodb 和 myisam 的區別？

1、注重方向：myisam 更注重于讀，而 innodb 注重于寫 2、搜索/訪問速度：myisam > innodb 3、innodb支持事務，myisam不支持事務 4、完整性：myisam對完整性無要求（不支持事務，ACID特點），innodb具有很好的完整性（支持事務，ACID特點） 5、安全性：innodb>myisam。（因完整性有所區別，所以安全性也有所區別，在遇到事故時，innodb比myisam恢復起來容易） 6、鎖級別：myisam為表級鎖定，innodb為行級鎖定（全表掃描仍是表級鎖定） 7、抗并發能力：myisam容納并發力差，innodb容納高并發能力較好 8、對于硬件要求：myisam對硬件要求不是很高，innodb對于硬件要求較高對比與這些區別，所用的場景也有所不同 例如： 1、innodb適用于如微博、論壇等讀寫較多且更新頻繁的場景 2、myisam適用于如資料檔案室、商品倉庫等數據修改較少、以讀為主并且不支持事務的場景

總結

以上是生活随笔為你收集整理的Mysql数据库（五）——mysql事务及引擎的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。