MySQL之四 儲存引擎

1.介紹

儲存引擎MySQL中的“檔案系統”

MySQL體系結構

 

 

 InnoDB儲存引擎介紹

 

 

 

My1SAM 和InnoDB區別

 mysql
 MariaDB [(none)]> show engines;  可以檢視當前My1SAM和InnoDB資訊
 +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
 | Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
 +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
 | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
 | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
 | CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
 | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
 | MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
 | InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES

My1SAM 和InnoDB

MyISAM	InnoDB
不支援事務	支援事務，適合處理大量短期事務
表級鎖，當表鎖定時，其他人都無法使用，影響併發性範圍大	行級鎖
讀寫相互阻塞，寫入不能讀，讀時不能寫	讀寫阻塞與事務隔離級別相關
只快取索引	可快取資料和索引
不支援外來鍵約束	支援外來鍵
不支援聚簇索引	支援聚簇索引
讀取資料較快，佔用資源較少	MySQL5.5後支援全文索引
不支援MVCC（多版本併發控制機制）高併發	支援MVCC高併發
崩潰恢復性差	崩潰恢復性好
MySQL5.5.5前預設的資料庫引擎	MySQL5.5.5後預設的資料庫引擎
適用只讀（或者寫較少）、表較小（可以接受長時間進行修復操作）的場景	系統表空間檔案：ibddata1, ibddata2, ...
tb_name.frm 表結構，tb_name.MYD 資料行，tb_name.MYI 索引	每表兩個資料庫檔案：tb_name.frm 每表表結構，tb_name.ibd 資料行和索引

彩蛋：InnoDB 核心特性有哪些？ InnoDB和MyISAM區別有哪些？ InnoDB支援事務、MVCC、聚簇索引、外來鍵、緩衝區、AHI、CR、DW，MyISAM不支援。 InnoDB支援行級鎖，MyISAM支援表級鎖。 InnoDB支援熱備（業務正常執行，影響低），MyISAM支援溫備份（鎖表備份）。 InnoDB支援CR(自動故障恢復)，當機自動故障恢復，資料安全和一致性可以得到保證。MyISAM不支援，當機可能丟失當前修改。

種類

Oracle MySQL自帶的儲存引擎種類

 mysql> show engines;
 MRG_MYISAM      
 CSV               
 MyISAM                      
 BLACKHOLE         
 PERFORMANCE_SCHEMA
 InnoDB                 
 ARCHIVE            
 MEMORY            
 FEDERATED  

MyISAM引擎特點

 不支援事務
 表級鎖定，當表鎖定時，其他人都無法使用，影響併發性範圍大
 讀寫相互阻塞，寫入不能讀，讀時不能寫
 只快取索引
 不支援外來鍵約束
 不支援聚簇索引
 讀取資料較快，佔用資源較少
 不支援MVCC（多版本併發控制機制）高併發
 崩潰恢復性較差
 MySQL5.5.5前預設的資料庫引擎<br>  資料庫有三個檔案，有frm、MYD、MYI字尾的檔案

MyISAM儲存引擎適用場景

 只讀（或者寫較少）、表較小（可以接受長時間進行修復操作）

MyISAM引擎檔案

 tbl_name.frm 表格式定義
 tbl_name.MYD 資料檔案
 tbl_name.MYI 索引檔案

InnoDB引擎特點

 行級鎖
 支援事務，適合處理大量短期事務
 讀寫阻塞與事務隔離級別相關
 可快取資料和索引
 支援聚簇索引
 崩潰恢復性更好
 支援MVCC高併發
 從MySQL5.5後支援全文索引
 從MySQL5.5.5開始為預設的資料庫引擎<br>  資料庫有兩個檔案frm和idb字尾的檔案

InnoDB支援更好的特性：Percona-XtraDB, Supports transactions（支援事務）, row-level locking（行級鎖）, and foreign keys（支援外來鍵）

事務含義：

 1.由一系列動作組合起來的一個完整的整體，需要將所有的動作全部做掉，要麼全不做，具有原子性。
 2.（rollback）支援回滾、撤銷之前未做完的事務。

InnoDB資料庫檔案

 所有InnoDB表的資料和索引放置於同一個表空間中
 表空間檔案：datadir定義的目錄下
 資料檔案：ibddata1, ibddata2, ...
 每個表單獨使用一個表空間儲存表的資料和索引
 啟用：innodb_file_per_table=ON

參看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table ON (>= MariaDB 5.5) 兩類檔案放在資料庫獨立目錄中

 資料檔案(儲存資料和索引)：tb_name.ibd
 表格式定義：tb_name.frm

其它儲存引擎

 Performance_Schema：Performance_Schema資料庫使用
 Memory ：將所有資料儲存在RAM中，以便在需要快速查詢參考和其他類似資料的環境中進行快速訪問。適用存放臨時資料。引擎以前被稱為HEAP引擎
 MRG_MyISAM：使MySQL DBA或開發人員能夠對一系列相同的MyISAM表進行邏輯分組，並將它們作為一個物件引用。適用於VLDB(Very Large Data Base)環境，如資料倉儲
 Archive ：為儲存和檢索大量很少參考的存檔或安全稽核資訊，只支援SELECT和INSERT操作；支援行級鎖和專用快取區
 Federated聯合：用於訪問其它遠端MySQL伺服器一個代理，它通過建立一個到遠端MySQL伺服器的客戶端連線，並將查詢傳輸到遠端伺服器執行，而後完成資料存取，提供連結單獨MySQL伺服器的能力，以便從多個物理伺服器建立一個邏輯資料庫。非常適合分散式或資料集市環境
 BDB：可替代InnoDB的事務引擎，支援COMMIT、ROLLBACK和其他事務特性
 Cluster/NDB：MySQL的簇式資料庫引擎，尤其適合於具有高效能查詢要求的應用程式，這類查詢需求還要求具有最高的正常工作時間和可用性
 CSV：CSV儲存引擎使用逗號分隔值格式將資料儲存在文字檔案中。可以使用CSV引擎以CSV格式匯入和匯出其他軟體和應用程式之間的資料交換
 BLACKHOLE ：黑洞儲存引擎接受但不儲存資料，檢索總是返回一個空集。該功能可用於分散式資料庫設計，資料自動複製，但不是本地儲存
 example：“stub”引擎，它什麼都不做。可以使用此引擎建立表，但不能將資料儲存在其中或從中檢索。目的是作為例子來說明如何開始編寫新的儲存引擎
 MariaDB支援的其它儲存引擎：
 OQGraph
 SphinxSE
 TokuDB
 Cassandra
 CONNECT
 SQUENCE

三、管理儲存引擎

 檢視mysql支援的儲存引擎
 show engines;
 檢視當前預設的儲存引擎
 show variables like '%storage_engine%';
 設定預設的儲存引擎
 vim /etc/my.conf
 [mysqld]
 default_storage_engine= InnoDB
 檢視庫中所有表使用的儲存引擎
 show table status from db_name;
 檢視庫中指定表的儲存引擎
 show table status like ' tb_name ';
 show create table tb_name;
 設定表的儲存引擎：
 CREATE TABLE tb_name(... ) ENGINE=InnoDB;
 ALTER TABLE tb_name ENGINE=InnoDB;

面試題:請你列舉MySQL中支援的儲存引擎種類？

 InnoDB、MyISAM、CSV、MEMORY

分支產品的引擎種類介紹

 - PerconaDB：預設是XtraDB
 - MariaDB：預設是InnoDB
 - 其他引擎：TokuDB、MyRocks、Rocksdb
   - 特點：壓縮比15倍以上，插入資料效能快3-5倍

適應場景：

 例如Zabbix監控類的平臺、歸檔庫、歷史資料儲存業務
 InnoDB儲存引擎特性
 MVCC       ： 多版本併發控制 
 聚簇索引   ： 用來組織儲存資料和優化查詢，IOT。
 支援事務   ： 資料安全保證
 支援行級鎖 ： 控制併發
 外來鍵
 多緩衝區支援
 自適應Hash索引： AHI
 複製中支援高階特性。
 備份恢復： 支援熱備。
 自動故障恢復：CR  Crash Recovery
 雙寫機制：DWB  Double Write Buffer

案例1 (專案)

 某期學員負責: 運維 + MySQL 工作
  環境: zabbix 3.2+centos7.3+mariaDB 5.5 InnoDB引擎,zabbix系統 監控了2000多個節點服務
 現象:每隔一段時間zabbix卡的要死,每隔3-4個月,都要重新搭建一遍zabbix,儲存空間經常爆滿.
  問題 
 zabbix 版本過低。
  資料庫版本
 zabbix資料庫500G,存在一個檔案裡ibdata1，手工刪除1個月之前的資料，空間不釋放。

優化建議:

 資料庫版本升級到percona 5.7+ 版本 mariadb 10.x+,zabbix升級更高版本
 儲存引擎改為tokudb 
 監控資料按月份進行切割(二次開發:zabbix 資料保留機制功能重寫,資料庫分表)
 關閉binlog和雙1
 引數調整....
 優化結果:
 監控狀態良好

參考： https://www.jianshu.com/p/898d2e4bd3a7

為什麼選用tokudb?

 MariaDB 10.0.9原生態支援TokuDB,另外經過測試環境,5.7要比5.5 版本效能 高  2-3倍
 TokuDB:insert資料比Innodb快的多，資料壓縮比要Innodb高
 監控資料按月份進行切割,為了能夠truncate每個分割槽表,立即釋放空間
 關閉binlog ----->減少無關日誌的記錄.
 引數調整...----->安全性引數關閉,提高效能.

擴充套件：部署 zabbix新版+ 新版本 tokudb VS 部署 zabbix + 低版本mariadb Tokudb特性：

TokuDB獨有的其他功能包括：

• 高達25倍的資料壓縮

• 快速插入

• 通過無讀複製消除從機延遲

• 熱架構更改

• 熱索引建立 - TokuDB表支援插入、刪除和查詢，而索引新增到該表時沒有停機時間

• 熱列新增、刪除、擴充套件和重新命名 — 當 alter table 新增、刪除、擴充套件或重新命名列時，TokuDB表支援不停機插入、刪除和查詢

• 線上備份

參考內容： https://www.jianshu.com/p/898d2e4bd3a7 https://mariadb.com/kb/en/installing-tokudb/ https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_installation.html

 Additional features unique to TokuDB include:
 Up to 25x Data Compression
 Fast Inserts
 Eliminates Slave Lag with Read Free Replication
 Hot Schema Changes
 Hot Index Creation - TokuDB tables support insertions, deletions and queries with no down time while indexes are being added to that table
 Hot column addition, deletion, expansion, and rename - TokuDB tables support insertions, deletions and queries without down-time when an alter table adds, deletes, expands, or renames columns
 On-line Backup

案例2:(專案)

環境: centos 5.8 ,MySQL 5.0版本,MyISAM儲存引擎,網站業務(LNMP),資料量50G左右 現象問題: 業務壓力大的時候,非常卡;經歷過當機,會有部分資料丟失.

問題分析:

 1.MyISAM儲存引擎表級鎖,在高併發時,會有很高鎖等待
 2.MyISAM儲存引擎不支援事務,在斷電時,會有可能丟失資料

職責

 1.監控鎖的情況:有很多的表鎖等待
 2.儲存引擎檢視:所有表預設是MyISAM

解決方案:

 升級MySQL 5.6.1x版本
 升級遷移所有表到新環境，調整儲存引擎為InnoDB
 開啟雙1安全引數
 重構主從

儲存引擎的基本操作

檢視儲存引擎 查詢支援的儲存引擎

 mysql> show engines;

查詢、設定預設儲存引擎

 -- 會話級別(僅影響當前會話)
 set default_storage_engine=myisam;
 -- 全域性級別(僅影響新會話)重啟失效
 set global default_storage_engine=myisam;
 -- 寫入配置檔案，重啟永久生效
 vim /etc/my.cnf
 [mysqld]
 default_storage_engine=InnoDB

儲存引擎是作用在表上的，也就意味著，不同的表可以有不同的儲存引擎型別。

檢視、設定 表的儲存引擎

（1） 檢視某張表的儲存引擎

 SHOW create table 表名;
 use 表名;
 SHOW TABLE STATUS LIKE 'countrylanguage'\G

（2） 查詢系統中所有業務表的儲存引擎資訊

  mysql> select table_schema,table_name ,engine 
  from information_schema.tables  
  where table_schema not in ('sys','mysql','information_schema','performance_schema');

（3）建立表設定儲存引擎

 create table 表名 (id int) engine=innodb charset=utf8mb4;

（4）修改已有表的儲存引擎

 alter table 庫名.表名 engine=innodb;

專案:將所有的非InnoDB引擎的表查詢出來，批量修改為InnoDB

1. 查詢：

 SELECT table_schema, table_name, ENGINE 
 FROM information_schema.tables  
 WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema')
 AND ENGINE !='innodb';

2. 開啟匯出檔案功能

 vim /etc/my.cnf 
 [mysqld]
 secure-file-priv=/tmp

3. 構建批量修改語句：

 SELECT CONCAT("alter table ",table_schema,".",table_name," engine=innodb;") 
 FROM information_schema.tables 
 WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema') 
 AND ENGINE !='innodb' INTO OUTFILE '/tmp/a.sql';

4. 執行批量修改語句：

 source /tmp/a.sql

InnoDB 儲存引擎的體系結構

磁碟結構 （on-disk）

ibdata1：系統資料字典資訊(統計資訊)，UNDO表空間等資料 ib_logfile0 ~ ib_logfile1: REDO日誌檔案，事務日誌檔案。 ibtmp1： 臨時表空間磁碟位置，儲存臨時表 frm：儲存表的列資訊 ibd：表的資料行和索引

myisam	InnoDB
.frm 資料字典	.ibd 資料行和索引
.myd 資料行	.frm 單表資料字典
.myi 索引	ibdata1

表空間結構

介紹:表空間的概念源於Oracle資料庫。最初的目的是為了能夠很好的做儲存的擴容。

  共享（系統）表空間
  儲存方式
 ibdata1~ibdataN, 5.5版本預設的表空間型別.
 ibdata1共享表空間在各個版本的變化

5.5版本：

 系統相關:（全域性）資料字典資訊（表基本結構資訊、狀態、系統引數、屬性..）、UNDO回滾日誌(記錄撤銷操作)、Double Write Buffer資訊、臨時表資訊、change buffer
     使用者資料:表資料行、表的索引資料

5.6版本：

 共享表空間只儲存於系統資料，把使用者資料獨立了，獨立表空間管理。
 系統相關:（全域性）資料字典資訊、UNDO回滾資訊、Double Write資訊、臨時表資訊、change buffer

5.7版本：

 在5.6基礎上，把臨時表獨立出來，UNDO也可以設定為獨立
 系統相關：(全域性)資料字典資訊、UNDO回滾資訊、Double Write資訊、change buffer

 

8.0.11~8.0.19版本：

 在5.7的基礎上將UNDO回滾資訊預設獨立，資料字典不再集中儲存了。
 系統相關：Double Write資訊、change buffer

8.0.20版本:

https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

在之前版本基礎上，獨立 Double Write資訊 系統相關：change buffer

 

 

總結：

 對於InnoDB表來講，例如 city表 
 city.ibd 
 city.frm 
 ibdata1 
 只是通過cp備份ibd和frm檔案無法實現,資料表的恢復

雙一標準

 innoda_file_per_table=1，1代表獨立表空間，5.6版預設模式；0代表共享表空間，5.6之前的預設模式
 5.6版之前表空間沒有獨立出來，存放在ibdata1檔案中。設為1後建立的表會在data目錄中生成表名.ibd檔案，
 設定為0後建立的表不會生成該檔案，會把.ibd中的內容存放到ibdata1檔案中。
 # 儲存引擎配置：
 default_storage_engine=innodb(5.6的預設引擎)
 # 配置共享表空間檔案個數和大小（即ibdata1檔案，該檔案成為共享表空間）：
 參考：https://www.cnblogs.com/quzq/p/12833135.html
 innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
 該配置通常在初始化之前配好，會生成兩個檔案
 # 雙一標準的其中一個（預設是1）
 innodb_flush_log_at_trx_commit=1，用於控制redo log buffer中資料寫入磁碟redo log檔案的。
 值1代表什麼呢？（redo log buffer，data buffer poll, undo log buffer都是存在於mysql記憶體中的）
 mysql啟動後會向作業系統申請專用的記憶體空間，配置為1代表在commit命令後會立即把redo log buffer
 遞交到作業系統記憶體中，然後由作業系統再立即寫入到磁碟的redo log檔案中。
 值0代表每秒執行一次把redo log buffer遞交到作業系統記憶體，作業系統記憶體也每秒往redo log中寫入一次。
 因為是每秒一次，如果在1秒內發生大量的事務遞交，突然當機，會造成1秒間隔內發生的事務資料丟失
 值2代表每次commit後立即把redo log buffer資料遞交到作業系統記憶體，然後作業系統每秒往redo log中寫入一次
 缺點和0一樣，只不過能好一點，如果只是mysql服務當機的話，提交到作業系統記憶體的事務還不會丟失。
 補充：無論哪個值，redo log buffer遞交到作業系統記憶體的日誌都會包含所有，無論該事務是否commit.
 # 雙一表中的另一個
 sync_binlog=1    每次事務遞交都立即把二進位制日誌刷寫到磁碟。
 雙一標準都是用來控制mysql記憶體資料刷寫到磁碟的頻率，一個用來控制redo log, 一個用來控制二進位制日誌的
 二進位制日誌相關參考：https://www.cnblogs.com/quzq/p/12866410.html
 # 控制mysql記憶體中logs到磁碟的過程
 innodb_flush_method=o_direct或fsync或o_dsync, 控制的是redo log buffer和data buffer pool，過程如下：
 預設使用的是fsync模式，建議使用o_direct模式
 #結合上兩個引數給出個建議配置如下：
 1.最高安全模式：
   innodb_flush_log_at_trx_commit=1
   innodb_flush_method=o_direct
 2.最高效能模式（安全不是特別重要場景）：
   innodb_flush_log_at_trx_commit=0
   innodb_flush_method=fsync
 # 三個和redo日誌設定有關的引數：
  1.innodb_log_buffer_size=16777216,  設定redo log     buffer記憶體區的大小
  2.innodb_log_file_size=50331648， 設定redo log的兩個檔案大小
  3.innodb_log_files_in_group=3，控制redo log的檔案數，預設是0和1兩個檔案
 # 髒頁刷寫策略：
 innodb_max_dirty_pages_pct=75, 75為百分比，控制data buffer pool中髒頁資料佔比達到75%時自動觸發CKPT
 和WAL機制把data buffer pool中的資訊刷寫到ibd檔案中,當然日誌也是優先於資料寫入到redo log中的。
 補充：哪些場景會觸發髒頁資料寫入ibd檔案（CKPT）呢？
 CSR機制。 參考：https://www.cnblogs.com/quzq/p/12839958.html
 redo檔案滿了。通常redo log中的資訊當髒頁資料寫回ibd後，redo log中的日誌就沒用了，可以被覆蓋寫掉。

 

 

 

 

 

 

 

共享表空間管理

擴容共享表空間

 mysql> select @@innodb_data_file_path;
 +-------------------------+
 | @@innodb_data_file_path |
 +-------------------------+
 | ibdata1:12M:autoextend  |
 +-------------------------+
 1 row in set (0.00 sec)
 mysql> select @@innodb_autoextend_increment;
 +-------------------------------+
 | @@innodb_autoextend_increment |
 +-------------------------------+
 |                            64 |
 +-------------------------------+
 1 row in set (0.00 sec)

引數用途：ibdata1檔案，預設初始大小12M，不夠用會自動擴充套件，預設每次擴充套件64M擴容

① 初始化後設定共享表空間，重啟生效。

 vim /etc/my.cnf
 [mysqld]
 innodb_data_file_path=ibdata1:12M;ibdata2:64M;ibdata3:64M:autoextend

注意：ibdata1必須和當前檔案時間大小一致

錯誤處理：

ibdata1設定值和當前檔案實際大小不一致，重啟資料庫報錯，檢視日誌檔案

 tail -10 /data/3306/data/db01.err | grep ERROR
 ... ...
 [ERROR] InnoDB: The innodb_system data file './ibdata1' is of a different size 4864 pages (rounded down to MB) than the 768 pages specified in the .cnf file!
 ... ...

實際大小：4864*16K/1024=76M

my.cnf檔案設定大小：768*16K/1024=12M

檢視ibdata1實際大小

 [root@db01 ~]# ls -lh /data/3306/data/ibdata1 
 -rw-r----- 1 mysql mysql 76M May  6 17:11 ibdata1

② 初始化前設定共享表空間（生產建議）

5.7 中建議：設定共享表空間2-3個，大小建議1G或者4G，最後一個定製為自動擴充套件。

8.0 中建議：設定1-2個就ok，大小建議1G或者4G，最後一個定製為自動擴充套件。

---

獨立表空間

從5.6開始，預設表空間不再使用共享表空間，替換為獨立表空間。

 主要儲存的是使用者資料
 儲存特點為：一個表一個ibd檔案，儲存資料行和索引資訊
 基本表結構後設資料儲存：
 xxx.frm     
 最終結論：
       後設資料            資料行+索引
 mysql表資料    =（ibdataX+frm）+ibd(段、區、頁)
         DDL             DML+DQL
 ​
 MySQL的儲存引擎日誌：
 Redo Log: ib_logfile0  ib_logfile1，重做日誌
 Undo Log: ibdata1 ibdata2(儲存在共享表空間中)，回滾日誌
 臨時表:ibtmp1，在做join union操作產生臨時資料，用完就自動

獨立表空間設定

 -- 檢視控制引數
 select @@innodb_file_per_table;
 -- 獨立表空間儲存使用者資料
 set global innodb_file_per_table=1;
 -- 共享表空間儲存使用者資料
 set global innodb_file_per_table=0;

案例背景:

 硬體及軟體環境:
 聯想伺服器（IBM） 
 磁碟500G 沒有raid
 centos 6.8
 mysql 5.6.33  innodb引擎  獨立表空間
 備份沒有，日誌也沒開
 ​
 開發使用者專用庫:
 jira(bug追蹤) 、 confluence(內部知識庫)    ------>LNMT

故障描述:

 斷電了，啟動完成後“/” 只讀
 fsck  重啟,系統成功啟動,mysql啟動不了。
 結果：confulence庫在  ， jira庫不見了

 求助：
 這種情況怎麼恢復？
 我問：
 有備份沒
 求助：
 連二進位制日誌都沒有，沒有備份，沒有主從
 我說：
 沒招了，jira需要硬碟恢復了。
 求助：
 1、jira問題拉倒中關村了
 2、能不能暫時把confulence庫先開啟用著
 將生產庫confulence，拷貝到1:1虛擬機器上/var/lib/mysql,直接訪問時訪問不了的
 ​
 問：有沒有工具能直接讀取ibd
 我說：我查查，最後發現沒有

我想出一個辦法來：

 表空間遷移:

 create table xxx
 alter table  confulence.t1 discard tablespace;
 alter table confulence.t1 import tablespace;
 虛擬機器測試可行。

處理問題思路:

 confulence庫中一共有107張表。
 1、建立107和和原來一模一樣的表。
 他有2016年的歷史庫，我讓他去他同時電腦上 mysqldump備份confulence庫
 mysqldump -uroot -ppassw0rd -B  confulence --no-data >test.sql
 拿到你的測試庫，進行恢復
 到這步為止，表結構有了。
 2、表空間刪除。
 select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
 source /tmp/discard.sql
 執行過程中發現，有20-30個表無法成功。主外來鍵關係
 很絕望，一個表一個表分析表結構，很痛苦。
 set foreign_key_checks=0 跳過外來鍵檢查。
 把有問題的表表空間也刪掉了。
 3、拷貝生產中confulence庫下的所有表的ibd檔案拷貝到準備好的環境中
 select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
 4、驗證資料
 表都可以訪問了，資料挽回到了出現問題時刻的狀態
   課後練習作業：
 案例2  ：  MySQL 5.7 中誤刪除了ibdata1檔案，導致資料無法啟動，如何恢復t100w,假設一共100張表，表結構無法通過show create table 獲得。
 提示：萬一是自研資料庫,怎麼辦？又沒備份，那怎麼辦？
 mysql工具包，mysqlfrm 讀取frm檔案獲得表結構。
 ./mysqlfrm  /data/3306/data/test/t100w.frm --diagnostic
 select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
 source /tmp/discard.sql
 #刪除ib_logfile0
 #刪除ib_logfile1
 不要刪除ibdata1 ibdata2  ibdata3

獲取表結構

8.0之前

可以使用MySQL Utilities提供的mysqlfrm用來讀取.frm檔案，並從該檔案中找到表定義資料，生成CREATE語句。

 cd /opt
 wget https://downloads.mysql.com/archives/get/p/30/file/mysql-utilities-1.6.5.tar.gz
 tar -xvzf mysql-utilities-1.6.5.tar.gz
 python /opt/mysql-utilities-1.6.5/setup.py build
 python /opt/mysql-utilities-1.6.5/setup.py install
 # 獲取獨立表空間的表結構
 mysqlfrm --diagnostic 表名.frm | grep -v "^#" > /tmp/db_table.sql

注意：.frm檔案中沒有外來鍵約束和自增長序列的資訊

刪除表空間前可以設定跳過外來鍵檢查來規避問題

 set foreign_key_checks=0

8.0之後

可以使用ibd2sdi離線的將ibd檔案中的冗餘儲存的SDI資訊提取出來，並以json的格式輸出到終端。

參考文章：英文原文 中文翻譯

1. 把 表名.ibd 中的表結構以json的格式輸出到 dbsdi.json檔案

 ibd2sdi --dump-file=dbsdi.json  表名.ibd

注意：當存在中文註釋時，解析出來的註釋可能是亂碼的，而且大概率會觸發ibd2sdi的bug（中文亂碼導致json格式錯誤，比如缺少引號）。

此時可以使用vscode開啟dbsdi.json，vscode會高亮json檔案格式正確的部分，手動修復不正確的格式，儲存。

2. 使用jq提取json裡的資料

   CentOS 使用yum安裝

   通用命令

    ibd2sdi 表名.ibd |jq  '.[]?|.[]?|.dd_object?|({table:.name?},(.columns?|.[]?|{name:.name,type:.column_type_utf8}))' > dbsdi-jq.json

   Windows 下載可執行檔案安裝

   Powershell呼叫jq解析json檔案

    Get-Content -Path dbsdi.json |jq  '.[]?|.[]?|.dd_object?|({table:.name?},(.columns?|.[]?|{name:.name,type:.column_type_utf8}))' > dbsdi-jq.json

---

撤銷表空間

儲存撤消日誌，用來回滾事務。

撤銷表空間檢視配置引數

 -- 開啟獨立undo模式，並設定undo的個數，建議3-5個，8.0棄用
 SELECT @@innodb_undo_tablespaces;
 -- undo日誌的大小，預設1G
 SELECT @@innodb_max_undo_log_size;
 -- 開啟undo自動回收的機制（undo_purge）
 SELECT @@innodb_undo_log_truncate;
 -- 觸發自動回收的條件，單位是檢測次數
 SELECT @@innodb_purge_rseg_truncate_frequency;
 -- undo檔案儲存路徑
 SELECT @@innodb_undo_directory;

撤銷表空間配置

5.7版本

預設儲存在共享表空間中（ibdataN），生產中必須手工獨立出來，否則影響高併發效率。

只能在初始化時配置undo個數，並且是固定的。

 # 1.建立目錄
 [root@db01 ~]# mkdir /data/3357/{data,etc,socket,log,pid,undologs} -pv
 [root@db01 ~]# chown -R mysql. /data/*
 ​
 # 2.新增引數
 [root@db01 ~]# vim /data/3357/my.cnf
 [mysqld]
 innodb_undo_tablespaces=3           
 innodb_max_undo_log_size=128M
 innodb_undo_log_truncate=ON
 innodb_purge_rseg_truncate_frequency=32
 innodb_undo_directory=/data/3357/undologs
 # 3.初始化資料庫
 [root@db01 ~]# /usr/local/mysql57/bin/mysqld --defaults-file=/data/3357/my.cnf  
--initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/3357/data
 # 4.啟動資料庫
 [root@db01 ~]# /etc/init.d/mysqld start
 # 5.檢視結果
 [root@db01 ~]# ll /data/3357/undologs/
 -rw-r----- 1 mysql mysql 10485760 May  11 15:39 /data/3357/undologs/undo001
 -rw-r----- 1 mysql mysql 10485760 May  11 15:39 /data/3357/undologs/undo002
 -rw-r----- 1 mysql mysql 10485760 May  11 15:39 /data/3357/undologs/undo003

8.0版本

預設就是獨立的（undo_001-undo_002），可以隨時配置，innodb_undo_tablespaces選項已過時。

 -- 查詢所有表空間檔案
 SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES;
 ​
 -- 查詢undo表空間
 SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
 ​
 -- 新增undo表空間
 CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';
 ​
 -- 刪除undo表空間
 -- 必須為空，先標記為非活動狀態，再刪除
 ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
 DROP UNDO TABLESPACE tablespace_name;
 ​
 -- 監視undo表空間的狀態
 SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE 'tablespace_name';

---

undo 表空間管理

檢視undo的配置引數

 SELECT @@innodb_undo_tablespaces;  ---->3-5個    #開啟獨立undo模式，並設定undo的個數。
 SELECT @@innodb_max_undo_log_size;               #undo日誌的大小，預設1G。
 SELECT @@innodb_undo_log_truncate;               #開啟undo自動回收的機制（undo_purge）。
 SELECT @@innodb_purge_rseg_truncate_frequency;   #觸發自動回收的條件，單位是檢測次數。

#如果進行undo獨立儲存到其他檔案系統

 a. 關閉資料庫：
 [root@db01 data]# systemctl start mysqld3357
 b.設定路徑引數 
 innodb_undo_directory=/data/3357/undologs 
 c. 建立目錄,並拷貝檔案
 [root@db01 data]# systemctl stop mysqld3357
 mkdir -p  /data/3357/undologs 
 chown -R mysql. /data/* 
 cp -a /data/3357/data/undo* /data/3357/undologs 

注： 8.0 undo表空間與5.7的區別

參考

https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

臨時表空間

5.7版本

臨時表空間（ibtmp1）用於儲存臨時表。建議資料初始化之前設定好，一般2-3個，大小512M-1G。

臨時表空間檢視配置引數

 mysql> select @@innodb_temp_data_file_path;
 +------------------------------+
 | @@innodb_temp_data_file_path |
 +------------------------------+
 | ibtmp1:12M:autoextend        |
 +------------------------------+

配置檔案設定，重啟生效

 [root@db01 ~]# vim /etc/my.cnf
 [mysqld]
 innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:128M:autoextend:max:500M

8.0版本

分為會話臨時表空間和全域性臨時表空間

• 會話臨時表空間（temp_N.ibt）用於儲存臨時表。

  位置引數

   mysql> select @@innodb_temp_tablespaces_dir;
   +-------------------------------+
   | @@innodb_temp_tablespaces_dir |
   +-------------------------------+
   | ./#innodb_temp/               |
   +-------------------------------+

• 全域性臨時表空間（ibtmp1）用於儲存對使用者建立的臨時表進行更改的回滾段。

  配置同5.7版本的臨時表空間

---

重做日誌(Redo Log)

Redo Log 記錄記憶體資料頁的變化（資料頁的變化資訊+資料頁當時的LSN號）。實現“前滾”的功能。

儲存在資料路徑下（ib_logfile0，ib_logfile1，...），輪序覆蓋記錄日誌。

重新整理策略：commit提交後，重新整理當前事務的 redo buffer 到磁碟，還會順便將一部分 redo buffer 中沒有提交的事務日誌也重新整理到磁碟。

WAL（write ahead log）：保證 Redo Log 優先於資料寫入磁碟。

---

查詢配置引數

 mysql> show variables like '%innodb_log_file%';
 +---------------------------+----------+
 | Variable_name             | Value    |
 +---------------------------+----------+
 | innodb_log_file_size      | 50331648 |
 | innodb_log_files_in_group | 2        |
 +---------------------------+----------+

設定

生產建議： 設定3-5組，512M-4G

配置檔案新增引數，重啟生效

 [root@db01 ~]# vim /etc/my.cnf 
 [mysqld]
 innodb_log_file_size=100M
 innodb_log_files_in_group=3

---

回滾日誌(undo log)

Undo Log 是撤消日誌的集合，提供快照技術，儲存事務修改之前的資料狀態，保證了MVCC，隔離性，mysqldump的熱備。

• 在rolback時，將資料恢復到修改之前的狀態。

• 在實現CSR時，回滾到redo當中記錄的未提交的時候。

5.7版本，儲存在共享表空間中 （ibdata1~ibdataN）

8.0版本

對常規表執行操作的事務的撤消日誌儲存在撤消表空間中（undo_001-undo_002）。 對臨時表執行操作的事務的撤消日誌儲存在全域性臨時表空間中（ibtmp1）。

每個撤消表空間和全域性臨時表空間分別支援最多128個回滾段。

配置回滾段的數量

 select @@innodb_rollback_segments;

---

雙寫緩衝區 Double Write Buffer（DWB）

雙寫緩衝區是一個儲存區域，InnoDB先將從頁面緩衝池中重新整理的頁面寫入雙寫緩衝區，然後再將頁面寫入InnoDB資料檔案中。

如果在頁面寫入過程中，發生作業系統，儲存子系統或mysqld程式的意外退出，則InnoDB可以在崩潰恢復期間從doublewrite緩衝區中找到頁面的良好副本。

8.0.19前預設位於ibdataN中，8.0.20後就獨立出來位於#*.dblwr。

---

預熱檔案(ib_buffer_pool)

用來緩衝和快取“熱”（經常查詢或修改）資料頁，減少物理IO。MySQL 5.7預設啟用。

當關閉資料庫的時候，緩衝和快取會失效。5.7版本後，MySQL正常關閉時，會將記憶體的熱資料存放（流方式）至ib_buffer_pool。下次重啟直接讀取ib_buffer_pool載入到記憶體中。

查詢配置引數

指定在關閉MySQL伺服器時是否記錄InnoDB 緩衝池中快取的頁面 ，以縮短下次重啟時的預熱過程。innodb_buffer_pool_dump_pct 選項定義要轉儲的最近使用的緩衝池頁面的百分比。

 select @@innodb_buffer_pool_dump_at_shutdown;
 select @@innodb_buffer_pool_load_at_startup;

---

InnoDB記憶體結構

緩衝池 InnoDB BUFFER POOL（IBP）

緩衝池主要用來緩衝、快取MySQL的資料頁和索引頁，還有AHI、Change buffer。MySQL中最大的、最重要的記憶體區域。

①配置InnoDB緩衝池大小

 -- 檢視快取池大小，預設128M
 mysql> select @@innodb_buffer_pool_size;
 +---------------------------+
 | @@innodb_buffer_pool_size |
 +---------------------------+
 |                 134217728 |
 +---------------------------+

生產建議：實體記憶體的：50-80%

全域性設定： 重新連線mysql生效。

 set global innodb_buffer_pool_size=268435456;

永久設定：配置檔案新增引數，重啟mysql生效

 vim /etc/my.cnf 
 [mysqld]
 innodb_buffer_pool_size=256M

② 配置多個緩衝池例項

 -- 查詢緩衝池例項數量，預設1，最大為64
 mysql> select @@innodb_buffer_pool_instances;
 +--------------------------------+
 | @@innodb_buffer_pool_instances |
 +--------------------------------+
 |                              1 |
 +--------------------------------+

注意：僅當您將innodb_buffer_pool_size大小設定為1GB或更大時，此選項才生效，是所有緩衝池例項大小之和。

為了獲得最佳效率，請組合 innodb_buffer_pool_instances 和innodb_buffer_pool_size使得每個緩衝池例項是至少為1GB。

---

日誌緩衝區 InnoDB LOG BUFFER (ILB)

用於儲存要寫入磁碟上的日誌檔案(Redo Log)的資料。

查詢配置引數

 select @@innodb_log_buffer_size;

預設大小：16M ​ 生產建議：innodb_log_file_size的1-N倍 永久設定：配置檔案新增引數，重啟mysql生效

 vim /etc/my.cnf
 [mysqld]
 innodb_log_buffer_size=33554432

利用獨立表空間進行快速資料遷移

 源端:3306/test/t100w  -----> 目標端：3307/test/t100w
 鎖定源端t100w表 

 mysql> flush tables  test.t100w with read lock ;
 mysql> show create table test.t100w;
 CREATE TABLE t100w (
  id int(11) DEFAULT NULL,
  num int(11) DEFAULT NULL,
  k1 char(2) DEFAULT NULL,
  k2 char(4) DEFAULT NULL,
  dt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 目標端建立test庫和t100w空表
 [root@db01 ~]# systemctl start mysqld3307
 [root@db01 ~]# mysql -S /tmp/mysql3307.sock
 mysql> create database test charset=utf8mb4;
 CREATE TABLE t100w (
   id int(11) DEFAULT NULL,
   num int(11) DEFAULT NULL,
   k1 char(2) DEFAULT NULL,
   k2 char(4) DEFAULT NULL,
   dt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 單獨刪除空的表空間檔案
 mysql> alter table test.t100w discard tablespace;
 拷貝源端ibd檔案到目標端目錄，並設定許可權
 [root@db01 test]# cp -a /data/3306/data/test/t100w.ibd     /data/3307/data/test/
 [root@db01 test]# chown -R mysql.mysql /data/*
 匯入表空間
 mysql> alter table test.t100w import tablespace;
 mysql> select count(*) from test.t100w;
 +----------+
 | count(*) |
 +----------+
 |  1000000 |
 解鎖源端資料表
 mysql> unlock tables;

事務的工作流程原理（ACID特性如何保證）

 #重做日誌 （redo log）
 ib_logfile0~N   48M   , 輪詢使用
 # 日誌緩衝區
 redo log buffer ： redo記憶體區域
 # 表空間資料檔案
 ibd  ： 儲存資料行和索引 
 # 資料緩衝區
 InnoDB buffer pool ： 緩衝區池,資料和索引的緩衝
 # 日誌序列號 
 LSN
 磁碟資料頁(ibd檔案的page),redo log檔案（ib_logfile）,Innodb_buffer_pool中的資料頁,redo buffer
 MySQL 每次資料庫啟動,都會比較磁碟資料頁和redolog的LSN,必須要求兩者LSN一致資料庫才能正常啟動
 #WAL : Write Ahead Log
 Redo日誌優先於資料頁寫到磁碟。
 # 髒頁: Dirty Page
 記憶體髒頁,記憶體中發生了修改,沒寫入到磁碟之前,我們把記憶體頁稱之為髒頁.
 # CheckPoint
 CKPT:檢查點,就是將髒頁重新整理到磁碟的動作
 #DB_TRX_ID（6位元組）  事務ID號
 InnoDB會為每一個事務生成一個事務號,伴隨著整個事務生命週期.
 #DB_ROLL_PTR（7位元組） 回滾指標
 rollback 時，會使用 undo 日誌回滾已修改的資料。DB_ROLL_PTR指向了此次事務的回滾位置點，用來找到undo日誌資訊。
  事務工作流程原理
 事務舉例： 
 begin; 
 update t1 set A=2 where A=1;
 commit;
 #redo log 重做日誌如何應用
 1.使用者發起update事務語句，將磁碟資料頁（page100,A=1,LSN=1000）載入到記憶體(buffer_pool)緩衝區。
 2.在記憶體中發生資料頁修改（A=1改成A=2），形成髒頁，更改中資料頁的變化，記錄到redo buffer中，加入1000個位元組日誌。LSN=1000+1000=2000。
 3. 當commit語句執行時，基於WAL機制，等到redo buffer中的日誌完全落盤到ib_logfileN中，commit正式完成。
 4. ib_logfileN中記錄了一條日誌。內容：page100資料頁變化+LSN=2000。
    ##情景： 當此時，redo落地了，資料頁沒有落地，當機了。
 5. MySQL CR（自動故障恢復）工作模式，啟動資料庫時，自動檢查redo的LSN和資料頁LSN。
 6. 如果發現redoLSN資料頁的LSN,載入原始資料頁+變化redo指定記憶體。使用redo重構髒頁（前滾）。
 7. 如果確認此次事務已經提交（commit標籤），立即觸發CKPT動作，將髒頁刷寫到磁碟上。
 •   MySQL有一種機制，批量刷寫redo的機制。會在A事務commit時，順便將redo buffer中的未提交的redo日誌也一併刷到磁碟。
 •   為了區分不同狀態的redo，日誌記錄時，會標記是否COMMIT。
 redo保證了ACID哪些特性?
 主要是D的特性，另外A、C也有間接關聯。
 undo log 回滾日誌如何應用？
 1. 事務發生資料頁修改之前，會申請一個undo事務操作，儲存事務回滾日誌（逆向操作的邏輯日誌）。
 2. undo寫完之後，事務修改資料頁頭部（會記錄DB_TRX_ID+DB_ROLL_PTR），這個資訊也會被記錄的redo。
 情景1： 
 當執行rollback命令時。根據資料頁的DB_TRX_ID+DB_ROLL_PTR資訊，找到undo日誌，進行回滾。
 情景2：
 begin; 
 update t1 set A=2 where A=1;
 當機。
 假設： undo 有  ， redo沒有
 啟動資料庫時，檢查redo和資料頁的LSN號碼。發現是一致的。
 所以不需要進行redo的前滾，此時也不需要回滾。undo資訊直接被標記為可覆蓋狀態。
 假設：undo 有，redo也有（沒有commit標籤。）
 3. MySQL CR（自動故障恢復）工作模式，啟動資料庫時，自動檢查redo的LSN和資料頁LSN。
 4. 如果發現redoLSN資料頁的LSN ，載入原始資料頁+變化redo指定記憶體。使用redo重構髒頁（前滾）。
 5. 如果確認此次事務沒有commit標記，立即觸發回滾操作，根據DB_TRX_ID+DB_ROLL_PTR資訊，找到und回滾日誌，實現回滾。
 以上流程被稱之為InnoDB的核心特性：自動故障恢復（Crash Recovery）。先前滾再回滾，先應用redo再應用undo。
  undo在ACID中保證了啥？
 主要保證事務的A的特性，同時C和I的特性也有關係。
 事務中的C特性怎麼保證？
 InnoDB crash recovery：資料庫意外當機時刻，通過redo前滾+undo回滾保證資料的最終一致。
 InnoDB doublewrite buffer： 預設儲存在ibdataN中。解決資料頁寫入不完整
 mysqld process crash in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery.
 DWB一共2M。分兩次，每次1M寫入

undo log 回滾日誌

ib_buffer_pool 預熱檔案

作用：

 緩衝和快取，用來做“熱”（經常查詢或修改）資料頁，減少物理IO。
 當關閉資料庫的時候，緩衝和快取會失效。
 5.7版本中，MySQL正常關閉時，會將記憶體的熱資料存放（流方式）至ib_buffer_pool。下次重啟直接讀取ib_buffer_pool載入到記憶體中。
 mysql> select @@innodb_buffer_pool_dump_at_shutdown;
 mysql> select @@innodb_buffer_pool_load_at_startup;

Double Write Buffer（DWB） 雙寫緩衝區 （8.0.19之前 預設在ibdataN中，8.0.20以後可以獨立了。）

作用： MySQL，最小IO單元page（16KB），OS中最小的IO單元是block（4KB） 為了防止出現以下問題：

 mysqld process exit in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery.
 #mysqld程式退出在頁面寫入中間過程中，InnoDB可以在崩潰恢復期間從雙寫緩衝區找到一個好的頁面副本。 

 

 

 

記憶體結構

InnoDB BUFFER POOL（IBP）(介紹：記憶體緩衝區池,最大\最重要)

作用：

 用來緩衝、快取，MySQL的資料頁和索引頁。MySQL中最大的、最重要的記憶體區域。

管理：

 查詢
 mysql> select @@innodb_buffer_pool_size;
 預設大小： 128M
 生產建議： 實體記憶體的：50-80%。
 OOM，全稱“Out Of Memory”，
 線上設定(256M)：
 mysql> select 256*1024*1024;
 +---------------+
 | 256*1024*1024 |
 +---------------+
 |     268435456 |
 +---------------+
 mysql> set global innodb_buffer_pool_size=268435456;
 重新登入mysql生效。
 永久設定：
 vim /etc/my.cnf 
 #新增引數
 innodb_buffer_pool_size=256M
 重啟生效

InnoDB LOG BUFFER (ILB)

作用： 用來緩衝 redo log日誌資訊。 管理 ：

 查詢： 
 mysql> select @@innodb_log_buffer_size;
 預設大小：16M
 生產建議：和innodb_log_file_size有關，1-N倍
 設定方式 ： 
 vim /etc/my.cnf 
 innodb_log_buffer_size=33554432
 重啟生效： 
 [root@db01 data]# /etc/init.d/mysqld restart  

InnoDB核心特性--事務支援

介紹

 事務：Transaction （交易）。 伴隨著交易類的業務出現的概念（工作模式）
 交易？ 
 物換物，等價交換。
 貨幣換物，等價交換。
 虛擬貨幣換物（虛擬物品），等價交換。
 現實生活中怎麼保證交易“和諧” ，法律、道德等規則約束。
 資料庫中為了保證線上交易的“和諧”，加入了“事務”工作機制。

事務ACID特性(面試題記憶)

 A: 原子性   (atomicity)
 一個事物是一個完整整體，不可再分。
 一個事務生命週期中的DML語句，要麼全成功要麼全失敗，不可以出現中間狀態。
 begin;
 DML1;
 DML2;
 DML3;
 commit;
 C：一致性   (consistency)
 事務發生前，中，後，資料都最終保持一致。
 只要提交成功的事務，資料保證最終一致。
 CR  + double write
 I：隔離性   (isolation)
 事務運算元據行的時候，不會受到其他時候的影響。
 D: 永續性   (durability)
 一但事務提交，保證永久生效,落盤。

事務的生命週期管理

標準(顯示)的事務控制語句

 # 開啟事務
 begin; 
 # 提交事務
 commit;
 # 回滾事務
 rollback;

注意：事務生命週期中，只能使用DML語句（select、update、delete、insert）

開始事務流程

 1、檢查autocommit是否為關閉狀態
 select @@autocommit;
 或者：
 show variables like 'autocommit';
 2、開啟事務,並結束事務

 begin
 delete from student where name='alexsb';
 update student set name='alexsb' where name='alex';
 rollback;
 begin
 delete from student where name='alexsb';
 update student set name='alexsb' where name='alex';
 commit;

事務的生命週期演示：

 mysql> use world
 mysql> begin;
 mysql> delete from city where id=1;
 mysql> update city set countrycode='CHN' where id=2;
 mysql> commit;
 mysql> begin;
 mysql> select * from city limit 10;
 mysql> update city set countrycode='AFG' where id=2;
 mysql> delete from city where id=3;
 mysql> rollback;
 MySQL的自動提交機制（autocommit）
 引數： 
 mysql> select @@autocommit;
 +--------------+
 | @@autocommit |
 +--------------+
 |            1 |
 +--------------+

作用：

 在沒有顯示的使用begin語句的時候，執行DML，會在DML前自動新增begin，並在DML執行後自動新增commit。

建議： 頻繁事務業務場景中，關閉autocommit,或者每次事務執行時都是顯示的begin和commit;

關閉autocommit方法

臨時

 mysql> set global autocommit=0;
 退出會話，重新連線配置生效。

永久

 [root@db01 ~]# vim /etc/my.cnf
 autocommit=0
 重啟生效。
 不進行begin操作,逐條提交。

隱式提交和回滾

 begin;
 DML1;
 DML2;
 DML3;
 commit;

隱式提交情況

 begin 
 a
 b
 SET AUTOCOMMIT = 1
 導致提交的非事務語句：
 DDL語句： （ALTER、CREATE 和 DROP）
 DCL語句： （GRANT、REVOKE 和 SET PASSWORD）
 鎖定語句：（LOCK TABLES 和 UNLOCK TABLES）
 導致隱式提交的語句示例：
 TRUNCATE TABLE
 LOAD DATA INFILE
 SELECT FOR UPDATE

隱式回滾

 會話視窗被關閉。
 資料庫關閉 。
 出現事務衝突（死鎖）。

事務的隔離級別 (面試必問專案)

作用

 實現事務工作期間的“讀”的隔離
 讀？ ----》 資料頁的讀
 級別型別 
 mysql> select @@transaction_isolation;

RU ： READ-UNCOMMITTED 讀未提交

 優點：可以讀取到事務未提交的資料。事務併發度最高。
 缺點：隔離性差，會出現髒讀（當前記憶體讀），不可重複讀，幻讀問題

RC ： READ-COMMITTED 讀已提交（可以用）

  優點:防止髒讀，防止不可重複讀
  缺點:不可重複讀，幻讀

RR ： REPEATABLE-READ 可重複讀（預設）

  優點:防止髒讀，防止不可重複讀
  缺點：事務併發度一般，幻讀問題

SE ： SERIALIZABLE 序列讀

 優點：隔離性最好
 缺點：事務沒有併發

預設級別是RR級別，網際網路業務的大部分場景RC級別。

 RC  可以減輕GAP+NextLock鎖的問題,一般在為了讀一致性會在正常select後新增for update語句.但是,請記住執行完一定要commit否則容易出現所等待比較嚴重.
 RC  可以減輕GAP+NextLock鎖的問題,一般在為了讀一致性會在正常select後新增for update語句.但是,請記住執行完一定要commit否則容易出現所等待比較嚴重.
 例如:
 [world]>select * from city where id=999 for update;
 [world]>commit;
 RR  利用的是undo的快照技術+GAP(間隙鎖)+NextLock(下鍵鎖)

隔離級別引數

 select @@transaction_isolation;
 set global transaction_isolation='READ-UNCOMMITTED';
 set global transaction_isolation='READ-COMMITTED';
 set global transaction_isolation='REPEATABLE-READ';
 set global transaction_isolation='SERIALIZABLE';

 vim /etc/my.cnf
 [mysqld]
 transaction_isolation='READ-COMMITTED';

問題現象演示

 -- 建立測試庫
 create database test;
 -- 建立測試表
 create table test.t1 (
 id int not null primary key auto_increment ,
 a  int not null ,
 b  varchar(20) not null, 
 c  varchar(20) not null 
 )charset=utf8mb4 engine=innodb;
 ​
 begin;
 insert into test.t1(a,b,c) 
 values
 (1,'a','aa'),
 (2,'c','ab'),
 (3,'d','ae'),
 (4,'e','ag'),
 (5,'f','at');
 commit;
 -- 關閉自動提交
 set global autocommit=0;
 -- 開啟兩個會話視窗：
 -- sessionA: 
 -- sessionB: 

InnoDB 事務的ACID如何保證

一些概念

 redo log ---> 重做日誌 ib_logfile0~1   50M   , 輪詢使用
 redo log buffer ---> redo記憶體區域
 ibd     ----> 儲存 資料行和索引 
 buffer pool --->緩衝區池,資料和索引的緩衝
 LSN : 日誌序列號 
 磁碟資料頁,redo檔案,buffer pool,redo buffer
 MySQL 每次資料庫啟動,都會比較磁碟資料頁和redolog的LSN,必須要求兩者LSN一致資料庫才能正常啟動
 WAL : write ahead log 日誌優先寫的方式實現持久化
 髒頁: 記憶體髒頁,記憶體中發生了修改,沒寫入到磁碟之前,我們把記憶體頁稱之為髒頁.
 CKPT:Checkpoint,檢查點,就是將髒頁刷寫到磁碟的動作
 TXID: 事務號,InnoDB會為每一個事務生成一個事務號,伴隨著整個事務.

髒讀

髒讀又稱無效資料的讀出，當前記憶體讀，可以讀取到別人未提交的資料。

例如：事務T1修改某一值，未提交，但是事務T2卻能讀取該值，此後T1因為某種原因撤銷對該值的修改，這就導致了T2所讀取到的資料是無效的。注意，髒讀一般是針對於update操作。

 -- RU級別下不可重讀現象演示：

 -- 第一步：設定隔離級別，重新連線資料庫
 mysql> set global transaction_isolation='READ-UNCOMMITTED';
 mysql> exit
 -- 第二步：檢查隔離級別
 -- sessionA: 
 mysql> select @@transaction_isolation;
 +-------------------------+
 | @@transaction_isolation |
 +-------------------------+
 | READ-UNCOMMITTED        |
 +-------------------------+
 -- sessionB: 
 mysql> select @@transaction_isolation;
 +-------------------------+
 | @@transaction_isolation |
 +-------------------------+
 | READ-UNCOMMITTED        |
 +-------------------------+
 -- 第三步：開啟事務
 -- sessionA: 
 mysql> begin;
 -- sessionB: 
 mysql> begin;
 -- 第四步：檢視當前表資料
 -- sessionA: 
 mysql> select * from test.t1 where id=2;
 +----+---+---+----+
 | id | a | b | c  |
 +----+---+---+----+
 |  2 | 2 | c | ab |
 +----+---+---+----+
 -- sessionB: 
 mysql> select * from test.t1 where id=2;
 +----+---+---+----+
 | id | a | b | c  |
 +----+---+---+----+
 |  2 | 2 | c | ab |
 +----+---+---+----+
 -- 第五步：
 -- sessionA: 執行DML語句
 mysql> update test.t1 set a=8 where id=2;
 -- 第六步：
 -- sessionB：檢視當前表資料發現資料變化，髒讀
 mysql> select * from test.t1 where id=2;
 +----+---+---+----+
 | id | a | b | c  |
 +----+---+---+----+
 |  2 | 8 | c | ab |
 +----+---+---+----+
 -- 第七步：
 -- sessionA: 回滾
 mysql> rollback;
 -- 第八步：
 -- sessionB：檢視當前表資料發現資料變化，不可重複讀
 mysql> select * from test.t1 where id=2;
 +----+---+---+----+
 | id | a | b | c  |
 +----+---+---+----+
 |  2 | 2 | c | ab |
 +----+---+---+----+

 

 

不可重複讀

不可重複讀，指一個事務範圍內兩個相同的查詢卻返回了不同資料。

這是由於查詢時系統中其他事務修改的提交而引起的。比如事務T1讀取某一資料，事務T2讀取並修改了該資料，T1為了對讀取值進行檢驗而再次讀取該資料，便得到了不同的結果。

 -- RC級別下不可重讀現象演示：
 

-- 第一步：設定隔離級別，重新連線資料庫
 mysql> set global transaction_isolation='READ-COMMITTED';
 mysql> exit
 -- 第二步：檢查隔離級別
 -- sessionA: 
 mysql> select @@transaction_isolation;
 +-------------------------+
 | @@transaction_isolation |
 +-------------------------+
 | READ-COMMITTED          |
 +-------------------------+
 -- sessionB: 
 mysql> select @@transaction_isolation;
 +-------------------------+
 | @@transaction_isolation |
 +-------------------------+
 | READ-COMMITTED          |
 +-------------------------+
 -- 第三步：開啟事務
 -- sessionA: 
 mysql> begin;
 -- sessionB: 
 mysql> begin;
 -- 第四步：檢視當前表資料
 -- sessionA: 
 mysql> select * from test.t1 where id=1;
 +----+---+---+----+
 | id | a | b | c  |
 +----+---+---+----+
 |  1 | 1 | a | aa |
 +----+---+---+----+
 -- sessionB: 
 mysql> select * from test.t1 where id=1;
 +----+---+---+----+
 | id | a | b | c  |
 +----+---+---+----+
 |  1 | 1 | a | aa |
 +----+---+---+----+
 -- 第五步：
 -- sessionA: 執行DML語句並提交事務
 mysql> update test.t1 set a=6 where id=1;
 mysql> commit;
 -- 第六步：
 -- sessionB：檢視當前表資料發現資料變化
 mysql> select * from test.t1 where id=1;
 +----+---+---+----+
 | id | a | b | c  |
 +----+---+---+----+
 |  1 | 6 | a | aa |
 +----+---+---+----+

幻讀

幻讀 ，指同一查詢在不同時間產生不同的行集。

例如：第一個事務對一個表中的資料進行了修改，比如這種修改涉及到表中的“全部資料行”。同時，第二個事務也修改這個表中的資料，這種修改是向表中插入“一行新資料”。那麼，就會發生操作第一個事務的使用者發現表中還存在沒有修改的資料行，就好象發生了幻覺一樣。

一般解決幻讀的方法是增加範圍鎖RangeS，鎖定檢索範圍為只讀，這樣就避免了幻讀。

-- RC級別下幻讀現象演示：
 -- 第一步：設定隔離級別，重新連線資料庫
 mysql> set global transaction_isolation='READ-COMMITTED';
 mysql> exit
 -- 第二步：檢查隔離級別
 -- sessionA: 
 mysql> select @@transaction_isolation;
 +-------------------------+
 | @@transaction_isolation |
 +-------------------------+
 | READ-COMMITTED          |
 +-------------------------+
 -- sessionB: 
 mysql> select @@transaction_isolation;
 +-------------------------+
 | @@transaction_isolation |
 +-------------------------+
 | READ-COMMITTED          |
 +-------------------------+
 -- 第三步：開啟事務
 -- sessionA: 
 mysql> begin;
 -- sessionB: 
 mysql> begin;
 -- 第四步：檢視當前表資料
 -- sessionA: 
 mysql> select * from test.t1;
 +----+---+---+----+
 | id | a | b | c  |
 +----+---+---+----+
 |  1 | 6 | a | aa |
 |  2 | 2 | c | ab |
 |  3 | 3 | d | ae |
 |  4 | 4 | e | ag |
 |  5 | 5 | f | at |
 +----+---+---+----+
 -- sessionB: 
 mysql> select * from test.t1;
 +----+---+---+----+
 | id | a | b | c  |
 +----+---+---+----+
 |  1 | 6 | a | aa |
 |  2 | 2 | c | ab |
 |  3 | 3 | d | ae |
 |  4 | 4 | e | ag |
 |  5 | 5 | f | at |
 +----+---+---+----+
 -- 第五步：
 -- sessionA：執行DML語句，全部資料行修改
 mysql> update test.t1 set a=10 where a<10;
 -- 第六步：
 -- sessionB：執行DML語句，插入一行資料，提交事務
 mysql> insert into test.t1(a,b,c) values (1,'z','az');
 mysql> commit;
 -- 第七步： 
 -- sessionA：提交事務
 mysql> commit;
 -- 第八步：
 -- sessionA：檢視當前表資料，好像發生了幻覺
 mysql> select * from test.t1;
 +----+----+---+----+
 | id | a  | b | c  |
 +----+----+---+----+
 |  1 | 10 | a | aa |
 |  2 | 10 | c | ab |
 |  3 | 10 | d | ae |
 |  4 | 10 | e | ag |
 |  5 | 10 | f | at |
 |  6 |  1 | z | az |
 +----+----+---+----+

儲存引擎進階

名詞介紹

重做日誌（redo log）

 磁碟  ib_logfile0~N   innodb_log_file_size
                      innodb_log_files_in_group
 記憶體  innodb_log_buffer   innodb_log_buffer_size

 表資料     

 磁碟  獨立表空間    
 xxxibd   
 segment  
 extents  
 pages
 記憶體   innodb_buffer_pool     innodb_buffer_pool_size
 日誌序列號LSN   
 一個順序遞增的數字
 記錄資料頁變化的版本
 redo日誌的變化量(位元組) 
 哪些物件有LSN
 redo buffer  Log  sequence number 180973874
 redo log     Log  flushed  up to  180973874
 資料頁        Last  checkpoint at 180973874
 查詢方式
 show  engine innodb  status \G
 write ahead log(WAL)
 日誌先行
 commit提交事務時，保證日誌先寫磁碟，資料後寫
 髒頁：Dirty Page       在記憶體中修改的資料頁，沒寫到磁碟的叫做髒頁
 檢查點：CheckPoint     將髒頁重新整理到磁碟的動作
 DB_ROLL_TR      回滾指標   儲存在資料頁頭部   

重做日誌 （redo log）

 ib_logfile0~N   48M   , 輪詢使用

日誌緩衝區

 redo log buffer ： redo記憶體區域

表空間資料檔案

 ibd: 儲存資料行和索引 

資料緩衝區

 InnoDB buffer pool ： 緩衝區池,資料和索引的緩衝

日誌序列號

LSN

 磁碟資料頁(ibd檔案的page),redo log檔案（ib_logfile）,Innodb_buffer_pool中的資料頁,redo buffer
 MySQL 每次資料庫啟動,都會比較磁碟資料頁和redolog的LSN,必須要求兩者LSN一致資料庫才能正常啟動
 #WAL : Write Ahead Log
 Redo日誌優先於資料頁寫到磁碟。

髒頁: Dirty Page

記憶體髒頁,記憶體中發生了修改,沒寫入到磁碟之前,我們把記憶體頁稱之為髒頁.

CheckPoint

 CKPT:檢查點,就是將髒頁重新整理到磁碟的動作
 #DB_TRX_ID（6位元組）  事務ID號
 InnoDB會為每一個事務生成一個事務號,伴隨著整個事務生命週期.
 #DB_ROLL_PTR（7位元組） 回滾指標
 rollback 時，會使用 undo 日誌回滾已修改的資料。DB_ROLL_PTR指向了此次事務的回滾位置點，用來找到undo日誌資訊。
  事務工作流程原理
 事務舉例： 
 begin; 
 update t1 set A=2 where A=1;
 commit;
 #redo log 重做日誌如何應用
 使用者發起update事務語句，將磁碟資料頁（page100,A=1,LSN=1000）載入到記憶體(buffer_pool)緩衝區。
 在記憶體中發生資料頁修改（A=1改成A=2），形成髒頁，更改中資料頁的變化，記錄到redo buffer中，加入1000個位元組日誌。LSN=1000+1000=2000。
 當commit語句執行時，基於WAL機制，等到redo buffer中的日誌完全落盤到ib_logfileN中，commit正式完成。
 ib_logfileN中記錄了一條日誌。內容：page100資料頁變化+LSN=2000。
 ##情景： 當此時，redo落地了，資料頁沒有落地，當機了。
 MySQL CR（自動故障恢復）工作模式，啟動資料庫時，自動檢查redo的LSN和資料頁LSN。
 如果發現redoLSN>資料頁的LSN ，載入原始資料頁+變化redo指定記憶體。使用redo重構髒頁（前滾）。
 如果確認此次事務已經提交（commit標籤），立即觸發CKPT動作，將髒頁刷寫到磁碟上。

補充：

 MySQL有一種機制，批量刷寫redo的機制。會在A事務commit時，順便將redo buffer中的未提交的redo日誌也一併刷到磁碟。
 為了區分不同狀態的redo，日誌記錄時，會標記是否COMMIT。

redo保證了ACID哪些特性

主要是D的特性，另外A、C也有間接關聯。

undo log 回滾日誌應用

 1.事務發生資料頁修改之前，會申請一個undo事務操作，儲存事務回滾日誌（逆向操作的邏輯日誌）。
 2.undo寫完之後，事務修改資料頁頭部（會記錄DB_TRX_ID+DB_ROLL_PTR），這個資訊也會被記錄的redo。
 情景1： 
 當執行rollback命令時。根據資料頁的DB_TRX_ID+DB_ROLL_PTR資訊，找到undo日誌，進行回滾。
 情景2：
 begin; 
 update t1 set A=2 where A=1;
 當機。
 假設： undo 有  ， redo沒有
 啟動資料庫時，檢查redo和資料頁的LSN號碼。發現是一致的。
 所以不需要進行redo的前滾，此時也不需要回滾。undo資訊直接被標記為可覆蓋狀態。
 假設：undo 有，redo也有（沒有commit標籤。）
 3.MySQL CR（自動故障恢復）工作模式，啟動資料庫時，自動檢查redo的LSN和資料頁LSN。
 4.如果發現redoLSN>資料頁的LSN ，載入原始資料頁+變化redo指定記憶體。使用redo重構髒頁（前滾）。
 5.如果確認此次事務沒有commit標記，立即觸發回滾操作，根據DB_TRX_ID+DB_ROLL_PTR資訊，找到und回滾日誌，實現回滾。
 以上流程被稱之為InnoDB的核心特性：自動故障恢復（Crash Recovery）。先前滾再回滾，先應用redo再應用undo。

undo在ACID中的保證

主要保證事務的A的特性，同時C和I的特性也有關係。 事務中的C特性怎麼保證？

 InnoDB crash recovery：資料庫意外當機時刻，通過redo前滾+undo回滾保證資料的最終一致。
 InnoDB doublewrite buffer： 預設儲存在ibdataN中。解決資料頁寫入不完整
 mysqld process crash in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery.
 DWB一共2M。分兩次，每次1M寫入

事務中的I的特性怎麼保證？

隔離級別：讀隔離性

 RU: 髒讀 、 不可重複讀 、幻讀
 RC: 不可重複讀、幻讀
 RR:有可能會出現幻讀。
 SR（SE）:事務序列工作。

鎖機制：寫的隔離

作用：保護併發訪問資源。

保護的資源分類：

 latch（閂鎖）：rwlock、mutex，主要保護記憶體資源
 MDL： Metadata_lock,後設資料(DDL操作)
 table_lock: 表級別 
 lock table t1 read ;
 mysqldump、XBK（PBK）：備份非InnoDB資料時，觸發FTWRL全域性鎖表（Global）。

行鎖升級為表鎖。

 row lock：InnoDB 預設鎖粒度，加鎖方式都是在索引加鎖的。
 record lock:記錄鎖，在聚簇索引鎖定。RC級別只有record lock。
 gap lock:間隙鎖，在輔助索引間隙加鎖。RR級別存在。防止幻讀。
 next lock:下一鍵鎖,GAP+Record。   RR級別存在。防止幻讀。 (,]

如何監控行鎖問題?

 mysql> select * from sys.innodb_lock_waits\G

功能性上：

 IS: select * from t1 lock in shared mode;
 S : 讀鎖。             
 IX: 意向排他鎖。表上新增的。 select * from t1 for update;
 X : 排他鎖，寫鎖。

MVCC ： 多版本併發控制

 樂觀鎖： 樂觀。
 悲觀鎖： 悲觀。
 每個事務操作都要經歷兩個階段： 
 讀:  樂觀鎖。
 MVCC利用樂觀鎖機制，實現非鎖定讀取。
 read view：RV,版本號集合。
 trx1 : 
 begin; 
 dml1  ---> 在做第一個查詢的時候，當前事務，獲取系統最新的：RV1 版本快照。
 dml2  ---> 生成 RV2 版本快照。
 select  查詢  RV2 快照資料
 commit; ---->  RV2 快照資料  ----》系統最新快照。

RC

 trx1: Rv1  Rv2  commit;
 trx2 RVV1 RVV1 RV2

RR

 trx1 : 第一個查詢時， 生成global consitence snapshot  RV-CS1(10:00) ,一直伴隨著事務生命週期結束。
 trx2 :  第一個查詢時，生成global consitence snapshot  RV-CS2（10:01) ,一直伴隨著事務生命週期結束。
 快照技術由undo log來提供。
 寫: 悲觀鎖 X 

總結：

 1.MVCC採用樂觀鎖機制，實現非鎖定讀取。
 2.在RC級別下，事務中可以立即讀取到其他事務commit過的readview
 3.在RR級別下，事務中從第一次查詢開始，生成一個一致性readview，直到事務結束。