explain select count(0) from ( select distinct RES.ID_, RES.NAME_, ART.ASSIGNEE_, A0.TEXT_ as 'status', RES.START_TIME_, RES.END_TIME_, RES.BUSINESS_KEY_ from ACT_HI_PROCINST RES left outer join ACT_RE_PROCDEF DEF on RES.PROC_DEF_ID_ = DEF.ID_ inner join ACT_HI_VARINST A0 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_ and a0.TASK_ID_ is null and A0.NAME_ = '@global_process_status' and A0.VAR_TYPE_ = 'integer' inner join ACT_HI_VARINST A1 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_ and a0.TASK_ID_ is null and A1.NAME_ = '@process_form_structure' and A1.VAR_TYPE_ = 'string' left join ACT_RU_TASK ART on RES.ID_ = art.PROC_INST_ID_ where (exists ( select LINK.USER_ID_ from ACT_HI_IDENTITYLINK LINK where USER_ID_ in ("1") and LINK.PROC_INST_ID_ = RES.ID_))) table_count 怎么加索引
时间: 2023-07-27 09:07:00 浏览: 147
首先需要确认哪些列在查询中使用频繁,可以使用 EXPLAIN 分析查询语句,找到慢查询的具体原因。
在这个查询中,以下列被使用了:
- ACT_HI_PROCINST.START_TIME_
- ACT_HI_PROCINST.END_TIME_
- ACT_HI_PROCINST.BUSINESS_KEY_
- ACT_RU_TASK.ASSIGNEE_
因此,可以考虑在这些列上创建索引来优化查询性能。具体如下:
```
CREATE INDEX idx_act_hi_procinst_start_end_time ON ACT_HI_PROCINST (START_TIME_, END_TIME_);
CREATE INDEX idx_act_hi_procinst_business_key ON ACT_HI_PROCINST (BUSINESS_KEY_);
CREATE INDEX idx_act_ru_task_assignee ON ACT_RU_TASK (ASSIGNEE_);
```
注意,索引并不是万能的,需要根据实际情况进行优化和调整。
相关问题
explain select count(0) from ( select distinct RES.ID_, RES.NAME_, ART.ASSIGNEE_, A0.TEXT_ as 'status', RES.START_TIME_, RES.END_TIME_, RES.BUSINESS_KEY_ from ACT_HI_PROCINST RES left outer join ACT_RE_PROCDEF DEF on RES.PROC_DEF_ID_ = DEF.ID_ inner join ACT_HI_VARINST A0 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_ and a0.TASK_ID_ is null and A0.NAME_ = '@global_process_status' and A0.VAR_TYPE_ = 'integer' inner join ACT_HI_VARINST A1 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_ and a0.TASK_ID_ is null and A1.NAME_ = '@process_form_structure' and A1.VAR_TYPE_ = 'string' left join ACT_RU_TASK ART on RES.ID_ = art.PROC_INST_ID_ where (exists ( select LINK.USER_ID_ from ACT_HI_IDENTITYLINK LINK where USER_ID_ in ("1") and LINK.PROC_INST_ID_ = RES.ID_))) table_count 怎么加索引生效
在这个查询中,需要对以下表的列添加索引:
- ACT_HI_PROCINST:ID_, START_TIME_, END_TIME_, PROC_DEF_ID_
- ACT_HI_VARINST:PROC_INST_ID_, NAME_, TASK_ID_, VAR_TYPE_
- ACT_RU_TASK:PROC_INST_ID_, ASSIGNEE_
你可以使用以下命令添加索引:
```sql
CREATE INDEX idx_act_hi_procinst_id ON ACT_HI_PROCINST (ID_);
CREATE INDEX idx_act_hi_procinst_start_time ON ACT_HI_PROCINST (START_TIME_);
CREATE INDEX idx_act_hi_procinst_end_time ON ACT_HI_PROCINST (END_TIME_);
CREATE INDEX idx_act_hi_procinst_proc_def_id ON ACT_HI_PROCINST (PROC_DEF_ID_);
CREATE INDEX idx_act_hi_varinst_proc_inst_id ON ACT_HI_VARINST (PROC_INST_ID_);
CREATE INDEX idx_act_hi_varinst_name ON ACT_HI_VARINST (NAME_);
CREATE INDEX idx_act_hi_varinst_task_id ON ACT_HI_VARINST (TASK_ID_);
CREATE INDEX idx_act_hi_varinst_var_type ON ACT_HI_VARINST (VAR_TYPE_);
CREATE INDEX idx_act_ru_task_proc_inst_id ON ACT_RU_TASK (PROC_INST_ID_);
CREATE INDEX idx_act_ru_task_assignee ON ACT_RU_TASK (ASSIGNEE_);
```
注意,添加索引可能会影响数据库的性能和空间消耗,因此需要根据具体情况进行权衡和测试。
阅读全文