mariadb 如何在MySQL中索引文本字段或使其可搜索?

klh5stk1  于 2022-11-08  发布在  Mysql
关注(0)|答案(1)|浏览(153)

我已经在MySQL表的text字段中存储了大约700万个生物蛋白质序列(使用InnoDB存储引擎和latin1_swedish_ci排序)。
存储在MySQL中的序列是英文字母大写的简单组合。如下所示:

MSTWQVYRLLMEYCSCLDNKTPNAFAKWCSSRKIKFLQADYFRKRPKHCDEGTGRYRSIYVMKKEYLGDIVRKITN

在MySQL中选择text字段看起来很重要,因为序列的趋势是从最小1字节到最大无限/未知字节(在存储的700万条记录中,最大大小为23089,但理想情况下,它将超过未来即将到来的记录)。
MySQL中varchar或text的最大键大小为767。这意味着只能对最左边的第一个767字节进行索引。
LIKE运算符对此索引无效,无法有效地从整个text字段检索substring
那么,有没有办法对整个text字段进行索引,以便高效地搜索其中的子字符串呢?

vecaoik1

vecaoik11#

您希望在alphabetic protein sequences中存储并搜索子字符串。
MySQL / MariaDB的搜索功能(LIKE '%CSCLDNKTPNAFAKW%'和FULLTEXT)不适合此应用程序,抱歉。
为什么不呢?

  • 使用LIKE '%CSCLDN%'进行搜索会慢得离谱。(LIKE字符串中的%是通配符运算符。)而且,列的前缀索引也无助于加快搜索速度。
  • FULLTEXT适用于自然语言的单词序列,而不是用于表示DNA序列的长字符串。

PostgreSQL RDBMS有一个名为trigram indexes的特性。当你使用它的时候,你可以用LIKE '%ACTG%'风格的过滤器搜索长的TEXT对象,性能相当不错。你可以用这样的方式声明你的三元组索引。

CREATE INDEX CONCURRENTLY table1_text ON table1
       USING GIN (textcolumn, gin_trgm_ops)
       INCLUDE (col1, col2, col3);

但在此之前,您必须切换到使用PostgreSQL。

相关问题