前言
本文的測試是基于740w條測試數(shù)據(jù)進(jìn)行的，只討論like模糊查詢的優(yōu)化方案。其他SQL優(yōu)化可參考：
SQL優(yōu)化的幾種方式
查詢開頭是“今天不開心”的聊天記錄，是可以走索引的。
select * from message_1 where content like "今天不開心%”;
查詢包含“今天不開心”的聊天記錄，是不能走索引的。
select * from message_1 where content like "%今天不開心%";
咱們主要優(yōu)化的是第二種情況，我本人測試查詢耗時是在9秒。
優(yōu)化方案
對于查詢包含某個關(guān)鍵詞的需求，從業(yè)務(wù)上來說應(yīng)盡量避免這種不合理的需求。
但是實(shí)際使用中，總有些類似需求避免不掉模糊查詢，就可以采取下列優(yōu)化方式。
- 稍微優(yōu)化
select * from message_1 where instr(content, "今天不開心") > 0;
select * from message_1 where locate("今天不開心", content) > 0;
這個方法優(yōu)化效果有限，這兩種方法耗時相差不多，比不優(yōu)化要快上2~3秒。
我還測試了一些其他的一些情況，這種優(yōu)化方式，在某些情況下會比優(yōu)化前還要慢，由此可見這種方式是有坑的。
比如優(yōu)化前：
select content from message_1 where content like "%今天不開心%";
優(yōu)化后：
select content from message_1 where instr(content, "今天不開心") > 0;
select content from message_1 where locate("今天不開心", content) > 0;
這種情況，優(yōu)化后比不優(yōu)化要慢上2秒左右。。。。
- 大幅度優(yōu)化
select * from message_1 where content in
(select content from message_1 where content like "%今天不開心%");
這種方法，能將查詢優(yōu)化至3秒左右，優(yōu)化效果已經(jīng)很明顯。
優(yōu)化原理：用索引全掃描取代表的全掃描。因?yàn)樗饕珤呙璧拇鷥r(jià)是全表掃描的1/N （即索引塊數(shù)與數(shù)據(jù)塊數(shù)的比例），表數(shù)據(jù)越多，優(yōu)化效果越明顯。
優(yōu)化后的sql語句，根據(jù)索引再回表的代價(jià)要看符合條件的記錄數(shù)多少：如果in子查詢返回的記錄數(shù)很少，那么優(yōu)化的效果就相當(dāng)于效率提高了N倍；如果in子查詢返回的記錄數(shù)較多，兩種SQL的性能區(qū)別就不是很明顯了。
- 根本優(yōu)化
使用ClickHouse 或者 Elasticsearch 同步數(shù)據(jù)庫。
這兩種方式可以從根本上解決模糊查詢的高延時，但是需要引入一套新的系統(tǒng)，代價(jià)還是不小的。文章來源：http://www.zghlxwxcb.cn/news/detail-401278.html
二者的對比可參考：
Elasticsearch和Clickhouse基本查詢對比
ClickHouse 與es比較文章來源地址http://www.zghlxwxcb.cn/news/detail-401278.html
到了這里，關(guān)于mysql 模糊查詢like優(yōu)化方案（親測）的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
