楊卓凡

摘要：隨著社會(huì)信息數(shù)據(jù)量高速發(fā)展，龐大數(shù)據(jù)量信息需要高效存儲(chǔ)在數(shù)據(jù)庫系統(tǒng)中。MySQL數(shù)據(jù)庫是一款較為普遍使用的開源關(guān)系型數(shù)據(jù)庫系統(tǒng)，其數(shù)據(jù)的存儲(chǔ)過程是依靠存儲(chǔ)引擎進(jìn)行的。文章基于幾種主流存儲(chǔ)引擎下分別進(jìn)行了數(shù)據(jù)存儲(chǔ)效率的實(shí)驗(yàn)研究與對(duì)比，從而給用戶進(jìn)行存儲(chǔ)數(shù)據(jù)時(shí)可根據(jù)不同需求和應(yīng)用環(huán)境選擇適合的存儲(chǔ)引擎提供了一些建議，進(jìn)而能使數(shù)據(jù)庫性能得到提升優(yōu)化。

關(guān)鍵詞： MySQL數(shù)據(jù)庫;存儲(chǔ)引擎;研究對(duì)比;性能優(yōu)化

中圖分類號(hào)：TP311.13? ? ? ? 文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1009-3044（2022）21-0018-03

開放科學(xué)（資源服務(wù)）標(biāo)識(shí)碼（OSID）：

1 序言

隨著Web2.0時(shí)代的來臨，人類社會(huì)信息數(shù)據(jù)量呈現(xiàn)爆發(fā)式增長(zhǎng)，在很多項(xiàng)目中存儲(chǔ)百萬級(jí)甚至上千萬級(jí)的數(shù)據(jù)很是普遍。MySQL數(shù)據(jù)庫則是一款應(yīng)用在企業(yè)級(jí)存儲(chǔ)數(shù)據(jù)較為流行的數(shù)據(jù)庫，如何高效率高質(zhì)量地將外部大量數(shù)據(jù)存儲(chǔ)或更新到MySQL數(shù)據(jù)庫中是許多項(xiàng)目需考慮的問題，而MySQL是依靠其存儲(chǔ)引擎來進(jìn)行存儲(chǔ)數(shù)據(jù)的[1]。因此，本文就其主流的幾種存儲(chǔ)引擎的特點(diǎn)進(jìn)行了相關(guān)介紹及數(shù)據(jù)存儲(chǔ)實(shí)驗(yàn)的研究。

2 存儲(chǔ)引擎及其介紹

2.1 存儲(chǔ)引擎定義及原理

存儲(chǔ)引擎是一種關(guān)于如何存儲(chǔ)數(shù)據(jù)，如何為存儲(chǔ)的數(shù)據(jù)建立索引以及如何更新，查詢數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方法。因?yàn)樵陉P(guān)系數(shù)據(jù)庫中數(shù)據(jù)的存儲(chǔ)是以表的形式存儲(chǔ)的，所以存儲(chǔ)引擎也可以稱為表類型（即存儲(chǔ)和操作此表的類型）。MySQL數(shù)據(jù)庫存儲(chǔ)引擎體系結(jié)構(gòu)如圖1所示[2]。

如圖1所示，從上到下依次是連接層、服務(wù)層、存儲(chǔ)引擎層和數(shù)據(jù)存儲(chǔ)層[3]。連接層主要提供客戶端接口和連接服務(wù)以及進(jìn)行授權(quán)認(rèn)證等操作，服務(wù)層包括SQL接口、Parser解析器、Optimizer查詢優(yōu)化器和Cache & Buffer查詢緩存，存儲(chǔ)引擎層實(shí)際進(jìn)行MySQL中數(shù)據(jù)的存儲(chǔ)和提取操作，MySQL服務(wù)器通過API與存儲(chǔ)引擎進(jìn)行通信，數(shù)據(jù)存儲(chǔ)層主要將數(shù)據(jù)存儲(chǔ)在運(yùn)行于裸設(shè)備的文件系統(tǒng)之上，并完成與存儲(chǔ)引擎的交互[4]。

2.2 存儲(chǔ)引擎主要類別

MySQL數(shù)據(jù)庫存在多種存儲(chǔ)引擎，其名稱與基本描述如表1所示。

如表1所示，不同存儲(chǔ)引擎依據(jù)其自身的特點(diǎn)適用于不同的場(chǎng)景下，其中使用最為廣泛的是MyISAM和InnoDB兩種存儲(chǔ)引擎。自MySQL5.5版本之后，MySQL的默認(rèn)內(nèi)置存儲(chǔ)引擎已經(jīng)是InnoDB了，其最大的優(yōu)勢(shì)在于提供事務(wù)支持，災(zāi)難恢復(fù)性較好。而MyISAM數(shù)據(jù)引擎雖插入速度快，但不支持事務(wù)處理，使用MyISAM創(chuàng)建表時(shí)，每張表的結(jié)構(gòu)及數(shù)據(jù)是依賴三個(gè)文件存儲(chǔ)在數(shù)據(jù)庫中的。三個(gè)文件的文件名與表名相同，文件后綴名分別是SDI、MYD和MYI。其中.SDI文件用于存儲(chǔ)表的元數(shù)據(jù)信息，.MYD存儲(chǔ)的是數(shù)據(jù)信息，而.MYI則是索引信息存儲(chǔ)的文件。下面主要進(jìn)行這兩種存儲(chǔ)引擎的數(shù)據(jù)存儲(chǔ)實(shí)驗(yàn)及其對(duì)實(shí)驗(yàn)結(jié)果的對(duì)比分析。

3 存儲(chǔ)實(shí)驗(yàn)的環(huán)境搭建及準(zhǔn)備工作

以下是基于MyISAM存儲(chǔ)引擎下的搭建及準(zhǔn)備工作。

（1） 軟件環(huán)境的搭建：

Windows10專業(yè)版，MySQL-8.0.23-winx64，

Navicat for MySQL，IIS6。

（2） MySQL表結(jié)構(gòu)的設(shè)計(jì)與存儲(chǔ)過程的定義。

表定義如下：

CREATE TABLE t_tag（

tag_name varchar（100） DEFAULT NULL，

tag_desc varchar（100） DEFAULT NULL，

tag_id? bigint（100） NOT NULL AUTO_INCREMENT，

PRIMARY KEY （tag_id）

）ENGINE=MyISAM AUTO_INCREMENT=1099999 DEFAULT CHARSET=latin1

存儲(chǔ)過程定義如下：

create procedure myproc（）

begin

declare index bigint;

declare number bigint;

set index=1;

while index

insert into t_tag（tag_name，tag_desc）values（concat（“tag”，index），concat（“tag”，index））;set index=index+1;

end while;

end

4 基于MyISAM引擎下數(shù)據(jù)存儲(chǔ)實(shí)驗(yàn)

4.1數(shù)據(jù)存儲(chǔ)實(shí)驗(yàn)過程

調(diào)用存儲(chǔ)過程語句為 call myproc（），通過向myproc（）存儲(chǔ)過程中定義的number變量分別多次傳入不同的值，來表示向t_tag表中分別插入不同數(shù)據(jù)量數(shù)據(jù)（每次的存儲(chǔ)過程是向空表中重新插入數(shù)據(jù)），為了保證實(shí)驗(yàn)數(shù)據(jù)的有效性、準(zhǔn)確性及可靠性，對(duì)每次的存儲(chǔ)數(shù)據(jù)過程在Navicat for MySQL客戶端進(jìn)行了三組實(shí)驗(yàn)，最終各項(xiàng)存儲(chǔ)時(shí)間取三組實(shí)驗(yàn)的平均值作為最終的實(shí)驗(yàn)結(jié)果。三組實(shí)驗(yàn)原始時(shí)間數(shù)據(jù)記錄如表2所示，且將時(shí)間數(shù)據(jù)繪制成如下曲線圖2所示（三組實(shí)驗(yàn)均成功將數(shù)據(jù)存儲(chǔ)到庫表中）。

4.2實(shí)驗(yàn)數(shù)據(jù)結(jié)果分析

由圖2可以看出，在MyISAM引擎下進(jìn)行存儲(chǔ)數(shù)據(jù)時(shí)，當(dāng)數(shù)據(jù)量在500萬以內(nèi)時(shí)，數(shù)據(jù)存儲(chǔ)到表中所需時(shí)間增長(zhǎng)較為緩慢，而當(dāng)數(shù)據(jù)量大于500萬時(shí)，從圖中可看出數(shù)據(jù)存儲(chǔ)到表中所需時(shí)間增長(zhǎng)速率較快（圖中曲線上各點(diǎn)的切線平均斜率較大）。例如從實(shí)驗(yàn)組一可以得出，數(shù)據(jù)量為1000萬時(shí)所用時(shí)間為11674.116s是數(shù)據(jù)量為100萬所用時(shí)間619.417s的18.85倍，大于數(shù)據(jù)量增長(zhǎng)的10倍。是由于MyISAM引擎不提供事務(wù)支持，也不支持行級(jí)鎖和外鍵，當(dāng)對(duì)表執(zhí)行大量的寫操作的時(shí)候需要鎖定該表，所以會(huì)造成寫操作效率降低。

5 基于InnoDB引擎下數(shù)據(jù)存儲(chǔ)實(shí)驗(yàn)

基于InnoDB存儲(chǔ)引擎下的搭建及準(zhǔn)備工作，在數(shù)據(jù)庫中建立一張新的數(shù)據(jù)表t_tag1，建立新的字段名為tag1_name，tag1_desc及tag1_id，存儲(chǔ)引擎設(shè)置為InnoDB，建立新的存儲(chǔ)過程為myproc1。

5.1影響存儲(chǔ)效率相關(guān)參數(shù)不同值的設(shè)定

InnoDB引擎下影響數(shù)據(jù)存儲(chǔ)效率主要有兩個(gè)參數(shù)autocommit（自動(dòng)提交事務(wù)參數(shù)）和innodb_flush_log_at_trx_commit（事務(wù)日志刷寫參數(shù)），兩者在默認(rèn)情況下值均為1，兩者參數(shù)均可以在MySQL安裝文件位置的源文件中的my.ini配置文件中進(jìn)行添加設(shè)置，其中autocommit取值可為0，1，innodb _flush_log_at_trx_commit可取值0，1，2[4]。

由于InnoDB引擎是支持事務(wù)處理，調(diào)用存儲(chǔ)過程語句為 call myproc1（），通過向myproc1（）存儲(chǔ)過程中定義的number變量分別多次傳入不同的值，來表示向t_tag1表中分別插入不同數(shù)據(jù)量數(shù)據(jù)（每次的存儲(chǔ)過程是向空表中重新插入數(shù)據(jù)）[5]。為了保證實(shí)驗(yàn)數(shù)據(jù)的有效性、準(zhǔn)確性及可靠性，對(duì)存儲(chǔ)數(shù)據(jù)過程在Navicat for MySQL客戶端進(jìn)行了六組實(shí)驗(yàn)，前三組實(shí)驗(yàn)（實(shí)驗(yàn)組一，實(shí)驗(yàn)組二及實(shí)驗(yàn)組三）是在autocommit設(shè)置為1下所進(jìn)行的，后三組實(shí)驗(yàn)（實(shí)驗(yàn)組四、實(shí)驗(yàn)組五及實(shí)驗(yàn)組六）是在autocommit設(shè)置為0下進(jìn)行的[6]。當(dāng)數(shù)據(jù)成功存儲(chǔ)到數(shù)據(jù)表中時(shí)，依照記錄的時(shí)間數(shù)據(jù)繪制成曲線圖，前三組實(shí)驗(yàn)組時(shí)間數(shù)據(jù)對(duì)應(yīng)為主坐標(biāo)軸，后三組實(shí)驗(yàn)組時(shí)間數(shù)據(jù)對(duì)應(yīng)為次坐標(biāo)軸。

分別設(shè)置參數(shù)innodb_flush_log_at_trx_commit值為0，1，2。各參數(shù)下實(shí)驗(yàn)原始所用時(shí)間如表3所示。

將以上三張表繪制成如下曲線圖所示，每張表將前三組實(shí)驗(yàn)組及后三組實(shí)驗(yàn)組分割為兩大類，并計(jì)算各自類中每一組插入數(shù)據(jù)量所用時(shí)間的平均值作為曲線圖中的描繪點(diǎn)。例如表3中，當(dāng)插入數(shù)據(jù)量為10萬條時(shí)，實(shí)驗(yàn)組一，實(shí)驗(yàn)組二及實(shí)驗(yàn)組三分別所用時(shí)間為63.125s，60.954s，61.876s。其三組數(shù)據(jù)的平均值為61.985s。此時(shí)autocommit設(shè)置為1，innodb_flush_log_at_trx_commit設(shè)置為0。如圖3中各曲線名稱用二維數(shù)組形式表示，例如autocommit=1，innodb_flush_log_at_trx_commit=0時(shí)表示為曲線[1，0]。其中曲線[1，0]，[1，1]，[1，2]數(shù)值對(duì)應(yīng)主坐標(biāo)軸數(shù)據(jù)，曲線[0，0]，[0，1]，[0，2]數(shù)值對(duì)應(yīng)次坐標(biāo)軸數(shù)據(jù)。

5.2實(shí)驗(yàn)數(shù)據(jù)結(jié)果分析

由上述實(shí)驗(yàn)過程及相應(yīng)的圖表結(jié)果可以得出，當(dāng)innodb_flush_log_at_trx_commit設(shè)置為0或2時(shí)（此時(shí)在autocommit值為1的默認(rèn)狀態(tài)下），數(shù)據(jù)存儲(chǔ)到表的存儲(chǔ)效率和基于MyISAM引擎下的存儲(chǔ)效率基本接近，但當(dāng)其值設(shè)置為1時(shí)，完成數(shù)據(jù)存儲(chǔ)所耗時(shí)間大幅度增加，致使性能急劇下降，這是由于每次存儲(chǔ)數(shù)據(jù)時(shí)都會(huì)自動(dòng)提交并且刷新日志，因此存儲(chǔ)效率會(huì)降低[7]，若設(shè)置autocommit的值為0時(shí)，無論innodb_flush_log_at_trx_commit的值為何值，從圖表中可以得出，當(dāng)插入數(shù)據(jù)量為同一數(shù)量級(jí)時(shí)，后三組實(shí)驗(yàn)組對(duì)應(yīng)次坐標(biāo)軸時(shí)間數(shù)據(jù)遠(yuǎn)遠(yuǎn)低于前三組實(shí)驗(yàn)組對(duì)應(yīng)的主坐標(biāo)軸時(shí)間數(shù)據(jù)，因此當(dāng)autocommit設(shè)置為0值時(shí)能大幅度提高數(shù)據(jù)存儲(chǔ)效率，這是因?yàn)楫?dāng)autocommit值為0時(shí)，即為關(guān)閉自動(dòng)提交事務(wù)功能，無需每次刷新到日志文件，因此數(shù)據(jù)讀寫性能會(huì)提升。

6結(jié)論

通過上述實(shí)驗(yàn)測(cè)試結(jié)果分析，可以得出在當(dāng)前軟件環(huán)境中，采用MyISAM引擎和在innodb_flush_log_at_trx_commit值為0或2時(shí)的狀態(tài)下的InnoDB存儲(chǔ)引擎進(jìn)行數(shù)據(jù)存儲(chǔ)效率差別不是很大，若innodb_flush_log_at_trx_commit的值為1時(shí)，在默認(rèn)狀態(tài)下的autocommit值為1時(shí)，InnoDB數(shù)據(jù)存儲(chǔ)引擎效率較低，且當(dāng)autocommit值為0時(shí)，InnoDB由于關(guān)閉了自動(dòng)提交事務(wù)和無需寫入日志文件到磁盤，因此具有高效的存儲(chǔ)效率。本文分析了兩種存儲(chǔ)引擎各自的特點(diǎn)，在實(shí)際中針對(duì)不同業(yè)務(wù)項(xiàng)目來講，各自有著不同的需求，進(jìn)而需要采用恰當(dāng)?shù)拇鎯?chǔ)引擎建立合適的存儲(chǔ)表類型，從而可以最大程度地發(fā)揮MySQL數(shù)據(jù)庫性能優(yōu)勢(shì)[8]。

參考文獻(xiàn)：

[1] 王威.MySQL數(shù)據(jù)庫源代碼分析及存儲(chǔ)引擎的設(shè)計(jì)[D].南京：南京郵電大學(xué)，2012.

[2] 徐昂，成科揚(yáng).基于關(guān)系型數(shù)據(jù)庫的SQL檢索優(yōu)化研究[J].電子設(shè)計(jì)工程，2019，27（11）：51-55.

[3] 陳小輝，文佳，鄧杰英.MySQL的體系結(jié)構(gòu)及InnoDB表引擎的配置[J].福建電腦，2009，25（7）：162，148.

[4] 周淵，王力生.MySQL中InnoDB存儲(chǔ)引擎在NUMA系統(tǒng)上的優(yōu)化[J].科技傳播，2011，3（1）：155-156.

[5] 胡雯，李燕.MySQL數(shù)據(jù)庫存儲(chǔ)引擎探析[J].軟件導(dǎo)刊，2012，11（12）：129-131.

[6] 黑馬程序員.MySQL數(shù)據(jù)庫原理、設(shè)計(jì)與應(yīng)用[M].北京：清華大學(xué)出版社，2019.

[7] 劉陽娜.大數(shù)據(jù)下的MySQL數(shù)據(jù)庫的效率優(yōu)化[J].信息通信，2017，30（12）：111-112.

[8] 張工廠.MySQL技術(shù)精粹架構(gòu)、高級(jí)特性、性能優(yōu)化與集群實(shí)戰(zhàn)[M].北京：清華大學(xué)出版社，2015.

【通聯(lián)編輯：王力】