SQL WHERE子句优化

Question

假设我有以下三种类型:

• 类型1
• Type2
• Type3

从优化的角度(忽略可维护性;我知道如果添加新类型，第一个选项不太可能被破坏);这样写where子句会更有效率吗?

WHERE p.TypeId IN (Type1, Type2)

或

WHERE p.TypeId <> Type3

Answer 1

第二个选项:

WHERE p.TypeId <> Type3

将更有效地书写，因为它使用更少的字符，除非你在iPad上，在这种情况下，它会很慢地到达<和>字符!

在执行方面，它也将更快地执行。显然，由于只需要进行一次比较，所以要做的工作更少，因此无论您以何种方式优化它都会比使用两个元素的IN更快。

Answer 2

这可能取决于索引的配置方式和表中的数据，但对于一个简单的测试用例，可以演示它们在SQL Server 2005中创建相同的查询计划。

下面的代码在SQL Server 2005实例上运行，为OR、IN和不等式版本的查询生成相同的查询计划。每个查询产生相同数量的记录。第一组查询使用3值相等分布。第二组查询使用10个值的相等分布。

CREATE TABLE #Test (TestID int, TestTypeID int);
WITH TallyTable AS
(
    SELECT
    TOP 10000
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Tally
    FROM            sys.objects s1
    CROSS JOIN      sys.objects s2
), InsertList AS
(
    SELECT
        Tally AS TestID,
        Tally % 3 AS TestTypeID
    FROM    TallyTable
)
INSERT INTO #Test
    (
        TestID,
        TestTypeID
    )
SELECT
    TestID,
    TestTypeID
FROM        InsertList;
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID = 0
OR          TestTypeID = 2;
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID IN (0,2);
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID <> 1;
CREATE INDEX TestIndex ON #Test (TestTypeID);
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID = 0
OR          TestTypeID = 2;
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID IN (0,2);
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID <> 1;
DROP INDEX TestIndex ON #Test;
TRUNCATE TABLE #Test;
WITH TallyTable AS
(
    SELECT
    TOP 10000
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Tally
    FROM            sys.objects s1
    CROSS JOIN      sys.objects s2
), InsertList AS
(
    SELECT
        Tally AS TestID,
        Tally % 10 AS TestTypeID
    FROM    TallyTable
)
INSERT INTO #Test
    (
        TestID,
        TestTypeID
    )
SELECT
    TestID,
    TestTypeID
FROM        InsertList;
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID = 0
OR          TestTypeID = 2
OR          TestTypeID = 3
OR          TestTypeID = 4
OR          TestTypeID = 5
OR          TestTypeID = 6
OR          TestTypeID = 7
OR          TestTypeID = 8
OR          TestTypeID = 9;
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID IN (0,2,3,4,5,6,7,8,9);
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID <> 1;
CREATE INDEX TestIndex ON #Test (TestTypeID);
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID = 0
OR          TestTypeID = 2
OR          TestTypeID = 3
OR          TestTypeID = 4
OR          TestTypeID = 5
OR          TestTypeID = 6
OR          TestTypeID = 7
OR          TestTypeID = 8
OR          TestTypeID = 9;
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID IN (0,2,3,4,5,6,7,8,9);
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID <> 1;
DROP TABLE #Test;

这个集合使用了3个值的不相等分布，也产生了相同的查询计划。

CREATE TABLE #Test (TestID int, TestTypeID int);
WITH TallyTable AS
(
    SELECT
    TOP 10000
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Tally
    FROM            sys.objects s1
    CROSS JOIN      sys.objects s2
), InsertList AS
(
    SELECT
        Tally AS TestID,
        CASE
            WHEN Tally % 1000 = 0 THEN 2
            WHEN Tally % 100 = 1 THEN 1
            ELSE 0
        END AS TestTypeID
    FROM    TallyTable
)
INSERT INTO #Test
    (
        TestID,
        TestTypeID
    )
SELECT
    TestID,
    TestTypeID
FROM        InsertList;
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID = 0
OR          TestTypeID = 2;
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID IN (0,2);
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID <> 1;
CREATE INDEX TestIndex ON #Test (TestTypeID);
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID = 0
OR          TestTypeID = 2;
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID IN (0,2);
SELECT
    TestTypeID
FROM        #Test
WHERE       TestTypeID <> 1;
DROP TABLE #Test;