mssql sqlserver in、or、union 关键字查询效率对比


摘要:
下文讲述存在表中索引字段上使用in、or、union关键字的查询效率对比分析,如下所示:


下文通过实验的方式,测试in or union 在索引列上查询所消耗的资源情况,并进行相应的对比分析
实验环境: sqlserver 2008 R2

数据表:
[20180627Tmp]
拥有索引
ix_tmp 包含列tmpZiDuan
拥有1041175行数据

————————————————————–
sqlserver or写法:

  

  set statistics io  on 

 DBCC DROPCLEANBUFFERS ---清理缓存
 
select tmpZiDuan from [20180627Tmp] 
where     tmpZiDuan ='B720964E-862A-4E99-913F-000033E571C4'
 or  tmpZiDuan ='F6A8CFDD-132C-4D93-A4A5-00004067D31C'
 or  tmpZiDuan ='61D889DF-A1F4-4C96-A862-00004597A850'
 or  tmpZiDuan ='A3A590D3-0807-4D89-B4EE-000049E51F88'
 or  tmpZiDuan ='FC07C694-D5FB-4A15-A965-00004D5226AF'
 or  tmpZiDuan ='9B8A7654-FF92-4051-B2F6-00005636E823'
 or  tmpZiDuan ='70625DA8-F3E2-4FA5-97FA-0000591CA7A1'
 or  tmpZiDuan ='B9BB6696-D7BC-40C9-99A8-00006A05F1AC'
 or  tmpZiDuan ='53B37306-9D2F-49D6-B411-00006B9A7B58'
 or  tmpZiDuan ='FDA181EF-1B12-469A-B0C9-000079776575'
 or  tmpZiDuan ='CA87F5B2-1A2A-4668-9C19-00007D576316'
 or  tmpZiDuan ='97851AFF-2B91-4656-B2DB-00008F5A6CCD'
 or  tmpZiDuan ='E078115C-0D44-46A1-BDF6-0000964251DA'
 or  tmpZiDuan ='FFCD4110-1E95-4F04-8E46-0000A91DD32D'
 or  tmpZiDuan ='3B58ECFD-5471-463C-BD9E-0000CBCDD360'
 or  tmpZiDuan ='26F44396-3A55-4B48-84AE-0000D073ACEC'
 or  tmpZiDuan ='DEAA2999-5DD5-45BC-B5AA-0000E462D625'
 or  tmpZiDuan ='9C7643D2-89F1-4248-8943-0000F102B608'
 or  tmpZiDuan ='2D9FF00F-C7EB-4E0B-AC34-0000F7634C9A'
 or  tmpZiDuan ='67251E8F-4205-4B16-9631-0000F7F9DD29'
 or  tmpZiDuan ='D2C59B11-0861-410E-8F75-00011F1485FA'
 or  tmpZiDuan ='306FFF3F-C8CB-49FA-AA8D-0001389992BF'
 or  tmpZiDuan ='E3063DD5-7DA2-4A55-B537-00017E242DF4'
 or  tmpZiDuan ='05CE4B27-A502-4794-AEDD-00019720118B'
 or  tmpZiDuan ='1E908768-B5C4-46F5-99D9-00019A5B72BA'
 or  tmpZiDuan ='95E4A677-C4CF-49E9-82D7-00019A759B6D'
 or  tmpZiDuan ='8AC6C0EA-BFE5-4354-8BE7-0001A5B9D526'
 or  tmpZiDuan ='C2E1C31D-10BF-41DE-AD46-0001A662C9DC'
 or  tmpZiDuan ='0172B13E-F127-40EE-AD7B-0001B709A3E4'
 or  tmpZiDuan ='2AF9CA4F-6C2C-4CEB-AD98-0001BC0490FB'
 or  tmpZiDuan ='3BF3AF07-EC78-49AE-B7B5-0001C6659896'
 or  tmpZiDuan ='5AFBD4FF-C51C-4AC4-B08F-00021B152C80'
 or  tmpZiDuan ='D0766BC3-6AD7-4D8B-99C9-00023A7D7673'
 or  tmpZiDuan ='3C5A899B-563D-4708-A25C-00025B9F4747'
 or  tmpZiDuan ='2052B2AE-AB93-4C06-8478-0002632E489F'
 or  tmpZiDuan ='0B1437FC-8A9B-4C7E-B46B-0002664291F0'
 or  tmpZiDuan ='E6184D8E-4AA4-4873-9442-00026842D6E1'
 or  tmpZiDuan ='F5C200BB-F5ED-425E-B069-0002854CEEC6'
 or  tmpZiDuan ='F70368B1-C47A-4040-B51D-0002862FBB46'
 or  tmpZiDuan ='328B1FF6-1E7A-45BD-BA9B-00028B691159'
 or  tmpZiDuan ='E9D6DFE1-0267-4A84-BD63-0002AB361266'
 or  tmpZiDuan ='7276A1CA-4C76-488A-BAEB-0002CEC9F0D5'
 or  tmpZiDuan ='D3A6AAE9-E4A4-4E38-84E3-0002D2188302'
 or  tmpZiDuan ='81FFF5F2-D582-45A2-84FE-0002D44527FA'
 or  tmpZiDuan ='BC6C363C-B139-4E95-8DEB-0002E49A8D79'
 or  tmpZiDuan ='79944EDD-009B-41C8-A3DD-0002F41A9D87'
 or  tmpZiDuan ='A03E0DFB-3024-4165-945C-00030B03B3C9'
 or  tmpZiDuan ='46354B31-2730-482F-BA84-00031AEEACCC'
 or  tmpZiDuan ='C2486FC5-0C4D-4068-8165-00031B1CDE41'
 or  tmpZiDuan ='43583104-D90A-4123-990A-00031DFC389B'
 or  tmpZiDuan ='E7AAF3F4-F918-45C5-AB2E-0003222CF7D5'
 or  tmpZiDuan ='10DEF07E-2323-40B1-80D1-00033109D167'
 or  tmpZiDuan ='3645861F-5A0C-44E4-8547-000340D08C40'
 or  tmpZiDuan ='C85BD068-BF16-40D6-A045-000347F8059A'
 or  tmpZiDuan ='84AFC461-CE9D-4138-839E-000383E2C7D7'
 or  tmpZiDuan ='F7B99896-6FB5-48FB-A65B-00038D7E2537'
 or  tmpZiDuan ='E3B4A72A-A0AA-4015-A29D-0003939BBEFE'
 or  tmpZiDuan ='F89C1F31-4630-437F-9E72-000394D2E7B6'
 or  tmpZiDuan ='500B321E-7F47-46DF-90F4-0003971140D2'
 or  tmpZiDuan ='BB7F44DE-B82E-41A2-80B1-0003A492FF2D'
 or  tmpZiDuan ='2F073374-A60A-4E94-8B56-0003B7A0A94F'
 or  tmpZiDuan ='3E528AA6-8FD4-438B-A40F-0003C796A269'
 or  tmpZiDuan ='8FD0E821-78DA-49D6-8DAC-0003CAE0F773'
 or  tmpZiDuan ='EE1553D6-756E-44E1-BE1E-0003EE1FFF8E'
 or  tmpZiDuan ='8A7CAACA-81CC-4975-B22A-00042AD22F3C'
 or  tmpZiDuan ='16AA75ED-0948-4449-8301-00043AB41CA6'
 or  tmpZiDuan ='DCF7D92B-E4BE-43F2-96CF-000444CFBD25'
 or  tmpZiDuan ='0566DF96-7C72-46CD-8246-000449674685'
 or  tmpZiDuan ='EFBFBF8D-D64F-4B3C-937A-00045E5FFD31'
 or  tmpZiDuan ='CFE90F9A-4E76-4225-9097-000463897EED'
 or  tmpZiDuan ='25555158-0851-4DAB-B3F7-00049850ED3C'
 or  tmpZiDuan ='BB256D2C-C6C2-4FCE-8C76-00049E66189D'
 or  tmpZiDuan ='6E15EAEF-78CC-47F8-8CD8-0004CA7A3610'
 or  tmpZiDuan ='49977E00-E860-4DF1-8E86-0004E84D9190'
 or  tmpZiDuan ='AB186827-0A37-415C-9ADF-0004F28A3E1E'
 or  tmpZiDuan ='D607B4C5-7027-4280-82EC-000513DFC9F0'
 or  tmpZiDuan ='20638DA0-96BA-472F-A89C-000526DDB7E7'
 or  tmpZiDuan ='7A7AFD28-11BE-45C2-A828-0005335F1CB4'
 or  tmpZiDuan ='01145555-4D92-47E5-9DE2-0005418205DE'
 or  tmpZiDuan ='85492F5C-3BEA-4FE1-A22C-000556AC8719'
 or  tmpZiDuan ='BB117951-77A4-4BE1-B6D9-000567EB6E90'
 or  tmpZiDuan ='B4AC5BFC-B0FC-43E7-A5D9-000569E76707'
 or  tmpZiDuan ='DA89F967-6F01-45E5-B016-000570EA4EBF'
 or  tmpZiDuan ='3E12AD59-7F05-4F70-9D5D-0005B4A1A92F'
 or  tmpZiDuan ='F379B7A9-6A2C-41C8-AD3B-0005B96E1E10'
 or  tmpZiDuan ='245AA49F-FD87-4631-AA7E-0005C2F935C3'
 or  tmpZiDuan ='0860CE90-7B8A-408B-80BF-0005C530B3C0'
 or  tmpZiDuan ='C510E820-514B-4086-876D-0005E2D8DA14'
 or  tmpZiDuan ='65C6A59D-B835-4EFD-ABF1-0006003DB5BA'
 or  tmpZiDuan ='C2F6ADB9-0B31-4C81-BD0A-000601FB378F'
 or  tmpZiDuan ='97E4E20E-8B1E-4431-AE1D-000602DD4BC8'
 or  tmpZiDuan ='DEB0975C-0638-44F8-B32E-000616D49EE6'
 or  tmpZiDuan ='B2A363CD-7E2A-42AA-A36D-00061E0F51B0'
 or  tmpZiDuan ='612903B9-3196-4178-98E8-00062DFBA9A6'
 or  tmpZiDuan ='DFD3FFC8-4A4C-4063-82C4-000657C549DE'
 or  tmpZiDuan ='AFE27F1E-0D6C-4E40-8C96-00067AF57FF3'
 or  tmpZiDuan ='42E96C4C-0921-4D0D-A3AC-0006E293DBD2'
 or  tmpZiDuan ='79F0F593-9782-4976-BDC4-0006F41426FA'
 or  tmpZiDuan ='EA8FDD08-D447-4491-B317-000728468E04'

—————————-IO读写信息——————————–
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

(99 行受影响)
表 ‘20180627Tmp’。扫描计数 99,逻辑读取 524 次,物理读取 1 次,预读 2 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)


sqlserver in 写法:


 DBCC DROPCLEANBUFFERS
 set statistics io  on 

 select tmpZiDuan from [20180627Tmp] 
where     tmpZiDuan in
 ( 'B720964E-862A-4E99-913F-000033E571C4',
 'F6A8CFDD-132C-4D93-A4A5-00004067D31C',
 '61D889DF-A1F4-4C96-A862-00004597A850',
 'A3A590D3-0807-4D89-B4EE-000049E51F88',
 'FC07C694-D5FB-4A15-A965-00004D5226AF',
 '9B8A7654-FF92-4051-B2F6-00005636E823',
 '70625DA8-F3E2-4FA5-97FA-0000591CA7A1',
 'B9BB6696-D7BC-40C9-99A8-00006A05F1AC',
 '53B37306-9D2F-49D6-B411-00006B9A7B58',
 'FDA181EF-1B12-469A-B0C9-000079776575',
 'CA87F5B2-1A2A-4668-9C19-00007D576316',
 '97851AFF-2B91-4656-B2DB-00008F5A6CCD',
 'E078115C-0D44-46A1-BDF6-0000964251DA',
 'FFCD4110-1E95-4F04-8E46-0000A91DD32D',
 '3B58ECFD-5471-463C-BD9E-0000CBCDD360',
 '26F44396-3A55-4B48-84AE-0000D073ACEC',
 'DEAA2999-5DD5-45BC-B5AA-0000E462D625',
 '9C7643D2-89F1-4248-8943-0000F102B608',
 '2D9FF00F-C7EB-4E0B-AC34-0000F7634C9A',
 '67251E8F-4205-4B16-9631-0000F7F9DD29',
 'D2C59B11-0861-410E-8F75-00011F1485FA',
 '306FFF3F-C8CB-49FA-AA8D-0001389992BF',
 'E3063DD5-7DA2-4A55-B537-00017E242DF4',
 '05CE4B27-A502-4794-AEDD-00019720118B',
 '1E908768-B5C4-46F5-99D9-00019A5B72BA',
 '95E4A677-C4CF-49E9-82D7-00019A759B6D',
 '8AC6C0EA-BFE5-4354-8BE7-0001A5B9D526',
 'C2E1C31D-10BF-41DE-AD46-0001A662C9DC',
 '0172B13E-F127-40EE-AD7B-0001B709A3E4',
 '2AF9CA4F-6C2C-4CEB-AD98-0001BC0490FB',
 '3BF3AF07-EC78-49AE-B7B5-0001C6659896',
 '5AFBD4FF-C51C-4AC4-B08F-00021B152C80',
 'D0766BC3-6AD7-4D8B-99C9-00023A7D7673',
 '3C5A899B-563D-4708-A25C-00025B9F4747',
 '2052B2AE-AB93-4C06-8478-0002632E489F',
 '0B1437FC-8A9B-4C7E-B46B-0002664291F0',
 'E6184D8E-4AA4-4873-9442-00026842D6E1',
 'F5C200BB-F5ED-425E-B069-0002854CEEC6',
 'F70368B1-C47A-4040-B51D-0002862FBB46',
 '328B1FF6-1E7A-45BD-BA9B-00028B691159',
 'E9D6DFE1-0267-4A84-BD63-0002AB361266',
 '7276A1CA-4C76-488A-BAEB-0002CEC9F0D5',
 'D3A6AAE9-E4A4-4E38-84E3-0002D2188302',
 '81FFF5F2-D582-45A2-84FE-0002D44527FA',
 'BC6C363C-B139-4E95-8DEB-0002E49A8D79',
 '79944EDD-009B-41C8-A3DD-0002F41A9D87',
 'A03E0DFB-3024-4165-945C-00030B03B3C9',
 '46354B31-2730-482F-BA84-00031AEEACCC',
 'C2486FC5-0C4D-4068-8165-00031B1CDE41',
 '43583104-D90A-4123-990A-00031DFC389B',
 'E7AAF3F4-F918-45C5-AB2E-0003222CF7D5',
 '10DEF07E-2323-40B1-80D1-00033109D167',
 '3645861F-5A0C-44E4-8547-000340D08C40',
 'C85BD068-BF16-40D6-A045-000347F8059A',
 '84AFC461-CE9D-4138-839E-000383E2C7D7',
 'F7B99896-6FB5-48FB-A65B-00038D7E2537',
 'E3B4A72A-A0AA-4015-A29D-0003939BBEFE',
 'F89C1F31-4630-437F-9E72-000394D2E7B6',
 '500B321E-7F47-46DF-90F4-0003971140D2',
 'BB7F44DE-B82E-41A2-80B1-0003A492FF2D',
 '2F073374-A60A-4E94-8B56-0003B7A0A94F',
 '3E528AA6-8FD4-438B-A40F-0003C796A269',
 '8FD0E821-78DA-49D6-8DAC-0003CAE0F773',
 'EE1553D6-756E-44E1-BE1E-0003EE1FFF8E',
 '8A7CAACA-81CC-4975-B22A-00042AD22F3C',
 '16AA75ED-0948-4449-8301-00043AB41CA6',
 'DCF7D92B-E4BE-43F2-96CF-000444CFBD25',
 '0566DF96-7C72-46CD-8246-000449674685',
 'EFBFBF8D-D64F-4B3C-937A-00045E5FFD31',
 'CFE90F9A-4E76-4225-9097-000463897EED',
 '25555158-0851-4DAB-B3F7-00049850ED3C',
 'BB256D2C-C6C2-4FCE-8C76-00049E66189D',
 '6E15EAEF-78CC-47F8-8CD8-0004CA7A3610',
 '49977E00-E860-4DF1-8E86-0004E84D9190',
 'AB186827-0A37-415C-9ADF-0004F28A3E1E',
 'D607B4C5-7027-4280-82EC-000513DFC9F0',
 '20638DA0-96BA-472F-A89C-000526DDB7E7',
 '7A7AFD28-11BE-45C2-A828-0005335F1CB4',
 '01145555-4D92-47E5-9DE2-0005418205DE',
 '85492F5C-3BEA-4FE1-A22C-000556AC8719',
 'BB117951-77A4-4BE1-B6D9-000567EB6E90',
 'B4AC5BFC-B0FC-43E7-A5D9-000569E76707',
 'DA89F967-6F01-45E5-B016-000570EA4EBF',
 '3E12AD59-7F05-4F70-9D5D-0005B4A1A92F',
 'F379B7A9-6A2C-41C8-AD3B-0005B96E1E10',
 '245AA49F-FD87-4631-AA7E-0005C2F935C3',
 '0860CE90-7B8A-408B-80BF-0005C530B3C0',
 'C510E820-514B-4086-876D-0005E2D8DA14',
 '65C6A59D-B835-4EFD-ABF1-0006003DB5BA',
 'C2F6ADB9-0B31-4C81-BD0A-000601FB378F',
 '97E4E20E-8B1E-4431-AE1D-000602DD4BC8',
 'DEB0975C-0638-44F8-B32E-000616D49EE6',
 'B2A363CD-7E2A-42AA-A36D-00061E0F51B0',
 '612903B9-3196-4178-98E8-00062DFBA9A6',
 'DFD3FFC8-4A4C-4063-82C4-000657C549DE',
 'AFE27F1E-0D6C-4E40-8C96-00067AF57FF3',
 '42E96C4C-0921-4D0D-A3AC-0006E293DBD2',
 '79F0F593-9782-4976-BDC4-0006F41426FA',
 'EA8FDD08-D447-4491-B317-000728468E04'
 )

—————————————-IO信息—————————————————-
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

(99 行受影响)
表 ‘20180627Tmp’。扫描计数 99,逻辑读取 524 次,物理读取 1 次,预读 2 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)


sqlserver union写法:

 DBCC DROPCLEANBUFFERS ---清理缓存

 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='B720964E-862A-4E99-913F-000033E571C4'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='F6A8CFDD-132C-4D93-A4A5-00004067D31C'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='61D889DF-A1F4-4C96-A862-00004597A850'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='A3A590D3-0807-4D89-B4EE-000049E51F88'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='FC07C694-D5FB-4A15-A965-00004D5226AF'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='9B8A7654-FF92-4051-B2F6-00005636E823'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='70625DA8-F3E2-4FA5-97FA-0000591CA7A1'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='B9BB6696-D7BC-40C9-99A8-00006A05F1AC'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='53B37306-9D2F-49D6-B411-00006B9A7B58'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='FDA181EF-1B12-469A-B0C9-000079776575'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='CA87F5B2-1A2A-4668-9C19-00007D576316'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='97851AFF-2B91-4656-B2DB-00008F5A6CCD'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='E078115C-0D44-46A1-BDF6-0000964251DA'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='FFCD4110-1E95-4F04-8E46-0000A91DD32D'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='3B58ECFD-5471-463C-BD9E-0000CBCDD360'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='26F44396-3A55-4B48-84AE-0000D073ACEC'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='DEAA2999-5DD5-45BC-B5AA-0000E462D625'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='9C7643D2-89F1-4248-8943-0000F102B608'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='2D9FF00F-C7EB-4E0B-AC34-0000F7634C9A'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='67251E8F-4205-4B16-9631-0000F7F9DD29'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='D2C59B11-0861-410E-8F75-00011F1485FA'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='306FFF3F-C8CB-49FA-AA8D-0001389992BF'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='E3063DD5-7DA2-4A55-B537-00017E242DF4'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='05CE4B27-A502-4794-AEDD-00019720118B'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='1E908768-B5C4-46F5-99D9-00019A5B72BA'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='95E4A677-C4CF-49E9-82D7-00019A759B6D'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='8AC6C0EA-BFE5-4354-8BE7-0001A5B9D526'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='C2E1C31D-10BF-41DE-AD46-0001A662C9DC'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='0172B13E-F127-40EE-AD7B-0001B709A3E4'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='2AF9CA4F-6C2C-4CEB-AD98-0001BC0490FB'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='3BF3AF07-EC78-49AE-B7B5-0001C6659896'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='5AFBD4FF-C51C-4AC4-B08F-00021B152C80'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='D0766BC3-6AD7-4D8B-99C9-00023A7D7673'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='3C5A899B-563D-4708-A25C-00025B9F4747'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='2052B2AE-AB93-4C06-8478-0002632E489F'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='0B1437FC-8A9B-4C7E-B46B-0002664291F0'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='E6184D8E-4AA4-4873-9442-00026842D6E1'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='F5C200BB-F5ED-425E-B069-0002854CEEC6'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='F70368B1-C47A-4040-B51D-0002862FBB46'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='328B1FF6-1E7A-45BD-BA9B-00028B691159'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='E9D6DFE1-0267-4A84-BD63-0002AB361266'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='7276A1CA-4C76-488A-BAEB-0002CEC9F0D5'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='D3A6AAE9-E4A4-4E38-84E3-0002D2188302'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='81FFF5F2-D582-45A2-84FE-0002D44527FA'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='BC6C363C-B139-4E95-8DEB-0002E49A8D79'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='79944EDD-009B-41C8-A3DD-0002F41A9D87'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='A03E0DFB-3024-4165-945C-00030B03B3C9'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='46354B31-2730-482F-BA84-00031AEEACCC'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='C2486FC5-0C4D-4068-8165-00031B1CDE41'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='43583104-D90A-4123-990A-00031DFC389B'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='E7AAF3F4-F918-45C5-AB2E-0003222CF7D5'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='10DEF07E-2323-40B1-80D1-00033109D167'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='3645861F-5A0C-44E4-8547-000340D08C40'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='C85BD068-BF16-40D6-A045-000347F8059A'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='84AFC461-CE9D-4138-839E-000383E2C7D7'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='F7B99896-6FB5-48FB-A65B-00038D7E2537'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='E3B4A72A-A0AA-4015-A29D-0003939BBEFE'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='F89C1F31-4630-437F-9E72-000394D2E7B6'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='500B321E-7F47-46DF-90F4-0003971140D2'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='BB7F44DE-B82E-41A2-80B1-0003A492FF2D'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='2F073374-A60A-4E94-8B56-0003B7A0A94F'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='3E528AA6-8FD4-438B-A40F-0003C796A269'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='8FD0E821-78DA-49D6-8DAC-0003CAE0F773'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='EE1553D6-756E-44E1-BE1E-0003EE1FFF8E'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='8A7CAACA-81CC-4975-B22A-00042AD22F3C'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='16AA75ED-0948-4449-8301-00043AB41CA6'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='DCF7D92B-E4BE-43F2-96CF-000444CFBD25'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='0566DF96-7C72-46CD-8246-000449674685'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='EFBFBF8D-D64F-4B3C-937A-00045E5FFD31'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='CFE90F9A-4E76-4225-9097-000463897EED'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='25555158-0851-4DAB-B3F7-00049850ED3C'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='BB256D2C-C6C2-4FCE-8C76-00049E66189D'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='6E15EAEF-78CC-47F8-8CD8-0004CA7A3610'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='49977E00-E860-4DF1-8E86-0004E84D9190'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='AB186827-0A37-415C-9ADF-0004F28A3E1E'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='D607B4C5-7027-4280-82EC-000513DFC9F0'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='20638DA0-96BA-472F-A89C-000526DDB7E7'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='7A7AFD28-11BE-45C2-A828-0005335F1CB4'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='01145555-4D92-47E5-9DE2-0005418205DE'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='85492F5C-3BEA-4FE1-A22C-000556AC8719'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='BB117951-77A4-4BE1-B6D9-000567EB6E90'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='B4AC5BFC-B0FC-43E7-A5D9-000569E76707'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='DA89F967-6F01-45E5-B016-000570EA4EBF'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='3E12AD59-7F05-4F70-9D5D-0005B4A1A92F'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='F379B7A9-6A2C-41C8-AD3B-0005B96E1E10'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='245AA49F-FD87-4631-AA7E-0005C2F935C3'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='0860CE90-7B8A-408B-80BF-0005C530B3C0'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='C510E820-514B-4086-876D-0005E2D8DA14'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='65C6A59D-B835-4EFD-ABF1-0006003DB5BA'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='C2F6ADB9-0B31-4C81-BD0A-000601FB378F'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='97E4E20E-8B1E-4431-AE1D-000602DD4BC8'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='DEB0975C-0638-44F8-B32E-000616D49EE6'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='B2A363CD-7E2A-42AA-A36D-00061E0F51B0'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='612903B9-3196-4178-98E8-00062DFBA9A6'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='DFD3FFC8-4A4C-4063-82C4-000657C549DE'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='AFE27F1E-0D6C-4E40-8C96-00067AF57FF3'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='42E96C4C-0921-4D0D-A3AC-0006E293DBD2'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='79F0F593-9782-4976-BDC4-0006F41426FA'
 union 
 select  tmpZiDuan from [20180627Tmp] 
where    tmpZiDuan ='EA8FDD08-D447-4491-B317-000728468E04'

————————————IO信息————————————–
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
(99 行受影响)
表 ‘20180627Tmp’。扫描计数 99,逻辑读取 297 次,物理读取 3 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)

总结分析:
1.in、or、union三种写法在有索引的情况下,在数据量不大的情况下具有同等的效率
2.in、or、union三种写法在逻辑读方面,union需要逻辑读取的次数比in和or少
—————————————————
从逻辑读的次数上分析,在有索引列的情况下union 性能高于in和or关键字