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

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

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

???‍??一、InnoDB存儲(chǔ)引擎的特性

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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