Mysql慢sql优化案例之使用force index改变执行计划

一 问题描述

生产有个这样的慢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秒。

执行计划：