走向DBA[MSSQL篇] 面试官最喜欢的问题 ----索引+C#面试题客串

2011-11-07 13:05 熬夜的虫子阅读(3175) 评论(21) 编辑收藏举报

对大量数据进行查询时,可以应用到索引技术。索引是一种特殊类型的数据库对象，它保存着数据表中一列或者多列的排序结果，有效地使用索引可以提高数据的查询效率。大家面试初级、中级或者高级程序员的时候应该大部分都会被问到这样一些问题，你了解索引吗？你知道索引的分类吗？你知道这些索引的区别吗？你如何去创建有效的索引。本章让大家学会反问面试官 hold住全场。 --_____--

友情客串

最近面试的文章比较火客串一下我只简单问2个问题看看有多少人能直接回答出来都是比较基础的

1.基类虚函数、派生类重写与基类普通函数、派生类覆盖的区别（-____-）

2.Using闭包中try catch，异常捕获后抛出，那么using资源能否正常释放，概述原因（-____-）

答案在文章结尾揭晓

基本知识 （面试官会问的）

索引是神马

一般说法：索引是与表关联的磁盘上结构，可以加快从表中检索行的速度。索引包含由表中的一列或多列生成的键。这些键存储在一个结构中，使 SQL Server 可以快速有效地查找与键值关联的行。

hold说法：索引说白了就是Balanced Tree(简称B树)结构，多层次、自维护，节点存放表的数据标识信息，如果表中的一条记录在磁盘上占用500字节的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引块的大小只有10字节。这样检索IO访问量要少的多。

索引的分类

一般说法：聚集索引与非聚集索引

hold说法：聚集索引、非聚集索引、唯一索引、包含性列索引、索引视图、全文索引、XML索引

说下聚集索引与非聚集索引的区别

一般说法：聚集索引是基于记录内容在数据表内的排序和存储。非聚集索引不会为数据表的数据进行物理上排序，只是将索引建立在索引页上，在查询数据时一样可以从索引中找到记录存放的位置。

hold说法：聚集索引根据数据行的键值在表中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。
非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位器是聚集索引键。

一张表中可以建立几个聚集索引和非聚集索引

一般说法：1个聚集索引和249个非聚集索引

hold说法：冷哼一声、笑而不语。

索引的设计原则

一般说法：表增删改操作多的少用索引查操作多的适当建立索引

hold说法：

1.大量的索引会影响DML语句的性能；

2. 对小表进行索引可能不会产生优化效果；

3.对经常用于查询中的谓词和连接列创建非聚集索引；

4.覆盖索引可以提高查询性能；

5.聚集索引，最好保持较短的索引键；

6.考虑对定义完善的的列使用筛选索引；

7.如果索引包含多个列，应考虑列的顺序；

进阶知识（可以用来调戏面试官）

创建索引的限制

每个索引最多16个键列；
索引键最长900字节；
大型对象数据类型不能作为索引键列；

唯一索引:唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。聚集索引和非聚集索引都可以是唯一索引。

设计原则：

唯一索引能够保证索引键中不包含重复的值，从而使表中的每一行从某种方式上具有唯一性。只有当唯一性是数据本身的特征时，指定唯一索引才有意义。例如，如果您希望确保 HumanResources.Employee 表的 NationalIDNumber 列中的值唯一，当主键为 EmployeeID 时，可以为 NationalIDNumber 列创建一个 UNIQUE 约束。如果用户尝试在该列中为多个雇员输入相同的值，将显示错误消息并且不能输入重复的值。

唯一索引能够确保定义的列的数据完整性并提供了对查询优化器有用的附加信息。

包含性列索引：一种非聚集索引，它扩展后不仅包含键列，还包含非键列。

设计原则：

重新设计索引键大小较大的非聚集索引，以便只有用于搜索和查找的列为键列。将覆盖查询的所有其他列设置为包含性非键列。这样，将具有覆盖查询所需的所有列，但索引键本身较小，而且效率高。

USE AdventureWorks;
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';

若要覆盖查询，必须在索引中定义每列。尽管可以将所有列定义为键列，但键大小为 334 字节。因为实际上用作搜索条件的唯一列是 PostalCode 列（长度为 30 字节），所以更好的索引设计应该将 PostalCode 定义为键列并包含作为非键列的所有其他列。

USE AdventureWorks;
GO
CREATE INDEX IX_Address_PostalCode       
ON Person.Address (PostalCode)       
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

索引视图：视图的索引将具体化（执行）视图，并将结果集永久存储在唯一的聚集索引中，而且其存储方法与带聚集索引的表的存储方法相同。创建聚集索引后，可以为视图添加非聚集索引。

设计原则：对视图创建索引的另一个好处是：优化器可以在未直接在 FROM 子句中指定某一视图的查询中使用该视图的索引。这样一来，可从索引视图检索数据而无需重新编码，由此带来的高效率也使现有查询获益。

索引视图可以提高下列查询类型的性能：1.处理大量行的联接和聚合。2.许多查询经常执行的联接和聚合操作。 3.决策支持工作负荷。

全文索引：这个虫子以后会单独开全文检索章节来讲

XML：xml 数据类型列中 XML 二进制大型对象 (BLOB) 的已拆分持久表示形式。XML实例的最大数据量可以达到2GB,如果在没有索引的xml字段里查询会很
耗时，在XML字段上创建的索引就是xml索引。
设计原则：主 XML 索引，辅助 XML 索引。xml 类型列的第一个索引必须是主 XML 索引。使用主 XML 索引时，支持三种类型的辅助索引。这些类型包括 PATH、VALUE 和 PROPERTY。根据查询类型的不同，这些辅助索引可能有助于改善查询性能。

提高篇（和面试无关了）

索引优化

无论何时对基础数据执行插入，更新或删除操作，数据库引擎都会自动维护索引。这些修改可导致索引中产生碎片；碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。

1.定期整理索引；
2.设置索引并行度；
3.分析TRACE数据，调整索引；

聚集表、堆和索引

聚集表是有聚集索引的表。数据行基于聚集索引键按顺序存储。聚集索引按 B 树索引结构实现，B 树索引结构支持基于聚集索引键值对行进行快速检索。索引中每个级别的页（包括叶级别的数据页）链接在一个双向链接的列表中。但是，通过使用键值来执行从一个级别到另一级别的导航。

堆是没有聚集索引的表。数据行不按任何特殊的顺序存储，数据页也没有任何特殊的顺序。数据页不在链接列表内链接。

菜鸟如何有效的创建索引

前提是你有线上服务器的权限 -___-

首先打开SQL Server Profiler新建一个跟踪