[IT认证]20113学习笔记.doc

2011-07-21数据仓库 典型的数据仓库因该是一个主题数据库、支持用户从巨大的运营数据存储中发现信息，支持对业务趋势进行跟踪和响应，实现业务的预测和计划。更为广泛接收的数据仓库定义是数据仓库之父提起的：数据仓库是一个面向主题的、集成的、不同时间的、稳定的数据集合，以便支持决策管理。Transact-SQL编程规范1 概述 1.1 基本原则 ：以大小写敏感编写SQL语句；尽量使用Unicode数据类型；优先使用连接代替子查询或嵌套查询；尽量使用参数化SQL查询代替语句拼接SQL查询年；禁止使用拼音+英语的方式来命名SQL对象和变量；尽量使用存储过程代替SQL语句。1.2基本规范：建议采用Pascal样式或Camel掩饰命名数据库对象；大写T-SQL语言的所有关键字、谓词和系统函数。2 命名规范 一般情况下，采用Pascal样式或Camel样式命名数据库对象，使在开发基于数据库应用程序的时候通过ORM工具声称的数据访问代码不需要调整就符合程序开发语言命名规范。另外，关系型数据库同XML结合得越来越紧密，规范的命名越来越重要。1.3 对象命名 数据库：第一种方式，采用Pascal样式命名。命名格式为项目英文名称；第二种方式，采用Pascal样式命名，命名格式为项目英文名称+db数据库文件 数据文件：数据库名称+_Data.mdf 日志文件:数据库名称+_Log.ldf 数据仓库：采用Pascal样式命名，命名格式为项目英文名称+DW数据架构：除SQL Server系统定义的数据架构外，新建架构采用Pascal样式命名，命名格式为架构名，对数据库对象Table，View，Procedure，Function等使用数据架构进行归类数据表：采用Pascal样式命名，命名格式为表名。表名以英文单数命名，主要是参考SQL Server 2005事例数据库，个人理解不采用附属是为了更好使用CRM工具生成复合编程规范的代码。数据试图、存储过程同理；数据列：列名称采用英文单词缩写，英文单词只来自于具体业务定义，尽量表达清楚含义。SQL备份SQL数据库恢复模型 1 完全恢复模型：备份时要备份数据库的数据文件和日志文件；还原时使用数据库的备份的数据文件副本和全部日志文件信息来恢复数据库；能还原全部数据，并可以将数据库恢复到人已制定的时刻；为保证实现即时点恢复，对数据库的所有操作都将完整地记录日志，这样，日志占用空间较大，对性能有所影响。2 大容量日志记录恢复模型备份时要备份数据库的数据文件和日志文件；还原时使用数据库的备份数据文件副本和全部日志信息来恢复数据库；日志中不记录操作细节，而只记录操作的最终结果，因此占用日志空间小；只支持将数据库还原到事务日志备份的时刻，而不支持即时点恢复，因此可能产生数据丢失。3 简单恢复模型 备份时至备份数据文件，还原时也用备份的数据文件恢复数据库；只能将数据恢复到数据文件备份的时刻，可能产生较多的数据丢失；不适合生产系统和大容量操作环境下使用。备份设备 物理设备：disk(支持本地磁盘或者网络备份) Tape（支持磁带机备份） name pipe (支持第三方备份软件)逻辑设备 永久备份文件（可以重复使用，应该在备份前创建）临时备份文件（用于一次性备份，在备份时创建）3 使用多个备份文件SQL Server 2005 备份增强对于备份来说最值得注意的增强就是检验。如果用户用With ChecksuM 选项来执行T-SQL的Backup命令，将会发生两件事：由于每个数据库页面都是从磁盘上的数据文件中读的，服务器将检验页面头的校验和值（如果它存在）。如果检验出故障，服务器将报出一个错误并停止备份；服务器对于被分钟所有页面上的比特位计算出一个检验和值，并将其写入备份介质。被称为备份校验和。这两点都有重要的目的。通过在服务器将页面写到备份之前检查他们的校验和，可以防止服务器将坏页面写到备份中。其次，通过对整个备份流写入一个校验和值，服务器可以通过Restore来检验备份介质本身是否在备份写入被提交到目标介质之后有所损坏。确定在做Backup时没有写入新的校验和值是很重要的。因此在数据库页面不包含一个校验和值，那么在它被写入备份介质之前，SQL不会计算一个新的校验和值，但该页面仍将作为计算备份校验和的一部分。如果在使用With CheckSUM时，确实由于一个损坏页面而遇到错误。用户可以选择用With Continue_After_Error选项来忽略任何错误。这样做是为了解决在备份前出现的校验和值问题，在紧急情况下，可以保证数据库的其余部分被备份。另一个改进是全文数据。之前要备份全文数据，用户需要手动备份与该数据库相关的全文目录文件。现在如果用户数据库有全文数据，SQL Server会自动将全文目录信息包含在该数据库相关的备份信息中，用户甚至可以通过T-SQL Backup命令还单独备份全文目录。还原增强 如果备份介质包含一个备份校验和，那么默认服务器就会检验他。服务器也会检验备份中任何页面的校验和；数据库备份与还原备份是数据的副本，用于在系统发生故障后还原和恢复数据。备份是您能够在发生故障后还原数据，包括：媒体故障；用户错误如误删了某个表或表中部分数据；硬件故障 如磁盘驱动器损坏或服务器报废；自然灾害数据库备份对于例行的工作也很有用例如，将数据库从一台服务器复制到另一台服务器、设置数据库镜像、政府机构文件归档和灾难恢复通过备份一台计算机上的数据库，再将该数据库还原到另一台计算机上，可以快速容易地生成数据库的副本。设计良好的备份和还原策略可尽量提高数据的可用性及尽量减少数据丢失，并考虑到特定的业务要求。备份和还原策略包含备份部分和还原部分。备份策略定义备份的类型和频率、他们所需硬件的特性和速度、测试备份的方法以及存储备份媒体的位置和方法还原策略为定义负责执行还原的人员以及满足数据库可用性和尽量减少数据丢失目标的方法。简单恢复模式：此模式简略地记录大多数事务，所记录的信息想只是为了确保在系统崩溃或还原数据备份之后数据库的一致性。由于旧的事务已提交，已不再不要其日志，因而日志将被截断。阶段日志将删除备份和还原事务日志。没有日志备份，数据库只可恢复到最近的数据备份时间。该模式不支持还原单个数据页。简单恢复模式并不适合生产系统，因为对生辰系统而言，丢失最新的更改是无法接受的，建议使用完整恢复模式。此模式完整地记录了所有的事务，并保留所有的事务日志记录，直到将他们备份。大容量日志恢复模式此模式简略地记录大多数大容量操作，完整地记录其他事务。大容量日志恢复提高大容量操作的性能，常用作完整恢复模式的补充。优缺点 简单：优点 允许执行高性能大容量复制操作。回收日志空间以使空间要求较小。数据丢失情况 必须重做最新数据库或差异备份后所做的修改。 可以恢复到任何备份的结尾，随后必须重做修改。完整：有点 数据文件丢失或损坏不会导致丢失工作，可以恢复到任意时间点。 数据丢失 正常情况下没有。如果日志损坏，则必须重做最新日志备份后所做的修改。 大容量日志：有点 允许执行高性能大容量复制操作。大容量操作使用的最小日志空间。 数据丢失 如果日志损坏或自最新日志备份后执行了大容量操作，则必须重做自上次备份后所做的更改。数据库备份包括完整备份和完整差异备份完整备份包含数据库中的所有诗句，并且可以用作完整差异备份所基于的“基准备份”。完整差异备份仅记录自前一完整备份后发生更改的数据扩展盘区数。与完整备份相比，差异备份较小且速度较快，便于进行较频繁的备份，同时降低丢失数据的风险。文件和文件组完整备份仅适用于包含多个文件组的数据库。在简单模式下，仅适用于包含只读文件组的数据库，完整文件备份一个或多个完整的文件，相当于文正备份。优点是：文件或文件组备份能够更快地从隔离的媒体故障中恢复可以迅速还原损坏的文件；可以同事穿件文件和事务日志备份；文件备份增加了计划和媒体处理的灵活性，增加了文件或文件组备份的灵活性对于包含具有不同更新特征的数据的大型数据库也很有用。文件备份也有雀帝年主要是管理太复杂：如果某个损坏的文件未备份，那么媒体故障可能会导致无法恢复整个数据库；必须维护完整的文件备份，包括完整恢复模式的文件备份和日志备份。维护和跟踪这些完整备份是一种耗时的任务，所需空间会超过完整数据库备份的所需空间；若要以增加管理复杂性为代价来减少恢复时间，请考虑使用文件差异备份。执行Backup Database 语句来创建完整备份，同时需要指定：要备份的数据库的名称写入完整备份的备份设备还可以制定：Init子句，=通过它可以改写备份媒体，并在备份媒体上将该备份作为第一个文件写入；SKIP和INIT子句，用于重写备份媒体，即使备份媒体中的备份未过期，或其名称与备份媒体中的名称不匹配也重写。2011-7-22数据库还原方案是从一个或多个备份中还原数据并在还原最后一个备份后恢复数据库的过程。使用还原方案可以还原下列某个级别的数据：数据库级别 还原和恢复整个数据库，并且数据库在还原和恢复操作期间处于离线状态；数据文件级别 还原和恢复一个数据文件或一组文件。在文件还原过程中，包含相应文件的文件组在还原过程中自动变为离线状态。访问离线文件组的任何尝试都会导致错误。数据页级别 可以对任何数据库进行页面还原，而不管文件组数为多少。还原完整备份是指备份完成时数据库中包含的所有文件重新创建数据库。通常将数据库恢复到故障点步骤为：备份活动事务日志，称为日志尾部。此操作将创建尾日志备份。如果活动事务日志不可用，则该日志部分的所有事务都将丢失；荒原最新的完整备份但不恢复数据库（With Norecovery）；如果存在差异备份，则还原最新的差异备份，而不恢复数据库；从还原备份后创建的第一个事务日志备份开始，使用NORECOVERY依次还原日志；恢复数据库（Restore Database DBNAME With Recovery ）此步骤也可以与还原上一次日志备份结合使用；数据库完整性还原通常可以恢复日志被分钟的某一时间点或标记的事务。但是在大容量日志恢复模式下，如果日志备份包含大容量更改，则不能进行时间点恢复。在完整恢复模式或大容量日志恢复模式下，必须先备份活动事务日志，（尾部日志）然后才能还原数据库。尾日志备份是使数据库处于还原状态的一种日志备份。通常会在失败之后进行尾日志备份来备份日志尾部，以防丢失工作。SQL Server 的代理包括三个基本元素:操作员:负责维护SQL Server服务器运行的人员 作业:用户可以使用做业务来定义将要执行一次或多次的管理任务,并监督该任务的执行/完成情况 警报:警报预定义了当某些SQL Server时间发生时服务器应做出的反映.SQL Server代理是一个任务规划器和警报管理器,在实际应用时,可以将那些周期性的工作定义成一个任务,在SQL Server代理的帮助下自动执行;在自动执行时,若出现故障,则SQL Server代理自动通知操作员,操作员获得通知后及时排除故障.如此在任务/操作员/警报三者之间既相互独立又相互联系,相互补充,构成了自动完成某些任务的有机整体.2011-7-25SQL 优化 没有索引或者没有用到索引这是查询慢最常见的问题，是程序设计的缺陷。我们把这种真跟内容本身就是一种按照一定规则排列的目录称为“聚集索引”，需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。动作描述使用聚集索引使用费聚集索引列经常被分组排序应应返回某范围内的数据应不应一个或极少不同值不应不应小数目的不同值应不应大数目的不同值不应应频繁更新的列不应应外键列应应主键列应应频繁修改索引的列不应应事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。聚类索引只需要把要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中每一项数据对应的号码，然后再根据页码查到具体内容。结合实际该索引使用的误区。1、 主键就是聚集索引 这种想法是极端错误的，是对聚集索引的一种浪费。虽然 SQL Server默认实在逐渐上建立聚集索引的。通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1.如果我们将这个列设为主键，SQL Server会将此列默认为聚集索引。这样做的好处是可以让你的数据在数据库中按照ID进行物理排序，但这样做意义不大。日期这个字段来限制表扫描，提高查询速度。2、 只要建立索引就能显著提高查询速度 我们可以看到这个有着1000万数据的表中日期字段有5003个不同的记录。在此字段上建立聚集索引再合适不过了、在事实中，我们每天会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求：既不能绝大多数都相同，又不能只有极少数相同的规则。3、 把所有需要提高查询速度的字段都加进聚集索引，以提高速度。 如果仅用复合索引的起始列作为查询条件和同事通道复合聚集索引的全部列的查询速度是几乎一样的，而如果禁用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。无论是否经常使用聚集索引的其他列，氮气前导列一定是使用频繁的列。经验总结注意：用聚集索引比用不是聚集索引的主键速度快；用狙击索引比用一般的主键做Order By时速度快，特别是小数据量的情况下；使用聚集索引内的时间段，搜索时间会按数据占整个数据表的百分比成正比例减少，而无论聚合索引使用了多少个；日期列不会因为有分秒的输入而减慢速度。普通SQL索引全文索引存储时受定义他们所在的数据库控制存储在文件系统中，但通过数据库管理每个表允许有若干个普通索引每个表允许有一个全文索引当对作为基础的数据进行插入、更新或删除时，他们自动更新将数据添加到全文索引称为填充，全文索引可通过调度或特定请求来请求，也可以在添加新数据时自动胜不分组在同一个数据库内分组为一个或多个全文目录使用SQL Server企业管理器、向导或Transact-SQL语句创建和除去使用SQL Server企业管理器、向导或存储过程创建、管理和除去。查询子句只把汗被忽略的词：这是因为使用一些简单的词“是”，进行查询的缘故。提出的办法不外乎是把C:program FilesMicrosoft SQL Server MSSQLFtdataSQL Servernoise.chs 清空觉得这种方法是不可取的，这个文件里面的词如：is are be 我 是 这些词都是频率很高的词，而且在查询中的意义不大，为这些词做索引得不偿失，所以全文引擎把这些词称为干扰词不做索引。个人觉得在应用中过滤这些词然后想用户提出有好的提示更好。与一般的索引立即更新不同，全文索引一般是定期维护索引的，所以对于频繁更新的数据不合适，需要做全文索引的对象一般都是论文网页之类。概念：索引实在数据库表或者视图上创建的对象，目的是为了加快对表或视图的查询速度。索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。唯一索引：唯一索引可以确保索引列不包含重复的值，可以用多个列，但是索引可以确保索引列中每个值组合都是唯一的。复合索引：如果在两个或以上的列上创建一个索引，则称为复合索引。系统自建的索引：在使用T-SQL语句创建表的时候使用Primary Key或Unique约束时。自动创建的索引是无法删除的。索引的结构是由：根节点非叶结点非叶结点叶结点聚集索引：表中存储的数据按照索引的顺序存储，检索效率比普通索引高。索引占用硬盘。存储空间小，但对数据新增、修复爱、删除的速度影响比较大。Create Clustered index indexed on emp(empid)非聚集索引：不影响表中的数据存储顺序，检索效率比聚集索引低，索引占用硬盘存储，空间大，对数据新增、修改、删除影响很少。Create NONClustered index indexed on emp(empid)主键：（PK）唯一标识表中的所有行的一个列或一组列。主键不允许为空值。不能存在具有相同的主键值的两个行，因此主键总是唯一标识单个行。表中可以不只一个键唯一标识行，每个键都成为候选键，只有一个候选键可以作为主键，所有其他候选键称为备用键。索引：关系数据库中基于键值提供对表的行中数据的快速访问的数据库对象。索引还可以在表的行上强制唯一性。聚集索引在创建时，将会对表进行复制，对表中的数据进行排序，然后删除原始的表。因此数据库上必须有足够的空闲空间，以容纳数据副本默认情况下，表中的数据在创建索引时排序但是如果因聚集索引已经存在，且正在使用统一名称和列重新创建，而数据已经排序，则会重建索引，而不是从头创建该索引，以自动跳过排序操作重建操作会检查行是否在生成索引时进行了排序如果有任何行排序不正确，机会取消操作，不创建索引只有当表包含聚集索引时，表内的数据行才按照排序次序存储如果表没有聚集索引，则其数据行按堆集方式存储如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针，对于有聚集索引的表，行定位器是聚集索引键。只有在表上创建了聚集索引时，表内的行才按特定的顺序存储。这些行就基于聚集索引键按顺序存储。如果一个表只有一个非聚集索引，它的数据行将按无需的堆集方式存储。索引可以是不唯一的，这样多个行共享同一个键值。填充因子是SQL Server索引的一个属性，他控制索引在创建时的填充密度。默认的填充因子通常能够提供较好的性能，但在某些情况下更改填充因子可能会有益。如果打算对表执行许多更新和插入，则可在创建索引时使用小填充因子，以便为后面的键留出更多的空间。如果是不会更改的只读表，则可以创建索引时使用大填充因子，以减少索引的物理大小，这样降低SQL Server浏览索引时的磁盘读取次数。只有在创建索引时才能应用填充因子。随着键的插入和删除，索引最终将稳定在某个密度上。索引不经可以提高选择行的检索速度，通常还可以提高更新和删除的速度。这是因为SQL Server在更新或删除行时必须先找到该行。使用索引定位行提高了效率，通常这样可以弥补更新索引所需的额外开销。2 I/O吞吐量小，形成了瓶颈效应。3 没有创建计算列导致查询不优化。计算列是物理上并不存储在表中的虚拟列。计算列由同一表中的其他列通过表达式计算得到的。表达式可以是非计算列的列名、常量、函数、变量，也可以使用一个或多个运算符连接的上述元素的任一组合。表达式不能为子查询。计算列可用于选择列表、Where子句、Order by 子句或任何其他可使用常规表达式的位置。但下列情况除外：计算列不能用作Default或Foreign Key约束定义，也不能与Not Null约束定义一起使用。但是，如果计算列由具有确定性的表达式定义，并且索引列中允许计算结果的数据类型，则可将该列用作索引中的键列或Primary Key或Unique约束的一部分。计算列不能作为Insert或Update语句的目标。表中计算所使用的列值因行而异，因此每行的计算列值可能不同。锁或者死锁 这是查询慢最常见的问题，是程序设计的缺陷。死锁是指在某组资源中，两个或两个以上的线程在执行过程中，在争夺某一资源时而造成互相等待的现象，若无外力的作用下，他们都将无法推进下去，此时就可能产生死锁。这些永远在相互等待的进程称为死锁线程。简单的说，进程A等待B释放它的资源，B又等待A释放他的资源，这样互相等待就形成死锁。如在数据库中，如果需要对一条数据进行修改，首先数据库管理系统会在上面加锁，以保证在同一时间只有一个事务能进行修改操作。事务在提交或回滚之前不能释放持有的锁。因为事务需要对方控制的锁才能继续操作，所以他们不能提交或回滚，这样数据库就会发生死锁了。所以我们在数据库设计的时候，虽然不能完全避免死锁，但可以使死锁的数量尽量减少增加事务的吞吐量并减少系统开销，因为只有很少的事务，所以就得遵循下面的原则：按同一顺序访问对象：如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低在写SQL语句或存储过程的时候，就需要按照顺序在两个并发事务中先获得表A上的锁，然后获得表B上的锁，当第一个事务完成职权案，另一个事务被阻塞在表A上第一个事务提交或回滚后，第二个事务继续进行，而不能在语句里面写先获得表B上的锁在获得表A上的锁避免事务中的用户交互：避免编写包含用户交互的事务，因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度，例如大幅应用程序请求参数的提示这样将降低系统的吞吐量，因为事务持有的任何锁只有提交或回滚时才会释放即使不出现死锁的情况，访问同一资源的其他事务也会被阻塞，等待食物完成保持事务简短并在一个批处理中：在同一数据库中并发执行多个需要长时间运行的事务时通常会发生死锁事务运行时间越长，其持有排它锁或更新所的时间也就越长，从而堵塞了其他活动并可能导致死锁保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并