这条sql执行的结果存在type 为all的情况：我应该如何优化
explain SELECT teeabi.authority_id id,
teeabi.authority_number as authorityNumber,
teeabi.authority_zh_name as authorityZhName,
teeabi.authority_en_name as authorityEnName,
teeabi.authority_type_number as authorityTypeNumber,
teeabi.authority_type_zh_name as authorityTypeZhName,
teeabi.authority_type_en_name as authorityTypeEnName,
teeabi.rel_numbers as relNumber,
teeabi.rel_names as relNames,
teeabi.rel_en_names as relEnNames,
teeabi.source as source,
teeabi.status as status,
(SELECT GROUP_CONCAT(teeam.person_id)
from t_epm_exec_authority_members teeam
where teeam.authority_number = teeabi.authority_number
and teeam.role_code in ('1010', '1210')
and teeam.delete_flag = 'N'
and ifnull(teeam.END_DATE, CURRENT_DATE) >= CURRENT_DATE) as director,
(SELECT GROUP_CONCAT(teeam.person_id)
from t_epm_exec_authority_members teeam
where teeam.authority_number = teeabi.authority_number
and teeam.role_code in ('1050', '1040')
and teeam.delete_flag = 'N'
and ifnull(teeam.END_DATE, CURRENT_DATE) >= CURRENT_DATE) as secretary,
(SELECT GROUP_CONCAT(DISTINCT (teeam.person_id))
from t_epm_exec_authority_members teeam
where teeam.authority_number = teeabi.authority_number
and teeam.role_code in
('1020', '1030', '1230', '1070', '1100', '1140', '1250', '1260', '1150', '1290', '1300', '1310', '1320',
'1120', '1130', '1090')
and teeam.delete_flag = 'N'
and ifnull(teeam.END_DATE, CURRENT_DATE) >= CURRENT_DATE) as member,
teeabi.created_by creatorid,
teeabi.creation_date createtime,
teeabi.last_updated_by modifierid,
teeabi.last_update_date modifytime,
teeabi.file_edoc_id as fileDocId
FROM t_epm_exec_authority_basic_info teeabi
LEFT JOIN t_epm_exec_authority_org_rel teo on teo.authority_number = teeabi.authority_number
where
teeabi.authority_type_number in ('1040', 'epm_002', '1060', '1010', '1070', '1020', '1080', '1050')
group by teeabi.authority_number
ORDER BY teeabi.level_number, teeabi.rel_numbers, teeabi.authority_type_order
limit 20,20
结果为：
1,PRIMARY,teeabi,,ALL,"epm_exec_authority_U1,epm_exec_authority_N3",,,,3491,50,Using where; Using temporary; Using filesort
1,PRIMARY,teo,,ref,"uq_id,epm_exec_authority_N2",epm_exec_authority_N2,242,epm.teeabi.authority_number,1,100,Using index
4,DEPENDENT SUBQUERY,teeam,,ref,12313,12313,242,func,22,5,Using where
3,DEPENDENT SUBQUERY,teeam,,ref,12313,12313,242,func,22,2,Using where
2,DEPENDENT SUBQUERY,teeam,,ref,12313,12313,242,func,22,2,Using where
表结构为：
CREATE TABLE `t_epm_exec_authority_basic_info` (
`authority_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '行权主体id',
`authority_number` varchar(60) DEFAULT NULL COMMENT '行权主体编码',
`authority_zh_name` varchar(255) DEFAULT NULL COMMENT '行权主体名称(中文)',
`authority_en_name` varchar(255) DEFAULT NULL COMMENT '行权主体名称(英文)',
`parent_authority_number` varchar(60) DEFAULT NULL COMMENT '父层行权主体编码 （闲置字段）',
`parent_authority_zh_name` varchar(255) DEFAULT NULL COMMENT '父层行权主体中文名称（闲置字段）',
`parent_authority_en_name` varchar(255) DEFAULT NULL COMMENT '父层行权主体英文名称 （闲置字段）',
`rel_numbers` text COMMENT '所属实体型组织编码(存在多个为逗号拼接)',
`rel_names` text COMMENT '所属实体型组织名称(中文)',
`rel_en_names` text COMMENT '所属实体型组织名称(英文)',
`authority_type_number` varchar(30) DEFAULT NULL COMMENT '行权主体类型编码',
`authority_type_zh_name` varchar(50) DEFAULT NULL COMMENT '行权主体类型名称',
`authority_type_en_name` varchar(255) DEFAULT NULL COMMENT '行权主体类型英文名称',
`authority_type_order` smallint(6) DEFAULT NULL COMMENT '行权主体类型排序',
`authority_status_zh_name` varchar(50) DEFAULT NULL COMMENT '行权主体状态名称',
`authority_status_en_name` varchar(255) DEFAULT NULL COMMENT '行权主体状态英文名称',
`authority_status_number` varchar(30) DEFAULT NULL COMMENT '行权主体状态编码',
`level_number` varchar(30) DEFAULT NULL COMMENT '行权主体层级编码',
`establish_date` datetime DEFAULT NULL COMMENT '行权主体设立日期',
`revocation_date` datetime DEFAULT NULL COMMENT '行权主体撤销日期',
`effective_date` datetime DEFAULT NULL COMMENT '行权主体生效日期',
`disable_date` datetime DEFAULT NULL COMMENT '行权主体失效日期',
`status` varchar(40) DEFAULT '1' COMMENT '数据状态(与LOOKUP CODE保持一致)',
`delete_flag` char(1) NOT NULL DEFAULT 'N' COMMENT '删除标志(Y/N)',
`remark` varchar(2000) DEFAULT NULL COMMENT '行权主体描述（人岗定义描述）',
`file_edoc_id` varchar(255) DEFAULT NULL COMMENT '行权主体附件edocId',
`enable_flag` char(1) NOT NULL DEFAULT 'Y' COMMENT '有效标识(Y/N)',
`modify_flag` char(1) NOT NULL DEFAULT 'N' COMMENT '用户是否手动修改过(Y/N)',
`primary_flag` char(1) NOT NULL DEFAULT 'Y' COMMENT '主要标识(Y/N)',
`source` varchar(60) NOT NULL COMMENT '来源(system/manual)',
`created_by` varchar(200) NOT NULL DEFAULT 'system' COMMENT '创建人',
`creation_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`last_updated_by` varchar(200) NOT NULL DEFAULT 'system' COMMENT '修改人',
`last_update_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`authority_id`) USING BTREE,
UNIQUE KEY `epm_exec_authority_U1` (`authority_number`) USING BTREE,
KEY `epm_exec_authority_N1` (`authority_zh_name`) USING BTREE,
KEY `epm_exec_authority_N2` (`authority_en_name`) USING BTREE,
KEY `epm_exec_authority_N3` (`authority_number`) USING BTREE,
KEY `epm_exec_rel_number_N3` (`rel_numbers`(768)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=949682 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='行权主体基础表';
最新发布
本文介绍了一种使用CTE（公用表表达式）结合ROW_NUMBER()函数的方法，在SQL中按指定顺序更新记录ID。此方法适用于需要根据特定排序规则重新设置数据库表中记录ID的场景。
扫一扫
687
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
