数据库优化之MS SqlServer篇
1）在查询中禁用SELECT * FROM Table,需要什么字段选择什么字段
Ø 避免键查找
Ø 避免查询到不需要的大字段类型数据，减少网络流量
SELECT *FROM dbo.TA_Logistics
WHERE Col_003='00000000054'
SELECT Col_006 FROM dbo.TA_Logistics
WHERE Col_003='00000000054'
2）语句带WHERE子句
需要什么数据就查询、删除、更新什么数据，避免删除、更新出错
同时能减少SELECT返回的行数，减少网络流量
3）减少访问数据库的次数
程序设计中最好将一些常用的配置表加载到内存中或者用其他的方式减少数据库的访问次数，避免使用一次就去连接数据库，减少数据库的负载
4）不要写一些没有意义的查询
构建表结构，类似这样的语句可以在代码里面去构建表结构，没必要到服务器查询
SELECT COL_004,COL_005 FROM TA_Logistics WHERE 1<>1
sys_guid不为空
当能预知查询没有结果的时候，在客户端就处理掉
declare @P0001 nvarchar(54)
set @P0001 = N'SELECT doc_path FROM TA_Documents WHERE sys_guid=@arg0'
declare @P0002 nvarchar(20)
set @P0002 = N'@arg0 nvarchar(4000)'
declare @P0003 nvarchar(1)
set @P0003 = N''
exec sp_executesql @P0001, @P0002, @arg0 = @P0003
5）尽量少做重复的工作
更新一条SQL语句，分两次执行，数据库会记录两次日志
UPDATE dbo.TA_Logistics SET COL_005='1'
WHERE Col_003='02700098207'
UPDATE dbo.TA_Logistics SET COL_006='2'
WHERE Col_003='02700098207'
这两个语句应该合并成以下一个语句
UPDATE dbo.TA_Logistics SET COL_005='1' , COL_006='2'
WHERE Col_003='02700098207'
6）简化SQL语句，语句关联不要写的太复杂,关联表太多的时候可以使用临时表来保存中间结果
让查询分析器能正确解析优化SQL语句
declare @P0001 nvarchar(392)
set @P0001 = N'Select Col_002,Col_012,Col_014,Col_039,Col_054,Col_006,Col_051,Col_052,Col_008,Col_011,Col_113,Col_119,Col_019
,Col_030,Col_065,Col_024,B.DD_133
from TA_LogisticsPH PH
LEFT JOIN dbo.TA_LogisticsXD AS B ON B.DD_102 = PH.Col_028 AND B.DD_122 = N''取货''
where Col_002=@arg0 or EXISTS(SELECT 1 FROM TA_Logistics YM WHERE PH.lyd_guid = YM.sys_guid AND YM.Col_003 = @arg0)'
declare @P0002 nvarchar(18)
set @P0002 = N'@arg0 nvarchar(11)'
declare @P0003 nvarchar(11)
set @P0003 = N'76901644328'
exec sp_executesql @P0001, @P0002, @arg0 = @P0003
正确写法
declare @P0001 nvarchar(2000)
set @P0001 = N'Select Col_002,Col_012,Col_014,Col_039,Col_054,Col_006,Col_051,Col_052,Col_008,Col_011,Col_113,Col_119,Col_019 ,Col_030,Col_065,Col_024,B.DD_133
from TA_LogisticsPH PH
LEFT JOIN dbo.TA_LogisticsXD AS B ON B.DD_102 = PH.Col_028 AND B.DD_122 = N''取货''
where Col_002=@arg0
UNION
Select Col_002,Col_012,Col_014,Col_039,Col_054,Col_006,Col_051,Col_052,Col_008,Col_011,Col_113,C ol_119,Col_019 ,Col_030,Col_065,Col_024,B.DD_133
from TA_LogisticsPH PH
LEFT JOIN dbo.TA_LogisticsXD AS B ON B.DD_102 = PH.Col_028 AND B.DD_122 = N''取货''
WHERE EXISTS(SELECT 1 FROM TA_Logistics YM WHERE PH.lyd_guid = YM.sys_guid AND YM.Col_003 = @arg0)'
declare @P0002 nvarchar(18)
set @P0002 = N'@arg0 nvarchar(11)'
declare @P0003 nvarchar(11)
set @P0003 = N'76901644328'
exec sp_executesql @P0001, @P0002, @arg0 = @P0003
7）、正确使用ISNULL函数
使用 ISNULL(字段,'')<>'' 和 字段<>''效果是一样的
SELECT *
FROM TV_LogisticsPH
WHERE Col_015 >= DATEADD(MONTH, -1, GETDATE())
AND ISNULL(Col_002, '') = ''
AND ISNULL(Col_020, '') = ''
AND Col_090 LIKE N'%'
AND Col_049 LIKE N'%'
AND Col_191 IS NULL
AND EXISTS ( SELECT 1
FROM TV_LogisticsXD AS T2
WHERE Col_028 = T2.DD_102
AND DATEDIFF(Mi, ISNULL(DD_120, '2000-01-01'),
GETDATE()) > 20
AND ISNULL(DD_120, '2000-01-01') > '2000-01-02' )
AND NOT EXISTS ( SELECT Col_002
FROM TA_LogisticsXDRemark AS T1
WHERE DATEDIFF(Mi, T1.Col_005, GETDATE()) < 20
AND Col_028 = T1.Col_002 )
AND ISNULL(Col_020, N'') = N''
AND NOT EXISTS ( SELECT TOP ( 1 )
1
FROM TA_LogisticsXDRemark T
WHERE T.Col_002 = Col_028
AND CHARINDEX(N'预计',
SUBSTRING(T.Col_003, 6, 3)) > 0
ORDER BY T.Col_005 DESC );
8）、索引的用法
不要对索引列计算、转换
时间字段索引示例
错误用法
SELECT
Z.[Col_D406]
,Z.[Col_D402]
,Z.[Col_D404]
FROM [dbo].[TA_ZWFixedAssetsNewSetDetail4] Z
WHERE CONVERT(VARCHAR(7), [Col_D404], 120) = N'2017-03'
正确用法
SELECT
Z.[Col_D406]
,Z.[Col_D402]
,Z.[Col_D404]
FROM [dbo].[TA_ZWFixedAssetsNewSetDetail4] Z
WHERE [Col_D404]>='2017-03-01' AND [Col_D404]<N'2017-04-01'
9）、正确使用字符集
数据集的转换只用两个表的字段字符集不一致才需要
错误用法
UPDATE dbo.TA_Logistics
SET Col_306 = ( CASE WHEN ISNULL(Col_334, 0) = 0
AND ISNULL(Col_349, '') = ''
THEN ( ( ISNULL(YD.Col_018, 0) - ISNULL(YD.Col_083, 0)
- ISNULL(YD.Col_032, 0) )
* ISNULL(CASE WHEN ISNULL(D.DriverNa, '') = ''
THEN D2.DriverRate
ELSE D.DriverRate
END, 0) / 100.0 )
ELSE Col_306
END ) ,
Col_297 = CASE WHEN ISNULL(D.DriverNa, '') = '' THEN D2.DriverNa
ELSE D.DriverNa
END ,
Col_298 = CASE WHEN ISNULL(D.DriverNa, '') = '' THEN D2.DriverRate
ELSE D.DriverRate
END
FROM dbo.TA_Logistics AS YD
LEFT JOIN dbo.TB_CompanyJJ AS D ON YD.Col_117 = D.Company COLLATE Chinese_PRC_CI_AS
LEFT JOIN dbo.TB_CompanyJJ AS D2 ON YD.Col_043 = D2.Company COLLATE Chinese_PRC_CI_AS
WHERE YD.sys_guid IN ( '4C8033A912254779B9ABC37D3A4886C7',
'8365C38506AB4FEA8D453A531D85B360' );
正确用法
UPDATE dbo.TA_Logistics
SET Col_306 = ( CASE WHEN ISNULL(Col_334, 0) = 0
AND ISNULL(Col_349, '') = ''
THEN ( ( ISNULL(YD.Col_018, 0) - ISNULL(YD.Col_083, 0)
- ISNULL(YD.Col_032, 0) )
* ISNULL(CASE WHEN ISNULL(D.DriverNa, '') = ''
THEN D2.DriverRate
ELSE D.DriverRate
END, 0) / 100.0 )
ELSE Col_306
END ) ,
Col_297 = CASE WHEN ISNULL(D.DriverNa, '') = '' THEN D2.DriverNa
ELSE D.DriverNa
END ,
Col_298 = CASE WHEN ISNULL(D.DriverNa, '') = '' THEN D2.DriverRate
ELSE D.DriverRate
END
FROM dbo.TA_Logistics AS YD
LEFT JOIN dbo.TB_CompanyJJ AS D ON YD.Col_117 = D.Company
LEFT JOIN dbo.TB_CompanyJJ AS D2 ON YD.Col_043 = D2.Company
WHERE YD.sys_guid IN ( '4C8033A912254779B9ABC37D3A4886C7',
'8365C38506AB4FEA8D453A531D85B360' );
10）尽量避免大事务操作，提高系统并发能力
针对需要大量更新数据的事务，需要将每次更新的数据量变小，多次执行
通业务数据的时候，可以执行每次执行多少行
SET ROWCOUNT 1000来指定每次执行多少
优化示例：
问题：在测试环境查询6分钟，出不了结果
SELECT *
FROM TV_LogisticsPH
WHERE Col_015 >= DATEADD(MONTH, -1, GETDATE())
AND ISNULL(Col_002, '') = ''
AND ISNULL(Col_020, '') = ''
AND Col_090 LIKE N'%'
AND Col_049 LIKE N'%'
AND Col_191 IS NULL
AND EXISTS ( SELECT 1
FROM TV_LogisticsXD AS T2
WHERE Col_028 = T2.DD_102
AND DATEDIFF(Mi, ISNULL(DD_120, '2000-01-01'),
GETDATE()) > 20
AND ISNULL(DD_120, '2000-01-01') > '2000-01-02' )
AND NOT EXISTS ( SELECT Col_002
FROM TA_LogisticsXDRemark AS T1
WHERE DATEDIFF(Mi, T1.Col_005, GETDATE()) < 20
AND Col_028 = T1.Col_002 )
AND ISNULL(Col_020, N'') = N''
AND NOT EXISTS ( SELECT TOP ( 1 )
1
FROM TA_LogisticsXDRemark T
WHERE T.Col_002 = Col_028
AND CHARINDEX(N'预计',
SUBSTRING(T.Col_003, 6, 3)) > 0
ORDER BY T.Col_005 DESC );
问题分析：
1）优化ISNULL(Col_020, N'') = N'' 为 Col_020=''
2）TV_LogisticsXD 换为实体表
3）ISNULL(DD_120, '2000-01-01') > '2000-01-02' ) 改为 DD_120>'2000-01-02'
4）去除ORDER BY T.Col_005 DESC
此时查询语句能够在5S内出结果
在表TA_LogisticsDDDetai的关联字段上[DD_102],建索引，1S内出结果
建索引：
CREATE NONCLUSTERED INDEX [IX_TA_LogisticsDDDetail_New]
ON [dbo].[TA_LogisticsDDDetail] ([DD_102],[DD_115])
DORP INDEX IX_TA_LogisticsDDDetail_New ON [TA_LogisticsDDDetail]
优化后语句：
SELECT *
FROM TV_LogisticsPH
WHERE Col_015 >= DATEADD(MONTH, -1, GETDATE())
AND ISNULL(Col_002, '') = ''
AND Col_020 = ''
AND Col_090 LIKE N'%'
AND Col_049 LIKE N'%'
AND Col_191 IS NULL
AND EXISTS ( SELECT 1
FROM TV_LogisticsXD AS T2
WHERE Col_028 = T2.DD_102
AND DATEDIFF(Mi, ISNULL(DD_120, '2000-01-01'),
GETDATE()) > 20
AND DD_120 > '2000-01-02' )
AND NOT EXISTS ( SELECT Col_002
FROM TA_LogisticsXDRemark AS T1
WHERE DATEDIFF(Mi, T1.Col_005, GETDATE()) < 20
AND Col_028 = T1.Col_002 )
AND NOT EXISTS ( SELECT TOP ( 1 )
1
FROM TA_LogisticsXDRemark T
WHERE T.Col_002 = Col_028
AND CHARINDEX(N'预计',
SUBSTRING(T.Col_003, 6, 3)) > 0 );
