MySQL优化（三）

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
    WHERE key_part1=constant;

SELECT ... FROM tbl_name
    ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
    ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

以下几个查询都是使用索引树，假使那些索引字段都是数字型：

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
    WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

以下几个查询使用索引来取得经过顺序排序后的记录而无需经过独立的排序步骤：

SELECT ... FROM tbl_name
    ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
    ORDER BY key_part1 DESC, key_part2 DESC, ... ;

7.2.5 MySQL 如何优化 OR 子句

SELECT * FROM tbl_name WHERE key_part1 = 10 OR key_part2 = 20;

SELECT * FROM tbl_name
    WHERE (key_part1 = 10 OR key_part2 = 20) AND non_key_part=30;

SELECT * FROM t1,t2
    WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
    AND t2.key1=t1.some_col;

SELECT * FROM t1,t2
    WHERE t1.key1=1
    AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);
  

7.2.6 MySQL 如何优化 IS NULL

SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

SELECT * FROM tbl_name
    WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

如果一个 WHERE 子句包括了一个 col_name IS NULL 条件，并且这个字段声明为 NOT NULL，那么这个表达式就会被优化。当字段可能无论如何都会产生 NULL 值时，就不会再做优化了；例如，当它来自一个 LEFT JOIN 中右边的一个表时。

MySQL 4.1.1或更高会对连接 col_name = expr AND col_name IS NULL 做额外的优化， 常见的就是子查询。EXPLAIN 当优化起作用时会显示 ref_or_null。

优化程序会为任何索引部分处理 IS NULL。

以下几个例子中都做优化了，假使字段 a 和 表 t2 中 b 有索引了：

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;

SELECT * FROM t1,t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1,t2
    WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1,t2
    WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1,t2
    WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
    OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null 首先读取引用键，然后独立扫描键值为 NULL 的记录。

请注意，优化程序只会处理一个 IS NULL 级别。下面的查询中，MySQL只会使用键来查询表达式 (t1.a=t2.a AND t2.a IS NULL) 而无法使在 b 上使用索引部分：

SELECT * FROM t1,t2
     WHERE (t1.a=t2.a AND t2.a IS NULL)
     OR (t1.b=t2.b AND t2.b IS NULL);
  

7.2.7 MySQL 如何优化 DISTINCT

在很多情况下，DISTINCT 和 ORDER BY 一起使用时就会创建一个临时表。

注意，由于 DISTINCT 可能需要用到 GROUP BY，就需要明白MySQL在 ORDER BY 或 HAVING 子句里的字段不在选中的字段列表中时是怎么处理的。详情请看"13.9.3 GROUP BY with Hidden Fields"。

当 LIMIT row_count 和 DISTINCT 一起使用时，MySQL在找到 row_count 不同记录后就会立刻停止搜索了。

如果没有用到来自查询中任何表的字段时，MySQL在找到第一个匹配记录后就会停止搜索这些没没用到的表了。在下面的情况中，假使 t1 在 t2 前就使用了（可以通过 EXPLAIN 分析知道），MySQL就会在从 t2 中找到第一条记录后就不再读 t2 了（为了能和中 t1 的任何特定记录匹配）：

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

7.2.8 MySQL 如何优化 LEFT JOIN 和 RIGHT JOIN

• 表 B 依赖于表 A 以及其依赖的所有表。
• 表 A 依赖于在 LEFT JOIN 条件中的所有表（除了 B）。
• LEFT JOIN 条件用于决定如何从表 B 中读取记录了（换句话说，WHERE 子句中的任何条件都对此不起作用）。
• 所有标准的连接优化都会执行，例外的情况是有一个表总是在它依赖的所有表之后被读取。如果这是一个循环的依赖关系，那么MySQL会认为这是错误的。
• 所有的标准 WHERE 优化都会执行。
• 如果 A 中有一条记录匹配了 WHERE 子句，但是 B 中没有任何记录匹配 ON 条件，那么就会产生一条 B 记录，它的字段值全都被置为 NULL。
• 如果使用 LEFT JOIN 来搜索在一些表中不存在的记录，并且 WHERE 部分中有检测条件：col_name IS NULL，col_name 字段定义成 NOT NULL 的话，MySQL就会在找到一条匹配 LEFT JOIN 条件的记录（用于和特定的索引键做联合）后停止搜索了。
  

SELECT *
    FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
    WHERE b.key=d.key;

解决这种情况的方法是按照如下方式重写查询：

SELECT *
    FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
    WHERE b.key=d.key;

从4.0.14开始，MySQL做如下 LEFT JOIN 优化：如果对产生的 NULL 记录 WHERE 条件总是 假，那么 LEFT JOIN 就会变成一个普通的连接。
例如，下面的查询中如果 t2.column1 的值是 NULL 的话，WHERE 子句的结果就是假了：

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

因此，这就可以安全的转换成一个普通的连接查询：

SELECT * FROM t1,t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

这查询起来就更快了，因为如果能有一个更好的查询计划的话，MySQL就会在 t1 之前就用到 t2 了。想要强行指定表顺序的话，可以使用 STRAIGHT_JOIN。

‹ MySQL 优化（二）向上MySQL 优化（四） ›