`
shendixiong
  • 浏览: 398640 次
  • 性别: Icon_minigender_1
  • 来自: 长沙
社区版块
存档分类
最新评论

如何让你的SQL运行得更快

阅读更多
在使用SQL往往会陷入一个区,即太注于所得的果是否正确,而忽略了不同的实现方法之可能存在的性能差异,这种性能差异在大型的或是复杂的数据库环境中(如机事务处OLTP或决策支持系DSS)中表得尤
笔者在工作践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的接条件和不可化的where子句。
们进行适当的化后,其运行速度有了明地提高!
下面我将从三个方面分别进总结
了更直问题,所有例中的SQL运行时间经过测试,不超1秒的均表示< 1秒)。----
测试环: 主机:HP LH II---- 330MHZ---- 内存:128----
操作系Operserver5.0.4----
数据Sybase11.0.3
 
一、不合理的索引设计----
例:表record620000行,看在不同的索引下,下面几个 SQL的运行情况:
---- 1.date上建有一非个群集索引
select count(*) from record where date >'19991201' and date < '19991214'and amount >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上有大量的重复值,在非群集索引下,数据在物理上随机存放在数据上,在范围查,必须执行一次表描才能找到一范内的全部行。
---- 2.date上的一个群集索引
select count(*) from record where date >'19991201' and date < '19991214' and amount >2000 14秒)
select date,sum(amount) from record group by date28秒)
select count(*) from record where date >'19990901' and place in ('BJ','SH')14秒)
---- 分析:---- 在群集索引下,数据在物理上按序在数据上,重复值也排列在一起,因而在范围查,可以先找到个范的起末点,且只在个范描数据,避免了大范围扫描,提高了查询速度。
---- 3.placedateamount上的合索引
select count(*) from record where date >'19991201' and date < '19991214' and amount >2000 26秒)
select date,sum(amount) from record group by date27秒)
select count(*) from record where date >'19990901' and place in ('BJ, 'SH')< 1秒)
---- 分析:---- 是一个不很合理的合索引,因它的前列是place,第一和第二条SQL没有引用place,因此也没有利用上索引;第三个SQL使用了place,且引用的所有列都包含在合索引中,形成了索引覆盖,所以它的速度是非常快的。
---- 4.dateplaceamount上的合索引
select count(*) from record where date >'19991201' and date < '19991214' and amount >2000(< 1)
select date,sum(amount) from record group by date11秒)
select count(*) from record where date >'19990901' and place in ('BJ','SH')< 1秒)
---- 分析:---- 是一个合理的合索引。它将date列,使SQL都可以利用索引,并且在第一和第三个SQL中形成了索引覆盖,因而性能达到了最
---- 5.总结----
缺省情况下建立的索引是非群集索引,但有它并不是最佳的;合理的索引设计要建立在种查询的分析和预测上。
一般来
.有大量重复值、且常有范围查询between, >,< >=,< =)和order bygroup by生的列,可考建立群集索引;
.常同存取多列,且列都含有重复值可考建立合索引;
.合索引要尽量使关键查询形成索引覆盖,其前列一定是使用最繁的列。
 
二、不充份的接条件:
例:表card7896行,在card_no上有一个非聚集索引,表account191122行,在account_no上有一个非聚集索引,看在不同的表接条件下,两个SQL行情况:
select sum(a.amount) from account a,card b where a.card_no = b.card_no20秒)
select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.account_no< 1秒)
---- 分析:---- 在第一个接条件下,最佳查询方案是将account作外表,card作内表,利用card上的索引,其I/O次数可由以下公式估算
account上的22541+(外account191122*card对应表第一行所要找的3=595907I/O
在第二个接条件下,最佳查询方案是将card作外表,account作内表,利用account上的索引,其I/O次数可由以下公式估算:外card上的1944+(外card7896*account对应一行所要找的4= 33528I/O
,只有充份的接条件,真正的最佳方案才会被行。
总结
1.多表操作在被实际执行前,查询优化器会根据接条件,列出几可能的接方案并从中找出系统开销最小的最佳方案。接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式:外表中的匹配行数*表中一次找的次数确定,乘最小最佳方案。
2.行方案的方法-- set showplanon,打showplan选项,就可以看到序、使用何索引的信息;想看更详细的信息,需用sa角色dbcc(3604,310,302)
 
三、不可化的where子句
1.例:下列SQL条件句中的列都建有恰当的索引,但行速度却非常慢:
select * from record wheresubstring(card_no,1,4)='5378'(13)
select * from record whereamount/30< 100011秒)
select * from record whereconvert(char(10),date,112)='19991201'10秒)
分析:
where子句中列的任何操作果都是在SQL运行逐列算得到的,因此它不得不行表搜索,而没有使用列上面的索引;
如果果在查询编译时就能得到,那就可以被SQL化器化,使用索引,避免表搜索,因此将SQL重写成下面这样
select * from record where card_no like'5378%'< 1秒)
select * from record where amount< 1000*30< 1秒)
select * from record where date= '1999/12/01'< 1秒)
你会发现SQL快起来!
2.例:表stuff200000行,id_no上有非群集索引,看下面SQL
select count(*) from stuff where id_no in('0','1')23秒)
分析:---- where条件中的'in'逻辑上相当于'or',所以法分析器会将in ('0','1')id_no ='0' or id_no='1'行。
期望它会根据or子句分别查找,再将果相加,这样可以利用id_no上的索引;
实际上(根据showplan,它却采用了"OR策略",即先取出or子句的行,存入临时数据的工作表中,再建立唯一索引以去掉重行,最后从临时表中果。因此,实际过程没有利用id_no上索引,并且完成时间还要受tempdb数据性能的影响。
明,表的行数越多,工作表的性能就越差,当stuff620000时间竟达到220秒!不如将or子句分
select count(*) from stuff where id_no='0'select count(*) from stuff where id_no='1'
得到两个果,再作一次加法合算。因为每句都使用了索引,时间只有3秒,在620000行下,时间也只有4秒。
或者,用更好的方法,写一个简单的存储过程:
create proc count_stuff asdeclare @a intdeclare @b intdeclare @c intdeclare @d char(10)beginselect @a=count(*) from stuff where id_no='0'select @b=count(*) from stuff where id_no='1'endselect @c=@a+@bselect @d=convert(char(10),@c)print @d
直接算出果,时间同上面一快!
 
---- 总结---- ,所谓优化即where子句利用了索引,不可化即生了表描或开销
1.任何列的操作都将致表描,它包括数据函数、算表达式等等,查询时要尽可能将操作移至等号右
2.inor子句常会使用工作表,使索引失效;如果不生大量重复值,可以考把子句拆;拆的子句中应该包含索引。
3.要善于使用存储过程,它使SQL得更加灵活和高效。
从以上些例子可以看出,SQL化的实质就是在果正确的前提下,用化器可以识别句,充份利用索引,减少表描的I/O次数,尽量避免表搜索的生。其SQL的性能化是一个复杂程,上述些只是在次的一,深入研究及数据库层源配置、网络层的流量控制以及操作系统层设计
分享到:
评论

相关推荐

    如何让你的SQL运行得更快.

    如何让你的SQL运行得更快.

    SQL运行得更快.rar

    "SQL运行得更快"这个主题涵盖了多种优化策略和技术,旨在帮助用户更好地理解和应用这些方法。以下是对PDF文件"SQL运行得更快"中可能涉及的关键知识点的详细阐述: 1. **查询优化器**:SQL查询优化器是数据库管理...

    如何让SQL运行得更快.doc

    SQL 优化是数据库管理员和开发者经常遇到的问题,如何让 SQL 运行得更快是数据库性能优化的关键。通过对索引设计和连接条件的优化,可以大幅度提高 SQL 的执行速度。 一、索引设计的重要性 索引是数据库中一个非常...

    如何让你的SQL运行得更快-实例.doc

    《如何让你的SQL运行得更快——实例分析》 在当今的互联网时代,数据库管理与查询效率至关重要,尤其是在处理大量数据的在线事务处理(OLTP)和决策支持系统(DSS)中。SQL(结构化查询语言)是数据库操作的基础,...

    如何让你的SQL运行更快

    本文将深入探讨如何通过合理的设计和调整索引来改善SQL的执行效率,从而让SQL运行得更快。 首先,我们需要理解索引的工作原理。索引是数据库中用于快速查找数据的数据结构,类似于书籍的目录。在非群集索引中,数据...

    如何让你的SQL运行的更快

    在SQL Server中,让SQL运行得更快是数据库管理员和开发人员的重要任务,因为这直接影响到系统的性能和用户体验。本文将探讨三个关键优化方面:索引设计、连接条件和WHERE子句优化。 首先,我们来看不合理的索引设计...

    数据库优化之让你的SQL运行得更快

    只有深入理解这些,才能在保证查询结果正确性的同时,提升SQL的运行速度,进而提高整个系统的性能。在实际工作中,需要不断监控和分析SQL执行计划,持续优化数据库设计和查询策略,以适应不断变化的业务需求。

    如何让你的SQL 运行更快

    人们在使用SQL时往往会陷入一个误区,即太关注于所得的结果是否正确,而忽略了不同的实现方法之间可能存在的性能差异,这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得...

    如何让SQL运行得更快

    以下是一些关于如何提升SQL运行速度的关键知识点: 1. **索引设计**: - **非群集索引**:非群集索引在物理存储上并不按照索引排序,数据分散在数据页上。对于范围查询,可能需要全表扫描来获取所有符合条件的行,...

    监控 SQL Server 的运行状况

    通过监控这些方面,可以帮助您更好地了解 SQL Server 的运行状况,并且能够快速诊断和解决问题。 监控 SQL Server 的运行状况是数据库管理员的重要任务之一。通过使用动态管理视图和动态管理函数,数据库管理员可以...

    SQL Server中存储过程比直接运行SQL语句慢的原因

    存储过程在创建时进行一次性编译,之后的执行会使用已编译的计划,从而避免了每次执行时的语法解析和编译步骤,理论上应该更快。然而,当存储过程中的参数变化导致执行计划不适用时,SQL Server可能会为每次不同的...

    SQLPrompt10.4.zip运行在 sql server2019 亲测可用

    4. **性能优化建议**:当编写可能影响性能的查询时,SQLPrompt会给出优化建议,比如使用更高效的索引或者重写查询结构,有助于提升SQL Server的执行效率。 5. **代码片段管理**:内置了大量的代码片段,可以自定义...

    2018SqlServer Sql Prompt

    而"2018 SQL Server Sql Prompt"正是这样一款为SQL Server量身定制的数据库帮助工具,它以其独特的功能特性,让SQL脚本的编写变得更加便捷,是数据库管理员和开发人员的理想伙伴。 Sql Prompt是Redgate Software...

    Quicksql360开源的更简单更安全更快速的跨数据源统一SQL查询引擎

    它提供了一套完整的SQL审核和过滤机制,可以帮助防止恶意的SQL命令执行,保障了系统的安全运行。此外,由于其开源特性,社区的持续贡献和漏洞修复也增强了其安全性。 在性能上,Quicksql360进行了深度优化,以确保...

Global site tag (gtag.js) - Google Analytics