SQL Server评价索引之有效性

数据库 SQL Server
以下的文章主要是对SQL Server评价索引之有效性(Evaluating Index Usefulness)的介绍,以下就是文章的主要内容讲述。

以下的文章主要描述的是SQL Server评价索引之有效性(Evaluating Index Usefulness),SQL Server数据库所提供的索引主要有2个原因:***是作为一种保证数据库表中数据唯一性的方法;其二,提供了一种快速访问表中数据的方法。

创建合适的索引是数据库物理设计时最为重要的方面之一。因为你不能在一个表上无限制地创建索引,而且不管怎么说,它也是不可行的。所以,你将想在一些具有高选择性 (high Selectivity )的列上创建索引,这样,查询时系统将会利用这些索引。一个SQL Server评价索引的选择性定义如下:

 

 

引用

选择率 = (唯一索引值的个数)/ (表中所有行数)

 

 

  1. Selectivity ratio = (Number of unique index values)/ (Total number of rows in the 

 

 

 

如果选择率高——也就是说,大量行都可以用索引键值来唯一标识——那么该SQL Server评价索引就具有高选择性,即对优化器来说也是有用的。***的选择性是1,即每一行都有一个唯一的索引键值。低选择性意味着表中有许多重复的键值,这样的索引将很少有用。

SQL Server优化器基于索引的选择性来决定对一个查询是否使用索引。越高的选择性,SQL Server检索结果集(Result set)就越快和越有效。

 

例如,你正在对authors 表中的索引的有效性进行评估。假如大多数查询是以author's last name或者state来进行访问的。因为大量的并发用户会修改该表的数据,你只允许一个索引——author's last name或者state,你将会选择谁?让我们进行一些分析来判断哪个索引更有效些,或者更有选择性。首先,利用一个查询来确定pubs数据库中 author表的last name列的有效性:

Sql代码

  1. select count(distinct au_lname) as '# unique',   
  2. count(*) as '# rows',   
  3. str(count(distinct au_lname) / cast (count(*) as real),4,2) as 'selectivity'   
  4. from authors   
  5. go   
  6. select count(distinct au_lname) as '# unique',   
  7. count(*) as '# rows',   
  8. str(count(distinct au_lname) / cast (count(*) as real),4,2) as 'selectivity'   
  9. from authors   
  10. go   
  11. # unique # rows selectivity   
  12. 22 23 0.96   

 

 

author表的au_lname列的有效率计算值为0.96,表明在au_lname创建的SQL Server评价索引将具有高选择性,也是一个好的候选索引。除了一行外,其余所有行的last name值都唯一。 现在,来分析state列的选择性:

 

 

Sql代码

 

  1. select count(distinct state) as '# unique',   
  2. count(*) '# rows',   
  3. str(count(distinct state) / cast (count(*) as real),4,2) as 'selectivity'   
  4. from authors   
  5. go   
  6. select count(distinct state) as '# unique',   
  7. count(*) '# rows',   
  8. str(count(distinct state) / cast (count(*) as real),4,2) as 'selectivity'   
  9. from authors   
  10. go   
  11. # unique # rows selectivity   
  12. 8 23 0.35   

 

正如你所看到的,state列的SQL Server评价索引选择率(0.35)比au_lname索引选择率要低很多,将不太有用。

 

对于这一点,你可能会问,是否因为state列中的一些值具有较高的重复性而导致了选择性的下降,或者说仅仅只有一些值具有唯一性。你可以用下面的查询来确定:

 

 

Sql代码

 

  1. select state, count(*)   
  2. from authors   
  3. group by state   
  4. order by 2 desc   
  5. go   
  6. select state, count(*)   
  7. from authors   
  8. group by state   
  9. order by 2 desc   
  10. go   
  11. state   
  12. CA 15   
  13. UT 2   
  14. TN 1   
  15. MI 1   
  16. OR 1   
  17. IN 1   
  18. KS 1   
  19. MD 1   

 

 

正如你所预料到的,state值,除了一个外,其余值都相对唯一。因为表中有多一半的state值都为‘CA’。所以state可能不是一个好的候选索引列,特别是假如大部分时间你都以CA来进行查询,此时,SQL Server将发现扫描整个表将比借助索引进行查询数据更有效。

一般来说,如果一个键值的选择率低于0.85,那么优化器通常会选择表扫描来处理查询。在这种情况下,使用表扫描来获取所有满足条件的数据行将比通过B-Tree来定位大量数据行来查找更有效率。

如果有更多的索引可以选择,那么SQL Server将怎样来确定每个索引是否具有选择性和到底选择哪一个索引对用户来说更有效呢?例如,SQL Server怎么知道下面的索引能够返回多少行?

  1. select * from table where key between 1000000 and 2000000  

如果该表在0到20,000,000之间有10,000,000行记录,优化器如何知道是使用一个索引还是进行表扫描呢?如果在该范围内有10行记录,或者900,000,又如何选择?SQL Server如何来估计在1,000,000 至2,000,000之间有多少行?等等诸如此类的问题,优化器是从SQL Server评价索引统计(Index Statistics)中获得这些信息的。

【编辑推荐】

  1. SQL Server 实用操作的代码演示
  2. SQL Server 2005数据库安装实例演示
  3. SQL Server 2005 Express混合模式登录的设置
  4. SQL Server数据库在安装时的注意事项
  5. SQL Server Compact中的DLL文件与工具

 

责任编辑:佚名 来源: csdn.net
相关推荐

2021-03-08 10:48:04

AI

2023-12-08 16:32:35

GenAI人工智能AI

2023-09-05 07:17:23

2022-09-28 08:18:01

I/ONIO2API

2022-01-19 09:01:28

字符串LeetCode

2015-03-24 11:04:58

2010-04-30 16:24:51

2016-07-26 11:21:53

2023-07-03 09:49:49

2009-04-16 17:55:15

扩展热插拔SQL Server

2010-07-28 16:40:38

2021-09-07 06:40:26

状态机识别地址

2021-03-02 08:21:58

LeetCode括号

2010-09-16 13:42:55

SQL SERVER索

2010-07-20 13:20:26

SQL Server聚

2023-06-06 12:11:51

2010-08-23 10:04:48

CSS浮动

2010-03-23 16:41:17

云计算

2015-03-16 11:16:59

生物识别身份验证数据中心

2010-09-10 13:24:21

DIV表格
点赞
收藏

51CTO技术栈公众号