MySQL 管道排序似乎不起作用

Question

我有 table 用户[id, 名称, 状态] 和索引[状态, 名称, id]

SELECT *
FROM user
WHERE status = 'active'
ORDER BY name, id
LIMIT 50

我有大约 50000 个状态为 ==“活跃”的用户

1.) 为什么 MySQL 解释显示 ROWS 列中大约有 50000 个？为什么即使索引列等于 order by 子句，它也会跟随所有叶节点？

2.) 当我将 order by 子句更改为

ORDER BY status, name, id

解释子句的额外列显示:

使用索引条件；使用地点；使用文件排序

有什么原因导致它不能在此查询中使用索引顺序吗？

编辑1:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `status_name_id` (`status`,`name`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

查询:

SELECT *
FROM `user`
WHERE status = 'complete'
ORDER BY status, name, id
LIMIT 50

解释:

id: 1
select_type: SIMPLE
table: f_order
type: ref
possible_keys: status_name_id
key: status_name_id
key_len: 768
ref: const
rows: 50331
Extra: "Using where; Using index; Using filesort"

最奇怪的是，如果我将 SELECT 语句更改为

SELECT *, count(id)

它再次使用索引，查询速度快了一倍。额外部分仅包含

Using where; Using index

表包含 100k 行、5 种不同的状态和 12 个不同的名称。

MySQL:5.6.27

编辑2:

另一个例子:

这需要 400 毫秒(平均)并进行显式排序

SELECT *
FROM `user`
WHERE status IN('complete')
ORDER BY status, name, id
LIMIT 50

这需要 2 毫秒(平均)并且没有显式排序

SELECT *
FROM `user`
WHERE status IN('complete', 'something else')
ORDER BY status, name, id
LIMIT 50

Answer 1

Q1:EXPLAIN 有点蹩脚。在提供 Rows 估计时，它没有考虑到 LIMIT 的存在。请放心，如果它能短暂停止，它就会停止。

Q2:它是否说正在使用您的索引？请提供完整的EXPLAIN和SHOW CREATE TABLE。

更多

使用 INDEX(status, name, id)、WHERE、ORDER BY、和 LIMIT 可以在索引中处理。因此它只能读取 50 行。

如果没有该索引(或者对查询进行任何实际更改)，则需要读取大部分或全部表，将其存储在临时表中，进行排序，然后才能剥离 50 行。

所以，我建议它比“显式排序可以杀死我的数据库服务器”更复杂。