请求的MySQL优化

Question

我在两张桌子上有一个连接请求。我为分页做了两次这个请求。我以前做过一个SQL_CALC_FOUND_ROWS请求。

表A包含145000行表B包含91000行

这些是MyIsam表。

表A如下：

id        MEDIUMINT(6) UNSIGNED PK
id_b      MEDIUMINT(6) UNSIGNED INDEX
rights    ENUM ('0', '1', '2', '3', '4', '5', '6', '7', '8') INDEX
avail     ENUM ('0', '1', '2', '3') INDEX
del_date  DATE INDEX
... and 40 others fields

这些指标如下：

rights, avail, del_date
id_b, rights, avail, del_date

表B如下：

id        MEDIUMINT(6) UNSIGNED PK
title     VARCHAR(150) INDEX
.. and 47 others fields

以下是我们的要求：

SELECT COUNT(*)
FROM A
INNER JOIN B ON B.id = A.id_b
WHERE
  A.rights NOT IN ('7')
  AND (A.del_date IS NULL OR A.del_date > '2016-02-17')
  AND A.avail NOT IN ('0')
ORDER BY B.title;

SELECT A.*, B.*
FROM A
INNER JOIN B ON B.id = A.id_b
WHERE
  A.rights NOT IN ('7')
  AND (A.del_date IS NULL OR A.del_date > '2016-02-17')
  AND A.avail NOT IN ('0')
ORDER BY B.title
LIMIT 0, 20;

第一个请求实际上需要2秒，第二个0.3秒。在尝试优化我所能达到的最大值之前，SQL_CALC_FOUND_ROWS请求有时需要10秒或更长的时间。

我所做的优化：

• 抑制SQL_CALC_FOUND_ROWS，并使用2个请求执行它。
• 字段的权利和效用是TINYINT(4)未签名的，现在它们是ENUM
• 我添加了这两个部分的索引right，del_date & id_b，rights，del_date来优化ORDER子句(非常好的收益)

但现在，我注意到(A.del_date为空或A.del_date > '2016-02-17')是导致缓慢的原因.

如果删除所有这些子句(A.del_date为空或A.del_date > '2016-02-17')或A.del_date为NULL，则请求非常快。

任何帮助都将非常感谢！

Answer 1

第一个SELECT，它只有一个COUNT(*)，是奇怪的，有几个原因.

• ORDER BY是无用的，可能会减慢查询的速度。
• A和B之间的映射是什么？如果是1:1，那么就没有必要包含任何关于B的内容。如果每个B都有0或1个A行，那么您就需要JOIN。如果是1:多，那么COUNT将超过来自A的行数；这就是您想要的吗？要解决这个问题，请将JOIN更改为EXISTS。

如果需要将B保留在查询中，可以添加INDEX(rights, del_date, avail, id_b) (按任何顺序)。

如果不需要保留B，那么可以添加INDEX(rights, del_date, avail) (按任何顺序)。

在任何一种情况下，这都是一个“覆盖索引”(EXPLAIN会说“使用索引”)，并且运行得更快。

OR通常是性能杀手--它通常消除了使用索引的可能性。请提供EXPLAIN SELECT，看看这里的情况是否如此。

请不要在句子中显示信息；使用实际输出或模式规范。事情可能会消失。

您应该考虑搬到InnoDB。

如果有必要，您应该去掉“nnn项之外”，从而消除COUNT(*)或SQL_CALC_FOUND_ROWS。

对于主查询，问题是筛选位于一个表上，而ORDER BY位于另一个表上。这使得优化要么困难要么不可能。

Answer 2

与其在"A“表中设置单独的列索引，不如尝试一个由多个字段组成的符合标准的复合索引。这样，引擎就不需要永远加载所有实际的页面数据来确认其他部分，而只能通过索引中的内容获得这些数据。然后，符合条件的人会读取记录其余部分的实际数据页。

查看WHERE子句，由于您使用NOT作为您的权限和可用性标志，我将有一个索引on ( del_date，are，right)

你的"B“表，我也会有一个索引(id，标题)出于类似的原因。