SQL Server查询优化策略与性能提升技巧全解析

如何解决SQLServer数据库查询速度慢

3.查询未优化，因为计算列尚未创建。
4.内存不足5.网速慢6.查询的数据量太大（可以使用多次查询等方法来减少数据量）7.锁或者死锁（也是速度慢最常见的问题是）查询，这是程序设计缺陷）8.sp_lock、sp_who、活跃用户视图，原因是读写竞争资源。
9.返回不需要的行和列。
10、查询语句不好，没有优化。
您可以通过以下方法优化查询：1.将数据、日志和索引放置在不同的I/O设备上。
提高阅读速度。
过去，Tempdb可以放在RAID0上，但SQL2000不再支持这一点。
数据量（大小）越大，提高I/O就越重要。
2.对表进行垂直和水平分区，减少表大小（sp_spaceuse）3.升级硬件4.创建索引，优化索引，根据查询条件优化访问方式和结果限制集合中的数据量。
注意填充因子一定要合适（最好使用默认值0）。
索引应该尽可能小。
最好使用字节数较少的列来创建索引（请参阅索引创建）。
不要为性别字段中值数量有限的字段创建单个索引。
5.提高网络速度。
6.扩展服务器内存Windows2000和SQLserver2000可以支持4-8G内存。
配置虚拟内存：设置计算机上的虚拟内存大小。
必须根据并发运行的服务进行配置。
运行MicrosoftSQLServer2000时，请考虑将虚拟内存大小设置为计算机中安装的物理内存的1.5倍。
如果您另外安装了全文检索功能并计划运行Microsoft搜索服务来执行全文索引和查询，请考虑将虚拟内存大小配置为至少是计算机中安装的物理内存的3倍。
将SQLServermaxservermemory服务器配置选项配置为物理内存的1.5倍（虚拟内存大小设置的一半）。
7.增加服务器CPU的数量，但您必须了解并行处理和串行处理需要更多的资源（例如内存）。
MsSQL自动评估是否使用并行处理或顺序处理。
单个任务被划分为多个可以在处理器上运行的任务。
例如，延迟查询的排序、连接、扫描和GROUPBY子句同时执行。
SQLSERVER根据系统负载确定最佳并行级别，这需要大量的CPU，最适合并行处理。
但是，更新操作UPDATE、INSERT和DELETE尚无法并行处理。
8、如果你喜欢查询，只用索引不行，但是全文索引很耗空间。
Like''a%''使用类似''%a''的索引，并且在执行类似''%a%''的查询时，查询的时间字段值与索引的总长度成正比，因此不能使用CHAR类型，但VARCHAR会为值很长的字段创建全文索引。
9、DBServer和ApplicationServer分离；分离OLTP和OLAP。
10.可以使用分布式分区视图来实现数据库服务器联合。
联合是一组单独管理但一起工作以分担系统处理负载的服务器。
这种对数据进行分区的机制可以创建数据库服务器复合体，可以扩展一组服务器来支持大型、多层网站的处理需求。
有关更多信息，请参阅设计联合数据库服务器。
（参见SQL帮助文件“分区视图”）A.在实现SplitView之前，必须对表进行水平分区。
B.创建成员表后，在每个成员服务器上定义一个同名的分布式分区视图。
这样，引用分布式分区视图名称的查询就可以在任何成员服务器上运行。
系统运行时就好像每台成员服务器上都有原始表的副本，但实际上每台服务器上只有一张成员表和一个分布式分区视图。
数据的位置对于应用程序是透明的。
11.重建索引DBCCREINDEX、DBCCINDEXDEFRAG，收缩数据并设置DBCCSHRINKDB、DBCCSHRINKFILE日志。
对于大型数据库，不要将数据库设置为自动增长，因为这会降低服务器性能。
其中非常强调如何编写T-SQL。
共同点如下：首先，DBMS处理查询计划的过程如下：1.对查询语句进行词法和语法检查2.将语句提交给DBMS。
查询优化器3.进行优化器代数优化和访问路径优化4.由预编译模块生成查询计划5.然后在适当的时候提交给系统处理执行6.最后将执行结果返回给用户。
其次看一下SQLSERVER的数据存储结构：一页的大小为8K（8060）字节，8页为一个分区，按照B树存储。
12.提交和回滚的区别：回滚一切。
提交：提交当前事务。
不需要用动态SQL写东西。
如果你想写，请写出来，如：begintranexec@s)committrans或将动态SQL写为函数或存储过程。
13、在查询select语句中使用WHERE子句来限制返回的行数，以避免表扫描。
如果返回不必要的数据，就会浪费服务器的I/O资源，增加网络负担，降低性能。
如果表很大，在表扫描时锁定表并限制其他连接访问该表将会产生严重的后果。
14、SQL注释语句对性能没有影响。
15.尽量不要使用游标，它占用大量资源。
如果需要逐行执行，请尝试使用非游标技术，例如：在客户端循环、临时表、使用表变量、子查询、case语句等。
游标可以根据它们支持的获取选项进行分类：只能按从第一行到最后一行的顺序获取只进行。
FETCHNEXT是唯一允许的获取操作，并且是默认操作。
可滚动性可以随机显示光标中任意位置的任何行。
游标技术在SQL2000下已经变得非常强大，并且旨在支持循环。
四个并发选项是READ_ONLY：不允许游标定位更新，并且不会在构成结果集的行上放置锁。
乐观的价值观：乐观并发控制是事务控制理论的标准部分。
乐观并发控制用于第二个用户在打开游标和更新行之间更新该行的可能性很小的情况。
当使用此选项打开游标时，没有锁控制其中的行，这有助于最大限度地提高其处理能力。
如果用户尝试修改行，则会将该行的当前值与上次获取该行时获得的值进行比较。
如果值发生更改，服务器就会知道其他人更新了该行并返回错误。
如果值相同，则服务器进行修改。
选择此并发选项OPTIMISTICWITHROWVERSIONING：此乐观并发控制选项基于行版本控制。
要使用行版本控制，表必须具有一些版本控制标识符，其使用服务器可以确定该行自读入游标以来是否已更改。
在SQLServer中，此功能由时间戳数据类型提供，它是表示数据库中更改的相对顺序的二进制数。
每个数据库都有一个全局当前时间戳值：@@DBTS。
每次包含时间戳列的行以任何方式更改时，SQLServer首先将当前的@@DBTS值存储在时间戳列中，然后递增@@DBTS值。
如果表有时间戳列，则时间戳将记录在行级别。
然后，服务器可以将该行的当前时间戳值与上次提取该行时存储的时间戳值进行比较，以确定该行是否已更新。
服务器不需要比较所有列的值，只需比较时间戳列。
如果应用程序需要对没有时间戳列的表进行基于行版本的乐观并发控制，则游标默认采用基于数字的乐观并发控制。
ScrollLock该选项实现悲观并发控制。
在悲观并发控制中，应用程序将在将数据库行读入游标结果集时尝试锁定数据库行。
使用服务器游标时，在将行读入游标时会对该行放置更新锁。
如果在事务内打开游标，则事务更新锁将一直保持，直到事务完成或在提取下一行时释放游标锁。
被移除。
如果游标在事务外打开，则在提取下一行时释放锁。
因此，每当用户需要完全悲观并发控制时，就必须在事务内打开游标。
更新锁可防止任何其他任务获取更新锁或排他锁，从而防止其他任务更新该行。
然而，更新锁不会阻塞共享锁，因此它不会阻止其他任务读取该行，除非其他任务也在请求读取新的行。
滚动锁这些游标可以根据并发选项游标定义的select语句中指定的锁定提示生成滚动锁。
在提取过程中，每行都会获取滚动锁，并一直保持到下一次提取或游标关闭（以先到者为准）。
在下一次提取时，服务器获取新提取中的行的滚动锁，并释放上一次提取中的行的滚动锁。
滚动锁独立于事务锁，并且可以一直保留到提交或回滚操作之后。
如果关闭提交时关闭游标的选项，则COMMIT语句不会关闭任何打开的游标，并且滚动锁将保留到提交之后，以保持提取的数据的隔离。
获得的滚动锁的类型取决于游标并发选项和游标选择语句中的锁定提示。
指定NOLOCK提示的锁定提示将使乐观值游标内使用该提示指定的表变为只读。
16.查询使用profiler进行跟踪，获取一次查询所需的时间并找到SQL问题，使用索引优化器优化索引17.注意UNion和UNionall的区别。
UNIONall不错18.注意使用DISTINCT。
不必要的时候不要像UNION那样使用它，它会减慢查询速度。
查询中不存在重复记录的问题。
19、查询时不要返回不必要的行和列。
20.使用sp_configure''querygovernorcostlimit''或SETQUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。
当评估查询消耗的资源超过限制时，服务器会自动取消查询，甚至在创建查询之前就将其终止。
SETLOCKTIME设置锁定时间21。
使用SELECTTOP100/10%限制返回给用户的行数或使用SETROWCOUNT限制在22上操作的行数。
SQL2000之前，一般不使用以下单词：“null”、“<>”、“!=”、“!>”、“!<”、“not”、“说明不存在”、“not”、“notlike”和“like''%500''”，因为它们不使用索引而只使用表扫描。
不要在WHERE子句中的列名上添加函数，例如转换、子字符串等。
如果必须使用函数，请创建计算列，然后创建索引。
还可以将写法：WHERESUBSTRING(firstname,1,1)=''m''改为WHEREfirstnamelike''m%''（索引扫描）。
确保将函数名称和列名称分开。
而且索引不能设得太多或太大。
NOTIN将多次扫描表，并改为EXISTS、NOTEXISTS、IN和LEFTOUTERJOIN特别是，左连接比IN更快，最慢的操作是NOT。
如果列值有空值，以前索引它不起作用，但现在2000优化器可以处理它。
相同的是ISNULL、“NOT”、“NOTEXISTS”、“NOTIN”都可以优化，但是“<>”仍然无法优化，并且没有使用索引。
23、使用QueryAnalyzer查看SQL语句的查询计划，评估分析是否经过SQL优化。
一般20%的代码占用了80%的资源，我们的优化重点就是这些慢的地方。
24、如果使用IN或OR等时发现查询没有通过索引，请使用显示语句指定索引：SELECT*FROMPersonMember(INDEX=IX_Title)WHEREprocessidIN('Male','Femail')25.预先计算出要查询的结果，放到一个表中，然后查询时选择。
这是SQL7.0之前最重要的方法。
例如，住院费用的计算。
26.MIN()和MAX()可以使用适当的索引。
27.数据库有一个原则，代码越接近数据越好，所以优先选择默认值，其次是规则、触发器和约束（如外键和主键checkUNIQUE...等约束）。
数据类型的最大长度等都是限制），过程。
这样，不仅维护工作量更小，而且编写的程序质量更高，执行速度也更快。
28.如果要向图像列中插入较大的二进制值，请使用存储过程，切勿使用嵌入式插入来插入（不知道Java是否这样做）。
由于应用程序首先将二进制值转换为字符串（其大小的两倍），因此服务器接收字符并将其转换为二进制值。

Mysql数据库优化可以从哪几个方面优化

1.选择最适用的字段属性

MySQL可以很好地支持大量数据的访问，但一般来说，数据库中的表越小，对其执行的查询就越小。
会更快。
因此，在创建表时，为了获得更好的性能，我们可以将表中字段的宽度设置得尽可能小。

2.使用连接（JOIN）代替子查询（Sub-Queries）

MySQL从4.1开始支持SQL子查询。
此技术允许您使用SELECT语句创建单列查询结果，然后将此结果用作另一个查询中的过滤条件。
例如，如果我们要删除基本客户信息表中没有任何订单的客户，我们可以使用子查询先从销售信息表中检索所有下订单的客户的ID，然后将结果传递给主要查询。

3.使用联合（UNION）替代手动创建的临时表

MySQL从4.0版本开始支持联合查询，可以组合两个或多个需要使用临时表的数据。
多个选择查询合并为一个查询。
当客户端的查询会话结束时，临时表将被自动删除，以保证数据库的整洁和高效。
使用union创建查询时，我们只需要使用UNION作为关键字来连接多个select语句。
需要注意的是，所有select语句中的字段数量必须相同。
以下示例演示了使用UNION的查询。

4.事务

虽然我们可以使用子查询、连接（JOIN）和联合（UNION）来创建各种查询，但并不是所有的数据库操作都可以只用一条或几条SQL语句来完成。
更多时候，需要一系列语句来完成某种工作。
但这样的话，当这个语句块中的某条语句运行不正确时，整个语句块的运行就会变得不确定。
想象一下，您想要同时将某些数据插入到两个相关的表中。
可能会发生这种情况：第一个表更新成功后，数据库出现意外情况，导致第二个表中的操作无法完成。
，这样的话，数据就会不完整，甚至数据库中的数据都会被破坏。
为了避免这种情况，您应该使用事务。
它的作用是：语句块中的每条语句要么成功，要么失败。
换句话说，可以保持数据库中数据的一致性和完整性。
事物以BEGIN关键字开始，以COMMIT关键字结束。
如果在此期间SQL操作失败，ROLLBACK命令可以将数据库恢复到BEGIN启动之前的状态。

5.锁定表

虽然事务是维护数据库完整性的一种非常好的方法，但由于其排他性，它有时会影响数据库的性能，尤其是在大型应用系统中。
由于事务执行过程中数据库会被锁定，其他用户请求只能等待事务结束。
如果一个数据库系统只有少数用户使用，事务的影响不会成为大问题；但如果成千上万的用户同时访问一个数据库系统，比如访问一个电子商务网站，就会造成严重的响应延迟。
事实上，在某些情况下我们可以通过锁定表来获得更好的性能。

6.使用外键锁定表的方法可以保持数据的完整性，但不能保证数据的相关性。
这时候我们就可以使用外键了。

7.使用索引

索引是提高数据库性能的常用方法。
它们允许数据库服务器比没有索引更快地检索特定行，特别是当查询语句包含时MAX()、MIN()、ORDERBY等命令，性能提升更加明显。

希望对您有所帮助，谢谢！