最近面试当中被问到很多SQL的问题，有些正好记得概念，有些对概念比较模糊，因此我重新复习了一下sql的知识，同时在这里做一些记录权当加深记忆。
1.什么是执行计划么？如何在语句执行的同时，看到执行计划？
A：SQL Server 在执行语句的时候右键附加实际执行计划；MySQL 在语句前加 EXPLAIN 或者Navicat 添加解释
2.索引有几种？有什么区别么？
A：索引分为聚集索引和非聚集索引，非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。（一般来说聚集索引就是主键）
还有组合索引，组合索引很有意思，举个例子：有一个组合索引是index_age_name，select name from people where age = 22，会先查询age这个平衡树，最终查到的节点包含主键id和name，此时可以直接返回name，不需要用id从聚集索引再次查找了。
索引以平衡树的方式存储于磁盘上。
3.有索引和没索引，语句执行的区别么？
A：没有索引，查询语句会逐个比对磁盘存储上的表数据，有聚集索引会从平衡树进行查找，数据量大的情况下比对情况大幅减少。
另外在插入新的数据时，因为需要重构平衡树的结构，所以有索引会让插入操作变慢。
枚举值不建议加索引的原因是，查询优化的好处小于插入时影响的坏处。
4.什么是统计信息么？
A：数据库的统计信息一般包括表的数据行、页，主键的长度，取值范围等，语句的最优执行计划取决于统计信息。
5.什么是临时表，表变量，CTE？有什么区别？
临时表包括：以#开头的局部临时表，以##开头的全局临时表，都存储在tempdb中，局部临时表会在连接断开后删除，全局临时表会在所有的连接不再引用后删除。
表变量是 declare table @{*}，储存在tempdb中，可以有主键不能加索引，在一次语句执行完成后自动清除，适用于小数据的情况。另外表变量不受事务影响。
CTE是 with as 写法的，存储于内存中，实际上运行过程中还是一段sql语句，一般适用于递归。
6.什么是事务？什么是隔离级别？
A：事务是一个工作单元，包含查询／修改数据以及修改数据定义的多个活动的组合，事务具有原子性（要么成功要么失败）、一致性（事务未提交数据无变化）、隔离性（事务互不干扰）、持久性（事务完成后数据不会回滚）
事务的隔离级别控制并发用户的读取和写入的行为，隔离级别主要分为两种类型：悲观并发控制和乐观并发控制
例： SET TRANSACTION ISOLATION LEVEL {隔离级别}
隔离级别分别如下：
READ UNCOMMITTED：最低隔离级别，无需锁，并发性好，但数据容易不一致
READ COMMITTED：会话默认的隔离级别，语句查询会带有共享锁，当事务进行时会等待事务中对该条数据更新后才能读到数据。
REPEATABLE READ：上一级别的提升，会在事务完成提交后进行读取。
SERIALIZABLE:再次提升隔离，牺牲并行效率。
SNAPSHOT：不加锁，直接读取快照，以事务为单位共享快照。
READ COMMITTED SNAPSHOT：语句为单位的快照读取，同一事务中每个语句返回最新快照的结果。
7.什么是逻辑读，什么是物理读，什么是预读么？怎么查看你执行消耗的IO资源？
A：逻辑读，预读，和物理读的单位是页，一页为8k，数据库以平衡树方式存储页。
一次查询的基本过程是：SQL SERVER会走第一步，分别为生成执行计划(占用CPU和内存资源),同步地用估计的数据去磁盘中取得需要取的数据(占用IO资源，这就是预读),注意，两个第一步是并行的，SQL SERVER通过这种方式来提高查询性能.
然后查询计划生成好了以后去缓存读取数据.当发现缓存缺少所需要的数据后让缓存再次去读硬盘（物理读）
最后从缓存中取出所有数据（逻辑读）
8.什么是等待？怎么查看你运行的语句是否在等待？等待反应出的问题是什么？
A:语句查询过程中需要请求锁会进入等待，或语句查询时间过长会进入等待。
SELECT request_session_id,resource_type,resource_database_id,DB_NAME(resource_database_id) AS dbname,resource_associated_entity_id,request_mode,request_status FROM sys.dm_tran_locks
可以查询会话的状态，如果会话需要等待锁资源释放会进入wait状态。
等待说明数据库需要性能优化，更合理地安排锁及事务隔离级别。
9.SQL的锁机制么？
A：MSSQL提供以下六种锁：
共享 (S) 用于不更改或不更新数据的操作（只读操作）
更新 (U) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排它 (X) 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。
意向锁 用于建立锁的层次结构。意向锁的类型为：意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。
架构锁 在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。
大容量更新 (BU) 向表中大容量复制数据并指定了 TABLOCK 提示时使用。
10.什么是TempDB么？什么样的语句会使用TempDB？
A：Tempdb系统数据库是一个全局资源，可供连接到 SQL Server 实例的所有用户使用，并可用于保存下列各项：
显式创建的临时用户对象，例如全局或局部临时表、临时存储过程、表变量或游标。
SQL Server 数据库引擎创建的内部对象，例如，用于存储假脱机或排序的中间结果的工作表。
由使用已提交读（使用行版本控制隔离或快照隔离事务）的数据库中数据修改事务生成的行版本。
由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及 AFTER 触发器等功能而生成的行版本。
11.SQL编译与重编译？
A：当SQLSERVER收到任何一个指令，包括查询（query）、批处理（batch）、存储过程、触发器（trigger）、预编译指令（prepared statement）和动态SQL语句（dynamic SQL Statement）要完成语法解释、语句解释，然后再进行“编译（compile）”，生成能够运行的“执行计划（execution plan）”。
在编译的过程中，SQLSERVER会根据所涉及的对象的架构（schema）、统计信息以及指令的具体内容，估算可能的执行计划，以及他们的成本（cost），最后选择一个SQLSERVER认为成本最低的执行计划来执行。
执行计划生成之后，SQLSERVER通常会把他们缓存在内存里，术语统称他们叫“plan cache”以后同样的语句执行，SQLSERVER就可以使用同样的执行计划，而无须再做一次编译。
这种行为叫“重用（reuse）或者叫重用执行计划”。但是有时候，哪怕是一模一样的语句，SQL下次执行还是要再做一次编译。
这种行为叫“重编译（recompile）”。执行计划的编译和重编译都是要消耗资源的。
如果执行计划能够重用，那么SQLSERVER就不需要再执行上面的过程，加快执行指令的速度，很多语句调优的文章里提到数据库重用执行计划就是指这个意思
网友评论