使用 order by 时 Mysql 查询运行速度很慢

Question

以下查询在有 order by 时需要 30 秒才能完成。没有命令它会在 0.0035 秒内完成。我已经在字段“名称”上建立了索引。字段“id”是主键。我在这张表中有 400,000 条记录。请帮忙，使用order by时查询有什么问题。

SELECT * 
FROM users 
WHERE name IS NOT NULL 
AND name != '' 
AND ( status IS NULL OR status = '0' ) 
order by id desc 
limit 50

更新:(解决方案在最后)大家好，感谢您的帮助。以下是您要求的一些更新:

1. 下面是解释的输出。

   id select_type table type possible_keys key key_len ref rows Extra

   1 SIMPLE 用户范围名称 name 258 NULL 226009 Using where;使用文件排序

2. 是的，这张表中大约有 20 个字段。

3. 以下是我拥有的索引:

   键名类型基数字段

   主要 主要 418099 id名称 INDEX 411049 名称

解决方法:事实证明，具有空值的字段是原因。当将 where 条件中的这 2 个字段设置为 NOT NULL 时，只需要 .000x 秒。但奇怪的是，如果我创建一个 (status,name,id DESC) 或 (status,name,id) 的索引，它会增加到 29 秒。

Answer 1

你绝对应该有复合索引。包含您作为 DBMS 所需的所有字段的单个索引实际上不能在单个查询中使用多个索引。

OR 子句并不是真正的索引友好，所以如果可以的话，我建议将 status 设置为 NOT NULL。我假设 NULL 与零数字没有任何不同的含义。这将对实际使用索引有很大帮助。

我不知道 name != '' 优化了多少。语义上相等的是 name > ''(意味着它在字母表中的后面)，可能这也为您节省了一些 CPU 周期。

然后您必须决定列的显示顺序。经验法则可以是基数，即字段可以具有的可能值。

由此:

ALTER TABLE users ADD INDEX order1 (status, name, id DESC);

编辑

您不需要删除索引。 MySQL 会很快选择最好的一个而忽略其余的。它们只在 UPDATE 上花费磁盘空间和一些 CPU 周期。但是，如果您在任何情况下都不需要它们，您当然可以删除它们。

时间长是因为访问你的表慢。这可能是由动态长度字段(例如 TEXT 或 BLOB)引起的。如果您并不总是需要这些，您可以将它们移动到一张双人辅助 table ，例如:

users (id, name, status, group_id)
profile (user_id, birthdate, gender, motto, cv)

这样，基本的系统操作就可以通过有关用户的受限信息来完成，而所有其他真正内容与用户相关联的东西只有在它确实是相关的时候才需要使用需要。

编辑2

您通过指定索引(或多个索引)来提示 MySQL 使用哪个索引，例如:

SELECT id, name FROM users USE INDEX (order1) WHERE name != '' and status = '0' ORDER BY id DESC