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

參看：//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
 參數調整....
 優化結果:
 監控狀態良好

參考： //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表支援不停機插入、刪除和查詢

  • 在線備份

參考內容： //www.jianshu.com/p/898d2e4bd3a7 //mariadb.com/kb/en/installing-tokudb/ //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';

  1. 開啟導出文件功能

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

  1. 構建批量修改語句：

 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';

  1. 執行批量修改語句：

 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版本:

//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文件，該文件成為共享表空間）：
 參考：//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, 一個用來控制二進位日誌的
 二進位日誌相關參考：//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機制。 參考：//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 //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文件格式正確的部分，手動修復不正確的格式，保存。

  1. 使用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的區別

參考

//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，直到事務結束。







VirMach 便宜 VPS

QNews

QNews