问题：导出接口因Mysql查询时间较长导致公司的csf调用超时无法导出。
第一次优化sql 也不是很懂，总结一下相应的优化思路吧，结论是30秒+的sql优化到10秒内。
在这总结一下优化思路：sql内内容如下
SELECT deputy.staffCode,deputy.staffName,
(CASE deputy.accountType
WHEN '1' THEN 'xxxx'
WHEN '2' THEN 'xxxx' END) AS accountType,
deputy.deputyAccountNumber,
deputy.orgCode,
deputy.orgName,
deputy.positionName,
(CASE deputy.inPositionType
WHEN 'P' THEN '是'
WHEN 'S' THEN '否' END) AS inPositionType,
(CASE r.isRoleGroup
WHEN '0' THEN '否'
WHEN '1' THEN '是' END ) AS isRoleGroup,
r.RoleOrRoleGroupCode,
r.RoleOrRoleGroupName
FROM (
SELECT
staff.staff_code staffCode,
staff.staff_name staffName,
account.ACCOUNT_TYPE accountType,
deputyAccount.DEPUTY_ACCOUNT_NUMBER deputyAccountNumber,
organization.ORG_CODE orgCode,
organization.DEPT_NAME orgName ,
position.POSITION_NAME positionName,
deputyRelat.IN_POSITION_TYPE inPositionType
FROM staff
LEFT JOIN account ON account.staff_code = staff.staff_code
LEFT JOIN deputyAccount ON deputyAccount.staff_code = account.staff_code
LEFT JOIN deputyRelat ON deputyAccount.DEPUTY_ACCOUNT_ID = deputyRelat.DEPUTY_ACCOUNT_ID
LEFT JOIN organization ON deputyRelat.ORGANIZATION_ID = organization.ORGANIZATION_ID
LEFT JOIN POSITION ON deputyRelat.POSITION_ID = position.POSITION_ID
WHERE
deputyAccount.DATA_STATUS = '1'
AND staff.DATA_STATUS = '1'
AND account.DATA_STATUS = '1'
AND deputyRelat.DATA_STATUS = '1'
AND (position.DATA_STATUS = '1' OR position.DATA_STATUS IS NULL)
AND organization.DATA_STATUS = '1'
AND staff.region_id = #{regionId}
AND account.region_id = #{regionId}
AND deputyAccount.region_id = #{regionId}
AND deputyRelat.region_id = #{regionId}
AND organization.SET_ID_DEPT = #{regionId}
AND position.SET_ID = #{regionId}
) deputy,
(SELECT
role.ROLE_CODE RoleOrRoleGroupCode,
role.ROLE_NAME RoleOrRoleGroupName,
deputyAccount.DEPUTY_ACCOUNT_NUMBER deputyAccountNumber,
'0' AS isRoleGroup
FROM staff
LEFT JOIN deputyAccount ON deputyAccount.staff_code = staff.staff_code
LEFT JOIN subAccount ON deputyAccount.DEPUTY_ACCOUNT_ID = subAccount.DEPUTY_ACCOUNT_ID
LEFT JOIN roleAuth ON subAccount.SUB_ACCOUNT_ID = roleAuth.OBJECT_ID
LEFT JOIN role ON roleAuth.ROLE_ID = role.ROLE_ID
WHERE
staff.DATA_STATUS = '1'
AND deputyAccount.DATA_STATUS = '1'
AND subAccount.DATA_STATUS = '1' AND subAccount.BUSINESS_SYSTEM_ID = '10'
AND roleAuth.DATA_STATUS = '1'
AND role.DATA_STATUS = '1'
AND role.ROLE_STATUS = '1'
AND staff.region_id = #{regionId}
AND deputyAccount.region_id = #{regionId}
AND subAccount.region_id = #{regionId}
UNION ALL
SELECT
roleGroup.ROLE_GROUP_CODE RoleOrRoleGroupCode,
roleGroup.ROLE_GROUP_NAME RoleOrRoleGroupName,
deputyAccount.DEPUTY_ACCOUNT_NUMBER deputyAccountNumber,
'1' AS isRoleGroup
FROM staff
LEFT JOIN deputyAccount ON deputyAccount.staff_code = staff.staff_code
LEFT JOIN subAccount ON deputyAccount.DEPUTY_ACCOUNT_ID = subAccount.DEPUTY_ACCOUNT_ID
LEFT JOIN roleGroupAuth force index(IDX_ROLE_DONE) ON subAccount.SUB_ACCOUNT_ID = roleGroupAuth.OBJECT_ID
LEFT JOIN roleGroup ON roleGroupAuth.ROLE_GROUP_ID = roleGroup.ROLE_GROUP_ID
WHERE
staff.DATA_STATUS = '1'
AND deputyAccount.DATA_STATUS = '1'
AND subAccount.DATA_STATUS = '1' AND subAccount.BUSINESS_SYSTEM_ID = '10'
AND roleGroupAuth.DATA_STATUS = '1' AND roleGroup.DATA_STATUS = '1'
AND roleGroup.ROLE_GROUP_STATUS = '1'
AND staff.region_id = #{regionId}
AND deputyAccount.region_id = #{regionId}
AND subAccount.region_id = #{regionId}
) r
WHERE deputy.deputyAccountNumber=r.deputyAccountNumber
1.因为上面sql可以分为三个部分，我就分别执行这三个sql，找到最慢sql，然后进行优化，慢sql如下
SELECT
roleGroup.ROLE_GROUP_CODE RoleOrRoleGroupCode,
roleGroup.ROLE_GROUP_NAME RoleOrRoleGroupName,
deputyAccount.DEPUTY_ACCOUNT_NUMBER deputyAccountNumber,
'1' AS isRoleGroup
FROM staff
LEFT JOIN deputyAccount ON deputyAccount.staff_code = staff.staff_code
LEFT JOIN subAccount ON deputyAccount.DEPUTY_ACCOUNT_ID = subAccount.DEPUTY_ACCOUNT_ID
LEFT JOIN roleGroupAuth force ON subAccount.SUB_ACCOUNT_ID = roleGroupAuth.OBJECT_ID
LEFT JOIN roleGroup ON roleGroupAuth.ROLE_GROUP_ID = roleGroup.ROLE_GROUP_ID
WHERE
staff.DATA_STATUS = '1'
AND deputyAccount.DATA_STATUS = '1'
AND subAccount.DATA_STATUS = '1' AND subAccount.BUSINESS_SYSTEM_ID = '10'
AND roleGroupAuth.DATA_STATUS = '1' AND roleGroup.DATA_STATUS = '1'
AND roleGroup.ROLE_GROUP_STATUS = '1'
AND staff.region_id = #{regionId}
AND deputyAccount.region_id = #{regionId}
AND subAccount.region_id = #{regionId}
2.然后用 EXPLAIN进行分析这个sql，发现在关联字段中，有关联字段没有用到索引，然后给他加上索引，sql从30秒提升到了15秒
3.然后我以为可以结束了，但上了生产之后，这个sql还是会有问题
4.问题是开发环境和生产环境同样的sql和索引使用到的情况却不同，开发环境能10秒执行完，生产却要15秒
开发环境用到的索引如下
生产索引如下
明显有问题啊，两套环境同样的索引却不一样的使用。
我怀疑生产环境是不是优先使用主建索引啊，导致索引使用情况不同，sql效率低下
于是我想指定索引 就使用到了 force index(IDX_ROLE_DONE)
然后测试和生产环境sql效率就相同了,10秒内
