一 问题描述
生产有个这样的慢sql:
SELECT
ua.id AS "id",
ua.appid AS "appid",
ua.user_id AS "userId",
ua.create_time AS "createTime",
ua.deptid AS "deptid",
ua.group_id AS "groupId",
ua.sort AS "sort",
ac.app_name AS "appName",
ac.app_range AS "appRange",
ac.app_icon AS "appIcon",
ac.app_short_name AS "appShortName",
ac.app_path AS "appPath",
ac.user_count AS "userCount"
FROM td_unicom_user_app ua #force index(IDX_USER_APP_DEPTID)
LEFT JOIN
td_unicom_app_config ac
ON
ua.appid = ac.appid
WHERE ac.app_status='1'
AND ac.app_publish_status = '1'
AND ua.user_id != 'he-wangdm6'
AND ua.deptid IN
(
'00'
,
'0000'
,
'0013'
,
'001302'
,
'00130254227'
)
ORDER BY ua.deptid DESC
查询执行需要14秒,返回8千多条数据。
执行计划如下:
查看ua表的索引:
ua表在deptid上有个索引,这里却没使用该索引,奇怪。
二 解决办法
这里结合实际情况选择force index这种方式(强制走ua的索引IDX_USER_APP_DEPTID):
SELECT
ua.id AS "id",
ua.appid AS "appid",
ua.user_id AS "userId",
ua.create_time AS "createTime",
ua.deptid AS "deptid",
ua.group_id AS "groupId",
ua.sort AS "sort",
ac.app_name AS "appName",
ac.app_range AS "appRange",
ac.app_icon AS "appIcon",
ac.app_short_name AS "appShortName",
ac.app_path AS "appPath",
ac.user_count AS "userCount"
FROM td_unicom_user_app ua FORCE INDEX(IDX_USER_APP_DEPTID)
LEFT JOIN
td_unicom_app_config ac
ON
ua.appid = ac.appid
WHERE ac.app_status='1'
AND ac.app_publish_status = '1'
AND ua.user_id != 'he-wangdm6'
AND ua.deptid IN
(
'00'
,
'0000'
,
'0013'
,
'001302'
,
'00130254227'
)
ORDER BY ua.deptid DESC
查询从14秒降为了0.05秒。
执行计划: