基于MySQL不同存儲引擎下數(shù)據(jù)存儲效率研究

楊卓凡

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

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

中圖分類號：TP311.13? ? ? ? 文獻標識碼：A

文章編號：1009-3044（2022）21-0018-03

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

1 序言

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

2 存儲引擎及其介紹

2.1 存儲引擎定義及原理

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

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

2.2 存儲引擎主要類別

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

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

3 存儲實驗的環(huán)境搭建及準備工作

以下是基于MyISAM存儲引擎下的搭建及準備工作。

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

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

Navicat for MySQL，IIS6。

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

表定義如下：

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

存儲過程定義如下：

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ù)存儲實驗

4.1數(shù)據(jù)存儲實驗過程

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

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

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

5 基于InnoDB引擎下數(shù)據(jù)存儲實驗

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

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

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

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

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

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

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

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

6結(jié)論

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

參考文獻：

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

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

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

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

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

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

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

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

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