如何加快涉及 varchar 列上"like"条件的 MySQL 查询？

Question

我有一个表格，其中有一列varchar，其中包含股票市场行情。这些值可以将证券交易所附加到实际代码的前面，但有些则没有。例如：

NYSE:MSFT
NYSE:AAPL
AMT:STZ
^SPX
JMAT

这些都是有效的股票代码。现在，当有人搜索股票代码时，查询如下所示：

select * from stocks where ticker like '%'.$string.'%'

该表在生产中将有超过 100k 个条目。我想通过索引列来解决这个潜在的问题，但根据 MySQL 文档，这似乎是不可能的，无论如何都不适用于这样的查询。

文档指出varcharindex是可能的，但在like查询中，如果搜索字符串前有通配符，则不起作用。而且我确实需要在搜索字符串之前使用通配符。

是否有其他选项可以加快此查询的速度？

Answer 1

如果要加快查询速度，则需要避免与以%开头的模式匹配。为此，您需要添加一些冗余，以将第二个值保留在单独的列中。

因此，您的表应如下所示：

full_ticker  ticker
-----------  ------
NYSE:MSFT    MSFT
NYSE:AAPL    AAPL
AMT:STZ      STZ
^SPX         ^SPX
JMAT         JMAT

简单的字符串操作将生成第二列(新(列;对于 100k 行，这将不需要时间。

现在，您可以创建两个索引，每列一个：

create index ix1 on stocks (full_ticker);
create index ix2 on stocks (ticker);

最后，当搜索带有冒号时，请使用第一个索引：

select * from stocks where full_ticker like $string.'%'

如果搜索没有冒号，则使用第二个索引：

select * from stocks where ticker like $string.'%'

请注意，这两个搜索都避免在模式开头使用%。这样，您的查询将使用索引并且速度极快。

最后一点：另一种选择是将第二个冗余列创建为"虚拟列";虚拟列实际上可以编制索引，以产生与我在这里详述的相同的体验。只是一个虽然。

Answer 2

就像你说的，不可能将索引与使用"%wildcard%"结合使用。 可以使用称为：全文搜索 (FTS( 的功能，与使用 LIKE 运算符相比，该功能可以提高搜索性能。

一些示例性能分析在这里

有很多关于FTS的讨论，当然你可以在StackOverflow上阅读。