摘要
近段时间，面试官关于Sql优化的提问已经越来越多了，Sql优化可以说是已经成为了面试必备技能之一。本文从Sql语句、硬件设备以及Java程序三个方面详细的讲解关于Sql优化的技巧。
目录
一、Sql语句优化
1、避免使用 Select *
举个例子如下：
Select * From User Where rownum = 1
在写Sql的时候，我们为了方便会使用以上方式查询（rownum = 1 表示只取一条），但是直接使用Select * 会使索引无法产生作用。
举个例子，user表中有ID，Name，Age这三个字段。
Select ID From User Where ID = 1
此时ID为主键有索引的存在，如果 那么利用索引效率就会大大提高，但是如果还需要其他数据，例如：
Select ID,Name From User Where ID = 1
这时由于只有ID字段有建立索引，而获取Name字段则需要回到表内进行全表扫描，从而导致效率很低。
总结
Select * 不会走覆盖索引，会进行大量的回表操作，故而影响Sql运行效率。
2、使用（创建）索引
众所周知使用数据库的索引机制会大大提升Sql运行效率，那么如何正确使用索引！
2.1、不能在索引上使用计算操作
举个例子：
Select Name From User Where ID + 1= 9
在索引上使用计算操作时就会使索引失效！所以非必要尽量避免，如果业务需要一定需要在索引上计算，那么可以换一个写法，比如：
Select Name From User Where ID = 9 - 1
此时索引便不会失效，你可能会觉得（9-1）有什么意义呢？我直接写8不好吗？但是实际业务中对于单表的操作一般很少，针对于业务需要进行多表联查。比如之前接触的医院HIS系统，多时能有十几张表，这个时候就可以将 “9” 或者 “1” 替换成其他表的某两个字段，从而达到避免在索引上计算的效果。
2.2、不能在索引上使用函数
因为索引是在基于索引列的原始值建立的，而不是通过使用函数后的值建立的，所以在索引上使用函数会使索引失效。例如：
Select Name From User Where next(ID)
所以要尽量避免在索引上使用函数。但是如果非要使用，且使用频率较高调用函数固定的话可以考虑建立函数索引，需要的话去单独了解。
2.3、索引不是多多益善
不知道大家有没有这种疑问，如果索引很方便，那么我给所有字段都加上索引那么Select * 不是也可以大大提高运行效率呢？
这里就需要知道索引是如何建立产生的，为什么使用它会变快，这个内容比较多就不再这里一一赘述。简单来说就是索引建立也是需要时间的，你插入或者修改的时候导致索引变更都是需要耗费时间的，这个本身也是需要耗费时间影响效率的，所以索引不是多多益善的。
总结
尽量不要对索引列进行操作，并且索引的建立也不是越多越好。
3、用 UNION All 避免 UNION
UNION All 与 UNION 的区别在于，UNION 会自动去掉重复内容。所以业务中没有要求不能出现重复数据时，尽量使用UNION All。UNION 去重时会遍历、排序和比较从而影响效率。
4、EXISTS() 和 IN()
这两个子句的功能都是差不多的，那么什么情况要选择要选择怎么样的子句呢？这里就涉及到另外一个优化方式，小表驱动大表。
先说结论：
（1）、当子句中子表的数据量小于主表的数据量时，使用IN()
（2）、当子句中子表的数据量大于主表的数据量时，使用EXISTS()
（3）、当子表与主表数据量差不多时，两个子句的执行效率是差不多的
举个例子，假设 Person表中数据有10000条数据，User表中有1000条数据：
1、Select Name From User
Where Name IN(Select Name From Person)
2、Select Name From User u
Where EXISTS(Select 1 From Person Where Name = u.Name)
使用 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: 3000003、利用缓存机制
合理的利用缓存机制也可以避免与数据库产生通信连接，对于一些固定的或者很少调休的数据，可以在进行查询之后保存起来。再没有执行Insert、Update以及Delete的情况下，在每次第一次查询过后储存起来，之后再查询就从缓存中获取。
4、利用异步处理
对于一些耗时的Sql语句，可以使用异步方法去执行。将数据库操作放到后台线程中执行，提高系统的并发能力和性能。
标签：Name,索引,使用,面试,User,Sql,必刷,ID,Select From： https://blog.csdn.net/qq_65225921/article/details/142789946