SQL server基础知识问答

无论是日常工作还是在我们参加面试的时候，都会涉及到数据库相关知识，可能部分人平常都会使用数据库的一些操作，但对于一些理论或原理却知道得不多，下面我就对一些常被问到或基础却重要的点，采用问答的方式做一些归纳：

 

Q：什么是索引？

A：在ORM数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的 SQL 语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。

Q：索引具体采用的哪种数据结构呢？

A：在SQL Server数据库钟，索引的存储是以B+树结构来存储的，又称索引树，其节点类型包含索引节点、叶子节点。索引节点按照层级关系，有时又可以分为根节点和中间节点，其本质是一样的，都只包含下一层节点的入口值和入口指针；叶子节点就不同了，它包含数据，这个数据可能是表中的真实数据行，也可能是索引列值和行书签，也就是聚焦索引和非聚集索引。

注：B+ 树是一种多路平衡查询树，所以它的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。

Q：什么是聚集索引、非聚集索引？两者又有什么区别？

A：聚集索引： 数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，因为一张表中的数据只能有一个物理顺序，所以一张表只能有一个主键/聚集索引。

　  非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

　　区别：

• 聚集索引查询会更快 。因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点一般是主键的值，然后执行回表查询具体数据。回表查询可能多次也可能只查询一次，覆盖索引便只查询一次。

• 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个

• 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续 

Q：在创建联合索引的时候，多个字段之间顺序是如何选择的？

A： 在创建多列索引时，我们根据业务需求，where子句中使用最频繁的一列放在最左边，因为索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则。

Q：什么时候使用聚集索引或非聚集索引？

A：

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

 

Q：事务、锁？

A：事务：保持逻辑数据一致性与可恢复性，必不可少的利器。如果不能都执行完成，那就回到原点，都不完成。

      锁：多用户访问同一数据库资源时，对访问的先后次序权限管理的一种机制，没有他事务或许将会一塌糊涂，不能保证数据的安全正确读写。

　 锁从数据库系统的角度大致可以分为6种：

• 共享锁（S）：还可以叫他读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁的时候，所有的事务都不能对这个资源进行修改，直到数据读取完成，共享锁释放。
• 排它锁（X）：还可以叫他独占锁、写锁。就是如果你对数据资源进行增删改操作时，不允许其它任何事务操作这块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。
• 更新锁（U）：防止出现死锁的锁模式，两个事务对一个数据资源进行先读取在修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排他锁，否则变为共享锁。
• 意向锁：SQL Server需要在层次结构中的底层资源上（如行，列）获取共享锁，排它锁，更新锁。例如表级放置了意向共享锁，就表示事务要对表的页或行上使用共享锁。在表的某一行上上放置意向锁，可以防止其它事务获取其它不兼容的的锁。意向锁可以提高性能，因为数据引擎不需要检测资源的每一列每一行，就能判断是否可以获取到该资源的兼容锁。意向锁包括三种类型：意向共享锁（IS），意向排他锁（IX），意向排他共享锁（SIX）。
• 架构锁：防止修改表结构时，并发访问的锁。
• 大容量更新锁：允许多个线程将大容量数据并发的插入到同一个表中，在加载的同时，不允许其它进程访问该表。

      死锁：是数据库性能的重量级杀手之一，而死锁却是不同事务之间抢占数据资源造成的。相互等待对方释放资源，造成资源读写拥挤堵塞的情况，就被称为死锁现象

 

锁	概念
乐观锁	自己实现，通过版本号
悲观锁	共享锁，多个事务，只能读不能写，加 lock in share mode
排它锁	一个事务，只能写，for update
行锁	作用于数据行
表锁	作于用表

Q：

A：