sqlserver 概念及优化

SQL Server 中的逻辑读与物理读

• 预读：用估计信息，去硬盘读取数据到缓存。预读100次，也就是估计将要从硬盘中读取了100页数据到缓存。

• 物理读：查询计划生成好以后，如果缓存缺少所需要的数据，让缓存再次去读硬盘。物理读10页，从硬盘中读取10页数据到缓存。

• 逻辑读：从缓存中取出所有数据。逻辑读100次，也就是从缓存里取到100页数据。
  基本上，逻辑读、物理读、预读都等于是扫描了多少个页。
  第二次查询时，如果数据已经再缓存中了，那么只有逻辑读，没有物理读了，因为直接在缓存中可以找到这些数据了。

  当SQL Server执行一个查询语句时，SQL Server会开始第一步，生成查询计划,查询处理器需要读取各个表的定义及表上各个索引的统计信息，当查询计划生成后，真正交给查询执行器执行时，SQL server 才会使用另外一个线程将查询“可能需要的数据”从磁盘读取的缓冲区中（前提是数据不在缓存中），这就是预读。SQL Server通过这种方式来提高查询性能。
  　　查询计划生成好了以后去缓存读取数据，当发现缓存缺少所需要的数据后让缓存再次去读硬盘(物理读)，然后从缓存中取出所有数据(逻辑读)。

查询sql语句详细读取统计
dbcc dropcleanbuffers
set statistics io on
select top 10 * from Person.Address

范式

第一范式（原子性，无重复列）

• 不允许出现重复的行；
• 没有重复的列；
• 每列(或者每个属性)都是不可再分的最小数据单元，即符合原子性；

那么符合第一范式带来的好处：
减少了代码的繁琐（比如Substring等的频繁使用），提高了查询的效率，方便使用关键字搜索，提高了数据库的性能。

第二范式（每列和主键相关）

2nf依赖1nf，所以2nf必须符合1nf，然后第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）
例子：

第三范式（消除相关性）

消除对主键的传递依赖,简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

BCNF(Boyce-Codd normal form)，

在3NF的基础上，表中任何字段对任一候选关键字段的传递函数依赖都不存在。

存储过程

优点

传输快、安全、执行快，复用性好

确定

不易维护，不容易跨数据库

数据库版本管理

使用Source Safe for SQL Server解决数据库版本管理问题

索引

在SQL Server中，索引（聚集索引）存储结构是一样的,都是B树。
对于数据库的检索和查询而言，当没有索引的时候就需要在包含表的数据的所有磁盘（数据页）进行全盘扫描，这样无疑非常消耗时间。
通过对索引的根节点和叶子节点的扫描大大减少了磁盘IO,从而提高了效率。

索引的缺点：

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

聚集索引

特点

• 物理排序与逻辑排序顺序一致。
• 每个表的此种排序只有一种。

非聚集索引

在数据库中的存储分为两种结构，
一是堆；堆的数据是没有排序也就没有结构性可言，我们可以简单理解为没有索引的表数据就是以堆的形式存在的
二是B树，索引都是B树的形式存储，这样的话索引中数据是有序排列的。

• 非聚集索引也是以B树组织的。和聚集索引的区别就在于它的叶层并不包含所有的数据
• 非聚集索引的叶子节点不是数据页，这样非聚集索引的叶子节点只包含键值和定位符（定位符，存在两种可能，如果表中有了聚集索引那么定位符就是个直接指向数据所在行的物理指针，如果有聚集索引，那么就是一个指向索引的聚集键）
• 与聚集索引不同，非聚集索引本身并不会改变数据页的存储模式。
• 非聚集索引包含了索引键列，包含列和书签。书签的值根据所在表是堆还是聚集索引既可以是RID也可以是聚集索引键
  优点
• 因为在SQL Server中一页只是8K，页面空间有限，所以一行所包含的列数越少，它能保存的行就越多。非聚集索引通常不包含表中所有的列，它一般只包含非常少数的列。因此，一个页上将能包含比表行(所有的列)更多行的非聚集索引。
• 非聚集索引的另一个好处是，它有一个独立于数据表的结构，所以可以被放置在不同的文件组，使用不同的I/O路径，这意味着SQL Server可以并行访问索引和表，使查找更快速。
• 与聚集索引不同的时，一个表中可以有多个非聚集索引增加查询覆盖和交叉等等可以提高查询速度。

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

列存储索引

索引的访问方式：
扫描（全表，全索引遍历）
查找（键值查找，书签查找）

SARG写法，房子索引失效
隐式类型转换

复合索引

Sqlserver会记录索引使用情况

脚本：

-- 未被使用的索引
SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,
        i.name
FROM    sys.indexes AS i
        INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id] 
WHERE   i.index_id NOT IN ( SELECT  ddius.index_id
                            FROM    sys.dm_db_index_usage_stats AS ddius
                            WHERE   ddius.[object_id] = i.[object_id]
                                    AND i.index_id = ddius.index_id
                                    AND database_id = DB_ID() )
        AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC;
 
--需要维护但是未被用过的索引
SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] ,
        ddius.[user_updates] AS [user_writes] ,
        SUM(SP.rows) AS [total_rows]
FROM    sys.dm_db_index_usage_stats ddius
        INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]
                                    AND i.[index_id] = ddius.[index_id]
        INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id] AND SP.[index_id] = ddius.[index_id]
        INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
        INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
WHERE   ddius.[database_id] = DB_ID() -- current database only 
        AND OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.[index_id] > 0
GROUP BY su.[name] ,
        o.[name] ,
        i.[name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] ,
        ddius.[user_updates]
HAVING  ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
        su.[name] ,
        o.[name] ,
        i.[name]
 
-- 可能不高效的非聚集索引 (writes > reads) 
SELECT  OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
        i.name AS [Index Name] ,
        i.index_id ,
        user_updates AS [Total Writes] ,
        user_seeks + user_scans + user_lookups AS [Total Reads] ,
        user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM    sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]
                                                       AND i.index_id = ddius.index_id
WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY [Difference] DESC ,
        [Total Writes] DESC ,
        [Total Reads] ASC;
 

--没有用于用户查询的索引
SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] ,
        ddius.[user_updates] AS [user_writes] ,
        ddios.[leaf_insert_count] ,
        ddios.[leaf_delete_count] ,
        ddios.[leaf_update_count] ,
        ddios.[nonleaf_insert_count] ,
        ddios.[nonleaf_delete_count] ,
        ddios.[nonleaf_update_count],
		'drop index '+ i.[name]+' on   '+'[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']'+';'
FROM    sys.dm_db_index_usage_stats ddius
        INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]
                                    AND i.[index_id] = ddius.[index_id]
        INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id]
                                        AND SP.[index_id] = ddius.[index_id]
        INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
        INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
        INNER JOIN sys.[dm_db_index_operational_stats](DB_ID(), NULL, NULL,
                                                       NULL) AS ddios ON ddius.[index_id] = ddios.[index_id]
                                                              AND ddius.[object_id] = ddios.[object_id]
                                                              AND SP.[partition_number] = ddios.[partition_number]
                                                              AND ddius.[database_id] = ddios.[database_id]
WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.[index_id] > 0
        AND ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
        su.[name] ,
        o.[name] ,
        i.[name]

--查看列存储行组物理信息
select object_name(object_id), index_id, row_group_id, delta_store_hobt_id, state_desc, total_rows, trim_reason_desc, transition_to_compressed_state_desc
from sys.dm_db_column_store_row_group_physical_stats
where object_id = object_id('表名') 

-- 索引列选择度
select (select count (distinct 列)*1.0 from 表)/(select count(1) from 表)


-- 索引使用情况
SELECT
ddmid.statement,
ddmid.equality_columns,
ddmid. inequality_columns,
ddmid.included_columns,
ddmigs.user_seeks,
ddmigs.user_scans,
ddmigs.avg_total_user_cost,
ddmigs.avg_user_impact
FROM sys.dm_db_missing_index_details AS ddmid
JOIN sys.dm_db_missing_index_groups AS ddmig
ON ddmid.index_handle = ddmig.index_handle
JOIN sys.dm_db_missing_index_group_stats AS ddmigs
ON ddmig.index_group_handle=ddmigs.group_handle

-- 无效索引
SELECT i.name,ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates
FROM sys.dm_db_index_usage_stats AS ddius
JOIN sys.indexes AS i ON ddius.index_id = i.index_id AND ddius.object_id = i.object_id
ORDER BY ddius.user_seeks

-- 查询是否启用自动优化
SELECT name, desired_state_desc, actual_state_desc, reason_desc
FROM sys.database_automatic_tuning_options;

-- 开启自动优化
ALTER DATABASE current
SET AUTOMATIC_TUNING( FORCE_LAST_GOOD_PLAN = ON );

事务日志的备份和还原

• 事务日志是一个与数据库文件分开的文件。它存储对数据库进行的所有更改，并全部记录插入、更新、删除、提交、回退和数据库模式变化。事务日志还称作前滚日志或重做日志。事务日志是备份和恢复的重要组件，也是使用 SQL Remote 或 [复制代理] 复制数据所必需的。
• SQL Server中使用了WAL（Write-Ahead Logging）技术来保证事务日志的ACID特性。而且大大减少了IO操作
• 当事务遇到Commit时，仅仅是将缓冲区的所有日志页写入磁盘中的日志文件；而直到Lazy Writer或CheckPoint时，才真正将缓冲区的数据页写入磁盘文件。