数据库性能优化总结

最新推荐文章于 2024-05-31 09:47:29 发布

科比可比克

最新推荐文章于 2024-05-31 09:47:29 发布

阅读量141

点赞数

本文链接：https://blog.csdn.net/qq_24884885/article/details/105093593

版权

3 月，跳不动了？>>>

数据库性能优化四个原则：

一、减少数据访问（减少磁盘访问）

1、创建并正确使用索引

         SQL什么条件会使用索引？
        当字段上建有索引时，通常以下情况会使用索引：
        INDEX_COLUMN = 、> 、>= 、< 、<= 、?between ? and ?、in (?,?,...,?)、like ?||'%'（后导模糊查询）
        T1. INDEX_COLUMN=T2. COLUMN1（两个表通过索引字段关联）
        SQL什么条件不会使用索引？
        <> not in (?,?,...,?)
        function(INDEX_COLUMN) = ?
        INDEX_COLUMN + 1 = ?
        INDEX_COLUMN || 'a' = ?
        经过普通运算或函数运算后的索引字段不能使用索引
        INDEX_COLUMN like '%'||?
        INDEX_COLUMN like '%'||?||'%'
        含前导模糊查询的Like语法不能使用索引
        INDEX_COLUMN is null
        B-TREE索引里不保存字段为NULL值记录，因此IS NULL不能使用索引
        NUMBER_INDEX_COLUMN='12345'
        CHAR_INDEX_COLUMN=12345
        Oracle在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。
        a.INDEX_COLUMN=a.COLUMN_1
        给索引查询的值应是已知数据，不能是未知字段值。
        注：经过函数运算字段的字段要使用可以使用函数索引，这种需求建议与DBA沟通。
有时候我们会使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不能使用索引

        我们一般在什么字段上建索引？
        这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：
    1、字段出现在查询条件中，并且查询条件可以使用索引；
    2、语句执行频率高，一天会有几千次以上；

    以下是一些字段是否需要建的经验分类：
         需要建索引的字段
        主键、外键、有标识意义字段

        长字符不适合建索引的字段、描述备注、大字段
        如何知道SQL是否使用了正确的索引？
        简单SQL可以根据索引使用语法规则判断，复杂的SQL不好办，判断SQL的响应时间是一种策略，但是这会受到数据量、主机负载及缓存等因素的影响，有时数据全在缓存里，可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用，需要到数据库中查看SQL真实的执行计划，这个话题比较复杂，详见SQL执行计划专题介绍。

        索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少？这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关，以下是一个普通表测试数据，仅供参考：
        索引对于Insert性能降低56%
        索引对于Update性能降低47%
        索引对于Delete性能降低29%
        因此对于写IO压力比较大的系统，表的索引需要仔细评估必要性，另外索引也会占用一定的存储空间。

2、只通过索引访问

        有些时候，我们只是访问表中的几个字段，并且字段内容较少，我们可以为这几个字段单独建立一个组合索引，这样就可以直接只通过访问索引就能得到数据，一般索引占用的磁盘空间比表小很多，所以这种方式可以大大减少磁盘IO开销。如：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表。
        还是拿字典举例：有一个需求，需要查询一本汉语字典中所有汉字的个数，如果我们的字典没有目录索引，那我们只能从字典内容里一个一个字计数，最后返回结果。如果我们有一个拼音目录，那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页，拼音目录有20页，那我们的数据访问成本相当于全表访问的50分之一。
        切记，性能优化是无止境的，当性能可以满足需求时即可，不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里，所以这种只通过索引访问数据的方法一般只用于核心应用，也就是那种对核心表访问量最高且查询字段数据量很少的查询。

二、减少数据返回（减少网络传输和磁盘访问）

1、对数据分页处理

客户端(应用程序或浏览器)分页

        将数据从应用服务器全部下载到本地应用程序或浏览器，在应用程序或浏览器内部通过本地代码进行分页处理
        优点：编码简单，减少客户端与应用服务器网络交互次数
        缺点：首次交互时间长，占用客户端内存
        适应场景：客户端与应用服务器网络延时较大，但要求后续操作流畅，如手机GPRS，超远程访问（跨国）等等。

应用服务器分页

将数据从数据库服务器全部下载到应用服务器，在应用服务器内部再进行数据筛选。以下是一个应用服务器端Java程序分页的示例：List list=executeQuery(“select * from employee order by id”);

        Int count= list.size();List subList= list.subList(10, 20);

        优点：编码简单，只需要一次SQL交互，总数据与分页数据差不多时性能较好。
        缺点：总数据量较多时性能较差。
        适应场景：数据库系统不支持分页处理，数据量较小并且可控。

数据库SQL分页

        采用数据库SQL分页需要两次SQL完成
        一个SQL计算总数量
        一个SQL返回分页后的数据
        优点：性能好
        缺点：编码复杂，各种数据库语法不同，需要两次SQL交互。

2、只返回需要的数据、避免使用select *

三、减少交互次数（减少网络传输）

1、 batch DML

数据库访问框架一般都提供了批量提交的接口，jdbc支持batch的提交处理方法，当你一次性要往一个表中插入1000万条数据时，如果采用普通的executeUpdate处理，那么和服务器交互次数为1000万次，按每秒钟可以向数据库服务器提交10000次估算，要完成所有工作需要1000秒。如果采用批量提交模式，1000条提交一次，那么和服务器交互次数为1万次，交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO，但是会大大减少客户端与服务端的交互次数，从而减少了多次发起的网络延时开销，同时也会降低数据库的CPU开销。

2、in list

对于一次查询多个id的情况尽量用in，而不是循环多次去数据库查询。可以减少与数据库交互。

3、设置fetch size

当我们采用select从数据库查询数据时，数据默认并不是一条一条返回给客户端的，也不是一次全部返回客户端的，而是根据客户端fetch_size参数处理，每次只返回fetch_size条记录，当客户端游标遍历到尾部时再从服务端取数据，直到最后全部传送完成。所以如果我们要从服务端一次取大量数据时，可以加大fetch_size，这样可以减少结果数据传输的交互次数及服务器数据准备时间，提高性能。

4、使用存储过程

存储过程可以提高性能，但是同样存在缺点，不可移植性，学习成本高，业务逻辑多处存在不方便后期维护。普通业务逻辑尽量不要使用存储过程，定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。

5、优化业务逻辑，少连接数据库

连表操作操作时尽量数量少的表作为主表，

四、减少服务器及cpu开销

1、使用绑定变量

         绑定变量是指SQL中对变化的值采用变量参数的形式提交，而不是在SQL中直接拼写对应的值。
        非绑定变量写法：Select * from employee where id=1234567
        绑定变量写法：
        Select * from employee where id=? Preparestatement.setInt(1,1234567)

        Java中Preparestatement就是为处理绑定变量提供的对像，绑定变量有以下优点：
        1、防止SQL注入
        2、提高SQL可读性
        3、提高SQL解析性能，不使用绑定变更我们一般称为硬解析，使用绑定变量我们称为软解析。