ElasticSearch 6.3版本(ES)查询关键字不拆词查询:类似mysql 的 like 语句.
mysql的sql语法类似如下,采用大量like和locate语法,进行模糊查询,导致查询一个需要8秒多.通过ES优化后,总的查询在1秒内完成,提升8倍以上.
SELECT
ecc.id,
ecc.customer_code AS customerCode,
...,
ei.industry_name AS industryName
FROM
ec_cust_customer ecc
LEFT JOIN ec_industry ei ON ecc.CUSTOMER_TRADE = ei.id
LEFT JOIN ec_cust_follow_info ecfi ON ecc.id = ecfi.CUST_ID
AND ecfi.id = ( SELECT fi.id FROM ec_cust_follow_info fi WHERE fi.CUST_ID = ecc.id ORDER BY fi.id DESC LIMIT 1 )
LEFT JOIN ec_cust_linkman ecl ON ecl.cust_id = ecc.ID
AND ecl.id = ( SELECT ecll.id FROM ec_cust_linkman ecll WHERE ecll.CUST_ID = ecc.id ORDER BY ecll.id DESC LIMIT 1 )
WHERE
ecc.STATUS = 0
AND deleted = 0
AND (
ecc.customer_code IN (
SELECT
r.customer_code
FROM
ec_cust_change_records r
WHERE
LOCATE( '名称', r.project_name ) > 0
AND ( LOCATE( '铁城', r.before_content ) > 0 OR LOCATE( '铁城', r.