이름에 "xiao"가 포함된 사용자를 쿼리하는 것과 같은 퍼지 쿼리는 "%xiao%"와 같은 일반적인 작성 방법으로 MySQL의 전체 테이블을 스캔하며 데이터 양은 적고 전체 테이블 스캔도 매우 빠르며, 데이터가 증가하면 속도가 느려지고 ES가 매우 무거워집니다. 이 기사에서는 유사 퍼지 일치 쿼리(MySQL 전체 텍스트 인덱스)를 느리게 만드는 솔루션을 소개합니다.
단어를 쿼리하려면 퍼지 일치가 필요합니다
select * from t_phrase where LOCATE('chang',phrase) = 0;
select * from t_chinese_phrase where instr(phrase,'chang' ) > 0;
select * from t_chinese_phrase 여기서 '%长%'
실행 계획을 설명하고 살펴보세요
구문에 대한 색인을 구축했지만 쿼리할 때 색인이 유효하지 않습니다.
이유: mysql의 인덱스는 B+ 트리 구조입니다. InnoDB는 퍼지 쿼리 데이터로 인해 인덱스가 실패할 때 "%xx"를 사용합니다(여기서는 자세히 설명하지 않겠습니다)
쿼리 시간 측면에서 소요 시간은 90ms입니다.
현재 데이터량 : 93230(9.3W)은 이미 90ms가 필요합니다. 데이터 양이 늘어나면 이 시간은 계속해서 늘어납니다.
해결책:
데이터의 양이 많지 않은 경우 mysql의 전체 텍스트 인덱스를 사용하세요.
데이터의 양이 상대적으로 크거나 mysql의 전체 텍스트 인덱스가 기대에 미치지 못하는 경우 ES 사용을 고려해보세요
다음은 주로 MySQL 전체 텍스트 인덱스 관련입니다.
MySQL5.6.24의 InnoDB 엔진에는 전체 텍스트 인덱싱도 추가되었습니다.
2. 전체 텍스트 색인
create table 表名( 字段名1, 字段名2, 字段名3, 字段名4, FULLTEXT full_index_name (字段名) )ENGINE=InnoDB;
테이블 이름(필드 이름)에 전체 텍스트 인덱스 인덱스 이름 생성;</code ><p></p>eg:<p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:sql;toolbar:false">create table t_word
(
id int unsigned auto_increment comment &#39;自增id&#39; primary key,
uid char(32) not null comment &#39;32位唯一id&#39;,
word varchar(256) null comment &#39;英文单词&#39;,
translate varchar(256) null
);
create fulltext index full_idx_translate
on t_word (translate);
create fulltext index full_idx_word
on t_word (word);
INSERT INTO t_word (id, uid, word, translate) VALUES (1, &#39;9d592499c65648b0a9519206688ef3f9&#39;, &#39;lion&#39;, &#39;狮子&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (2, &#39;ce26ac4239514bc6af481bcb1d9b67df&#39;, &#39;panda&#39;, &#39;熊猫&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (3, &#39;a7d6042853c44904b68275daafb44702&#39;, &#39;tiger&#39;, &#39;老虎&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (4, &#39;f13bd0a8ecea44fc9ade1625eeb4cc3c&#39;, &#39;goat&#39;, &#39;山羊&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (5, &#39;27d5cbfc93a046388d712085e567474f&#39;, &#39;sheep&#39;, &#39;绵羊&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (6, &#39;ed35df138cf348aa937781be8ee21cbf&#39;, &#39;lamb&#39;, &#39;羊羔&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (7, &#39;fba5861d9527440990276e999f47ef8f&#39;, &#39;buffalo&#39;, &#39;水牛&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (8, &#39;3a72e76f210841b1939fff0d3d721375&#39;, &#39;bull&#39;, &#39;公牛&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (9, &#39;272e0b28ea7a48248a86f17533bf9943&#39;, &#39;cow&#39;, &#39;母牛&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (10, &#39;47127adface54e418e4c1b9980af6d16&#39;, &#39;calf&#39;, &#39;小牛&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (11, &#39;10592499c65648b0a9519206688ef3f9&#39;, &#39;little lion&#39;, &#39;小狮子&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (12, &#39;1bf095110b634a01bee5b31c5ee7ee0c&#39;, &#39;little cow&#39;, &#39;母牛&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (13, &#39;4813e588cde54c30bd65bfdbb243ad1f&#39;, &#39;little calf&#39;, &#39;小小牛&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (14, &#39;5e377e281ad344048b6938a638b78ccb&#39;, &#39;little bull&#39;, &#39;小公牛&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (15, &#39;2855ad0da2964c7682c178eb8271f13d&#39;, &#39;little buffalo&#39;, &#39;小水牛&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (16, &#39;72f24c9a77644d57a36f3bdf2b8116b0&#39;, &#39;little lamb&#39;, &#39;小羊羔&#39;);
INSERT INTO t_word (id, uid, word, translate) VALUES (17, &#39;2d592499c65648b0a9519206688ef3f9&#39;, &#39;I&#39;&#39;m a big lion&#39;, &#39;我是一只大狮子&#39;);</pre><div class="contentsignin">로그인 후 복사</div></div> </p>3. 전체 텍스트 인덱스 삭제<p><code>create fulltext index 索引名称 on 表名(字段名)；eg:
MATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION
}3、删除全文索引
alter table 表名 drop index 索引名; 테이블 이름 변경 인덱스 인덱스 이름 삭제;
구문
// 默认是使用 in natural language mode select * from t_word where match(word) against ('lion'); // 或者 显示写 select * from t_word where match(word) against ('lion' in natural language mode);
4.1을 사용합니다. 자연어 모드
자연어 모드는 MySQL default 전체 텍스트 검색 모드입니다. 자연어 모드는 연산자를 사용할 수 없으며, 나타나야 하거나 나타나지 않아야 하는 키워드와 같은 복잡한 쿼리를 지정할 수 없습니다.
// 默认是使用 in natural language mode select * from t_word where match(word) against ('lion'); // 或者 显示写 select * from t_word where match(word) against ('lion' in natural language mode);
결과는 다음과 같습니다.
4.2 IN BOOLEAN MODE
BOOLEAN 모드연산자를 사용할 수 있습니다. 키워드의 가중치가 높거나 낮습니다. 부울 모드를 사용하는 것이 좋습니다
| Description | |
|---|---|
| 기본값은 | |
| include라는 단어를 포함합니다. 존재해야 합니다. | |
| 제외, 단어가 나오지 않아야 합니다. | |
| 순위 값을 포함하고 높이면 쿼리 결과가 높아집니다 | |
| 순위 값을 포함하고 낮추면 쿼리 결과가 낮아집니다 | |
| 단어를 하위 표현식으로 그룹화합니다(그룹으로 포함, 제외, 순위 지정 등 가능). | |
| 부정어의 순위값입니다. | |
| 와일드카드는 단어 끝에 있습니다. | |
| 문구를 정의합니다(전체 문구가 일치하여 포함하거나 제외되는 개별 단어 목록과 반대). |
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
