MySQL 简单但缓慢的查询(错误的索引？)

Question

我正在处理一个简单的查询，该查询在包含 126,000 条记录的 myisam 表中运行大约 1.2 秒:

SELECT * FROM my_table
WHERE primary_key != 5 AND
(
    col1 = 528 OR (col2 = 265 AND col3 = 1)
)
ORDER BY primary_key DESC

我已经为 where 子句中使用的每个字段创建了单个索引，但只有 primary_key(my_table 的自增字段)用作键，而 col1 和 col2 被忽略，查询变得更慢。我应该如何创建索引(可能是多个索引)或编辑查询？

Answer 1

如果您具有以下多列“覆盖”索引，您将获得最佳性能:

(primary_key, col1)
(primary_key, col2, col3)

并发出以下查询:

(SELECT * FROM my_table
WHERE primary_key != 5 AND
  col1 = 528)
UNION
(SELECT * FROM my_table
WHERE primary_key != 5 AND
 col2 = 265 AND col3 = 1)
ORDER BY primary_key DESC

您可以根据基数更改索引中字段的顺序，从而获得可变的、更好的性能。

在你的原始查询中，WHERE子句中的整个选择都没有使用索引，导致部分表扫描。

在上面的查询中，第一个子查询可以完全利用第一个索引，避免任何表扫描，第二个子查询使用第二个索引。

不幸的是，MySQL 无法利用索引对完整结果集上的记录进行排序，而可能会使用文件排序对它们进行排序。因此，如果您不需要按 primary_key 排序的记录，请删除外部 ORDER 子句以获得更好的性能，但如果结果集很小，则无论哪种方式都不成问题。