Flowable-Optimization

阅读更多

1 慢SQL

Flowable中很多操作会使用id来查询这些数据，但是数据表却没有为这些id列增加索引，导致了非常多的慢sql

1.1 act_ru_identitylink

SELECT *
FROM act_ru_identitylink
WHERE proc_inst_id_ = 'f69a1b42-bf55-11e8-86a7-00163e0e390f'

DELETE FROM act_ru_identitylink
WHERE task_id_ = '20720152-be30-11e8-a50e-00163e045396'

1.2 act_ru_execution

SELECT *
FROM act_ru_execution
WHERE parent_id_ = '1fc7b7f6-be30-11e8-a50e-00163e045396'

SELECT *
FROM act_ru_execution
WHERE super_exec_ = '41198800-be30-11e8-a50e-00163e045396'

SELECT *
FROM ACT_RU_EXECUTION
WHERE PROC_INST_ID_ = 'f11e4b5e-fa1d-11e8-9ab2-00163e1afb6d'
AND PARENT_ID_ IS NOT NULL

1.3 act_ru_task

SELECT t.*
FROM act_ru_task t
WHERE t.proc_inst_id_ = '3f47fd17-be30-11e8-a50e-00163e045396'

1.4 act_ru_deadletter_job

SELECT *
FROM act_ru_deadletter_job j
WHERE j.execution_id_ = '1fc7b7f6-be30-11e8-a50e-00163e045396'

1.5 act_ge_bytearray

SELECT * FROM act_ge_bytearray
WHERE deployment_id_ = 'f8575101-bf54-11e8-86a7-00163e0e390f'
ORDER BY name_ ASC

1.6 建议索引

CREATE INDEX IDX_OPTIMIZATION_PROC_INST_ID ON ACT_RU_IDENTITYLINK(PROC_INST_ID_);
CREATE INDEX IDX_OPTIMIZATION_TASK_ID ON ACT_RU_IDENTITYLINK(TASK_ID_);

CREATE INDEX IDX_OPTIMIZATION_PARENT_ID ON ACT_RU_EXECUTION(PARENT_ID_);
CREATE INDEX IDX_OPTIMIZATION_SUPER_EXEC ON ACT_RU_EXECUTION(SUPER_EXEC_);
CREATE INDEX IDX_OPTIMIZATION_PROC_INST_ID ON ACT_RU_EXECUTION(PROC_INST_ID_);
CREATE INDEX IDX_OPTIMIZATION_PROC_DEF_ID ON ACT_RU_EXECUTION(PROC_DEF_ID_);
CREATE INDEX IDX_OPTIMIZATION_SUSPENSION_STATE ON ACT_RU_EXECUTION(SUSPENSION_STATE_);

CREATE INDEX IDX_OPTIMIZATION_PROC_INST_ID ON ACT_RU_TASK(PROC_INST_ID_);

CREATE INDEX IDX_OPTIMIZATION_PROC_INST_ID ON ACT_RU_DEADLETTER_JOB(PROCESS_INSTANCE_ID_);
CREATE INDEX IDX_OPTIMIZATION_EXECUTION_ID ON ACT_RU_DEADLETTER_JOB(EXECUTION_ID_);

CREATE INDEX IDX_OPTIMIZATION_DEPLOYMENT_ID ON ACT_GE_BYTEARRAY(DEPLOYMENT_ID_);

CREATE INDEX IDX_OPTIMIZATION_KEY ON ACT_RE_PROCDEF(KEY_);

CREATE INDEX IDX_OPTIMIZATION_PARENT_TASK_ID ON ACT_HI_TASKINST(PARENT_TASK_ID_);

CREATE INDEX IDX_OPTIMIZATION_SUPER_PROC_INST_ID ON ACT_HI_PROCINST(SUPER_PROCESS_INSTANCE_ID_);

CREATE INDEX IDX_OPTIMIZATION_KEY ON ACT_RU_VARIABLE(PROC_INST_ID_);

CREATE INDEX IDX_OPTIMIZATION_PROC_INST_ID ON ACT_RU_TIMER_JOB(PROCESS_INSTANCE_ID_);

CREATE INDEX IDX_OPTIMIZATION_PROC_INST_ID ON ACT_RU_SUSPENDED_JOB(PROCESS_INSTANCE_ID_);

2 清理历史数据

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '<your database name>' ORDER BY table_rows DESC

DELETE FROM ACT_RU_EXECUTION 
WHERE PROC_INST_ID_ = '<proc_inst_id>'

DELETE FROM ACT_RU_TASK
WHERE PROC_INST_ID_ = '<proc_inst_id>'

DELETE FROM ACT_RU_VARIABLE 
WHERE PROC_INST_ID_ = '<proc_inst_id>'

DELETE FROM ACT_RU_IDENTITYLINK
WHERE PROC_INST_ID_ = '<proc_inst_id>'

DELETE FROM ACT_RU_TIMER_JOB
WHERE PROCESS_INSTANCE_ID_ = '<proc_inst_id>'

DELETE FROM ACT_RU_DEADLETTER_JOB 
WHERE PROCESS_INSTANCE_ID_ = '<proc_inst_id>'

DELETE FROM ACT_RU_SUSPENDED_JOB
WHERE PROCESS_INSTANCE_ID_ = '<proc_inst_id>'

DELETE FROM ACT_HI_PROCINST 
WHERE PROC_INST_ID_ = '<proc_inst_id>'

DELETE FROM ACT_HI_ACTINST 
WHERE PROC_INST_ID_ = '<proc_inst_id>'

DELETE FROM ACT_HI_TASKINST 
WHERE PROC_INST_ID_ = '<proc_inst_id>'

DELETE FROM ACT_HI_VARINST 
WHERE PROC_INST_ID_ = '<proc_inst_id>'

DELETE FROM ACT_HI_IDENTITYLINK 
WHERE PROC_INST_ID_ = '<proc_inst_id>'

DELETE FROM ACT_HI_DETAIL 
WHERE PROC_INST_ID_ = '<proc_inst_id>'

-- find all BYTEARRAY_ID_

SELECT BYTEARRAY_ID_
FROM ACT_RU_VARIABLE
WHERE PROC_INST_ID_ = '<proc_inst_id>'
AND BYTEARRAY_ID_ IS NOT NULL

SELECT BYTEARRAY_ID_
FROM ACT_HI_VARINST
WHERE PROC_INST_ID_ = '<proc_inst_id>'
AND BYTEARRAY_ID_ IS NOT NULL

SELECT BYTEARRAY_ID_
FROM ACT_HI_DETAIL
WHERE PROC_INST_ID_ = '<proc_inst_id>'
AND BYTEARRAY_ID_ IS NOT NULL

DELETE FROM ACT_GE_BYTEARRAY
WHERE ID_ IN (
    '<id1>',
    '<id2>'
)