花色中所用的sql管理格局,怎么着写出高质量S

      这几天有业务部门需要使用一个SAP B1老系统  中的报表,但是由于此报表没有加时间条件,导致一旦开始查询 就会导致B1系统异常退出。由于报表对应的SQL 是存在数据库中,所以想通过查找到这个报表的SQL,然后给SQL加时间条件的方式来处理(多年前的一个报表,不知道内部逻辑,很难重写)。 

(声明:本文内容摘自web开发者,仅供收藏学习之用,如有侵权请作者联系博主,博主将在第一时间删除)

原文地址:

以生存率统计为例。

# 问题的提出

 在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用 系统提交实际应用后,随着数据库中数据的增加,系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据,劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍,可见对于一个系统不是简单地能实现其功能就可,而是要写出高质量的 SQL语句,提高系统的可用性。

  在多数情况下,Oracle使用索引来更快地遍历表,优化器主要根据定义的索引来提高性能。但是,如果在SQL语句的where子句中写的 SQL代码不合理,就会造成优化器删去索引而使用全表扫描,一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引,这有助于写出高性能的SQL语句。

 

随着软件技术的不断发展,系统性能越来越重要。系统性能主要用:系统响应时间并发性来衡量。

 

造成SQL语句性能不佳大致有两个原因:

l  开发人员只关注查询结果的正确性,忽视查询语句的效率。

l  开发人员只关注SQL语句本身的效率,对SQL语句执行原理、影响SQL执行效率的主要因素不清楚。

 

*  前者可以通过深入学习SQL语法及各种SQL调优技巧进行解决。

   SQL调优是一个系统工程,熟悉SQL语法、掌握各种内嵌函数、分析函数的用法只是编写高效SQL的必要条件。

*  后者从分析SQL语句执行原理入手,指出SQL调优应在优化SQL解析和优化CBO上。

     但是苦于不清楚报表相关的表结构,不知道报表的存储位置,但只知道报表的名称 又陷入了不知道下一步该怎么处理的窘境。于是又开始大胆猜想,是不是有什么方法 可以根据数据的关键词 查询数据所在的表名呢?于是开始百度各种资料,证明 思路是可以行得通的,大致思路就是遍历每一个表的每一个字段 查询此字段是否包含关键词 。最终查到这篇文章写的方法 很不错,和大家分享下,同时也感谢博主的分享。

1、首先要搞明白什么叫执行计划?

  执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生欀如一条SQL语句如果用来从一个10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归档,当前只剩下5000条记录了,那查询优化器就会改变方案,采用 “全表扫描”方式。

  可见,执行计划并不是固定的,它是“个性化的”。产生一个正确的“执行计划”有两点很重要:

  (1) SQL语句是否清晰地告诉查询优化器它想干什么?

  (2) 查询优化器得到的数据库统计信息是否是最新的、正确的?

1.表示数据。产品对数据库里存在的字段并不熟悉,给出的公式都是逻辑上的,通过文献或者院方的需求推导的,需要将先将数据用数据库里已经有的字段通过处理表示出来。

# SQL语句调优

80%的数据库性能问题都是由于糟糕的SQL语句造成的。

 

2、统一SQL语句的写法

  对于以下两句SQL语句,程序员认为是相同的,数据库查询优化器认为是不同的。

  select * from dual
  select * From dual

  其实就是大小写不同,查询分析器就认为是两句不同的SQL语句,必须进行两次解析。生成2个执行计划。
  所以作为程序员,应该保证相同的查询语句在任何地方都一致,多一个空格都不行!

最复杂的一个参数是叫最近随访时间。这个参数是要取出来,代入到总的公式里,就是最外层那个大的sql。可以理解成一个大公式。

##  SQL语句优化的过程:

l  定位有问题的语句

l  检查执行计划

l  检查执行过程中优化器的统计信息

l  分析相关表的记录数、索引情况

l  改写SQL语句、使用HINT、调整索引、表分析

l  有些SQL语句不具备优化的可能,需要优化处理方式

l  达到最佳执行计划

CREATE PROCEDURE [dbo].[SP_FindValueInDB]
(
    @value VARCHAR(1024)
) 
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql VARCHAR(1024) 
    DECLARE @table VARCHAR(64) 
    DECLARE @column VARCHAR(64) 
    CREATE TABLE #t ( 
        tablename VARCHAR(64), 
        columnname VARCHAR(64) 
    ) 
    DECLARE TABLES CURSOR FOR 
    SELECT o.name, c.name FROM syscolumns c 
    INNER JOIN sysobjects o ON c.id = o.id 
    WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239) 
    ORDER BY o.name, c.name 
    OPEN TABLES 
        FETCH NEXT FROM TABLES 
        INTO @table, @column 
        WHILE @@FETCH_STATUS = 0 
        BEGIN 
            SET @sql = 'IF EXISTS(SELECT NULL FROM ['   @table   '] ' 
            SET @sql = @sql   'WHERE RTRIM(LTRIM(['   @column   '])) LIKE ''%'   @value   '%'') ' 
            SET @sql = @sql   'INSERT INTO #t VALUES ('''   @table   ''', ''' 
            SET @sql = @sql   @column   ''')' 
            EXEC(@sql) 
            FETCH NEXT FROM TABLES 
            INTO @table, @column 
        END 
    CLOSE TABLES 
    DEALLOCATE TABLES 
    SELECT * FROM #t 
    DROP TABLE #t 
End

3、不要把SQL语句写得太复杂

  我经常看到,从数据库中捕捉到的一条SQL语句打印出来有2张A4纸这么长。一般来说这么复杂的语句通常都是有问题的。我拿着这2页长的SQL语句去请教原作者,结果他说时间太长,他一时也看不懂了。可想而知,连原作者都有可能看糊涂的SQL语句,数据库也一样会看糊涂。

  一般,将一个Select语句的结果作为子集,然后从该子集中再进行查询,这种一层嵌套语句还是比较常见的,但是根据经验,超过3层嵌套,查询优化器就很容易给出错误的执行计划。因为它被绕晕了。像这种类似人工智能的东西,终究比人的分辨力要差些,如果人都看晕了,我可以保证数据库也会晕的。

  另外,执行计划是可以被重用的,越简单的SQL语句被重用的可能性越高。而复杂的SQL语句只要有一个字符发生变化就必须重新解析,然后再把这一大堆垃圾塞在内存里。可想而知,数据库的效率会何等低下。

我们库里有个叫随访时间的字段,但实际上不是这个。它的定义是:患者最近出院时间,最近门诊时间,最近一次人工有效随访的时间三个之间取最靠前一次的。这三个时间都不是具象的字段。

## 什么是好的SQL语句?

l  尽量简单,模块化

l  易读、易维护

l  节省资源、内存、CPU

l  扫描的数据块要少

l  少排序

l  不造成死锁

只需要传入一个想要查找的值,即可查询出这个值所在的表和字段名。

4、使用“临时表”暂存中间结果

  简化SQL语句的重要方法就是采用临时表暂存中间结果,但是,临时表的好处远远不止这些,将临时结果暂存在临时表,后面的查询就在tempdb中了,这可以避免程序中多次扫描主表,也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。

处理方式:(1)宽表,添加冗余字段。在患者表里,添加最近有效人工随访时间,最近出院时间和最近门诊时间,用程序来维护,update住院表和随访结果表,这些表的时候,同事对患者表进行维护。

## 首先要搞明白什么叫执行计划?

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归档,当前只剩下5000条记录了,那查询优化器就会改变方案,采用 “全表扫描”方式。

可见,执行计划并不是固定的,它是“个性化的”。产生一个正确的“执行计划”有两点很重要:

(1) SQL语句是否清晰地告诉查询优化器它想干什么?

(2)查询优化器得到的数据库统计信息是否是最新的、正确的?

exec [SP_FindValueInDB]  '仓库转储申请单'

5、 OLTP系统SQL语句必须采用绑定变量 

  select * from orderheader where changetime > ’2010-10-20 00:00:01′
  select * from orderheader where changetime > ’2010-09-22 00:00:01′

  以上两句语句,查询优化器认为是不同的SQL语句,需要解析两次。
  如果采用绑定变量

  select * from orderheader where changetime > @chgtime

  @chgtime变量可以传入任何值,这样大量的类似查询可以重用该执行计划了,这可以大大降低数据库解析SQL语句的负担。一次解析,多次重用,是提高数据库效率的原则。

                 (2)函数嵌套。greatest可以取三个最大值,对每个值用IFNULL进行处理。如果为NULL就取一个很久之前的时间。

# 什么是索引?

SQL索引有两种,聚集索引和非聚集索引,索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间

 

下面举两个简单的例子:

图书馆的例子:一个图书馆那么多书,怎么管理呢?建立一个字母开头的目录,例如:a开头的书,在第一排,b开头的在第二排,这样在找什么书就好说了,这个就是一个聚集索引,可是很多人借书找某某作者的,不知道书名怎么办?图书管理员在写一个目录,某某作者的书分别在第几排,第几排,这就是一个非聚集索引

字典的例子:字典前面的目录,可以按照拼音和部首去查询,我们想查询一个字,只需要根据拼音或者部首去查询,就可以快速的定位到这个汉字了,这个就是索引的好处,拼音查询法就是聚集索引,部首查询就是一个非聚集索引.

看了上面的例子,下面的一句话大家就很容易理解了:聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。就像字段,聚集索引是连续的,a后面肯定是b,非聚集索引就不连续了,就像图书馆的某个作者的书,有可能在第1个货架上和第10个货架上。还有一个小知识点就是:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

索引相关介绍:

软件开发人员真的了解SQL索引吗(聚集索引)

软件开发人员真的了解SQL索引吗(索引原理)

软件开发人员真的了解SQL索引吗(索引使用原则)

 查询出来的结果如下:

6、绑定变量窥测

  事物都存在两面性,绑定变量对大多数OLTP处理是适用的,但是也有例外。
  比如在where条件中的字段是“倾斜字段”的时候。

  “倾斜字段”指该列中的绝大多数的值都是相同的,一张人口调查表,其中“民族”这列,90%以上都是汉族。那么如果一个SQL语句要查询30岁的汉族人口有多少,那“民族”这列必然要被放在where条件中。这个时候如果采用绑定变量@nation会存在很大问题。

  试想如果@nation传入的第一个值是“汉族”,那整个执行计划必然会选择表扫描。然后,第二个值传入的是“布依族”,按理说“布依族”占的比例可能只有万分之一,应该采用索引查找。但是,由于重用了第一次解析的“汉族”的那个执行计划,那么第二次也将采用表扫描方式。这个问题就是著名的“绑定变量窥测”,建议对于“倾斜字段”不要采用绑定变量。

  1. SQL优化。需要提供数据的表有10几张。查询条件比较多。

## 索引的存储机制

 首先,无索引的表,查询时,是按照顺序存续的方法扫描每个记录来查找符合条件的记录,这样效率十分低下,举个例子,如果我们将字典的汉字随即打乱,没有前面的按照拼音或者部首查询,那么我们想找一个字,按照顺序的方式去一页页的找,这样效率有多底,大家可以想象。

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致,其实理解起来非常简单,还是举字典的例子:如果按照拼音查询,那么都是从a-z的,是具有连续性的,a后面就是b,b后面就是c,聚集索引就是这样的,他是和表的物理排列顺序是一样的,例如有id为聚集索引,那么1后面肯定是2,2后面肯定是3,所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是,可能按照偏房查询的时候,根据偏旁‘弓’字旁,索引出两个汉字,张和弘,但是这两个其实一个在100页,一个在1000页,(这里只是举个例子),他们的索引顺序和数据库表的排列顺序是不一样的,这个样的就是非聚集索引。

原理明白了,那他们是怎么存储的呢?在这里简单的说一下,聚集索引就是在数据库被开辟一个物理空间存放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个整个物理空间,而非聚集索引其实可以看作是一个含有聚集索引的表,他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针,其实就有点和堆栈差不多的感觉了。

图片 1

7、 只在必要的情况下才使用begin tran

  SQL Server中一句SQL语句默认就是一个事务,在该语句执行完成后也是默认commit的。其实,这就是begin tran的一个最小化的形式,好比在每句语句开头隐含了一个begin tran,结束时隐含了一个commit。

  有些情况下,我们需要显式声明begin tran,比如做“插、删、改”操作需要同时修改几个表,要求要么几个表都修改成功,要么都不成功。begin tran 可以起到这样的作用,它可以把若干SQL语句套在一起执行,最后再一起commit。好处是保证了数据的一致性,但任何事情都不是完美无缺的。Begin tran付出的代价是在提交之前,所有SQL语句锁住的资源都不能释放,直到commit掉。

  可见,如果Begin tran套住的SQL语句太多,那数据库的性能就糟糕了。在该大事务提交之前,必然会阻塞别的语句,造成block很多。

  Begin tran使用的原则是,在保证数据一致性的前提下,begin tran 套住的SQL语句越少越好!有些情况下可以采用触发器同步数据,不一定要用begin tran。

(1)动态拼接。查询条件很多,处理成,未加查询条件时为单表操作。原来的多个表join改成用子查询拼接的形式。然后还有做一些中间表。虽然innodb 对in没做什么优化处理,但是其他表数据量都比基础表患者表数据量要小。所以加了查询条件其实是个缩小结果集的过程。

## 什么情况下设置索引

动作描述

使用聚集索引 

 使用非聚集索引

 外键列

 应

 应

 主键列

 应

 应

 列经常被分组排序(order by)

 应

 应

 返回某范围内的数据

 应

 不应

 小数目的不同值

 应

 不应

 大数目的不同值

 不应

 应

 频繁更新的列

不应 

 应

 频繁修改索引列

 不应

 应

 一个或极少不同值

 不应

 不应

建立索引的原则

1) 定义主键的数据列一定要建立索引。

2) 定义有外键的数据列一定要建立索引。

3) 对于经常查询的数据列最好建立索引。

4) 对于需要在指定范围内的快速或频繁查询的数据列;

5) 经常用在WHERE子句中的数据列。

6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

8) 对于定义为text、image和bit的数据类型的列不要建立索引。

9) 对于经常存取的列避免建立索引

9) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

10) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

完美解决我的问题,再次感谢博主的分享 踏雪无痕

8、一些SQL查询语句应加上nolock

  在SQL语句中加nolock是提高SQL Server并发性能的重要手段,在oracle中并不需要这样做,因为oracle的结构更为合理,有undo表空间保存“数据前影”,该数据如果在修改中还未commit,那么你读到的是它修改之前的副本,该副本放在undo表空间中。这样,oracle的读、写可以做到互不影响,这也是oracle 广受称赞的地方。

  SQL Server 的读、写是会相互阻塞的,为了提高并发性能,对于一些查询,可以加上nolock,这样读的时候可以允许写,但缺点是可能读到未提交的脏数据。
使用 nolock有3条原则。

  (1)    查询的结果用于“插、删、改”的不能加nolock !

  (2)    查询的表属于频繁发生页分裂的,慎用nolock !

  (3)    使用临时表一样可以保存“数据前影”,起到类似oracle的undo表空间的功能,

  能采用临时表提高并发性能的,不要用nolock 。

(2)在经常查询的字段加索引,我们查询的东西唯一性太低,作为患者唯一性标识的病案号,采用的都是批量查询,就是输一堆查出来一堆一种。只在name上加了,实际上优化效果不大。但单表可以忍受,大概十几秒吧。

# SQL语句编写注意问题

style="color: #ff0000;">数据库系统按着从左到右的顺序来解析一个系列由 AND 连接的表达式,但是 Oracle 却是个例外,它是从右向左地解析表达式。可以利用数据库系统的这一特性,来将概率小的表达示放在前面,或者是如果两个表达式可能性相同,那么可将相对不复杂的表达式放在前面。这样做的话,如果第一个表达式为假的话,那么数据库系统就不必再费力去解析第二个表达式了。

1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num is null

最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.

备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。

不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num = 0

3.应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num=10 or Name = 'admin'

可以这样查询:

select id from t where num = 10
union all
select id from t where Name = 'admin'

5.in 和 not in 也要慎用,否则会导致全表扫描,如:

select id from t where num in(1,2,3)

对于连续的数值,能用 between 就不要用 in 了:

select id from t where num between 1 and 3

很多时候用 exists 代替 in 是一个好的选择:

select num from a where num in(select num from b)

用下面的语句替换:

select num from a where exists(select 1 from b where num=a.num)

6.下面的查询也将导致全表扫描:

select id from t where name like ‘
			

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:花色中所用的sql管理格局,怎么着写出高质量S

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。