一、MySQL 事務

（一）、事務的概念

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

說白了，所謂事務，它是一個操作序列，這些操作要么都執行，要么都不執行，它是一個不可分割的作業單位，

（二）、事務的 ACID 特點

ACID，是指在可靠資料庫管理系統 (DBMS) 中，事務 (transaction) 應該具有的四個特性：原子性 (Atomicity) 、一致性 (Consistency )、隔離性 (Isolation) 、持久性 (Durability) ，這是可靠資料庫所應具備的幾個特性，

1、原子性：指事務是一個不可再分割的作業單位，事務中的操作要么都發生，要么都不發生，

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

案例：
A給B轉帳100元錢的時候只執行了扣款陳述句，就提交了，此時如果突然斷電，A賬號已經發生了扣款，B賬號卻沒收到加款，在生活中就會引起糾紛，這種情況就需要事務的原子性來保證事務要么都執行，要么就都不執行，

2、一致性：指在事務開始之前和事務結束以后，資料庫的完整性約束沒有被破壞，

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

案例：
對銀行轉帳事務，不管事務成功還是失敗，應該保證事務結束后表中A和B的存款總額跟事務執行前一致，

3、隔離性：指在并發環境中，當不同的事務同時操縱相同的資料時，每個事務都有各自的完整資料空間，
對資料進行修改的所有并發事務是彼此隔離的，表明事務必須是獨立的，它不應以任何方式依賴于或影響其他事務，
修改資料的事務可在另一個使用相同資料的事務開始之前訪問這些資料，或者在另一-個使用相同資料的事務結束之后訪問這些資料，

事務之間的相互影響分為幾種，分別為：
(1)、臟讀：一個事務讀取了另一個事務未提交的資料，而這個資料是有可能回滾的，
(2)、不可重復讀：一個事務內兩個相同的查詢卻回傳了不同資料，這是由于查詢時系統中其他事務修改的提交而引起的，
(3)、幻讀：一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行，同時，另一個事務也修改這個表中的資料，這種修改是向表中插入一行新資料， 那么，操作前一個事務的用戶會發現表中還有沒有修改的資料行，就好象發生了幻覺一樣，
(4)、丟失更新：兩個事務同時讀取同一條記錄，A先修改記錄，B也修改記錄 (B不知道A修改過)，B提交資料后B的修改結果覆寫了A的修改結果，

Mysql及事物隔離級別：
(1) 、read uncommitted：讀取尚未提交的資料，不解決臟讀
(2)、 read committed：讀取己經提交的資料，可以解決臟讀
(3)、 repeatable read：重讀讀取，可以解決臟讀和不可重復讀-------------mysql默認
(4)、 serializable：串行化，可以解決臟讀不可重復讀和虛讀----------------相當于鎖表

mysql 默認的事務處理級別是 repeatable read，而 Oracle 和 SQL Server 是 read committed

查詢全域事務隔離級別:

show global variables like '%isolation%';
SELECT @@global.tx_isolation;

查詢會話事務隔離級別:.

show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

設定全域事務隔離級別:.

set global transaction isolation level read committed;

設定會話事務隔離級別:

set session transaction isolation level read committed;

4、持久性：在事務完成以后，該事務對資料庫所作的更改便持久的保存在資料庫之中，并不會被回滾，

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

總結：在事務管理中，原子性是基礎，隔離性是手段，一致性是目的，持久性是結果，

（三）、事務控制陳述句

BEGIN 或 START TRANSACTION：顯式地開啟一個事務，
COMMIT 或 COMMITWORK：提交事務，并使已對資料庫進行的所有修改變為永久性的，
ROLLBACK 或 ROLLBACKWORK：回滾回結束用戶的事務，并撤銷正在進行的所有未提交的修改，
SAVEPOINT S1：使用 SAVEPOINT 允許在事務中創建一個回滾點， 一個事務中可以有多個SAVEPOINT；“S1"代表回滾點名稱，
ROLLBACK TO [SAVEPOINT] S1：把事務回滾到標記點，

案例:

use gcc;
create table account (
id int(10) primary key not null,
name varchar(40),
money double 
);

insert into account values(1,'A',1000);
insert into account values(2,'B',1000);

#測驗提交事務
begin;
update account set money= money - 100 where name='A';
commit;
quit

mysql -u root -p
use gcc;
select * from account;

#測驗回滾事務
begin;
update account set money= money + 100 where name='A';
rollback;
mysql -u root -P
use gcc;
select * from account;

#測驗多點回滾
begin;
update account set money= money + 100 where name='A';
SAVEPOINT S1;
update account set money= money + 100 where name='B';
SAVEPOINT S2;
insert into account values(3,'C',1000);

select * from account;
ROLLBACK TO S1;
select * from account

（四）、使用 set 設定控制事務

SET AUTOCOMMIT=0;      #禁止自動提交
SET AUTOCOMMIT=1;      #開啟自動提交，Mysql默認為1
SHOW VARIABLES LIKE 'AUTOCOMMIT';    #查看Mysql中的AUTOCOMMIT值

如果沒有開啟自動提交，當前會話連接的mysq1的所有操作都會當成一個事務直到你輸入 rollback I commit; 當前事務才算結束，
當前事務結束前新的mysql連接時無法讀取到任何當前會話的操作結果，
如果開起了自動提交，mysql 會把每個sql陳述句當成一個事務，然后自動的commit，
當然無論開啟與否，begin; commit lrollback; 都是獨立的事務，

案例：
use gcc;
select * from account;
SET AUTOCOMMIT=0;
update account set money= money + 100 where name='B';
select * from account;
quit

mysql -u root -p
use gcc;
select * from account; 

二、MySQL存盤引擎

（一）、存盤引擎概念介紹

1、MySQL中的資料用各種不同的技術存盤在檔案中，每一種技術都使用不同的存盤機制、索引技巧、鎖定水平，并最終提供不同的功能和能力，這些不同的技術以及配套的功能在MySQL中稱為存盤引擎，

2、存盤引擎是MySQL將資料存盤在檔案系統中的存盤方式或者存盤格式
3、MySQL 常用的存盤引擎有：

• MylSAM
• InnoDB

4、MySQL資料庫中的組件，負責執行實際的資料I/O操作
5、MySQL系統中，存盤引擎處于檔案系統之.上，在資料保存到資料檔案之前會傳輸到存盤引擎，之后按照各個存盤引擎的存盤格式進行存盤，

（二）、MyISAM的特點介紹

1、MyISAM不支持事務，也不支持外鍵約束，只支持全文索引，資料檔案和索引檔案是分開保存的
2、訪問速度快，對事務完整性沒有要求
3、MyISAM適合查詢、插入為主的應用
4、MylSAM在磁盤上存盤成三個檔案，檔案名和表名都相同，但是擴展名分別為：

• .frm 檔案存盤表結構的定義
• 資料檔案的擴展名為 .MYD (MYData)
• 索引檔案的擴展名是 .MYI (MYIndex)

5、表級鎖定形式，資料在更新時鎖定整個表
6、資料庫在讀寫程序中相互阻塞

• 會在資料寫入的程序阻塞用戶資料的讀取
• 也會在資料讀取的程序中阻塞用戶的資料寫入

7、資料單獨寫入或讀取，速度程序較快且占用資源相對少
8、MyIAM支持的存盤格式

• 靜態表
• 動態表
• 壓縮表

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

（三）、MyISAM 適用的生產場景舉例

1、公司業務不需要事務的支持
2、單方面讀取或寫入資料比較多的業務
3、MylSAM存盤引擎資料讀寫都比較頻繁場景不適合
4、使用讀寫并發訪問相對較低的業務
5、資料修改相對較少的業務
6、對資料業務一致性要求不是非常高的業務
7、服務器硬體資源相對比較差

（四）、InnoDB 特點介紹

1、支持事務，支持4個事務隔離級別
2、MySQL從5.5.5版本開始，默認的存盤引擎為InnoDB
3、讀寫阻塞與事務隔離級別相關
4、能非常高效的快取索引和資料
5、表與主鍵以簇的方式存盤
6、支持磁區、表空間，類似oracle資料庫
7、支持外鍵約束，5.5前不支持全文索引，5.5后支持全文索引
8、對硬體資源要求還是比較高的場合
9、行級鎖定，但是全表掃描仍然會是表級鎖定，如

update table set a=1 where user like '%zhang%';

10、InnoDB 中不保存表的行數，如select count(*) from table;時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM 只要簡單的讀出保存好的行數即可，需要注意的是，當count(*)陳述句包含where條件時 MyISAM 也需要掃描整個表
11、對于自增長的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中可以和其他欄位一起建立組合索引
12、清空整個表時，InnoDB是一行一行的洗掉，效率非常慢，MyISAM則會重建表，

（五）、InnoDB適用生產場景分析

1、業務需要事務的支持
2、行級鎖定對高并發有很好的適應能力，但需確保查詢是通過索引來完成
3、業務資料更新較為頻繁的場景

• 如:論壇，微博等

4、業務資料一致性要求較高

• 如:銀行業務

5、硬體設備記憶體較大，利用InnoDB較好的快取能力來提高記憶體利用率，減少磁盤IO的壓力

（六）、企業選擇存盤引擎依據

1、需要考慮每個存盤引擎提供了哪些不同的核心功能及應用場景
2、支持的欄位和資料型別

• 所有引擎都支持通用的資料型別
• 但不是所有的引|擎都支持其它的欄位型別，如二進制物件

3、鎖定型別：不同的存盤引擎支持不同級別的鎖定

• 表鎖定：MyISAM 支持
• 行鎖定：InnoDB支持

4、索引的支持

• 建立索引在搜索和恢復資料庫中的資料時能顯著提高性能
• 不同的存盤引擎提供不同的制作索引|的技術
• 有些存盤引擎根本不支持索引

5、事務處理的支持

• 提高在向表中更新和插入資訊期間的可靠性
• 可根據企業業務是否要支持事務選擇存盤引擎

（七）、相關命令

#查看系統支持的存盤引擎
show engines;

#查看表使用的存盤引擎
方法一-:
show table status from 庫名 where name='表名'\g

方法二:
use 庫名;
show create table 表名;

#修改存盤引擎
1.通過 alter table修改
use 庫名;
alter table 表名 engine=MyISAM;

2.通過修改 /etc/my.cnf 組態檔，指定默認存盤引擎并重啟服務
vim /etc/my.cnf 
.......
[mysqld]
.......
default-storage-engine=INNODB

systemctl restart mysql.service
注意:此方法只對修改了組態檔并重啟mysq1服務后新創建的表有效，已經存在的表不會有變更，

3.通過 create table 創建表時指定存盤引擎
use 庫名;
create table 表名 (欄位1 資料型別,...) engine=MyISAM;