索引合并优化
可以使用Index Merge的示例查询：
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name
WHERE (key1 = 10 OR key2 = 20) AND non_key = 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);
如果您的查询具有WHERE 带深度and/or 嵌套的复杂子句 且MySQL未选择最佳计划，请尝试使用以下身份转换来分发术语：
(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)
索引合并不适用于全文索引。
IS NULL优化
假设列a和 b表上有索引，优化的查询的一些示例t2：
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);
优化总篇：blog.csdn.net/weixin_4274…
文章持续更新，转发表明出处，方便更新！
