mysql 类似整数的运算符给我比 = 运算符更好的结果

Question

这是 tblNewsToCity 上的索引:

我有这个查询:

SELECT * 
FROM   `tblnews` 
       INNER JOIN `tblnewstocity` 
               ON `tblnews`.`id` = `tblnewstocity`.`fkid` 
WHERE  `tblnewstocity`.`city` = '233' 
       AND `tblnews`.`id` != '1771' 
ORDER  BY `tblnews`.`id` DESC 
LIMIT  3 offset 0 

运行缓慢

但如果我将 tblnewstocitycity = 233 更改为:tblnewstocitycity like 233

我得到了很好的结果:

很想了解为什么？我错过了什么？为什么当第二个查询在整数上使用 like 运算符时，第一个查询运行得不如第二个查询，而它应该更慢

Answer 1

MySQL 有几个选项来执行该查询:

• 使用索引查找包含 tblnewstocity.city = '233' 的所有行，按 ID 进行连接和排序。它必须检查索引给出的所有行，因为前 3 行不必具有最大的 tblnews.id。

• 按照 order by 顺序(从末尾开始)遍历 tblnews，进行连接，并查找恰好有正确的city值。它可以在找到 3 行后停止，因为不能有更大的 tblnews.id。

这取决于您的数据，哪种方式更快。如果你有例如只有 2 行适合您的 index 条件(和连接)，第一个查询会更快，因为它只需要检查少数行，而第二个查询则必须检查整个行表要意识到只有 2 个。所有行都有 city = 233，第一个查询必须找到所有行(通过索引，但仍然是全部)，对它们进行排序并获取前 3 个，而第二个查询只会有测试前 3 行，因为它们已经排序。

现实的分布将介于这些可能性之间。 MySQL必须猜测。它猜测索引(对于=)只会给你少量的行，所以它选择了选项1。like会让MySQL更少信任索引，所以它会更喜欢第二种选择，幸运的是，第二种选择更快。但它也可能会走向相反的方向，尝试例如like 和 = 的值没有城市(好吧，根据你的 mysql 服务器版本，优化器可能会检查它并且可能不会这样做，所以也许测试它其值只会给出少量行而没有限制。

长话短说:有两种解决方案:

• 要么用straight_join tblnewstocity替换INNER JOIN tblnewstocity，这将迫使mysql采取第二种方式(但当然会有反例执行缓慢的风险)
• 或者添加适当的索引:tblnewstocity (city, fkid) 应该可以解决这个问题。您可能必须将 ORDER BY tblnews.id DESC 更改为 ORDER BY tblnewstocity.fkid DESC，这是等效的，因为 tblnewstocity.fkid = tblnews.id > 根据 join 条件，mysql 应该自己实现这一点，但你永远不知道...