一直从事运维的工作，免不了优化一些SQL语句，因为本人比较懒的原因，很多经典的案例没有记录下来，深表遗憾
案例语句
某大型房地产公司，巡检日期2013-04-22，问题语句
1 SELECT COUNT(*) 2 FROM ( SELECT temp.Application , 3 COUNT(*) AS UserCount 4 FROM ( SELECT f.Application 5 FROM myUserRights ur 6 INNER JOIN myFunction f ON ur.ObjectType = f.FunctionCode 7 INNER JOIN myUser u ON ur.UserGUID = u.UserGUID 8 WHERE u.IsAdmin = 0 9 AND ( IsDisabeld = 0 10 OR IsDisabeld IS NULL 11 ) 12 AND ur.UserGUID NOT IN ( 13 SELECT UserGUID 14 FROM myUserRoles 15 WHERE RoleGUID = 'dbf6cef3-5dc1-4b21-9865-4664849eac78' ) 16 GROUP BY f.Application , 17 ur.UserGUID 18 ) AS temp 19 GROUP BY temp.Application 20 ) AS temp2 21 RIGHT JOIN myApplication a ON a.Application = temp2.Application 22 WHERE a.Level = 1 23 AND a.Application IN ( 24 SELECT Application 25 FROM myFunction 26 WHERE FunctionCode IN ( '', '01010102', '01010104', '01010105', 27 '01010111', '01010106', '01010107', 28 '01010110', '01010118', '01010604', 29 '01010603', '01010202', '01010203', 30 '01010205', '01010206', '01010207', 31 '01010301', '01010302', '01010303', 32 '01010304', '01010308', '01010305', 33 '01010306', '01010310', '01010309', 34 '01010407', '01010401', '01010403', 35 '01010404', '01010406', '01010408', 36 '01010409', '01010410', '01010411', 37 '01010506', '01010502', '01010503', 38 '01010507', '01010509', '01011999', 39 '06636E7E_F243_4CAE_86AB_723CFBA1513C', 40 '06636E7E_F243_4CAE_86AB_723CFBA1513C', 41 '0741CB62_C25A_4B57_B0A9_2236BA8BAE97', 42 '0741CB62_C25A_4B57_B0A9_2236BA8BAE97', 43 '458E20D3_51B9_4A87_96B9_7005CAD462F0', 44 '458E20D3_51B9_4A87_96B9_7005CAD462F0', 45 '4B3F9D72_3427_4ECA_B089_78E2B9A76375', 46 '4B3F9D72_3427_4ECA_B089_78E2B9A76375', 47 '945F14D1_717B_4434_89AF_E4DF11E03617', 48 '945F14D1_717B_4434_89AF_E4DF11E03617', 49 'C035C1A7_7612_421C_800B_690FDB4E4E44', 50 'C035C1A7_7612_421C_800B_690FDB4E4E44', 51 'C6E3E43E_19EF_43E6_88B8_6C9DFC4363A1', 52 'C6E3E43E_19EF_43E6_88B8_6C9DFC4363A1', 53 'F7BFE91E_D79B_48BD_8233_E92A63A01921', 54 'F7BFE91E_D79B_48BD_8233_E92A63A01921' ) 55 GROUP BY Application ) 56 AND ( CASE WHEN temp2.UserCount IS NULL THEN 0 + 17 57 ELSE temp2.UserCount + 0 + 17 58 END ) > a.LicenseUserCount
表 'Worktable'。扫描计数 1，逻辑读取 109 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUserRoles'。扫描计数 1，逻辑读取 73 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUserRights'。扫描计数 2772576，逻辑读取 11812753 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myFunction'。扫描计数 2，逻辑读取 90043 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUser'。扫描计数 1，逻辑读取 163 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myApplication'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1 行受影响)
表 'Worktable'。扫描计数 1，逻辑读取 109 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUserRoles'。扫描计数 1，逻辑读取 73 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUserRights'。扫描计数 2772576，逻辑读取 11812753 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myFunction'。扫描计数 2，逻辑读取 90043 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUser'。扫描计数 1，逻辑读取 163 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myApplication'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1 行受影响)
SQL Server 执行时间:
CPU 时间 = 12625 毫秒，占用时间 = 12652 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
IO上千万,该语句是对应的一个展示页面，统计count(*)是为了分页，执行时间为12S，开始拿到该语句，一头雾水，不知如何下手。
分析手段
说说我的分析手段，这里无法把数据库给大家，所以只能是分享我的分析手段，能给大家启发就达到了本文的目的,一般拿到这种SQL语句,我首先执行一遍
然后看执行计划,找最大的开销,看能不能加索引优化,看多了也没太大用,
最大的开销在索引查找上，其实我最讨厌的就是这种，一看最大开销是索引查找，也没办法加索引去优化，一般遇到这种情况，是因为选错了执行计划导致的，
选错了执行计划是代表了你写的SQL有问题，有什么问题？SQL语句中有OR,NOT IN这些不符合SARG的，我采取了一个比较有效的验证方式，强制改变连接
方式，很暴力，也很危险
1 SELECT COUNT(*) 2 FROM ( SELECT temp.Application , 3 COUNT(*) AS UserCount 4 FROM ( SELECT f.Application 5 FROM myUserRights ur 6 INNER JOIN myFunction f ON ur.ObjectType = f.FunctionCode 7 INNER JOIN myUser u ON ur.UserGUID = u.UserGUID 8 WHERE u.IsAdmin = 0 9 AND ( IsDisabeld = 0 10 OR IsDisabeld IS NULL 11 ) 12 AND ur.UserGUID NOT IN ( 13 SELECT UserGUID 14 FROM myUserRoles 15 WHERE RoleGUID = 'dbf6cef3-5dc1-4b21-9865-4664849eac78' ) 16 GROUP BY f.Application , 17 ur.UserGUID 18 ) AS temp 19 GROUP BY temp.Application 20 ) AS temp2 21 RIGHT HASH JOIN myApplication a ON a.Application = temp2.Application 22 WHERE a.Level = 1 23 AND a.Application IN ( 24 SELECT Application 25 FROM myFunction 26 WHERE FunctionCode IN ( '', '01010102', '01010104', '01010105', 27 '01010111', '01010106', '01010107', 28 '01010110', '01010118', '01010604', 29 '01010603', '01010202', '01010203', 30 '01010205', '01010206', '01010207', 31 '01010301', '01010302', '01010303', 32 '01010304', '01010308', '01010305', 33 '01010306', '01010310', '01010309', 34 '01010407', '01010401', '01010403', 35 '01010404', '01010406', '01010408', 36 '01010409', '01010410', '01010411', 37 '01010506', '01010502', '01010503', 38 '01010507', '01010509', '01011999', 39 '06636E7E_F243_4CAE_86AB_723CFBA1513C', 40 '06636E7E_F243_4CAE_86AB_723CFBA1513C', 41 '0741CB62_C25A_4B57_B0A9_2236BA8BAE97', 42 '0741CB62_C25A_4B57_B0A9_2236BA8BAE97', 43 '458E20D3_51B9_4A87_96B9_7005CAD462F0', 44 '458E20D3_51B9_4A87_96B9_7005CAD462F0', 45 '4B3F9D72_3427_4ECA_B089_78E2B9A76375', 46 '4B3F9D72_3427_4ECA_B089_78E2B9A76375', 47 '945F14D1_717B_4434_89AF_E4DF11E03617', 48 '945F14D1_717B_4434_89AF_E4DF11E03617', 49 'C035C1A7_7612_421C_800B_690FDB4E4E44', 50 'C035C1A7_7612_421C_800B_690FDB4E4E44', 51 'C6E3E43E_19EF_43E6_88B8_6C9DFC4363A1', 52 'C6E3E43E_19EF_43E6_88B8_6C9DFC4363A1', 53 'F7BFE91E_D79B_48BD_8233_E92A63A01921', 54 'F7BFE91E_D79B_48BD_8233_E92A63A01921' ) 55 GROUP BY Application ) 56 AND ( CASE WHEN temp2.UserCount IS NULL THEN 0 + 17 57 ELSE temp2.UserCount + 0 + 17 58 END ) > a.LicenseUserCount
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
警告: 由于使用了本地联接提示，联接次序得以强制实施。
SQL Server 分析和编译时间:
CPU 时间 = 46 毫秒，占用时间 = 58 毫秒。
(1 行受影响)
表 'Worktable'。扫描计数 1，逻辑读取 109 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myFunction'。扫描计数 2，逻辑读取 58 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myApplication'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUser'。扫描计数 1，逻辑读取 163 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUserRoles'。扫描计数 1，逻辑读取 73 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUserRights'。扫描计数 1，逻辑读取 8236 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1 行受影响)
SQL Server 执行时间:
CPU 时间 = 4266 毫秒，占用时间 = 5306 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
我在连接那里加上了一个HASH，连接有三种方式，强制HASH后，我发现速度大幅度提高了，IO下来了，这里给出了警告，这里加HASH，是为了验证上面我所
说的，SQL的引擎选错了执行计划，导致执行时间过长，那么既然知道了这个问题，接下来找出有问题的点，这在优化非常长语句的时候，往往非常有效，我们
选取一部分SQL语句，从优化片段的方式，达到优化整个SQL语句，那么有问题的SQL，出在哪里？我想很多人能够看出来
1 SELECT temp.Application , 2 COUNT(*) AS UserCount 3 FROM ( SELECT f.Application 4 FROM myUserRights ur 5 INNER JOIN myFunction f ON ur.ObjectType = f.FunctionCode 6 INNER JOIN myUser u ON ur.UserGUID = u.UserGUID 7 WHERE u.IsAdmin = 0 8 AND ( IsDisabeld = 0 9 OR IsDisabeld IS NULL 10 ) 11 AND ur.UserGUID NOT IN ( 12 SELECT UserGUID 13 FROM myUserRoles 14 WHERE RoleGUID = 'dbf6cef3-5dc1-4b21-9865-4664849eac78' ) 15 GROUP BY f.Application , 16 ur.UserGUID 17 ) AS temp 18 GROUP BY temp.Application
这里因为NOT IN的原因，导致了SQL的引擎选择了错误的执行计划，想想看，如果改用连接，那么SQL的引擎会自动选择比较优秀的连接方式，如果使用in,not
in当语句复杂的时候,执行计划往往是嵌套循环的连接方式，这也解释了为什么，我们提倡使用连接，而不是子查询.所以这里我们只需要修改NOT IN即可
如何优化
1 SELECT COUNT(*) 2 FROM ( 3 SELECT temp.Application , 4 COUNT(*) AS UserCount 5 FROM ( SELECT f.Application 6 FROM myUserRights ur 7 INNER JOIN myFunction f ON ur.ObjectType = f.FunctionCode 8 INNER JOIN myUser u ON ur.UserGUID = u.UserGUID 9 LEFT JOIN (SELECT distinct UserGUID 10 FROM myUserRoles 11 WHERE RoleGUID = 'dbf6cef3-5dc1-4b21-9865-4664849eac78') 12 m ON ur.UserGUID = m.UserGUID 13 14 WHERE u.IsAdmin = 0 15 AND ( IsDisabeld = 0 16 OR IsDisabeld IS NULL 17 ) 18 AND m.UserGUID IS NULL 19 20 21 GROUP BY f.Application , 22 ur.UserGUID 23 ) AS temp 24 GROUP BY temp.Application 25 ) AS temp2 26 RIGHT JOIN myApplication a ON a.Application = temp2.Application 27 WHERE a.Level = 1 28 AND a.Application IN ( 29 SELECT Application 30 FROM myFunction 31 WHERE FunctionCode IN ( '', '01010102', '01010104', '01010105', 32 '01010111', '01010106', '01010107', 33 '01010110', '01010118', '01010604', 34 '01010603', '01010202', '01010203', 35 '01010205', '01010206', '01010207', 36 '01010301', '01010302', '01010303', 37 '01010304', '01010308', '01010305', 38 '01010306', '01010310', '01010309', 39 '01010407', '01010401', '01010403', 40 '01010404', '01010406', '01010408', 41 '01010409', '01010410', '01010411', 42 '01010506', '01010502', '01010503', 43 '01010507', '01010509', '01011999', 44 '06636E7E_F243_4CAE_86AB_723CFBA1513C', 45 '06636E7E_F243_4CAE_86AB_723CFBA1513C', 46 '0741CB62_C25A_4B57_B0A9_2236BA8BAE97', 47 '0741CB62_C25A_4B57_B0A9_2236BA8BAE97', 48 '458E20D3_51B9_4A87_96B9_7005CAD462F0', 49 '458E20D3_51B9_4A87_96B9_7005CAD462F0', 50 '4B3F9D72_3427_4ECA_B089_78E2B9A76375', 51 '4B3F9D72_3427_4ECA_B089_78E2B9A76375', 52 '945F14D1_717B_4434_89AF_E4DF11E03617', 53 '945F14D1_717B_4434_89AF_E4DF11E03617', 54 'C035C1A7_7612_421C_800B_690FDB4E4E44', 55 'C035C1A7_7612_421C_800B_690FDB4E4E44', 56 'C6E3E43E_19EF_43E6_88B8_6C9DFC4363A1', 57 'C6E3E43E_19EF_43E6_88B8_6C9DFC4363A1', 58 'F7BFE91E_D79B_48BD_8233_E92A63A01921', 59 'F7BFE91E_D79B_48BD_8233_E92A63A01921' ) 60 GROUP BY Application ) 61 AND ( CASE WHEN temp2.UserCount IS NULL THEN 0 + 17 62 ELSE temp2.UserCount + 0 + 17 63 END ) > a.LicenseUserCount 64 65
(1 行受影响)
表 'Worktable'。扫描计数 1，逻辑读取 109 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUserRights'。扫描计数 1905，逻辑读取 15739 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myFunction'。扫描计数 2，逻辑读取 58 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUser'。扫描计数 1，逻辑读取 163 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myUserRoles'。扫描计数 1，逻辑读取 73 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'myApplication'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1 行受影响)
SQL Server 执行时间:
CPU 时间 = 828 毫秒，占用时间 = 934 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
我们把not in改成连接后，性能得到了本质的提升，这种巨大的差异，有时候在工作上往往让我得意一会。
招聘信息
1. 工作地点：武汉
2. 工作经验2年以上
3. asp.net开发, 基础夯实。
公司名称:武汉明源动力软件有限公司
给我发邮件:236773862@qq.com
