网上关于SQL优化方案有很多,并且有些方案之间有冲突,所以自己测试、整理了一些sql的优化信息。
1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库。备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num = 0
3.应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。
4.or、in 和 union all 效率的对比。
4.1 不要迷信union all 就比 or及in 快,要结合实际情况分析到底使用哪种情况。
4.2 对于索引列来最好使用union all,因复杂的查询【包含运算等】将使or、in放弃索引而全表扫描,除非你能确定or、in会使用索引。
4.3 对于只有非索引字段来说你就老老实实的用or 或者in,因为 非索引字段本来要全表扫描而union all 只成倍增加表扫描的次数。
4.4 对于及有索引字段【索引字段有效】又包含非索引字段来时,就看索引带来的时间优化能不能抵消扫描表所需的时间再选择。
4.5 以上主要针对的是单表,而多表联合查询来说,考虑的地方就比较多了,比如连接方式,查询表数据量分布、索引等,再结合单表的策略选择合适的关键字。
5. in 与 exists 的使用选择,一般情况下都是主查询的表数据量大于子查询表数据量时使用in,相反使用 exists 。
例如 b 表数据小于 a 表
select num from a where num in(select num from b)
a 表数据小于 b 表
select num from a where exists(select 1 from b where num=a.num)
6.模糊查询比全文查询效率更低。
7.应该尽量避免在where 字段中使用表达式或函数操作,这将导致引擎放弃索引搜索进行全表扫描。
8.不管是加不加索引group by 都比 distinct 快,因此使用的时候建议选 group by,尽量不要使用distinct。