前言
我看过不少对Bit字段能否建立索引,以及建立索引后性能如何的讨论,还有朋友建议用Tinyint代替Bit,我在这里深入研究一下:
研究方法:
一、建立六张表,具体说明见SQL语句中的注释部分:
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
CREATE DATABASE IndexTest
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
USE IndexTest
GO
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- 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] )
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- 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)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- 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)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- 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] )
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- 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)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- 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)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
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成本与成本
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
--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
四、比较执行计划中的成本:
数据收集如下表:
数据分析:
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
我看过不少对Bit字段能否建立索引,以及建立索引后性能如何的讨论,还有朋友建议用Tinyint代替Bit,我在这里深入研究一下:
研究方法:
一、建立六张表,具体说明见SQL语句中的注释部分:
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
二、分别填充10万条数据,其中Bit与Tinyint各有一张表的0、1比例悬殊(97:3)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
三、分别搜索六张表中ColumnBit = 0 或者 ColumnTinyint = 0的数据,并在执行计划中查看I/0成本、CPU成本与成本
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
四、比较执行计划中的成本:
|
|
|
|
|
|
数据收集如下表:
|
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