前几天发现一个count 语句特别慢，看了下sql where 条件都是 or , explain sql瞅了眼，全表扫描；创建的索引都没用上；sql 大致如下：

SELECT COUNT(1) 
FROM A a 
INNER JOIN B b ON a.ID = b.A_ID 
INNER JOIN C c ON b.ID = c.B_ID
INNER JOIN D d ON c.ID = d.C_ID
LEFT  JOIN E e ON d.ID = e.D_ID 
WHERE 
   a.Q = 'xxxxxx' 
   OR b.Q = 'xxxxxx' 
   OR 
   (  
	   (
		   a.Q = 'yyyyyy' 
		   OR b.Q = 'yyyyyy' 
		)
		AND a.Z >= str_to_date('xxxxxx' , '%Y-%m-%d %H:%i:%S')
		AND e.X IN ( 'xxxxxx' ) 
   ) 

通过 union 去 替换 or ，sql 会复杂但是查询都能走索引；数据量较大可以使用union all + distinct；改造完成后如下：

SELECT COUNT(1) 
FROM 
(
	(
		SELECT aa.ID FROM 
		( 
			SELECT a.ID,b.ID AS bId FROM A a INNER JOIN B b ON a.ID = b.A_ID  WHERE a.Q = 'xxxxxx' 
			 UNION 
			SELECT a.ID,b.ID AS bId FROM A a INNER JOIN B b ON a.ID = b.A_ID  WHERE b.Q = 'xxxxxx' 
		) aa 
		INNER JOIN C c ON aa.bId = c.B_ID
		INNER JOIN D d ON c.ID = d.C_ID
	)
	UNION
	(
		SELECT aa.ID FROM 
		( 
			SELECT a.ID,b.ID AS bId FROM A a INNER JOIN B b ON a.ID = b.A_ID  WHERE a.Q = 'yyyyyy' AND a.Z >= str_to_date('dddddd' , '%Y-%m-%d %H:%i:%S')
			 UNION 
			SELECT a.ID,b.ID AS bId FROM A a INNER JOIN B b ON a.ID = b.A_ID  WHERE b.Q = 'yyyyyy' 
		) aa 
		INNER JOIN C c ON aa.bId = c.B_ID
		INNER JOIN D d ON c.ID = d.C_ID
		LEFT  JOIN E e ON d.ID = e.D_ID AND e.X IN ( 'yyyyy2' )
	)
)

A表中 Q 和 B表中 Q 字段都能走上原定索引