第 69.1 节：不要隐藏列在函数中

一个常见错误是将索引列隐藏在函数调用内部。例如，以下查询无法从索引中受益：

WHERE DATE(dt) = '2000-01-01'

相反，给定 INDEX(dt)，以下查询可能使用索引：

WHERE dt = '2000-01-01'   -- 如果 'dt' 是 DATE 类型

以下方式适用于 DATE、DATETIME、TIMESTAMP，甚至 DATETIME(6)（微秒）：

WHERE dt >= '2000-01-01' AND dt < '2000-01-01' + INTERVAL 1 DAY

第 69.2 节：OR

一般来说，OR 会破坏优化。

WHERE a = 12 OR b = 78

不能使用 INDEX(a,b)，并且可能使用或不使用 INDEX(a) 和 INDEX(b) 通过“索引合并”。“索引合并”比什么都没有好，但也仅此而已。

WHERE x = 3 OR x = 5

可以转换为：

WHERE x IN (3, 5)

这可能会使用包含 x 的索引。

第 69.3 节：添加正确的索引

这是一个庞大的主题，但也是最重要的“性能”问题。

新手需要学习的主要教训是“复合”索引。快速示例：

INDEX(last_name, first_name)

对以下查询非常有效：

WHERE last_name = '...'
WHERE first_name = '...' AND last_name = '...'   -- WHERE 中的顺序无关紧要

但对以下查询无效：

WHERE first_name = '...'      -- 顺序很重要
WHERE last_name = '...' OR first_name = '...'   -- "OR" 是杀手

第 69.4 节：使用索引

对于任何非极小的表，加快查询速度的最重要的事情是拥有合适的索引。

WHERE a = 12          --> INDEX(a)
WHERE a > 12          --> INDEX(a)
WHERE a = 12 AND b > 78   --> INDEX(a,b) 比 INDEX(b,a) 更有用
WHERE a > 12 AND b > 78   --> INDEX(a) 或 INDEX(b)；无法同时处理两个范围
ORDER BY x            --> INDEX(x)
ORDER BY x, y         --> INDEX(x,y) 按此顺序
ORDER BY x DESC, y ASC --> 没有索引有帮助，因为混合了 ASC 和 DESC

第 69.5 节：子查询

子查询有多种类型，优化潜力不同。首先，子查询可以是“相关”的或“非相关”的。相关意味着它们依赖于子查询外部的某个值。这通常意味着子查询必须为每个外部值重新求值。

以下相关子查询通常相当不错。注意：它最多只能返回 1 个值。通常作为 LEFT JOIN 的替代方案，尽管不一定更快。

SELECT a, b, (SELECT ... FROM t WHERE t.x = u.x) AS c FROM u
SELECT a, b, (SELECT MAX(x) ... ) AS c FROM u
SELECT a, b, (SELECT x FROM t ORDER BY ... LIMIT 1) AS c FROM u

以下通常是非相关的：

SELECT ... FROM (SELECT ... ) AS a JOIN b ON ...

关于 FROM 中子查询的注意事项：

  • 如果它返回 1 行，很好。
  • 一个好的范式（同样是“1 行”）是让子查询为 (SELECT @n := 0)，从而初始化一个 @ 变量供查询其余部分使用。
  • 如果它返回多行且 JOIN 的另一个子查询也返回多行，那么效率可能非常糟糕。在 5.6 之前，没有索引，因此变成了 CROSS JOIN；5.6+ 会推断临时表的最佳索引并生成它，只在查询完成后丢弃。

第 69.6 节：JOIN + GROUP BY

导致低效查询的常见问题如下所示：

SELECT ... FROM a JOIN b ON ... WHERE ... GROUP BY a.id

首先，JOIN 扩展了行数；然后 GROUP BY 将行数缩减回 a 的行数。

对于这种“膨胀-收缩”问题，可能没有好的选择。一种可能的选项是将 JOIN 转换为 SELECT 中的相关子查询。这也消除了 GROUP BY。

第 69.7 节：正确设置缓存

innodb_buffer_pool_size 应约为可用 RAM 的 70%。

第 69.8 节：反面教训

以下内容不太可能有助于性能。它们源于过时的信息和/或天真。

  • InnoDB 已经改进到 MyISAM 不太可能更好的程度。
  • PARTITIONING 很少能带来性能提升，甚至可能损害性能。
  • 将 query_cache_size 设置为大于 100M 通常会影响性能。
  • 在 my.cnf 中增加许多值可能导致“交换”，这是一个严重的性能问题。
  • “前缀索引”（如 INDEX(foo(20))）通常无用。
  • OPTIMIZE TABLE 几乎总是无用的（并且会锁定表）。