论坛上看到的6个数据库面试题

原帖链接：

http://topic.csdn.net/u/20120730/15/b682aa5b-1672-4a90-b6c7-91430313eb2f.html?seed=856407833&r=79268448#r_79268448

 

1, 建立索引一定会减少查询时间吗？也就是提高查询效率
2, 一个系统，你使用了一年后，系统响应突然变慢了，请分析变慢的原因
3, 有一个很大的表，建立索引一般需要注意些什么，在什么字段合理？
 

如果是面DBA，这三个大概是：
1. 请解释一下表的存储以及聚集和非聚集索引结构。
2. 如果系统变慢，你如何针对DB做一个论断检查？如何确定是CPU、内存、IO有瓶颈？请举例你之前优化过的成功案例及解决方法。
3. 你以前接触的业务的核心表有多大？有多少索引？建一个索引大概有多久？

 

下面是我对问题的想法，首先是上面的3个问题：

 

 

1、建立索引不一定能减少查询时间。因为如果建立的索引不合适，查询时生成的查询计划不一定会用到这个索引，当然也有可能会用这个索引。如果数据量不大，那么索引的作用不大，如果数据量很大，那么如果查询计划使用了这个不适合的索引，说不定还是能提升效率的，当然也有可能变的更慢，本来只需要全表扫描或者聚集索引扫描的，用了索引，而这个索引又没有查询需要的其他字段或者建立索引时字段顺序不对，那么可能对这个索引进行跳跃式扫描、还会继续进行聚集索引扫描，这样效率更低，还不如来个全扫描呢。

再加上如果统计信息不是很新，导致查询优化器判断有误，那么可能会更慢。关键还是看这个索引和查询是否合适。

 

2、系统在使用一段时间后变的更慢，如果平时有一些基本的性能监控的话，那么首先应该看一下大概是什么原因导致的，是cpu使用率很高，还是io等待队列很长，还是内存的问题。

如果是cpu，那么cpu的使用率主要和3个方面有关：

一个是sql语句的解析，如果改不了代码，那么可通过设置系统强制参数化来减少硬解析次数，如果能改代码，那么尽量使用参数化的查询；

一个是sql语句写的有问题导致逻辑读次数太多，当然也是随着数据量越来越大，索引存在碎片需要重新组织、重建，或者是索引建立的不合适，或者是统计信息不是最新的导致产生了不好的查询计划；

一个是并行执行计划导致cpu使用率同时上升，虽然使用并行执行计划往往能是查询的执行时间更短，但是总的开销更大。

如果是磁盘，那么要监控这些磁盘的等待队列，当然等待队列太长，不仅说明磁盘很忙，也可能说明应该优化存储，比如建立分区表、分区索引，这有什么好处呢？

比如原来的表有1亿条记录，现在分区后每个分区1000万条，如果我们建了索引，那么单个分区的索引中页级别有1000万条索引记录，相对于原来的索引的1亿条索引记录来说整个规模缩小了10倍，效率肯定是大大提升的，同时可以把这些分区物理的放到不同的磁盘上，这样如果要查1亿条记录，那么可以同时启动多个硬盘来并行的读取数据。

如果是内存使用量很大，甚至导致系统宕机，那么就得考虑是sql server中那些组件使用了那么多的内存，是过程缓存使用的，还是buffer cache使用的，还是sql server有bug，及时打最新的补丁。

当然除了上面cpu、磁盘、内存外，也有可能是锁的问题，由于sql语句之间相互阻塞，还有死锁，会导致很多语句在等待获取资源，也有可能是存储过程编写的问题，导致事务没有提交。

 

3、语句怎么写，那就怎么建索引。比如where子句中的字段，排在前面，而用于关联的字段放后面，select子句中的字段，可以放到include中，建立覆盖索引。

一般表中索引不可以太多，太多的话会对dml操作产生太多的开销，导致性能问题。另外，可以通过动态性能视图查看，已经建立的索引的使用情况，包括seek次数、scan次数、lookup次数，如果发现这些次数为0的，那么说明这些索引没有被用到，可以删除，这样也能减小dml操作的压力。 

另外还可以查看索引的碎片，考虑是否重建或重新组织索引，提高查询效率。

 

 

后面针对DBA的3个问题中：

 

1、如果建表时没有指定主键，那么这个表就是堆表，就是当有新的记录要添加时，往上堆就行，没有什么顺序。

如果在建立表时，指定了主键，那么会自动建立一个聚集索引，当然不指定主键，在建表之后，也可以建议聚集索引。聚集索引会把数据按照索引指定的字段排序存放在硬盘上，构成一个B树，叶子页上存放数据记录，叶子节点之间通过指针串联起来，数据中间层都是存放的索引记录，上层通过指针指向下层节点。

非聚集索引其实和集聚索引很相似，只是叶子页上存储的是索引字段值和行记录的标识，如果这个表有1000条记录，那么叶子节点上也会1000条索引记录，记录条数是一样的。

 

2、这个上面已经提到了基本的思路，但是还须多实践。

 

3、最大的业务表数据接近1亿条，共有3到4个索引，1个聚集索引，其他为非聚集索引，其中建立一个字段最多的非聚集索引大概20多分钟，建立索引时这个表中一直有数据在插入，而且cpu使用率维持在高位，磁盘的等待队列非常高，建立索引需要的时间也和系统繁忙程度有关。