MySQL select 看起来很慢但是想不出如何改进？

Question

我有一个包含四列的表格...

 `id` INT(11) NOT NULL AUTO_INCREMENT   
 `tid` INT(11) NOT NULL
 `cid` INT(11) NOT NULL
 `name` NVARCHAR(4096) NULL DEFAULT NULL

id 是唯一的主键。其他列并不唯一。

我想返回具有特定 tid 和 cid 值并按名称排序的所有 id 值的列表。所以这...

 select id
 from myTable
 where cid = 1 && tid = 1
 order by name

表中大约有 125k 条记录，并且应该有大约 50k 条记录恰好符合此条件。所有四列都有单独的索引。

在我的机器上，查询运行大约需要 140 毫秒。我需要将其降低到 20 毫秒左右或更好。我认为解决方案是添加一个新的覆盖索引，该索引按 cid、tid 和 name 的顺序定义。但没有任何区别。

有什么想法吗？我的覆盖索引设置不正确吗？

Answer 1

我认为查询和表定义本身存在一些问题。

• Table.name 是一个 4K 字符列
• 查询按该列排序

您正在根据存储字符串的列进行排序。为了按字符串排序，必须执行字符串比较。字符串比较往往是一个缓慢的操作，并且考虑到您所使用的列的大小，它很可能会导致明显的性能下降。

我们没有说明您的name 列的内容，而且似乎很难想到需要这么多字符的实际名称。 p>

如果此字符串具有概念上不同的几条数据，则可能应将该列分解为多个单独的列(如果可能)，然后根据需要进行规范化。

如果您可以将该列的内容分解为多个较小的内容，然后使用它们，则字符串比较虽然仍然很昂贵，但会“更快”，因为要比较的字符串将比现在的字符串短得多.

要考虑的另一件事是，您是否可以通过完全避免字符串比较或避免尽管您已定义索引而导致全表扫描的查询来优化搜索。

为此，您应该考虑在查询中使用 explain ，以便您可以更好地理解 Query Execution Plan

引用文档(我的重点):

Depending on the details of your tables, columns, indexes, and the conditions in your WHERE clause, the MySQL optimizer considers many techniques to efficiently perform the lookups involved in an SQL query. ... Your goals are ... to learn the SQL syntax and indexing techniques to improve the plan if you see some inefficient operations.

<小时/>

编辑 1

您已澄清，您的 name 列实际上用于用户注释。在这种情况下，我认为您应该考虑以下事项(除了已经提到的内容之外):

1. 将列重命名为与其实际内容相关的名称
2. 从列中删除索引
3. 不要使用该列进行搜索、排序或任何其他操作，而不仅仅是选择它来显示它(如果需要的话，这是非常罕见的)恕我直言，可以用于其他任何事情。)
4. (可选)可以考虑将列更改为 text 类型，这样您就不必太担心用户essays在没有警告的情况下被截断(除非 GUI 强制执行)对用户的输入长度限制相同)