一个字段只有0和1的字段,到底要不要建索引?

小白上次面试遇到一道数据库优化方面的面试题,一个值只有0和1的字段,到底要不要建索引?小白当时的回答是不需要建索引,为啥呢?没有回答出来。对于这个问题,在网上查询了一下有两种解答。1、两者数据均匀不需要索引;2、是两者数据差距较大的情况下需要索引,也就是数据1为1000条,数据0为百万条。那么我们今天就这个问题来测试一下,使用的数据库是SQL Server。我们拿1千万数据来测试一下。.

    我们创建一张测试表,有三个字段Id、sex、UserName。

1、验证不需要索引

    首先我们验证一下不需要索引,用循环创建20万以上数据。其中sex为0的10万条,sex为1的10万条。

declare @num intset @num=0while @num<10000  --先循环1万条数据begin  set @num=@num+1 INSERT INTO [dbo].[Test] (Sex,UserName) values (1,N'张山')print @numend--批量重复拷贝数据,这样快点INSERT INTO [dbo].[Test] (Sex,UserName) select Sex,UserName from [dbo].[Test]

1)、20万数据未建索引时查询Sex为1的效果。由于计算机性能不稳定的原因,我们执行三次,执行时间如下。

select * from Test1 where sex=0

一个字段只有0和1的字段,到底要不要建索引?

一个字段只有0和1的字段,到底要不要建索引?

一个字段只有0和1的字段,到底要不要建索引?

200000数据建索引时查询Sex为1的效果。执行三次,执行时间如下。

select * from Test1 where sex=0

一个字段只有0和1的字段,到底要不要建索引?

一个字段只有0和1的字段,到底要不要建索引?

一个字段只有0和1的字段,到底要不要建索引?

由上可以看出差距不是太大啊。

2)、为了验证数据的准确性我把数据量增加到百万级别,100万是SEX为1,100万SEX为0,然后分别验证。

200万数据未建索引时查询Sex为1的效果。分别执行三次,执行时间如下:

select * from Test1 where sex=0

一个字段只有0和1的字段,到底要不要建索引?

一个字段只有0和1的字段,到底要不要建索引?

一个字段只有0和1的字段,到底要不要建索引?

200万数据建索引时查询Sex为1的效果。分别执行三次,执行时间如下:

select * from Test1 where sex=0

一个字段只有0和1的字段,到底要不要建索引?

一个字段只有0和1的字段,到底要不要建索引?

一个字段只有0和1的字段,到底要不要建索引?

由上可以看出,加了索引后CPU时间反而增加了,占用时间减少。从占用来说索引还是有点用啊。差好几秒呢。

注意:这个测试有问题,我检查了一下数据不均等,虽然都是100万,但是分布不均。接下来做均等的数据。

3)、均等的插入100万是SEX为1,100万SEX为0的数据,然后分别验证。

未建索引

  select * from Test1 where sex=0 --  第一次  --  SQL Server 执行时间:  --  CPU 时间 = 2079 毫秒,占用时间 = 6444 毫秒。  --  第二次 --    SQL Server 执行时间: --   CPU 时间 = 2438 毫秒,占用时间 = 6117 毫秒。 --   第三次 --    SQL Server 执行时间: --   CPU 时间 = 1954 毫秒,占用时间 = 6209 毫秒。

建索引

 select * from Test1 where sex=0 --  第一次  --  SQL Server 执行时间:  --  CPU 时间 = 2406 毫秒,占用时间 = 6529 毫秒。  --   第二次  --   SQL Server 执行时间: --   CPU 时间 = 2437 毫秒,占用时间 = 6282 毫秒。  --     第三次 --  SQL Server 执行时间: --   CPU 时间 = 2717 毫秒,占用时间 = 6187 毫秒。

由此可见差距不是很大。

为啥这样呢?

索引的本质就是排序,大家都排好对了,并且只有两队,所以好找。索引本质是B+树,由于是连续的,非常容易就找到了。

2、验证0和1的数据量差距较大的情况

我们这里创建188万的SEX为1的数据,然后再创建2000条为0的数据,并且数据分布不均匀,我们来测试一下。

未建索引的效果

 select * from Test1 where sex=0  -- CPU 时间 = 1860 毫秒,占用时间 = 226 毫秒。

建索引后的效果

select * from Test1 where sex=0-- CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

看出效果了吗?非常有效。

3、结论

       从上面可以看出,这个答案不是绝对的,如果在数据分布绝对均匀的情况下,建索引没有用,甚至会增加负担。在数据不均匀和数据量对比较大的情况下,建索引也是相当有用的,当然是百万级别以上的数据量了。如果你的数据达不到百万,建议还是不要建索引了,建了作用不大。

    这是sql server 测试的结果,感兴趣的伙伴可以测试一下mysql。希望本文对你有收获,同时欢迎留言讨论。

PS 

    CPU时间:CPU执行语句的时间

    占用时间:从磁盘读取数据,处理的总时间。(CPU时间+阻塞时间)