mysql最佳化設計方案

標籤：

首先講一下項目的情境：

1：這是一個基於web的java項目，其主要功能是對一些視音頻資訊的處理跟展示，其中視音頻資料是由爬蟲進行爬取的千萬層級量的資料

2：該項目使用的是mysql5.5版本的資料庫

3：該項目有一個搜尋的功能，需要根據關鍵字在千萬條資料中模糊比對查詢出相應的資料

4：該項目可能面向的使用者會比較多，會出現大量高並發

主要涉及問題：

1：本項目對於資料的讀操作需求遠大於寫操作，而且考慮到寫操作可能出現的事務問題需要選擇innodb作為搜尋引擎。然而innodb讀取的效能遠遠低於myIsam

2：由於資料量比較大，對於搜素功能的實現時我們需要使用到模糊查詢，此時i需要使用like ‘%keywords%‘，那麼mysql的搜尋將不走索引而是進行全表搜尋，那麼每次搜尋尋找都需要消耗至少50秒的時間，這樣對效能要求是絕對不允許的

針對以上問題做出以下的解決方案：

1：對資料庫進行讀寫分離，分配1主（Master）1從（Slave）的資料庫 ，Master為可讀寫資料庫（使用innodb搜尋引擎：為了支援事務），Slave為唯讀資料庫（使用myisam資料引擎：唯讀可考慮不支援事務，而使用myisam可以提高檢索速度）。

1）讀寫分離需要注意的一點：slave中資料會有延遲，在master中資料進行寫入後slave需要一定時間後才會得到同步，所以需要我們項目對於即時性的要求不用做到絕對的精確。

2）讀寫分離使用於讀遠大於寫的情境，如果只有一台伺服器，當select很多時，update和delete會被這些select訪問中的資料堵塞，等待select結束，並發效能不高，所以進行讀寫分離還能提高並發效能。

3）讀寫分離減少了伺服器的壓力，使資料庫不那麼容易奔潰

2：在slave資料庫中考慮到全文檢索索引效能問題，使用mysql+coreseek

1）對於mysql資料庫，在5.6版本前只有myIsam搜尋引擎支援全文索引（fulltext關鍵字），5.6版本後支援innodb的全文索引。然而它們卻不支援中文的索引，這對於我們這個項目需要的中文搜尋是個很大的問題。

2）一開始我的想法是使用lucence進行中文分詞索引，但是效率跟使用方法都沒有達到理想的效果，比較lucence不是針對資料庫來的

3）之後是選擇了sphinx，然而sphinx也是不支援中文的，想要支援到中文需要自己做很多處理。

4）最後選擇了coreseek，它是中華民國的偉大開發人員們開發的支援中文分詞的，支援mysql的一個全文檢索索引的一個搜尋引擎（它是基於Sphinx的），它對於千萬層級資料的搜尋速度能達到零點幾的層級。用它結合mysql能解決問題2。

以下是流程圖：

在web中通過調用sphinx提供的api我們能夠檢索出mysql中對於的主鍵id值，然後再通過這些id值查詢資料庫（走資料庫中的索引），這樣就能大大的提升搜尋的速度了。

當然這隻是我個人的解決方案，可能有很多不足之處，希望大家幫忙糾正學習

mysql最佳化設計方案