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

总共有252k行.如果我像WHERE那样搜索u_> 0我得到60k行.但是,如果我做那个选择的解释,它说有225k行可以通过,没有索引可能.为什么？列被添加为索引.为什么它没有说有60k行可以通过,可能的关键是U_？

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

相比之下,如果我运行查询：其中id> 250000我得到了2983个结果,如果我解释了那个选择它说有2982行和密钥用于主要.

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

编辑：

如果我创建列U(varchar(1))并执行更新U =’U’,其中U_> 0,然后如果我做选择WHERE U =’U’我也得到60k行(显然),但如果我解释我得到这个：

仍然不太好(行120k不是60k),但至少比之前的情况下的行225k更好.虽然这个解决方案比第一个解决方案有点小,但可能更有效率.

解决方法:

你问的是后端查询优化器.特别是你问：“它如何选择一条访问路径？为什么要在这里编制索引,但是表格可以在那里搜索？”

让我们考虑一下优化器.什么是优化？经过的时间,在期待中.它具有连续读取和随机读取所用时间以及查询选择性(即查询返回的预期行数)的模型.从几个备选访问路径中,它选择看起来需要最少经过时间的路径.

你的ID> 250000查询有一些事情要做：

>良好的选择性,因此不到1％的行将出现在结果集中
> id是主键,因此在导航到btree中的正确位置时,所有列都可立即使用

这导致优化器计算索引访问路径的预期已用时间,远小于表扫描的预期时间.

另一方面,你的u_> 0查询的选择性非常差,将近四分之一的行拖入结果集.此外,索引不是将所有列值复制到结果集中的*需求的覆盖索引.因此优化器预测它必须读取四分之一的索引块,然后基本上读取它们指向的所有数据行块.因此,与tablescan相比,我们必须从磁盘读取更多块,它们将是随机读取而不是顺序读取.这两个人都反对使用索引,因此选择tablescan是因为它最便宜.此外,请记住,通常多行将适合单个磁盘块或单个读取请求.如果它总是选择索引访问路径,我们会称它为悲观者,即使在索引磁盘I / O需要更长时间的情况下也是如此.

总结建议

当查询具有良好的选择性时,在单个列上使用索引,返回远小于关系行的1％.当您的查询具有较差的选择性并且您愿意进行空间与时间的权衡时,请使用covering index.