MySQL 优化一个请求

Question

我有 2 个表的加入请求。我执行此请求两次以进行分页。我之前执行过一个 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) UNSIGNED，现在它们是 ENUM
• 我添加了这 2 个部分索引 rights、avail、del_date 和 id_b、rights、avail、del_date 来优化 ORDER BY 子句(非常好的 yield )

但现在，我注意到 (A.del_date IS NULL OR A.del_date > '2016-02-17') 是速度缓慢的原因。

如果我删除所有此子句(A.del_date IS NULL OR A.del_date > '2016-02-17')或仅删除A.del_date IS NULL，请求非常快。

任何帮助将非常感谢!

Answer 1

第一个 SELECT 只有一个 COUNT(*)，由于多种原因很奇怪......

• ORDER BY 没有用，而且可能会减慢查询速度。
• A 和 B 之间的映射是什么？如果是 1:1，则无需包含有关 B 的任何内容。如果每个 A 有 0 或 1 个 B 行，那么您需要 JOIN。如果是 1:many，那么 COUNT 将大于 A 的行数；那是你要的吗？要解决此问题，请将 JOIN 更改为 EXISTS。

如果您需要在查询中保留 B，请添加 INDEX(rights, del_date,avail, id_b)(按任意顺序)。
如果您不需要保留 B，则添加 INDEX(rights, del_date,avail)(按任意顺序)。
无论哪种情况，这都将是一个“覆盖索引”(EXPLAIN 将显示“使用索引”)并且运行速度会更快。

OR 通常是性能 killer ——它通常消除了使用索引的可能性。请提供 EXPLAIN SELECT 以查看此处是否属于这种情况。

请不要用句子来呈现信息；使用实际输出或模式规范。东西可能会丢失。

您应该考虑迁移到 InnoDB。

如有必要，您应该删除“out of nnn items”，从而消除 COUNT(*) 或 SQL_CALC_FOUND_ROWS。

对于主查询，问题在于过滤是在一个表上，但 ORDER BY 是在另一张表上。这使得优化变得困难或不可能。