为什么我的MySQL带有ORDER BY的SELECT语句很慢，即使该列上有索引？

Question

我有一个名为 movies 的表格，其中有130万行内容。

这个表格在 title 列上建立了一个索引，按照顺序 asc 排列，长度为 255 。 title 列本身是一个 VARCHAR(1000) 类型。

即使配置了以上环境，下面的查询也需要8秒才能运行。你们有什么想法或者大胆的尝试吗？我被困扰了，因为这似乎是一个基本问题。

SELECT title
FROM movies 
ORDER BY title
LIMIT 150000, 50000

当我去掉ORDER BY时，查询速度非常快（0.05秒）：

SELECT title
FROM movies 
LIMIT 150000, 50000

- Ethan Allen

---

由于“限制”，数据库引擎需要遍历150,000行。 - Gordon Linoff

@GordonLinoff，实际上限制是50000。 - Ormoz

我同意LIMIT也是减速的一部分，但它所花费的时间太长了，特别是当你将偏移量提高到像500000这样的数字时（这会使查询时间延长到30秒）。有什么解决方法可以加快速度呢？ - Ethan Allen

您可能会遇到表格本身的一些问题。请重新创建表格并查看时间是否更改。 - Ormoz

@EthanAllen，你能否做一下 explain select ... 并且发布输出结果吗？如果 explain 由于某些原因没有使用索引，你可以提供提示，例如：select ... from moves use index (idx_indexname) order...。 - zedfoxus

Answer 1

编辑：前缀索引是我所使用的部分索引更好的名称。

由于您的索引是部分索引，MySQL 可能无法将其用于 order by，并且仍然必须按其完整长度对值进行排序。

让我们尝试这个小样本：

 create table o1 (a varchar(10));

 insert into o1 values('test1'),('test2'),('test3'),('tes1');
 create index oindex on o1 (a);
 explain select a from o1 order by a;

MySQL正在使用索引进行order by操作。

     # id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
     '1', 'SIMPLE', 'o1', 'index', NULL, 'oindex', '103', NULL, '8', 'Using index'

现在，重新创建一个部分索引：

 drop index oindex on o1;
 create index oindex on o1 (a (2) );
 explain select a from o1 order by a;

MySQL正在尝试使用“filesort”。

 # id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
 '1', 'SIMPLE', 'o1', 'ALL', NULL, NULL, NULL, NULL, '8', 'Using filesort'

对于搜索来说，部分索引非常有用，因为MySQL可以只丢掉那些不完全匹配的值。但对于ORDER BY来说，MySQL可能就没有这样的运气了。在上述情况下，即使我为列的最大长度创建了一个“部分索引”，MySQL仍然不会使用该索引进行ORDER BY。

- Tim3880

Answer 2

性能问题在于limit子句中表达的偏移值。如果您正在通过表格进行读取，则可以存储这些值并在order by之前使用>：

select title
from movies
where title > $title
order by title
limit 50000;

如果$title是第150,000行的标题，那么这应该很快。根据此查询的结果，您将为下一次查询重置$title。

我很惊讶相对较少的行需要几十秒钟。一旦索引在内存中，速度应该会更快。另一个复杂因素是标题可能很长，所以索引可能占用了许多十几或数百兆字节。这仍然不是非常大，但会引入明显的延迟。

- Gordon Linoff

Answer 3

如果要搜索像 title 这样的内容，最好使用 FULLTEXT 和 MATCH(...) AGAINST(...)。

- Rick James