MySQL?存儲引擎InnoDB最佳實(shí)踐

更新時間：2025年05月09日 10:24:04 作者：JhonKI

InnoDB是一款兼顧高可靠性和高性能的通用存儲引擎,在MySQL8.0中默認(rèn)的存儲引擎是InnoDB,這篇文章主要介紹了MySQL存儲引擎InnoDB詳解,需要的朋友可以參考下

???‍??一、InnoDB存儲引擎的特性

???‍??二、|nnoDB 的主要優(yōu)勢

DML操作遵循ACID模型，事務(wù)具有提交、回滾和崩潰恢復(fù)功能，以保護(hù)用戶數(shù)據(jù)。事務(wù)和鎖專題中介紹
如果發(fā)生意外而崩潰，無論當(dāng)時數(shù)據(jù)庫發(fā)生了什么，都不需要在重啟數(shù)據(jù)庫后執(zhí)行任何特殊操作。InnoDB 的崩潰恢復(fù)功能會自動完成崩潰之前提交的更改，并撤消崩潰前正在進(jìn)行但未提交的更改，從而允許我們從中斷的地方繼續(xù)執(zhí)行。備份與恢復(fù)專題介紹
支持行級鎖，提高了多用戶的讀取并發(fā)性和性能。事務(wù)和鎖專題中介紹
InnoDB 存儲引擎維護(hù)了一個自己的緩沖池，訪問數(shù)據(jù)時在內(nèi)存中緩存表和索引數(shù)據(jù)，對于經(jīng)常使用的數(shù)據(jù)直接從內(nèi)存中處理，大幅提升了效率。在專用數(shù)據(jù)庫服務(wù)器上，通常會將高達(dá) 80%的物理內(nèi)存分配給緩沖池。
InnoDB表優(yōu)化了基于主鍵的查詢，每個InnoDB表都有一個稱為聚簇索引的主鍵索引，實(shí)現(xiàn)通過最少的磁盤I/0完成對主鍵的查找。索引專題中介紹
為了保持?jǐn)?shù)據(jù)完整性，InnoDB 支持 FOREIGN KEY(外鍵)約束。在進(jìn)行插入、更新和刪除數(shù)據(jù)時確保相關(guān)表之間的一致性
當(dāng)從表中反復(fù)查詢相同的行時，自適應(yīng)哈希索引會自動接管這些查詢，此時查詢效率和哈希表相同。

???‍??三、InnoDB表的最佳實(shí)踐

如何利用 InnoDB 的特性實(shí)現(xiàn)最佳的性能

為表中最頻率查詢的列(或多個列)指定主鍵(或復(fù)合主鍵)，如果沒有明顯的主鍵，則創(chuàng)建一個自增的列做為主鍵。
從多個表中根據(jù)相同的ID查詢數(shù)據(jù)，建議使用表連接?？梢栽谶B接的列上定義外鍵，并在每個表中使用相同的數(shù)據(jù)類型聲明這些列。添加外鍵可以確保被引用的列使用索引，從而提高性能。
在每秒提交數(shù)百次事務(wù)的服務(wù)器上，結(jié)合存儲設(shè)備的寫入速度，關(guān)閉事務(wù)的自動提交，通過系統(tǒng)變量 autocommit=0FF 設(shè)置。
把相關(guān)的DML操作用 START TRANSACTION 和 COMMIT 語句括在一起，分組為事務(wù)一起提交或回滾。
不要使用 LOCK TABLES 語句，InnoDB可以在不犧牲可靠性和高性能的情況下處理多個會話同時對一個表進(jìn)行讀寫操作。

???‍??四、驗(yàn)證InnoDB是否為默認(rèn)存儲引擎

執(zhí)行 SHOW ENGINES 語句查看可用的存儲引擎時，查找 SUPPORT 列的值為 DEFAULT 的行

使用 SELECT * FROM INFORMATION_SCHEMA.ENGINES \G 查看當(dāng)前 MySQL 服務(wù)器支持的存儲引擎及其詳細(xì)信息的命令

INFORMATION_SCHEMA 是 MySQL 內(nèi)置的系統(tǒng)數(shù)據(jù)庫，存儲了數(shù)據(jù)庫、表、列等元數(shù)據(jù)信息。
ENGINES 是該數(shù)據(jù)庫中的一張表，記錄了 MySQL 服務(wù)器支持的所有存儲引擎的詳細(xì)信息。

如果InnoDB不是默認(rèn)的存儲引擎，可以通過在命令?指定選項(xiàng) --default-storageengine=InnoDB
或者在選項(xiàng)?件的 [mysqld] 節(jié)點(diǎn)定義 default-storageengine=InnoDB 并重新啟動服務(wù)器來設(shè)置 InnoDB 存儲引擎

由于業(yè)務(wù)實(shí)際需要，服務(wù)器默認(rèn)存儲引擎不是InnoDB時，想要創(chuàng)建一個InnoDB表，可以在使用 CREATE TABLE 語句創(chuàng)建表時明確指定 InnoDB 存儲引擎，當(dāng)然這樣方式也可以指定其他任何支持的存儲引擎

CREATE TABLE table_name (
	... 定義字段
) ENGINE = InnoDB; # 指定存儲引擎

如果想測試使用其他存儲引擎表中的數(shù)據(jù)在InnoDB表中的工作情況，在確保不影響原始表的情況下，使用以下方式創(chuàng)建一張InnoDB表

CREATE TABLE ... ENGINE=InnoDB AS SELECT * FROM other_engine_table;

???‍??五、創(chuàng)建InnoDB表

# 選擇?標(biāo)數(shù)據(jù)庫
use test_db
# 創(chuàng)建?個使?InnoDB存儲引擎的表
CREATE TABLE t_innodb (
	id int(11) PRIMARY KEY AUTO_INCREMENT,
	name varchar(20)
) ENGINE = InnoDB;

當(dāng)創(chuàng)建一個存儲引擎為 InnoDb 的表時，會在 data_dir/test_db 目錄下生成一個用來存儲真實(shí)數(shù)據(jù)的物理文件，命名格式為表名.ibd

以當(dāng)前為例會在 /var/lib/mysql/test_db 目錄下生成一個 t_innodb.ibd 的表空間數(shù)據(jù)文件

表空間文件 t_innodb.ibd 用來存儲表中的數(shù)據(jù)

在MySQL8.0中表結(jié)構(gòu)的信息也保存在.ibd 文件中，可以使用 ibd2sdi 工具提取表定義的具體信息，使用方法: ibd2sdi --dump-file=t_innodb.txt t_innodb.ibd，生成的t_innodb.txt 文件中有對應(yīng)表的具體描述
sid = Serialized DictionaryInformation 序列化字典信息和
8.0有所不同的是，在MySOL5.X及以前的版本中使用一個后綴為 .frm 的二進(jìn)制文件來記錄和描述表定義的信息