基于MySQL性能調優的推薦系統優化設計

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

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

0 引言

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

1 相關研究

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

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

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

2 關鍵技術分析

2.1 MySQL數據庫

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

Fig.1 Recommended system database design圖1 推薦系統數據庫設計

2.2 數據庫主從部署模式

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

3 數據庫性能調優以及主從部署

3.1 數據庫執行原理解析

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

Fig.2 Schematic of stand-alone database execution圖2 單機數據庫執行原理

3.2 數據庫配置參數優化

接下來需要對數據庫配置文件中的一些源碼進行修改優化，并進行解析，具體解析與修改內容如下：

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

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

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

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

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

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

3.3 數據庫主從模式搭建

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

Fig.3 Schematic of master-slave database execution圖3 主從數據庫執行原理

3.4 項目啟動策略

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

Fig.4 Project design圖4 項目設計

4 項目運行及實驗

4.1 實驗設置

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

本次測試主要驗證經過數據庫優化后推薦系統的數據響應能力及抗并發能力，采用Jmeter 工具進行測試。實驗測試環境為：i7-10875H/16G 內存、Windows10 系統、Centos7.4、JDK1.8、Tomcat8.5、MySQL5.7。

4.2 壓測實驗結果

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

Fig.5 500 concurrency comparison results圖5 500并發量對比結果

Fig.6 600 concurrency comparison results圖6 600并發量對比結果

Fig.7 700 concurrency comparison results圖7 700并發量對比結果

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

5 結語

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