mysql中文全文检索从入门到放弃
like全匹配模糊查詢不能使用索引一直是sql查詢的一個棘手的問題，那么mysql的全文檢索真的能解決這個問題嗎？
背景
最近，在工作中遇到一個查詢優化的問題，簡化的sql如下：
SELECT* FROMwxswj_nsrxx WHEREnsrmc LIKE '%東鵬%' OR nsrsbh LIKE '%東鵬%' OR shxydm LIKE '%東鵬%';問題：
1、采用了全匹配模糊查詢
2、使用了OR關鍵字
很明顯這樣的查詢是不能走索引，而由于表的數據量非常大，有500多萬數據，導致整個查詢的響應速度非常不理想。
中文全文檢索實戰
ngram分詞插使用說明：
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html添加鏈接描述
1、優化思路：
中文模糊匹配查詢，主要涉及到分詞和全文檢索，而mysql里面有一種索引類型就是全文索引FULLTEXT。所以想通過全文索引來解決mysql中全匹配模糊查詢的問題。
2、說明：
在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分詞器把中文段落預處理拆分成單詞，然后存入數據庫。
從MySQL 5.7.6開始，MySQL內置了ngram全文解析器，用來支持中文分詞。
3、查看當前數據庫版本：
select version() from dual;結果為5.7.28，支持中文全文檢索
4、全文檢索限制：
FULLTEXT indexes are created on text-based columns (CHAR, VARCHAR, or TEXT columns)
全文索引只能被創建在CHAR, VARCHAR, or TEXT的字段上。
每張表只能有一個全文檢索的索引
由多列組合而成的全文檢索的索引必須使用相同的字符集與排序規則
5、關閉查詢緩存
sql優化前，一般會關閉查詢緩存：
SHOW VARIABLES LIKE ‘query_cache%’;
set global query_cache_size=0;
set global query_cache_type=0;
SHOW VARIABLES LIKE ‘query_cache%’;
6、建立全文索引
ALTER TABLE `wxswj`.`wxswj_nsrxx` ADD FULLTEXT INDEX `ft_index`(`nsrmc`,`nsrsbh`,`shxydm`) WITH PARSER ngram;7、使用全文索引
通過**MATCH (col1,col2,…) AGAINST (expr [search_modifier])**語句，使用全文索引。
這里使用東鵬去模糊匹配nsrmc, nsrsbh, shxydm這三個字段，任意一個字段中包含查詢關鍵字東鵬就返回對應記錄。
8、查詢執行計劃
使用了新建的組合全文檢索，ref達到const級別
9、優化效果
查詢性能提升了100多倍。
坑
目前為止，好像一切都非常美好，但很快坑就出現了。
當查詢的關鍵詞太長，就出現了異常？
問題一：FTS query exceeds result cache limit
當采用比較長的查詢條件去匹配執行查詢或甚至執行查詢計劃時，出現異常：
mysql官網中對該異常的解釋說明：
https://bugs.mysql.com/bug.php?id=86036
每個全文搜索查詢或每個線程的InnoDB全文搜索都對查詢結果進行了緩存限制，以字節為單位定義。中間和最終的InnoDB全文搜索查詢結果在內存中處理。可以使用innodb_ft_result_cache_limit設置大小限制。全文搜索查詢結果緩存可避免InnoDB全文搜索查詢結果非常大（例如，數百萬或數億行）時過多的內存消耗。 如果達到結果緩存大小限制，則返回錯誤，指示查詢超出了最大允許的內存。
推薦解決辦法：
1、增加innodb_ft_result_cache_limit的值，使其大于4G
2、優化查詢語句，限制查詢返回的記錄條數，減少來自中間結果的巨大緩存。一般通過顯示指定limit來限制。
問題二：查詢速度非常不穩定
我們通過修改innodb_ft_result_cache_limit的值，解決了緩存限制的異常問題。
當時，我們嘗試修改查詢條件時，發現查詢性能非常不穩定。
有時候查詢速度非常快，有時候甚至比不上like全匹配模塊查詢。
特別是當查詢條件非常長的時候，問題非常明顯，查詢性能完全沒有保證。
放棄
通過調研各種資料，沒有找到比較好解決方案，最后還是無奈選擇放棄。
測試語句
create table test( id int(11) not null primary key auto_increment, name varchar(100) not null comment '工商名', brand varchar(100) default null comment '品牌名', en varchar(100) default null comment '英文名', fulltext key (name,brand,en) with parser ngram )engine=innodb default charset=utf8; insert into test (name,brand,en) values ('蕪湖美的廚衛電氣制造有限公司','aa','wh'); insert into test (name,brand,en) values ('北京凡客尚品電子商務有限公司','aa','ef'); insert into test (name,brand,en) values ('凡客誠品（北京）科技有限公司','aa','dfd'); insert into test (name,brand,en) values ('瞬聯訊通科技（北京）有限公司','aa','sdfs'); insert into test (name,brand,en) values ('北京暢捷通訊有限公司','aa','wsdh'); insert into test (name,brand,en) values ('北京暢捷通支付技術有限公司','aa','df'); insert into test (name,brand,en) values ('暢捷通信息技術股份有限公司','aa','whdfgh'); insert into test (name,brand,en) values ('北京暢捷科技有限公司','aa','dgdf'); insert into test (name,brand,en) values ('中國航天工業科學技術咨詢有限公司','aa','whffgh'); insert into test (name,brand,en) values ('北京·松下彩色顯象管有限公司','aa','wfghfgh'); insert into test(name,brand,en) select name,brand,en from test; insert into test(name,brand,en) select name,brand,en from test; insert into test(name,brand,en) select name,brand,en from test; insert into test(name,brand,en) select name,brand,en from test; insert into test(name,brand,en) select name,brand,en from test; insert into test(name,brand,en) select name,brand,en from test;EXPLAIN SELECT * from test where match (name,brand,en) against ('通訊錄' IN BOOLEAN MODE) LIMIT 100;創建的測試數據總數據量為：655360
select count(*) from test;
通過加雙引號，實現確切短語搜索，不對搜索條件進行分詞匹配，我們測試下：
發現對查詢性能沒有影響。
通過實驗發現，當查詢條件越長，查詢性能越慢。
大家可以自己測試感受一下。
有關于mysql全文檢索使用好的建議，歡迎分享。
結論
本次實驗證明，mysql對全文檢索的支持有限，限制比較大，查詢性能也得不到保證，很多時候可能比不上直接使用like查詢。
幾十萬數據的小表，可以考慮玩一下。
對一些大表需要全匹配模糊查詢時，首先是和業務方商量是否可以只支持前匹配模糊查詢，其次盡可能增加其他查詢條件，另外通過limit限制匹配的記錄數。
復雜查詢下，并且一定要求對全匹配模糊查詢支持且對查詢性能有嚴格要求，那么推薦使用Elasticsearch。
關注私聊我，免費領取視頻教程。
更多精彩，關注我吧。
總結
以上是生活随笔為你收集整理的mysql中文全文检索从入门到放弃的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： php smarty使用,php-sma
- 下一篇： Autobahn实现WebSocket通