我有什么选择可以让我的 ORDER BY 更快？

Question

我有以下查询：

SELECT DISTINCT c.id
FROM clients AS c
LEFT JOIN client_project AS cp ON (cp.client_id = c.id)
WHERE cp.project_id = 1
    AND c.active_flag = 1
ORDER BY c.client_name

如果我删除 order by，则查询需要 0.005 秒。 使用order by，查询需要1.8-1.9秒。 我在 client_name 上有一个索引。

还有什么可以提高速度？

编辑： c.id 是主键，但在 client_project 中可能有多个记录，因此可能会导致每个 id 都有多个记录。 此外，删除不同的值会使查询出现 0.1 秒的差异。

补充：这是我的客户表：

CREATE TABLE IF NOT EXISTS `clients` (
  `id` int(11) NOT NULL auto_increment,
...
  `organization` varchar(255) character set utf8 collate utf8_bin NOT NULL,
  `client_name` varchar(255) character set utf8 collate utf8_bin NOT NULL,
  `active_flag` tinyint(1) NOT NULL,
...
  PRIMARY KEY  (`id`),
  KEY `active_flag` (`active_flag`),
...
  KEY `organization` (`organization`),
  KEY `client_name` (`client_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

使用 MySQL 5.0

Answer 1

查看您的编辑，

在这种情况下尝试使用 EXISTS

SELECT  c.id
FROM clients AS c
WHERE EXISTS (SELECT * FROM  client_project AS cp  
              WHERE cp.client_id = c.id and cp.project_id = 1)
AND c.active_flag = 1

Answer 2

尝试将此密钥添加到 client_projects 中：

KEY(client_name, id, active_flag)

Answer 3

可能在clients.id和clients.active_flag上有索引，因此优化器不需要转到完整表（或附加索引），除非您想对其进行排序。

检查优化器计划，我认为在mySQL中它是explain。

client_name、id 上的索引可能有帮助（也可能没有帮助 - 检查计划）。

几个可能有帮助的附加问题/想法/评论...

• 如果您从选择中获得的只是 id，为什么要按名称排序？
• 如果您有“cp.project_id”的 where 子句，为什么要进行左连接，因此客户端没有无论如何，一个项目都不会被退回

• 至于其他海报（paul、eppz），对于拥有多个项目的客户来说，可能需要“不同”。 所以另一个想法是做类似的事情

  选择ID
  来自客户c
  哪里存在
  (select * from client_project cp where c.id = cp.client_id)

Answer 4

我没有给你解决方案，但我有一个解释。

MySQL 每个表仅使用一个索引。 您有两个表，其中使用的索引是其中一个表的主键 (WHERE cp.project_id = 1)，并且联接强制使用第二个表索引来有效联接。

使用 ORDER BY 之后，MySQL 就无法使用索引来排序。 添加更多索引不会有帮助。 EXPLAIN 将显示 MySQL 选择为每个表使用哪些索引。 强制索引会导致查询的其他部分变慢。

Answer 5

c.id 是主键吗？ 如果是这样，您不应该对其执行 DISTINCT，因为它已经是不同的，并且强制 DISTINCT 可能会导致它按 id 排序，然后按 client_name 排序。

Answer 6

一些优化与数据库供应商无关，而另一些优化则是数据库供应商特定的。 这里有一些值得尝试的事情。

• 按照其他地方的建议删除 DISTINCT。
• 考虑使用内连接。 我意识到这对于您的情况可能不是一个可行的选择。

此外，运行执行计划可以更好地了解查询的哪些部分占用了最多时间以及原因，从而更好地了解正在发生的情况。 有关更多详细信息，请参阅 EXPLAIN 关键字。

Answer 7

您需要在 client_name 上强制使用索引：

SELECT id
FROM (
  SELECT c.id,
    (
    SELECT 1
    FROM client_projects cp
    WHERE cp.client_id = c.id
      AND cp.project_id = 1
    LIMIT 1
    ) e
FROM clients c
FORCE INDEX (client_name)
WHERE c.active_flag = 1
ORDER BY
  client_name
) co
WHERE e IS NOT NULL

Answer 8

c.id 是身份列吗？ 如果是，我认为您不需要其中的 DISTINCT，因为每个 c.id 都是唯一的。

编辑

那么，即使 cp.project_id = 1，c.id 也可能在 cp 中有多个条目？

编辑

只是好奇为什么您在未选择客户名称时要按客户名称订购。

Answer 9

如果您甚至不退货，为什么要按客户姓名订购？

您还需要独特的吗？

如果您的 where 子句无论如何都会使其成为内部联接，那么您为什么要执行左联接

通过在 WHERE 子句之前使用 WHERE cp.project_id = 1 而不是 AND cp.project_id = 1，无论如何它都是 INNEr JOIN