精华内容
下载资源
问答
  • 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应该怎么办??

    更多相关内容
  • 慢SQL优化In PostgreSQL

    2017-10-21 17:11:23
    10、Comprehensive optimization【综合优化】 11、索引的创建 12、查找需要删除的索引 13、查找重复的索引 14、查找需要维护的索引,并自定创建索引维护SQL 15、一个index size影响query plan的例子
  • 慢sql优化

    千次阅读 2020-09-17 17:35:07
    1.通过查询日志定位到具体那一条SQL 通过配置两个参数来输出查询sql: ①set global slow_query_log = on(开启查询日志) ②set global long_query_time = 1000(设置sql执行时间超过多少就打印日志,以毫秒...

    目录

     

    1.通过慢查询日志定位到具体那一条SQL

    通过配置两个参数来输出慢查询sql:
    ① set global slow_query_log = on (开启慢查询日志)
    ② set global long_query_time = 1000 (设置sql执行时间超过多少就打印日志,以毫秒为单位,所以这里是超过1秒的sql语句都会输出在slow log)
    在这里插入图片描述
    默认输出在mysql安装路径的data路径下
    在这里插入图片描述
    可以看到具体的查询消耗时间,阻塞时间,扫描行数,返回的行数等


    2. 分析慢查询的原因

    分析Explain:
         ① type:索引是否失效(是否走了预想的索引)。
         ② rows:是否扫描了额外的记录慢sql查找优化
         ③ key,possible_key:同①,结合type分析。
    这一步的分析个人觉得,主要是判断出是数据导致的慢查询(如一次查询数据过多),还是sql语句的错误攥写导致的慢查询。数据导致的就改库表结构或重构sql,sql语句导致的就看sql哪写的不好


    3.具体优化方案

    重构查询:
         ① 根据具体情况决定将sql写成一个复杂查询,还是多个简单查询(在应用程序合并结果集)。
         ② 切分操作(例如:删10万条数据拆分成10次操作)
         ③ 分解关联查询(分为单表查询,将结果集在应用程序进行关联)
         ④ 尽量让二级索引实现覆盖索引,避免回表

    大表优化:
         ① 优化sql,索引
         ② 加缓存(如Redis)
         ③ 限定访问数据的范围
         ④ 读/写分离(主库写,从库读)
         ⑤ 垂直分表(优点:缩小结构,易于维护。缺点:主键冗余等)
         ⑥ 水平分表(分表帮助小,最好分库)

    展开全文
  • 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等)
    展开全文
  • 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

    展开全文
  • MySQL慢SQL优化-如何分析性能瓶颈

    千次阅读 2017-07-26 17:26:11
    优化慢SQL首先得知道瓶颈在哪,本文主要介绍慢SQL性能瓶颈分析。本文就以前段时间参加的一个SQL优化活动为例。 mysql命令行或者一些可视化工具在sql执行时间的精度比较低,尤其是命令行只显示到10ms,所以需要打开...
  • 慢Sql语句优化思路

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

    千次阅读 2018-06-19 18:44:38
    在日常开发工作中,我们可以做一些工作达到预防 SQL 问题,比如在上线前预先用诊断工具对 SQL 进行分析。常用的工具有: mysqldumpslow mysql profile mysql explain 具体使用及分析方法在此就不赘述,网上有丰富...
  • 二、为什么要对慢SQL进行优化?三、数据库性能1. 最大数据量2. 最大并发数3. 查询耗时0.5秒4. 具体实施四、数据库表的设计1. 数据类型2. 避免空值3. text 类型优化五、索引优化1. 索引分类2. 索引优化六、SQL 优化1....
  • 有关慢uri优化,慢Sql优化

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

    千次阅读 2019-01-10 14:51:17
    昨天在测一个场景的时候,又出现慢sql,当时还邀请了dba一起分析,我们用的是阿里云的drds、rds、redis、nigix等系列产品。 解决思路是这样的,首先我们在压测的时候,先看看是否存在慢sql,如果有的话,先把它捞...
  • 要对查询进行优化,首先要搞清楚查询的原因,原因主要有三: (1)加载了不需要的数据列 (2)查询条件没有命中索引 (3)数据量太大 二、优化方案 优化也是针对这三个方向的: (1)先分析语句,看看是否...
  • 慢SQL 到底应该如何优化

    千次阅读 2020-08-18 21:30:17
    SQL 语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化,本章将详细介绍在 MySQL 中优化 SQL 语句的方法。 当面对一个有 SQL 性能问题的数据库时,我们应该从何处入手来进行系统的分析,使得能够尽快定位...
  • 互联网中慢SQL优化手段

    千次阅读 2020-12-10 20:53:58
    最近在做一个比较有意思的工作,优化系统中的慢SQL,整个过程我是很享受的,往往很多慢SQL都是系统中调用非常频繁的接口,这让我对业务更加熟悉,整个优化过程下来对系统和团队的帮助是非常大的,自己针对这次优化...
  • mysql数据库多表关联查询的慢SQL优化

    千次阅读 2021-01-20 02:41:06
    举个例子:现在porder表有 1000W数据,其他关联的表数据都很少,因为条件的限制 必须要关联3个表,正常的逻辑就是这样写,但是我们在数据库执行的时候会发现这样的SQL 非常耗时,而且此时才 limit 800 这样的SQL怎么...
  •  前几天在项目上线过程中,发现有一个页面无法正确获取数据,经排查原来是接口调用超时,而后发现是因为SQL查询长达到20多秒而导致了问题的发生。  这里,没有高深的理论或技术,只是备忘一下经历和解读一些思想...
  • 字节客增 SQL 治理体系

    千次阅读 2022-03-31 00:50:36
    项目概览背景 SQL 即执行时间超过 long_query_time 设定阈值的SQL语句,可通过 select @@long_query_time 查看数据库具体的查询阈值。另外 SQL 不仅仅包括 select 语句,也包括 delete,insert 等 DML 语句。...
  • 某气象现场一条查询语句,大数据场景下,单个机构查询耗时5分钟以上,需要分析,SQL语句如下: SELECT t.station_no , t.collect_time_stamp, t.r2020 , t.topFROM ( SELECT t1.station_no , t1.collect_time_stamp...
  • 目录一、前言二、sql语句优化三、索引优化3.1、建立并利用合理索引3.1.1、前缀索引3.1.2、全文索引3.2、避免索引失效而全表扫描【导致索引失效】四、表优化4.1、分区表----(事先预估就分区场景)4.2、分区表----(数据...
  • MS SQL Server查询优化方法 查询速度的原因很多,常见如下几种 1、没有索引或者没有用到索引(这是查询最常见的问题,是程序设计的缺陷) 2、I/O吞吐量小,形成了瓶颈效应。 3、没有创建计算列导致查询不优化。...
  • 【MySQL】如何优化慢SQL

    千次阅读 2019-02-15 13:18:25
    1、druid 是阿里巴巴开源的为监控而生的数据库连接池 我们开发项目的时候尽可能的提前发现...2、MySQL日志监控 show variables like "%query%"; slow_query_log_file 日志地址 slow_query_log ...
  • 慢SQL语句优化

    2022-04-09 17:48:39
    慢SQL语句优化一般可以按下面几步思路:开启慢查询日志,设置超过几秒为慢SQL语句,抓取慢SQL语句;通过explain查看执行计划,对慢SQL语句分析;创建索引并调整语句,再查看执行计划,对比调优结果。  ...
  • ClickHouse sql优化技巧

    2022-01-18 14:28:49
    1.使用分区 clickhouse的表,走索引和非索引效率差距很大,在使用一个表进行查询时,必须限制索引字段。避免扫描全表 确定索引分区字段,可以用show create table default.ods_user,查看本地表的建表语句,...
  • MySQL SQL慢查询优化

    2018-12-05 11:17:23
    当MySQL服务器出现异常(),首先要考虑是否因,SQL语句引起数据库,如果情况比较紧急,我们就要立刻 SHOW FULL PROCESSLIST; 去查看,但我建议大家使用-e参数,采用非交互的方式,因为这样可以使用grep等命令,...
  • MySQL通过查询日志定位执行效率较低的SQL语句,当查询日志的内容过多时,通过mysqldumpslow工具(MySQL客户端安装自带)来对查询日志进行分类汇总。  MySQL通过查询日志定位那些执行效率较低的SQL 语句,...
  • MySQL之查询sql排查及优化

    千次阅读 多人点赞 2020-11-25 11:59:38
    sql语句优化的方式: 1.尽量少 join 2.尽量少排序 3.尽量避免 select * 4.尽量少 or 5.尽量用 union all 代替 union … (优化的方式有很多,这里就不一一举例了) 当你避免这些问题的时候,为什么sql查询还是这么...
  • 随着系统的数据量逐年增加,并发量也成倍增长,SQL性能越来越成为IT系统设计和开发时首要考虑的问题之一。SQL性能问题已经逐步发展成为数据库性能的首要问题,80%的数据库性能问题都是因SQL而导致。面对日益增多的...
  • 用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。
  • 本文主要通过一下几个方面介绍:使用SQLDMV查找慢速查询、通过APM解决方案查询报告、SQLServer扩展事件、SQLAzure查询性能洞察等相关内容。本文来自博客园,由火龙果软件Anna编辑、推荐。SQLServer的一个重要功能是...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 179,115
精华内容 71,646
关键字:

慢sql优化