基于MySQL性能調(diào)優(yōu)的推薦系統(tǒng)優(yōu)化設計

焦 宇，李 民，王 歡，余開朝

（昆明理工大學機電工程學院，云南昆明 650500）

0 引言

隨著大數(shù)據(jù)時代的到來，人們每天都會瀏覽成百上千的數(shù)據(jù)信息，而一些常用的例如淘寶、美團、大眾點評等具備推薦功能的軟件，其中的數(shù)據(jù)量更是每天都以驚人的速度增長［1］。一個推薦系統(tǒng)中的數(shù)據(jù)主要存儲在MySQL、Redis、Oracle、SqlServer 等一系列數(shù)據(jù)庫中，大數(shù)據(jù)的出現(xiàn)使推薦系統(tǒng)得到了迅速發(fā)展，也使人們在進行網(wǎng)購、出行等活動時可通過推薦系統(tǒng)更好地進行選擇［2］。然而，推薦系統(tǒng)中數(shù)據(jù)量增加的同時也面臨著一系列問題，例如數(shù)據(jù)量過大、數(shù)據(jù)庫性能不足導致用戶提取數(shù)據(jù)信息效率低下等，從而影響用戶體驗［3-5］。在大量人群同時訪問數(shù)據(jù)庫獲取數(shù)據(jù)時，MySQL 數(shù)據(jù)庫的承受壓力有限，甚至會出現(xiàn)宕機狀態(tài)。因此，對推薦系統(tǒng)中的數(shù)據(jù)庫性能進行調(diào)優(yōu)，使其具有更好的性能對于人們的現(xiàn)實生活具有重要意義。

1 相關研究

“數(shù)據(jù)庫”概念最早在20 世紀60 年代被提出，目前已經(jīng)歷了半個多世紀的發(fā)展，從早期的數(shù)據(jù)庫到流行至今的關系型數(shù)據(jù)庫，發(fā)展異常迅猛［6］。在信息技術日益成熟的今天，程序員開發(fā)了各式各樣的系統(tǒng)，然而幾乎每一個系統(tǒng)都離不開數(shù)據(jù)庫。在大數(shù)據(jù)背景下的信息時代，人們接觸的信息量與日俱增。為了獲取更加個性化的信息，推薦系統(tǒng)應運而生。在推薦系統(tǒng)中，數(shù)據(jù)庫扮演著重要角色，系統(tǒng)中的各類數(shù)據(jù)信息都存放在數(shù)據(jù)庫中。因此，如何提升數(shù)據(jù)庫性能一直是熱門研究話題。

在國內(nèi)外學者的研究中，宋永鵬［7］針對數(shù)據(jù)庫查詢性能，以天氣圖片資料為環(huán)境背景，對表中的sql 查詢語句進行優(yōu)化，同時通過“id”限定查詢，提高了數(shù)據(jù)庫響應速度；王宏偉［8］通過對MySQL 數(shù)據(jù)庫分區(qū)技術進行研究，提高了數(shù)據(jù)庫的穩(wěn)定性，保障了短信平臺的穩(wěn)定運行；陳素芳［9］從SQL Server 數(shù)據(jù)庫的基本特點與功能入手，淺析了數(shù)據(jù)庫性能的優(yōu)化方法；石怡［10］通過分析SQL 查詢語句實現(xiàn)過程，得出影響查詢執(zhí)行效率的客觀因素，并且提出了幾種可行的性能優(yōu)化方法，保證了SQL 語句執(zhí)行的正確性與執(zhí)行效率；Aponso 等［11］通過遺傳算法策略執(zhí)行查詢語句，縮短了查詢響應時間，提高了準確性；王寧［12］提出一些改進的數(shù)據(jù)庫查詢優(yōu)化算法，提升了分布式數(shù)據(jù)庫系統(tǒng)的查詢效率；黃建軍等［13］研究了Oracle 數(shù)據(jù)庫，首先討論SQL 語言優(yōu)化的必要性，其次對設計原則進行分析，最后提出幾種SQL 優(yōu)化策略；Gerardo［14］對數(shù)據(jù)庫執(zhí)行的等待時間進行分析，并對其進行改進，減少了數(shù)據(jù)查詢時間；聶小雄［15］通過改進遺傳算法，將其運用于分布式部署的數(shù)據(jù)庫架構中，提高了系統(tǒng)查詢效率和運行效能；字鳳芹等［16］將圖數(shù)據(jù)庫與電影推薦系統(tǒng)相結(jié)合，通過KNN 算法有效解決了信息過載、難以查詢等問題。以上研究大多通過SQL 語句查詢優(yōu)化或算法優(yōu)化的方式提高數(shù)據(jù)庫性能，但針對大量用戶訪問時的數(shù)據(jù)庫壓力問題以及高并發(fā)情況下的數(shù)據(jù)庫響應性能問題，學者們并沒有作出較為有效的改善。

針對以上不足，本文對MySQL 數(shù)據(jù)庫源碼進行深入研究，在了解其執(zhí)行原理的基礎上，對其源碼進行修改與優(yōu)化，并將數(shù)據(jù)庫從單機部署方式改成分布式（一主一從）部署方式部署到虛擬機中，最后結(jié)合本地部署的推薦系統(tǒng)以及其中的數(shù)據(jù)集，運用壓測工具進行實驗對比。實驗結(jié)果表明，優(yōu)化后的數(shù)據(jù)庫提高了數(shù)據(jù)響應能力，抗并發(fā)能力也得到了一定程度提升。

2 關鍵技術分析

2.1 MySQL數(shù)據(jù)庫

數(shù)據(jù)庫是用來管理組織數(shù)據(jù)的倉庫，隨著現(xiàn)代網(wǎng)絡和信息技術的發(fā)展，數(shù)據(jù)庫已轉(zhuǎn)變成用戶所迫切需要的數(shù)據(jù)管理工具。其中，MySQL 數(shù)據(jù)庫是一款高安全性、高效率且可跨平臺的數(shù)據(jù)庫系統(tǒng)，同時可與PHP、Java 等主流編程語言結(jié)合使用，具有很強的兼容性［17］。本文推薦系統(tǒng)的數(shù)據(jù)存儲在MySQL 數(shù)據(jù)庫中，由于其具有安全性高及存儲容量大的特點，因此成為支撐推薦系統(tǒng)數(shù)據(jù)的不二選擇。本文推薦系統(tǒng)的數(shù)據(jù)庫設計表包括category（類目）、recommend（推薦）、user（用戶）、seller（賣家）與shop（門店）。推薦系統(tǒng)數(shù)據(jù)庫設計如圖1所示。

Fig.1 Recommended system database design圖1 推薦系統(tǒng)數(shù)據(jù)庫設計

2.2 數(shù)據(jù)庫主從部署模式

數(shù)據(jù)庫主從部署模式通常是將“寫”和“讀”操作分別對應到主數(shù)據(jù)庫和從數(shù)據(jù)庫中，主服務器負責“寫”操作，從服務器負責“讀”操作，并使主服務器數(shù)據(jù)與從服務器數(shù)據(jù)實現(xiàn)同步更新［18］。主從模式通常分為一主一從、一主多從以及多主多從等設計模式，隨著其搭建的復雜程度逐漸增大，相應的穩(wěn)定性也逐漸提高。主從模式的出現(xiàn)可更好地分擔數(shù)據(jù)庫壓力，將“讀”“寫”操作分開的設計方式有效提高了數(shù)據(jù)庫性能。對于一個數(shù)據(jù)量龐大的推薦系統(tǒng)來說，該部署模式使整個系統(tǒng)性能得到有效改善。

3 數(shù)據(jù)庫性能調(diào)優(yōu)以及主從部署

3.1 數(shù)據(jù)庫執(zhí)行原理解析

對數(shù)據(jù)庫執(zhí)行原理進行分析：以一個client 服務器和一個MySQL 客戶端為例，通過tcp connect 進行連接。產(chǎn)生一個socket 進行連接，有socket 之后才可以進行例如select/insert 等操作。若要寫一個事務，需要先發(fā)一個start transaction 命令來開啟事務，對應的sql 語句最終會反映到執(zhí)行引擎中。MySQL 可以保證事務ACID 的特性，不至于在事務提交之后因為斷電而丟失。原因在于MySQL 的InnoDB具有WAL 機制，執(zhí)行引擎進行所有操作之前，先不操作數(shù)據(jù)，而是先記錄日志（undo/redo）。redo 相當于重做日志，例如用戶寫了一條insert 語句，對應的redo 寫了一條insert，若過程掉電，則可以重新做；undo 為事務回滾的反操作，可將用戶上一步操作對程序造成的改動恢復到改動之前。寫完日志后再操作數(shù)據(jù)，執(zhí)行change data 操作。一旦對應數(shù)據(jù)被改變，寫完日志后，作對應事務的commit 操作，將事務真正提交上去。單機數(shù)據(jù)庫執(zhí)行原理如圖2所示。

Fig.2 Schematic of stand-alone database execution圖2 單機數(shù)據(jù)庫執(zhí)行原理

3.2 數(shù)據(jù)庫配置參數(shù)優(yōu)化

接下來需要對數(shù)據(jù)庫配置文件中的一些源碼進行修改優(yōu)化，并進行解析，具體解析與修改內(nèi)容如下：

（1）max_connection=1 000。此行代碼是指socket 端連接客戶端的最大數(shù)量，MySQL 默認設置為200，這顯然對于一個推薦系統(tǒng)而言是遠遠不夠的。因此，將其設置為1 000，該數(shù)值對于后面的壓測實驗以及聯(lián)系本地部署的推薦系統(tǒng)而言都是十分可行的數(shù)值。

（2）innodb_file_per_table=1。盡管部署的推薦系統(tǒng)中只有5 張表，但若今后數(shù)據(jù)庫中表數(shù)量以及數(shù)據(jù)量增加，所有數(shù)據(jù)都存儲在datafile 中，此時數(shù)據(jù)庫尋址會變得異常困難。這行代碼是指使用Innodb，保證其對應database 里每一個table作為一張表，可大大減少尋址難度。

（3）innodb_buffer_pool_size=1G（結(jié)合服務器實際情況修改）。Databuffer 的作用在于當寫入數(shù)據(jù)時，先寫入對應的buffer 區(qū)。若該區(qū)域的內(nèi)存足夠大，則命中緩存的概率將足夠高。進行“讀”操作時，若對應主鍵在該區(qū)域中，則可直接讀取。

（4）innodb_log_file_size=256M；innodb_log_buffer_size=16M（結(jié)合服務器實際情況修改）。前者是日志內(nèi)容大小，后者是日志buffer大小。當事務不斷向undo/redo 中進行寫入操作時，文件內(nèi)存會不斷變大。當其內(nèi)存超過256M 時，此時文件有一個重命名動作，會把該文件變成一個歷史文件，同時再開一個日志文件。在此間隙內(nèi)，對應的log 無法進行flush 操作。同樣，對應的write-log-ahead 操作也無法執(zhí)行，相當于client 端的sql 語句無法實現(xiàn)，這對于用戶而言十分不友好，此時buffer 區(qū)域即發(fā)揮了作用。本文將其修改為大小為16M 的緩沖內(nèi)存，即便在日志發(fā)生切換時仍可接受16M 的緩沖作對應操作，再向?qū)男挛募鱢lush操作，有效避免了暫時性的數(shù)據(jù)庫宕機而產(chǎn)生的不良影響。

（5）innodb_flush_log_at_trx_commit=2。需要放在［My-SQLd_safe］節(jié)點下進行配置，默認配置為“1”，現(xiàn)將其數(shù)值改為“2”。修改原因如下：數(shù)值為“1”意味著每次事務一旦提交，對應地會將logfile 的buffer 區(qū)刷到磁盤中，只要事務提交就立馬刷盤，但這樣可能會發(fā)生意外。例如客戶端發(fā)起commit 操作，也進行了刷盤操作，但成功之后response給客戶端時網(wǎng)絡斷線，這樣一來客戶會認為事務是失敗的，然而實際上已經(jīng)成功執(zhí)行。當數(shù)值為“0”時，writeahead-log 操作全部寫到buffer 中，MySQL 借助于一個輪詢的時間，每隔1s 將buffer 中的內(nèi)容flush 到日志中，使對應client 端的性能得到提高。但是如果遇到操作后下一秒MySQL 出現(xiàn)宕機的情況，其對應的業(yè)務丟失就是那1s的數(shù)據(jù)丟失，性能安全性無法得到保證。將數(shù)值改為“2”后，flush 將會變?yōu)閣rite 和flush 兩步操作。write 相當于將對應文件數(shù)據(jù)寫到操作系統(tǒng)對這個文件系統(tǒng)的緩沖池內(nèi)，是硬件級別的緩沖，此時對應的write 級別操作雖然數(shù)據(jù)沒有進入磁盤，但已被操作系統(tǒng)的內(nèi)核進行管理。“2”代表client端發(fā)起commit 后寫到buffer 上，再去調(diào)用write 方法，而不調(diào)用flush 方法。對應數(shù)值完全在操作系統(tǒng)的緩存池中后，MySQL 數(shù)據(jù)庫每隔1s 再調(diào)用flush 操作。對于Linux 操作系統(tǒng)而言，只要操作系統(tǒng)不宕機，對應內(nèi)核級別的buffer 遲早會在關機前被flush 到。在這1s 時間內(nèi)，就算MySQL 數(shù)據(jù)庫宕機，只要系統(tǒng)仍在運行中，對應數(shù)據(jù)也不會丟失。相比于前兩種情況，這一種狀態(tài)更加安全。

之前針對log 及buffer 級別作出優(yōu)化后，現(xiàn)在針對datafile 級別仍可作出優(yōu)化，修改代碼為：innodb_data_file_path=ibdata1：1G；ibdata2：1G；ibdata3：1G：auto extend。以推薦系統(tǒng)中的user 表為例，該配置代表user 表的數(shù)據(jù)量很大，但當其達到1G 內(nèi)存時，會將對應數(shù)據(jù)存放在重新創(chuàng)建的一個名為ibdata1 的文件中。同理，當ibdata1 文件大小達到1G 時，會以同樣的方式繼續(xù)創(chuàng)建MySQL 的文件分區(qū)，有效對datafile 級別進行了優(yōu)化。

3.3 數(shù)據(jù)庫主從模式搭建

優(yōu)化完單機狀態(tài)下MySQL 的性能后，為了支撐線上業(yè)務需求，仍需要對數(shù)據(jù)庫作進一步優(yōu)化。接下來進行數(shù)據(jù)庫分布式部署，即主從模式搭建。主從模式搭建可實現(xiàn)讀寫分離。在數(shù)據(jù)庫中開啟bin_log，設置主從同步賬號，配置主從同步。Binarylog 是一個二進制log 文件，事務提交后，所有寫操作都會被執(zhí)行引擎引入bin_log 中，之后bin_log 記錄這臺機器的所有MySQL 數(shù)據(jù)，此時變成主數(shù)據(jù)庫（masterMySQL）。對應有一個從數(shù)據(jù)庫（slaveMySQL），在主數(shù)據(jù)庫上開一個賬號，賦予密碼，此時對應的slave-MySQL 通過唯一權限連接到bin_log 中，從主數(shù)據(jù)庫的bin_log 中讀取所有數(shù)據(jù)change，并且進行從數(shù)據(jù)庫變更。由于只在主數(shù)據(jù)庫中執(zhí)行“寫”操作，在從數(shù)據(jù)庫中執(zhí)行“讀”操作，使負載被均衡分配，可極大地緩解數(shù)據(jù)庫壓力。本文設計一主一從的部署模式，該模式下的執(zhí)行原理圖如3所示。

Fig.3 Schematic of master-slave database execution圖3 主從數(shù)據(jù)庫執(zhí)行原理

3.4 項目啟動策略

本文將數(shù)據(jù)庫主從模式部署到多臺虛擬機Linux 系統(tǒng)中，開啟訪問權限和防火墻，運用Xshell 實現(xiàn)本地Windows與Linux 系統(tǒng)的通信連接，從而可對項目進行運行操作。項目設計如圖4所示。

Fig.4 Project design圖4 項目設計

4 項目運行及實驗

4.1 實驗設置

在本地電腦中開啟2 臺Vmware 虛擬機，每臺虛擬機設置4 核4G 的配置，將數(shù)據(jù)庫安裝至兩臺服務器中。服務器靜態(tài)IP地址分別設置為192.168.157.150 和192.168.157.151，開啟兩臺服務器防火墻以便于本地服務器接入端口，將192.168.157.150 服務器中的MySQL 設置為主數(shù)據(jù)庫。首先打開MySQL 文件夾中的配置文件進行參數(shù)優(yōu)化配置，具體配置內(nèi)容如上文所述；然后在數(shù)據(jù)庫中開啟bin_log，設置主從同步賬號，并且配置兩個數(shù)據(jù)庫的主從節(jié)點等信息；設置完成后啟動兩臺數(shù)據(jù)庫，同時將本地啟動的推薦系統(tǒng)連接到數(shù)據(jù)庫服務器所開啟的端口上。通過測試，項目可正常運行，部署調(diào)試完成后，通過測試工具進行壓力測試。

本次測試主要驗證經(jīng)過數(shù)據(jù)庫優(yōu)化后推薦系統(tǒng)的數(shù)據(jù)響應能力及抗并發(fā)能力，采用Jmeter 工具進行測試。實驗測試環(huán)境為：i7-10875H/16G 內(nèi)存、Windows10 系統(tǒng)、Centos7.4、JDK1.8、Tomcat8.5、MySQL5.7。

4.2 壓測實驗結(jié)果

Jmeter 是基于Java 的壓力測試工具，可對服務器進行壓力和性能測試，也可通過JDBC 對數(shù)據(jù)庫進行測試［19-20］。在工具中設置訪問請求http，訪問路徑為：localhost：8010/static/index.html。該路徑直接提取數(shù)據(jù)庫shop 表中的內(nèi)容，并將數(shù)據(jù)返回給前端頁面，展示給用戶。其中，shop 表中共有1 500 個商家店面信息。分別設置測試用戶為500、600、700 并發(fā)量，啟動線程時間為10s，循環(huán)次數(shù)為10 次進行壓力測試，通過觀察數(shù)據(jù)響應時間平均值、中位數(shù)、90%線位、錯誤率及吞吐量對比優(yōu)化前后的數(shù)據(jù)庫性能。圖5-圖7分別是500、600、700并發(fā)量下的測試結(jié)果。

Fig.5 500 concurrency comparison results圖5 500并發(fā)量對比結(jié)果

Fig.6 600 concurrency comparison results圖6 600并發(fā)量對比結(jié)果

Fig.7 700 concurrency comparison results圖7 700并發(fā)量對比結(jié)果

實驗結(jié)果表明，與未對數(shù)據(jù)庫進行性能優(yōu)化的系統(tǒng)相比，優(yōu)化后的系統(tǒng)在響應時間方面有了明顯改善，且數(shù)據(jù)庫承受的并發(fā)能力有一定提高。在并發(fā)量為500 的情況下，吞吐量增加了123/sec、平均響應時間減少了44ms；在并發(fā)量為600 的情況下，吞吐量增加了194/sec、平均響應時間減少了63ms；在并發(fā)量為700 的情況下，吞吐量增加了238/sec、平均響應時間減少了109ms。在錯誤率方面，當并發(fā)量為500、600 時錯誤率都為0；當并發(fā)量為700 時，優(yōu)化后系統(tǒng)的錯誤率為0，原因主要在于數(shù)據(jù)庫主從部署的存在降低了數(shù)據(jù)庫負載，而未進行優(yōu)化的系統(tǒng)錯誤率達到0.025%。

5 結(jié)語

本文深度解析了數(shù)據(jù)庫運行原理，從源碼層面對數(shù)據(jù)庫的部署參數(shù)進行優(yōu)化，并結(jié)合本地設計的推薦系統(tǒng)及其數(shù)據(jù)庫中的數(shù)據(jù)，采用虛擬機Linux 系統(tǒng)進行數(shù)據(jù)庫主從部署，構建了一個數(shù)據(jù)庫優(yōu)化后的推薦系統(tǒng)。壓力測試結(jié)果表明，優(yōu)化后的數(shù)據(jù)庫在不增加錯誤率的情況下，數(shù)據(jù)處理效率有一定提高，并且數(shù)據(jù)庫的抗并發(fā)能力也有一定提升。然而，本文并未采取多從多主的方式部署數(shù)據(jù)庫，對于推薦系統(tǒng)中的推薦算法也未進行優(yōu)化以提高推薦精度，因此如何進一步提升數(shù)據(jù)庫性能，以及提高推薦系統(tǒng)的推薦精準度是接下來需要深入研究的內(nèi)容。