MySQL 索引不用于 LIKE for INT？

Question

我不知道这是错误还是功能，或者我做错了什么。我继承了一个有几十万行的 MySQL 数据库。该表包括 VARCHAR 类型的“full_name”字段和 INT 类型的“workpack”。

此表的用途之一是在人们开始填写 HTML 表单时提供自动完成功能，这在上述字段中提供。我注意到，当输入“全名”时，自动完成会出现并更新得非常快，但是当输入“workpack”的整数时，自动完成出现和更新的速度很慢，以至于几乎无法使用。

这两个字段都有索引，查询结构的简化示例如下:

SELECT distinct full_name       
FROM xx.xx
WHERE full_name LIKE 'Joe Bl%';

EXPLAIN 表明这是按预期使用索引“full_name”。

“工作包”的几乎相同的查询:

SELECT distinct workpack
FROM xx.xx
WHERE workpack LIKE '153%';

这里的 EXPLAIN 显示它没有使用索引“workpack”，即使我使用 FORCE INDEX。

因为我能看到的唯一区别是一个是 INT，一个是 VARCHAR，所以我决定通过创建表的本地副本并将“workpack”的数据类型更改为 VARCHAR 来进行试验。有效!也许对某些人来说并不那么令人惊讶，但我想知道为什么会这样。显然，我的“工作包”数据应该存储为 INT，因为它就是这样，但是为了让我的自动完成功能以合理的方式工作，我似乎需要将其更改为 VARCHAR。我意识到 LIKE 是一个字符串函数，但考虑到 LIKE 函数仍然适用于 INT，是否有逻辑上的技术原因导致它不能仅仅因为它是一个 INT 而无法使用索引？

Answer 1

索引在这里不合适，因为您正在将它转换为字符串。而且，如果您使用数字列表的值或字符串表示对数字列表进行排序，结果将有所不同。以 125 和 1234 为例。按值排序时，您会得到:

• 125
• 1234

当按字符串表示排序时，您会得到:

• 1234
• 125

所以索引在你的情况下是无用的，因为它是按值顺序排列的(因为你的字段是一个整数)而你的搜索需要字符串顺序(因为你正在使用 like 来查找一些字符串前缀)。 (请记住，索引实际上只是一个排序列表)