在项目维护过程中,经常会遇到一些因为数据库查询速度慢导致页面加载缓慢的问题。这种情况下我们主要考虑SQL优化,通常SQL优化的目的就是减少语句执行时间,提高代码的执行效率。
面对执行时间较长的SQL语句,主要有以下几种思路:
1、表作为筛选条件时,用EXISTS代替JOIN
-- 修改前
select A.* from A
inner join B
on A.id = B.id
-- 修改后
select * from A
where exists (
select * from B
where A.id = B.id
)
2、连接多个表但只使用了一个字段,可将JOIN变成SELECT。
-- 修改前
select A.*, B.name from A
inner join B
on A.id = B.id
-- 修改后
select A.*, (select name from B where B.id = A.id) name
from A
3、将复杂SQL拆解成多个简单的SQL,降低代码复杂度,提高执行效率。
-- 修改前
select * from A
inner join B
on A.id = B.id
where A.createtime > getdate() - 3
and exists (
select * from C
where A.gid = C.gid
and enabled = 1
)
and exists (
select * from D
where A.pid = D.gid
)
and not exists (
select * from E
where A.id = E.id
and E.createtime > A.createtime
)
-- 修改后
select *
into #temp1
from A
inner join B
on A.id = B.id
where A.createtime > getdate() - 3
select * into #temp2 from #temp1 A
where exists (
select * from C
where A.gid = C.gid
and enabled = 1
)
select * into #temp3 from #temp2 A
where exists (
select * from D
where A.pid = D.gid
)
select * into #tt from #temp3 A
where not exists (
select * from E
where A.id = E.id
and E.createtime > A.createtime
)
4、连接表时可以将大表变为小表,先筛选一部分数据再连接其他的表。
-- B表 10000000行
-- 修改前
select * from A
inner join B
on A.id = B.id
-- 修改后
select A.*, C.cnt from A
inner join (
select * from B
where B.enabled = 1
and B.createtime > getdate() - 7
) C
on A.id = C.id
5、尽量命中索引,如果无法命中可选择强制使用索引。
查看SQL的执行计划,对于表中已存在的索引,使查询尽可能命中索引,如果无法命中可选择强制使用索引。
-- 修改前
select * from T_A A
where A.id not in (121312, 1241415, 5322123)
and A.createtime >= TO_DATE('2022-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and A.createtime <= TO_DATE('2022-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + 1
and exists (
select * from T_B B
where A.id = B.id
and A.createtime >= B.createtime
)
-- 修改后
select /*+index(A INDEX_CREATETIME) */ * from T_A A
where A.id not in (121312, 1241415, 5322123)
and A.createtime >= TO_DATE('2022-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and A.createtime <= TO_DATE('2022-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + 1
and exists (
select /*+index(B INDEX_ID) */ * from T_B B
where A.id = B.id
and A.createtime >= B.createtime
)
6、如果使用的字段没有索引,对执行时间较长的部分字段建立索引。
对于表中不存在的索引的字段,根据执行计划的提示,对执行时间较长的部分字段建立索引。