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 INTASSELECT 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 记录数:730select * from a_001记录数:2424select * from a_075 记录数:30028--执行后,需要耗费时间为: 1分钟48秒,经常查询超时分析消耗资源的点：--1：从存储过程中可以看到,查询用到了很多左连接,左连接消耗了大量资源--2：查询条件中用到变量,要导致全表扫描,消耗大量资源
--解决办法：--1:把所有左连接更改为子查询--2:把查询条件,强制指定索引步骤：
A:改为子查询如下：
CREATE PROCEDURE P_TEST @Date INTASSELECT 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 INTASSELECT 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
