SQL 性能技巧与规范

SQL 性能技巧与规范 天善问答，由天善讲师团、智囊团长期驻扎，一站式解决你在工作和学习中遇到各类BI技术问题,网址：SQL性能技巧与规范1 SQL性能技巧1.1 AND OR 索引方式有正确使用到索引的 SQL 语句，以垂直的方向使用索引。用 AND 算符时，只要有任一个字段有加上索引，就能受惠于索引的好处，并避免全表扫描未正确使用索引的 SQL 语句，以水平的方向使用索引。用 OR 算符时，必须所有用到的字段都有加上索引，才能有效使用索引、避免全表扫描1.2 合理设计索引例：表RECORD有620000行，试看在不同的索引下，下面几个 SQL的运行情况：1.2.1 在Date建立非聚集索引select count(*) from record where date 19991201 and date 2000 (25秒)select date ,sum(amount) from record group by date(55秒)select count(*) from record where date 19990901 and place in (BJ,SH) (27秒)分析: Date上有大量的重复值，在非聚集索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。1.2.2 在Date建立聚集索引select count(*) from record where date 19991201 and date 2000 （14秒）select date,sum(amount) from record group by date（28秒）select count(*) from record where date 19990901 and place in (BJ,SH)（14秒）分析: 在聚集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。1.2.3 在Place，Date，Amount上的组合索引select count(*) from record where date 19991201 and date 2000 （26秒）select date,sum(amount) from record group by date（27秒）select count(*) from record where date 19990901 and place in (BJ, SH)（19991201 and date 2000(19990901 and place in (BJ,SH)（,=, =）和order by、group by发生的列，可考虑建立聚集索引；.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。1.3 执行计划分析和 TSQL语句生成的磁盘活动量的信息1.3.1 执行计划分析:如果在执行计划中看到如下所示的任何一项,从性能方面来说,下面所示的每一项都是不理想的。Index or table scans(索引或者表扫描)：可能意味着需要更好的或者额外的索引。Bookmark Lookups(书签查找)：考虑修改当前的聚集索引，使用复盖索引，限制SELECT语句中的字段数量。Filter(过滤)：在WHERE从句中移除用到的任何函数，不要在SQL语句中包含视图，可能需要额外的索引。Sort(排序)：数据是否真的需要排序？可否使用索引来避免排序？在客户端排序是否会更加有效率？以上事項避免得越多,查询性能就会越快.总结: 实现同一查询功能的SQL写法可能会有多种，如果判断哪种最优化，如果仅仅是从时间上来测，会受很多外界因素的影响，而我们明白了MSSQL如何去执行，通过IO逻辑读、通过查看图示的查询计划、通过其优化后而执行的SQL语句，才是优化SQL的真正途径。1.3.2 TSQL语句生成的磁盘活动量的信息分析: 表Employee。扫描计数1，逻辑读取1 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。但是决定那条语句是最优的是根据(logical reads) 逻辑读取来判断。逻辑读越低，查询效率越高。1.4 性能优化原则1.4.1 查询的模糊匹配 尽量避免在一个复杂查询里面使用 LIKE %parm1% 红色标识位置的百分号会导致相关列的索引无法使用，最好不要用.解决办法:其实只需要对该脚本略做改进，查询速度便会提高近百倍。改进方法如下： a、修改前台程序把查询条件的供应商名称一栏由原来的文本输入改为下拉列表，用户模糊输入供应商名称时，直接在前台就帮忙定位到具体的供应商，这样在调用后台程序时，这列就可以直接用等于来关联了。 b、直接修改后台根据输入条件，先查出符合条件的供应商，并把相关记录保存在一个临时表里头，然后再用临时表去做复杂关联1.4.2 索引问题法则：不要在建立的索引的数据列上进行下列操作:避免对索引字段进行计算操作避免在索引字段上使用not，!=避免在索引列上使用IS NULL和IS NOT NULL 避免在索引列上出现数据类型转换避免在索引字段上使用函数 避免建立索引的列中使用空值。1.4.3 复杂操作部分UPDATE、SELECT 语句 写得很复杂（经常嵌套多级子查询）可以考虑适当拆成几步，先生成一些临时数据表，再进行关联操作1.4.4 在可以使用UNION ALL的语句里，使用了UNIONUNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话， 务必使用UNION ALL。还有一种情况大家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉重复记录，但由于脚本的特殊性，不可能存在重复记录，这时便应该使用UNION ALL。1.4.5 Update同一个表的修改在一个过程里出现好几十次，如： update table1 set col1=. where col2=.; update table1 set col1=. where col2=. . 像这类脚本其实可以很简单就整合在一个UPDATE语句来完成1.4.6 尽量避免对索引字段进行计算操作任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。如:select * from record where substr(cardno,1,4) = 5378select * from record where amount/30 1000由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的，因此它不得不进行表扫描，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表扫描，因此将SQL重写如下:select * from record where cardno like 5378%select * from record where amount = 2010-10-05 00:00:00 and create_date2010-10-05 23:59:591.4.8 对Where语句应用法则 避免在WHERE子句中使用in,not in,or 或者having。可以使用 exist 和not exist代替 in和not in。可以使用表链接代替 exist。Having可以用where代替，如果无法代替可以分两步处理。例子SELECT * FROM ORDERS WHERE CUSTOMER_NAME NOT IN (SELECT CUSTOMER_NAME FROM CUSTOMER)优化SELECT * FROM ORDERS WHERE CUSTOMER_NAME not exist (SELECT CUSTOMER_NAME FROM CUSTOMER) 不要以字符格式声明数字，要以数字格式声明字符值。否则会使索引无效，产生全表扫描。例子使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;不要使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 73691.4.9 对SELECT 语句法则在应用程序,存储过程和查询中限制使用select * from table这种方式。例子: 使用SELECT empno,ename,category FROM emp WHERE empno = 7369而不要使用 SELECT * FROM emp WHERE empno = 7369使用 IF NOT EXISTS (SELECT 1 FROM SRM_ZHSHSMMX WHERE WYM=WYM)而不要使用IF NOT EXISTS (SELECT * FROM SRM_ZHSHSMMX WHERE WYM=WYM)1.4.10 排序避免使用耗费资源的操作,如果客户端能排序、分组，那查询时就不要order by ,group by。 sql server的order by ,group by 的本质是把数据存放到tempdb中的临时表中进行处理，很耗临时表资源带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序1.4.11 慎用游标数据库一般的操作是集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功能搬到客户端。游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。1.5 SQL性能影响因素常见的影响数据访问速度的因素，有以下几种：1.5.1 没有索引或者没有用到索引数据库索引就像书籍中目录一样，使用户在访问数据库数据时，不必遍历所有数据就可以找到需要的数据。创建索引后，可以保证每行数据的唯一性，极大地提高数据检索效率，这是一中牺牲空间换取性能的方法。没有索引或者没有用到索引是数据访问速度慢最常见的因素，也是程序设计的一个缺陷所在。1.5.2 I/O吞吐量小，形成了瓶颈效应I/O吞吐量是影响数据访问速度的客观因素（硬件因素）。在一定的硬件环境下，利用优化的部署方案可适当提高I/O吞吐量。1.5.3 没有创建计算列导致查询不优化计算列是一个比较特殊的列，不填写任何设计类型，用户不可以改变该列的值。计算列的值是通过一定的函数公式等以另一个或多个列的值为输入值计算出的结果。如果没相应的计算列，在一些数据查询的时候需要对已有数据进行计算，从而浪费一部分性能。1.5.4 内存不足对数据库数据的查询访问毫无疑问会占用大量的内存空间，当内存不足的情况下，数据的访问速度会受到明显的影响甚至访问出现超时情况，是影响数据访问速度的客观因素。1.5.5 网络速度慢网络速度慢是影响数据访问速度的客观因素。可通过提高网络访问的位宽来解决。1.5.6 查询出的数据量过大当查询出的数据量过大时，内存的占用、系统时间的占用等都影响数据访问的速度。可以采用多次查询、定位查询、和查询数据量控制来解决。1.5.7 锁或者死锁锁或者死锁在数据库