mysql - 如何加快涉及 varchar 列上“like”条件的 MySQL 查询？

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

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

这些都是有效的代码。现在，当有人搜索股票代码时，查询看起来像：

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

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

文档声明 avarchar index是可能的，但在查询中，如果搜索字符串之前like有通配符，它将不起作用。而且我确实需要在搜索字符串之前使用通配符。

是否有加快此查询的另一种选择？

标签： mysql

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

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

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.'%'

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

最后一点：另一种选择是将第二个冗余列创建为“虚拟列”；虚拟列实际上可以被索引，以产生与我在此处详述的相同体验。只是一个。