Mysql：前缀索引 vs 索引

Question

一个mysql前缀索引是否可以像普通索引一样使用？

如果有一个TEXT列，它的前缀索引长度为1，查询语句如下：

SELECT * FROM table WHERE textcol = 'ab'

它会返回以'a'开头的所有行还是会检查整个列的值？

总的来说，我想知道在使用前缀索引时是否有任何注意事项。不是关于性能方面的，更多的是查询是否需要以不同的方式编写或客户端是否需要进行额外的逻辑处理。

- iPherian

Answer 1

如果你仔细想一想，即使没有索引，MySQL 仍然会给出正确的答案...只是速度不够快而已...所以，是的，使用前缀索引也能得到正确答案。

性能会降低，因为在将"可能的"行与索引匹配后，服务器需要再对行数据进行进一步过滤以满足 WHERE 子句。这需要两个步骤，而不是一个，但这并不需要应用程序关心。

其中的注意点包括：前缀索引不能被优化器用于某些操作，例如排序或分组，因为它没有涵盖列数据的足够长度。

前缀索引在超出前缀长度范围之外就无法排序。如果查询使用完整索引来查找行，则通常会发现返回的行隐式按索引顺序排序。如果您的应用程序期望这种行为，那么它当然是期望了不该期望的事情，因为除非您明确地使用 ORDER BY，否则返回行的顺序未定义。不要依赖巧合的行为，在任何查询中，因为由前缀索引匹配的行不会以任何特定顺序排列......实际上，在任何未明确排序的结果集中的顺序都可能随时更改。

而且，前缀索引不能用作覆盖索引。覆盖索引是指所有在一个索引里面的列（加上主键，因为它总是存在的）恰好能满足 SELECT 的情况。优化器将直接从索引中读取数据，而不是使用索引查找主表数据中要查找的行。即使索引无法用于查找匹配的行，优化器也会仅对覆盖索引进行全表扫描，而不是对整个表进行全表扫描，以节省 I/O 和时间。（顺便说一下，这种能力应该足以选择您需要的列，而不是懒惰地使用 SELECT * - 它可能会打开一些更高效的查询计划）。然而前缀索引也无法用于此。

除了性能、优化和暗示执行您期望的查询（您不应该期望这些），在使用前缀索引时，没有任何与逻辑相关的警告需要注意。结果仍将是正确的。

- Michael - sqlbot

---

前缀索引示例不能用于排序，即使前缀长度比列中实际存储的最长值还要长，我在相关问题的答案中进行了解释，网址为http://dba.stackexchange.com/a/48104/11651。 - Michael - sqlbot

Answer 2

往往，“前缀索引”毫无用处。我见过忽略前缀索引的情况，尽管我认为它可以使用。

如果您的TEXT字段永远不会超过255个字符，请将其更改为VARCHAR(255)（或更小），然后使用真正的索引，而不是前缀索引。

假设你有INDEX(textcol(1))，那么它必须扫描以a开头的所有行，找到具有textcol = 'ab'的行，并仅提供这些行。请注意，这是一个性能问题，而不是一个正确性问题（如@Michaelsqlbot所说的那样）。

- Rick James