mysql-事務
1. 事務的概念
● 事務是一種機制、一個操作序列，包含了一組資料庫操作指令，并且把所有的指令作為一個整體一起向系統送出或撤銷操作請求，即這一組資料庫指令要麼都執行，要麼都不執行。
● 事務是一個不可分割的工作邏輯單元，在資料庫系統上執行并發操作時，事務是最小的控制單元。
● 事務适用于多使用者同時操作的資料庫系統的場景，如銀行、保險公司及證券交易系統等等。
● 事務通過事務的整體性以保證資料的一緻性。
● 事務能夠提高在向表中更新和插入資訊期間的可靠性。
總結來說，事務是一個操作序列，這些操作要麼都執行，要麼都不執行，它是一個不可分割的工作機關。
2. 事務的ACID特點
ACID是指在可靠資料庫管理系統（DBMS）中，事務（transaction）應該具有的四個特性：原子性（Atomicity）、一緻性（Consistency）、隔離性（Isolation）、持久性（Durability），這是可靠資料庫所應具備的幾個特性。
原子性
原子性：指事務是一個不可再分割的工作機關，事務中的操作要麼都發生，要麼都不發生。
事務是一個完整的操作，事務的各元素是不可分的。
事務中的所有元素必須作為一個整體送出或復原。
如果事務中的任何元素失敗，則整個事務将失敗。
案例：
A給B轉賬100元錢的時候，隻執行了扣款語句，就送出了，此時如果突然斷電，A賬号已經發生了扣款，B賬号卻沒收到加款，在生活中就會引起糾紛。這種情況就需要事務的原子性來保證事務要麼都執行，要麼就都不執行。
一緻性
一緻性：指在事務開始之前和事務結束以後，資料庫的完整性限制沒有被破壞。
當事務完成時，資料必須處于一緻狀态。
當事務開始前，資料庫中存儲的資料處于一緻狀态。
當正在進行的事務中，資料可能處于不一緻的狀态。
當事務成功完成時，資料必須再次回到已知的一緻狀态。
案例：
對銀行轉賬事務，不管事務成功還是失敗，應該保證事務結束後表中A和B的存款總額跟事務執行前一緻。
隔離性
隔離性：指在并發環境中，當不同的事務同時操縱相同的資料時，每個事務都有各自的完整資料空間。
對資料進行修改的所有并發事務是彼此隔離的，表明事務必須是獨立的，它不應以任何方式依賴于或影響其他事務。
修改資料的事務可在另一個使用相同資料的事務開始之前通路這些資料，或者在另一個使用相同資料的事務結束之後通路這些資料
3、事務之間的互相影響
髒讀：一個事務讀取了另一個事務未送出的資料，而這個資料是有可能復原的。
不可重複讀：一個事務内兩個相同的查詢卻傳回了不同資料，這是由于查詢時系統中其他事務修改的送出而引起的。
幻讀：一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，另一個事務也修改這個表中的資料，這種修改是向表中插入一行新資料。那麼，操作前一個事務的使用者會發現表中還有沒有修改的資料行，就好像發生了幻覺一樣。
丢失更新：兩個事務同時讀取同一條記錄，A先修改記錄，B也修改記錄（B不知道A修改過），B送出資料後B的修改結果覆寫了A的修改結果。
4、mysql事務隔離性介紹
MySQL事務支援的四種隔離
未送出讀（Read Uncommitted）
允許髒讀，其他事務隻要修改了資料，即使未送出，本事務也能看到修改後的資料值。也就是可能讀取到其他會話中未送出事務修改的資料。
送出讀（Read Committed）
隻能讀取到已經送出的資料。Oracle等多數資料庫預設都是該級别（不重複讀）。
可重複讀（Repeated Read）
可重複度。無論其他事務是否修改并送出了資料，在這個事務中看到的資料值始終不受其他事務影響。MySQL預設使用該隔離級别。
串行讀（Serializable）
完全串行化的讀，每次讀都需要獲得表級共享鎖，讀寫互相都會堵塞，相當于鎖表。
資料庫的預設事務隔離級别
mysql: repeatable read 可重複讀
Oracle: read committed送出讀
SQL Server: read committed送出讀
總結：在事務管理中，原子性是基礎，隔離性是手段，一緻性是目的，持久性是結果。
5、事務的正常操作
1、檢視與設定事務級别
查詢全局事務隔離級别
show global variables like ‘%isolation%’;
select @@global.tx_isolaion;
查詢會話事務隔離級别
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;
2、事務控制語句
begin 或 start transaction：顯式的開啟一個事務
commit 或 commit work：送出事務，并使已對資料庫進行的所有修改變為永久性。
rollback 或 rollback work：復原會結束使用者的事務，并撤銷正在進行的所有未送出的資料。
savepoint S1：使用savepoint允許在事務中建立一個復原點，一個事務中可以有多個savepoint，"S1"代表復原點名稱。savepoint的作用類似于遊戲中的存檔。
rollback to [savepoint] S1：把事務復原到标記點。類似于遊戲中的讀取存檔。
例：測試開始與送出事務
use qiao;
create table qiao(id int,name char(10),sex char(10));
insert into qiao values(1,'zs','男');
MySQL [email protected]:qiao> select*from qiao;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | zs | 男 |
+----+------+-----+
begin #開啟一個事務
insert into qiao values(2,'ls','男');
quit #直接退出
mycli -u使用者名 -p密碼 #再次登入
use qiao;
MySQL [email protected]:qiao> select*from qiao;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | zs | 男 |
+----+------+-----+
begin #開啟一個事務
insert into qiao values(2,'ls','男');
commit; #送出事務
#退出再進
use qiao;
MySQL [email protected]:qiao> select*from qiao;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | zs | 男 |
| 2 | ls | 男 |
+----+------+-----+
例：測試事務的復原
MySQL [email protected]:qiao> begin;
Query OK, 0 rows affected
Time: 0.001s
MySQL [email protected]:qiao> insert into qiao values(3,'yp','男');
Query OK, 1 row affected
Time: 0.001s
MySQL [email protected]:qiao> rollback;
Query OK, 0 rows affected
Time: 0.002s
MySQL [email protected]:qiao> select*from qiao;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | zs | 男 |
| 2 | ls | 男 |
+----+------+-----+
2 rows in set
Time: 0.007s
MySQL [email protected]:qiao>
例：測試多點復原
MySQL [email protected]:qiao> begin;
Query OK, 0 rows affected
Time: 0.001s
MySQL [email protected]:qiao> insert into qiao values(3,'yp','男');
Query OK, 1 row affected
Time: 0.001s
MySQL [email protected]:qiao> savepoint s1;
Query OK, 0 rows affected
Time: 0.001s
MySQL [email protected]:qiao> insert into qiao values(4,'zww','男');
Query OK, 1 row affected
Time: 0.001s
MySQL [email protected]:qiao> savepoint s2;
Query OK, 0 rows affected
Time: 0.000s
MySQL [email protected]:qiao> insert into qiao values(5,'niuma','男');
Query OK, 1 row affected
Time: 0.001s
MySQL [email protected]:qiao> select*from qiao;
+----+-------+-----+
| id | name | sex |
+----+-------+-----+
| 1 | zs | 男 |
| 2 | ls | 男 |
| 3 | yp | 男 |
| 4 | zww | 男 |
| 5 | niuma | 男 |
+----+-------+-----+
5 rows in set
Time: 0.007s
MySQL [email protected]:qiao> rollback to s1;
Query OK, 0 rows affected
Time: 0.001s
MySQL [email protected]:qiao> select*from qiao;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | zs | 男 |
| 2 | ls | 男 |
| 3 | yp | 男 |
+----+------+-----+
4、使用set設定控制事務
set autocommit=0; #禁止自動送出
set autocommit=1; #開啟自動送出，MySQL預設為1
show variables like ‘autocommit’; #檢視MySQL中的autocommit值
如果沒有開啟自動送出，目前會話連接配接的MySQL的所有操作都會當成一個事務直到你輸入rollback或commit，目前事務才算結束。目前事務結束前新的MySQL連接配接時，無法讀取到任何目前會話的操作結果。
如果開啟了自動送出，MySQL會把每個sql語句當成一個事務，然後自動的commit。
當然，無論開啟與否，begin;commit|rollback;都是獨立的事務。
mysql存儲引擎
1、存儲引擎的概念
● MySQL中的資料用各種不同的技術存儲在檔案中，每一種技術都是用不同的存儲機制、索引技巧、鎖定水準并最終提供不同的功能和能力，這些不同的技術以及配套的功能在MySQL中稱為存儲引擎。
● 存儲引擎是MySQL将資料存儲在檔案系統中的存儲方式或者存儲格式。
● MySQL常用的存儲引擎為：MyISAM、InnoDB。
● MySQL資料庫中的元件，負責執行實際的資料I/O操作。
● MySQL系統中，存儲引擎處于檔案系統之上，在資料儲存到資料檔案之前會傳輸到存儲引擎，之後按照各個存儲引擎的存儲格式進行存儲。
2. MyISAM
2.1 MyISAM的特點
● MyISAM不支援事務，也不支援外鍵限制，隻支援全文索引，資料檔案和索引檔案是分開儲存的。
● 通路速度快，對事務完整性沒有要求
● MyISAM适合查詢、插入為主的應用
● MyISAM在磁盤上存儲成三個檔案，檔案名和表名都相同，但是擴充名分别為：
.frm檔案存儲表結構定義
.MYD(MYData)為資料檔案的擴充名
.MYI(MYIndex)為索引檔案的擴充名
● 表級鎖定形式，資料在更新時鎖定整個表
● 資料庫在讀寫過程中互相阻塞
會在資料寫入的過程中阻塞使用者資料的讀取
也會在資料讀取的過程中阻塞使用者的資料寫入
● 資料單獨寫入或讀取，速度過程較快且占用資源相對少
● MyISAM支援的存儲格式
靜态表
動态表
壓縮表
2.2 MyISAM支援3種存儲格式
①靜态（固定長度）表
靜态表是預設的存儲格式。靜态表中的字段都是非可變字段，這樣每個記錄都是固定長度，這種存儲方式的優點是存儲非常迅速，容易緩存，出現故障容易恢複；缺點是占用的空間通常比動态表多。
②動态表
動态表包含可變字段，記錄不是固定長度的，這樣存儲的優點是占用空間較少，但是頻繁的更新、删除記錄會産生碎片，需要定期執行optimize table語句或myisamchk -r指令來改善性能，并且出現故障的時候恢複相對比較困難。
③壓縮表
壓縮表由myisamchk工具建立，占據非常小的空間，因為每條記錄都是被單獨壓縮的，是以隻有非常小的通路開支。
2.3 MyISAM适用的生産場景舉例
● 公司業務不需要事務的支援
● 單方面讀取或寫入資料比較多的業務
● MyISAM存儲引擎資料讀寫都比較頻繁的場景不适合
● 适用讀寫并發通路相對較低的業務
● 資料修改相對較少的業務
● 對資料業務一緻性要求不是非常高的業務
● 伺服器硬體資源相對比較差
3、innodb
3.1InnoDB特點介紹
● 支援事務，支援4個事務隔離級别
● MySQL從5.5.5版本開始，預設的存儲引擎為InnoDB
● 讀寫阻塞與事務隔離級别相關
● 能非常高效的緩存索引和資料
● 表與主鍵以簇的方式存儲
● 支援分區、表空間，類似于Oracle資料庫
● 支援外鍵限制，5.5前不支援全文索引，5.5後支援全文索引
● 對硬體資源要求還是比較高的場合
● 行級鎖定，但是全表掃描仍然會是表級鎖定，如update table set a=1 where user like ‘%zhang%’;
● InnoDB中不儲存表額行數，如select count() from table;時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM隻要簡單的讀出儲存好的行數即可。需要注意的是，當count()語句包含where條件時MyISAM也需要掃描整個表
● 對于自增長的字段，InnoDB中必須包含隻有該字段的索引，但是在MyISAM表中可以和其他字段一起建立組合索引
● 清空整個表時，InnoDB是一行一行的删除，效率非常慢。MyISAM則會重建表
3.2 InnoDB适用生産場景分析
● 業務需要食物的支援
● 行級鎖定對高并發有很好的适應能力，但需確定查詢是通過索引來完成
● 業務資料更新較為頻繁的場景，如論壇、微網誌等
● 業務資料一緻性要求較高，如銀行業務
● 硬體裝置記憶體較大，利用InnoDB較好的緩存能力來提高記憶體使用率，減少磁盤IO的壓力
3.3 企業選擇存儲引擎的依據
需要考慮每個存儲引擎提供了哪些不同的核心功能及應用場景
● 支援的字段和資料類型
所有引擎都支援通用的資料類型
但不是所有的引擎都支援其他的字段類型，如二進制對象
● 鎖定類型：不同存儲引擎支援不同級别的鎖定
表鎖定：MyISAM支援
行鎖定：InnoDB支援
● 索引的支援
建立索引在搜尋和恢複資料庫中的資料時能顯著提高性能
不同的存儲引擎提供不同的制作索引的技術
有些存儲引擎根本不支援索引
● 事務處理的支援
提高在向表中更新和插入資訊期間的可靠性
可根據企業業務是否要支援事務選擇存儲引擎
4、存儲引擎的檢視與修改
檢視：
方法一：
show table status from 庫名 where name='表名'\G;
MySQL [email protected]:qiao> show table status from qiao where name='qiao'\G;
***************************[ 1. row ]***************************
Name | qiao
Engine | MyISAM #此處就是存儲引擎
Version | 10
Row_format | Fixed
Rows | 2
Avg_row_length | 65
Data_length | 130
Max_data_length | 18295873486192639
Index_length | 1024
Data_free | 0
Auto_increment | <null>
Create_time | 2021-08-31 11:55:10
Update_time | 2021-08-31 11:55:10
Check_time | <null>
Collation | utf8_general_ci
Checksum | <null>
Create_options |
Comment |
1 row in set
Time: 0.002s
方法二：
use 庫名；
show create table 表名；
MySQL [email protected]:qiao> show create table qiao\G;
***************************[ 1. row ]***************************
Table | qiao
Create Table | CREATE TABLE "qiao" (
"id" int(11) DEFAULT NULL,
"name" char(10) DEFAULT NULL,
"sex" char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 #此處存儲引擎，預設字元
修改
方法一：
use 庫名;
alter table 表名 engine=myisam; #比如改為myisam存儲引擎
方法二：
通過修改 /etc/my.cof 配置檔案，指定預設存儲引擎并重新開機服務
vim /etc/my.cof
...
[mysqld]
...
default-storage-engine=INNODB #比如改為innodb存儲引擎
systemctl restart mysqld
方法三：
建立表的時候指定存儲引擎