精华内容
下载资源
问答
  • 优化sql
    千次阅读
    2016-10-29 15:35:18

    通常,网站的性能瓶颈在数据库查询,如果你希望你的网站在一定阶段之内保持稳定,优化你的SQL和数据库是非常必要的优化环节。

    优化数据库是一个很大的话题,这里只是摘要一些比较关键的优化参考建议,并且需要具体分析项目的情况才能给出最合理的优化建议,所以具体的优化建议你应该咨询你公司的架构师或者DBA。

    1. 选择合适的存储引擎

    MySQL 为例,常用的有两个存储引擎 MyISAMInnoDB,它们各有利弊。

    MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到更新操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是非常快的。

    InnoDB 支持行锁,于是在写操作比较多的时候,会更优秀。并且它还支持更多的高级应用,比如:事务和外键。

    2. 优化字段的数据类型

    记住一个原则,越小的列会越快。对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把你的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。

    如果一个表只有几列数据(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。当然,你也需要保留足够的扩展空间。

    3. 为搜索字段添加索引

    索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你会经常用来做搜索,那么最好是为其建立普通索引,除非你要搜索的字段是大的文本字段,那应该建立全文索引。

    4. 避免使用 Select *

    从数据库里读出越多的数据,查询就会越慢。并且,如果你的数据库服务器和WEB服务器是两台独立的服务器,这还会增加网络传输的负载。即使你要查询数据表的所有字段,也尽量不要用 * 通配符,把所有的字段列出来也比 * 通配符的查询效率高。

    5. 尽可能的使用 NOT NULL

    除非你有很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL,并给一个默认值。 NULL 需要额外的空间,当然,并不是说你就不能使用NULL了,现实情况是很复杂的,有些情况下,你需要使用NULL值。

    6. 固定长度的表会更快

    如果表中的所有字段都是固定长度的,整个表会被认为是 “static” 或 “fixed-length”。

    表中如果没有VARCHAR、TEXT和BLOB这些类型的字段,就是固定长度的 。只要你包括了其中一个类型的字段,这个表就不是固定长度静态表,这样,MySQL 引擎会用另一种方法来处理。

    固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量,所以查询很快。而如果字段不是定长的,那么每次要找下一条的话,需要程序找到主键。

    固定长度的表也更容易被缓存和重建。唯一的缺点是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,都是要分配那么多的空间。可使用垂直分割技术,把表分割为两个,一个定长的,一个不是定长的。

    7. 使用垂直分割技术

    “垂直分割”是把一张复杂的数据表按列分割成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。

    例如:在user表中有一个字段是家庭地址,这个字段是可选字段,和其他的字段相比,你并不需要经常操作这个字段。那么,就可以把它放到另外一张表中, 这样会让user表有更好的性能。对于用户表来说,只有用户ID、用户名、密码、用户角色等会被经常使用。小一点的表总是会有好的性能。

    8. 使用EXPLAIN或DESC分析SELECT查询语句

    使用 EXPLAIN 或 DESC 关键字分析SQL查询语句,可以知道MySQL是如何处理你的SQL语句的。

    EXPLAIN 的结果会显示,SQL语句中索引的使用情况,要查询的数据是如何在表中进行搜索扫描和排序的等等。

    9. 不要使用 ORDER BY RAND() 随机排序

    一些新手程序员,喜欢使用 ORDER BY RAND() 命令,来对查询结果进行随机排序,然后取出几条记录。但这却严重影响了性能,如果真的需要随机显示几条数据,可通过其他更好的方法来实现。

    更多相关内容
  • 详解优化sql的过程(看完可以跟面试官正面对线)

    千次阅读 多人点赞 2021-10-19 10:37:12
    详解优化sql的过程 在我们平时的面试中,如果面试官问起数据库的问题时,一般都逃不开数据库引擎的区别和如何优化sql的问题,关于数据库引擎的区别,我上一篇文章就很详细的写了有兴趣的可以去学习学习(Mysql引擎...

    详解优化sql的过程

    在我们平时的面试中,如果面试官问起数据库的问题时,一般都逃不开数据库引擎的区别和如何优化sql的问题,关于数据库引擎的区别,我上一篇文章就很详细的写了有兴趣的可以去学习学习(Mysql引擎之间的区别),下面我们就一起来学习学习如何在面试中关于Sql优化问题与面试官进行对线。

    1.很多人面试中,当面试官问起你在实际开发中,你是怎么处理一些sql执行很慢的,很多面试者可能想都不想就说直接加索引,这虽然很笼统的说出了如何优化sql 的一种方式,但是这样的答案,在面试官看来是不合格的,不出众的,因为这种回答加索引的,在面试中太多人是这样回答的,这样不能让面试官觉得你是正在懂优化sql 的,因此接下来我们讲讲怎么样的回答能够使得面试官认为你是真正学习过sql优化或者真正在实际开发中使用过msql优化的。

    2.sql执行慢总体上有两种优化方式:一种是软件层面的优化、另一种是硬件层面的优化

    2.1 硬件层面的优化

    首先我们先来说说硬件层面优化:

    1. 配置运行速度更快的CUP
    2. 把机械硬盘更换成固态硬盘
    3. 加大运行内存

    硬件层面优化最主要是上面这几种方式。

    2.2 软件层面的优化(基于Mysql 8.0及以上)

    关于软件层面的优化,也是本文章的最主要的核心内容,接下来我们将结合我实际开发中用到的案例进行详细的讲解。

    2.2.1 我们要抛弃在面试中,面试官一问到如何优化一些慢查询的时,就不假思索地回答加索引的“陋习”。

    当我们实际开发中,我们在执行sql语句需要很长的时间时,我们需要对该语句进行分析,首先是使用explain命令对sql进行分析。接下来我们以案例进行讲解:

    即将用到的表结构:

    在这里插入图片描述

    创表语句:
    CREATE TABLEinnodb_table(idint DEFAULT NULL,namevarchar(45) DEFAULT NULL,sexvarchar(45) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

    我创建了以上简单的数据表。

    如何我们执行了一个sql语句
    在这里插入图片描述
    假如执行的时间花了很久,那么我们就要对该语句进行分析,分析过程如下:

    在这里插入图片描述然后发现type为all,说明进行了全表扫描,并且在possible_keys和key中都是为null,说明没有用到索引,其实我们一开始就没有建立索引,因此我们对该sql的优化是对该表进行加个索引,操作如下:

    ALTER TABLE `shop`.`innodb_table` 
    CHANGE COLUMN `id` `id` INT NOT NULL ,
    ADD PRIMARY KEY (`id`);
    
    

    即假如了字段id的索引,然后我们再次执行explain命令
    在这里插入图片描述发现sql查询语句用到了索引,这样就能加快了sql的执行的速度。

    2.2.2 如果上面的查询语句不再是 select * from innodb_table where id =101;,而是变成了select * from innodb_table where name=?时,我们应该怎么优化呢?在这里插入图片描述

    执行分析结果很显然的使用了全表扫描,这是读到这里的读者,很清楚的知道要怎么加索引了,对,就是加一个字段为name 的索引;

    alter table `innodb_table` add index name_index(name);
    
    

    在这里我们能看到两条命令的分析结果中possible_keys都是name_index,但是第一条的key却为null,而第二条key却为name_key,从结果看出,第一条查询没有使用到了索引,第二条的索引使用到了索引,这是为什么呢?关于这个的原因,我再文章的后面会进行讲解,这里我们还是先探究加索引方式。
    在这里插入图片描述从第二跳的命令中,我们能够分析出我们加了name字段索引后,查询语句使用到了我创建的索引了,因此这就是我们对该语句的优化方式。

    2.2.3 此时我们的需求又发生了变化,变成了 select name,sex from innodb_table where name =‘444’;那我们要怎么优化呢?首先我们先要分析一下该语句,结果如下:

    在这里插入图片描述name索引,很多人就会觉得这句sql已经很完美了,不能再优化了,其实不是这样的。在innodb引擎中,我们建立的普通索引的树的叶子节点存的是该数据对应的主键的值,而不是存了我们需要查的数据,也就是说,当我们使用了普通的索引后,只是查到该数据的主键值,然后需要再次回表到主键索引的树中,才能读取出我们想要的数据,其实我们可以使用联合索引的覆盖索引的特性去优化该查询的语句的,这样就可以减少回表的次数:

    非主键索引树的结构:
    在这里插入图片描述

    联合索引树的结构:
    在这里插入图片描述
    因此从上面的索引树结构中,我们发现如果我们创建(name,sex)联合索引,那么就可以省去回表的步骤,这也能加快sql执行速度。

    alter table `innodb_table` add index name_sex_index(name,sex);
    

    执行结果:
    在这里插入图片描述
    很显然三条命令中,只有第三条使用到了我们创建的索引,这和2.2.2中出现的问题,我们先不进行探究,但是这样的联合索引的优化可以提高查询的效率。

    2.2.4 如果通过上面的方式,sql优化的效果还是不明显,那我们就需要考虑是不是表中的数据真的太大了,那就需要进行分库分表了,分库分表可以分为垂直分和水平分,根据自身的业务进行分。
    2.2.5 接下来我们就来说说为什么我们建立了索引,而sql在最后执行的时候没有用到呢?

    Mysql可以分为几个部分:连接器、分析器、优化器、执行器、引擎。
    选择哪个索引的工作是优化器复制的,而优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的CPU资源越少。
    在mysql执行sql之前,优化器是不能精确的知道满足条件有多少行的,只能通过之前统计的信息来预估有多少行,因此这一步就有可能使得索引选择时与我们预想的结果不一样。
    这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”。也就是说,这个基数越大,索引的区分度越好。
    我们可以使用show index方法,看到一个索引的基数

    (注意:Cardinality就是我们上面所说的基数)
    在这里插入图片描述通过show index发现我们创建索引的区分度(基数)都不是很大,所以这就导致了优化器选择索引时就会出错。如果我们真的需要mysql按照我们预想的使用索引,我们就可以通过强制索引的方式就行优化了。

    例如:

     select name,sex from innodb_table force(name_sex_index)  where name ='444' and sex='nv';
    

    结果如下:
    在这里插入图片描述不过我们在开发中一般不会使用强制索引的方式,因为优化器选错索引的情况是很少发生的,而且这样写的话,索引改名字了,sql语句就需要改;还有就是比如还数据库了,不同数据库强制索引的方式不一样,因此不建议开发中使用。

    本期就先讲到这,下期我们讲讲如何优化我们进行插入操作时比较慢的方法。

    展开全文
  • 优化sql的十五个技巧

    千次阅读 2021-11-11 14:54:34
    那么你首先想到的很有可能是优化sql语句,因为它的改造成本相对于代码来说也要小得多。 那么,如何优化sql语句呢? 这篇文章从15个方面,分享了sql优化的一些小技巧,希望对你有所帮助。 Java干货合集 1 避免...

    前言

    sql优化是一个大家都比较关注的热门话题,无论你在面试,还是工作中,都很有可能会遇到。

    如果某天你负责的某个线上接口,出现了性能问题,需要做优化。那么你首先想到的很有可能是优化sql语句,因为它的改造成本相对于代码来说也要小得多。

    那么,如何优化sql语句呢?

    这篇文章从15个方面,分享了sql优化的一些小技巧,希望对你有所帮助。

    Java干货合集

    1 避免使用select *

    很多时候,我们写sql语句时,为了方便,喜欢直接使用select *,一次性查出表中所有列的数据。

    反例:

    在实际业务场景中,可能我们真正需要使用的只有其中一两列。查了很多数据,但是不用,白白浪费了数据库资源,比如:内存或者cpu。

    此外,多查出来的数据,通过网络IO传输的过程中,也会增加数据传输的时间。

    还有一个最重要的问题是:select *不会走覆盖索引,会出现大量的回表操作,而从导致查询sql的性能很低。

    那么,如何优化呢?

    例:

    sql语句查询时,只查需要用到的列,多余的列根本无需查出来。

    2 用union all代替union

    我们都知道sql语句使用union关键字后,可以获取排重后的数据。而如果使用union all关键字,可以获取所有数据,包含重复的数据。

    反例:

    正例:

    3 小表驱动大表

    小表驱动大表,也就是说用小表的数据集驱动大表的数据集。

    • in 适用于左边大表,右边小表。
    • exists 适用于左边小表,右边大表。
    • 不管是用in,还是exists关键字,其核心思想都是用小表驱动大表。

    4 批量操作

    例:

    这样只需要远程请求一次数据库,sql性能会得到提升,数据量越多,提升越大。

    但需要注意的是,不建议一次批量操作太多的数据,如果数据太多数据库响应也会很慢。批量操作需要把握一个度,建议每批数据尽量控制在500以内。如果数据多于500,则分多批次处理。

    5 多用limit

    有时候,我们需要查询某些数据中的第一条,比如:查询某个用户下的第一个订单,想看看他第一次的首单时间。

    反例;

    根据用户id查询订单,按下单时间排序,先查出该用户所有的订单数据,得到一个订单集合。 然后在代码中,获取第一个元素的数据,即首单的数据,就能获取首单时间。

    正例:

     使用limit 1,只返回该用户下单时间最小的那一条数据即可。此外,在删除或者修改数据时,为了防止误操作,导致删除或修改了不相干的数据,也可以在sql语句最后加上limit。

    6 in中值太多

    更多Java技巧 戳icon-default.png?t=LA92https://jq.qq.com/?_wv=1027&k=y4KJG4cb

     

     

     

     

     

     

     

     

     

     

    展开全文
  • MySQL性能优化 SQL优化方法技巧
  • Oracle SQL性能优化 SQL优化

    万次阅读 2017-07-31 08:47:44
    (1) 选择最有效率的表名顺序(只在基于规则的优化器(Oracle有两种优化器:RBO基于规则的优化器和CBO基于成本的优化器)中有效): ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表...

    (1) 选择最有效率的表名顺序(只在基于规则的优化器(Oracle有两种优化器:RBO基于规则的优化器和CBO基于成本的优化器)中有效):
    ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
    (2) WHERE子句中的连接顺序.:
    ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
    (3) SELECT子句中避免使用 ‘ * ‘:
    ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
    (4) 减少访问数据库的次数:
    ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等;
    (5) 在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200
    (6) 使用DECODE函数来减少处理时间:
    使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
    (7) 整合简单,无关联的数据库访问:
    如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)
    (8) 删除重复记录:
    最高效的删除重复记录方法 ( 因为使用了ROWID)例子:
    DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
    FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
    (9) 用TRUNCATE替代DELETE:
    当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
    (10) 尽量多使用COMMIT:
    只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
    COMMIT所释放的资源:
    a. 回滚段上用于恢复数据的信息.
    b. 被程序语句获得的锁
    c. redo log buffer 中的空间
    d. ORACLE为管理上述3种资源中的内部花费
    (11) 用Where子句替换HAVING子句:
    避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后,因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的,where也应该比having快点的,因为它过滤数据后才进行sum,在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是where可以使用rushmore技术,而having就不能,在速度上后者要慢如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。在多表联接查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里
    (12) 减少对表的查询:
    在含有子查询的SQL语句中,要特别注意减少对表的查询.例子:
    SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
    TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
    (13) 通过内部函数提高SQL效率.:
    复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
    (14) 使用表的别名(Alias):
    当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
    (15) 用EXISTS替代IN、用NOT EXISTS替代NOT IN:
    在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
    例子:
    (高效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
    (低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
    (16) 识别’低效执行’的SQL语句:
    虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法:
    SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
    ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
    ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
    SQL_TEXT
    FROM V$SQLAREA
    WHERE EXECUTIONS>0
    AND BUFFER_GETS > 0
    AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
    ORDER BY 4 DESC;

    (17) 用索引提高效率:
    索引是表的一个概念部分,用来提高检索数据的效率,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.:
    ALTER INDEX REBUILD
    (18) 用EXISTS替换DISTINCT:
    当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子:
    (低效):
    SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
    WHERE D.DEPT_NO = E.DEPT_NO
    (高效):
    SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’
    FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
    (19) sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行
    (20) 在java代码中尽量少用连接符“+”连接字符串!
    (21) 避免在索引列上使用NOT 通常, 
    我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
    (22) 避免在索引列上使用计算.
    WHERE子句中,如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描.
    举例:
    低效:
    SELECT … FROM DEPT WHERE SAL * 12 > 25000;
    高效:
    SELECT … FROM DEPT WHERE SAL > 25000/12;
    (23) 用>=替代>
    高效:
    SELECT * FROM EMP WHERE DEPTNO >=4
    低效:
    SELECT * FROM EMP WHERE DEPTNO >3
    两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
    (24) 用UNION替换OR (适用于索引列)
    通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.
    高效:
    SELECT LOC_ID , LOC_DESC , REGION
    FROM LOCATION
    WHERE LOC_ID = 10
    UNION
    SELECT LOC_ID , LOC_DESC , REGION
    FROM LOCATION
    WHERE REGION = “MELBOURNE”
    低效:
    SELECT LOC_ID , LOC_DESC , REGION
    FROM LOCATION
    WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
    如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
    (25) 用IN来替换OR
    这是一条简单易记的规则,但是实际的执行效果还须检验,在ORACLE8i下,两者的执行路径似乎是相同的. 
    低效:
    SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
    高效
    SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);
    (26) 避免在索引列上使用IS NULL和IS NOT NULL
    避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引.对于单列索引,如果列包含空值,索引中将不存在此记录. 对于复合索引,如果每个列都为空,索引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入). 然而如果所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
    低效: (索引失效)
    SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
    高效: (索引有效)
    SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
    (27) 总是使用索引的第一个列:
    如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引
    (28) 用UNION-ALL 替换UNION ( 如果有可能的话):
    当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是,UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量
    低效:
    SELECT ACCT_NUM, BALANCE_AMT
    FROM DEBIT_TRANSACTIONS
    WHERE TRAN_DATE = ‘31-DEC-95’
    UNION
    SELECT ACCT_NUM, BALANCE_AMT
    FROM DEBIT_TRANSACTIONS
    WHERE TRAN_DATE = ‘31-DEC-95’
    高效:
    SELECT ACCT_NUM, BALANCE_AMT
    FROM DEBIT_TRANSACTIONS
    WHERE TRAN_DATE = ‘31-DEC-95’
    UNION ALL
    SELECT ACCT_NUM, BALANCE_AMT
    FROM DEBIT_TRANSACTIONS
    WHERE TRAN_DATE = ‘31-DEC-95’
    (29) 用WHERE替代ORDER BY:
    ORDER BY 子句只在两种严格的条件下使用索引.
    ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
    ORDER BY中所有的列必须定义为非空.
    WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.
    例如:
    表DEPT包含以下列:
    DEPT_CODE PK NOT NULL
    DEPT_DESC NOT NULL
    DEPT_TYPE NULL
    低效: (索引不被使用)
    SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
    高效: (使用索引)
    SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0
    (30) 避免改变索引列的类型.:
    当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
    假设 EMPNO是一个数值类型的索引列.
    SELECT … FROM EMP WHERE EMPNO = ‘123’
    实际上,经过ORACLE类型转换, 语句转化为:
    SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123’)
    幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
    现在,假设EMP_TYPE是一个字符类型的索引列.
    SELECT … FROM EMP WHERE EMP_TYPE = 123
    这个语句被ORACLE转换为:
    SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123
    因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型
    (31) 需要当心的WHERE子句:
    某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.
    在下面的例子里, (1)‘!=’ 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) ‘||’是字符连接函数. 就象其他函数那样, 停用了索引. (3) ‘+’是数学函数. 就象其他数学函数那样, 停用了索引. (4)相同的索引列不能互相比较,这将会启用全表扫描.
    (32) a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高.
    b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要快几倍乃至几千倍!
    (33) 避免使用耗费资源的操作:
    带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎
    执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强
    (34) 优化GROUP BY:
    提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.
    低效:
    SELECT JOB , AVG(SAL)
    FROM EMP
    GROUP JOB
    HAVING JOB = ‘PRESIDENT’
    OR JOB = ‘MANAGER’
    高效:
    SELECT JOB , AVG(SAL)
    FROM EMP
    WHERE JOB = ‘PRESIDENT’
    OR JOB = ‘MANAGER’
    GROUP JOB

    展开全文
  • sql优化sql优化sql优化sql优化
  • Sql执行计划,优化sql必备!

    万次阅读 多人点赞 2018-06-22 22:22:29
    SQL执行计划学习背景: 实际项目开发中,由于我们不知道实际查询的时候数据库里发生了什么事情,数据库软件是怎样扫描表、怎样使用索引的,因此,我们能感知到的就只有sql语句运行的时间,在数据规模不大时,查询是...
  • 定位及优化SQL语句的性能问题

    千次阅读 2018-09-05 12:29:03
    做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例,标注(1,2,3,4,5)我们要重点关注的数据 type列,连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别 key列,使用到的索引名。...
  • Oracle数据库SQL优化详解

    千次阅读 2022-01-07 16:53:15
    Oracle数据库SQL优化1. Oracle SQL优化概述2. Oracle SQL优化详解2.1 Oracle 查询阻塞2.2 Oracle 查询耗时 SQL2.3.Oracle 查看执行计划2.4.Oracle 查看收集统计信息2.5.Oracle 查询优化器 -- 改写查询语句2.6.Oracle...
  • SQL常见的优化

    2018-03-30 16:14:26
    SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL常见的优化SQL...
  • http://blog.51cto.com/xiao1ang/1900950
  • 文章目录一、考点1、查找分析查询速度慢的原因1)分析SQL查询慢的方法2)使用 show profile3)使用 show status4)使用 show processlist5)使用 explain2、优化查询过程中的数据访问1)访问数据太多导致性能下降2)...
  • 数据库性能优化SQL语句优化数据库性能优化SQL语句优化数据库性能优化SQL语句优化数据库性能优化SQL语句优化
  • SQL优化 SQL 优化

    2010-03-10 17:56:46
    SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化SQL 优化
  • Spark SQLSQL优化

    千次阅读 2022-02-22 14:43:25
    Spark SQLSQL优化 主要关注于执行性能问题 1、避免使用不必要的UDF函数 UDF:用户定义函数,可以直接在SQL语句中计算的函数,如:count、sum、avg、max、min等 2、没有指定数据分区 SQL-1: SELECT date FROM test_...
  • 优化SQL步骤

    万次阅读 2021-04-01 22:59:20
    1、查看SQL执行频率 show (global\session) status like "Com_______"; show (global\session) status like "Innodb_rows%"; 2、定位低效率执行SQL 1> 慢查询日志:通过慢查询日志定位哪些执行效率较低的SQL...
  • , 现在《收获,不止SQL优化——抓住SQL的本质》开始带你抛除烦恼,走进优化的可乐世界!, 首先教你SQL整体优化、快速优化实施、如何读懂执行计划、如何左右执行计划这四大必杀招。整这些干嘛呢?答案是,传授一个先...
  • SQL优化 SQL优化

    2010-03-06 10:22:47
    SQL优化SQL优化SQL优化SQL优化
  • SQL 优化推荐书单

    千次阅读 2018-05-26 15:11:39
    还有与之交互的软件,SQL 和 内嵌的语言 远古时期的数据库应用,只有少数科学家在上面跑批处理,瓶颈往往都是单个硬件组件,比如 CPU, 内存,IO. 大家都知道的是老式的硬盘,往往转速不够快,导致计算一直很缓慢,...
  • 在ORACLE数据库中,需要对SQL语句进行优化的话需要知道其执行计划,从而针对性的进行调整.ORACLE的执行计划的获得有几种方法,下面就来总结下 1、EXPLAIN的使用 Oracle RDBMS执行每一条SQL语句,都必须经过Oracle...
  • MySql基础知识总结(SQL优化篇)

    万次阅读 多人点赞 2021-07-10 12:26:26
    2、添加索引 3、更改索引顺序 4、去掉in 5、小结 四、双表sql优化 1、建表语句 2、左连接查询 3、小结 五、避免索引失效的一些原则 六、一些其他的优化方法 1、exist和in 2、order by 优化 七、sql顺序 -> 慢日志...
  • MySQL SQL和索引优化总结

    千次阅读 2022-03-28 16:38:17
    文章目录一、SQL语句和索引的优化二、应用优化三、MySQL Server优化1. 自适应哈希索引2. redo log3. MySQL查询缓存4. MySQL线程缓存5. 并发连接数量和超时时间 首先我们需要知道MySQL主要是从以下3个方面进行优化: ...
  • 自动SQL优化工具Tosska SQL Tuning Expert for Oracle,帮助SQL开发人员解决SQL性能问题。 官网下载https://tosska.com/tosska-sql-tuning-expert-tse-oracle-free-download/ 产品介绍...
  • PostgreSQL常用SQL优化技巧

    千次阅读 2022-01-07 16:10:06
    PostgreSQL的SQL优化技巧其实和大多数使用CBO优化器的数据库类似,因此一些常用的SQL优化改写技巧在PostgreSQL也是能够使用的。当然也会有一些不同的地方,今天我们来看看一些在PostgreSQL常用的SQL优化改写技巧。 1...
  • 本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下:1、EXPLAIN做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:type列,连接类型。一个...
  • MySQL执行计划详解(优化sql语句)

    千次阅读 2018-07-20 11:46:22
    执行计划,简单的来说,是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。在MySQL使用 explain 关键字来查看SQL的执行计划。如下所示:   1 2 3 4 5 //1. 查询...
  • ORACLE SQL DEVELOPER 优化SQL语句

    千次阅读 2016-11-08 14:39:05
    利用oracle sql developer 工具轻松实现SQL优化
  • sql优化常用的几种方法

    千次阅读 2022-02-16 15:31:55
    做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据: type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列,使用...
  • sql优化的几种方式

    万次阅读 多人点赞 2018-11-05 10:20:46
    一、为什么要对SQL进行优化 我们开发项目上线初期,由于业务数据量相对较少,一些SQL的执行效率对程序运行效率的影响不太明显,而开发和运维人员也无法判断SQL对程序的运行效率有多大,故很少针对SQL进行专门的优化...
  • 下面通过一个实例来介绍下 sql 优化神器 Explain,它可以辅助进行 sql 优化。 案例 存在两张表,第一张表是item表(10w+),存储了人力资源分配单据的概要信息(存储了id、company_id、sn),第二张表是detail(1000w...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 626,434
精华内容 250,573
关键字:

优化sql

友情链接: VORTEX LATTICE.zip