mysql - 提高长 VARCHAR 字段的排序性能

Question

我有一个 VARCHAR(500) 类型的 composite_title 字段来保存电影或电视节目的标题。以下是它的外观的几个示例:

- The Office, Season 1, Ep. 4 -- The Alliance
- BSG, Season 2, Ep. 3 -- Fragged
- Terminator

当我使用以下方法对字段进行排序时:

select composite_title from title order by composite_title limit 10

它非常慢并且不使用索引，它使用文件排序。我尝试添加类似的内容:

ALTER TABLE title ADD INDEX composite_title(10)

但它并没有提高性能。

我可以做些什么来改进这个查询？

解释

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  title   ALL NULL    NULL    NULL    NULL    341353  Using filesort

更新:

数据库中最长的电视剧是285个字符。

在 Bill Karwin 的解决方案中，这对我有用:

ALTER TABLE `title` ADD `composite_title_for_sorting` VARCHAR(255)  NOT NULL  DEFAULT '';
UPDATE IGNORE title SET composite_title_for_sorting=composite_title;

现在，如果我按 composite_title_for_sorting 排序，查询需要 1 毫秒而不是 500 毫秒。

Answer 1

MySQL 应该使用索引进行排序，但如果索引是前缀索引，它似乎不喜欢这样做。

很明显，如果您的 varchar 长度太长，您必须使用前缀索引。

因此，如果您想加快 ORDER BY 的速度，最好的方法是将列的数据类型更改为可以支持非前缀索引的类型。 (你真的需要 varchar(500) 吗？？)

ALTER TABLE title MODIFY composite_tite VARCHAR(255), ADD KEY (composite_title);

然后你应该得到这个:

EXPLAIN SELECT composite_title FROM title ORDER BY composite_title\G

           id: 1
  select_type: SIMPLE
        table: title
         type: index
possible_keys: NULL
          key: composite_title
      key_len: 768    <-- this is the longest key_len allowed for an index
          ref: NULL
         rows: 100
        Extra: Using index

---

另一个想法是创建第二列来存储字符串的缩短版本，然后索引该列。

如果您必须进行文件排序，那么增加 sort_buffer_size 的大小可以减少排序过程中对使用磁盘的依赖，这将有助于提高性能。

但是如果您全局增加 sort_buffer_size，请注意它是为查询期间排序的每个 session 分配的，因此如果您将它设置为 1GB 或类似的大值，您可能会在不可预测的时间耗尽内存。

您可以逐个 session 更改 sort_buffer_size。因此，您可以在全局范围内将其保留为默认大小，但在运行此类查询的 session 中，增加它。