Fuzzy-Abfrage, z. B. Abfrage von Benutzern, deren Name „xiao“ enthält, die übliche Schreibweise ist „% xiao%“, die gesamte Tabelle in MySQL wird gescannt, die Datenmenge ist gering und die vollständige Der Tabellenscan ist auch sehr schnell, wenn die Datenmenge zunimmt, und ES wird sehr schwer. In diesem Artikel wird Ihnen die Lösung zur Verlangsamung von Fuzzy-Matching-Abfragen vorgestellt – der MySQL-Volltextindex.
Benötigen Sie Fuzzy-Matching, um ein Wort abzufragen
select * from t_phrase where LOCATE('chang',phrase) = 0;
select * from t_chinese_phrase where instr(phrase,'chang' ) > 0;
select * from t_chinese_phrase where Phrase like '%长%'
explain und werfen Sie einen Blick auf den Ausführungsplan
Aus den Erklärungsergebnissen geht hervor, dass wir Ich habe einen Index für die Phrase erstellt, aber bei der Abfrage ist der Index ungültig.
Grund: Der Index von MySQL ist eine B+-Baumstruktur. Die Verwendung von „%xx“ durch InnoDB führt dazu, dass der Index fehlschlägt (ich werde hier nicht auf Details eingehen)
Aus Sicht der Abfragezeit beträgt der Zeitaufwand: 90 ms
Aktuelles Datenvolumen: 93230 (9,3 W) benötigt bereits 90 ms. Wenn die Datenmenge zunimmt, wird diese Zeit weiter wachsen.
Lösung:
Wenn die Datenmenge nicht groß ist, verwenden Sie den Volltextindex von MySQL.
Wenn die Datenmenge relativ groß ist oder der Volltextindex von MySQL nicht den Erwartungen entspricht, können Sie dies tun Erwägen Sie die Verwendung von ES
Das Folgende bezieht sich hauptsächlich auf den MySQL-Volltextindex.
create table 表名( 字段名1, 字段名2, 字段名3, 字段名4, FULLTEXT full_index_name (字段名) )ENGINE=InnoDB;
Tabelle ändern, Tabellenname löschen, Indexname löschen;4. Volltextindex verwendet Syntax4.1 IM NATÜRLICHEN SPRACHMODUScreate fulltext index 索引名称 on 表名(字段名)；
eg:
create table t_word
(
id int unsigned auto_increment comment '自增id' primary key,
uid char(32) not null comment '32位唯一id',
word varchar(256) null comment '英文单词',
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, '9d592499c65648b0a9519206688ef3f9', 'lion', '狮子');
INSERT INTO t_word (id, uid, word, translate) VALUES (2, 'ce26ac4239514bc6af481bcb1d9b67df', 'panda', '熊猫');
INSERT INTO t_word (id, uid, word, translate) VALUES (3, 'a7d6042853c44904b68275daafb44702', 'tiger', '老虎');
INSERT INTO t_word (id, uid, word, translate) VALUES (4, 'f13bd0a8ecea44fc9ade1625eeb4cc3c', 'goat', '山羊');
INSERT INTO t_word (id, uid, word, translate) VALUES (5, '27d5cbfc93a046388d712085e567474f', 'sheep', '绵羊');
INSERT INTO t_word (id, uid, word, translate) VALUES (6, 'ed35df138cf348aa937781be8ee21cbf', 'lamb', '羊羔');
INSERT INTO t_word (id, uid, word, translate) VALUES (7, 'fba5861d9527440990276e999f47ef8f', 'buffalo', '水牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (8, '3a72e76f210841b1939fff0d3d721375', 'bull', '公牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (9, '272e0b28ea7a48248a86f17533bf9943', 'cow', '母牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (10, '47127adface54e418e4c1b9980af6d16', 'calf', '小牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (11, '10592499c65648b0a9519206688ef3f9', 'little lion', '小狮子');
INSERT INTO t_word (id, uid, word, translate) VALUES (12, '1bf095110b634a01bee5b31c5ee7ee0c', 'little cow', '母牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (13, '4813e588cde54c30bd65bfdbb243ad1f', 'little calf', '小小牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (14, '5e377e281ad344048b6938a638b78ccb', 'little bull', '小公牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (15, '2855ad0da2964c7682c178eb8271f13d', 'little buffalo', '小水牛');
INSERT INTO t_word (id, uid, word, translate) VALUES (16, '72f24c9a77644d57a36f3bdf2b8116b0', 'little lamb', '小羊羔');
INSERT INTO t_word (id, uid, word, translate) VALUES (17, '2d592499c65648b0a9519206688ef3f9', 'I''m a big lion', '我是一只大狮子');3、删除全文索引
alter table 表名 drop index 索引名;
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
}4.2 IM BOOLEAN-MODUS
BOOLEAN-Modus
Sie können Operatoren verwenden, die komplexe Abfragen unterstützen können, z Die Gewichtung des Keywords ist hoch oder niedrig. 
OperatorBeschreibung
| Standardmäßig enthält das Wort | |
|---|---|
| - | |
| () | |
| ~ | |
| * | |
| "" | |
示例： // 默认是使用 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); Nach dem Login kopieren // 排除包含lion记录、查询出包含cow或者little的记录，提升包含calf单词的排名，降低包含cow记录的排名，查询出以go开头的记录 select * from t_word where match(word) against ('-lion cow little >calf <cow go*' in boolean mode) ; Nach dem Login kopieren 好像问题都解决了， 但是问题才刚开始 回到最开始的需求，我想模糊搜索 select * from t_word where match(word) against('lio' in boolean mode); Nach dem Login kopieren 预期值：把包含lion的都查询出来 实际结果：啥都没有。 全匹配查询的时候能查询出来 select * from t_word where match(translate) against('小水牛' in boolean mode); Nach dem Login kopieren 只查询部分查询不出来。如：下面只查询 "小水" 或者"水牛" 都没有数据 select * from t_word where match(translate) against('小水' in boolean mode); Nach dem Login kopieren 奇怪了，这咋没出来呢？
中文分词与全文索引InnoDB默认的全文索引parser非常合适于Latin，因为Latin是通过空格来分词的。但对于像中文，日文和韩文来说，没有这样的分隔符。一个词可以由多个字来组成，所以我们需要用不同的方式来处理。在MySQL 5.7.6中我们能使用一个新的全文索引插件来处理它们：N-gram parser。 什么是N-gram？在全文索引中，n-gram就是一段文字里面连续的n个字的序列。例如，用n-gram来对“齿轮传动”来进行分词，得到的结果如下： N=1 : '齿', '轮', '传', '动'; N=2 : '齿轮', '轮传', '传动'; N=3 : '齿轮传', '轮传动'; N=4 : '齿轮传动'; Nach dem Login kopieren 这个上面这个N是怎么去配置的？查一下目前的值show variables like '%token%'; Nach dem Login kopieren 参数解析:
修改方式方式1： 在my.cnf中修改/添加参数 [mysqld]ngram_token_size = 1 Nach dem Login kopieren 方式2： 修改启动参数 mysqld --ngram_token_size=1复制代码 Nach dem Login kopieren
实际使用初始化测试数据这里 create table t_chinese_phrase
(
id int unsigned auto_increment comment 'id'
primary key,
phrase varchar(32) not null comment '词组'
)
collate = utf8mb4_general_ci;
INSERT INTO t_chinese_phrase (id, phrase) VALUES (278911, '阿昌族');
INSERT INTO t_chinese_phrase (id, phrase) VALUES (279253, '八一南昌起义');
INSERT INTO t_chinese_phrase (id, phrase) VALUES (282316, '昌明');
INSERT INTO t_chinese_phrase (id, phrase) VALUES (282317, '昌盛');
INSERT INTO t_chinese_phrase (id, phrase) VALUES (282318, '昌言');
INSERT INTO t_chinese_phrase (id, phrase) VALUES (286534, '东昌纸');
INSERT INTO t_chinese_phrase (id, phrase) VALUES (291525, '海昌蓝');
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (346682, '繁荣昌盛');
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (282317, '昌盛');
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (287738, '繁盛');
INSERT INTO test.t_chinese_phrase (id, phrase) VALUES (287736, '繁荣');Nach dem Login kopieren 添加索引
添加索引： alter table t_chinese_phrase add fulltext ful_phrase (phrase) with parser ngram; Nach dem Login kopieren 建完索引，我们可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE和INFORMATION_SCHEMA.INNODB_FT_TABLE_TABLE来查询哪些词在全文索引里面。这是一个非常有用的调试工具。如果我们发现一个包含某个词的文档，没有如我们所期望的那样出现在查询结果中，那么这个词可能是因为某些原因不在全文索引里面。比如，它含有stopword，或者它的大小小于ngram_token_size等等。这个时候我们就可以通过查询这两个表来确认。下面是一个简单的例子： # test: 库名 t_chinese_phrase： 表名字 SET GLOBAL innodb_ft_aux_table="test/t_chinese_phrase"; # 查询分词情况 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; # 查询分词情况 select * from information_schema.innodb_ft_index_table; Nach dem Login kopieren 查询结果如下：
查询1、使用自然语言模式 NATURAL LANGUAGE MODE 查询
例如，当ngram_token_size = 1 时，(‘繁荣昌盛’)转换为(‘繁 荣 昌 盛’)。下面一个例子： SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST ('繁荣昌盛' in natural language mode) ; Nach dem Login kopieren 2、使用布尔模式（BOOLEAN MODE）查询
例如，当ngram_token_size = 1 时，(‘繁荣昌盛’)转换为(‘”繁荣昌盛“’)。下面一个例子： SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST ('繁荣昌盛' in boolean mode) ; Nach dem Login kopieren 实际使用回到我们最开始的查询需求，看看实际的效果 查询包含了“昌”的数据 SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST ('昌' IN boolean MODE) ; SELECT * FROM t_chinese_phrase WHERE MATCH (phrase) AGAINST ('昌' ) order by id asc; Nach dem Login kopieren 可以看到结果：目前“昌”在任意位置都能被查询到。 查询执行计划如下： 耗时31ms（不走索引是90ms）， 注意点1、自然语言全文索引创建索引时的字段需与查询的字段保持一致，即MATCH里的字段必须和FULLTEXT里的一模一样； 2、自然语言检索时，检索的关键字在所有数据中不能超过50%（即常见词），则不会检索出结果。可以通过布尔检索查询； 3、在mysql的stopword中的单词检索不出结果。可通过 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD Nach dem Login kopieren 查询所有的stopword。遇到这种情况，有两种解决办法： （1）stopword一般是mysql自建的，但可以通过设置ft_stopword_file变量为自定义文件，从而自己设置stopword，设置完成后需要重新创建索引。但不建议使用这种方法； （2）使用布尔索引查询。 4、小于最短长度和大于最长长度的关键词无法查出结果。可以通过设置对应的变量来改变长度限制，修改后需要重新创建索引。 myisam引擎下对应的变量名为ft_min_word_len和ft_max_word_len innodb引擎下对应的变量名为innodb_ft_min_token_size和innodb_ft_max_token_size 5、MySQL5.7.6之前的版本不支持中文，需使用第三方插件 6、全文索引只能在 InnoDB（MySQL 5.6以后） 或 MyISAM 的表上使用，并且只能用于创建 char,varchar,text 类型的列。 【相关推荐：mysql视频教程】 Das obige ist der detaillierte Inhalt vonLassen Sie uns darüber sprechen, wie der MySQL-Volltextindex das Problem langsamer Abfragen wie Fuzzy-Matching löst. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
Verwandte Etiketten：
Quelle：juejin.cn
Vorheriger Artikel：Lassen Sie uns darüber sprechen, wie GitHub MySQL hochverfügbar macht
Nächster Artikel：Grundlegende MySQL-Fallfreigabe für mehrere Tabellenabfragen
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Neueste Artikel des Autors
Aktuelle Ausgaben
Wie gruppiere und zähle ich in MySQL?
Ich versuche, eine Abfrage zu schreiben, die die Gesamtzahl der nicht gelöschten Nachricht...
Aus 2024-04-06 18:30:17
0
1
353
verwandte Themen
Mehr>
|
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
