基于MySQL不同存儲引擎下數據存儲效率研究

楊卓凡

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

關鍵詞： MySQL數據庫;存儲引擎;研究對比;性能優化

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

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

開放科學（資源服務）標識碼（OSID）：

1 序言

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

2 存儲引擎及其介紹

2.1 存儲引擎定義及原理

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

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

2.2 存儲引擎主要類別

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

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

3 存儲實驗的環境搭建及準備工作

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

（1） 軟件環境的搭建：

Windows10專業版，MySQL-8.0.23-winx64，

Navicat for MySQL，IIS6。

（2） MySQL表結構的設計與存儲過程的定義。

表定義如下：

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引擎下數據存儲實驗

4.1數據存儲實驗過程

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

4.2實驗數據結果分析

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

5 基于InnoDB引擎下數據存儲實驗

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

5.1影響存儲效率相關參數不同值的設定

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

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

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

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

5.2實驗數據結果分析

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

6結論

通過上述實驗測試結果分析，可以得出在當前軟件環境中，采用MyISAM引擎和在innodb_flush_log_at_trx_commit值為0或2時的狀態下的InnoDB存儲引擎進行數據存儲效率差別不是很大，若innodb_flush_log_at_trx_commit的值為1時，在默認狀態下的autocommit值為1時，InnoDB數據存儲引擎效率較低，且當autocommit值為0時，InnoDB由于關閉了自動提交事務和無需寫入日志文件到磁盤，因此具有高效的存儲效率。本文分析了兩種存儲引擎各自的特點，在實際中針對不同業務項目來講，各自有著不同的需求，進而需要采用恰當的存儲引擎建立合適的存儲表類型，從而可以最大程度地發揮MySQL數據庫性能優勢[8]。

參考文獻：

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

[2] 徐昂，成科揚.基于關系型數據庫的SQL檢索優化研究[J].電子設計工程，2019，27（11）：51-55.

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

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

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

[6] 黑馬程序員.MySQL數據庫原理、設計與應用[M].北京：清華大學出版社，2019.

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

[8] 張工廠.MySQL技術精粹架構、高級特性、性能優化與集群實戰[M].北京：清華大學出版社，2015.

【通聯編輯：王力】