mysql没有使用索引？

Question

我有一个表，其中包含单词、A_、E_、U_ 等列。这些带有 X_ 的列是 tinyints，其值是特定字母在单词中存在的次数(稍后有助于优化通配符搜索查询)。

总共有 252k 行。如果我像 WHERE u_ > 0 这样搜索，我会得到 60k 行。但是如果我对那个选择进行解释，它说有 225k 行要经过并且没有索引可能。为什么？列已添加为索引。为什么它不说有 60k 行要经过并且可能的键是 U_？

列出表上的索引(也很奇怪，其他人都在 A_ 索引下分组)

相比之下，如果我运行查询:where id > 250000，我会得到 2983 个结果，如果我确实解释了那个选择，它说有 2982 行和主要使用的键。

顺便说一句，如果我按 U_ 分组，我会得到这个:(但可能并不重要，因为我已经说过查询会返回 60k 个结果)

编辑:

如果我创建列 U (varchar(1)) 并执行更新 U = 'U' where U_ > 0，那么如果我执行 select WHERE U = 'U' 我也会得到 60k 行(显然)，但是如果我解释一下，我会明白这一点:

仍然不是很好(第 120k 行不是 60k)但至少比前一个案例中的第 225k 行要好。虽然这个解决方案比第一个解决方案更麻烦，但可能更有效。

Answer 1

我的经验是，如果您的查询将选择表中超过大约 25% 的行，即使您正在搜索的列上有索引，MySQL 也会选择进行表扫描。

这样做的原因是在 InnoDB 中使用二级索引比使用主索引要多一些工作。

1. 在二级索引中查找值，例如您在 u_ 上的索引。
2. 读取索引条目，并找到存储该值在 u_ 中的行的对应主键值。
3. 按主键查找行。

实际上，通过辅助键查找至少是 两倍 的工作。如果您最终匹配表中的少数行，这不是问题，并且在某些情况下二级索引对您的查询非常重要。所以不要勉强使用二级索引。

但是，如果您的查询匹配太多行，并且这会成为表格的很大一部分，那么只从头到尾扫描表格会减少工作量。

以此类推，为什么一本书后面的索引中没有“the”这个词呢？因为条目自然会列出书中的每一页，如果你引用索引然后用它来引导你到书中主要部分的每一页，那将是一种浪费.你最好只读这本书。

MySQL 没有任何正式记录的阈值来选择表扫描而不是索引搜索。 25% 的数字只是我的经验(实际上有时它看起来更接近 21%，但我对代码的了解不够深入，无法准确理解阈值的计算方式)。

我见过这样的情况，其中匹配的行的比例非常接近实现中的任何阈值，并且优化器的行为实际上可以从一个查询切换到下一个查询，从而导致高度可变的性能。

如果这种情况适用于您，您可以使用 index hint使 MySQL 的优化器假装表扫描非常昂贵，并且它应该更喜欢索引而不是表扫描。这是通过 FORCE INDEX 提示完成的。

SELECT * FROM words FORCE INDEX(U_) WHERE U_ > 0

我仍然尽量保守地使用索引提示。它们不是必需的，除非在极少数情况下，使用索引提示意味着您的查询必须包含索引名称。这使得在不破坏应用程序代码的情况下很难更改索引。