Oracle-数据库优化
执行计划
数据库优化相关
在执行计划里面需要看什么内容
-
查看一个sql语句前后查询表格的顺序和逻辑
-
查看每一次对表的查询是否使用了分区和索引或者是全表查询
-
查看系统的cost资源消耗
-
每次运行查询结果的行数
-
每次查询结果得出的字节数大小
-
CPU计算的资源消耗
-
硬盘读写的资源消耗
-
每个句子运行的毫秒数时间
通过执行计划看到的表连接的内部逻辑
hash哈希连接
列和列的等值连接，通过某个列和某个列相等来进行表连接的时候，使用的是hash连接
当两表的数据量相差比较大的时候，即使一个表有索引，也是哈希连接
select * from emp a join dept b on a.deptno = b.deptno;
select * from emp a join dept b on a.job='CLERK' and a.deptno=b.deptno;
NL嵌套连接
列和列的不等值连接，通过两个列的不相等来进行表连接
连接的两张表
当数据量的区别不是很大的时候，连接的列有索引的情况下，这个时候列和列的等值连接是NL嵌套连接；
如果当两表的数据量相差比较大的时候，强制让大表先运行，一个表有索引的时候，此时是NL嵌套连接
select * from emp a join dept b on a.deptno != b.deptno;
select * from emp a join dept b on a.job='CLERK' and a.deptno!=b.deptno;
select * from emp_i a join dept_i b on a.deptno=b.deptno
merge join排序归并
列进行具体值的查询
select * from emp a join dept b on b.deptno=20;
select * from emp a join dept b on a.ename like 'S%';
强行更改表格查询的内部逻辑
优化器
使用优化器来更改： hints优化器
/*+ 优化器的内容 */放在select后面，放在列的前面
更改表连接的逻辑：
-
强制使用hash连接
/*+ use_hash(a b) */ -
强制使用nl连接
/*+ use_nl(a b) */ -
强制使用merge连接
/*+ use_merge(a b) */ -
强制改变表格读取的顺序
/*+ leading(a b) */a 小表：驱动表 b 大表：匹配表
-
**多线程：/*+ parallel(线程数量)*/
select /*+ parallel(8) */* from emp_more a join dept_i b on a.deptno=b.deptno; -
选择表格扫描的方式：
/*+ full(表名) */ -
强制更改索引
/*+ index(表名 索引名) */ -
在数据插入的时候，让数据默认都是直接追加到表格的末尾
insert /*+ append */ into dept_i values(50,'BIGDATA','SHENZHEN');
怎么去做数据库优化的？
-
首先查看表格的数据量，数据量如果超过了2000W就一定要去给表格设置分区
-
表格中的索引是否合理（表格一定要有主键，索引的数量（大表索引不能超过5个，小表索引不能超过列数量的15%））
-
sql语句里面有没有导致索引失效的情况
-
如果要做联合查询，先筛选数据，然后对筛选后的结果做表连接
-
如果表的数据量特别大，然后又需要做表连接，就需要将筛选结果存入到临时表，然后用临时表进行表连接
-
where查询的顺序，数据筛选量大的放在右边，筛选量小的放在左边
-
尽量先用where筛选然后再分组，having只对聚合结果筛选
-
尽量不要用select * from 表
-
尽量使用decode()去替代case when，函数计算效率更高
-
使用union all替代or，union all是并行
-
使用group by 替代distinct，分组比以行为单位筛选更快
-
使用大写的sql替代小写的sql，数据库本来就是要转换成大写再运行的
-
尽量少使用union minus intersect这三种集合运算，这三种集合会多一步sort unique的排序操作
-
使用exists()函数去替代in 包含语句
exists() 语法
select * from 原表 a where exists(select 1 from 原表 b where 查询条件);
--查询和SMITH岗位相同的其他员工信息
select * from emp where job in (select job from emp where ename='SMITH') and ename!='SMITH';
--结果上下是一样的，exists函数计算效率更高，用1来select是因为exists不关注查询的结果，只关心有没有数据，所以1可以尽可能减少需要反馈的信息
select * from emp a where exists(select 1 from emp b where b.ename='SMITH' and a.job=b.job) and ename!='SMITH';
