MySQL的存儲引擎與日誌說明_ZenDei技術網路在線

1.1 存儲引擎的介紹 1.1.1 文件系統存儲文件系統：操作系統組織和存取數據的一種機制。文件系統是一種軟體。類型：ext2 3 4 ，xfs 數據。不管使用什麼文件系統，數據內容不會變化，不同的是，存儲空間、大小、速度。 1.1.2 mysql資料庫存儲 MySQL引擎：可以理解為，My ...

1.1 存儲引擎的介紹

1.1.1 文件系統存儲

　　文件系統：操作系統組織和存取數據的一種機制。文件系統是一種軟體。

　　類型：ext2 3 4 ，xfs 數據。不管使用什麼文件系統，數據內容不會變化，不同的是，存儲空間、大小、速度。

1.1.2 mysql資料庫存儲

　　MySQL引擎：可以理解為，MySQL的“文件系統”，只不過功能更加強大。

　　MySQL引擎功能：除了可以提供基本的存取功能，還有更多功能事務功能、鎖定、備份和恢復、優化以及特殊功能。

1.1.3 MySQL存儲引擎種類

MySQL 提供以下存儲引擎：

InnoDB、MyISAM (最常用的兩種)
MEMORY、ARCHIVE、FEDERATED、EXAMPLE
BLACKHOLE、MERGE、NDBCLUSTER、CSV

　　除此之外還可以使用第三方存儲引擎。

1.1.4 innodb與myisam對比

InnoDb引擎

支持ACID的事務，支持事務的四種隔離級別；
支持行級鎖及外鍵約束：因此可以支持寫併發；
不存儲總行數；
一個InnoDb引擎存儲在一個文件空間（共用表空間，表大小不受操作系統控制，一個表可能分佈在多個文件里），也有可能為多個（設置為獨立表空，表大小受操作系統文件大小限制，一般為2G），受操作系統文件大小的限制；
主鍵索引採用聚集索引（索引的數據域存儲數據文件本身），輔索引的數據域存儲主鍵的值；因此從輔索引查找數據，需要先通過輔索引找到主鍵值，再訪問輔索引；最好使用自增主鍵，防止插入數據時，為維持B+樹結構，文件的大調整。

Innodb的主索引結構如下：

MyISAM引擎

不支持事務，但是每次查詢都是原子的；
支持表級鎖，即每次操作是對整個表加鎖；
存儲表的總行數；
一個MYISAM表有三個文件：索引文件、表結構文件、數據文件；
採用菲聚集索引，索引文件的數據域存儲指向數據文件的指針。輔索引與主索引基本一致，但是輔索引不用保證唯一性。

MYISAM的主索引結構如下：

兩種索引數據查找過程如下：

1.2 innodb存儲引擎

　　在MySQL5.5版本之後，預設的存儲引擎，提供高可靠性和高性能。

1.2.1 Innodb引擎的優點

a)    事務安全（遵從ACID）
b)    MVCC（Multi-Versioning Concurrency Control，多版本併發控制）
c)    InnoDB行級鎖
d)    支持外鍵引用完整性約束
e)    出現故障後快速自動恢復（crash safe recovery）
f)    用於在記憶體中緩存數據和索引的緩衝區池（buffer pool（data buffer page  log buffer page） 、undo buffer page）
g)    大型數據捲上的最大性能
h)    將對錶的查詢與不同存儲引擎混合
i)    Oracle樣式一致非鎖定讀取(共用鎖)
j)    表數據進行整理來優化基於主鍵的查詢（聚集索引）

1.2.2 Innodb功能總覽

功能	支持	功能	支持
存儲限制	64 TB	索引高速緩存	是
MVCC	是	數據高速緩存	是
B 樹索引	是	自適應散列索引	是
群集索引	是	複製	是
壓縮數據	是	更新數據字典	是
加密數據[b]	是	地理空間數據類型	是
查詢高速緩存	是	地理空間索引	否
事務	是	全文搜索索引	是
鎖定粒度	行	群集資料庫	否
外鍵	是	備份和恢復	是
文件格式管理	是	快速索引創建	是
多個緩衝區池	是	PERFORMANCE_SCHEMA	是
更改緩衝	是	自動故障恢復	是

1.2.3 查詢存儲引擎的方法

1、使用 SELECT 確認會話存儲引擎：

SELECT @@default_storage_engine;
或
show variables like '%engine%';

2、使用 SHOW 確認每個表的存儲引擎：

SHOW CREATE TABLE City\G
SHOW TABLE STATUS LIKE 'CountryLanguage'\G

3、使用 INFORMATION_SCHEMA 確認每個表的存儲引擎：

SELECT TABLE_NAME, ENGINE FROM 
INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'City'
AND TABLE_SCHEMA = 'world_innodb'\G

4、從5.1版本，遷移到5.5版本以上版本

　　假如5.1版本資料庫所有生產表都是myisam的。

　　使用mysqldump備份後，一點要替換備份的文件中的engine(引擎)欄位，從myisam替換為innodb（可以使用sed命令），否則遷移無任何意義。

　　資料庫升級時，要註意其他配套設施的相容性，註意代碼能否相容新特性。

1.2.4 設置存儲引擎

1、在啟動配置文件中設置伺服器存儲引擎：

[mysqld]
default-storage-engine=<Storage Engine>

2、使用 SET 命令為當前客戶機會話設置：

SET @@storage_engine=<Storage Engine>;

3、在 CREATE TABLE 語句指定：

CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

1.3 InnoDB存儲引擎的存儲結構

1.3.1 InnoDB 系統表空間特性

預設情況下，InnoDB 元數據、撤消日誌和緩衝區存儲在系統“表空間”中。
這是單個邏輯存儲區域，可以包含一個或多個文件。
每個文件可以是常規文件或原始分區。
最後的文件可以自動擴展。

1.3.2 表空間的定義

　　表空間：MySQL資料庫存儲的方式

　　　　表空間中包含數據文件

　　MySQl表空間和數據文件是1:1的關係

　　　　共用表空間除外，是可以1:N關係

1.3.3 表空間類型

　　1、共用表空間：ibdata1~ibdataN,一般是2-3個

　　2、獨立表空間：存放在指定庫目錄下，例如data/world/目錄下的city.ibd

　　　　表空間位置（datadir）：

　　　　data/目錄下

1.3.4 系統表空間的存儲內容

共用表空間（物理存儲結構）

　　 ibdata1~N 通常被叫做系統表空間，是數據初始化生成的

　　系統元數據，基表數據，除了表內容數據之外的數據。

　　tmp 表空間（一般很少關註）

　　undo日誌：數據--回滾數據（回滾日誌使用）

　　 redo日誌：ib_logfile0~N 存放系統的innodb表的一些重做日誌。

　　說明：undo日誌預設實在ibdata中的，在5.6以後是可以單獨定義的。

　　 tmp 表空間在5.7版本之後被移出了ibdata1，變為ibtmp1

　　在5.5版本之前，所有的應用數據也都預設存放到了ibdata中。

獨立表空間(一個存儲引擎的功能)

　　在5.6之後，預設的情況下會單表單獨存儲到獨立表空間文件

　　　除了系統表空間之外，InnoDB 還在資料庫目錄中創建另外的表空間，用於每個 InnoDB 表的 .ibd 文件。

　　 InnoDB 創建的每個新表在資料庫目錄中設置一個 .ibd 文件來搭配表的.frm 文件。

　　　可以使用 innodb_file_per_table 選項控制此設置，更改該設置僅會更改已創建的新表的預設值。。

1.3.5 設置共用表空間

查看當前的共用表空間設置

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

　設置共用表空間：

　　一般是在初始搭建環境的時候就配置號，預設值一般為1G；且最後一個為自動擴展。

[root@db02 world]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:100M:autoextend

重啟服務查看當前的共用表空間設置

mysql> show variables like 'innodb_data_file_path';
+-----------------------+-------------------------------------+
| Variable_name         | Value                               |
+-----------------------+-------------------------------------+
| innodb_data_file_path | ibdata1:76M;ibdata2:100M:autoextend |
+-----------------------+-------------------------------------+
1 row in set (0.00 sec)

1.3.6 設置獨立表空間

　　　獨立表空間在5.6版本是預設開啟的。

　　　獨立表空間註意事項：不開起獨立表空間，共用表空間會占用很大

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

在參數文件/etc/my.cnf 可以控制獨立表空間

關閉獨立表空間（0是關閉，1是開啟）

[root@db02 clsn]# vim /etc/my.cnf
[mysqld]
innodb_file_per_table=0

查看獨立表空間配置

mysql> show variables like '%per_table%' ;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

小結:

innodb_file_per_table=0    關閉獨立表空間
innodb_file_per_table=1    開啟獨立表空間，單表單存儲

1.4 MySQL中的事務

　　一組數據操作執行步驟，這些步驟被視為一個工作單元

　　　　用於對多個語句進行分組，可以在多個客戶機併發訪問同一個表中的數據時使用。

　　所有步驟都成功或都失敗

　　　　如果所有步驟正常，則執行，如果步驟出現錯誤或不完整，則取消。

簡單來說事務就是：保證工作單元中的語句同時成功或同時失敗。

事務處理流程示意圖

1.4.1 事務是什麼

　　與其給事務定義，不如說一說事務的特性。眾所周知，事務需要滿足ACID四個特性。

A(atomicity) 原子性。

　　　一個事務的執行被視為一個不可分割的最小單元。事務裡面的操作，要麼全部成功執行，要麼全部失敗回滾，不可以只執行其中的一部分。

所有語句作為一個單元全部成功執行或全部取消。
updata t1 set money=10000-17 where  id=wxid1
updata t1 set money=10000+17  where  id=wxid2

C(consistency) 一致性。

　　一個事務的執行不應該破壞資料庫的完整性約束。如果上述例子中第2個操作執行後系統崩潰，保證A和B的金錢總計是不會變的。

如果資料庫在事務開始時處於一致狀態，則在執行該事務期間將保留一致狀態。
    updata t1 set money=10000-17 where  id=wxid1
    updata t1 set money=10000+17  where  id=wxid2
    在以上操作過程中，去查自己賬戶還是10000

I(isolation) 隔離性。

　　通常來說，事務之間的行為不應該互相影響。然而實際情況中，事務相互影響的程度受到隔離級別的影響。文章後面會詳述。

　　事務之間不相互影響。在做操作的時候，其他人對這兩個賬戶做任何操作，在不同的隔離條件下，可能一致性保證又不一樣

隔離級別

    隔離級別會影響到一致性。
    read-uncommit  X
    read-commit   可能會用的一種級別
    repeatable-read   預設的級別，和oracle一樣的
    SERIALIZABLE       嚴格的預設，一般不會用

　　此規則除了受隔離級別控制，還受鎖控制，可以聯想一下NFS的實現

D(durability) 持久性。

　　事務提交之後，需要將提交的事務持久化到磁碟。即使系統崩潰，提交的數據也不應該丟失。

　保證數據落地，才算事務真正安全

1.4.2 事務的控制語句

常用的事務控制語句：

    START TRANSACTION（或 BEGIN）：顯式開始一個新事務
    COMMIT：永久記錄當前事務所做的更改(事務成功結束)
    ROLLBACK：取消當前事務所做的更改(事務失敗結束)

需要知道的事務控制語句：

    SAVEPOINT：分配事務過程中的一個位置，以供將來引用
    ROLLBACK TO SAVEPOINT：取消在 savepoint 之後執行的更改
    RELEASE SAVEPOINT：刪除 savepoint 標識符
    SET AUTOCOMMIT：為當前連接禁用或啟用預設 autocommit模式

1.4.3 autocommit參數

　　在MySQL5.5開始，開啟事務時不再需要begin或者start transaction語句。並且，預設是開啟了Autocommit模式，作為一個事務隱式提交每個語句。

　　在有些業務繁忙企業場景下，這種配置可能會對性能產生很大影響，但對於安全性上有很大提高。將來，我們需要去權衡我們的業務需求去調整是否自動提交。

　　註意：在生產中，根據實際需求選擇是否可開啟，一般銀行類業務會選擇關閉。

查看當前autocommit狀態：

mysql> show variables like '%autoc%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

修改配置文件，並重啟

[root@db02 world]# vim /etc/my.cnf
[mysqld]
autocommit=0

再次查看autocommit狀態

mysql> show variables like '%autoc%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

　　說明： autocommit設置為開啟的對比

　　優點：數據安全性好，每次修改都會落地

　　缺點：不能進行銀行類的交易事務、產生大量小的IO

1.4.4 導致提交的非事務語句：

DDL語句： （ALTER、CREATE 和 DROP）
DCL語句： （GRANT、REVOKE 和 SET PASSWORD）
鎖定語句：（LOCK TABLES 和 UNLOCK TABLES）

導致隱式提交的語句示例：

TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

用於隱式提交的 SQL 語句：

START TRANSACTION
SET AUTOCOMMIT = 1

1.5 redo與undo

1.5.1 事務日誌undo

undo原理：

　　Undo Log的原理很簡單，為了滿足事務的原子性，在操作任何數據之前，首先將數據備份到一個地方（這個存儲數據備份的地方稱為Undo Log）。然後進行數據的修改。

　　如果出現了錯誤或者用戶執行了ROLLBACK語句，系統可以利用Undo Log中的備份將數據恢復到事務開始之前的狀態。

除了可以保證事務的原子性，Undo Log也可以用來輔助完成事務的持久化。

undo是什麼？

　　undo,顧名思義“回滾日誌”，是事務日誌的一種。

作用是什麼？

　　在事務ACID過程中，實現的是“A“原子性的作用。

用Undo Log實現原子性和持久化的事務的簡化過程

  假設有A、B兩個數據，值分別為1,2。
  A.事務開始.
  B.記錄A=1到undo log.
  C.修改A=3.
  D.記錄B=2到undo log.
  E.修改B=4.
  F.將undo log寫到磁碟。
  G.將數據寫到磁碟。
  H.事務提交

這裡有一個隱含的前提條件：‘數據都是先讀到記憶體中，然後修改記憶體中的數據，最後將數據寫回磁碟之所以能同時保證原子性和持久化，是因為以下特點：

  A. 更新數據前記錄Undo log。
  B. 為了保證持久性，必須將數據在事務提交前寫到磁碟。只要事務成功提交，數據必然已經持久化。
  C. Undo log必須先於數據持久化到磁碟。如果在G,H之間系統崩潰，undo log是完整的，可以用來回滾事務。
  D. 如果在A-F之間系統崩潰,因為數據沒有持久化到磁碟。所以磁碟上的數據還是保持在事務開始前的狀態。

缺陷：

　　每個事務提交前將數據和Undo Log寫入磁碟，這樣會導致大量的磁碟IO，因此性能很低。如果能夠將數據緩存一段時間，就能減少IO提高性能。但是這樣就會喪失事務的持久性。

　　因此引入了另外一種機制來實現持久化，即Redo Log.

1.5.2 事務日誌redo

redo原理：

　　和Undo Log相反，Redo Log記錄的是新數據的備份。在事務提交前，只要將Redo Log持久化即可，不需要將數據持久化。當系統崩潰時，雖然數據沒有持久化，但是Redo Log已經持久化。

　　系統可以根據Redo Log的內容，將所有數據恢復到最新的狀態。

Redo是什麼？

　　redo,顧名思義“重做日誌”，是事務日誌的一種。

作用是什麼？

　　在事務ACID過程中，實現的是“D”持久化的作用。

Undo + Redo事務的簡化過程

 假設有A、B兩個數據，值分別為1,2.
  A.事務開始.
  B.記錄A=1到undo log.
  C.修改A=3.
  D.記錄A=3到redo log.
  E.記錄B=2到undo log.
  F.修改B=4.
  G.記錄B=4到redo log.
  H.將redo log寫入磁碟。
  I.事務提交

Undo + Redo