在 SO,最近有人问为什么 ORDER BY 不使用索引?
这种情况涉及 MySQL 中的一个简单 InnoDB 表,该表包含三列和 10k 行。其中一列,一个整数,被索引了——并且 OP 试图检索他在该列上排序的整个表:
SELECT * FROM person ORDER BY age
他附加EXPLAIN
的输出显示这个查询是用 a filesort
(而不是索引)解决的,并询问为什么会这样。
尽管提示 FORCE INDEX FOR ORDER BY (age)
导致使用索引,但有人回答(带有其他人的支持评论/赞成票)索引仅用于从索引中读取所有选定列时的排序(即通常Using index
在Extra
列中表示EXPLAIN
输出)。后来给出了一个解释,即遍历索引然后从表中获取列会导致随机 I/O,MySQL 认为这比filesort
.
这似乎与ORDER BY
优化的手册章节背道而驰,它不仅传达了一种强烈的印象,即ORDER BY
从索引中满足比执行额外的排序更可取(实际上,filesort
它是快速排序和合并排序的组合,因此 必须有一个下限; 虽然按顺序遍历索引并查找表应该是 - 所以这很有意义),但它也忽略了提到这个所谓的“优化”,同时还说明:Ω(nlog n)
O(n)
以下查询使用索引来解析
ORDER BY
部件:SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
根据我的阅读,在这种情况下正是这种情况(但在没有明确提示的情况下没有使用索引)。
我的问题是:
为了让 MySQL 选择使用索引,是否确实需要对所有选定的列进行索引?
如果是这样,这在哪里记录(如果有的话)?
如果不是,这里发生了什么?
这是一个加载的问题,因为有一些因素决定了索引是否值得使用。
因素 #1
对于任何给定的索引,关键人群是什么?换句话说,索引中记录的所有元组的基数(不同计数)是多少?
因素 #2
你用的是什么存储引擎?是否可以从索引访问所有需要的列?
下一步是什么 ???
让我们举一个简单的例子:一个包含两个值(男性和女性)的表
让我们创建这样一个表并测试索引使用情况
测试 InnoDB
测试 MyISAM
InnoDB 分析
当数据作为 InnoDB 加载时,请注意所有四个
EXPLAIN
计划都使用了gender
索引。第三个和第四个EXPLAIN
计划使用gender
索引,即使请求的数据是id
. 为什么?因为id
在PRIMARY KEY
并且所有二级索引都有引用指针返回PRIMARY KEY
(通过gen_clust_index)。MyISAM 分析
当数据作为 MyISAM 加载时,请注意前三个
EXPLAIN
计划使用gender
索引。在第四个EXPLAIN
计划中,查询优化器决定根本不使用索引。它选择了全表扫描。为什么?不管 DBMS 是什么,查询优化器都按照一个非常简单的经验法则运行:如果一个索引被筛选为用于执行查找的候选者,并且查询优化器计算出它必须查找超过总数量的 5%表中的行:
结论
如果您没有适当的覆盖索引,或者任何给定元组的键人口超过表的 5%,则必须发生六件事:
WHERE
、GROUP BY
和 ORDER BY` 子句WHERE
具有静态值的子句列GROUP BY
列ORDER BY
列WHERE
子句的查询)我过去曾写过这 5% 的经验法则:
May 07, 2012
:MySQL EXPLAIN 不显示 FULLTEXT 的“使用索引”Mar 22, 2012
: MySQL 为什么选择这个执行计划?Mar 09, 2012
:未使用索引Jan 18, 2012
: MySQL 状态变量 Handler_read_rnd_next 增长了很多Dec 27, 2011
: MySQL - ALTER TABLE for InnoDB 的最快方法Jul 29, 2011
: MySQL 查询优化 : 索引和分页Jul 12, 2011
:尽管没有索引/键,但在更改一个 WHERE 字段时,MySQL 查询非常慢更新 2012-11-14 13:05 EDT
我回顾了你的问题和原来的 SO 帖子。然后,我想到了我
Analysis for InnoDB
之前提到的我。与person
表相吻合。为什么?对于两个表
mf
和person
id
EXPLAIN
计划现在,查看来自 SO question 的查询:
select * from person order by age\G
。由于没有WHERE
子句,您明确要求进行全表扫描。表的默认排序顺序将按id
(PRIMARY KEY) 排序,因为它的 auto_increment 和gen_clust_index(又名聚集索引)按内部 rowid 排序。当您按索引排序时,请记住 InnoDB 二级索引将 rowid 附加到每个索引条目。这产生了每次对全行访问的内部需求。ORDER BY
如果您忽略有关如何组织 InnoDB 索引的这些事实,则在 InnoDB 表上设置可能是一项相当艰巨的任务。回到那个 SO 查询,因为您明确要求进行全表扫描,恕我直言,MySQL 查询优化器做了正确的事情(或者至少,选择了阻力最小的路径)。当涉及到 InnoDB 和 SO 查询时,执行全表扫描然后进行一些
filesort
操作要比通过 gen_clust_index 对每个二级索引条目执行全索引扫描和行查找要容易得多。我不提倡使用索引提示,因为它忽略了解释计划。尽管如此,如果你真的比 InnoDB 更了解你的数据,你将不得不求助于索引提示,尤其是对于没有
WHERE
子句的查询。更新 2012-11-14 14:21 EDT
根据《Understanding MySQL Internals 》一书
第 202 页第 7 段说:
这就是为什么我前面说过:执行全表扫描然后进行一些文件排序,而不是通过 gen_clust_index 对每个二级索引条目执行全索引扫描和行查找要容易得多。InnoDB 每次都会进行双索引查找。这听起来有点残酷,但这只是事实。再次,考虑到缺少
WHERE
子句。这本身就是 MySQL 查询优化器进行全表扫描的提示。改编自Denis对 SO 上另一个问题的回答(经许可) :
由于查询将获取所有记录(或几乎所有记录),因此您通常最好完全不使用索引。这样做的原因是,读取索引实际上要花一些钱。
当您要查找整个表格时,按顺序读取表格并在内存中对其行进行排序可能是您最便宜的计划。如果您只需要几行并且大多数都将匹配 where 子句,那么选择最小的索引就可以了。
要了解原因,请想象所涉及的磁盘 I/O。
假设您想要整个表没有索引。为此,您读取 data_page1、data_page2、data_page3 等,依次访问涉及的各个磁盘页,直到到达表的末尾。然后,您排序并返回。
如果您想要前 5 行没有索引,您可以像以前一样顺序读取整个表,同时对前 5 行进行堆排序。诚然,对于少数几行,这需要大量的阅读和排序。
现在假设您希望整个表都有一个索引。为此,您依次读取 index_page1、index_page2 等。然后,这会导致您以完全随机的顺序(排序的行出现在数据中的顺序)访问,比如说,data_page3,然后是 data_page1,然后是 data_page3,然后是 data_page2,等等。所涉及的 IO 使得按顺序读取整个混乱并在内存中对抓包进行分类变得更便宜。
相反,如果您只想要索引表的前 5 行,则使用索引成为正确的策略。在最坏的情况下,您在内存中加载 5 个数据页并继续前进。
顺便说一句,一个好的 SQL 查询计划器将根据数据的碎片程度来决定是否使用索引。如果按顺序获取行意味着在表格中来回缩放,那么优秀的计划者可能会认为不值得使用索引。相反,如果使用相同的索引对表进行聚类,则可以保证行是有序的,从而增加了它被使用的可能性。
但是,如果您将同一个查询与另一个表连接起来,并且该表有一个非常有选择性的 where 子句可以使用一个小索引,那么规划器可能会决定实际上更好,例如获取标记为的行的所有 ID
foo
,散列加入表,并在内存中对它们进行堆排序。