Sql优化技巧总结

使用 IN() 子句时，是User表驱动Person表，先查出Person表的数据存入内存中，再去查User表中的数据，这样子执行最低效率是Person.length * User.length。
而使用Exists() 子句时，它并不关心对于Person表中数据的结果集，只关心存不存在。并且首先针对User表执行查询，所以Exists() 子句最多只会执行User.length次。这样子执行最低效率是User.length * User.length。

综上所述，当Where子句中的子表数据相较于主表较小时，使用IN() 子句的话效率更高。想要了解更多，后续可以在单开一章，这里就不一一赘述了。

5、使用连接查询代替子查询

通过使用连接查询和子查询的方式，都能达到查询多表关联数据的目的。

举个例子：

子查询：

        Select P.Name,(Select DepNa From Dept Where P.ID = Pid) From Person P

连接：

        Select

                P.Name，D.DepNa

        From

        Person P

          Left Join Dept D on P.ID = D.Pid

通过以上语句进行子查询时，针对于Person表中的每一条数据都需要去Dept表中去进行遍历查找，所以除了对于Person的遍历外，还需要多次访问Dept表进行遍历时间复杂度较高。

而使用Left Join进行关联时，会先将Person表与Dept表均遍历一次关联起来，而不会多次对Dept表进行遍历效率大大提高。

综上所述，如果使用连接和子查询都可以达到业务需求，那么优先考虑使用连接的方式，效率会更高。

6、多使用 limt/rownum 这种限制条目数的条件

假如现在业务需要获取到某用户的最新的订单，举个例子：

Select * From Order Where User_ID = 111 And Order By Insert_Time Desc

通过以上方式获取Order表中D为111用户的所有订单，并根据插入时间排序从而获取最新的订单。

Select * From Order Where User_ID = 111 And Order By Insert_Time Desc And Rownum = 1

通过增加限制结果集的关键字，从而只获取到需要的一条数据，进而大大提高Sql语句的运行效率。

7、提高group by having的运行效率

group by和having配合使用，可以对结果集进行分组和过滤。举个例子：

Select User_Name,User_ID From Order

Group By User_ID

Having User_ID < 100

根据用户进行分组并筛选出ID小于100的用户。以上Sql可以实现功能但是还有优化空间。在运行此Sql时，会先查询出所有的订单号，然后再进行分组和过滤。这个时候就要考虑Having的过滤可不可以再查询时实现。举个例子：

Select User_Name,User_ID From Order

Where User_ID < 100

Group By User_ID

此时就会在查询时就会过滤结果集，使得分组时的数据量减少从而提高效率。

8、使用 UNION All 代替 or

Sql1使用or：

Select User_Name From User Where User_ID = 10081 or Salary = 10000.00

Sql2使用union all :

Select User_Name From User Where User_ID = 10081

Union all

Select User_Name From User Where Salary = 10000.00

使用or时可能会导致索引失效从而进行全表扫描，运行Sql语句1时如果走了User_ID的索引但是Salary还要进行全表扫描。不如使用Sql语句2直接一次全表扫描获得结果。

9、合理的定义字段类型

在数据库中定义字符串类型的字段时。如果不确定该字段的长度并且长度也并不固定时，选择varchar要优于char。

char是固定长度的字符串，假设定义长度为255，但是实际长度为25该字段占用空间依旧为255。而varchar是可变长度的字符串，只会定义最大长度，会根据存储内容来变化长度。从而节省空间。而且对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

我们在选择字段类型时，应该遵循这样的原则：

1、能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。

2、尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。

3、长度固定的字符串字段，用char类型。

4、长度可变的字符串字段，用varchar类型。

5、金额字段用decimal，避免精度丢失问题。

等等。。。

二、设备硬件优化

在硬件层面，CPU和内存的性能对SQL执行速度有直接影响。如果CPU或内存资源不足，可能导致SQL执行卡顿或延迟。在这种情况下，可以考虑升级硬件设备，增加CPU核数或扩展内存容量，以提高系统性能。例如：

升级CPU：选择高性能的CPU，特别是多核处理器，以提高并发处理能力。较高的CPU频率和更多的核心数可以加快SQL查询和计算速度。
增加内存容量：增加内存可以减少频繁的磁盘访问，提高缓存命中率，从而加快数据读取和写入速度。
选择高速存储设备：使用固态硬盘（SSD）替代传统机械硬盘可以大幅提升IO性能，加快数据的读写速度。
提高网络带宽：优化数据库服务器与客户端之间的网络连接，确保网络带宽充足，减少网络延迟对数据库操作的影响。
调整操作系统参数：针对数据库工作特性，调整操作系统的参数如文件描述符、最大进程数等，以提高数据库性能。

三、代码块优化（Java）

1、批量插入

当需要往某张表中批量插入数据时：

public void addUser(List<User> userList ) {
        for (User user : userList) {
            userMapper.insert(user);
        }
    }

使用以上代码可以实现功能，但是由于在Java程序中每次运行Sql语句都需要与数据库建立连接，所以当数据量过大时使用此方法会增加不需要的性能消耗。可以自定义一个的批量插入的方法来实现功能。

userMapper.insertAll(userList);

<insert id="insertAll">
    INSERT INTO orders (user_id, user_name, salary)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.user_id}, #{user.user_name}, #{user.salary})
    </foreach>
</insert>

这样的话只需要与数据库产生一次连接通信就可以实现批量插入的功能，从而大大提高效率。但是如果表字段过多且数据量过大，那么可能会导致内存溢出。这是因为批量插入时，所有数据都需要加载到内存中，然后一次性发送给数据库。如果数据量非常大，可能会超出 JVM 的堆内存限制，从而导致内存溢出。所以可以对每一次通信的数据量进行限制例如每插入600条数据与数据库进行一次连接通信。

2、使用连接池技术

频繁地创建和关闭数据库连接，会增加系统开销。如果使用数据库连接池可以管理连接，提高连接的复用性和性能。以下配置连接池的示例：

spring:
  datasource:
    url: jdbc:oracle:thin:@ IP:port:bsrun
    username: 
    password: 
    driver-class-name: oracle.jdbc.OracleDriver
    druid:
      # 连接池的配置信息
      # 初始化大小，最小，最大
      initial-size: 5
      min-idle: 5
      maxActive: 20
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 300000