如何优化数据库性能

T-SQL脚本优化技巧：1）对于SELECT/UPDATE语句必须显示的定义所有的列,避免使用星号。 2）在执行SELECT/INSERT/UPDATE/DELETE语句时,请考虑执行规划的重用,尽量考虑用SP-EXECUTESQL存储过程。 3）优先使用 SELECT...INTO，然后使用 INSERT...SELECT，以避免大量死锁。 4）如果需要删除所有的数据,用TRUNCATE TABLE 代替DELETE 。5）避免使用DISTINCT 语句。 6）如果你需要有限的记录,通过TOP N代替SET ROWCOUNT来控制排序取值。7）避免使用SARGABLE的语句在WHERE子句,比如: OR, <>, !=, !<, >!, IS NULL, NOT, NOT IN, NOT LIKE 和LIKE,因为这些操作很难利用已知的索引。 8）避免使用NOT IN,可以采用IN,EXISTS NOT EXISTS和LEFT JOIN 加空值判断 --NOT EXISTS, 效率最高 SELECT a.hdr_key FROM hdr_tbl a WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key) --LEFT JOIN SELECT a.hdr_key FROM hdr_tbl a LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL --NOT IN ,效率最低 SELECT hdr_key FROM hdr_tbl WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl) 9）使用EXISTS判断记录是否存在。 --不好的写法: IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx') --正确的写法: IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx') 10）避免在GROUP BY中使用HAVING 语句。 11）GROUP BY的语句要尽量简单,不要进行GROUP BY语句的嵌套,避免在GROUP BY中包含多余的列 考虑在GROUP BY的列,进行ORDER BY排序,特别在多用户的环境下。 12）如果需要在一个包含JOIN的SELECT语句进行GROUP BY,请考虑用子查询代替JOIN. 如果必须使用GROUP BY, GROUP BY 的应该列在同一张表。 13）如果WHERE条件语句有多个AND条件,请确保至少有一个列有索引,如果没有可以建立多列复合INDEX。 14）对于SQL 无法执行自动优化的WHERE条件语句,可以通过HINTS显示的制定INDEX来提高查询的效率。 --可能不好的写法: SELECT * FROM tblTaskProcessesWHERE nextprocess = 1 AND processid IN (8,32,45) --正确的写法: SELECT * FROM tblTaskProcesses (INDEX = IX_ProcessID)WHERE nextprocess = 1 AND processid IN (8,32,45) 15）尽可能避免在WHERE条件语句中使用函数计算。 --不好的写法: WHERE SUBSTRING(firstname,1,1) = 'm' --正确的写法: WHERE firstname like 'm%' 16）在WHERE条件语句中,避免在函数中包列,如果无法避免,请考虑在该列建立INDEX。 --不好的写法: SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21 -- 正确的写法: SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE()) 17）在WHERE条件语句中,避免使用NOT 。 --不好的写法: WHERE NOT column_name > 5 --正确的写法: WHERE column_name <= 5 18）在WHERE条件语句中,推荐使用10位的日期函数。 --正确的写法: SELECT * FROM Northwind.dbo.Orders WHERE OrderDate > '12/31/1997'--不好的写法: SELECT * FROM Northwind.dbo.Orders WHERE OrderDate > '12/31/97' 19）避免使用UNION,而是用UNION ALL 。 20）使用 SQL-92 标准连接句法,为了提高性能，应优先使用连接，然后使用子查询或嵌套查询,表之间的连接使用INNER JOIN,LEFT JOIN 和RIGHT JOIN,不使用CROSS JOIN和多列表方式.。 21）多表关联避免超过5个,可以通过临时表(表变量),简化复杂的关联。存储过程的开发和优化技巧：1）避免使用触发器TRIGGER,考虑用存储过程代替触发器 ——与临时表一样，光标并不是不可使用。对小型数据集使用FAST_FORWARD 光标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用光标执行的速度快。如果开发时 间允许，基于光标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。2）考虑用UDF代替存储过程 ——使用表值 UDF 时要小心，因为在变量（而不是常量）中传递某个参数时，如果在 WHERE 子句中使用该参数，会导致表扫描。还要避免在一个查询中多次使用相同的表值 UDF。但是，表值 UDF 确实具有某些非常方便的动态编译功能。3）对于频繁调用的存储过程,考虑用SP_RECOMPILE重新编译 4）使用输出语句代替返回整个数据集,输出语句的执行效率会更加高效 5）在存储过程的头部使用SET NOCOUNT ON, 通过@@ROWCOUNT来控制,这样可以减少网络流量和避免潜在的问题, 而在结束时设置 SET NOCOUNT OFF. 6）不使用SP_作为存储过程的名称,建议用USP_,这个会影响数据库的执行时间. 7）尽可能使用临时表而使用表变量,表变量可以减少上锁和重新编译的次数并且表变量不使用TEMPDB的空间,而是全部使用内存来处理数据. 8）先在例程中创建临时表，最后再显式删除临时表。将 DDL 与 DML 语句混合使用有助于处理额外的重新编译活动 9）尽可能不要在流程控制语句中使用临时表,比如:IF .. ELSE, WHILE 10）避免在事务中进行赋值和复杂计算, --不好的写法: Create procedure proc_1 As Begin Begin transaction -- step 1 verify the data -- step 2 perform calculations -- step 3 get default variable values (date, user info) -- update/insert records commit end --不好的写法: Create procedure proc_1 As Begin -- step 1 verify the data -- step 2 make calculations -- step 3 get default variable values (date, user info) Begin transaction -- update/insert records commit end 上面的一些优化规则只是一般原则,在某些特殊情况下可能会有所差别,如果需要分析T-SQL的性能,可以通过查询分析器的CTRL+L 显示执行规划进行分析,也可以通过 SET STATISTICS PROFILE ON进行分析.

1、主键就是聚集索引2、只要建立索引就能显著提高查询速度3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度　(四)其他书上没有的索引使用经验总结1、用聚合索引比用不是聚合索引的主键速度快2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个4 、日期列不会因为有分秒的输入而减慢查询速度(五)其他注意事项1. 不要索引常用的小型表2. 不要把社会保障号码(SSN)或身份证号码(ID)选作键3. 不要用用户的键4. 不要索引 memo/notes 字段和不要索引大型文本字段(许多字符)5. 使用系统生成的主键　二、改善SQL语句1、Like语句是否属于SARG取决于所使用的通配符的类型2、or 会引起全表扫描3、非操作符、函数引起的不满足SARG形式的语句4、IN 的作用相当与OR5、尽量少用NOT 6、exists 和 in 的执行效率是一样的7、用函数charindex()和前面加通配符%的LIKE执行效率一样8、union并不绝对比or的执行效率高9、字段提取要按照“需多少、提多少”的原则，避免“select *”10、count(*)不比count(字段)慢11、order by按聚集索引列排序效率最高12、高效的TOP

1、主键就是聚集索引2、只要建立索引就能显著提高查询速度3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度　(四)其他书上没有的索引使用经验总结1、用聚合索引比用不是聚合索引的主键速度快2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个4 、日期列不会因为有分秒的输入而减慢查询速度(五)其他注意事项1. 不要索引常用的小型表2. 不要把社会保障号码(SSN)或身份证号码(ID)选作键3. 不要用用户的键4. 不要索引 memo/notes 字段和不要索引大型文本字段(许多字符)5. 使用系统生成的主键　二、改善SQL语句1、Like语句是否属于SARG取决于所使用的通配符的类型2、or 会引起全表扫描3、非操作符、函数引起的不满足SARG形式的语句4、IN 的作用相当与OR5、尽量少用NOT 6、exists 和 in 的执行效率是一样的7、用函数charindex()和前面加通配符%的LIKE执行效率一样8、union并不绝对比or的执行效率高9、字段提取要按照“需多少、提多少”的原则，避免“select *”10、count(*)不比count(字段)慢11、order by按聚集索引列排序效率最高12、高效的TOP

Java数据库连接（JDBC）API是一系列能够让Java编程人员访问数据库的接口，各个开发商的接口并不完全相同。在使用多年的Oracle公司的JDBC后，我积累了许多技巧，这些技巧能够使我们更好地发挥系统的性能和实现的功能。1、在客户端软件开发中使用Thin驱动程序在开发Java软件方面，Oracle的数据库提供了四种类型的驱动程序，二种用于应用软件、applets、servlets等客户端软件，另外二种用于数据库中的Java存储过程等服务器端软件。在客户机端软件的开发中，我们可以选择OCI驱动程序或Thin驱动程序。OCI驱动程序利用Java本地化接口（JNI），通过Oracle客户端软件与数据库进行通讯。Thin驱动程序是纯Java驱动程序，它直接与数据库进行通讯。为了获得最高的性能，Oracle建议在客户端软件的开发中使用OCI驱动程序，这似乎是正确的。但我建议使用Thin驱动程序，因为通过多次测试发现，在通常情况下，Thin驱动程序的性能都超过了OCI驱动程序。2、关闭自动提交功能，提高系统性能在第一次建立与数据库的连接时，在缺省情况下，连接是在自动提交模式下的。为了获得更好的性能，可以通过调用带布尔值false参数的Connection类的setAutoCommit()方法关闭自动提交功能，如下所示：conn。setAutoCommit(false)；值得注意的是，一旦关闭了自动提交功能，我们就需要通过调用Connection类的commit()和rollback()方法来人工的方式对事务进行管理。3、在动态SQL或有时间限制的命令中使用Statement对象在执行SQL命令时，我们有二种选择：可以使用PreparedStatement对象，也可以使用Statement对象。无论多少次地使用同一个SQL命令，PreparedStatement都只对它解析和编译一次。当使用Statement对象时，每次执行一个SQL命令时，都会对它进行解析和编译。这可能会使你认为，使用PreparedStatement对象比使用Statement对象的速度更快。然而，我进行的测试表明，在客户端软件中，情况并非如此。因此，在有时间限制的SQL操作中，除非成批地处理SQL命令，我们应当考虑使用Statement对象。此外，使用Statement对象也使得编写动态SQL命令更加简单，因为我们可以将字符串连接在一起，建立一个有效的SQL命令。因此，我认为，Statement对象可以使动态SQL命令的创建和执行变得更加简单。4、利用helper函数对动态SQL命令进行格式化在创建使用Statement对象执行的动态SQL命令时，我们需要处理一些格式化方面的问题。例如，如果我们想创建一个将名字O'Reilly插入表中的SQL命令，则必须使用二个相连的“''”号替换O'Reilly中的“'”号。完成这些工作的最好的方法是创建一个完成替换操作的helper方法，然后在连接字符串心服用公式表达一个SQL命令时，使用创建的helper方法。与此类似的是，我们可以让helper方法接受一个Date型的值，然后让它输出基于Oracle的to_date()函数的字符串表达式。5、利用PreparedStatement对象提高数据库的总体效率在使用PreparedStatement对象执行SQL命令时，命令被数据库进行解析和编译，然后被放到命令缓冲区。然后，每当执行同一个PreparedStatement对象时，它就会被再解析一次，但不会被再次编译。在缓冲区中可以发现预编译的命令，并且可以重新使用。在有大量用户的企业级应用软件中，经常会重复执行相同的SQL命令，使用PreparedStatement对象带来的编译次数的减少能够提高数据库的总体性能。如果不是在客户端创建、预备、执行PreparedStatement任务需要的时间长于Statement任务，我会建议在除动态SQL命令之外的所有情况下使用PreparedStatement对象。