`

如何才能让你的SQL运行得更快(sql语句运行在sybase数据库下)

阅读更多

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

笔者在工作实践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。

在对它们进行适当的优化后,其运行速度有了明显地提高!
下面我将从这三个方面分别进行总结:
为了更直观地说明问题,所有实例中的SQL运行时间均经过测试,不超过1秒的均表示为(< 1秒)。
测试环境: 主机:HP LH II---- 主频:330MHZ---- 内存:128兆----
操作系统:Operserver5.0.4----
数据库:Sybase11.0.3


一、不合理的索引设计

例:表record有620000行,试看在不同的索引下,下面几个 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 date(28秒)

select count(*) from record
where date >'19990901' and place in ('BJ','SH')(14秒)

分析:在群集索引下,数据在物理上按顺序在数据页上,重复值也排列在一起,因而在范围查找时,可以先找到这个范围的起末点,且只在这个范围内扫描数据页,避免了大范围扫描,提高了查询速度。
3.在place,date,amount上的组合索引

select count(*) from record
where date >'19991201' and date < '19991214' and amount >2000 (26秒)

select date,sum(amount) from record
group by date(27秒)

select count(*) from record
where date > '19990901' and place in ('BJ, 'SH')(< 1秒)

分析:这是一个不很合理的组合索引,因为它的前导列是place,第一和第二条SQL没有引用place,因此也没有利用上索引;第三个SQL使用了place,且引用的所有列都包含在组合索引中,形成了索引覆盖,所以它的速度是非常快的。
4.在date,place,amount上的组合索引
select count(*) from record
where date > '19991201' and date < '19991214' and amount >2000(< 1秒)

select date,sum(amount) from record
group by date(11秒)

select count(*) from record
where date > '19990901' and place in ('BJ','SH')

分析:这是一个合理的组合索引。它将date作为前导列,使每个SQL都可以利用索引,并且在第一和第三个SQL中形成了索引覆盖,因而性能达到了最优。

5.总结:缺省情况下建立的索引是非群集索引,但有时它并不是最佳的;合理的索引设计要建立在对各种查询的分析和预测上。

一般来说:
有大量重复值、且经常有范围查询(between, >,< ,>=,< =)和 order by、group by发生的列,可考虑建立群集索引;
经常同时存取多列,且每列都含有重复值可考虑建立组合索引;
组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。


二、不充份的连接条件:

例:表card有7896行,在card_no上有一个非聚集索引,表account有191122行,在account_no上有一个非聚集索引,试看在不同的表连接条件下,两个SQL的执行情况:
select sum(a.amount) from account a,card b
where a.card_no = b.card_no(20秒)

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页+(外层表account的191122行*内层表card上对应外层表第一行所要查找的3页)=595907次I/O

在 第二个连接条件下,最佳查询方案是将card作外层表,account作内层表,利用account上的索引,其I/O次数可由以下公式估算为:外层表 card上的1944页+(外层表card的7896行*内层表account上对应外层表每一行所要查找的4页)= 33528次I/O

可见,只有充份的连接条件,真正的最佳方案才会被执行。

总结: 1.多表操作在被实际执行前,查询优化器会根据连接条件,列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式:外层表中的匹配行数*内层表中每一次查找的次数确定,乘积最小为最佳方案。
2.查看执行方案的方法-- 用set showplanon,打开showplan选项,就可以看到连接顺序、使用何种索引的信息;想看更详细的信息,需用sa角色执行dbcc(3604,310,302)。

三、不可优化的where子句

1.例:下列SQL条件语句中的列都建有恰当的索引,但执行速度却非常慢:
select * from record where substring(card_no,1,4)='5378'(13秒)
select * from record where amount/30< 1000(11秒)
select * from record where convert(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.例:表stuff有200000行,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数据库性能的影响。

实践证明,表的行数越多,工作表的性能就越差,当stuff有620000行时,执行时间竟达到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
as
declare @a int
declare @b int
declare @c int
declare @d char(10)
begin
select @a=count(*) from stuff where id_no='0'
select @b=count(*) from stuff where id_no='1'
end
select 3 Z/ I y( K0 u; F9 Y @c=@a+@b
select @d=convert(char(10),@c)
print @d

直接算出结果,执行时间同上面一样快!
总结:
大家可以看到,优化即where子句利用了索引,不可优化即发生了表扫描或额外开销。

1.任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。
2.in、or子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引。

3.要善于使用存储过程,它使SQL变得更加灵活和高效。
从以上这些例子可以看出,SQL优化的实质就是在结果正确的前提下,用优化器可以识别的语句,充份利用索引,减少表扫描的I/O次数,尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程,上述这些只是在应用层次的一种体现,深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的 总体设计

分享到:
评论

相关推荐

    Sybase数据库的SQL语法资料

    存储过程是一组预先编译的SQL语句,可以接受参数并返回结果。 ```sql CREATE PROCEDURE CalculateBonus @EmployeeID INT, @Bonus DECIMAL(10,2) OUTPUT AS BEGIN UPDATE Employees SET Salary = Salary + @Bonus ...

    恢复Sybase SQL Anywhere数据库的好方法

    5. **执行恢复操作**:通过`rtsqlw.exe`运行SQL语句,指定用户ID为`dba`,密码为`sql`,执行`db.sql`中包含的恢复命令,将日志文件转换的SQL操作应用到新数据库上。 请注意,`.log`文件在整个恢复过程中起着至关...

    Mysql、oracle、Sybase数据库两列合并成一列的sql语句

    本文将详细介绍如何在MySQL、Oracle以及Sybase三种主流数据库中使用SQL语句来完成两列数据的合并操作。 ### 1. MySQL中的两列合并 在MySQL中,可以使用`CONCAT()`函数或者连接运算符`+`来合并两个字段。但是,需要...

    Sybase数据库SQL过程详解

    所有Sybase数据库中的存储过程都是使用Transact-SQL语句创建的。创建一个存储过程的基本语法如下: ```sql CREATE PROCEDURE procedure_name AS BEGIN -- SQL statements here END; ``` 例如,下面是一个简单的...

    sybase库中导出全部表的oracle、mysql和sybase的建表语句

    它可能包含了连接数据库、执行SQL、解析结果和生成SQL语句的逻辑。 接下来是`oracle`,它是另一种广泛使用的RDBMS,具有不同的语法和特性。将Sybase的建表语句转换为Oracle格式,需要考虑Oracle特定的语法差异,如...

    sybase数据库sql常见问题处理

    为了了解Sybase数据库在运行过程中对CPU资源的消耗情况,可以使用以下命令: ```sql EXEC sp_sysmon 'internal', 'model'; ``` 其中,`internal`参数用于显示内部引擎的活动情况,`model`参数则列出所有可用的模型...

    sybase数据库无法删除解决办法

    在使用Sybase数据库时,有时会遇到无法删除数据库的情况,这可能是由于多种原因导致的。以下是一些可能的解决步骤和相关知识点,帮助你解决Sybase数据库无法删除的问题。 1. **检查数据库状态**:首先,确认数据库...

    数据库转换工具,可实现oracle,mysql,sqlserver,sybase,db2相互转换

    数据库转换工具,可实现oracle,mysql,sqlserver,sybase,db2相互转换

    sybase 数据库 jdbc 驱动下载

    首先,JDBC驱动是Java应用程序与数据库之间通信的桥梁,它提供了Java API来执行SQL语句并处理结果。对于Sybase数据库,JDBC驱动主要有以下几种类型: 1. **类型1(纯Java驱动)**:也称为桥接驱动,它依赖于数据库...

    sybase ASE sql expert

    - 探索并使用SQL Expert的各种功能,例如在“查询分析器”中输入SQL语句进行分析,或者在“性能监视器”中查看数据库状态。 - 利用“优化顾问”来优化查询性能,根据建议进行调整。 - 定期生成性能报告,以便持续...

    Sybase 数据库维护快速参考手册v2.0

    本快速参考手册v2.0是针对Sybase数据库维护的专业指南,旨在帮助管理员更有效地管理和优化其数据库环境。 1. **数据库基础知识** - 数据库概念:理解数据库的基本构成,包括数据表、索引、视图和存储过程。 - ...

    Sybase数据库连接方法

    `SyBaseHelper.cs`文件看起来是一个自定义的帮助类,它可能封装了连接Sybase数据库、执行SQL语句、处理结果集等功能。引入这样的辅助类可以使代码更加简洁和易于维护。开发人员可以参考或直接使用这个类来快速集成...

    Sybase 数据库浏览器

    2. **开发人员**:在开发过程中,开发人员可以快速查看和测试SQL语句,调试数据库相关代码。 3. **数据分析**:数据分析人员可以便捷地查询和导出数据,进行数据分析和报表制作。 4. **培训与教育**:在教学环境中...

    sybase数据库查被锁的表.doc

    Sybase 数据库锁机制和锁表查询 Sybase 数据库锁机制是指数据库管理系统中用于控制并发访问和维护数据一致性的机制。锁机制可以防止多个事务同时访问同一资源,避免数据的不一致和损坏。 在 Sybase 数据库中,锁...

    VB连接SQL,SyBase数据库操作实例+源码

    在VB(Visual Basic)编程中,连接和操作SQL Server或SyBase数据库是一项基本技能。本实例将通过提供的源码详细讲解如何实现这一功能。VB作为经典的面向对象编程语言,其强大的窗体设计和数据库访问能力使得它在开发...

    SQL连接Sybase数据库的设定方法.pdf

    在配置安全性后,我们可以使用 SQL 语句来读取 Sybase 数据库中的数据了。例如,我们可以使用以下 SQL 语句来读取 Sybase 数据库中的数据: ``` select * from SYBASELINK.cardcenter.dbo.VIPCARD ``` 如果服务器...

    sybase导出建表SQL工具

    标题中的“sybase导出建表SQL工具”指的是一个专门针对Sybase数据库系统的应用程序,它的主要功能是帮助用户方便地导出数据库中的表结构定义,即建表SQL语句。在数据库管理中,建表SQL语句是创建数据库表结构的关键...

    JTDS和JDBC连接Sybase数据库

    标题中的“JTDS和JDBC连接Sybase数据库”涉及到的是Java开发中的一种数据库连接技术,主要聚焦于如何通过Java应用程序与Sybase数据库进行交互。Sybase是一种流行的关系型数据库管理系统,广泛应用于企业级应用中。...

    Sql语句和数据库教程

    内容概要:介绍几种经常使用的数据库,如MySQL,mssql,Oracle,DB2,...能学到什么:了解几种数据库的使用和底层原理,以及SQL语句的语法,示例。以及数据库表,视图,函数,索引等,以及经常碰到的数据库问题的处理

Global site tag (gtag.js) - Google Analytics