mysql - Activiti mysql 性能调优(索引)

Question

我有 activiti 触发的慢速查询。什么索引有助于提高查询性能？:

explain select distinct RES.*,
    VAR.ID_ as VAR_ID_, VAR.NAME_ as VAR_NAME_, VAR.VAR_TYPE_ as VAR_TYPE_, VAR.REV_ as VAR_REV_,
    VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_, VAR.TASK_ID_ as VAR_TASK_ID_,
    VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, VAR.DOUBLE_ as VAR_DOUBLE_, 
    VAR.TEXT_ as VAR_TEXT_, VAR.TEXT2_ as VAR_TEXT2_, VAR.LAST_UPDATED_TIME_ as VAR_LAST_UPDATED_TIME_, VAR.LONG_ as VAR_LONG_
    from ACT_HI_TASKINST RES
        left outer join 
        ACT_HI_VARINST VAR
         ON RES.ID_ = VAR.TASK_ID_ or RES.PROC_INST_ID_ = VAR.EXECUTION_ID_
      inner join 
      ACT_RE_PROCDEF D 
      on RES.PROC_DEF_ID_ = D.ID_ 
     inner join ACT_HI_VARINST A0 
     on RES.ID_ = A0.TASK_ID_ 
     WHERE  D.KEY_ = 'studentApprovalFlow'
        and RES.TASK_DEF_KEY_ = 'approve_student'
          and A0.NAME_= 'studentId'
          and A0.VAR_TYPE_ = 'string'
              and A0.TEXT_
    =
          'MT201083' 
     order by RES.ID_ desc, VAR.LAST_UPDATED_TIME_ asc 
    LIMIT 20000 OFFSET 0;
<br/>

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: D
         type: ref
possible_keys: PRIMARY,ACT_UNIQ_PROCDEF
          key: ACT_UNIQ_PROCDEF
      key_len: 767
          ref: const
         rows: 1
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: A0
         type: ref
possible_keys: ACT_IDX_HI_PROCVAR_NAME_TYPE
          key: ACT_IDX_HI_PROCVAR_NAME_TYPE
      key_len: 1070
          ref: const,const
         rows: 1
        Extra: Using index condition; Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: RES
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 194
          ref: listman.A0.TASK_ID_
         rows: 1
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: VAR
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 278238
        Extra: Using where; Using join buffer (Block Nested Loop)
-----------------------------
<br/>

It is taking 2 sec to return results.
Following are the indexes on the table:
mysql> show index in ACT_HI_VARINST \G;
*************************** 1. row ***************************
        Table: act_hi_varinst
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID_
    Collation: A
  Cardinality: 278238
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: act_hi_varinst
   Non_unique: 1
     Key_name: ACT_IDX_HI_PROCVAR_PROC_INST
 Seq_in_index: 1
  Column_name: PROC_INST_ID_
    Collation: A
  Cardinality: 18549
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: act_hi_varinst
   Non_unique: 1
     Key_name: ACT_IDX_HI_PROCVAR_NAME_TYPE
 Seq_in_index: 1
  Column_name: NAME_
    Collation: A
  Cardinality: 44
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: act_hi_varinst
   Non_unique: 1
     Key_name: ACT_IDX_HI_PROCVAR_NAME_TYPE
 Seq_in_index: 2
  Column_name: VAR_TYPE_
    Collation: A
  Cardinality: 58
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
<br/>
----------------------

mysql> show index in ACT_HI_TASKINST \G;
*************************** 1. row ***************************
        Table: act_hi_taskinst
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID_
    Collation: A
  Cardinality: 13061
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: act_hi_taskinst
   Non_unique: 1
     Key_name: PROC_INST_ID_
 Seq_in_index: 1
  Column_name: PROC_INST_ID_
    Collation: A
  Cardinality: 13061
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 

<br/>
------------------------------

mysql> show index in ACT_RE_PROCDEF \G;
*************************** 1. row ***************************
        Table: act_re_procdef
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID_
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: act_re_procdef
   Non_unique: 0
     Key_name: ACT_UNIQ_PROCDEF
 Seq_in_index: 1
  Column_name: KEY_
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: act_re_procdef
   Non_unique: 0
     Key_name: ACT_UNIQ_PROCDEF
 Seq_in_index: 2
  Column_name: VERSION_
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: act_re_procdef
   Non_unique: 0
     Key_name: ACT_UNIQ_PROCDEF
 Seq_in_index: 3
  Column_name: TENANT_ID_
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 

<小时/>

Answer 1

按照使用的顺序在查询引用的列上创建简单索引或复合索引。
首先检查索引是否不存在。

GROUP BY 或 ORDER BY 也可以从相应的索引中受益匪浅。

在此示例中

WHERE  D.KEY_ = 'studentApprovalFlow' --> column already indexed

AND RES.TASK_DEF_KEY_ = 'approve_student' 

--> 缺少索引 HI_TASK_INST(TASK_DEF_KEY_)

AND A0.NAME_= 'studentId'
AND A0.VAR_TYPE_ = 'string'
AND A0.TEXT_ = ' .. '

--> 缺少索引 ACT_HI_VARINST(name, var_type, text)

ORDER BY RES.ID_ desc, VAR.LAST_UPDATED_TIME_ asc 

--> 可能无法使用索引，因为 ORDER BY 中引用了 2 个表；
尝试为LAST_UPDATED_TIME_asc创建单独的索引，看看是否可以改善查询。

如果外键尚不存在，也将索引放入外键中。