索引如何减缓类似查询的速度？

Question

这不是MySQL index slowing down query的复制，也不是Slow search by index query LIKE% MYSQL的复制。

在一个非常简单的表上，我尝试了一个非常简单的查询，无论有没有特定的索引。

表(40 000行)：

id                      int(11)     
shipping_address_id     int(11)     
billing_address_id      int(11)     
label                   varchar(100)
code                    varchar(15) 
email                   varchar(100)

查询：SELECT email FROM table WHERE email LIKE "%yahoo%"

如果没有“电子邮件”的索引，则需要0.0035s。然而，对于这个索引，它需要0.021s，这要慢7倍。

然而，相同的索引确实将等式查询速度提高了50倍(WHERE email = "me@yahoo.com")。

那么，为什么LIKE查询会受到索引的负面影响呢？

编辑错误：查询最初声明的是LIKE "yahoo"，而它应该读LIKE "%yahoo%"，我很抱歉。

为了清晰起见，编辑：表是InnoDB，我正在比较相同查询的速度。在第一种情况下(“相似”情况)，查询速度慢7倍，在比较字段上有索引。在第二种情况下("=")，查询速度比索引快50倍。

Answer 1

索引隐式地使用比较运算符解决(并加快)查询：=，<，>。如果您在email字段上有一个索引和一个LIKE查询，那么MySQL仍然需要检索所有的索引元素，并将regexp应用于它们。

如果查询是

 WHERE email LIKE 'yahoo%com'

您可以通过观察email必须介于'yahoo‘和'yahoo[’之间来加快速度(让我们假设[按字典顺序在z之后，不能出现在email字段中)。然后将查询更改为

WHERE (email BETWEEN 'yahoo' AND 'yahoo[') AND email LIKE 'yahoo%com'

这将产生一个好处，因为索引将快速地预选一组较小的行，然后regexp只对那些行运行(在一个已经可用的字段上，因为它在索引中，因此不需要查找表)。

但是在这种情况下，实际上，MySQL必须做一个完整的表扫描；只是它在索引上这样做。这样做的成本实际上可以比“真”表扫描的高出。在某些情况下，MySQL会意识到这一点，您将看到查询实际上根本不使用索引(取决于最近分析的时间、大小和其他因素)，而MySQL更喜欢使用完整的表扫描。

通过收集查询所需的数据，索引可能帮助您的另一种情况也不适用，因为您只请求email字段，这是正在进行的一种计算。因此，再一次，该指数没有产生任何业绩增长。

如果您想要一个加速这类WHERE的索引，那么您需要一个FULLTEXT索引，而不是一个“普通”索引。