精华内容
下载资源
问答
  • 阿里面试真题:慢SQL优化思路
    千次阅读
    2020-01-12 16:55:10

    其实这是个常见的面试题目:

    1、知识点一:执行计划:具体参数可以参见:https://segmentfault.com/a/1190000008131735

    各列的含义如下:

    • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

    • select_type: SELECT 查询的类型.

    • table: 查询的是哪个表

    • partitions: 匹配的分区

    • type: join 类型

    • possible_keys: 此次查询中可能选用的索引

    • key: 此次查询中确切使用到的索引.

    • ref: 哪个字段或常数与 key 一起被使用

    • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.

    • filtered: 表示此查询条件所过滤的数据的百分比

    • extra: 额外的信息

    划重点的几个字段:

    type

    type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等.

    type 常用类型

    type 常用的取值有:

    • system: 表中只有一条数据. 这个类型是特殊的 const 类型.

    • const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
      例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的.

    • eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高. 例如:

    • ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.

    • range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
      当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.

    • index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
      index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.

    • ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.

    • 通常来说, 不同的 type 类型的性能关系如下:

    • ALL < index < range ~ index_merge < ref < eq_ref < const < system
      ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
      而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
      后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

    possible_keys

    possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

    key

    此字段是 MySQL 在当前查询时所真正使用到的索引.

    key_len

    表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
    key_len 的计算规则如下:

    • 字符串

      • char(n): n 字节长度

      • varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.

    • 数值类型:

      • TINYINT: 1字节

      • SMALLINT: 2字节

      • MEDIUMINT: 3字节

      • INT: 4字节

      • BIGINT: 8字节

    • 时间类型

      • DATE: 3字节

      • TIMESTAMP: 4字节

      • DATETIME: 8字节

    • 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

    rows

    rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
    这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

    Extra

    EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

    • Using filesort
      当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

    • Using index
      "覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

    • Using temporary
      查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

    划重点:如果sql语句优化已无法解决慢SQL应该怎么办??

    更多相关内容
  • oracle中慢sql优化思路

    千次阅读 2020-07-04 09:59:18
    参考资料:官方文档SQL Tuning Guide https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/sql-performance-fundamentals.html#GUID-DD9CAA74-3E0B-48C9-8770-AADB614BC992 Oracle Database 2...

    参考资料:官方文档SQL Tuning Guide

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/sql-performance-fundamentals.html#GUID-DD9CAA74-3E0B-48C9-8770-AADB614BC992

    Oracle Database 2 Day + Performance Tuning Guide

    Oracle Performance Tuning Guide

    如何发现慢SQL

    主动发现

    DBA和开发人员均可通过平台来发现某时间段、某数据库实例上的慢SQL信息。但平台中只能简单的查看一些执行计划以及执行过程的统计信息,需要更详细信息还是要去数据库查询,目前慢优化这块待完善。

    通过ASH&AWR去发现

    从ash查看某段时间SQL的等待总次数,CPU、IO等维度

    col type for a10 
    select * from ( 
    select 
         ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type, 
         sum(decode(ash.session_state,'ON CPU',1,0))     "CPU", 
         sum(decode(ash.session_state,'WAITING',1,0))    - 
         sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "WAIT" , 
         sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    "IO" , 
         sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL" 
    from v$active_session_history ash, 
         audit_actions aud 
    where SQL_ID is not NULL  
       and ash.sql_opcode=aud.action 
       and ash.sample_time > sysdate - &minutes /( 60*24) --最近几分钟的时间范围
     --and ash.sample_time between to_timestamp('&begin_time','yyyy-mm-dd hh24:mi:ss') and to_timestamp('&end_time','yyyy-mm-dd hh24:mi:ss') --某段时间范围
    group by sql_id, SQL_PLAN_HASH_VALUE   , aud.name 
    order by sum(decode(session_state,'ON CPU',1,1))   desc 
    ) where  rownum < 20;  --取TOP 20条等待次数最多sql

    从AWR报告查看TOP SQL

    image.png

    awr中重点关注某问题段时间一般间隔为15分钟,top sql,主要关注平均每次执行的时间以及执行sql耗用资源情况。

     

    按照某top sql维度从awr基表中批量获取慢SQL

    适合做营销活动前主动的从awr资料库里面抓取最近几天的所有慢SQL

    select dbms_lob.substr(sql_text, 100) sqla, AA.*, BB.SQL_TEXT
      from (select sql_id,
                   plan_hash_value,
                   object_name,
                   BUFFER_GETS,
                   EXECUTIONS,
                   BUFFER_GETS / decode(nvl(EXECUTIONS, 1), 0, 1, EXECUTIONS) as BUFFER_GETS_Per_Exec,
                   DISK_READS / decode(nvl(EXECUTIONS, 1), 0, 1, EXECUTIONS) as DISK_READS_Per_Exec,
                   ELAPSED_TIME / 1000000 as to_time,
                   io_wait / 1000000 as io_time,
                   round(io_wait / ELAPSED_TIME * 100) || '%' ioa_time,
                   -- round(CPU_TIME/ELAPSED_TIME*100)||'%' cpua_time,
                   row_processed / decode(nvl(EXECUTIONS, 1), 0, 1, EXECUTIONS) rows_processed_1exec,
                   ELAPSED_TIME / decode(nvl(EXECUTIONS, 1), 0, 1, EXECUTIONS) /
                   1000000 as ELAPSED_TIME_Per_Exec,
                   CPU_TIME / decode(nvl(EXECUTIONS, 1), 0, 1, EXECUTIONS) /
                   1000000 as CPU_TIME_Per_Exec
              from (select b.sql_id sql_id,
                            b.plan_hash_value,
                            o.object_name,
                            sum(nvl(b.EXECUTIONS_DELTA, 3)) as EXECUTIONS,
                            sum(nvl(b.DISK_READS_DELTA, 3)) as DISK_READS,
                            sum(nvl(b.iowait_DELTA, 3)) as io_wait,
                            sum(nvl(b.BUFFER_GETS_DELTA, 0)) as BUFFER_GETS,
                            sum(nvl(b.CPU_TIME_DELTA, 0)) as CPU_TIME,
                            sum(nvl(b.rows_processed_delta, 0)) as row_processed,
                            -- b.rows_processed_delta
                            sum(nvl(b.ELAPSED_TIME_DELTA, 0)) as ELAPSED_TIME
                       from DBA_HIST_SQLSTAT  b,
                            dba_hist_snapshot a,
                            dba_hist_sql_plan p,
                            dba_objects o
                      where /*b.sql_id in
                                                                              (select distinct (sql_id)
                                                                                 from dba_hist_active_sess_history t
                                                                                where session_id in (708,978)
                                                                                  and sql_id is not null
                                                                                  and to_char(t.sample_time, 'yyyy-mm-dd hh24-mi-ss') >=
                                                                                      '2016-05-26 21-00-00'
                                                                                  and to_char(t.sample_time, 'yyyy-mm-dd hh24-mi-ss')<=
                                                                                      '2016-05-26 23-50-00')
                                                                          and  */
                      b.snap_id = a.snap_id
                   and b.parsing_schema_name in ('CCIC', 'CCICAGT')
                   and b.instance_number = a.instance_number
                   and b.sql_id = p.sql_id
                  -- and p.options = 'FULL'
                   and p.object_name=o.object_name
                   and to_char(a.begin_interval_time, 'yyyy-mm-dd hh24-mi-ss') >=
                      '2016-06-02 09-00-00'
                   and to_char(a.end_interval_time, 'yyyy-mm-dd hh24-mi-ss') <=
                      '2016-06-02 17-40-00'
                     --and b.snap_id >= 67040
                     -- and b.snap_id <= 67050
                      group by b.sql_id, b.plan_hash_value,o.object_name)) aa,
           dba_hist_sqltext bb
     where AA.sql_id = BB.sql_id
       and BUFFER_GETS_Per_Exec > 10000
     order by -- to_time desc
              BUFFER_GETS_Per_Exec desc

    被动发现

    1、慢SQL监控告警:

    image.png

    2、开发人员主动找到DBA说有慢SQL

     

    3、数据库出现性能问题告警

    阻塞会话告警

    活跃会话数告警

    CPU、IO等告警

    分析并优化慢SQL

    开发人员反馈某个应用的SQL卡住了, 一直未返回结果

    现象:开发人员发现某业务SQL没有反应,应用接口其它SQL正常。 DBA接收到阻塞会话和活跃会话告警信息。

    一般是dba先接收到告警。这时候可以先去查看活跃会话,看看数据库当前节点在忙些啥?

    接收到的告警:

    image.png

    image.png

    同一时间开发人员反馈执行有问题的SQL

    image.png

    问题原因分析:

    造成活跃会话升高原因基本上都是被瓶颈问题阻塞了,常见的有频次高的慢SQL,应用接连不断的发送sql 但执行比较慢,累积的越来越多活跃会话。阻塞会话过多,8成是遇到锁特别是行锁。

    先看看活跃会话情况:

    set linesize 200 
    col sid format 999999 
    col s# format 9999999 
    col username format a15 
    col event format a40  
    col BLOCKING_SESSION format 999999  
    col machine format a20 
    col p123 format a30 
    col wt format 999 
    col spid format a15 
    col SQL_ID for a18 
       SELECT /* XJ LEADING(S) FIRST_ROWS */
        S.SID,
        S.SERIAL# S#,
        S.USERNAME,
        S.MACHINE,
        S.EVENT,
        S.BLOCKING_SESSION,
        S.P1 || '/' || S.P2 || '/' || S.P3 P123,
        S.WAIT_TIME WT,
        NVL(SQL_ID, S.PREV_SQL_ID) SQL_ID
         FROM V$SESSION S
        WHERE S.STATUS = 'ACTIVE' and S.TYPE <>'BACKGROUND';

    查询结果如下:

    image.png

    从活跃会话查询结果中看到,sql ba2wr7m4xcrzx的等待事件都是关于行锁的enq:Tx - row lock contention,并且阻塞者的会话是6829,阻塞源头基本断定是6829,后面看看会话6829在干啥。

    执行查询sql:

    set linesize 200 
    col sid format 999999 
    col s# format 9999999 
    col username format a15 
    col event format a40  
    col BLOCKING_SESSION format 999999  
    col machine format a20 
    col p123 format a30 
    col wt format 999 
    col spid format a15 
    col SQL_ID for a18 
    col PROGRAM for a18 
    col MODULE for a18 
    alter session set cursor_sharing=force; 
       SELECT /* XJ LEADING(S) FIRST_ROWS */
        S.inst_id,
        S.SID,
        S.SERIAL# S#,
        S.USERNAME,
        S.MACHINE,
        S.PROGRAM,
        S.MODULE,
        S.EVENT,
        S.BLOCKING_SESSION,
        S.P1 || '/' || S.P2 || '/' || S.P3 P123,
        S.WAIT_TIME WT,
        NVL(SQL_ID, S.PREV_SQL_ID) SQL_ID
         FROM gV$SESSION S
        WHERE S.TYPE <>'BACKGROUND' 
           and S.sql_id = '&sql_id'
           order by 1,2; 

    执行结果:

    image.png

    接下来在看看会话6829上sql 5haaxd3zxbqgc在跑啥?

    select sql_id,sql_fulltext from v$sql where sql_id='5haaxd3zxbqgc' and rownum=1;
    或者直接查看执行计划以及sql文本,看的信息更多一些
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(to_char('&SQL_ID'),&child_NULL,'ADVANCED'));

    image.png

    发现是阻塞者和被阻塞者都是在更新同一张表中的某些行数据,更新到相同的行就会造成行锁冲突。解决也很简单,kill掉阻塞源头就可以,但DBA这个时候要作出评估。

    1)、立马把SQL语句丢到开发沟通群,快速询问 这是阻塞源头是否可以立马kill掉,请尽快评估kill掉对业务是否有影响

    2)、多次查询活跃会话,持续关注该库上的告警信息,看活跃会话和阻塞会话是否一直在快速增加

    如果活跃会话和阻塞会话一直增加,数据库性能不可控。DBA要果断kill该阻塞源头。

    alter system kill session '6829,43685' immediate;
    或者通过sql_id生成相关kill语句
    SELECT q'[alter system kill session ']'||S.SID||','||S.SERIAL#||q'[' immediate;]' sql_text from V$SESSION S
     WHERE S.sql_id = '&sql_id' 
       AND S.STATUS = 'ACTIVE'; 

    如果数据库性能暂时可控,告知开发后果后,等待他们答复后再处理。等开发人员做好准备工作后就可以kill该会话。

     

    收尾工作:

    持续关注该库上的告警信息,同时关注因kill掉了大事物的DML语句,关注数据库回滚情况。

    alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 
     select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo", 
                decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) 
                 "Estimated time to complete" 
      from v$fast_start_transactions; 

    如果回滚事物太慢,可以考虑调整参数:

    alter system set "_rollback_segment_count" = 2000;

     

    开发人员反馈同样SQL昨天好好,今天突然变慢

    一般分这几种情况:

    1)、执行计划变了,最常见

    2)、之前绑定的执行计划,随着数据量的增长已经不合适了。

    3)、修改了数据库参数,特别是优化器相关的参数,问题sql是定时跑的,并没有立马体现出来。比较少见。

    分析思路与解决方案:

    执行计划抖动,绑定

    开发人员给出的sql往往是sql文本,并且很有可能是同一张表雷同SQL,只是有细微差异,体现在数据库中的是不同SQL_ID。这种情况不能完全相信开发人员给出的sql,一定要根据提供的信息去数据库里面再找找,把所有雷同的sql列出来。解决问题不仅要解决问题点,还要覆盖到问题面。

    核对慢sql 看平台上慢SQL,以及查v$SQL

    select sql_id,sql_fulltext from v$sql where sql_text like '%sql注释部分%'

    查看sql执行情况,对比性能好时段和变差时段执行计划变更情况

    col PLAN_HASH_VALUE for 9999999999 
    col instance_number for 9 
    col snap_id heading 'SnapId' format 999999 
    col executions_delta heading "No. of exec" 
    col date_time heading 'Date time' for a20 
    col avg_lio heading 'LIO/exec' for 999999999999 
    col avg_cputime_s heading 'CPUTIM/exec' for 99999 
    col avg_etime_s heading 'ETIME/exec' for 999999 
    col avg_pio heading 'PIO/exec' for 999999999 
    col avg_row heading 'ROWs/exec' for 9999999999 
    col sql_profile format a35 
    SELECT distinct 
    s.snap_id , 
    s.instance_number, 
    PLAN_HASH_VALUE, 
    to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time, 
    SQL.executions_delta, 
    SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio, 
    (SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime_s , 
    (SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime_s, 
    SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio, 
    SQL.rows_processed_total/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row, 
    SQL.sql_profile 
    FROM dba_hist_sqlstat SQL,dba_hist_snapshot s 
    WHERE 
    SQL.dbid =(select dbid from v$database) 
    and s.snap_id = SQL.snap_id 
    and sql.instance_number = s.instance_number 
    AND sql_id in ('&sql_id') order by s.snap_id; 

    如果结果中看出来执行计划变更了,那就要考虑把问题sql的执行计划绑定。

    使用COE脚本绑定步骤:

    脚本下载地址:https://github.com/AlbertCQY/scripts/blob/master/oracle/sql_profile_new2.sql

    脚本简单说明:原始coe脚本出自oracle MOS官方,sql_profile_new2.sql脚本是oracle官方高级售后DBA修改的增强版。可以绑定执行计划、替换执行计划。

    @sql_profile_new2.sql
    Parameter 1:
    SQL_ID (required)
    
    Enter value for 1:  --这里传入需要优化的sqlid
    Parameter 2:
    PLAN_HASH_VALUE (required)
    
    Enter value for 2:  --这里传入正确执行计划的PLAN_HASH_VALUE,可以不是Parameter 1对应sqlid的plan_hash
    
    最后在当前目录下生成一个要执行的脚本,包含sql_id和plan hash
    比如:coe_xfr_sql_profile_62159umsg6z8m_4105682492.sql
    绑定执行计划就直接执行上面生成的脚本。

    刷新sql执行计划游标:

    select PLAN_HASH_VALUE,q'[exec sys.dbms_shared_pool.purge(']'||address||','||hash_value||q'[','C');]' as flush_sql 
      from v$sqlarea where sql_id='63u74y7gdafzf';
     得到刷新语句后直接执行即可。
                                                                                                

    绑定执行计划后重新查看下sql执行计划信息,如果还是原来的执行计划则有可能是coe绑定成功了,但由于sql正在执行中 导致执行计划游标刷出失败。需要和开发沟通是否可以kill掉正在执行sql的会话,然后再去刷新即可。

     

    构造新的执行计划,解绑->绑定新的

    如果发现sql上面已经绑定了执行计划,但随着表上数据量的增长,以及业务逻辑的变更,绑定的执行计划已经不适合了,需要解绑并替换为更优的执行计划。

    构造想要的执行计划:hint提示方法

    由于业务评估失误以及数据量的不断增长,该sql在项目开始时候评估下来适合走object_id列上的索引,并且也做了执行计划的绑定。

    现在业务数据产生了变化,需要按照预定方式走object_name列上的索引idx_name

    原来sql(fvscnttfnqvkf)  select * from t_testplan where object_id=1 and object_name='test'
    Plan hash value: 2317386271
    
    ------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |            |     8 |  1656 |     2   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| T_TESTPLAN |     8 |  1656 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | IDX_ID     |    14 |       |     1   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("OBJECT_NAME"='test')
       2 - access("OBJECT_ID"=1)
    
    加hint后sql(9xtcn2g6n7gsw)  select /*+INDEX(t_testplan idx_name) */ * from t_testplan where object_id=1 and object_name='test'
    Plan hash value: 1801285354
    
    --------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |            |     7 |  3367 |    12   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_TESTPLAN |     7 |  3367 |    12   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN                  | IDX_NAME   |    16 |       |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("OBJECT_ID"=1)
       2 - access("OBJECT_NAME"='test')
    

    现在需要把fvscnttfnqvkf的执行计划替换为9xtcn2g6n7gsw的执行计划

     

    第一步:删除绑定的执行计划(解绑profile)

     

    select name from dba_sql_profiles where name like '%fvscnttfnqvkf%';
    
    BEGIN 
    DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_fvscnttfnqvkf'); 
    END; 
    /

     

    第二步:绑定执行计划

    @sql_profile_new2.sql
    Parameter 1:
    SQL_ID (required)
    
    Enter value for 1:  --这里传入需要优化的sqlid fvscnttfnqvkf
    Parameter 2:
    PLAN_HASH_VALUE (required)  
    
    Enter value for 2:  --这里传入加Hint后的9xtcn2g6n7gsw执行计划PLAN_HASH_VALUE 1801285354

    参照之前的步骤,刷新执行计划游标。

     

    开发人员反馈应用OOM告警,需要看下SQL是否有异常

    场景分析:应用server内存OOM后,开发人员在分析应用代码以及框架没问题后,一般会找DBA查找SQL的原因。

    1)、开发人员提供的SQL有很明显的全表扫描语句

    比较少见,一般添加合适的索引即可。

    2)、开发人员提供的带绑定变量的sql,并且dba提供了完整测试语句

    开发提供的sql在数据库上测试了下,性能很好,返回的结果集也很小。但真的是这样么?这时候就要怀疑是不是没有给到出现OOM时绑定变量真正的传参值。

    出现这种比较奇怪的信息不对称情况时,其实也挺好求证。查看该SQL历史执行情况,和之前的逻辑读、物理读、返回行数等对比下就知道了。如果问题时段这些指标相对高,那么八九不离十就是传参倾斜导致。

     

    新上线了功能,第二天发现一堆性能告警

    场景分析:新上线的SQL由于性能评估不到位,过段时间在业务高峰时段,逐渐暴露出来性能问题。

    常见有缺少必要的索引:DBA根据表结构以及各列的统计信息来判断,下面分享两个常用的脚本

     

    表维度,查看表上结构信息、统计信息等,tabstat.sql脚本:传入用户名+表名

    https://github.com/AlbertCQY/scripts/blob/master/oracle/tabstat.sql

    SQL维度,SQL语句所有关联的表上结构信息、统计信息等,sql10.sql脚本:传入sql_id

    https://github.com/AlbertCQY/scripts/blob/master/oracle/sql10.sql

     

    创建索引指导建议:

    适合创建索引的列

    • 索引覆盖(只select索引列)、避免排序(order by索引列)
    • 复合索引尽量兼顾更多SQL(索引具有较多的使用场景)
    • 该列在表中的唯一性特别高、有些状态列有倾斜值(符合少数)
    • 复合索引等值谓词条件字段做前导列,非等值谓词条件字段放在后面
    • 表关联使用Nested Loop 被驱动表的关联字段上建议创建索引
    • SQL语句是主流的业务,具有高并发,where条件中出现的列可以考虑创建复合索引

    不适合创建索引的列

    • DML频繁的表不适合创建索引,索引会带来额外的维护成本
    • 为了少数查询,并且频次不高的查询列上建索引(这类SQL考虑放读库执行)
    • Where条件中不会使用的列也不适合创建索引

     

     

    如何解决一条复杂的SQL

    Oracle数据库不仅对OLTP型短平快的sql支持很好,OLAP型复杂的分析SQL同样支持很好。一般来说复杂SQL执行计划特别长,甚至超过200行,关联5张以上表或视图,无法快速分析出执行计划是否有问题,甚至执行计划还经常抖动。

     

    优化思路:不管SQL写的多复杂,执行计划超级长,只需要抓住sql最影响性能的地方即可。

    借助脚本plan_ash.sql或者sql10.sql脚本可以展示出最消耗性能的部分:https://github.com/AlbertCQY/scripts/blob/master/oracle/plan_ash.sql

    比如下面这个执行计划,发现性能瓶颈在逻辑读上面,优化掉db file sequential read(2)(40%) 这一步骤的性能问题,该复杂SQL问题也就解决了。

     

    Oracle官方工具篇:

    Oracle官方提供了丰富的sql调优工具,面对复杂SQL善于使用官方提供的工具也是个不错的方法。

    Oracle真的是博大精深,学习永无止境...

    Information Center: Sql Performance Tuning: Troubleshoot (Doc ID 1516522.2)

     

    SQL Tuning Advisor:

    SQL Tuning Advisor (Doc ID 2582636.1)

    Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1)

    Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor (Doc ID 262687.1)

    Example: SQL Tuning Task Options (Doc ID 2461848.1)

    SQL Performance Analyzer Summary (Doc ID 1577290.1)

     

    SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

    NOTE:243755.1 - Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data

    NOTE:1482811.1 - Best Practices: Proactively Avoiding Database and Query Performance Issues

    NOTE:1460440.1 - Script PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports

    NOTE:1477599.1 - Best Practices: Proactive Data Collection for Performance Issues

    NOTE:224270.1 - TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (NO LONGER SUPPORTED - Use SQLTXPLAIN sqltrcanlzr.sql)

    NOTE:1627387.1 - How to Determine the SQL_ID for a SQL Statement

    NOTE:1455583.1 - SQL Tuning Health-Check Script (SQLHC) Video

    NOTE:215187.1 - All About the SQLT Diagnostic Tool

    NOTE:1417774.1 - FAQ: SQL Health Check (SQLHC) Frequently Asked Questions

     

    最后分享一个丁俊老师的一篇文章:

    https://dbaplus.cn/news-10-1314-1.html

    展开全文
  • 慢Sql语句优化思路

    2021-02-05 02:18:35
    1.开启慢查询日志,设置超过几秒为慢sql语句,抓取慢sq语句。l 2.通过explain查看执行计划,对慢sql语句分析。 3.创建索引并调整语句,再查看执行计划,对比优化结果。 抓取慢sql show variables like ‘%quer%’; ...

    思路

    1.开启慢查询日志,设置超过几秒为慢sql语句,抓取慢sq语句。l
    2.通过explain查看执行计划,对慢sql语句分析。
    3.创建索引并调整语句,再查看执行计划,对比优化结果。

    抓取慢sql

    show variables like ‘%quer%’;
    在这里插入图片描述

    分析查询语句

    在这里插入图片描述
    在这里插入图片描述

    先看type:All全表扫描,没有用到索引
    再看key:Null没有使用索引列
    然后看rows:数值越多耗时越长
    最后看Extra:避免Using temporary和Using Filesort

    1. id:选择标识符,代表执行顺序
    2. select_type:表示查询的类型。
    simple:简单的select查询,不包含联合查询和子查询
    primary:查询中包含子查询
    subquery:select 或者where中包含子查询
    derived:from中包含子查询
    union:联合查询
    union result:union后的结果集
    
    1. table:查询的表名
    2. partitions:匹配的分区
    3. type:表示表的连接类型
    All:全表扫描
    Index:只遍历索引树,索引树上就有要查询的结果,不需要IO
    Range:索引范围扫描
    Ref:非唯一性索引扫描
    Eq_ref:唯一索引扫描
    Const:通过一次索引就能查询到,通常是Primary Key或者Unique
    System:Const特例,表数据只有一行
    Null:不用访问索引就可以直接查询到结果
    
    1. Possible_key:能使用哪个索引找到数据行,单并不一定会被使用到
    2. Key:Possible_key中决定使用的索引
    3. Key_len:显示索引中使用的字节数
    4. ref:上述表的连接匹配条件,即哪些列或常量被用于查找索引上的值
    5. Rows:找到所需记录要读取的行数
    6. Extra:
    Using Where:仅通过索引就可以过滤所需数据
    Using temporary:需要使用临时表来存储结果集
    Using Filesort:order by操作无法利用索引完成导致的“文件排序”
    Using join buffer:连接使用了缓存,可以通过添加索引来解决
    Using Index:索引树中包含要查询的所有信息
    Using Index Condition:根据辅助索引过滤数据,减少Server和磁盘的IO次数
    

    索引创建原则

    表一定要有业务无关的主键
    适合添加索引的列:经常被查询、经常用于表链接,经常排序或者分组
    索引列尽量都是不重复的数据
    组合索引一般不超过5列,选择性高的放在前面
    合理利用索引覆盖,禁止select *
    explain 判断sql是否合理利用索引
    单表索引控制在5个以内
    不建议在频繁更新的字段上添加索引
    where条件中的索引列不能是表达式的一部分,避免对索引列进行函数计算
    join类型的字段必须类型一致且都建立索引

    索引失效

    隐式的类型转换会导致索引失效,导致全表扫描
    对索引列进行函数或者数学计算,例如日期格式化
    模糊匹配未使用前缀匹配
    使用了负方向查询,not,!=,not in等

    Sql规范

    按需查询避免 select *
    无法使用覆盖索引,回表,增加IO
    多查询的列,会有多余的IO和网络开销
    避免大事务,将大事务拆成小事务。防止出现锁阻塞,导致的雪崩效应
    少用多表join,禁止大表join,小表驱动大表,join列必须字符集一致,且有索引
    尽量避免多层子查询嵌套
    定期对慢sql优化

    展开全文
  • MySQL的慢sql优化

    2021-11-11 21:09:59
    针对慢sql优化思路整理 一、开启慢查询 开启慢查询有两种方式: 1⃣️修改配置文件 windows下:配置文件为my.ini,一般在mysql的安装目录下,或c:\\windows下 linux下:配置文件为my.cnf,一般在/etc下 在配置...

    针对慢sql优化思路整理,一共可以分为以下三步

    一、开启慢查询

    二、通过explain关键字进行sql语句的分析

    三、具体的优化步骤

    1、首先要避免全表扫描,检查是否有索引

    2、查看索引是否生效(以下是索引失效的部分场景)

    3、sql结构的优化

    4、数据库表设计的优化

    一、开启慢查询

    开启慢查询有两种方式:

    1⃣️修改配置文件

    windows下:配置文件为my.ini,一般在mysql的安装目录下,或c:\\windows下

    linux下:配置文件为my.cnf,一般在/etc下

    在配置文件的[mysqld]下加入如下语句

    [mysqld]
    slow_query_log = ON
    slow_query_log_file = /usr/local/mysql/data/slow.log
    long_query_time = 1

    2⃣️通过数据库开启慢查询

    修改配置文件需要重启mysql sever进程mysqld才会生效,线上环境可以通过指令来开启慢查询

    set global slow_query_log = 'ON':设置参数为ON,开始慢查询
    set global slow_query_log_file = '/slowlog/log.log':记录超时的语句的文件夹
    set global long_query_time = 2:超过该时间的语句会被记录到日志中
    
    

    二、通过explain关键字进行分析

    其中比较重要的几个参数

    1、possible_keys:可能使用到的索引
    2、key:实际上使用到的索引
    3、type:查询使用的类型
    4、rows:查询到需要的数据,扫描了多少行
    

    对于type,从坏到好all < index < range < ref < eq_ref < const

    一般保证达到range,ref最好(const写死一个常量、eq_ref针对主键或唯一键查询结果唯一、ref针对非唯一键查询多个、range索引范围查询、index全索引扫描、all全表扫描)

    三、具体的优化步骤

    1、首先要避免全表扫描,检查是否有索引

    • 没有索引考虑是否建立索引(可以在经常需要查询的列上、需要排序的列上添加索引)

    2、查看索引是否生效(以下是索引失效的部分场景)

    • 不满足最左匹配原则
    • like以%开头或者全模糊
    • >、<号右边的索引会失效
    • or也会导致索引失效(在or的两边都加索引才不会失效或者使用union(去重)、union all(不去重)替代or)
    • !=、<>也会导致索引失效
    • 在where子句中使用表达式操作、函数操作等

    3、sql语句结构的优化

    • 避免使用select*,不要返回不必要的字段,增加索引覆盖的概率
    • 尽量减少子查询(子查询会创建临时表,查询完毕删除临时表)
    • 调整where子句的连接顺序,将过滤数据多的条件放在前面,最快速度缩小结果集
    • 尽量减少联表查询,联表查询是笛卡尔乘积的形式,检索的数据几何倍上升
    • 一次查询的结果最好不要过大,可以使用分页查询

    4、数据库表设计的优化

    • 单表的字段最好不要超过20个。如果是比较大的表,有的字段使用的多,有的字段使用比较少,使用频率比较少的会拖慢查询速度
    • 表的结构是否合理(范式、反范式需要结合实际场景来决定)
    • 列类型的选择是否合适(能用int却用了bigint等)
    展开全文
  • 慢sql优化

    千次阅读 2020-09-17 17:35:07
    1.通过查询日志定位到具体那一条SQL 通过配置两个参数来输出查询sql: ①set global slow_query_log = on(开启查询日志) ②set global long_query_time = 1000(设置sql执行时间超过多少就打印日志,以毫秒...
  • 通过查日志获取存在性能问题的SQL 实时获取存在性能问题的SQL 2查询日志介绍 slow_query_log 启动停止记录查日志 slow_query_log_file 指定查日志的存储路径及文件 long_query_time 指定记录查日志...
  • hiveSQL优化思路

    千次阅读 2018-04-12 17:45:36
    存储的优化思想就是查询数据时能够很快定位到需要的数据,通过索引技术或者缩小检索数据范围来解决;传统数据库领域通过采用索引技术来优化数据的存储达到高效检索访问,在hive数仓技术中也有索引技术,但是,最常用...
  • SQL优化思路

    2021-05-08 15:39:00
    1、通过查询日志定位执行效率较低的SQL 2、explain分析SQL的执行计划,重点关注type、rows、filtered、extra type由上到下,效率越来越高 ALL全表扫描 index索引全扫描 range索引范围扫描,<,<=,>,>=,...
  • SQL优化的基本思路

    2022-03-29 08:23:36
    (1)建立物化视图或尽可能减少多表查询。... (9)的查询的sql,根据性能和存储容量大小进行评估,适当的可以考虑水平分表和垂直分表,以提高sql的查询性能。 (10)查询数据是否存在,适当的可以使用exists替代in。
  • 谈谈SQL慢查询的解决思路

    千次阅读 2019-02-14 14:22:19
    为了让我们在SQL的处理上更为合理,形成可实践、可借鉴、可参考优化的方案,我在这里梳理一下慢SQL的解决思路,供大家参考。 慢SQL的系统表现 首先,我们如何判别系统中遇到了SQL慢查询问题?个人认为慢SQL有如下...
  • 订阅每日慢日志,优先解决调用次数多的慢sql,因慢sql优化的知识点非常多,只列举几个容易忽视的地方。 注意: 1、数据量不同,查询条件不同,sql使用的索引可能是不一样的,要构造多种查询条件去测试。 2、避免...
  • 别急,这是教你如何解决问题,准确地说,是如何不改写即完成SQL优化。 随后本书指引大家学会等价改写、过程包优化、高级SQL、分析函数、需求优化这些相关的五大神功。有点头晕,能否少一点套路?淡定,这还是“术”...
  • 慢SQL语句优化

    2022-04-09 17:48:39
    慢SQL语句优化一般可以按下面几步思路:开启慢查询日志,设置超过几秒为慢SQL语句,抓取慢SQL语句;通过explain查看执行计划,对慢SQL语句分析;创建索引并调整语句,再查看执行计划,对比调优结果。  ...
  • SQL Server多表查询的优化方案是本文我们主要要介绍的内容,本文我们给出了优化方案和具体的优化实例,接下来就让我们一起来了解一下这部分内容。 1.执行路径 ORACLE的这个功能大大地提高了SQL的执行性能并节省了...
  • SQL语句执行的过程、ORACLE优化器 ,表之间的关联,如何得到SQL执行计划,如何分析执 行计划等内容,从而由浅到深的方式了解SQL优化的过 程,使大家逐步掌握SQL优化
  • 要正确的优化SQL,必须能快速定位性能瓶颈点,或者说快速找到SQL主要的开销所在。的设备通常是瓶颈点的成因,如文件下载时的瓶颈点可能是网络速度,本地文件复制时的瓶颈点可能在于硬盘性能。  为了快速找到SQL...
  • 常见Mysql的查询优化方式

    万次阅读 多人点赞 2018-09-21 16:37:02
    这篇文章主要是就在公司实习的时候,对SQL优化工作作出的一些整理。  在公司实习的时候,导师分配了SQL查询优化的任务,任务是这样的:每周从平台中导出生产数据库的查询文件进行分析。进行SQL优化的手段也...
  • SQL优化思路大全

    千次阅读 2017-07-19 11:20:00
    一、百万级数据库优化方案1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表...
  • sql优化的几种思路

    千次阅读 2018-07-24 18:52:11
    很多时候我们的Sql比较,或者就是错误无用的写法,不知道为什么,我个人总计和网上抄袭汇总了一些,另外最下面会吧我们公司的sql规约共享一份给大家!请大家多讨论分享! 你要先知道sql的执行顺序,然后会用工具...
  • 有关慢uri优化,慢Sql优化

    千次阅读 2021-02-02 22:40:50
    最近对sql优化,还有uri接口优化,遇到一些比较有意思的事情,这记录一下 最近接手APP产品,被用户吐槽蛮多的,主要是入库和出库的流程比较,查询。 看了一下同事写的代码,果然,复杂条件的查询写不出来就...
  • Mysql统计sql优化思路

    千次阅读 2019-08-15 15:36:28
    -----日常记录一个解决自己在开发中遇到的sql统计查询的问题 建立字段索引 对数据冗多的副表,进行关联关系片区化(先对附表进行数据筛选再关联主表) 优化查询条件 1、字段索引的好处就不说了,各位都应该懂,...
  • MySQL监控与慢SQL解决思路

    千次阅读 2020-06-11 00:09:41
    实时监控语句耗时与SQL状态 使用语句show full processlist; 此命令有权限,自己的账号只能看自己的连接,root账号可以看全部连接。 字段值说明 Id整数连接和会话的唯一ID User字符串哪个用户使用了此连接或者...
  • 我一般优化思路(1、检查SQL语法是否有问题(比如用了子查询,或者说大表连小表) 2、是否建立了索引 3、是否索引失效导致全表扫描,4、检查mysql服务磁器盘cpu、5、数据量太大是否需要分库分表) 首先看SQL语句
  • 慢sql优化必看

    2020-10-21 18:17:17
    慢sql的危害 1.增加方法调用链执行耗时,影响调用链性能,影响用户体验 2.占据数据库连接,数据库连接池占满还有可能导致其他涉及sql操作的功能全部等待。无法正常使用 一、一条sql在mysql当中执行的旅途 这里引用...
  • 结合 SQL与执行计划,进行分析与优化​ 引起 SQL 查询很的原因与解决办法: 1、没有索引。解决办法: 根据 where 和 order by 使用比较频繁的字段创建索引,提高查询效率 索引不宜过多,单表最好不要超过 6 ...
  • 小谈 MySQL 第一话· SQL 优化

    千次阅读 2019-04-16 16:53:53
    1、慢SQL可能会消耗了70%~90%的数据库CPU资源 2、SQL语句独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低 3、SQL语句可以有不同的写法 一、MySQLEXPLAIN 详解 ...
  • 随后《收获,不止SQL优化——抓住SQL的本质》指引大家学会等价改写、过程包优化、高级SQL、分析函数、需求优化这些相关的五大神功。有点头晕,能否少一点套路?淡定,这还是“术”的范畴,依然是教你如何解决问题,...
  • 慢SQL治理方法论

    2022-03-16 11:12:47
    本文介绍了笔者实际工作中慢SQL治理的方法论,1、发现:如何发现慢SQL。2、定位:如何定位到慢SQL写在哪。3、分析:遇到慢SQL时的分析思路。4、解决:慢SQL的解决思路
  • 记一次后台订单列表检索慢SQL优化实践一、背景介绍二、设计思路如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 32,991
精华内容 13,196
关键字:

慢sql优化思路