关于SQL优化的日常整理:
(目前先整理这么多,后期继续完善)
-
添加索引(最有效的方式之一)
1). 什么时候考虑添加索引?
场景:针对数据量特别大的时候(如上千万条数据) ; 查询频率高(查询次数较多的时候);
2). 针对什么样的字段添加索引?
where条件后面跟着的字段; 数据波动范围大的字段; 数据范围波动小的, 可以考虑使用oracle的位图索引; 如: 性别, 状态.
3). 数据库表的索引是否越多越好 ?
索引也不是越多越好; 太多反而影响 insert , update 的效率; 关于索引的使用应该注意: 1)数据量小的表不需要建立索引,因为数据量小的表即使创建索引也不会有太大的用处,还会增加额外的索引开销 2)不经常使用的列不要建立索引,没意义 3)经常更新比较频繁的列不要建立索引,会影响insert , update 的效率
4). 索引的使用规则?
EXPLAIN 解释select 的执行计划 (mysql);
-
select 语句中 尽量不适用 select * from…, 使用具体的字段代替 select name , age from …;
—> select * , * 代表所有的列, 不同的表, 列也不同, 需要解析列, 会查找所有的字段. 效率低. -
where 和 having, where子句中不能跟组函数. 当使用where 和 having 都可以使用时, 尽量使用where.
1). select deptno, avg(sal) from emp where deptno = 10 group by deptno; —> 先过滤,后分组, 分组的对象数据少, 效率高.
2). select deptno, avg(sal) from emp group by deptno having deptno = 10; —> 先分组后过滤.
-
如果需要查询多张表, 可以使用子查询, 也可以使用多表查询时, 理论上,尽量使用多表查询 —> 因为性能高.
因为子查询, 对数据操作两次或多次, 而多表查询则只操作一次, 所以效率高---------> 不考虑笛卡尔积的情况下.
但是,如果表中的数据太多, 多表产生的笛卡尔积太大, 会影响多表查询的效率.
-
尽量不使用集合运算.
参与运算的集合越多, 运行效率越低. UNION(并集) , UNION ALL(全并集) , INTERCEPT(交集) , MINUS(差集)
-
尽量不适用in操作符.
ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采
用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程
在业务密集的SQL当中尽量不采用IN操作符,用 EXISTS 方案代替。
-
强烈不建议使用 not in 操作符.
此操作是强列不推荐使用的,因为它不能应用表的索引 ; 可以使用not exists来替代.
-
IS NULL 或IS NOT NULL操作(判断字段是否为空)
判断字段是否为空一般是不会应用索引的,因为索引是不索引空值的 ;
用其它相同功能的操作运算代替,如:a is not null 改为 a>0 或a>’’等。不允许字段为空,
-
LIKE的模糊查询如果用的不好也会降低性能.
LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,
如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE '5400%'则会使用索引
-
避免使用耗费资源的操作,带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行,耗费资源的排序(SORT)功能.
-
在WHERE 语句中,尽量避免对索引字段进行计算操作
避免在索引字段上使用not,<>,!= 避免在索引列上使用IS NULL和IS NOT NULL 避免在索引列上出现数据类型转换 避免在索引字段上使用函数 避免建立索引的列中使用空值
-
尽量使用预编译的SQL;
因为预编译的SQL语句, 发送给数据库时包含两个部分 : 1). sql语句 ; 2).参数信息;
sql语句编译之后, 会将其缓存在数据库中, 下一次再次请求该语句时, 不会再执行编译的过程.*