act_ru_variable表增加索引以提升查询性能

[TwilightTian]

我们在生产中环境中发现位于activiti-core/activiti-engine/src/main/resources/org/activiti/db/mapping/entity/VariableInstance.xml中以下查询sql存在耗时异常问题

 <select id="selectVariablesByExecutionId"
   parameterType="org.activiti.engine.impl.db.ListQueryParameterObject"
   resultMap="variableInstanceResultMap">
   select * from ${prefix}ACT_RU_VARIABLE
   where EXECUTION_ID_ = #{parameter, jdbcType=VARCHAR}
   and TASK_ID_ is null
 </select>

 <select id="selectVariablesByExecutionIds"
   parameterType="org.activiti.engine.impl.db.ListQueryParameterObject"
   resultMap="variableInstanceResultMap">
   select * from ${prefix}ACT_RU_VARIABLE
   where TASK_ID_ is null
   and EXECUTION_ID_ in
   <foreach item="item" index="index" collection="parameter" open="(" separator="," close=")">
     #{item}
   </foreach>
 </select>

可以通过对act_ru_variable表字段EXECUTION_ID_增加索引来提升查询性能。

ALTER TABLE `act_ru_variable` 
ADD INDEX `ACT_IDX_EXECUTION_ID_`(`EXECUTION_ID_`);