如何在文本列的子字符串上创建索引

Question

我经常需要根据文本列中是否存在子字符串来筛选特定表中的记录。特别是，我需要排除包含/的记录。

我当前使用WHERE语句，例如：

WHERE table_name.text_col NOT LIKE "%/%"

我的直觉是，在每个记录的字符串中搜索这个子字符串需要很长时间(相对而言(，并且可以通过某种方式进行索引来改进。我可以创建一个新的二进制索引列，并根据文本列是否包含/来填充它，但我想知道是否有更整洁的解决方案？

我发现了这个问题，它涉及LEFT()风格的解决方案，但我不理解语法，我正在寻找能够处理字符串中任何位置的子字符串的东西。

Answer 1

您可以创建一个持久存储信息的计算列：

alter table table_name 
add column text_has_slash tinyint
generated always as (text_col like '%/%')
stored
;

或者，如果要将null值视为负值：

generated always as (coalesce(text_col like '%/%', 0))

列值通过计算并存储在表中(当值发生变化时，数据库会自动更新(。

现在您可以在查询中使用该列：

select * from table_name where not text_has_slash;

DB Fiddle演示

对预先计算的值进行筛选应该已经提高了性能。

在布尔列上创建索引并不一定有帮助，因为只有三个可能的值(0、1、null(。除非这些值分布非常不均匀，否则数据库执行完全扫描通常会更快。另一方面，如果您的搜索条件比所显示的搜索条件多，则可能希望将此列包含在多列索引中。

Answer 2

真正的问题是是否需要检查整个表，以及是否有某种方法可以通过索引限制行数。

首先，让我们决定是否会使用这样的索引。根据经验法则，如果某个索引匹配了表的20%以上，则不会使用该索引。("20"取决于月亮的相位。(逻辑是，在指数的BTree和数据的BTree之间跳跃会付出一些代价。如果没有太多行，那么这种反弹是值得的——也就是说，当索引为"0"时；"选择性"；。

因此，如果超过20%的行具有"/&"；，没有一个建议是有效的。CCD_ 6可能比CCD_；CCD_ 8可能比任何一个都慢。尽管如此，查询中的主成本将不得不查看每行。

另一方面，如果极少数行具有"/&"；，则任何预先计算的索引都将是有益的。

如果真正的测试是WHERE x LIKE '%/%' AND ...，那么我们需要查看测试的次要部分。它可能是即使对"/"可以有效地与测试的其他部分相结合。

一句话：给我们一个完整的画面，再加上一些统计数据。

Answer 3

也许LOCATE可以帮助您。

WHERE LOCATE('/', table_name.text_col) = 0 

当LOCATE返回0时，表示在字符串中找不到子字符串。更多信息，请访问https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_locate.

你要求LEFT()，这不是你想要的。此函数从字符串的开头返回子字符串。语法很简单，https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_left