数据库性能优化

来源：互联网发布：sql 分段求和编辑：程序博客网时间：2024/04/27 15:29

A：索引的使用

--数据库的执行效率很大程度上取决余索引的建立,具体建索引原则,很多文章都有介绍,略.
--主要介绍一些用SQL语句建索引的方法，和维护索引的方法：

/*
建索引
*/
--建普通索引
create index 索引名称 on 表名(字段)
--建聚集索引
create clustered index 索引名称 on 表名(字段)
--建非聚集索引
create NONCLUSTERED index 索引名称 on 表名(字段)

/*
删除索引
*/
drop index 表名.索引名称

/*
使用索引
*/

情况1：
如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

情况2：
使用索引查询:
select * from 表名(index =索引名) where 索引字段=50100

B：连接查询没有子查询效率高

大量的左查询、连接效率没有子查询效率高！

--案例一:
存储过程如下:
CREATE     PROCEDURE P_TEST @Date INT
AS
SELECT
F2_A001,
F15_A001,
SplitStyle = F11_A074,
[Schema] = Isnull('规则一' + Convert(varchar(100), SongGu) + '单位一; ', '')
         + Isnull('规则二+ Convert(varchar(100), XianJin) + '单位二; ', '')
         + Isnull('规则三 + Convert(varchar(100), SuoGu / 10.0) + '单位三; ', '')
         + Isnull('规则四+ Convert(varchar(100), RenGou) + '单位四; ', '')
         + Isnull('规则六+ Convert(varchar(100), RenGu) + '单位五; ', '')
         + Isnull('规则七' + Convert(varchar(100), ZhiGongGu) + '单位六; ', '')
         + Isnull('规则八' + Convert(varchar(100), GFenHong) + '单位七; ', '')
         + Isnull('规则九' + Convert(varchar(100), GSongGu) + '单位八; ', '')
FROM (
SELECT DISTINCT A.F2_A001, A.F15_A001, F11_A074 = (SELECT TOP 1 F11_A074 FROM A_074 C WHERE F1_A074 = B.F1_A074 AND F18_A074 / 100 = B.F18_A074 / 100 ORDER BY F18_A074 DESC),
SongGu    = ISNULL(ISNULL(A1.F14_A075,A3.F14_A075),A2.F14_A075),
XianJin   = ISNULL(ISNULL(B1.F14_A075,B3.F14_A075),B2.F14_A075),
SuoGu     = ISNULL(ISNULL(I1.F25_A075,I3.F25_A075),I2.F25_A075),
RenGou    = ISNULL(ISNULL(C1.F25_A075,C3.F25_A075),C2.F25_A075),
RenGu     = ISNULL(ISNULL(D1.F25_A075,D3.F25_A075),D2.F25_A075),
ZhiGongGu = ISNULL(ISNULL(J1.F14_A075,J3.F25_A075),J2.F25_A075),
GFenHong = ISNULL(ISNULL(F1.F10_A075,F3.F25_A075),F2.F25_A075),
GSongGu   = ISNULL(ISNULL(G1.F14_A075,G3.F25_A075),G2.F25_A075)
from a_001 a INNER JOIN a_074 B
ON A.OID_A001 = B.F1_A074 AND F18_A074/100= @Date
left join A_075 A1 on A1.F1_A075=a.OID_A001 AND A1.F3_A075 = '4A1'
left join A_075 A3 on A3.F1_A075=a.OID_A001 AND A3.F3_A075 = '4A3'
left join A_075 A2 on A2.F1_A075=a.OID_A001 AND A2.F3_A075 = '4A2'

left join A_075 B1 on B1.F1_A075=a.OID_A001 AND B1.F3_A075 = '4B1'
left join A_075 B3 on B3.F1_A075=a.OID_A001 AND B3.F3_A075 = '4B3'
left join A_075 B2 on B2.F1_A075=a.OID_A001 AND B2.F3_A075 = '4B2'

left join A_075 I1 on I1.F1_A075=a.OID_A001 AND I1.F3_A075 = '4I1'
left join A_075 I3 on I3.F1_A075=a.OID_A001 AND I3.F3_A075 = '4I3'
left join A_075 I2 on I2.F1_A075=a.OID_A001 AND I2.F3_A075 = '4I2'

left join A_075 C1 on C1.F1_A075=a.OID_A001 AND C1.F3_A075 = '4C1'
left join A_075 C3 on C3.F1_A075=a.OID_A001 AND C3.F3_A075 = '4C3'
left join A_075 C2 on C2.F1_A075=a.OID_A001 AND C2.F3_A075 = '4C2'

left join A_075 D1 on D1.F1_A075=a.OID_A001 AND D1.F3_A075 = '4D1'
left join A_075 D3 on D3.F1_A075=a.OID_A001 AND D3.F3_A075 = '4D3'
left join A_075 D2 on D2.F1_A075=a.OID_A001 AND D2.F3_A075 = '4D2'

left join A_075 J1 on J1.F1_A075=a.OID_A001 AND J1.F3_A075 = '4J1'
left join A_075 J3 on J3.F1_A075=a.OID_A001 AND J3.F3_A075 = '4J3'
left join A_075 J2 on J2.F1_A075=a.OID_A001 AND J2.F3_A075 = '4J2'

left join A_075 F1 on F1.F1_A075=a.OID_A001 AND F1.F3_A075 = '4F1'
left join A_075 F3 on F3.F1_A075=a.OID_A001 AND F3.F3_A075 = '4F3'
left join A_075 F2 on F2.F1_A075=a.OID_A001 AND F2.F3_A075 = '4F2'

left join A_075 G1 on G1.F1_A075=a.OID_A001 AND G1.F3_A075 = '4G1'
left join A_075 G3 on G3.F1_A075=a.OID_A001 AND G3.F3_A075 = '4G3'
left join A_075 G2 on G2.F1_A075=a.OID_A001 AND G2.F3_A075 = '4G2'

) T

--数据量情况
select * from a_074
记录数:730
select * from a_001
记录数:2424
select * from a_075
记录数:30028
--执行后,需要耗费时间为: 1分钟48秒,经常查询超时

分析消耗资源的点：
--1：从存储过程中可以看到,查询用到了很多左连接,左连接消耗了大量资源
--2：查询条件中用到变量,要导致全表扫描,消耗大量资源

--解决办法：
--1:把所有左连接更改为子查询
--2:把查询条件,强制指定索引

步骤：

A:改为子查询如下：

CREATE     PROCEDURE P_TEST @Date INT
AS
SELECT
F2_A001,
F15_A001,
SplitStyle = F11_A074,
[Schema] = Isnull('规则一' + Convert(varchar(100), SongGu) + '单位一; ', '')
          + Isnull('规则二+ Convert(varchar(100), XianJin) + '单位二; ', '')
          + Isnull('规则三 + Convert(varchar(100), SuoGu / 10.0) + '单位三; ', '')
          + Isnull('规则四+ Convert(varchar(100), RenGou) + '单位四; ', '')
          + Isnull('规则六+ Convert(varchar(100), RenGu) + '单位五; ', '')
          + Isnull('规则七' + Convert(varchar(100), ZhiGongGu) + '单位六; ', '')
          + Isnull('规则八' + Convert(varchar(100), GFenHong) + '单位七; ', '')
          + Isnull('规则九' + Convert(varchar(100), GSongGu) + '单位八; ', '')
FROM (
SELECT DISTINCT A.F2_A001, A.F15_A001, F11_A074 = (SELECT TOP 1 F11_A074 FROM A_074 C WHERE F1_A074 = B.F1_A074 AND F18_A074 / 100 = B.F18_A074 / 100 ORDER BY F18_A074 DESC),
SongGu    = ISNULL(ISNULL((SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4A1'), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4A3')), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4A2')),
XianJin   = ISNULL(ISNULL((SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4B1'), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4B3')), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4B2')),
SuoGu     = ISNULL(ISNULL((SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4I1'), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4I3')), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4I2')),
RenGou    = ISNULL(ISNULL((SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4C1'), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4C3')), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4C2')),
RenGu     = ISNULL(ISNULL((SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4D1'), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4D3')), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4D2')),
ZhiGongGu = ISNULL(ISNULL((SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4J1'), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4J3')), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4J2')),
GFenHong = ISNULL(ISNULL((SELECT NULLIF(F10_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4F1'), (SELECT NULLIF(F10_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4F3')), (SELECT NULLIF(F10_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4F2')),
GSongGu   = ISNULL(ISNULL((SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4G1'), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4G3')), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4G2'))
from a_001 a INNER JOIN a_074 B
ON A.OID_A001 = B.F1_A074 AND F18_A074/100= @SEQ
) T

B:建索引
1：为表a_074 建聚集索引
create clustered index Index_a_074_F18_A074 on a_074(F18_A074)
2：为表A_075建非聚集索引
create NONCLUSTERED index Index_A_075_F3_A075 on A_075(F3_A075)

强制在查询条件使用索引后的存储过程如下：

CREATE     PROCEDURE P_TEST @Date INT
AS
SELECT
F2_A001,
F15_A001,
SplitStyle = F11_A074,
[Schema] = Isnull('规则一' + Convert(varchar(100), SongGu) + '单位一; ', '')
          + Isnull('规则二+ Convert(varchar(100), XianJin) + '单位二; ', '')
          + Isnull('规则三 + Convert(varchar(100), SuoGu / 10.0) + '单位三; ', '')
          + Isnull('规则四+ Convert(varchar(100), RenGou) + '单位四; ', '')
          + Isnull('规则六+ Convert(varchar(100), RenGu) + '单位五; ', '')
          + Isnull('规则七' + Convert(varchar(100), ZhiGongGu) + '单位六; ', '')
          + Isnull('规则八' + Convert(varchar(100), GFenHong) + '单位七; ', '')
          + Isnull('规则九' + Convert(varchar(100), GSongGu) + '单位八; ', '')
FROM (
SELECT DISTINCT A.F2_A001, A.F15_A001, F11_A074 = (SELECT TOP 1 F11_A074 FROM A_074 C WHERE F1_A074 = B.F1_A074 AND F18_A074 / 100 = B.F18_A074 / 100 ORDER BY F18_A074 DESC),
SongGu    = ISNULL(ISNULL((SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4A1'), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4A3')), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4A2')),
XianJin   = ISNULL(ISNULL((SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4B1'), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4B3')), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4B2')),
SuoGu     = ISNULL(ISNULL((SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4I1'), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4I3')), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4I2')),
RenGou    = ISNULL(ISNULL((SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4C1'), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4C3')), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4C2')),
RenGu     = ISNULL(ISNULL((SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4D1'), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4D3')), (SELECT NULLIF(F25_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4D2')),
ZhiGongGu = ISNULL(ISNULL((SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4J1'), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4J3')), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4J2')),
GFenHong = ISNULL(ISNULL((SELECT NULLIF(F10_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4F1'), (SELECT NULLIF(F10_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4F3')), (SELECT NULLIF(F10_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4F2')),
GSongGu   = ISNULL(ISNULL((SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4G1'), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4G3')), (SELECT NULLIF(F14_A075, 0.0) FROM A_075 WHERE F1_A075=a.OID_A001 AND F3_A075 = '4G2'))
from a_001 a INNER JOIN a_074 B with(index(Index_a_074_F18_A074)) --强制使用索引
ON A.OID_A001 = B.F1_A074 AND F18_A074/100= @SEQ
) T

--执行结果: 小于<1S