转载:https://blog.csdn.net/weixin_39106371/article/details/82117148
数据库优化的五个层次
1、 减少数据访问(减少磁盘访问)
2、 返回更少数据(减少网络传输或磁盘访问)
3、 减少交互次数(减少网络传输)
4、 减少服务器CPU开销(减少CPU及内存开销)
5、 利用更多资源(增加资源)
数据库优化方向
1:减少数据访问
1.1创建并正确使用索引
索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销,正确的索引可以让性能提升100,1000倍以上,不合理的索引也可能会让性能下降100倍,因此在一个表中创建什么样的索引需要平衡各种业务需求。
B-TREE索引的简单介绍:
B-TREE索引也称为平衡树索引(Balance Tree),它是一种按字段排好序的树形目录结构,主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。就是一个平衡二叉树。
1.2只通过索引访问数据
如:select id,name from company where type='2';
如果这个SQL经常使用,我们可以在type,id,name上创建组合索引
create index my_comb_index on company(type,id,name);
有了这个组合索引后,SQL就可以直接通过my_comb_index索引返回数据,不需要访问company表
1.31.3、优化SQL执行计划
2、返回更少的数据
2.1数据分页处理,在数据里查找返回一部分而不是全部数据。
分页操作的三个入口,数据库、服务器、和客户端
数据库分页分两部一个sql计算总量,一个返回分页后的数据
2.2只返回需要的字段
通过去除不必要的返回字段可以提高性能,例:
调整前:select * from product where company_id=?;
调整后:select id,name from product where company_id=?;
优点:
1、减少数据在网络上传输开销
2、减少服务器数据处理开销
3、减少客户端内存占用
4、字段变更时提前发现问题,减少程序BUG
5、如果访问的所有字段刚好在一个索引里面,则可以使用纯索引访问提高性能。
缺点:增加编码工作量
如果你的查询表中有大字段或内容较多的字段,如备注信息、文件内容等等,那在查询表时一定要注意这方面的问题,否则可能会带来严重的性能问题。如果表经常要查询并且请求大内容字段的概率很低,我们可以采用分表处理,将一个大表分拆成两个一对一的关系表,将不常用的大内容字段放在一张单独的表中。如一张存储上传文件的表:
T_FILE(ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT)
我们可以分拆成两张一对一的关系表:
T_FILE(ID,FILE_NAME,FILE_SIZE,FILE_TYPE)
T_FILECONTENT(ID, FILE_CONTENT)
通过这种分拆,可以大大提少T_FILE表的单条记录及总大小,这样在查询T_FILE时性能会更好,当需要查询FILE_CONTENT字段内容时再访问T_FILECONTENT表。
3、减少交互次数
3.1batch dml 批处理
用batch操作一般不会减少很多数据库服务器的物理IO,但是会大大减少客户端与服务端的交互次数,从而减少了多次发起的网络延时开销,同时也会降低数据库的CPU开销。nsert操作加大Batch可以对性能提高近8倍性能,一般根据主键的Update或Delete操作也可能提高2-3倍性能,但不如Insert明显,因为Update及Delete操作可能有比较大的开销在物理IO访问。以上仅是理论计算值,实际情况需要根据具体环境测量。
3.2 in list的使用
很多时候我们需要按一些ID查询数据库记录,我们可以采用一个ID一个请求发给数据库,如下所示:
for :var in ids[] do begin
select * from mytable where id=:var;
end;
我们也可以做一个小的优化, 如下所示,用ID INLIST的这种方式写SQL:
select * from mytable where id in(:id1,id2,...,idn);
通过这样处理可以大大减少SQL请求的数量,从而提高性能。那如果有10000个ID,那是不是全部放在一条SQL里处理呢?答案肯定是否定的。首先大部份数据库都会有SQL长度和IN里个数的限制,如ORACLE的IN里就不允许超过1000个值。
3.3设置fetch-size
当我们采用select从数据库查询数据时,数据默认并不是一条一条返回给客户端的,也不是一次全部返回客户端的,而是根据客户端fetch_size参数处理,每次只返回fetch_size条记录,当客户端游标遍历到尾部时再从服务端取数据,直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时,可以加大fetch_size,这样可以减少结果数据传输的交互次数及服务器数据准备时间,提高性能
注意,fetchsize不能设置太大,如果一次取出的数据大于JVM的内存会导致内存溢出,所以建议不要超过1000,太大了也没什么性能提高,反而可能会增加内存溢出的危险。
3.4使用存储过程
合理的利用存储过程也可以提高系统性能。如你有一个业务需要将A表的数据做一些加工然后更新到B表中,但是又不可能一条SQL完成,这时你需要如下3步操作:
a:将A表数据全部取出到客户端;
b:计算出要更新的数据;
c:将计算结果更新到B表。
如果采用存储过程你可以将整个业务逻辑封装在存储过程里,然后在客户端直接调用存储过程处理,这样可以减少网络交互的成本。
普通业务逻辑尽量不要使用存储过程,定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。
3.5优化业务逻辑
看程序员的了。
4减少数据库服务器的运算
4。1使用绑定数据
绑定变量是指SQL中对变化的值采用变量参数的形式提交,而不是在SQL中直接拼写对应的值。
非绑定变量写法:Select * from employee where id=1234567
绑定变量写法:
Select * from employee where id=?
Preparestatement.setInt(1,1234567)
4.2合理使用排序
大记录集排序不仅增加了CPU开销,而且可能会由于内存不足发生硬盘排序的现象,当发生硬盘排序时性能会急剧下降,
4.3大量复杂运算放到客户端
5、利用跟更多资源
5.1客户端多进程访问
多进程并行访问是指在客户端创建多个进程(线程),每个进程建立一个与数据库的连接,然后同时向数据库提交访问请求。当数据库主机资源有空闲时,我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时,采用多进程并行访问性能不会提高,反而可能会更慢。所以使用这种方式最好与DBA或系统管理员进行沟通后再决定是否采用。
5.2数据库并行处理
数据库并行处理是指客户端一条SQL的请求,数据库内部自动分解成多个进程并行处理。
并不是所有的SQL都可以使用并行处理,一般只有对表或索引进行全部访问时才可以使用并行。数据库表默认是不打开并行访问,所以需要指定SQL并行的提示,如下所示:
select /*+parallel(a,4)*/ * from employee;
SQL语句注意的问题:
在这些where子句中,即使某些列存在索引,但是由于编写了劣质的SQL,系统在运行该SQL语句时也不能使用该索引,而同样使用全表扫描,这就造成了响应速度的极大降低。
in 和not in使用EXISTS STS 和NOT EXISTS代替
IS NULL 或IS NOT NULL操作。用其它相同功能的操作运算代替,如:a is not null 改为 a>0 或a>’’等。不允许字段为空,而用一个缺省值代替空值,如申请中状态字段不允许为空,缺省为申请。
> 及 < 操作符(大于或小于操作符)尽量使用>= 或<=
IKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。
UNION操作符
select * from gc_dfys
union
select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys
对于有联接的列,即使最后的联接值为一个静态值,优化器是不会使用索引的
select * from employss where first_name||''||last_name ='Beill Cliton';
上面这条语句完全可以查询出是否有Bill Cliton这个员工,但是这里需要注意,系统优化器对基于last_name创建的索引没有使用。当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。
*** where first_name ='Beill' and last_name ='Cliton';
同一功能同一性能不同写法SQL的影响,如果将SQL的字符串及格式写得完全相同,则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。
WHERE后面的条件顺序影响
查询表顺序的影响
用IN来替换OR
这是一条简单易记的规则,但是实际的执行效果还须检验,在ORACLE8i下,两者的执行路径似乎是相同的.
低效:
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效
SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);