精华内容
下载资源
问答
  • 使用profile来分析慢sql mysql 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项资源的使用情况。分析器可以更好的展示出不良 SQL 的性能问题所在。 最近遇到一个查询比较慢的sql语句,用了子查询,大概...
  • 慢SQL优化In PostgreSQL

    2017-10-21 17:11:23
    1、Create Index Directly 2、Change Conditions to Use Index 3、尽量避免在where子句中对字段进行运算,导致查询规划器...14、查找需要维护的索引,并自定创建索引维护SQL 15、一个index size影响query plan的例子
  • 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

    展开全文
  • jdbc日志慢SQL过滤工具

    2018-04-26 16:04:41
    jdbc打印sql日志,如果每条sql都有执行时间,可以使用这个小工具进行慢sql的过滤,做性能测试的童鞋可以试一下
  • MySQL监控与慢SQL解决思路

    千次阅读 2020-06-11 00:09:41
    实时监控语句耗时与SQL状态 使用语句show full processlist; 此命令有权限,自己的账号只能看自己的连接,root账号可以看全部连接。 字段值说明 Id整数连接和会话的唯一ID User字符串哪个用户使用了此连接或者...

    实时监控语句耗时与SQL状态

    使用语句show full processlist; 此命令有权限,自己的账号只能看自己的连接,root账号可以看全部连接。

    alt
    字段说明
    Id整数连接和会话的唯一ID
    User字符串哪个用户使用了此连接或者会话
    Host字符串由那个ip的客户端连接到服务的
    db字符串数据库名称
    Command字符串连接执行命令状态,例如:一般就是休眠(sleep),查询(query),连接(connect)
    Time整数空闲或者执行语句时间,如果是空闲,这个时间代表空闲了多久。如果是执行,那就是这个sql执行所经过的时间(sql执行的太久属于不正常现象)
    State字符串详细见下方state字段描述
    Info字符串如果是执行就显示正在执行的sql语句。因为长度有限,所以长的sql语句就显示不全,但是一个判断问题语句的重要依据。

    state字段描述

    显示使用当前连接的sql语句的状态,很重要的列,可用来判断mysql的运行状态。 这个命令中最关键的就是state列,mysql列出的状态主要有以下几种:

    Checking table

     正在检查数据表(这是自动的)。

    Closing tables

     正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。

    Connect Out

     复制从服务器正在连接主服务器。

    Copying to tmp table on disk

     由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。

    Creating tmp table

     正在创建临时表以存放部分查询结果。

    deleting from main table

     服务器正在执行多表删除中的第一部分,刚删除第一个表。

    deleting from reference tables

     服务器正在执行多表删除中的第二部分,正在删除其他表的记录。

    Flushing tables

     正在执行FLUSH TABLES,等待其他线程关闭数据表。

    Killed

     发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。

    Locked

     被其他查询锁住了。

    Sending data

     正在处理Select查询的记录,同时正在把结果发送给客户端。

    Sorting for group

     正在为GROUP BY做排序。

    Sorting for order

     正在为ORDER BY做排序。

    Opening tables

     这个过程应该会很快,除非受到其他因素的干扰。例如,在执Alter TABLE或LOCK TABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。

    Removing duplicates

     正在执行一个Select DISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。

    Reopen table

     获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。

    Repair by sorting

     修复指令正在排序以创建索引。

    Repair with keycache

     修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。

    Searching rows for update

     正在讲符合条件的记录找出来以备更新。它必须在Update要修改相关的记录之前就完成了。

    Sleeping

     正在等待客户端发送新请求.

    System lock

     正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表,那么可以通过增加--skip-external-locking参数来禁止外部系统锁。

    Upgrading lock Insert DELAYED

    正在尝试取得一个锁表以插入新记录。

    Updating

     正在搜索匹配的记录,并且修改它们。

    User Lock

     正在等待GET_LOCK()。

    Waiting for tables

     该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知:FLUSH TABLES tbl_name, Alter TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。 waiting for handler insert

    Insert DELAYED

    已经处理完了所有待处理的插入操作,正在等待新的请求。  大部分状态对应很快的操作,只要有一个线程保持同一个状态好几秒钟,那么可能是有问题发生了,需要检查一下。

    杀死连接

    语法为kill id; 例:kill 62532 使用方式是使用show full processlist 人工判断出锁表的会话,使用kill杀死此会话。不只是锁表的需要杀死,可能某些建立索引的,或者特别耗时的sql也需要杀死,原则就是无法很快处理掉的会话都需要杀死,如果不杀死可能会严重拖慢mysql的效率而造成死机,更多的慢sql等等,

    记录导出慢SQL日志

    通过

    SHOW VARIABLES LIKE '%slow_query_log%' ;

    查看是否开启了慢sql日志以及日志位置。 配置my.ini文件(Linux下文件名为my.cnf), 查找到[mysqld]区段,增加日志的配置,如下示例:

    [mysqld]
    slow_query_log = 1;   #开启
    slow_query_log_file=/var/lib/mysql/slow_query.log    #慢日志地址,缺省文件名host_name-slow.log
    long_query_time=5;   #运行时间超过该值的SQL会被记录,默认值>10,单位s(秒)
    log_output=FILE

    Linux下这些配置项应该已经存在,只是被注释掉了,可以去掉注释。 未使用索引的查询被记录到慢查询日志中。如果调优的话,建议开启这个选项。如果开启了这个参数,full index scan的sql也会被记录到慢查询日志中。

    show variables like '%log_queries_not_using_indexes%' 
    set global log_queries_not_using_indexes=1

    分析慢查询日志

    mysqldumpslow 慢日志分析工具。 命令: -s 按照那种方式排序 c:访问计数 l:锁定时间 r:返回记录 al:平均锁定时间 ar:平均访问记录数 at:平均查询时间 -t 是top n的意思,返回多少条数据。 -g 可以跟上正则匹配模式,大小写不敏感。 得到返回记录最多的20个sql:

    mysqldumpslow -s r -t 20 sqlslow.log

    得到平均访问次数最多的20条sql:

    mysqldumpslow -s ar -t 20 sqlslow.log

    得到平均访问次数最多,并且里面含有ttt字符的20条sql:

    mysqldumpslow -s ar -t 20 -g "ttt" sqldlow.log

    注: 如果出现如下错误,Died at /usr/bin/mysqldumpslow line 161, <> chunk 405659.说明你要分析的sql日志太大了,请拆分后再分析 拆分的命令为:

    tail -100000 mysql-slow.log>mysql-slow.20180725.log

    pt-query-digest,为另外一款慢sql分析工具,也推荐使用。 Mysqlsla,功能最全面的慢sql分析工具。

    执行计划与慢SQL分析

    Explain分析 Explain是Mysql的自带查询优化器,负责select语句的优化器模块,可以模拟优化器执行SQL查询语句,从而知道Mysql是如何处理SQL的,语法也很简单:Explain + SQL。 以下是通过explain查询出的几个属性

    alt

    (常见性能瓶颈 —— CPU:CPU饱和一般发生在数据装入内存或从磁盘上读取数据时 IO:磁盘I/O瓶颈发生在装入数据远大于内存容量时  服务器硬件的性能瓶颈:top,free,iostat,vmstat来查看系统的性能状态) 用途: (1)表的读取顺序,id (2)数据读取操作的操作类型,select_type (3)哪些索引可以使用 (4)哪些索引被实际使用 (5)表之间的引用 (6)每张表有多少行被优化器查询 rows

    • id:反映的是表的读取的顺序,或查询中执行select子句的顺序。 小表永远驱动大表,三种情况: (1)id相同,执行顺序是由上至下的 (2)id不同,如果是子查询,id序号会递增,id值越大优先级越高,越先被执行 (3)id存在相同的,也存在不同的,所有组中,id越大越先执行,如果id相同的,从上往下顺序执行

      alt derived是衍生虚表的意思,derived2中的2对应id2
    • select_type:反映的是Mysql理解的查询类型 (1)simple:简单的select查询,查询中不包含子查询或union。 (2)primary:查询中若包含任何复杂的字部分,最外层查询标记为primary。 (3)subquery:select或where列表中的子查询。 (4)derived(衍生):在from列表中包含的子查询,Mysql会递归执行这些子查询,把结果放在临时表里。 (5)union:若第二个select出现在union后,则被标记为union,若union包含在from字句的子查询中,外层select将被标记为derived (6)union result:union后的结果集

    • table:反映这一行数据是关于哪张表的

    • type:访问类型排序反映sql优化的状态,至少达到range级别,最好能达到ref查询效率:system > const > eq_ref > ref > range > index > all   (完整的排序:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >all)   (1)system:从单表只查出一行记录(等于系统表),这是const类型的特例,一般不会出现 (2)const:查询条件用到了常量,通过索引一次就找到,常在使用primary key或unique索引中出现。

      alt where id=1写死,所以类型是const (3)eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描。 (4)ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它可能会找到多个符合条件的行,与eq_ref的差别是eq_ref只匹配了一条记录。 (5)range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般是在where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。与eq_ref和ref的区别在于筛选条件不是固定值,是范围。
      alt (6)index:full Index scan,index和all的区别为index类型只遍历索引树。这通常比all快,因为索引文件通常比数据文件小。
      alt 要获得的id信息,刚好id在索引上,从索引中读取(all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读的) (7)all:全表扫描,如果查询数据量很大时,全表扫描效率是很低的。
    • possible_keys、key、key_len:反映实际用到了哪个索引,索引是否失效 (1)possible_keys:Mysql推测可能用到的索引有哪些,但不一定被查询实际使用 (2)key:实际使用的索引,若为null,则可能没建索引或索引失效。

      alt (查询中若使用了覆盖索引,则该索引仅出现在key列表中。   覆盖索引:select后面的字段和所建索引的个数、顺序一致) (3)key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。同样的查询结果下,长度越短越好。 key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
    • ref:反映哪些列或常量被用于查找索引列上的值

      alt
    • rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

      alt 仅通过主键索引查找是641行
      alt 建完相关的复合索引再查,需要查询的行数就变少了
    • Extra (1)using filesort:mysql中无法利用索引完成的排序,这时会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。

      alt 创建索引时就会对数据先进行排序,出现using filesort一般是因为order by后的条件导致索引失效,最好进行优化。
      alt order by的排序最好和所建索引的顺序和个数一致 (2)using temporary:使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
      alt 影响更大,所以要么不建索引,要么group by的顺序要和索引一致
      alt (3)using index:表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率好   覆盖索引:select后的数据列只从索引就能取得,不必读取数据行,且与所建索引的个数(查询列小于等于索引个数)、顺序一致。   所以如果要用覆盖索引,就要注意select的列只取需要用到的列,不用select *,同时如果将所有字段一起做索引会导致索引文件过大,性能会下降。
      alt 出现using where,表明索引被用来执行索引键值的查找
      alt 如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。 (4)using where:表明使用了where过滤 (5)using join buffer:使用了连接缓存 (6)impossible where:where子句的值是false (7)select tables optimized away (8)distinct:优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作

    Show Profile分析

    Show Profile也是分析慢SQL的一种手段,但它能获得比explain更详细的信息,能分析当前会话中语句执行的资源消耗情况,能获得这条SQL在整个生命周期的耗时,相当于执行时间的清单,也很重要。 默认关闭。开启后,会在后台保存最近15次的运行结果,然后通过Show Profile命令查看结果。

    开启:set profiling = on;
    查看:SHOW VARIABLES LIKE 'profiling%';
    • 通过Show Profile能查看SQL的耗时

      alt
    • 通过Query_ID可以得到具体SQL从连接 - 服务 - 引擎 - 存储四层结构完整生命周期的耗时

      alt
    • 可用参数type:

    ALL #显示所有的开销信息
    BLOCK IO   #显示块IO相关开销
    CONTEXT SWITCHES   #上下文切换相关开销
    CPU #显示CPU相关开销信息
    IPC #显示发送和接收相关开销信息
    MEMORY  #显示内存相关开销信息
    PAGE FAULTS   #显示页面错误相关开销信息
    SOURCE   #显示和Source_function,Source_file,Source_line相关的开销信息
    SWAPS   #显示交换次数相关开销的信息

    出现这四个status时说明有问题,group by可能会创建临时表

    • 危险状态:
    converting HEAP to MyISAM #查询结果太大,内存不够用了,在往磁盘上搬
    Creating tmp table #创建了临时表,回先把数据拷贝到临时表,用完后再删除临时表
    Copying to tmp table on disk #把内存中临时表复制到磁盘,危险!!!
    locked

    alt

    二进制日志解析

    什么是二进制日志?

    用来记录操作MySQL数据库中的写入性操作(增删改,但不包括查询),相当于sqlserver中的完整恢复模式下的事务日志文件。

    二进制日志的作用?

    • 用于复制,配置了主从复制的时候,主服务器会将其产生的二进制日志发送到slave端,slave端会利用这个二进制日志的信息在本地重做,实现主从同步
    • 用户恢复,MySQL可以在全备和差异备份的基础上,利用二进制日志进行基于时间点或者事物Id的恢复操作。原理同于主从复制的日志重做。
    • 可以用作数据分发,有收费的工具可以把一个数据库的数据分发到其他的mysql数据库,在事务性不强的统计等其他方面的需求可以使用分发库,请注意分发库会有一定的延迟(正常延迟是毫秒级)。

    Mysqlbinlog命令行工具可以对二进制日志进行操作。二进制日志很重要,请务必开启。

    MySQL的读写分离

    Mysql的读写分离正常情况下只支持一主多从。

    读写分离应用中间件来支持(尽量不要使用多数据源的方式):

    • 可以支持SQLhint路由,可以根据规则引入确认的一个节点上
    • 可以把事务全部到主节点上(如果不这样做读在从节点上,写在主节点上会出现脏数据,因为主从同步是有少许延迟的,虽然是毫秒级的)

    不再详细描述

    索引的注意事项

    • 索引对查询是速度提升以及减低慢SQL的查询是非常必要的,但是索引建立的复杂度高的,会影响插入和更新的效率,但是不要为了插入和更新的效率而不建立索引或者少建立索引,因为插入和更新在没有表锁的情况下基本没有慢SQL。

    • 但是如果一定想要屏蔽插入和更新因为索引带来的效率下降有以下方式(中间件方式): 分库分表,减低单表数据量(数据量少了索引也就小了),这样因为插入和更新造成的索引更新效率就变的影响非常的小。

    • 读写分离,主库(主要负责插入和更新)减少或者弱化索引的数量,从库(主要负责读取)增加索引的组合方式和数量,从库还可以是多个,不同的从库在同一个表建立的索引可以不一样,这样可以通过路由的方式把不同规则的语句路由到不同的从库上。

    • 例如: 数据库DB,有2个只读节点DBR1和DBR2。对table中有A、B、C三个列,对这3个列进行的索引。正常来说数据库table表中如果是AB一个组合索引,ABC一个组合索引,BC一个组合索引就会让索引变的比较臃肿,并且SQL在解析的时候还有可能用错索引。 DBR1只建立ABC索引,DBR2建立AB索引,就变的比较清晰了。

    • 建立索引不要太在意网上的那些方式,包括 in 语句无法用到索引等,要相信执行计划(explain)而不要相信直觉,因为数据库不断的升级,一个版本都会有部分的差异。

    • 建立组合索引的原则可以基本的理解为, where 条件后有几个and条件,这些条件就应该建立一个组合索引,有些and条件是可变的或者拼接的,所以索引不要这样盲目建立,需要根据慢sql来建立。 高并发正在运行的生成系统建立索引需要注意的步骤: 1、打开查询编辑器输入,show full processlist 2、打了另外的编辑器输入建立索引的语句 3、执行建立索引的语句 4、切换第一个查询编辑器不断的运行show full processlist,查看目前所有会话执行sql的状态,如果出现大量的缓慢SQL现象以及死锁现象,尽快用kill语句杀死一直在运行的会话(不只是这个建立索引的会话)。因为失败,建立索引需要再次规划时间,正常情况下应该是凌晨进行。

    可以使用函数等方式强制只用某个索引或者取消使用索引:

    SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
    
    SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

    数据库设计范式

    第一范式

    第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库满足第一范式。 第一范式的合理遵循需要根据系统给的实际需求来确定。比如某些数据库系统中需要用到"地址"这个属性,本来直接将"地址"属性设计成为一个数据库表的字段就行,但是如果系统经常访问"地址"属性中的"城市"部分,那么一定要把"地址"这个属性重新拆分为省份、城市、详细地址等多个部分来进行存储,这样对地址中某一个部分操作的时候将非常方便,这样设计才算满足数据库的第一范式。如下图。

    alt 上图所示的用户信息遵循第一范式的要求,这样对用户使用城市进行分类的时候就非常方便,也提高了数据库的性能。

    第二范式

    第二范式在第一范式的基础上更进一层,第二范式需要确保数据库表中每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。 比如要设计一个订单信息表,因为订单中可能会有多种商品,所以要将订单编号和商品编号作为数据库表的联合主键,如下图。

    alt 这里产生一个问题:这个表中是以订单编号和商品编号作为联合主键,这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关,而仅仅是与商品的编号相关,所以在这里违反了第二范式的设计原则。 而如果把这个订单信息表进行拆分,把商品信息分离到另一个表中,把订单项目表也分离到另一个表中,就非常完美了,如下图。
    alt 这里这样设计,在很大程度上减小了数据库的冗余,如果要获取订单的商品信息,使用商品编号到商品信息表中查询即可。

    第三范式

    第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。 比如在设计一个订单数据表的时候,可以将客户编号作为一个外键和订单表建立相应的关系,而不可以在订单表中添加关于客户其他信息(比如姓名、所属公司)的字段,如下面这两个表所示的设计就是一个满足第三范式的数据库表。

    alt 这样在查询订单信息的时候,就可以使用客户编号来引用客户信息表中的记录,也不必再订单信息表中多次输入客户信息的内容,减小了数据冗余。
    展开全文
  • 开启慢SQL的配置参数 slow_query_log: 该参数表示是否开启慢SQL查询日志。在mysql中,我们可以通过以下命令来查看和修改该变量的状态 show variables like '%slow_query_log%' #查询该变量在MySQL中当前的状态。 set...

    开启慢SQL的配置参数

    slow_query_log: 该参数表示是否开启慢SQL查询日志。在mysql中,我们可以通过以下命令来查看和修改该变量的状态

    show variables like '%slow_query_log%'  #查询该变量在MySQL中当前的状态。
    set GLOBAL slow_query_log = ON/OFF     #开启慢查询监控ON,关闭慢查询监控OFF
    

    开启general_log执行日志

    show variables like "general_log%";#查询该变量在MySQL中当前的状态。
    set GLOBAL general_log = 'ON';#开启执行日志监控ON,关闭执行日志监控OFF
    

    long_query_time设置该参数,可以规定执行多长时间的SQL为慢SQL。

    show variables like '%long_query_time%'  #查询该变量在MySQL中的状态
    set long_query_time = 6    #设置SQL执行时间超过6S,就当作慢SQL输出
    

    slow_query_log_file设置该参数,可以将慢SQL输出到指定的规定的日志文件。

    show variables like '%slow_query_log_file%'   #查询该变量在MySQL中的状态
    set slow_query_log_file = /home/admin/mysql/log/slow_sql.log  #指定慢SQL输出的日志文件
    

    log_queries_not_using_indexes该参数设置为ON,可以输出所有未使用索引的SQL语句。

    show variables like 'log_queries_not_using_indexes' #查询该变量在MySQL中的状态
    set global log_queries_not_using_indexes = ON/OFF #设置该变量值打开或关闭
    

    log_output可以通过该参数,指定慢SQL输出到表或文件中。如果输出到表中在mysql.slow_log表中可以查看。

    show variables like 'log_output'  #查询该变量在MySQL中的状态
    set GLOBAL log_output = table/FILE  #设置慢SQL输出到table或file
    set GLOBAL log_output = 'table,FILE' #设置慢SQL同时输出到table和file
    

    从mysql的table中查看mysql日志

    这要在上面中设置输出日志到table中,比如
    set GLOBAL log_output = table/FILE #设置慢SQL输出到table或file同时输出到file和table中

    select * from mysql.slow_log
    

    这个是在mysql的库
    在这里插入图片描述

    记录分析(mysqldumpslow)

    mysqldumpslow -s t -t 10 -g 'log' mysql_slow.log
    

    MySQL自带的慢SQL分析工具

    -s : 按照哪种规则排序
    -t: 显示前几个记录
    -g : 有点像grep, 后跟正则

    • c: 访问计数
    • l: 锁定时间
    • r: 返回记录
    • t: 查询时间
    • al:平均锁定时间
    • ar:平均返回记录数
    • at:平均查询时间

    demo如下
    最好在mysql_slow.log前加全路径,这里的mysql-slow.log是之前设置的日志输出文件的路径下的文件名,默认就是mysql-slow.log

    mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log
    在这里插入图片描述

    得到返回记录集最多的10条SQL

    mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log
    

    得到按照查询时间排序,并且带有left join的10条SQL

    mysqldumpslow -s t -t 10 -g “left join/var/lib/mysql/mysql-slow.log
    

    查看正在运行的sql

    select * from information_schema.processlist
    

    demo
    找出所有执行时间超过 5 分钟的线程,拼凑出 kill 语句,方便后面查杀 (此处 5分钟 可根据自己的需要调整SQL标红处)

    select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;
    

    查询线程及相关信息

    除了 root 用户能看到所有正在运行的线程外,其他用户都只能看到自己正在运行的线程,看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。

    show full processlist
    

    ID 为此线程ID,Time为线程运行时间,Info为此线程SQL
    在这里插入图片描述

    展开全文
  • 慢SQL!压垮团队的最后一根稻草!

    万次阅读 多人点赞 2018-05-21 11:15:58
    那是先解决慢SQL还是先开发需求呢?拆库是不可能了,逻辑这么死鬼复杂拆库完全没法跑啊,加CPU加内存啊 DBA大佬!!! [DBA日报] 慢SQL 180+,已解决10。 又上了一个版本 [DBA日报] 慢SQL 200+,已解决15。 ...

    这里写图片描述
    来源 | 公众号 | 一名叫大蕉的程序员 | 作者 | 杨钊

    原文地址:https://mp.weixin.qq.com/s/62fTZoAU_ThqA50v9iY1TQ


    先说结论,我支持将逻辑写在Java等应用系统中!

    背景:

    今天只讨论一种应用模式,就是最普遍的,前端实时调用后端Web服务,服务端经过DB的增删改查作出响应的应用。至于离线数据分析,在线规则引擎模板执行,流式计算等不在本次讨论范畴。

    一、重SQL还是重Java的开发场景演示

    先看一个例子吧,需求是:查询出每个学生所在的城市名以及分数展示到前端。用经典的Controller、Service、DAO开发模式描述,设计数据库表如下:

    这里写图片描述

    (1)重SQL模式示例代码:

    这里写图片描述

    (2)重Java模式示例代码:

    这里写图片描述
    这里写图片描述
    这里写图片描述

    可以看到,使用重SQL的模式来进行开发确实很快很快,只需要把SQL开发出来基本就完事了,但是看着用重 Java 的模式开发,需要写一堆的代码,这么看来好像是 SQL 胜利一筹。

    好,PD突然说了,我要把城市名为 “大蕉” 的,分数乘于2展示出来。握草,这个怎么搞??

    (1)重SQL模式示例代码:

    这里写图片描述

    好了。。这个SQL已经变得很复杂了基本没法看了。。

    (2)重 Java 模式示例代码:

    这里写图片描述

    咦好像改动也不多嘛。

    这时候PD又来了我要把城市名为 “大蕉” ,并且城市Code小于10086的,分数乘于2展示出来。握草,完蛋了,之前全是SQL,这个需求要怎么搞??继续叠加上去继续 CASE WHEN?

    还没想清楚呢,突然 DBA 电话飞过来了,兄dei你的SQL太慢了,现在把整个库拖垮了,你是不是没有加索引?

    我:索引加了啊。。。难道是没走到?那是先解决慢SQL还是先开发需求呢?拆库是不可能了,逻辑这么死鬼复杂拆库完全没法跑啊,加CPU加内存啊 DBA大佬!!!

    [DBA日报] 慢SQL 180+,已解决10。

    又上了一个版本

    [DBA日报] 慢SQL 200+,已解决15。

    又上了一个版本

    [DBA日报] 慢SQL 250+,已解决30。

    慢慢的,开发和运营和DBA每天都疲劳于监控这些SQL。。。。

    二、上述示例的思考

    观察了一下,传统企业以及绝大部分转型中的企业的 Java 应用中,很神奇的是,他们的开发人员包括我自己以前,大家都非常非常希望使用一个 SQL 来完成所有的逻辑的编写,非常多企业更是把数据库的存储过程和数据库自定义函数来完成。

    这些关于逻辑应该写在哪里的争论从来没有停止过,不仅仅发生在后端和数据库端,连前后端都经常会发生这种争论,现在只讨论后端和数据库端的纠结。

    我将从这五个方面分别对比一下两种模式的异同:

    • 出现场景

    • 开发效率

    • 缺陷排查

    • 架构升级

    • 系统维护

    三、出现场景

    1、SQL

    我们绝大多数的历史代码都是用存储过程来实现的啊,如果有新需求不往上面做的话,很难兼容原来的逻辑啊啊。

    前面的人呢是这样写的,我来了看大家都这样写就这样写了。

    2、Java

    新应用嘛,我想怎么样写就怎样写。

    监控和埋点写起来简单吖,排查问题可方便了。

    前面的人呢是这样写的,我来了看大家都这样写就这样写了。

    四、开发效率

    1、SQL

    这样写起来很快啊,而且写 Java 代码多难受啊,写 SQL 我自己在数据库开发环境跑一下结果正确我就直接丢到代码中提交了,多爽啊。

    老实说,这样子确实会提高开发的效率,因为不用写那么多查库聚合的操作,一切都在 SQL 中搞定了。另一方面来看,这确实会让 Java 代码看起来很鸡肋,好像只是把数据从 web 层到数据层的一个管道而已,一切 if else 能写在 SQL 中的都写在 SQL 中了。

    但是新需求来或者需求变更的时候,我经常要重新写SQL,如果变动不多我可能要改动到原来的 SQL,但是我又不敢改,所以只好 copy 重新写一个,改 SQL 的风险好大,一报错又要重启好难受。

    2、Java

    一次要写N个类,有点烦。

    新需求来或者需求变更的时候,如果逻辑比较复杂,我直接抽成方法或者改成一些设计模式,维护起来效率还是可以接受的。

    五、缺陷排查

    1、SQL

    开发排查问题的时候,除了看日志,直接把SQL和参数丢到 PL/SQL 或者 其他工具里跑一下,基本就能知道数据问题出现在哪了。测试同学在进行测试的时候,如果发现有不对的东西,直接跟开发同学一样的思路,把SQL 跑一下,问题基本就定位得七七八八了。

    但是呢,一旦遇到跑 SQL 无法一眼看出问题的 bug 或者 SQL 实在是太长太长了的的时候,就蒙圈了。我曾经就维护了一个几千行的存储过程,一旦发生问题,排查问题的过程巨艰难。但是呢直接用一个数据库一个功能搞定所有功能未尝不是一件很爽的事情,因为关系型数据库实在是实在是太太太稳定了,一次编写永久运行。

    2、Java

    看日志看监控。

    根据报错的代码位置 check 一下代码逻辑。

    一些入参分支肉眼 check 不出来,只能远程 debug 慢慢看数据流向。

    测试的同学基本无法帮忙 check 缺陷,只能靠程序的表现来判断。

    六、架构升级

    1、SQL

    SQL 慢没关系,它稳定啊,慢就把机器垂直扩展一下好啦,加cpu,加内存,换SSD,加加加绝对可以解决事情的。

    SQL 有各种索引和优化策略,说不定跑起来比我们自己写逻辑还快呢。

    加加加,加内存加cpu垂直升级。也没有其他招数了,除了前置缓存,但是如果查询都很个性化SQL很复杂,前置缓存也基本没啥乱用。。。

    如果你的逻辑全部写在 SQL 中,那完蛋了,你这个表基本就没法分表了,因为你的业务逻辑跟数据库的数据完整性是强耦合的,需要一切数据基本都在一个数据库中,这是一件很难受很难受的事情,不信你去问问那些所有业务逻辑全写在 SQL 中的小伙。

    数据库中非常复杂的表关联会极大程度拖慢数据库处理每条 SQL 的平均时间,极大程度拖慢数据库 RT,降低了数据库的 RT ,如果逻辑都写在 SQL 中,那么只能进行垂直升级。因为一旦进行水平扩展,那么多机器的非常复杂的分布式表关联,RT 基本不是一个高并发的业务应用的能容忍的。

    2、Java

    如果是数据库瓶颈,加数据库机器,分库分表一下,应用层基本不用改,在DAO层进行路由一下。

    如果是服务器cpu瓶颈,多加几台机器就好了。

    如果还有瓶颈,增加一下查询缓存。

    在应用快速发展的过程中一般都会分库分表的拆分或者自动水平扩展,这时候其实只需要数据库层面做好自己的数据迁移和同步就好了,对于业务层来说是完全无感知的。即使业务非常非常复杂,需要拆应用,其实也非常简单,只需要把对应的 DAO 层的操作拆分出去,换成 RPC 或者其他方式的调用就好了。

    七、系统维护

    1、SQL

    旧SQL完全不敢动,来一个需求加一个 SQL。

    慢SQL日益增加,应对疲乏。

    2、Java

    SQL写完一次基本不用动,来一个需求加一个方法聚合一下数据操作即可。

    应用维护比较简单,只要监控做好了,定位到问题基本都能很快解决。

    逻辑越来越复杂,没有好的开发框架的话,代码维护起来也是挺要命,因为完全不知道跑哪个分支去了。但是现在已经有很多优秀的开源框架来更好地维护代码了,比如 Spring 的全家桶。

    八、怎么破!

    旧的重 SQL 逻辑暂时不要动,新的逻辑都基于 Java 模式开发,先保证慢 SQL 不增加,旧的 SQL 稳定运行,毕竟业务稳定是第一要素。

    如果业务初期需要非常非常快速开发,那么使用重 SQL 模式也是可以理解的,但是还是要抽时间重构成 Java 模式。

    九、结论

    我支持将逻辑写在 Java 等应用系统中。其实原因在上面基本描述完了,第一就是复杂 SQL 的表关联其实跟个人的能力有非常大的关系,如果一个 SQL 写得不好,那是极慢极慢的非常容易把整个数据库拖慢的。第二就是维护这些 SQL 也是一件很难受的事情,因为你完全不知道这个 SQL 背后的数据流转是怎样的,你只能根据自己的猜测去查看 SQL 中的 bug,Java 应用好歹还能 debug 一下还有打点看看数据不是?如果逻辑写在 Java 中那么其实你的 DAO 层只需要编写一次,但是可以永久使用,基本不会在这一层浪费很多的时间(用过 ibatis 的都知道改了 SQL 需要重启应用吧?)。第三就是逻辑都写在 SQL ,中对于分库分表和应用拆分来说是一件非常难受的事情,真的难受。

    展开全文
  • 慢sql优化

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

    千次阅读 2020-12-26 12:07:45
    为什么会出现慢SQL 在生产环境中,慢SQL是一个比较严重的问题,在面试时也会经常被问到,那究竟哪些情况会产生慢SQL,我们又该如何排查,如何定位呢?接下来我们将一步步带来这些问题的答案 写操作 刷脏页 脏页的...
  • MySQL如何定位慢sql

    千次阅读 2019-08-12 23:33:05
    MySQL如何定位慢sql MySQL“慢SQL”定位 数据库调优我个人觉得必须要明白两件事 1.定位问题(你得知道问题出在哪里,要不然从哪里调优呢) 2.解决问题(这个没有基本的方法来处理,因为不同的问题处理的方式方法不...
  • 达梦数据库获取慢sql

    2021-02-03 13:47:57
    在数据库优化中sql优化是很重要的一部分,那么如何有效的获取慢sql 一、通过历史执行sql记录 当 INI 参数 ENABLE_MONITOR=1、MONITOR_TIME=1 打开时,显示系统最近 1000 条执行时间超过预定值的 SQL 语句。默认...
  • Mybatis拦截慢SQL日志记录

    千次阅读 2020-04-24 11:32:58
    背景 通过分析MySQL慢查询日志不方便,利用...本文利用SpringBoot和Mybatis,基于Mybatis Interceptor实现慢SQL拦截日志记录和打印输出 一、服务配置 # slow-sql # 基于interceptor slow-sql-interceptor.enabled=...
  • 一个sql执行很慢的就叫慢sql,一般来说sql语句执行超过5s就能够算是慢sql,需要进行优化了 为何要对慢SQL进行治理 每一个SQL都需要消耗一定的I/O资源,SQL执行的快慢直接决定了资源被占用时间的长短。假设业务要求...
  • 查询慢sql语句消耗

    2018-05-14 11:17:29
    查询慢sql语句消耗 :1.查询定位当前sqlid 的执行慢在哪一步。2.查询历史sqlid 的执行慢在哪一步。3. 通过SQL Monitor 定位SQL的问题。
  • 如何查看慢SQL

    千次阅读 2019-07-24 15:40:29
    # 配置慢查询,5.7版本默认为1,这里long_query_time=10要改很小,方便出慢sql日志 slow-query-log=1 slow_query_log_file="user-slow.log" long_query_time=10 #默认不开启二进制日志 #log-bin=mysql-...
  • 互联网中慢SQL优化手段

    千次阅读 2020-12-10 20:53:58
    一个再完美的架构,也会因为一个慢SQL导致系统直接崩溃。 最近在做一个比较有意思的工作,优化系统中的慢SQL,整个过程我是很享受的,往往很多慢SQL都是系统中调用非常频繁的接口,这让我对业务更加熟悉,整个优化...
  • 开启druiddatasource的状态监控 stat: enabled: true db-type: mysql # 开启慢sql监控,超过2s 就认为是慢sql,记录到日志中 log-slow-sql: true slow-sql-millis: 2000 # 日志监控,使用slf4j 进行日志输出 slf4j: ...
  • SQL 问题经验总结

    万次阅读 2018-08-21 08:54:07
    1、 导致 SQL 的原因 在遇到 SQL 情况时,不能简单的把原因归结为 SQL 编写问题(虽然这是最常见的因素),实际上导致 SQL 有很多因素,甚至包括硬件和 mysql 本身的 bug。根据出现的概率从大到小,罗列如下: ...
  • 达梦数据分析慢SQL

    千次阅读 2020-08-24 14:52:54
    达梦数据分析慢SQL 一、前提条件安装好数据库并启动实例。 二、打开SQL日志功能 2.1、调用系统过程动态打开日志 SP_SET_PARA_VALUE(1,‘SVR_LOG’,1) --打开 2.2、打开之后会在数据库安装目录下的log产生一个dmsql_...
  • explain使用+慢SQL分析

    万次阅读 2018-05-31 14:27:08
    MySQL查询截取分析步骤:一、开启慢查询日志,捕获慢SQL二、explain+慢SQL分析三、show profile查询SQL语句在服务器中的执行细节和生命周期四、SQL数据库服务器参数调优一、开启慢查询日志,捕获慢SQL1、查看慢查询...
  • mysql如何获取慢SQL,以及慢查询的解决方式

    万次阅读 多人点赞 2019-02-07 21:00:00
    浅谈MySQL中优化sql语句查询常用的30种方法 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。  2.应尽量避免在 where 子句中使用!=或&lt;&gt;操作符,否则将...
  • 慢sql排查流程

    2020-03-16 17:39:43
    查看数据库服务查询日志是否开启 show variables like 'slow_query_log'; on:开启 off:关闭 开启查询 set global slow_query_log=on; ...设置没有开启索引的SQL语句记录到...设置超过多久时间的sql记录到查...
  • // 执行超过此时间的为慢sql,毫秒 statFilter.setLogSlowSql(true);// 是否打印慢日志 statFilter.setMergeSql(true);// 是否将日志合并起来 return statFilter; } 数据源配置中修改为: @Bean @...
  • 利用mybatis插件功能实现慢sql监控

    千次阅读 2020-06-11 15:55:22
    首先在mybatis全局配置文件中加入如下配置。... //定义一个属性值用来配置慢sql的时间 <property name="slowSqlTime" value="10000"/> </plugin> </plugins> 自定义插件类,配置拦截的方
  • 慢SQL 到底应该如何优化?

    千次阅读 2020-08-18 21:30:17
    在应用的的开发过程中,由于初期数据量小,开发人员写 SQL 语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多 SQL 语句开始逐渐显露出性能问题,对生产的影响也越来越大,此时...
  • oracle常用查询慢SQL的语句

    千次阅读 2020-10-25 16:37:07
    SQL_PLAN_OPERATION, SQL_PLAN_OPTIONS, EVENT, PROGRAM FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SNAP_ID IN (147877, 147878) AND SESSION_TYPE = 'FOREGROUND' AND SQL_ID = 'da90pycpdy62b' --查看查询sql ...
  • 查找postgresql的慢SQL

    千次阅读 2019-03-18 11:39:09
    查找执行sql,可以开启druid的监控,也可以在数据库开启性能监控。 oracle默认开启,pg需要手动配置开启。 pg的统计信息放入pg_stat_statements中,开启统计信息之后,对pg库的性能会有一定影响,应该不会太大...
  • 阿里面试真题:慢SQL优化思路

    千次阅读 2020-01-12 16:55:10
    其实这是个常见的面试题目: 1、知识点一:执行计划:具体参数可以参见: ... 各列的含义如下: ...id: SELECT 查询的标识符....rows: 显示此查询一共扫描了多少...划重点:如果sql语句优化已无法解决慢SQL应该怎么办??
  • oracle慢sql跟踪oracle慢sql跟踪查询执行最慢的sql查询当天执行慢的SQL:查询次数最多的SQL完整跟踪仅对增删改跟踪查找最近一分钟内,最消耗CPU的SQL语句:查找最近一分钟内,最消耗CPU的会话:查找最近一分钟内,最...
  • Druid : 慢SQL统计与监控

    千次阅读 2020-01-13 16:35:38
    1.美图 2.概述 背景是我写了一个代码,这个代码是批量操作 这里会打印大量的,很多的ERROR级别的...-- 慢SQL记录 --> <bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter"> &l...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 286,800
精华内容 114,720
关键字:

慢sql