MySQL 儲存引擎

1.          MySQL 儲存引擎

1.1.         儲存引擎和 MySQL

MySQL 提供並維護多個儲存引擎，每個引擎具有不同的特徵和含義。建立表時可以選擇特定儲存引擎， MySQL 使用 InnoDB 儲存引擎在硬碟上為該表建立儲存。可以選擇替代儲存引擎來用於每個表。通常，根據哪些儲存引擎可以提供最適合您的應用程式需求的功能來做出此選擇。每個儲存引擎具有一組特定執行特徵。這些特徵包括用於管理查詢爭用的鎖的型別以及該儲存引擎是否支援事務。這些引擎屬性對查詢處理效能、併發性以及死鎖預防具有一定影響。

雖然可以使用許多其他儲存引擎，但對於大多數用例， InnoDB 都是最適合的。

MySQL 提供以下儲存引擎：

²   InnoDB

²   MyISAM

²   MEMORY

²   ARCHIVE

²   FEDERATED

²   EXAMPLE

²   BLACKHOLE

²   MERGE

²   NDBCLUSTER

²   CSV

還可以使用第三方儲存引擎。

MySQL 提供並維護多個儲存引擎。 MySQL 伺服器還可以與許多第三方儲存引擎相容。 MySQL 儲存引擎是資料庫伺服器內的低階別引擎，它負責儲存和檢索資料，並且可以通過內部 MySQL API 進行訪問，在某些情況下，可以由應用程式直接訪問。請注意，一個應用程式可以在任何給定時間使用多個儲存引擎。幻燈片中列出了當前支援的儲存引擎。

請注意， InnoDB 和 NDBCLUSTER 是具有事務性的唯一兩個 MySQL 儲存引擎。

第三方引擎具有不同的源和功能， MySQL 不技術支援這些引擎。有關這些引擎的進一步資訊、文件、安裝指南、錯誤報告或任何幫助或協助，請直接與該引擎的開發人員聯絡。有關第三方儲存引擎的簡要論述，請參見以下連結中的“ Other Storage Engines （其他儲存引擎）”文件：

http://dev.mysql.com/doc/refman/5.6/en/storage-engines-other.html 。

有關 MySQL 儲存引擎的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/storage-engines.html 。

1.2.         顯示儲存引擎設定

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

SELECT @@default_storage_engine;

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

SHOW CREATE TABLE City\G

SHOW TABLE STATUS LIKE 'CountryLanguage'\G

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

SELECT TABLE_NAME, ENGINE FROM

INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'City'

AND TABLE_SCHEMA = 'world_innodb'\G

 

1.3.         設定儲存引擎

在啟動配置檔案中設定伺服器儲存引擎：

[mysqld]

default-storage-engine=<Storage Engine>

使用 SET 命令為當前客戶機會話設定：

SET @@storage_engine=<Storage Engine>;

使用 CREATE TABLE 語句指定：

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

如果建立表時沒有使用 ENGINE 選項顯式指定儲存引擎， MySQL 伺服器使用預設引擎建立該表，該引擎由 storage_engine 系統變數的值給定。可以通過使用 SET 命令在會話級別覆蓋伺服器預設值。

 

1.4.         InnoDB 儲存引擎

InnoDB 是 MySQL 的預設儲存引擎，提供高可靠性和高效能以及下面的主要優點：

²   事務安全（遵從 ACID ）

²   MVCC （ Multi-Versioning Concurrency Control ，多版本併發控制）

²   表資料進行整理來優化基於主鍵的查詢

²   支援外來鍵引用完整性約束

²   大型資料捲上的最大效能

²   將對錶的查詢與不同儲存引擎混合

²   出現故障後快速自動恢復

²   用於在記憶體中快取資料和索引的緩衝區池

任何其他磁碟相關的關聯式資料庫引擎很難比得上 InnoDB 的效率。下面介紹使用 InnoDB 的一些其他優點：

事務安全 ：通過事務提交、回滾以及用於保護使用者資料的故障恢復功能，可以實現 ACID 遵從性。

外來鍵支援 ：包括級聯刪除和更新。

恢復和備份 ：支援一致而聯機的邏輯備份。

混合查詢 ：在同一語句內，可以將 InnoDB 表與來自其他 MySQL 儲存引擎的表混合。例如，可以使用 join 運算在單個查詢中合併來自 InnoDB 和 MEMORY 表的資料。

全文索引 ：可以在文字列中有效地搜尋單詞或短語。

有關 InnoDB 儲存引擎功能的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html 。

 

InnoDB 作為預設儲存引擎內建到 MySQL 伺服器中，新表的預設儲存引擎是 InnoDB 。使用 ENGINE=<Storage Engine> 等子句建立或更改表時選擇替代儲存引擎。

1.4.1          InnoDB 功能

功能	支援	功能	支援
儲存限制	64 TB	索引快取記憶體	是
MVCC	是	資料快取記憶體	是
B 樹索引	是	自適應雜湊索引	是
群集索引	是	複製 [ c ]	是
壓縮資料	是 [ a ]	更新資料字典	是
加密資料 [ b ]	是	地理空間資料型別	是
查詢快取記憶體	是 [ c ]	地理空間索引	否
事務	是	全文搜尋索引	是
Lock 粒度	行	群集資料庫	否
外來鍵	是	備份和恢復 [ c ]	是
檔案格式管理	是	快速索引建立	是
多個緩衝區池	是	PERFORMANCE_SCHEMA	是
更改緩衝	是	自動故障恢復	是

注意：

[a] 需要 InnoDB Barracuda 檔案格式。

[b] 在伺服器中（通過加密功能）而不是在儲存引擎中實現。

[c] 在伺服器中而不是在儲存產品中實現。使用 MEB 進行備份時例外，其不是在伺服器級別進行。

有關這些功能的更多資訊，請參見 MySQL 術語詞彙表，網址為：

http://dev.mysql.com/doc/refman/5.6/en/glossary.html 。

有關 InnoDB 功能的完整資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html 。

 

1.4.2          將現有錶轉換為 InnoDB

使用 ALTER TABLE 更改引擎是一項成本很高的操作，因為它在內部將所有資料從一個引擎複製到另一個引擎。建議不要將 mysql 資料庫（例如 user 或 host ）中的 MySQL 系統錶轉換為 InnoDB ，系統表使用 MyISAM 引擎，此操作不受支援。轉換方法如下：

方法一：使用 ALTER TABLE 更改儲存引擎

ALTER TABLE t ENGINE = InnoDB;

方法二：從其他儲存引擎克隆表，例如使用相同定義建立空 InnoDB 表，建立適當的索引，然後插入行

INSERT INTO <innodb_table> SELECT * FROM<other_table>;

建立作為使用其他儲存引擎的表的克隆的 InnoDB 表時，還可以在插入資料後建立索引。要更好地控制插入過程，您可以小塊形式插入大型表：

INSERT INTO newtable SELECT * FROM oldtable

WHERE yourkey > something AND yourkey <= somethingelse;

注：插入了所有記錄後，可以重新命名錶。

在轉換大型表的過程中，增加 InnoDB 緩衝區池的大小來減少磁碟 I/O 。還可以增加 InnoDB 日誌檔案的大小。第一次啟動 MySQL 伺服器並且啟用 InnoDB 時，最好從命令提示符執行 MySQL 伺服器 mysqld ，而不是從 mysqld_safe 執行或者作為 Windows 服務執行。

有關涉及的步驟，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/innodb-init.html 。

 

1.4.3          InnoDB 系統表空間

預設情況下， InnoDB 後設資料、撤消日誌和緩衝區儲存在系統“表空間”中。這是單個邏輯儲存區域，可以包含一個或多個檔案。每個檔案可以是常規檔案或原始分割槽。最後的檔案可以自動擴充套件。 InnoDB 使用兩個主要的基於磁碟的資源來執行：

表空間 ：在單個邏輯儲存區域中儲存表內容（資料行）和索引

日誌檔案 ：記錄回滾和恢復的事務活動

InnoDB 在表空間中儲存資料、索引、後設資料、日誌和緩衝區。預設情況下，資料和索引儲存在基於表的表空間中。 InnoDB 使用共享表空間來包含後設資料、撤消日誌、更改緩衝區以及兩次寫緩衝區。

共享表空間可以佔用多個檔案。可以將共享表空間中的最後一個檔案配置為自動擴充套件，在這種情況下，如果表空間裝滿， InnoDB 會自動擴充套件該表空間。

預設情況下，共享表空間還包含回滾段。事務修改行時，將在回滾段中儲存撤消日誌資訊。此資訊用於回滾失敗的事務。通過將 innodb_undo_logs 選項設定為非零值並配置 innodb_undo_tablespaces 的值，來將回滾段移出共享表空間。

 

1.4.4          InnoDB 資料表空間

除了系統表空間之外， InnoDB 還在資料庫目錄中建立另外的表空間，用於每個 InnoDB 表的 .ibd 檔案。 InnoDB 建立的每個新表在資料庫目錄中設定一個 .ibd 檔案來搭配表的 .frm 檔案。 .ibd 檔案用作表自己的表空間檔案， InnoDB 儲存表內容和索引。

基於表的表空間（獨立表空間）支援表壓縮、支援空間回收（通過 TRUNCATE ）、– 支援動態行格式；

使用共享表空間的好處是移除大量資料的語句使用較少檔案系統開銷，例如 DROP TABLE 或 TRUNCATE TABLE ，同時避免冗餘臨時資料檔案。另外，共享表空間包含 InnoDB 資料字典和回滾段，可以使用 innodb_file_per_table 選項控制此設定。如果不想將資料儲存在基於表的表空間中，可以通過使用 skip_innodb_file_per_table 選項或者將 innodb_file_per_table 選項設定為 OFF 來將資料儲存在共享資料庫中。禁用該選項不會影響已經建立的任何 InnoDB 表的可訪問性。仍然可以訪問這些表。

同一資料庫也可以不同表混合使用不同的表空間型別。更改該設定僅會更改已建立的新表的預設值，或者已更改來將引擎設定為 InnoDB 的表（甚至已經在使用 InnoDB 的表）的預設值。

 

1.4.5          Innodb 表空間目錄結構

                                             

除了其表空間檔案， InnoDB 儲存引擎還管理一組特定於 InnoDB 的日誌檔案，其中包含關於正在進行的事務的資訊。客戶機執行事務時，其進行的更改存放在 InnoDB 日誌 (ib_logfile) 中。最新日誌內容快取在記憶體中。通常，快取的日誌資訊會在事務提交時寫入並重新整理到磁碟上的日誌檔案中，雖然也可能提前發生該操作。

 

1.4.6          共享表空間配置

共享表空間通過新增資料檔案增加表空間大小。如果命名了多個資料檔案，則通過分號 (;) 符號分隔這些檔案。增加 InnoDB 系統表空間的一種更容易（但是不太可取）的方式是從一開始就將其配置為自動擴充套件。在表空間定義中指定最後一個資料檔案的 autoextend 屬性。然後， InnoDB 在用完空間時會以 64 MB 增量自動增加該檔案的大小。可以通過設定 innodb_autoextend_increment 系統變數的值來更改該增量大小，以 MB 為單位。如果使用關鍵字 autoextend 定義了最後一個資料檔案，重新配置表空間的過程必須考慮最後一個資料檔案已經增長到的大小。獲取資料檔案的大小，將其向下舍入到 1024 × 1024 位元組 (= 1 MB) 的最近倍數，並在 innodb_data_file_path 中顯式指定舍入的大小。然後，可以新增其他資料檔案（使用尚不存在的檔名）。

在 my.cnf 檔案中使用 innodb_data_file_path 選項。

[mysqld]

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

示例 ：建立一個表空間，其中包含一個名為 ibdata1 且大小為 50 MB （固定）的資料檔案和一個名為 ibdata2 且大小為 50 MB （自動擴充套件）的資料檔案：

[mysqld]

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

預設情況下將檔案放置在 data 目錄中。如果需要，顯式指定檔案位置。

請記住，僅 innodb_data_file_path 中的最後一個資料檔案可以指定為自動擴充套件。

有關 InnoDB 配置引數的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html 。

 

1.4.7          日誌檔案和緩衝區

A.         日誌檔案

客戶機執行事務時，其進行的更改存放在 InnoDB 日誌中。最新日誌內容快取在記憶體中（日誌緩衝區）。快取的日誌資訊會在事務提交時寫入並重新整理到磁碟上的日誌檔案中，雖然也可能提前發生該操作。

如果修改表時發生故障，將使用日誌檔案進行自動恢復。 MySQL 伺服器重新啟動時，它會重新應用日誌中記錄的更改，以確保表中反映所有已提交事務。有關更改 InnoDB 日誌檔案的數量和大小的資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html 。

B.         緩衝區

InnoDB 維護其自己的緩衝區池以在主記憶體中快取常用的資料和索引。 InnoDB 在表空間中儲存其表和索引。 InnoDB 表可以非常大，甚至在檔案大小限制為 2 GB 的作業系統上也是如此。可以啟用多個緩衝區池來最大程度地降低爭用。此快取記憶體應用於如此多種型別的資訊並且將處理速度提高如此多，您應該將多達 80% 的資料庫伺服器的實體記憶體分配給 InnoDB 緩衝區池。

如果未指定 InnoDB 配置選項，則 MySQL 在 MySQL 資料目錄中建立一個名為 ibdata1 且大小為 10 MB 的自動擴充套件資料檔案以及名為 ib_logfile0 和 ib_logfile1 且大小為 5 MB 的兩個日誌檔案。要獲得良好效能，您應該指定顯式 InnoDB 引數。

 

配置緩衝區池以獲得良好效能

MySQL 使用多個緩衝區池來改進大型緩衝區池（通常為多 GB 範圍）的效能。

InnoDB 緩衝區池很大時，可以從記憶體（而不是硬碟）快速檢索許多資料請求。 MySQL 使用多個緩衝區池例項來最大程度地降低執行緒之間的爭用。儲存在緩衝區池中或者從緩衝區池讀取的每個頁將基於雜湊值分配給一個緩衝區池。每個緩衝區池管理其自己的資料。使用雜湊函式將緩衝區池中的每個頁隨機分配給一個緩衝區池。每個緩衝區池管理其自己的空閒列表、重新整理列表、 LRU 以及連線到緩衝區池的所有其他資料結構，並由其自己的緩衝區池互斥鎖進行保護。

預設情況下， MySQL 配置八個緩衝區池例項（除了在 32 位 Windows 系統上）。要更改此設定，請將 innodb_buffer_pool_instances 配置選項設定為從 1 （最小值）到 64 （最大值）的一個值。僅當將 innodb_buffer_pool_size 設定為大小為 1 GB 或更大時，此選項才生效。您指定的總大小在所有緩衝區池之間分割。您應指定 innodb_buffer_pool_instances 和 innodb_buffer_pool_size 的組合，從而每個緩衝區池例項至少為 1 GB 。

在啟動時預裝入緩衝區池來提高效能，請啟用選項：

– innodb_buffer_pool_load_at_startup

– innodb_buffer_pool_dump_at_shutdown

1.4.8          NoSQL 和 Memcached API

InnoDB 支援具有整合的 Memcached 守護程式的外掛，其在相同程式空間中執行，從而以較低開銷進行高效資料通訊。對於某些操作，通過使用較簡單的 Memcached API （基於文字的協議和二進位制協議），而不是使用 SQL 層，您的應用程式可以在提交 SQL 語句時繞過所需的解析和優化階段，並避免檢查強型別資料的開銷。此型別的應用程式稱為 NoSQL （ Not Only SQL ，不僅僅是 SQL ）。

除了傳統的 Memcached 環境（其在重新啟動 Memcached 時會丟失鍵 / 值儲存）， InnoDB 整合表示您可以將 Memcached 儲存配置為持久儲存到 InnoDB 。因為 InnoDB 支援該儲存，所以您可以受益於 InnoDB 的其他功能，例如緩衝資料訪問和故障恢復。這意味著您可以使用記憶體儲存的 Memcached 儲存（由強大而持久的 InnoDB 表支援）來獲得速度和簡化。

您仍可以通過 SQL 訪問基礎表以進行報告、分析、即席查詢、批量載入、集合運算（例如並集和交集）以及非常適合於 SQL 的表達性和靈活性的其他運算（例如彙總和聚合）。因為 Memcached 消耗相對較少的 CPU 並且易於控制其記憶體容量，所以可以與 MySQL 例項在同一系統上一起順利執行。

 

1.4.8.1.        配置 Memcached

A.         配置 Memcached 表

MySQL>SOURCE <MYSQL_HOME>/scripts/innodb_memcached_config.sql

或者

MySQL>SOURCE /usr/share/mysql/innodb_memcached_config.sql

innodb_memcached_config.sql 指令碼設定 Memcached 配置資料庫及其表。預設 InnoDB 後備儲存是 test 資料庫中的 demo_test 表。可以通過修改 innodb_memcache 資料庫中的容器表來更改此項。

innodb_memcache 資料庫，其包含下列表：

l   cache_policies ：存放每個 Memcached 操作的後備儲存策略；本地 RAM 快取記憶體、 InnoDB 或兩者

l   Containers ：存放用作 Memcached 後備儲存的每個 InnoDB 表的配置

l   config_options ：存放 separator 和 table_map_delimiter 配置選項的值

test 資料庫，其包含下列表： demo_test

 

B.         安裝 Memcached 外掛：

INSTALL PLUGIN daemon_memcached SONAME "libmemcached.so";

該外掛啟用許多可配置變數。要檢視 Memcached 配置變數，請發出以下命令：

SHOW VARIABLES LIKE 'daemon_memcached%';

例如，以下引數配置 Memcached 外掛的行為：

l   daemon_memcached_enable_binlog ：啟用 Memcached 操作的二進位制日誌記錄，其允許複製 Memcached API 操作。

l   daemon_memcached_r_batch_size ：控制讀取批處理大小，出於效能考慮。

l   daemon_memcached_w_batch_size ：控制寫入批處理大小。預設值為 1 ，所以會立即提交每個 Memcached 寫入操作

 

C.         配置 Memcached 變數：

– daemon_memcached_option ：在啟動時傳遞給 Memcached 守護程式的空格分隔的選項

 

1.4.8.2.        Memcached 的鍵 / 值資料

Memcached 協議支援 add 、 delete 、 set 、 get 、 incr 和 decr 等操作。

l   每個操作處理快取記憶體中的鍵 / 值對。

鍵和值作為字串來傳遞。鍵不能包含空格、回車符、換行符、製表符等空白字元。鍵類似於表中的主鍵，值類似於同一表中的另一列。

l   快取記憶體是平面名稱空間。

確保每個鍵都是唯一的。例如，如果您提供其他表中的鍵 / 值，則在鍵前面附加該表名稱。

l   值可以是簡單字串（或儲存為字串的數值）或序列化複雜資料。

使用 Memcached 的主要好處是讀取或寫入鍵 / 值資料時效率較高。在概念上，這類似於一個包含兩列的表，其中鍵列是主鍵（因此是唯一的）。鍵和值儲存為字串，由於 Memcached 協議的性質，鍵值不能包含空格或換行符。

對於鍵 / 值查詢， Memcached 外掛使用的直接低階別資料庫訪問路徑比等效 SQL 查詢高效得多。

值儲存為字串，可以表示簡單值，例如數值、字串或已經序列化的複雜值（即，置於可以重新構造原始指標和巢狀的文字形式）。必須在 API 介面的 Memcached 外部執行此序列化（例如，通過在程式碼中實現 Java Serializable 介面）或者通過將複雜資料格式化為 JSON 或 XML 。

類似地，必須在訪問 memcached 表的 SQL 語句中將簡單數值從字串轉換為數值（例如，通過使用 CAST() 函式）。

 

1.4.8.3.        將 NoSQL 用於 MySQL 資料庫

使用 InnoDB 表作為 Memcached 操作的容器（後備儲存）。要從 Memcached 獲得最大好處，請將其用作快速鍵 / 值訪問方法，並使用 InnoDB 後備儲存。 Memcached 協議不支援簡單聚合、聯接資料或 SQL 協議支援的其他功能。使用 InnoDB 表作為 Memcached 鍵 / 值對的容器，您可以執行功能強大的 SQL 語句，來執行使用 Memcached 協議建立和修改的資料分組、聚合和聯接。

鍵和值是字串。鍵最大大小為 250 位元組。對於更長的鍵：將鍵值雜湊到小於 250 位元組的值，為 Memcached 鍵重新配置允許的大小；

通過指定不同類的 Memcached 資料來使用多個 InnoDB 表作為 Memcached 容器。在 containers 表中配置此項。例如，以 get @@newcontainer 形式發出 Memcached 請求，將後續請求重定向到名為“ newcontainer ” 的容器。

使用 innodb_memcache.containers 表可以配置一個或多個 InnoDB 表來用作 Memcached 後備儲存。指定容器表的名稱、模式和鍵列、值、標誌以及其他設定。每個容器具有一個 Memcached 名稱，可以用來標識 Memcached 操作的容器。操作定位於名為“ default ” 的容器，直到被要求更改容器。

/usr/share/mysql/innodb_memcached_config.sql 檔案建立和填充 innodb_memcache.containers 表，是配置容器的很好示例。

1.4.9          引用完整性

將相關的資料分割到單獨表中時，可以使用主鍵和外來鍵來加強表關係。一個表的主鍵被另一個表中的鍵引用，引用鍵稱為外來鍵，每個表都需要主鍵。

INSERT 到輔助表（而沒有指向主表的外來鍵）不會完成。通過在聯接的列上使用外來鍵將提高 JOIN 效能。 UPDATE 或 DELETE 資料時，可以使用 CASCADE 選項自動更新或刪除輔助表中的任何相關資料。

使用外來鍵：

l   通過使用最常查詢的列為每個表指定主鍵，如果沒有明顯主鍵則指定自動增量值。

l   可以嘗試將資料插入在主表中沒有相應資料的輔助表，但是其返回錯誤且插入失敗。

l   為了獲得快速聯接效能，在聯接列上定義外來鍵，並在每個表中使用相同資料宣告這些列。如果您啟用 CASCADE 選項，外來鍵將刪除或更新傳播到所有受影響的表，並且當父表中沒有相應列時禁止在子表中插入資料。

在 City 表中指定主鍵的示例：

CREATE TABLE City (

ID int(11) NOT NULL AUTO_INCREMENT,

Name char(35) NOT NULL DEFAULT '',

CountryCode char(3) NOT NULL DEFAULT '',

District char(20) NOT NULL DEFAULT '',

Population int(11) DEFAULT NULL,

PRIMARY KEY (ID),

KEY CountryCode (CountryCode),

CONSTRAINT city_ibfk_1 FOREIGN KEY (CountryCode) REFERENCES country (Code)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

 

1.4.10       多版本控制

多版本控制儲存關於已更改行的舊版本的資訊，支援併發和回滾等事務功能，並使用稱為回滾段的資料結構在表空間中儲存資訊。

回滾段是包含撤消日誌的儲存區域，其預設情況下是系統表空間的一部分。 InnoDB 使用回滾段中的資訊執行事務回滾中所需的撤消操作。它還使用該資訊來構建行的早期版本以實現一致讀取。在內部向資料庫中儲存的每行新增三個欄位：

DB_TRX_ID ：六個位元組的欄位，指示插入或更新該行的最後一個事務的事務識別符號。此外，刪除在內部被視為更新處理，其中設定行的特殊位來將其標記為刪除。

DB_ROLL_PTR ：稱為滾動指標的七個位元組的欄位。它指向寫入回滾段的撤消日誌記錄。如果更新了行，撤消日誌記錄包含重建行被更新前的內容所需的資訊。

DB_ROW_ID ：包含行 ID 的六個位元組的欄位，隨著插入新行而單調增加。如果 InnoDB 自動生成群集索引，該索引包含行 ID 值。否則， DB_ROW_ID 列不顯示在任何索引中。

如果在表中按相同速度以小批量插入和刪除行，清除執行緒會滯後，表會因為所有“死”行而變得越來越大，使所有操作受到磁碟限制並且非常慢。在這種情況下，通過使用 innodb_max_purge_lag 系統變數限制新行操作並向清除執行緒分配更多資源。

 

1.4.11       Lock

InnoDB Lock 無需設定鎖即可實現一致讀取。對 DML 語句使用行級別 Lock ，從不提升鎖，死鎖的等待圖形檢測。

InnoDB 具有下列一般 Lock 屬性：

l   InnoDB 無需設定鎖即可實現一致讀取，因為它使用多版本控制，從而不需要鎖。修改行的事務可以檢視其自己版本的那些行，撤消日誌使其他事務可以檢視原始行。要強制 SELECT 語句 Lock 資料，可以向該語句新增 Lock 修飾符。

l   需要鎖時， InnoDB 使用行級別 Lock 。與多版本控制一起，這將產生良好的查詢併發性，因為給定表可以由不同客戶機同時讀取和修改。

l   InnoDB 在發現需要鎖時可以獲取行鎖。從不通過將鎖轉換為頁鎖或表鎖來提升該鎖。這樣可以最大程度降低鎖爭用，提高併發性（雖然其對 DDL 操作使用表級別 Lock ）。

l   可能會出現死鎖，因為 InnoDB 在事務過程中直到需要鎖時才獲取鎖。 InnoDB 可以刪除死鎖並回滾一個事務來解決死鎖。

l   失敗的事務最終開始超時， InnoDB 將回滾這些事務。

有關死鎖的更多資訊，請參見 MySQL 術語詞彙表，網址為：

http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_deadlock 。

 

1.4.12       Next-Key Lock

InnoDB 使用一種稱為 Next-Key Lock 的演算法，其使用行級別 Lock ，搜尋 / 掃描表索引並在索引記錄上設定共享鎖或互斥鎖；

Next-Key Lock 會影響索引記錄前面的“間隙”，即新索引記錄無法正好插在 Lock 記錄前面。

Gap Lock 可防止出現“幻影問題”。在下面的示例中，第一個事務 Lock 大於 10 的任何值，即使該值不存在也是如此：

事務 1 ：

SELECT c FROM t WHERE c > 10 FOR UPDATE;

事務 2 ：

INSERT INTO t(c) VALUES (50);

InnoDB 將一種稱為 Next-Key Lock 的演算法用於行級別 Lock 。按以下方式執行 Lock ：搜尋或掃描表的索引時，會在遇到的索引記錄上設定共享鎖或互斥鎖。因此，行級別鎖實際是索引記錄鎖。 InnoDB 在索引記錄上設定的 Next-Key Lock 會影響索引記錄前面的“間隙”。如果使用者在索引中的記錄上具有共享鎖或互斥鎖，其他使用者無法按索引順序正好在 Lock 記錄前面（間隙）插入新記錄。

執行此間隙的 Next-Key Lock 可以防止所謂的幻影問題。同一查詢在不同時間產生不同行集時，事務內會出現幻影問題。例如，如果 SELECT 執行兩次，但第二次返回的行不是第一次返回的行，該行是“幻影”行。

 

1.4.13       非 Lock 一致性讀取

時間	會話   1	會話   2
|	s1> START TRANSACTION;	s2> START TRANSACTION;
| |	s1> SELECT * FROM t; Empty Set
|		s2> INSERT INTO t VALUES (1, 2);
| V	s1> SELECT * FROM t; Empty Set
|		s2> COMMIT;
| |	s1> SELECT * FROM t;   Empty Set
|	s1> COMMIT;
| V	s1> SELECT * FROM t;   ---------------------   | 1 | 2 |   ---------------------   1 row in set

一致讀取表示 InnoDB 使用多版本控制對您的查詢提供某個時間點的資料庫快照。您的查詢看見在時間點之前提交的事務所進行的更改，不會看見後面或未提交事務進行的更改。示例中，僅當會話 2 已提交了插入並且會話 1 也已提交（從而時間點超過會話 2 的提交）時，會話 1 才看見會話 2 插入的行。如果要檢視資料庫的“最新”狀態，請使用 READ COMMITTED 隔離級別或 Lock 讀取。

 

1.4.14       減少死鎖

要減少死鎖的可能性，請使用事務，而不是 LOCK TABLE 語句：

l   將插入或更新資料的事務保持足夠小（很少行數），從而其不會保持開啟狀態很長時間。經常提交事務。

l   不同的事務更新多個表或大範圍的行時，在每個事務中使用相同順序的操作（例如， SELECT ... FOR UPDATE ）。

l   在 WHERE 子句中使用的列上建立索引。

         不要設定隔離級別以避免死鎖，此操作沒有作用，因為隔離級別更改讀取操作的行為，而死鎖是由於寫入操作而出現的，與讀取操作無關。

如果事務由於死鎖而失敗則重新發出該事務。死鎖並不危險，只需要重試。另外，按固定順序訪問表和行，或者向表新增精心選擇的索引也有助於減少死鎖。

         如果出現死鎖， InnoDB 會檢測到該情況並回滾一個事務（犧牲者）。因此，即使您的應用程式邏輯完全正確，您仍須處理必須重試事務的情況。要監視死鎖出現的頻率（以及許多其他 InnoDB 統計資訊），請使用 SHOW ENGINE INNODB STATUS 命令：

mysql> SHOW ENGINE INNODB STATUS\G

=====================================

110222 16:54:12 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 5 seconds

...

 

1.4.15       外來鍵 Lock

建立表時新增外來鍵約束：

CREATE TABLE City (

...

CountryCode char(3) NOT NULL DEFAULT '',

...

KEY CountryCode (CountryCode),

CONSTRAINT city_ibfk_1 FOREIGN KEY

(CountryCode) REFERENCES country (Code)

...

約束就是在表中的一個或多個列值上放置的限制，從而積極強制執行完整性規則。約束使用索引來實現。如果在表上定義了外來鍵約束，將在用於引用外來鍵約束的插入、更新或刪除操作中的任何記錄上放置共享記錄級別鎖。 InnoDB 在約束失敗的情況下也設定這些鎖。

InnoDB 逐行檢查外來鍵約束。執行外來鍵檢查時， InnoDB 在其必須檢視的子或父記錄上設定共享行級別鎖。 InnoDB 立即檢查外來鍵約束；該檢查不會延遲到事務提交時。示例顯示 City 表的 CountryCode 列與 Country 表的 Code 列相關。對任一列的任何更改都受到此關係的約束。

有關外來鍵約束的更多資訊，請參見《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html 。

1.5.         MyISAM 儲存引擎

MyISAM 儲存引擎將磁碟上的每個表儲存在三個檔案中（ .frm 、 .MYD 和 .MYI ）並具有以下功能：

l   支援 FULLTEXT 搜尋和空間資料型別

l   靈活的 AUTO_INCREMENT

l   壓縮式只讀表，可以節省空間

l   表級別 Lock 來管理查詢之間的爭用

l   可移植儲存格式

l   可以指定表的行數

l   可以控制非唯一索引的更新以便將資料載入到空表中

在 MySQL 伺服器版本 5.5.5 之前， MyISAM 是預設 MySQL 儲存引擎。當前預設是 InnoDB 儲存引擎。 mysql 資料庫包含 MyISAM 格式的表。

每個 MyISAM 表由三個檔案表示：

l   格式檔案： 儲存表結構的定義 (mytable.frm)

l   資料檔案： 儲存錶行的內容 (mytable.MYD)

l   索引檔案： 儲存表上的所有索引 (mytable.MYI)

其他功能：

l   MyISAM 表佔用的空間非常小。

l   表儲存格式是可移植的，所以表檔案可以直接複製到其他主機並由該主機上的伺服器使用。

l   將資料載入到空表中時，可以禁用非唯一索引的更新，然後在載入資料後重新啟用。

l   MyISAM 支援幾何空間擴充套件。

l   MyISAM 可以通過將表大小限制為特定數量的行來提高效能。

有關 MyISAM 儲存引擎功能的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html 。

 

1.6.         MEMORY 儲存引擎

MEMORY 儲存引擎使用記憶體中儲存的、通過 .frm 檔案表示在磁碟上的內容建立表。

它具有以下功能：

l   表資料和索引儲存在記憶體中

l   由於是記憶體中儲存所以具有很高效能

l   固定長度行儲存格式

l   重新啟動後表內容將丟失

l   最大大小選項 --max-heap-table-size

l   表級別 Lock

MEMORY 表：

l   不能包含 TEXT 或 BLOB 列

l   可以針對不同的列使用不同字符集

l   每個表通過資料庫目錄中的 .frm 格式檔案表示在磁碟上。

l   伺服器重新啟動後內容將丟失（結構存在，但是表包含零行）。

l   MySQL 使用表級別 Lock 管理查詢爭用。不會出現死鎖。

HEAP 可能仍舊以舊 SQL 程式碼顯示， MySQL 伺服器仍舊可識別 HEAP 以實現向後相容。處理更新時， MEMORY 效能受到由於單執行緒執行所導致的爭用和表鎖開銷的約束。這限制了負載增加時的可伸縮性，特別是對於包括寫入的語句混合情況。此外， MEMORY 在伺服器重新啟動過程中不保留表內容。

MEMORY 是有效且有用的儲存引擎，對於設計用來提高效能以及滿足特定業務規則的幾乎所有應用，應該考慮該引擎。

有關 MEMORY 儲存引擎功能的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/memory-storage-engine.html 。

 

1.7.         ARCHIVE 儲存引擎

ARCHIVE 儲存引擎用於以壓縮格式儲存大量資料從而佔用非常少的資源。它具有下列主要特徵：

l   由 .frm 檔案表示

l   資料檔案： .ARZ

l   不支援索引

l   支援 INSERT 和 SELECT ，但不支援 DELETE 、 REPLACE 或 UPDATE

l   支援 ORDER BY 操作和 BLOB 列

l   接受除空間資料型別之外的所有資料型別

l   使用行級別 Lock

l   支援 AUTO_INCREMENT 列

 

1.8.         BLACKHOLE 儲存引擎

BLACKHOLE 儲存引擎充當“黑洞” ，其接受資料，但是將其丟棄而不儲存。此儲存引擎具有下列主要特徵：

l   由 .frm 檔案表示

l   用於複製

l   支援所有種類的索引

l   檢索始終返回空結果

l   驗證轉儲檔案語法

l   根據二進位制日誌記錄度量開銷

l   “空操作” 儲存引擎，可用於查詢與儲存引擎無關的效能瓶頸

l   識別事務

提交的事務寫入二進位制日誌，回滾的事務不寫入

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16976507/viewspace-2649087/，如需轉載，請註明出處，否則將追究法律責任。