SqlServer中Bit字段索引性能问题研究

前言
我看过不少对Bit字段能否建立索引，以及建立索引后性能如何的讨论，还有朋友建议用Tinyint代替Bit，我在这里深入研究一下：

研究方法：
一、建立六张表，具体说明见SQL语句中的注释部分：

CREATE DATABASE IndexTest
GO 

USE IndexTest 
GO 


-- bit无索引，0、1相等
CREATE TABLE dbo.TestBitNoIndex
(
    TestID INT NOT NULL,
    ColumnBit BIT NOT NULL 
)
ALTER TABLE [dbo].[TestBitNoIndex] WITH NOCHECK ADD CONSTRAINT [PK_TestBitNoIndex] PRIMARY KEY  CLUSTERED ( [TestID] ) 


-- bit有非聚集索引，0、1相等
CREATE TABLE dbo.TestBitIndexed
(
    TestID INT NOT NULL,
    ColumnBit BIT NOT NULL 
)
ALTER TABLE [dbo].[TestBitIndexed] WITH NOCHECK ADD CONSTRAINT [PK_TestBitIndexed] PRIMARY KEY  CLUSTERED ( [TestID] ) 
CREATE INDEX IX_TestBitIndexed_ColumnBit ON dbo.TestBitIndexed(ColumnBit)


-- bit有非聚集索引，0、1相差悬殊
CREATE TABLE dbo.TestBitIndexedWideGap
(
    TestID INT NOT NULL,
    ColumnBit BIT NOT NULL 
)
ALTER TABLE [dbo].[TestBitIndexedWideGap] WITH NOCHECK ADD CONSTRAINT [PK_TestBitIndexedWideGap] PRIMARY KEY  CLUSTERED ( [TestID] ) 
CREATE INDEX IX_TestBitIndexedWideGap_ColumnBit ON dbo.TestBitIndexedWideGap(ColumnBit)


-- tinyint无索引，0、1相等
CREATE TABLE dbo.TestTinyintNoIndex
(
    TestID INT NOT NULL,
    ColumnTinyint TINYINT NOT NULL 
) 
ALTER TABLE [dbo].[TestTinyintNoIndex] WITH NOCHECK ADD CONSTRAINT [PK_TestTinyintNoIndex] PRIMARY KEY  CLUSTERED ( [TestID] ) 


-- tinyint有非聚集索引，0、1相等
CREATE TABLE dbo.TestTinyintIndexed
(
    TestID INT NOT NULL,
    ColumnTinyint TINYINT NOT NULL 
) 
ALTER TABLE [dbo].[TestTinyintIndexed] WITH NOCHECK ADD CONSTRAINT [PK_TestTinyintIndexed] PRIMARY KEY  CLUSTERED ( [TestID] ) 
CREATE INDEX IX_TestTinyintIndexed_ColumnTinyint ON dbo.TestTinyintIndexed(ColumnTinyint)


-- tinyint有非聚集索引，0、1相差悬殊
CREATE TABLE dbo.TestTinyintIndexedWideGap
(
    TestID INT NOT NULL,
    ColumnTinyint TINYINT NOT NULL 
)
ALTER TABLE [dbo].[TestTinyintIndexedWideGap] WITH NOCHECK ADD CONSTRAINT [PK_TestTinyintIndexedWideGap] PRIMARY KEY  CLUSTERED ( [TestID] ) 
CREATE INDEX IX_TestTinyintIndexedWideGap_ColumnTinyint ON dbo.TestTinyintIndexedWideGap(ColumnTinyint)

二、分别填充10万条数据，其中Bit与Tinyint各有一张表的0、1比例悬殊（97:3）

DECLARE 
    @i INT, 
    @SameBitFlag BIT, -- 0、1 50比50
    @WideGapBitFlag BIT, -- 0、1 97比3
    @SameTinyintFlag BIT, -- 0、1 50比50
    @WideGapTinyintFlag BIT -- 0、1 97比3
 
SELECT 
    @i = 1, 
    @SameBitFlag = 0, 
    @SameTinyintFlag = 0,
    @WideGapBitFlag = 0,
    @WideGapTinyintFlag = 0
 
WHILE @i <= 100000
BEGIN 
    IF @i > 50000 
        SET @SameBitFlag = 1
        SET @SameTinyintFlag = 1 
 
    IF @i > 97000 
        SET @WideGapBitFlag = 1
        SET @WideGapTinyintFlag = 1
 
    INSERT INTO [dbo].[TestBitNoIndex](TestID, ColumnBit) VALUES (@i, @SameBitFlag)
    INSERT INTO [dbo].[TestBitIndexed](TestID, ColumnBit) VALUES (@i, @SameBitFlag)
    INSERT INTO [dbo].[TestTinyintNoIndex](TestID, ColumnTinyint) VALUES (@i, @SameBitFlag)
    INSERT INTO [dbo].[TestTinyintIndexed](TestID, ColumnTinyint) VALUES (@i, @SameBitFlag)
 
    INSERT INTO [dbo].[TestBitIndexedWideGap](TestID, ColumnBit) VALUES (@i, @WideGapBitFlag)
    INSERT INTO [dbo].[TestTinyintIndexedWideGap](TestID, ColumnTinyint) VALUES (@i, @WideGapTinyintFlag)
 
    SET @i = @i + 1 
END

三、分别搜索六张表中ColumnBit = 0 或者 ColumnTinyint = 0的数据，并在执行计划中查看I/0成本、CPU成本与成本

--Test1
SELECT TestID FROM [dbo].[TestBitNoIndex] WHERE ColumnBit = 0
--Test2
SELECT TestID FROM [dbo].[TestBitIndexed] WHERE ColumnBit = 0
--Test3
SELECT TestID FROM [dbo].[TestBitIndexedWideGap] WHERE ColumnBit = 0
--Test4
SELECT TestID FROM [dbo].[TestTinyintNoIndex] WHERE ColumnTinyint = 0
--Test5
SELECT TestID FROM [dbo].[TestTinyintIndexed] WHERE ColumnTinyint = 0
--Test6
SELECT TestID FROM [dbo].[TestTinyintIndexedWideGap] WHERE ColumnTinyint = 0

四、比较执行计划中的成本：

 

Test1：Bit无索引，0、1数据量相等	Test4：Tinyint无索引，0、1数据量相等
Test2：Bit有索引，0、1数据量相等	Test5：Tinyint有索引，0、1数据量相等
Test3：Bit有索引，0、1数据量悬殊	Test6：Tinyint有索引，0、1数据量悬殊

数据收集如下表：

	I/O成本	CPU成本	成本
Bit无索引，0、1数据量相等	0.164	0.109	0.274653
Bit有索引，0、1数据量相等	0.110	0.109	0.220691
Bit有索引，0、1数据量悬殊	0.110	0.110	0.220990
Tinyint无索引，0、1数据量相等	0.164	0.109	0.274653
Tinyint有索引，0、1数据量相等	0.0426	0.0549	0.097554
Tinyint有索引，0、1数据量悬殊	0.0774	0.106	0.184218

数据分析：
1、对于Bit，有索引与无索引、01数据量相等与数据量悬殊的各项成本相差都不大；
2、对于Tinyint，01数据量相等时，有索引比无索引降低I/O成本接近5倍、降低CPU成本近2倍、降低成本近3倍；
3、对于Tinyint，01数据量悬殊时，比数据量相等要增加各项成本近2倍；
4、无索引、数据量相同时，Bit与Tinyint成本完全相同；
5、有索引、数据量相同时，Bit比Tinyint增加成本接近3倍；
6、有索引、数据量悬殊时，Bit比Tinyint增加成本近2倍。

结论：
1、毋庸置疑，Bit可以建立索引；
2、在10万条数据时，Bit是否建立索引成本消耗相差不大；
3、强烈推荐用Tinyint代替Bit，10万条数据时，成本降低近3倍，相信随着数据量的增加，与Bit相比成本降低会更多，有兴趣的朋友可以再深入测试一下。

参考：
http://sqlserver2000.databases.aspfaq.com/can-i-create-an-index-on-a-bit-column.html

 

复制自：

http://www.cnblogs.com/zhubo/archive/2008/02/27/index_performance_between_bit_and_tinyint.html