SQL Server数据库性能优化技巧性能调优

关键词： 调优 并发 数据库 性能

SQL Server数据库性能优化技巧性能调优（精选5篇）

篇1：SQL Server数据库性能优化技巧性能调优

在一个大型的数据库中，性能成为人们关注的焦点之一，如何让数据库高效有效的运行成为广大数据库管理人员和开发人员必须要考虑的问题，性能是一个应用或多个应用在相同的环境下运行时对效率的衡量。性能常用响应时间和工作效率来表示。响应时间是指完成一个任务花费的时间，可以从以下三方面来减少响应时间：

· 减少竞争和等待的次数，尤其是磁盘读写等待次数

· 利用更快的部件

· 减少利用资源所需的时间

绝大多数性能的获得来自于优秀的数据库设计、精确的查询分析和适当的索引。最好性能的获得能够通过确立优秀的数据库设计，在开发时学会使用SQL Server查询优化器来实现。

为了取得更好的数据库性能，我们就需要对数据库进行优化，减少系统资源的竞争，如对数据cache，过程cache，系统资源和CPU的竞争。

在SQL Server中，有如下优化层次：

·应用层——大部分性能的获得来自于对你的SQL应用中查询的优化，这必须是以好的数据库设计为基础的。

·数据库层——应用共享在数据库层中的资源，这些资源包括硬盘，事务日志和数据cache。

·服务器层——在服务器层有许多共享的资源，包括数据高速缓存，过程高速缓存，锁，CPU等。

·设备层——指的是存储数据的磁盘及其控制器，在这一层，你应尤其关注磁盘的I/O。

·网络层——指连接用户和SQL Server的网络。

·硬件层——指可利用的CPU。

·操作系统层——理想地，SQL Server是一台机器的唯一主要应用，它必须和操作系统以及其他sybase软件，如Backup Server或SQL Server Monitor共享处理器、内存以及其他资源。

在大多数情况下面，我们是对应用层进行优化，，因为对应用性能的优化是大家最乐于接受的功能，其结果能被观测及检验，查询的性能是SQL应用的整个性能的一个关键。

应用层上的问题包括以下内容：

·决策支持VS.和在线事务处理(OLTP)需要不同的性能策略

·事务设计能够减少并发，因为长的事务保持占用锁，也就减少了其他用户对相关数据的存取

·关联一致性对数据修改需要join操作

·支持Select操作的索引增加了修改数据的时间

·为了安全而设立的审计限制了性能

在应用层优化的选项包括：

·远程处理或复制处理能够把决策支持从OLTP机器中分离出来

·利用存储过程来减少编译时间和网络的利用

·利用最少量的锁去满足你的应用需要

数据库层的问题包括：

·建立备份和恢复方案

·在设备上分布存储数据

·审计操作影响性能;仅审计你所需的

·日常的维护活动将导致性能的降低和导致用户不能操作数据库表

在数据库层上优化选择包括：

·利用事务日志的阀值来自动转储事务日志防止其超出使用空间

·在数据段中用阀值来监视空间的使用

·利用分区来加速数据的装入

·对象的定位以避免硬盘的竞争

·把重要表和索引放入cache中，保证随时取得

服务器层的问题有：

·应用的类型——服务器是支持OLTP还是DSS，或者两者都支持

·所支持的用户数影响优化决策——随着用户数的增加，对资源的竞争会发生改变

关 键 字：MYSQL

篇2：SQL Server数据库性能优化技巧性能调优

1 数据库设计

要在良好的SQL Server方案中实现最优的性能，最关键的是要有1个很好的数据库设计方案。在实际工作中，许多SQL Server方案往往是由于数据库设计得不好导致性能很差。所以，要实现良好的数据库设计就必须考虑这些问题。

1.1 逻辑库规范化问题

一般来说，逻辑数据库设计会满足规范化的前3级标准：

1.第1规范：没有重复的组或多值的列。

2.第2规范：每个非关键字段必须依赖于主关键字，不能依赖于1个组合式主关键字的某些组成部分。

3.第3规范：1个非关键字段不能依赖于另1个非关键字段。

遵守这些规则的设计会产生较少的列和更多的表，因而也就减少了数据冗余，也减少了用于存储数据的页。但表关系也许需要通过复杂的合并来处理，这样会降低系统的性能。某种程度上的非规范化可以改善系统的性能，非规范化过程可以根据性能方面不同的考虑用多种不同的方法进行，但以下方法经实践验证往往能提高性能。

1.如果规范化设计产生了许多4路或更多路合并关系，就可以考虑在数据库实体(表)中加入重复属性(列)。

2.常用的计算字段(如总计、最大值等)可以考虑存储到数据库实体中。

比如某一个项目的计划管理系统中有计划表，其字段为：项目编号、年初计划、二次计划、调整计划、补列计划…，而计划总数(年初计划+二次计划+调整计划+补列计划)是用户经常需要在查询和报表中用到的，在表的记录量很大时，有必要把计划总数作为1个独立的字段加入到表中。这里可以采用触发器以在客户端保持数据的一致性。

3.重新定义实体以减少外部属性数据或行数据的开支。相应的非规范化类型是：

(1)把1个实体(表)分割成2个表(把所有的属性分成2组)。这样就把频繁被访问的数据同较少被访问的数据分开了。这种方法要求在每个表中复制首要关键字。这样产生的设计有利于并行处理，并将产生列数较少的表。

(2)把1个实体(表)分割成2个表(把所有的行分成2组)。这种方法适用于那些将包含大量数据的实体(表)。在应用中常要保留历史记录，但是历史记录很少用到。因此可以把频繁被访问的数据同较少被访问的历史数据分开。而且如果数据行是作为子集被逻辑工作组(部门、销售分区、地理区域等)访问的，那么这种方法也是很有好处的。

1.2 生成物理数据库

要想正确选择基本物理实现策略，必须懂得数据库访问格式和硬件资源的操作特点，主要是内存和磁盘子系统I/O。这是一个范围广泛的话题，但以下的准则可能会有所帮助。

1.与每个表列相关的数据类型应该反映数据所需的最小存储空间，特别是对于被索引的列更是如此。比如能使用smallint类型就不要用integer类型，这样索引字段可以被更快地读取，而且可以在1个数据页上放置更多的数据行，因而也就减少了I/O操作。

2.把1个表放在某个物理设备上，再通过SQL Server段把它的不分簇索引放在1个不同的物理设备上，这样能提高性能。尤其是系统采用了多个智能型磁盘控制器和数据分离技术的情况下，这样做的好处更加明显。

3.用SQL Server段把一个频繁使用的大表分割开，并放在2个单独的智能型磁盘控制器的数据库设备上，这样也可以提高性能。因为有多个磁头在查找，所以数据分离也能提高性能。

4.用SQL Server段把文本或图像列的数据存放在1个单独的物理设备上可以提高性能。1个专用的智能型的控制器能进一步提高性能。

2 与SQL Server相关的硬件系统

与SQL Server有关的硬件设计包括系统处理器、内存、磁盘子系统和网络，这4个部分基本上构成了硬件平台，Windows NT和SQL Server运行于其上。

2.1 系统处理器(CPU)

根据自己的具体需要确定CPU结构的过程就是估计在硬件平台上占用CPU的工作量的过程。从以往的经验看，CPU配置最少应是1个80586/100处理器。如果只有2～3个用户，这就足够了，但如果打算支持更多的用户和关键应用，推荐采用Pentium Pro或PⅡ级CPU。

2.2 内存(RAM)

为SQL Server方案确定合适的内存设置对于实现良好的性能是至关重要的。SQL Server用内存做过程缓存、数据和索引项缓存、静态服务器开支和设置开支。SQL Server最多能利用2GB虚拟内存，这也是最大的设置值。还有一点必须考虑的是Windows NT和它的所有相关的服务也要占用内存。

Windows NT为每个WIN32应用程序提供了4GB的虚拟地址空间。这个虚拟地址空间由Windows NT虚拟内存管理器(VMM)映射到物理内存上，在某些硬件平台上可以达到4GB。SQL Server应用程序只知道虚拟地址，所以不能直接访问物理内存，这个访问是由VMM控制的。Windows NT允许产生超出可用的物理内存的虚拟地址空间，这样当给SQL Server分配的虚拟内存多于可用的物理内存时，会降低SQL Server的性能。

这些地址空间是专门为SQL Server系统设置的，所以如果在同一硬件平台上还有其它软件(如文件和打印共享，应用程序服务等)在运行，那么应该考虑到它们也占用一部分内存。一般来说硬件平台至少要配置32MB的内存，其中，Windows NT至少要占用16MB。1个简单的法则是，给每一个并发的用户增加100KB的内存。例如，如果有100个并发的用户，则至少需要32MB+100用户*100KB=42MB内存，实际的使用数量还需要根据运行的实际情况调整。可以说，提高内存是提高系统性能的最经济的途径。

2.3 磁盘子系统

设计1个好的磁盘I/O系统是实现良好的SQL Server方案的一个很重要的方面。这里讨论的磁盘子系统至少有1个磁盘控制设备和1个或多个硬盘单元，还有对磁盘设置和文件系统的考虑。智能型SCSI-2磁盘控制器或磁盘组控制器是不错的选择，其特点如下：

(1)控制器高速缓存。

(2)总线主板上有处理器，可以减少对系统CPU的中断。

(3)异步读写支持。

(4)32位RAID支持。

(5)快速SCSI—2驱动。

(6)超前读高速缓存(至少1个磁道)。

3 检索策略

在精心选择了硬件平台，又实现了1个良好的数据库方案，并且具备了用户需求和应用方面的知识后，现在应该设计查询和索引了。有2个方面对于在SQL Server上取得良好的查询和索引性能是十分重要的，第1是根据SQL Server优化器方面的知识生成查询和索引;第2是利用SQL Server的性能特点，加强数据访问操作。

3.1 SQL Server优化器

Microsoft SQL Server数据库内核用1个基于费用的查询优化器自动优化向SQL提交的数据查询操作，

数据操作查询是指支持SQL关键字WHERE或HAVING的查询，如SELECT、DELETE和UPDATE。基于费用的查询优化器根据统计信息产生子句的费用估算。

了解优化器数据处理过程的简单方法是检测SHOWPLAN命令的输出结果。如果用基于字符的工具(例如isql)，可以通过键入SHOW SHOWPLAN ON来得到SHOWPLAN命令的输出。如果使用图形化查询，比如SQL Enterprise Manager中的查询工具或isql/w，可以设定配置选项来提供这一信息。

SQL Server的优化通过3个阶段完成：查询分析、索引选择、合并选择。

1.查询分析

在查询分析阶段，SQL Server优化器查看每一个由正规查询树代表的子句，并判断它是否能被优化。SQL Server一般会尽量优化那些限制扫描的子句。例如，搜索和/或合并子句。但是不是所有合法的SQL语法都可以分成可优化的子句，如含有SQL不等关系符“”的子句。因为“”是1个排斥性的操作符，而不是1个包括性的操作符，所在扫描整个表之前无法确定子句的选择范围会有多大。当1个关系型查询中含有不可优化的子句时，执行计划用表扫描来访问查询的这个部分，对于查询树中可优化的SQL Server子句，则由优化器执行索引选择。

2.索引选择

对于每个可优化的子句，优化器都查看数据库系统表，以确定是否有相关的索引能用于访问数据。只有当索引中的列的1个前缀与查询子句中的列完全匹配时，这个索引才被认为是有用的。因为索引是根据列的顺序构造的，所以要求匹配是精确的匹配。对于分簇索引，原来的数据也是根据索引列顺序排序的。想用索引的次要列访问数据，就像想在电话本中查找所有姓为某个姓氏的条目一样，排序基本上没有什么用，因为你还是得查看每一行以确定它是否符合条件。如果1个子句有可用的索引，那么优化器就会为它确定选择性。

所以在设计过程中，要根据查询设计准则仔细检查所有的查询，以查询的优化特点为基础设计索引。

(1)比较窄的索引具有比较高的效率。对于比较窄的索引来说，每页上能存放较多的索引行，而且索引的级别也较少。所以，缓存中能放置更多的索引页，这样也减少了I/O操作。

(2)SQL Server优化器能分析大量的索引和合并可能性。所以与较少的宽索引相比，较多的窄索引能向优化器提供更多的选择。但是不要保留不必要的索引，因为它们将增加存储和维护的开支。对于复合索引、组合索引或多列索引，SQL Server优化器只保留最重要的列的分布统计信息，这样，索引的第1列应该有很大的选择性。

(3)表上的索引过多会影响UPDATE、INSERT和DELETE的性能，因为所有的索引都必须做相应的调整。另外，所有的分页操作都被记录在日志中，这也会增加I/O操作。

(4)对1个经常被更新的列建立索引，会严重影响性能。

(5)由于存储开支和I/O操作方面的原因，较小的自组索引比较大的索引性能更好一些。但它的缺点是要维护自组的列。

(6)尽量分析出每一个重要查询的使用频度，这样可以找出使用最多的索引，然后可以先对这些索引进行适当的优化。

(7)查询中的WHERE子句中的任何列都很可能是个索引列，因为优化器重点处理这个子句。

(8)对小于1个范围的小型表进行索引是不划算的，因为对于小表来说表扫描往往更快而且费用低。

(9)与“ORDER BY”或“GROUP BY”一起使用的列一般适于做分族索引。如果“ORDER BY”命令中用到的列上有分簇索引，那么就不会再生成1个工作表了，因为行已经排序了。“GROUP BY”命令则一定产生1个工作表。

(10)分簇索引不应该构造在经常变化的列上，因为这会引起整行的移动。在实现大型交易处理系统时，尤其要注意这一点，因为这些系统中数据往往是频繁变化的。

3.合并选择

当索引选择结束，并且所有的子句都有了一个基于它们的访问计划的处理费用时，优化器开始执行合并选择。合并选择被用来找出一个用于合并子句访问计划的有效顺序。为了做到这一点，优化器比较子句的不同排序，然后选出从物理磁盘I/O的角度看处理费用最低的合并计划。因为子句组合的数量会随着查询的复杂度极快地增长，SQL Server查询优化器使用树剪枝技术来尽量减少这些比较所带来的开支。当这个合并选择阶段结束时，SQL Server查询优化器已经生成了1个基于费用的查询执行计划，这个计划充分利用了可用的索引，并以最小的系统开支和良好的执行性能访问原来的数据。

3.2 高效的查询选择

从以上查询优化的3个阶段不难看出，设计出物理I/O和逻辑I/O最少的方案并掌握好处理器时间和I/O时间的平衡，是高效查询设计的主要目标。也就是说，希望设计出这样的查询：充分利用索引、磁盘读写最少、最高效地利用了内存和CPU资源。

以下建议是从SQL Server优化器的优化策略中总结出来的，对于设计高效的查询是很有帮助的。

1.如果有独特的索引，那么带有“=”操作符的WHERE子句性能最好，其次是封闭的区间(范围)，再其次是开放的区间。

2.从数据库访问的角度看，含有不连续连接词(OR和IN)的WHERE子句一般来说性能不会太好。所以，优化器可能会采用R策略，这种策略会生成1个工作表，其中含有每个可能匹配的执行的标识符，优化器把这些行标志符(页号和行号)看做是指向1个表中匹配的行的“动态索引”。优化器只需扫描工作表，取出每一个行标志符，再从数据表中取得相应的行，所以R策略的代价是生成工作表。

3.包含NOT、、或! =的WHERE子句对于优化器的索引选择来说没有什么用处。因为这样的子句是排斥性的，而不是包括性的，所以在扫描整个原来数据表之前无法确定子句的选择性。

4.限制数据转换和串操作，优化器一般不会根据WHERE子句中的表达式和数据转换式生成索引选择。例如：

paycheck * 12>36000 or substring(lastname,1,1)=“L”

如果该表建立了针对paycheck和lastname的索引，就不能利用索引进行优化，可以改写上面的条件表达式为：

paycheck<36000/12 or lastname like “L%”

5.WHERE子句中的本地变量被认为是不被优化器知道和考虑的，例外的情况是定义为储备过程输入参数的变量。

6.如果没有包含合并子句的索引，那么优化器构造1个工作表以存放合并中最小的表中的行。然后再在这个表上构造1个分簇索引以完成一个高效的合并。这种作法的代价是工作表的生成和随后的分族索引的生成，这个过程叫REFORMATTING。

所以应该注意RAM中或磁盘上的数据库tempdb的大小(除了SELECT INTO语句)。另外，如果这些类型的操作是很常见的，那么把tempdb放在RAM中对于提高性能是很有好处的。

4 性能优化的其他考虑

篇3：SQL Server数据库性能优化技巧性能调优

SQL Server作为一种被广泛使用着的数据库管理系统, 它能满足当下数据库系统的各种要求和不同类型的数据库解决方案, 具有着许多显著的优点, 如:易用性、集成性、可伸缩性以及用于决策支持的数据仓库功能等, 在越来越多的商业技术领域中使用。但随着使用的时间增长, 必须要面对的问题是:当数据信息积累到一定的规模时, 必将导致数据库的性能下降, 数据查询的效率变慢, 甚至到无法容忍的地步。定期维护数据库和对系统的性能进行优化就显得尤为的重要。

1 设计优化策略

设计阶段是决定系统性能的关键阶段, 只有设计合理, 才能减少瓶颈问题, 减少CUP利用率, 减少资源争用。

(1) 数据库语言编程优化。在设计阶段, 数据库系统应遵循规范化的要求, 注重语句的书写规范, 避免因语句的大小写、输入错误等影响数据库的查询。避免大量的使用SELECT语句, 通过SELECT语句对数据进行数据查询是程序编写中最常用的一种方法。但是, SELECT语句使用过多, 需要较长的时间从数据库系统中提取数据, 容易造成数据库系统运行缓慢。

(2) 索引优化。根据数据量决定哪些表需要增加索引, 数据量小的可以只有主键。根据使用的频率决定哪些列需要建立索引, 将经常作为连接条件、筛选条件、排序、经常使用在WHERE子句中的列作为索引侯选列;还可以把经常出现的列组合在一起, 组成复合索引, 在创建的时候注意重复率低的列放在前面。一个表中不要有太多的索引;如果表经常被用来做修改操作的或者定义为text、image和bit数据类型的列, 就不建议添加索引了。要注重对索引的维护, 周期性的重新组织或重新生成索引。如果不正确的使用索引, 不但不会提高查询效率, 反而会降低更新速度, 造成严重的问题。

(3) 数据库对象的存储。数据库对象存储也是有一定策略的。一般数据库对象应是均匀地把数据分布在系统磁盘中, 这样访问分散到不同的磁盘, 使用户数据尽可能跨越多个设备, 多个I/O运转, 避免I/O竞争, 克服访问上的瓶颈。同时, 分离系统数据库和应用数据库, 把系统审计表和临时表放在不忙的磁盘上, 把事务日志放在单独的磁盘上, 减少磁盘I/O的开销;把频繁访问的表放在不同的磁盘上;把频繁用的表、频繁做Join操作的表分别放在不同的磁盘上;甚至可以把频繁访问的表的列放在不同的磁盘上, 这样做可以把访问分散到不同的磁盘上, 避免I/O的争用问题。

2 查询优化策略

优化数据库系统查询功能, 可以让用户在短时间内寻找到所需要的数据, 提高效率。在一般情况下, 我们可以考虑以下几点来对查询进行优化:

(1) 不要写SELECT *的语句。当我们想要列出表中所有的信息时, 这是一个简便的方法, 但是数据库在解析的时候会将”*”转换成所有的列名, 这意味着将耗费更多的时间。所以要在查询中选择需要的列。

(2) 应避免在WHERE中进行数据的计算, 若计算的数据过多、过大, 容易造成数据库系统索引失效而进行全表扫描, 进而影响数据库系统运行效率。另外, 应多使用AND连接, 减少长字符串连接或OR连接的使用。

(3) 当采用谓词LIKE作查询时, 如果以”_”或”%”等通配符作为条件的开始就会导致SQL Server无法使用索引而影响查询效率。如:SELECT Student Name FROM Student WHEREStudent Name LIKE“_海”, 在使用的时候尽可能避免这种语句的出现。

(4) 避免使用子查询语句。当一个查询中使用了子查询, 查询速度难免就会受到影响。查询嵌套的层次越多, 查询的效率就越差。

(5) 查询中避免使用”!=”、”not exist”和”not in”等, 这些查询的效率极低, 不但需要花费大量的时间, 而且会耗费相当大的系统资源, 甚至会造成其它进程的阻塞。在实际应用中, 尽可能用其它替代。

(6) IS NULL或IS NOT NULL操作的限量使用。这是因为不能用null作索引, 任何包含null值的列都将不会被包含在索引中。即使索引有多列的情况下, 只要这些列中有一列含有null, 该列就会从索引中排除。这也就是说如果某列存在空值, 即使对该列建索引也不会提高性能。

(7) DELETE语句的优化。在一般的删除操作中我们都使用DELETE语句。对于小数量的使用该语句没有任何问题, 但是一些大的数据表来说, DELETE就会产生一定的影响, 它是一种事物性操作, 会将操作记录到SQL的事务日志中, 这样不但增加时间, 而且由于频繁的写入, 会导致Log文件过大, 过于消耗磁盘空间。在这种情况下, 可以考虑使用TRUNCATE来代替DELETE, 这样既不会写入事务日志, 执行速度较快。

除此之外, 对于SQL Server数据库查询优化, 还可以采用SQL Server数据库本身自带的一些数据库管理工具, 熟练运用这些工具能够提高优化工作的效率。如在SQL Server2008中, SQLServer Profiler和数据库引擎优化顾问是比较常用的两个工具。

3 存储过程的优化策略

存储过程是由一些SQL语句和控制语句组成的被封闭起来的过程, 它驻留在数据库中, 可以被客户应用程序调用, 也可以从另一个过程或触发器调用。使用存储过程的好处在于可以减少网络通信量;提供单点维护;抽象化业务规则;增强安全性;能支持执行计划重复使用等。在环境允许的情况下, 将易于变化的业务规则;需要集中管理和控制的逻辑与运算处理;需要对基本表的数据进行较复杂的逻辑处理才能返回所需的结果数据集等这样的一些操作可以考虑放入存储过程中完成。但在使用的过程中要注意:

(1) 使用SETNOCOUNT ON。在默认情况下, 存储过程将返回过程中每个语句影响的行数。如果不需要在应用程序中使用该信息, 那么就在存储过程中使用SET NOCOUNT ON语句以终止行数的统计。尽管这不是一个大的问题, 但它可以为高流量应用程序的性能产生负面影响。

(2) 尽量使用OUTPUT参数。通过使用OUTPUT参数返回标量数据, 可以略微提高速度并节省少量的处理功率。

(3) 提供返回值。将一组返回值及其含义标准化, 并一致地使用这些状态信息返回给进行调用的应用程序, 这会使得处理调用应用程序中的错误更加容易。

(4) DDL和DML语句混合使用中的问题。在DDL和DML语句混合使用的时候, 将DML语句放在DDL语句之后执行, SQL Server将重新编译存储过程, 这是由于为了给DML创建计划, SQL Server需要考虑由DDL对该对象所作的更改, 强制存储过程多次进行重新编译, 对性能造成负面影响。

在SQL Server数据库被普及的今天, 人们对保障数据库运行的高效性、安全性的需求日益加大, 对其进行合理的设计和优化, 已显得尤为重要。本文中所提到的一些策略, 只是简单阐述了数据库性能优化的一些原则和方法。实际上, 影响数据库系统性能的因素还有很多, 这就需要数据库的设计师和管理员不断的进行探索、改进。

参考文献

[1]马力.SQL Server数据库性能优化研究[J].国际IT传媒品牌, 2013.11 (7) :146-148.

[2]罗云.SQL Server数据库查询语句优化的研究[J].信息通信, 2014, 4.

[3]李红丽.SQL Server数据库的查询优化探析[J[.长春教育学院学报, 2014.4 (7) .

篇4：SQL Server数据库性能优化技巧性能调优

摘要：本文对数据库应用系统的各个部分特别是数据库服务器、SQL语句、存储过程等的性能调整作了大量的分析和试验，提出了一些具体的性能调整方法和措施，并取得较好的应用效果。

关键词：SQL;数据库;性能优化

一、 基于SQLServer2000的数据库性能调整

1.系统规划

数据库服务器是整个数据库应用系统的核心，它的性能高低直接影响整个系统的性能。SQL Server2000数据库的许多方面都可以被优化或调整，以便给予系统更好的性能，诸如硬件、SQL Server配置、数据库设计、SQL语句、SQL索引、复制、备份与恢复及其他。

2.优化SQL语句

SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。在这里就不展开了，在第五章将有专题讨论。

3.存储过程调整

SQL Server存储过程是用Transact-SQL语句PROCEDURE创建的，并可用ALTER PROCEDURE语句进行修改。存储过程定义包含两个主要组成部分：过程名称及其参数的说明，以及过程的主体所有设计优良的Microsoft SQL ServerTM 2000应用程序都应当使用存储过程。不论是否将应用程序的业务逻辑写入存储过程都应如此。

4.高性能备份与恢复

需要确定数据的可用性要求，以便选择适当的备份和还原策略。总体备份策略定义备份的类型和频率以及所需的硬件特性和速度。测试备份和恢复过程。测试有助于确保拥有从各种故障中恢复所需的备份，并且当真正的故障发生时可以快速平稳地执行恢复过程。

5.用户管理

工程设计企业传统的组织结构按专业及职责设置，是面向部门的层次管理结构。这种组织结构管理层次多，各个机构间协调复杂，造成了信息交流和传递困难，设计周期长等问题。

二、优化SQL语句和存储过程

数据库调整中一个很重要的方面就是应用程序的调整，关键在于SQL语句的优化和存储过程的应用。本章结合具体的项目实践，讨论了一些关于SQL语句的优化和存储过程的应用的方法和措施。

1.优化SQL语句

SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

2.存储过程

存储过程(Stored Procedure)是一组编译在单个执行计划中的Transact一SQL语句。Microsoft SQLServerTM2000的存储过程可以通过输入参数接受输入，并能够以下面四种方式之一返回数据：输出参数，既可以返回数据(整型值或字符值等)，也可以返回游标变量(游标是可以逐行检索的结果集)；如果返回代码，始终是整型值；SE比CT语句的结果集，这些语句包含在该存储过程内或该存储过程所调用的任何其它存储过程内；可从存储过程外引用的全局游标。

3.B/S模式下的备份与恢复

B/S模式下的备份与恢复的实现步骤：

（1）编写存储过程

存储过程的编写需要遵循SQL语言语法，在SQLServer企业管理器中打开master数据库，打开存储过程，右键新建存储过程，会出现存储过程属性的SQL编辑器，然后按语法直接编写。下面显示文件备份的编写过程。

（2）JSP语句调用存储过程

为清楚说明JSP语句调用存储过程的实现过程，现将JSP语句按功能分解：

—实现与数据库连接功能

Driver DriverCallablel=(Driver)C1ass.forName(MM_Cmaster_ DRIVER). newInstance();

Connection ConnCallablel =DriverManager. getConnection (MM_Cmaster_ STRING, MM_Cmaster_ USERNAME,MM_Cmaster_PASSWORD);

—实现调用存储过程功能

CallableStatement Caliablel=ConnCallablel.prepareCall(“{?=call dbo.backup_diffrience(?，?)}”);

—实现存储过程中变量传递功能

Object Callablel_data;

Callablel.registerOutParameter(1，Types.LONGVARCHAR);

Callablel.setString(2, Callablel_bname);

Callablel.setString(3, Callablel_dir);

—实现存储过程执行和关闭功能

Callablel.execute();

ConnCallablel.close();

三、结论

文对基于SQL Server2000的数据库性能调整进行了较为全面、系统的研究，希望总结出数据库性能调整的一般性原则和方法，并取得了一些成果。

参考文献:

[1]袁鹏飞:SQLServer数据库应用开发技术人民邮电出版社1998. 5.

[2]赵 敏:基于SQL Server性能调整和测评方法计算机工程2000.5.

篇5：试析MySQL数据库性能的调优

从宏观上来说,调优分为3个部分:硬件、网络、软件,软件再细分为表设计(范式、字段类型、存储引擎)、SQL语句与索引、配置文件参数、操作系统同、体系架构等几大部分,限于篇幅,在文中主要从My SQL数据库的字段类型和存储引擎入手来讲解对性能调优的建议。

1 字段类型的选取

选择字段的一般原则是保小不保大,能用占用字节少的字段就不用大字段。比如,主键,强烈建议用int整形,不用guid,节省空间,空间就是效率,4个字节和32个字节相比,定位一条记录,当然是4字节的数据快了。当涉及几个表做join连接操作时,效果就更加明显了。更小的字段类型占用的内存就少,占用的磁盘空间和磁盘I/O也会更少,而且还会占用更少的带宽。因此,在日常选择字段时必须要遵守这一规则。下面针对不同的数据类型逐个分析。

1.1 数值类型

在My SQL中支持的5个主要整数类型是TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT,它们占用的内存空间分别是1、2、3、4和8字节,同时它们能表示的数据范围也随着空间的不同而不同。在实际使用中,应根据数据的大小范围来选择合适的类型。比如存储手机号字段,如果开发人员考虑到varchar占用空间大,影响查询性能,于是把字段类型由varchar改成了int,结果在录入手机号时发生了溢出,因为无符号的int型数据能表示的最大数是4 294 967295,而手机号是需要11位数字的。在这种情况下,可以考虑把int类型转换为bigint类型,就不会存在溢出的情况了。另外,有不少开发人员在设计表字段时,只要是针对数值类型的全部用int,这不一定合适,就比如用户的年龄,一般来说,年龄大都在1~100岁之间,长度只有3,那么用int就不合适了,可以用tinyint代替。又比如,用户在线状态,0表示离线、1表示在线、2表示离开、3表示忙碌、4表示隐身等,其实类似这样的情况,用int都是没有必要的,浪费空间,采用tinyint完全可以满足需要,int占用的是4字节,而tinyint才占用1个字节。

1.2 字符类型

char和varchar是日常使用最多的字符类型。char(N)用于保存固定长度的字符串,长度最大为255,比指定长度大的值将被截短,而比指定长度小的值将会用空格进行填补。varchar(N)用于保存可变长度的字符串,长度最大为65 535,只存储字符串实际需要的长度,当然,它会增加一个或两个额外字节来存储字符串本身的长度,如果列的最大长度小于或等于255,则使用1字节,否则就使用2字节。同时char和varchar跟字符编码也有密切联系,latin占用1个字节,gbk占用2个字节,utf8占用3个字节。

什么情况下用char类型,什么情况下用varchar类型呢?一般来说,经常变化的值,如家庭住址,由于每个人的地址都不同,有的地址很长有的很短,那么用varchar相对比较合适。而对于固定长度的值,比如uuid函数,是数字和字母组成的36位长度的字符类型,长度是固定不变的,或者是md5加密后的32位长度的字符类型,可以设置为char(36)和char(32),这样相比于varchar,还节省空间,因为varchar还要用1字节存储串的长度。

有的人会问:既然varchar只存储字符串实际的长度,使用varchar(20)或varchar(100)存储’abc’字节都会一样,那干脆在设计表时就把值定义得大一些,为今后的扩展先预留出来。对于这个问题,虽然两者存储的空间是一样的,但二者的性能完全不一样,My SQL需要先在内存中分配固定的空间来保存值,这无形中就浪费了内存,而且对表的排序或使用临时表尤其不好,所以只分配真正需要的那部分空间即可。

1.3 时间类型

在My SQL中支持的5个时间类型是DATE、TIME、DATETIME、TIMESTAMP和YEAR,它们分别占用的内存空间是3、3、8、4和1字节。datetime和timestamp都可以精确到秒,但datetime占用8字节,而timestamp只占用4字节,在日常建表时应优先选择timestamp类型。并且timestamp还具有自动更新时间功能,如图1所示。

此时,ctime字段会自动插入当前的时间,当更新id字段时,ctime的时间也会自动更新,如图2所示。

可能有人看到这里会有疑问,业务需求并不是这样的,不想让系统自动更新为当前时间,应该怎么办呢?只需要更改默认值为空,这样就跟datetime类型是一样了,当插入、更新完毕后,都不会自动更改ctime字段为当前时间了。

2 Inno DB引擎与My ISAM引擎的性能对比

Inno DB和My ISAM作为My SQL数据库中两种最主要、最常用的存储引擎,各有所长。在My SQL5.5之前的版本中,My ISAM是My SQL中默认的存储引擎,而在My SQL5.5之后,My SQL中默认的存储引擎则改为了Inno DB。对于这两种存储引擎的选择,要根据项目应用特点来权衡,而对于复杂的应用系统,也可以根据实际情况来选择多种存储引擎的组合。不过,还是建议尽量不要混合使用多种存储引擎,这样容易带来更复杂的问题。

My ISAM支持全文索引,这是一种基于分词创建的索引,支持一些比较复杂的查询,但不是事务安全的,而且不支持外键。Inno DB是事务型引擎,支持回滚,具有崩溃恢复能力,多版本并发控制、至此ACID事务、支持行级锁定(Inno DB表的行锁不是绝对的,如果执行一个SQL语句没有使用到索引,Inno DB表同样会锁全表)。Inno DB在工作时把数据存到内存中,被用户读写,这样大大增加了性能,当数据全部加载到内存中,这时的性能是最好的,可以减少磁盘I/O使用率。

My ISAM和Inno DB之间的主要区别在于以下几点:(1)My ISAM是非事务安全型的,而Inno DB是事务安全型的,也就是ACID事务支持;(2)My ISAM锁是表级锁,锁开销最小,而Inno DB支持行级锁定,锁管理开销大,支持更好的并发写操作;(3)My ISAM支持全文索引,而Inno DB不支持全文索引,但在5.6版本中已提供支持;(4)My ISAM相对简单,管理方便,因此在效率上要优于Inno DB,小型应用可以考虑使用My ISAM;(5)My ISAM表是保存成文件的形式,在跨平台的数据转移中使用My ISAM存储会省去不少麻烦;(6)Inno DB表比My ISAM表更安全,可以在保证数据不会丢失的情况下,切换非事务表到事务表。

3 结语

Inno DB用于事务处理应用程序,具有众多特性,包括支持ACID事务、行锁等。如果应用中需要执行大量的读写操作,则应该使用Inno DB,这样可以提高多用户并发操作的性能。对于My ISAM引擎,在My SQL5.5版本里Oracle公司支持的已经很少了,以后内存数据库是一种趋势,所以建议优先选择Inno DB引擎。

参考文献

[1]唐汉明,等.My SQL数据库开发、优化与管理维护[M].人民邮电出版社,2008.

[2]张工厂.My SQL技术精粹:架构、高级特性、性能优化与集群实战[M].清华大学出版社,2015.