DBMS索引的选择

约定：这里以SQL Server 2008 DB Engine为环境

问题：这是一家公司的面试问题

有一张表结构如下

tbl（

     col1 int

 ,col2 int

col3 int

）

1。在col1上有non clustered index，表上没有其他约束

假设col1上有=1的这条记录

那么下面的query是否会使用索引

select *

from tbl

where col1 = 1；

问题分析：这个问题问得其实非常模糊，不管你回答引擎会选择索引或引擎不回选择索引，对方都可以找出问题。

原因就是：数据引擎选择索引的依据是表的统计信息，比如表里面的数据量的多少会影响引擎对索引的使用，如果表中数据少，引擎会直接table scan；

而数据量多，则会考虑索引。

例如新建tbl，插入3条数据，查看执行计划，如图，引擎使用Table Scan

使用cross join增加表中数据量

select col1 = t1.col1 + t2.col2
     , col2 = t1.col2 + t2.col3
     , col3 = t1.col3 + t2.col1
from  tbl t1
cross join tbl t2

几次运行后，表中数据增加到 24，336 行

 

再次查看上面查询的查询计划,索引有被选择， 

但是是不是所有条件的query索引都会被选择呢？答案是否定的

当条件改为 col1 = 24 时，查询计划为table scan并建议需要把col2和col3加入到索引列里面去

select col1, col2, col3
from tbl
where col1 = 24;

为什么呢？原因就是统计信息，DBMS选择索引时，要根据数据的分布来选择数据检索方式。举个极端的例子：比如表中有1万条记录，假设已经知道其中col1=100的记录会超过5000条，那么这时候使用全表扫描可能更快些。