1. 物理存儲結(jié)構(gòu)
1.1 表空間
支持兩類表空間: 共享,獨立
5.5 版本 : 默認共享表空間. 包含: 數(shù)據(jù)字典\undo\tmp\用戶表數(shù)據(jù)和索引
5.6 版本 : 默認獨立表空間. 包含: 數(shù)據(jù)字典\undo\tmp,將用戶數(shù)據(jù)和索引獨立,每個表單獨存儲
5.7 版本 : 默認獨立表空間. 包含: 數(shù)據(jù)字典\undo,tmp獨立,將用戶數(shù)據(jù)和索引獨立,每個表單獨存儲
8.0 版本 : 默認獨立表空間. 數(shù)據(jù)字典取消掉, undo,tmp獨立 將用戶數(shù)據(jù)和索引獨立,每個表單獨存儲
1.2 功能名詞介紹
transaction 事務(wù)
undo : ibdata1 回滾日志
tmp : ibtmp1 臨時表空間
redo : ib_logfile0~N 重做日志
ibd : t1.ibd 表空間數(shù)據(jù)文件
Innodb Buffer Pool 數(shù)據(jù)緩沖區(qū)池(70-80%)
log buffer 重做日志緩沖區(qū)
LSN 日志序列號
Trx_id 事務(wù)ID
checkpoint 檢查點
1.3 事務(wù) ?
1.3.1 什么是事務(wù)?
將多條DML(標(biāo)準(zhǔn)的事務(wù)語句),放在一個"組"中運行,要么全成功要么全失敗.
1.3.2 事務(wù)ACID特性atomicity, consistency, isolation, and durability.
A : 原子性 : 每一個事務(wù)都是一個完整整體,不可再分性 . 要么全執(zhí)行成功要么全失敗.
C : 一致性 : 在事務(wù)前,中,后,保證事務(wù)操作的數(shù)據(jù)前后一致.
I : 隔離性 : 多個事務(wù)之間,所做事務(wù)互不干擾,不能同時更新同一行數(shù)據(jù).
D : 持久性 : 事務(wù)完成之后,所涉及到的數(shù)據(jù),必須永久有效(落地)
1.3.3 事務(wù)的生命周期管理
標(biāo)準(zhǔn)的事務(wù)生命周期:
(1) 開啟一個事務(wù)
begin / start transaction;
(2) 標(biāo)準(zhǔn)的事務(wù)語句
insert
update
delete
(3) 結(jié)束事務(wù)
commit; # 提交事務(wù)
rollback; # 回滾事務(wù)
非標(biāo)準(zhǔn)的事務(wù)生命周期
(1) 自動提交機制
MySQL 5.6 以后:
1. begin子句會自動添加
2. 每一條執(zhí)行完成之后都會自動提交
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
[root@db01 data_3306]# vim /etc/my.cnf
autocommit=0
[root@db01 data_3306]# /etc/init.d/mysqld restart
說明: 默認情況下,開啟事務(wù)時不加begin,逐條自動提交. 手工開啟begin命令,按照正常事務(wù)工作過程.
(2) 隱式提交
用于隱式提交的 SQL 語句：
begin
a
b
begin
SET AUTOCOMMIT = 1
導(dǎo)致提交的非事務(wù)語句：
DDL語句： （ALTER宇姚、CREATE 和 DROP）
DCL語句： （GRANT浑劳、REVOKE 和 SET PASSWORD）
鎖定語句：（LOCK TABLES 和 UNLOCK TABLES）
導(dǎo)致隱式提交的語句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE
(3) 隱式回滾
會話斷開
數(shù)據(jù)庫重啟
死鎖
1.3.4 事務(wù)底層的工作過程
image.png
(1) redo
分布:
內(nèi)存: log buffer
磁盤: ib_logfile0~N
功能:
1. 保存內(nèi)存數(shù)據(jù)頁的變化
2. commit時, 實現(xiàn)事務(wù)的快速持久化的特性: 量少,順序IO
3. 宕機時,通過redo實現(xiàn)重做事務(wù),將數(shù)據(jù)庫恢復(fù)到宕機之前的狀態(tài).
我們由把這步稱之為 ACSR 中的"前滾"操作
(2) undo 回滾日志
分布: 默認 ibdata1, 5.7開始可以獨立undo,8.0后自動獨立
功能 :
1. 保存當(dāng)前事務(wù)操作的反操作
2. 在執(zhí)行rollback命令時,undo提供回滾操作,在ACID中主要實現(xiàn)A的特性,CI也有部分功能
3. 宕機時,ACSR過程中提供回滾操作(將沒有commit標(biāo)記的)
1.3.5 鎖(寫) 及 隔離級別(讀)主要保證隔離性
(1) 鎖 :
S : 共享鎖,讀鎖
X : 排它鎖,寫鎖
IS : 意向S
IX : 意向X
(2) X 鎖的細分
TX ------> 表鎖 DDL
全局鎖表:
備份時,備份系統(tǒng)表時(非INOODB表),FTWRL
mysql> flush table with read lock;
mysql> unlock tables;
單表: DDL
mysql> lock table t1 read ;
mysql> unlock tables;
RX ------> 記錄鎖 DML
GAP LOCK X ------> 間隙鎖 特殊DML
Next LOCK X ------> 下一鍵鎖定
(3) 隔離級別(transaction_isolation)
mysql> select @@transaction_isolation;
RU :讀未提交 READ-UNCOMMITTED
模擬:
session A
mysql> begin;
mysql> use world
mysql> delete from city where id=1000;
session B
mysql> begin;
mysql> use world
mysql> select *from city where id=1000;
會產(chǎn)生的問題:
1. 臟讀：A表刪除一段數(shù)據(jù)魔熏，B表進行查詢可以發(fā)現(xiàn)這條數(shù)據(jù)不存在鸽扁。當(dāng)A表進行回滾操作（rollback）后桶现，B表再次進行查詢，發(fā)現(xiàn)數(shù)據(jù)又會回來骡和。
效果如圖：
image.png
2. 不可重復(fù)讀現(xiàn)象
3. 幻讀
RC :讀已提交 ***** READ-COMMITTED
1. 不可重復(fù)讀現(xiàn)象：執(zhí)行同一條語句所得結(jié)果不同。
效果如圖：
image.png
2. 幻讀：A表執(zhí)行某種操作時唤衫，B表插入符合A表所執(zhí)行條件的數(shù)據(jù)绵脯，A表執(zhí)行完成commit后，經(jīng)過查詢植兰，結(jié)果與所執(zhí)行命令的條件不符璃吧，這種情況為幻讀。
RR :可重復(fù)讀 ***** REPEATABLE-READ
1. 幻讀
說明:
- RR級別+ GAP（間隙所）+ Next lock(GAP+RX（記錄鎖）)有效防止幻讀現(xiàn)象
- 通過MVCC,多版本并發(fā)控制中,一致性快照讀技術(shù),解決了不可重復(fù)讀問題.
SR :串行化
總結(jié):
AID 都是為了數(shù)據(jù)庫最終一致性 C
SQL_MODE
約束
自己擴展:
MDL 原數(shù)據(jù)鎖
page lock 頁鎖
latch 內(nèi)存頁鎖
2. InnoDB 存儲引擎核心參數(shù) *****
mysql> select @@innodb_data_file_path; 共享表空間的設(shè)置參數(shù)
mysql> select @@innodb_file_per_table; 共享表空間的設(shè)置參數(shù)
mysql> select @@innodb_buffer_pool_size; #不要超過80%物理內(nèi)存 ----> Out of memory
mysql> select @@innodb_log_buffer_size; 日志緩沖區(qū)的大小
mysql> select @@innodb_log_file_size; 日志文件的大小
mysql> select @@innodb_log_file_in_group;
mysql> select @@innodb_flush_log_at_trx_commit; # 雙1標(biāo)準(zhǔn)之一.控制redo刷寫的策略.
0 每秒鐘刷寫redo到磁盤.
1 每次事務(wù)提交,理解刷寫redo到磁盤
2 每次事務(wù)提交,立即寫日志到OS cache中,然后每秒鐘刷寫到磁盤.
mysql> select @@innodb_flush_method; 控制(buffer臟頁,redo buffer日志)刷寫方式
建議設(shè)置:
O_DIRECT : 數(shù)據(jù)頁刷寫磁盤直接穿過文件系統(tǒng)緩存,redo 刷寫時,先寫os cache,再寫到磁盤筒繁。
