1、数据库优化的基本原则：让笛卡尔积发生在尽可能小的集合之间，mysql在join的时候可以直接通过索引来扫描，而嵌
入到子查询里头，查询规划器就不晓得用合适的索引了。 一个SQL在数据库里是这么优化的：首先SQL会分析成一堆分析树
，一个树状数据结构，然后在这个数据结构里，查询规划器会查找有没有合适 的索引，然后根据具体情况做一个排列组合
，然后计算这个排列组合中的每一种的开销（类似explain的输出的计算机可读版本），然后比较里 面开销最小的，选取
并执行之。
2、mysql认为扫描全表比走索引代价小,则不走索引
NOT IN、JOIN、IS NULL、NOT EXISTS效率对比
语句一：select count(*) from A where A.a not in (select a from B)
语句二：select count(*) from A left join B on A.a = B.a where B.a is null
语句三：select count(*) from A where not exists (select a from B where A.a = B.a)
知道以上三条语句的实际效果是相同的已经很久了，但是一直没有深究其间的效率对比。一直感觉上语句二是最快的。
今天工作上因为要对一个数千万行数据的库进行数据清除，需要删掉两千多万行数据。大量的用到了以上三条语句所要实
现的功能。本来用的是语句一，但是结果是执行速度1个小时32分，日志文件占用21GB。时间上虽然可以接受，但是对硬盘
空间的占用确是个问题。因此将所有的语句一都换成语句二。本以为会更快。没想到执行40多分钟后，第一批50000行都没
有删掉，反而让SQL SERVER崩溃掉了，结果令人诧异。试了试单独执行这条语句，查询近一千万行的表，语句一用了4秒，
语句二却用了18秒，差距很大。语句三的效率与语句一接近。
第二种写法是大忌，应该尽量避免。第一种和第三种写法本质上几乎一样。
假设buffer pool足够大，写法二相对于写法一来说存在以下几点不足：
（1）left join本身更耗资源（需要更多资源来处理产生的中间结果集）
（2）left join的中间结果集的规模不会比表A小
（3）写法二还需要对left join产生的中间结果做is null的条件筛选，而写法一则在两个集合join的同时完成了筛选，这
部分开销是额外的
3、很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
4、select count(*) from A where A.a not in (select a from B)
select count(*) from A where not exists (select a from B where A.a = B.a)
5.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
6.把潜逃子查询处理掉
select count(id) from (select * from sam where **) as a , (select * from where ***) as b
where a.avitivId=b.id
7、union与union all
我们经常会遇到将两个表中的记录合并到一起，做个sum，在mysql数据库中提供了UNION和UNION ALL关键字，这两个关键
字都是将结果集合并为一个，但这两者从使用和效率上来说都有所不同。
效率上,union all比union更好一点.因为union在执行的时候会把结果集进行排序并删除重复的记录,而union all则会把两
个结果集原封不动地合并在一起.
值得注意的是这两个表的列数要相等。也就是字段的数目要相等。
select * from table union select * from table
select * from table union all select * from table
另外，在数据量大的时候，可以使用mysql的union all 代替多个的OR连接查询。
SELECT * FROM `wox_article` WHERE title like '%we%' UNION all SELECT * FROM wox_article WHERE title LIKE
'%he%'
另外，对于索引列来最好使用union all，因复杂的查询【包含运算等】将使or、in放弃索引而全表扫描，除非你能确定or
、in会使用索引；而对于只有非索引字段来说你就老老实实的用or或者in，因为非索引字段本来要全表扫描而union all
只成倍增加表扫描的次数
阅读(1309) | 评论(0) | 转发(0) |
