MySQL 优化（三）

天高云淡

or 子句
　　index merge 方法用于使用 ref, ref_or_null, 或 range 扫描取得的记录合并起来放到一起作为结果。这种方法在表条件是或条件 ref, ref_or_null, 或 range ，并且这些条件可以用不同的键时采用。
　　"join"类型的优化是从 mysql 5.0.0 开始才有的，代表者在索引的性能上有着标志性的改进，因为使用老规则的话，数据库最多只能对每个引用表使用一个索引。
　　在 explain 的结果中，这种方法在 type 字段中表现为 index_merge。这种情况下，key 字段包含了所有使用的索引列表，并且 key_len 字段包含了使用的索引的最长索引部分列表。
　　例如：
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); is null
　　mysql在 col_name is null 时做和 col_name = constant_value 一样的优化。例如，mysql使用索引或者范围来根据 is nul l搜索 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); 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; left join 和 right join
　　a left join b join_condition 在mysql中实现如下：

• 表 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 条件的记录（用于和特定的索引键做联合）后停止搜索了。
  

　　right join 的实现和 left join 类似，不过表的角色倒过来了。
　　连接优化程序计算了表连接的次序。表读取的顺序是由 left join 强行指定的，而且使用 straight_join 能帮助连接优化程序更快地执行，因为这就会有更少的表排队检查了。注意，这里是指如果你执行下面这种类型的查询后，mysql就会对 b 做一次全表扫描，因为 left join 强制要求了必须在读 d 之前这么做：
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。