精华内容
下载资源
问答
  • SQL语句调优

    2013-08-12 17:02:01
    SQL Server SQL语句调优技巧
  • sql语句调优

    2020-07-16 11:00:54
    尽量将多条SQL语句压缩到一句SQL中   每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果,这个过程是非常耗时的,因此应该尽量避免过多的执行SQL语句,能够压缩到一句SQL...

    一.创建索引

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

    2.(1)在经常需要进行检索的字段上创建索引,比如要按照表字段username进行检索,那么就应该在姓名字段上创建索引,如果经常要按照员工部门和员工岗位级别进行检索,那么就应该在员工部门和员工岗位级别这两个字段上创建索引。

    (2)创建索引给检索带来的性能提升往往是巨大的,因此在发现检索速度过慢的时候应该首先想到的就是创建索引。

    (3)一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。索引并不是越多越好,索引固然可以提高相应的 select 效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。

    二.尽量将多条SQL语句压缩到一句SQL中
      每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果,这个过程是非常耗时的,因此应该尽量避免过多的执行SQL语句,能够压缩到一句SQL执行的语句就不要用多条来执行。

    三.使用表的别名
      当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减 少哪些友列名歧义引起的语法错误。

    四.用varchar/nvarchar 代替 char/nchar
      可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。 不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

    五.查询select语句优化
    1.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段
    2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,

    select id from t where num is null 
    
    可以在num上设置默认值0,确保表中num列没有null值, 
    然后这样查询:
    
    select id from t where num=0
    select id from t where num=10 or num=20
    

    可以这样查询

    select id from t where num=10
    union all
    select id from t where num=20
    

    3.不能前置百分

    select id from t where name like ‘%abc%’
    这样会导致全表扫描
    若要提高效率,可以考虑全文检索。
    
    select id from t where num in(1,2,3)
    

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

    select id from t where num between 1 and 3 
    

    5.如果查询的两个表大小相当,那么用in和exists差别不大。

    例如:表A(小表),表B(大表)
    
    select * from A where cc in (select cc from B) 效率低,用到了A表上cc列的索引; 
    select * from A where exists(select cc from B where cc=A.cc) 效率高,用到了B表上cc列的索引。 
    相反的
    
    select * from B where cc in (select cc from A) 效率高,用到了B表上cc列的索引;
    select * from B where exists(select cc from A where cc=B.cc) 效率低,用到了A表上cc列的索引。
    总结:
    当B表的数据小于A表的数据集时,用in
    当A表的数据小于B表的数据集时,用exists(对比以上两个效率高的看)
    

    六.更新Update语句优化
      如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志
    七.删除Delete语句优化语句

    最高效的删除重复记录方法 ( 因为使用了ROWID,oracle)例子:
    
    delete from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no);
    

    八.插入Insert语句优化
      在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

    九.选择合适的字段属性
      一般来说,数据库中的表越小,在它上面执行的查询也就越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度舍得尽可能小。
      例如:在定义邮政编码这个字段时,如果将其设置为char(255),显然给数据库增加了不必要的空间,甚至使用varchar这种类型也是多余的,因为char(6)就可以很好地完成了任务。同样的如果可以的话,我们应该是用MEDIUMINT而不是BIGINT来定义整形字段。

    十.尽量把字段设置为NOT NULL
      在可能的情况下,尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。对于某些文本字段来说,例如“省份”或者“性别”,我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中,ENUM类型被当做数值型数据来处理,而数值型数据被处理起来的速度要比文本类型要快得多。这样我们又可以提高数据库的性能。

    展开全文
  • MySQL SQL语句调优

    2021-03-06 19:54:33
    SQL语句调优 文章目录SQL语句调优一、大批量插入数据1. 主键顺序插入2. 关闭唯一性校验3. 手动提交事务二、优化insert语句三、优化order by语句1. 环境准备2. 两种排序方式3. 多字段排序的优化4. filesort的优化四、...

    SQL语句调优

    一、大批量插入数据

    大批量数据存在本地文件中,使用load命令加载

    对于 InnoDB 类型的表,有以下几种方式可以提高导入的效率:

    1. 主键顺序插入

    因为InnoDB类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率

    提前创建好的脚本文件介绍 :
    	sql1.log  ----> 主键有序
    	sql2.log  ----> 主键无序
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2gJzaSDB-1615031649954)(图片/4. SQL语句调优/image-20210306181026993.png)]

    • 插入主键有序文件

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-plz9UWaj-1615031649959)(图片/4. SQL语句调优/image-20210306181216903.png)]

    • 插入主键无序文件

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6OH5KqTo-1615031649962)(图片/4. SQL语句调优/image-20210306181243659.png)]

    2. 关闭唯一性校验

    如果在创建表的时候创建了唯一索引,添加数据的时候会检查是否唯一

    在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,可以提高导入的效率,在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性校验

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OlsXeEhZ-1615031649966)(图片/4. SQL语句调优/image-20210306181522803.png)]

    3. 手动提交事务

    在导入前执行 SET AUTOCOMMIT=0,关闭自动提交,可以提高导入的效率,导入结束后再执行 SET AUTOCOMMIT=1,打开自动提交

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yTTaI94e-1615031649968)(图片/4. SQL语句调优/image-20210306181709562.png)]

    二、优化insert语句

    当进行数据的insert操作的时候,可以考虑采用以下几种优化方案

    • 如果需要同时对一张表插入很多行数据时,应该尽量使用一条insert语句,这种方式可以缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

      示例, 原始方式为:

      insert into tb_test values(1,'Tom');
      insert into tb_test values(2,'Cat');
      insert into tb_test values(3,'Jerry');
      

      优化后的方案为 :

      insert into tb_test values(1,'Tom'),(2,'Cat')(3,'Jerry'); --只需要连接数据库一次
      
    • 在事务中进行数据插入

      start transaction;
      insert into tb_test values(1,'Tom');
      insert into tb_test values(2,'Cat');
      insert into tb_test values(3,'Jerry');
      commit;
      
    • 按照主键的顺序插入

      insert into tb_test values(4,'Tim');
      insert into tb_test values(1,'Tom');
      insert into tb_test values(3,'Jerry');
      insert into tb_test values(5,'Rose');
      insert into tb_test values(2,'Cat');
      

      优化后

      insert into tb_test values(1,'Tom');
      insert into tb_test values(2,'Cat');
      insert into tb_test values(3,'Jerry');
      insert into tb_test values(4,'Tim');
      insert into tb_test values(5,'Rose');
      

    三、优化order by语句

    1. 环境准备

    CREATE TABLE `emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) NOT NULL,
      `age` int(3) NOT NULL,
      `salary` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
    
    --插入数据
    
    --创建索引
    create index idx_emp_age_salary on emp(age,salary);
    

    2. 两种排序方式

    • filesort 排序(在explain中的extra中可以看到),所有不是通过索引直接返回排序结果的排序都叫 fileSort 排序,效率低

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SFygG6XS-1615031649970)(图片/4. SQL语句调优/image-20210306182350566.png)]

    • index排序,通过有序索引顺序扫描直接返回有序数据,不需要额外排序,操作效率高

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lQBEnKvA-1615031649973)(图片/4. SQL语句调优/image-20210306182818933.png)]

    了解了MySQL的排序方式,优化目标就清晰了:尽量减少额外的排序,通过索引直接返回有序数据

    3. 多字段排序的优化

    • 对于多字段的排序,尽量排序方向一致

    • 即使多字段排序方向一致,也要保证排序字段和索引字段顺序位置相对应

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WFw9cM5z-1615031649974)(图片/4. SQL语句调优/image-20210306183058292.png)]

    4. filesort的优化

    对于filesort , MySQL 有两种排序算法:

    • 两次扫描算法 :MySQL4.1 之前,使用该方式排序。首先根据条件取出排序字段和行指针信息,然后在排序区 sort buffer 中排序,如果 sort_buffer_size 不够,则在临时表 temporary table 中存储排序结果。完成排序之后,再根据行指针回表读取记录,该操作可能会导致大量随机I/O操作

    • 一次扫描算法:一次性取出满足条件的所有字段,然后在排序区 sort buffer 中排序后直接输出结果集,排序时内存开销较大,但是排序效率比两次扫描算法要高

    MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小, 来判定使用哪种排序算法,如果max_length_for_sort_data 更大,那么使用一次扫描算法;否则使用两次扫描算法

    可以适当提高 sort_buffer_sizemax_length_for_sort_data 系统变量,来增大排序区的大小,提高排序的效率

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IPtnrSei-1615031649975)(图片/4. SQL语句调优/image-20210306190031804.png)]

    四、优化group by语句

    • GROUP BY 实际上也会进行排序操作,在排序的基础上,增加了分组操作

    • 可以执行 order by null 禁止排序,仅分组,如下(此时没有使用索引):

      没有禁止的效果:

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hrrwEk7J-1615031649975)(图片/4. SQL语句调优/image-20210306191341577.png)]

      禁止排序后:

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H1d52GPN-1615031649975)(图片/4. SQL语句调优/image-20210306191747369.png)]

      从上面的例子可以看出,第一个SQL语句需要进行"filesort",而第二个SQL由于order by null 不需要进行 “filesort”, 而上文提过filesort往往非常耗费时间

    • 使用 group by 时,也可以使用索引

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eRwxjNCv-1615031649976)(图片/4. SQL语句调优/image-20210306192204404.png)]

      此例的 group by 使用索引 + 禁止排序,效率较高

    五、优化嵌套语句

    • 有些情况下,子查询可以被更高效的连接(JOIN)替代

    • 使用多表连接替换子查询

    示例:

    优化前:

    select * from t_user where id in (select user_id from user_role );
    

    优化后:

    select * from t_user u , user_role ur where u.id = ur.user_id;
    

    连接(Join)查询之所以更有效率一些 ,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作

    六、优化or条件

    • 对于包含OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引

    • 建议使用 union 替换 or

    示例:

    优化前:

    select * from emp where id = 1 or id = 10;
    

    优化后:

    select * from emp where id = 1 union select * from emp where id = 10;
    

    七、优化分页查询

    • 分页操作时会对主键排序

    • 对于 limit 2000000,10 ,此时需要MySQL排序前2000010 记录,仅仅返回2000000 - 2000010 的记录,其他记录丢弃,查询排序的代价非常大

    1. 优化思路一

    在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bPIFLTRq-1615031649977)(图片/4. SQL语句调优/image-20210306194014136.png)]

    2. 优化思路二

    该方案仅适用于主键自增且无断层的表,可以把 limit 查询转换成某个位置的查询

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Hcjnl0nX-1615031649977)(图片/4. SQL语句调优/image-20210306194140968.png)]

    八、使用SQL提示

    SQL提示就是在SQL语句中加入一些人为的指示来达到优化操作的目的

    1. USE INDEX

    在查询语句中表名的后面,添加 use index(索引名) 来提供希望MySQL去参考的索引列表,就可以让MySQL不再考虑其他可用的索引

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FV0YjD65-1615031649978)(图片/4. SQL语句调优/image-20210306194424879.png)]

    2. IGNORE INDEX

    如果想让MySQL忽略一个或者多个索引,则可以在表名后使用 ignore index(索引名)

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hvhFaf1r-1615031649979)(图片/4. SQL语句调优/image-20210306194556040.png)]

    3. FORCE INDEX

    强制MySQL使用一个特定的索引,可在表名后使用 force index(索引名)

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qIxvw1id-1615031649979)(图片/4. SQL语句调优/image-20210306194720602.png)]

    与 use index 区别:use 仅作为参考,MySQL认为全表扫描更快则不使用此索引,而 force 强制使用此索引

    展开全文
  • sql 语句调优

    2017-01-25 10:42:34
    上面的语句明显会比下面没有用索引统计的语句要快 2.使用函数提高SQL执行速度 当出现复杂的查询sql语名,可以考虑使用函数来提高速度 查询学生信息并查询学生(李明)个人信息与的数学成绩排名 如  ...

    一、执行顺序及优化细则

    1.表名顺序优化 
    (1) 基础表放下面,当两表进行关联时数据量少的表的表名放右边
    表或视图: 
    Student_info   (30000条数据)
    Description_info (30条数据)  
    select *
      from description_info di
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'      
    与    
    select *
      from student_info     si--学生信息表
          ,description_info di
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'   
    以student_info作为基础表,你会发现运行的速度会有很大的差距。
       
       
    (2) 当出现多个表时,关联表被称之为交叉表,交叉表作为基础表
    select *
      from description_info di
        ,description_info di2
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and si.school_id = di.lookup_code(+)
       and di.lookup_type(+) = 'SCHOOL_ID'

    select *
      from student_info     si--学生信息表
          ,description_info di
          ,description_info di2
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and si.school_id = di.lookup_code(+)
       and di.lookup_type(+) = 'SCHOOL_ID'
    以student_info作为基础表,你会发现运行的速度会有很大的差距,
    当基础表放在后面,这样的执行速度会明显快很多。

    2.where执行顺序
    where执行会从至下往上执行
    select *
    from student_info si --学生信息表
    where si.school_id=10 --学院ID
    and  si.system_id=100--系ID
    摆放where子句时,把能过滤大量数据的条件放在最下边

    3. is null 和is not null
    当要过滤列为空数据或不为空的数据时使用
    select *
    from student_info si --学生信息表
    where si.school_id is null(当前列中的null为少数时用is not null,否则is null)

    4.使用表别名
    当查询时出现多个表时,查询时加上别名,
    避免出现减少解析的时间字段歧义引起的语法错误。

    5. where执行速度比having快
    尽可能的使用where代替having
    select  from student_info si
    group by si.student_id
    having si.system_id!=100
      and si.school_id!=10
    (select  from student_info si
    wehre si.system_id!=100
    and si.school_id!=10
    group by si.student_id)  
      
    6.  * 号引起的执行效率
    尽量减少使用select * 来进行查询,当你查询使用*,
    数据库会进行解析并将*转换为全部列。


    二、替代优化
    1、用>=替代> 
    select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id>=10
      与
      select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id>9
      执行时>=会比>执行得要快 
      
    2、用UNION替换OR (适用于索引列) 
    select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id=10
      union 
    select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id=2  
       上面语句可有效避免全表查询
       select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id=10 
      or ui.student_id=2
      如果坚持要用OR, 可以把返回记录最少的索引列写在最前面
        
    3、用in 代替or
    select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id=10
      or ui.student_id=20
      or ui.student_id=30
      改成
      select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id in (10,20,30)
      执行会更有效率
        
    4、 Union All 与Union
    Union All重复输出两个结果集合中相同记录
    如果两个并集中数据都不一样.那么使用Union All 与Union是没有区别的,
    select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id=10
      union All
    select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id=2  
      与
      select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id=10
      union 
    select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id=2  
    但Union All会比Union要执行得快

    5、分离表和索引 
    总是将你的表和索引建立在另外的表空间内 
    决不要将这些对象存放到SYSTEM表空间里

    三、一些优化技巧


    1、计算表的记录数时

    select count(si.student_id) 
    from Student_info si(student_id为索引)

    select count(*) from Student_info si
    执行时.上面的语句明显会比下面没有用索引统计的语句要快

    2.使用函数提高SQL执行速度

    当出现复杂的查询sql语名,可以考虑使用函数来提高速度
    查询学生信息并查询学生(李明)个人信息与的数学成绩排名
    如 
    select di.description student_name
          ,(select res.order_num--排名
             from result res
            where res.student_id = di.student_id
            order by result_math) order_num
      from description_info di
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and di.description = '李明'
       
    而且我们将上面order_num排名写成一个fuction时
    create or replace package body order_num_pkg is
    function order_num(p_student_id number) return_number is
      v_return_number number;
    begin
      select res.order_num --排名
        into v_return_number
        from result res
       where res.student_id = di.student_id
       order by result_math;
      return v_return_number;
    exception
      when others then
        null;
        return null;
    end;
    end order_num_pkg;
    执行
    select di.description student_name
          ,order_num_pkg.order_num(di.student_id) order_num
      from description_info di
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and di.description = '李明'
    执行查询时的速度也会有所提高     
     
    3.减少访问数据库的次数

    执行次数的减少(当要查询出student_id=100的学生和student_id=20的学生信息时)
    select address_id
    from student_info si --学生信息表
    where si.student_id=100

    select address_id
    from student_info si --学生信息表
    where si.student_id=20
    都进行查询.这样的效率是很低的
    而进行
    (
    select si.address_id,si2.address_id
    from student_info si --学生信息表
    ,student_info si2 
    where si.student_id=100
    and si2.student_id=20 

    select decode(si.student_id,100,address_id)
       ,decode(si.student_id,20,address_id)
    from student_info si
    )
    执行速度是提高了,但可读性反而差了..
    所以这种写法个人并不太推荐

    4、用Exists(Not Exists)代替In(Not In)

       在执行当中使用Exists或者Not Exists可以高效的进行查询

    5、Exists取代Distinct取唯一值的

       取出关联表部门对员工时,这时取出员工部门时,出现多条..
    select distinct di.dept_name 
      from departments_info di --部门表
          ,user_info        ui --员工信息表
    where ui.dept_no = di.dept_no
       可以修改成
      select di.dept_name
        from departments_info di --部门表
       where  exists (select 'X'
                from user_info ui --员工信息表
               where di.dept_no = ui.dept_no)
    6、用表连接代替Exists
       通过表的关联来代替exists会使执行更有效率
    select ui.user_name
      from user_info ui--员工信息表
    where exists (select 'x '
              from departments_info di--部门表
             where di.dept_no = ui.dept_no
               and ui.dept_cat = 'IT');
    执行是比较快,但还可以使用表的连接取得更快的查询效率
       select ui.user_name
        from departments_info di
            ,user_info        ui --员工信息表
       where ui.dept_no = di.dept_no
         and ui.department_type_code = 'IT'

    代码是经测试并进行优化所写,
    以上只例子,具体使用还是要针对各个不同的具体的业务使用用Exists(Not Exists)代替In(Not In)

    四、索引篇

    1、运算导致的索引失效

    select di.description student_name
          ,(select res.order_num--排名
             from result res
            where res.student_id = di.student_id
            order by result_math) order_num
      from description_info di
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and si.student_id+0=100/*student_id索引将失效*/ 
       
    2、类型转换导致的索引失效

    select di.description student_name
          ,(select res.order_num--排名
             from result res
            where res.student_id = di.student_id
            order by result_math) order_num
      from description_info di
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and di.student_id='100'
      
    student_id为number类型的索引,当执行下列语句,
    oracle会自动转换成
    select di.description student_name
          ,(select res.order_num--排名
             from result res
            where res.student_id = di.student_id
            order by result_math) order_num
      from description_info di
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and di.student_id=to_number('100')
    所幸,只是解析并转换类型,并没有导到失效,
    但要是写成下面,将会使用其失效
    select di.description student_name
          ,(select res.order_num--排名
             from result res
            where res.student_id = di.student_id
            order by result_math) order_num
      from description_info di
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and to_char(di.student_id)='100'
       
    3、在索引列上进行计算引起的问题

    select di.description student_name
          ,(select res.order_num--排名
             from result res
            where res.student_id = di.student_id
            order by result_math) order_num
      from description_info di
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and di.student_id-2=10
    在索引列中进行运算,将会不使用索引而使用全表扫描
    而将
    select di.description student_name
          ,(select res.order_num--排名
             from result res
            where res.student_id = di.student_id
            order by result_math) order_num
      from description_info di
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and di.student_id=10+2
    将会得到高效的运行速度

    4、 Is not null引起的问题(student_id为索引)

    不要把存在空值的列做为索引,否则无法使用索引
    select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id is not null--索引失效 
      
    select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id>=-1--索引有效

    5、Order by导致索引失效(student_id为索引)

    select ui.user_name
      from user_info ui--员工信息表
      group by ui.student_id   
    而使用
    select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id>=-1
      将使其有效,
      在order by中只存在两种条件下可以使用索引
      (ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序
    ORDER BY中所有的列必须定义为非空. )
      
    6、自动选择索引
     
    如果表中有两个以上(包括两个)索引,其中有一个唯一性索引,而其他是非唯一性.
    在这种情况下,ORACLE将使用唯一性索引而完全忽略非唯一性索引.

    7、 !=导致索引失效
     
    select ui.user_name
      from user_info ui--员工信息表
      where ui.student_id!=0
    在Where中使用!=将会把索引失效

    8、%导致的索引失效

    select di.description student_name
          ,(select res.order_num--排名
             from result res
            where res.student_id = di.student_id
            order by result_math) order_num
      from description_info di
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and di.look_code Like '%12'/*look_code为索引,索引将失效*/ 

    select di.description student_name
          ,(select res.order_num--排名
             from result res
            where res.student_id = di.student_id
            order by result_math) order_num
      from description_info di
          ,student_info     si --学生信息表
    where si.student_id = di.lookup_code(+)
       and di.lookup_type(+) = 'STUDENT_ID'
       and di.look_code Like '12%'/*索引有效*/ 
    以上只例子,具体还是要针对各个不同的具体的业务使用

    五、oracle 中的not Exists与Not in的性能巨大差异

    Not Exists与Not in的作用同样是排除数据,在oracle 中使用not in并不象mysql中的执行那么快,如(
    select jt1.doc_num --单据号码
          ,oalc.description school_name --学校名称
          ,oalc2.description system_name --系名称
          ,oalc.description class_name --班级名称
      from java_table1            jt1
          ,java_table_description oalc
          ,java_table_description oalc2
          ,java_table_description oalc3
    where oalc.lookup_type(+) = 'JAVA_SCHOOL_NAME'
       and jt1.school_id = oalc.lookup_code(+)
       and oalc2.lookup_type(+) = 'JAVA_SYSTEM_NAME'
       and jt1.system_id = oalc2.lookup_code(+)
       and oalc3.lookup_type(+) = 'JAVA_CLASS_NAME'
       and jt1.class_id = oalc3.lookup_code(+)
       and not exists
    (select jt2.header_id
              from java_table2 jt2 jt1.header_id = jt2.header_id))

    select jt1.doc_num --单据号码
          ,oalc.description school_name --学校名称
          ,oalc2.description system_name --系名称
          ,oalc.description class_name --班级名称
      from java_table1            jt1
          ,java_table_description oalc
          ,java_table_description oalc2
          ,java_table_description oalc3
    where oalc.lookup_type(+) = 'JAVA_SCHOOL_NAME'
       and jt1.school_id = oalc.lookup_code(+)
       and oalc2.lookup_type(+) = 'JAVA_SYSTEM_NAME'
       and jt1.system_id = oalc2.lookup_code(+)
       and oalc3.lookup_type(+) = 'JAVA_CLASS_NAME'
       and jt1.class_id = oalc3.lookup_code(+)
       and jt1.header_id not in (select jt2.header_id from java_table2 jt2)

    当jt2表中的数据比较大时,就会出现巨大的差异,以上只能是我的个人理解与测试结果(java_table1 视图测试

    数据量为36749,java_table2 为300条),如有其它可相互讨论

    展开全文
  • SQL语句调优心得

    2013-03-29 22:21:02
    SQL语句的调优在性能优化工作中至关重要,甚至有人说80%以上的数据库性能问题会归结到SQL语句调优。SQL语句的编写形式千变万化,DB2进行的语句内部转换扑朔迷离,语句的执行方式也纷繁复杂。因此,许多刚开始接触SQL...
    SQL语句的调优在性能优化工作中至关重要,甚至有人说80%以上的数据库性能问题会归结到SQL语句调优。SQL语句的编写形式千变万化,DB2进行的语句内部转换扑朔迷离,语句的执行方式也纷繁复杂。因此,许多刚开始接触SQL语句调优的朋友会感到不得要领,疲于应付,面对层出不穷的复杂情况望洋兴叹。

    其实,SQL优化“万变不离其宗”。所谓“变”,指的是SQL语言高度灵活,DB2处理SQL时产生的优化结果会随着各种因素的变化而变。所谓“宗”,指的是DB2编译和运行时,分析、转换、优化、执行SQL的机制。在调优SQL时,不应把DB2当成“黑盒”,停留在简单应用上,而是应该通过学习和实践,逐渐掌握DB2处理SQL的规律。例如SQL语句形式、统计信息、外部参数对优化器的影响、查询重写规则、各种计划节点的实际含义等。随着对这个“宗”的深入理解,你将能够从DB2的角度看待SQL,而不是相反。还有个建议:不妨多阅读和分析实际的查询重写结果和查询访问计划,理解什么才是期望的查询重写和访问计划行为,找出不符合期望结果的地方,从而能顺利找到问题根源。
     
    SQL是一种“聪明”的数据库查询与程序设计语言,对于SQL的应用,需要从一开始就考虑数据库应用的性能问题。使用SQL语句,用户不需要指定查询的具体执行方案,而只需要描述查询结果集。同一查询结果集会有多种SQL语句描述形式。不过,规范的SQL语句形式比起其他形式更容易被DB2优化。虽然DB2优化器有很强的查询重写能力,能够将用户输入的SQL语句转换为优化形式,但并不总是可以把各种形式的SQL语句重写成最优的形式。所以,我们在编写SQL时,要遵循必要的规范,尽量避免性能问题的出现。
     
    优化器是用来产生访问计划(Access Plan)的组件。一个SQL要以最优性能运行,有赖于优化器产生最优的访问计划。SQL语句编写规范,也是为了有利于优化器产生最优的访问计划而提出的。下面先来了解一下SQL语句在DB2中的编译过程。
    SQL编译器很像一个程序语言编译器,不过它输出的是可以被DB2运行时(Runtime)执行的代码段(Section)。SQL编译器将非结构化的SQL语句代码转换成经过优化的可执行代码,而优化器的工作则是在这个过程中选择最优的访问计划。编译器内部用QGM(Query Graph Model)来表示SQL。QGM是一种功能强大的可以表示各种形式SQL语义的图结构。
    我们看看编译SQL的各个步骤:
      语法分析(Parse Query)。编译器验证SQL的语法时,如果检测到任何语法错误,就会停止处理,返回相应的错误提示给用户,经过语法分析后,SQL就被转成QGM形式。
      检查语义(Check Semantics)。在这个步骤中,编译器确保查询语句的各部分没有不一致的地方。比如,编译器会检查一个传给函数YEAR的列是否是日期时间类型。编译器还会把参照完整性约束、触发器和视图等对象加到QGM中。
     
      重写查询(Rewrite Query)。编译器在不改变语义的基础上,对查询进行有利于优化的重写,并对QGM形式的SQL进行变换。
      下推分析(Pushdown Analysis)。处理联邦查询(Federated Query)时才会有这个步骤。其主要任务是告诉优化器查询中的操作是否可以发到远程数据源执行。
      优化访问计划(Optimize Access Plan)。在这一步,查询优化器分析QGM,生成各种访问计划。同时用表、索引和列上的统计信息,估计计划的代价。然后选择代价最小的计划作为最终的访问计划。这个访问计划可以使用解释工具(Explain Tool)查看。
      远程SQL生成(Remote SQL Generation)。处理联邦查询时才会有这个步骤。联邦查询的访问计划中,会含有对远处数据表的访问。这里编译器会根据远程表的访问计划,生成用于发到远程的SQL语句。
      生成可执行代码(Generate Executable Code)。在这最后一步,编译器会根据访问计划和QGM生成一个可执行的代码段(Section)。
    “重写查询”这一步是将SQL语句转换为有利于优化器生成最优计划的形式,可以称为逻辑优化过程。而“优化访问计划”这一步可以称为物理优化过程。查询优化器会评估各种可能的访问计划,包括操作(连接、应用谓词和聚集)的顺序和实现方法(表扫描、索引扫描和各种连接方法)。优化器还会根据代价模型计算每个计划的代价,包括中间结果的大小、CPU、I/O和内存等代价。
    影响访问计划的主要因素有:CPU的速度(即数据库管理器的CPUSPEED设置)、存储设备的反应和传输速度(即表空间的OVERHEAD 和TRANSFERRATE设置)、数据库工作的并行模式(数据库管理器的INTRA_PARALLEL 和MAX_QUERYDEGREE)设置、并发环境(数据库AVG_APPLS设置、隔离级别以及可用锁数量)、带宽(数据库管理器COMM_BANDWIDTH设置,在分区间并行时有效)、优化级别、内存设置、统计信息等。这些因素里面,优化级别、内存设置和统计信息等几个因素是可调节的,而且对于SQL调优也是最重要的。

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25714550/viewspace-757443/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/25714550/viewspace-757443/

    展开全文
  • 文件在:E:\学习文档子目录压缩\数据库\mysql\mysql优化\蚂蚁\蚂蚁1\3mysql优化之SQL语句调优 或 我的网盘\我的笔记\学习文档子目录压缩\数据库\mysql\mysql优化\蚂蚁\蚂蚁1\3mysql优化之SQL语句调优 SQL优化技巧 ...
  • oracle的SQL语句调优总结,Oracle语句优化53个规则详解。
  • SQL语句调优手段

    2014-04-20 11:56:19
    工作中,我个人按照下面的手段渐进SQL语句调优 1.更少 2.简化 3.索引 4.高级 更少 指删减或合并sql语句,目的是减少语句执行数量 简化 指语句本身的重写,特别关注减少子查询、去除重复表连接、简化...
  • Oracle Sql语句调优

    2013-08-02 16:46:46
    Oracle Sql语句调优   下面给出SQL优化经常遇到的情形。它们是在很多人长期积累的经验上总结出来的,这些都是一些一般性的结论,在实际工作中,还应当具体情况具体分析,并加以灵活运用。 (1) 用NOT ...
  • mysql数据库sql语句调优 、 索引设计原则: 索引列一般为where子句中的列或连接字句中的列 尽量不对基数小的列做索引,如性别列 尽可能使用短索引:如果对字符列索引尽量指定最小长度。 (short Keys are ...
  • 一则SQL语句调优的问题一则SQL语句调优的问题,该表日数据量比较大。查询速度很慢。曾经在主表上创建过索引但是实际使用过程中发现还不如不加索引。因此实在没办法了,希望各位能帮忙优化下##1. table_...
  • 2、SQL 语句调优(尽量避免全表扫描) 1)、在 select 、 where 、 order by 常涉及到的字段上建立索引。 2)、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进...
  • MySQL百万级、千万级数据多表关联SQL语句调优 本文不涉及复杂的底层数据结构,通过explain解释SQL,并根据可能出现的情况,来做具体的优化,使百万级、千万级数据表关联查询第一页结果能在2秒内完成(真实业务告警...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,015
精华内容 1,206
关键字:

sql语句调优