mysql优化-加索引

EXPLAIN
SELECT
    a.guild_id AS guildId,
    a.user_id AS userId,
    a.user_name AS userName,
    a.account,
    a. STATUS,
    a.created_date AS createdTime,
    a.last_mark_time AS lastMarkTime,
    a.user_type AS userType,
    a.role_level AS roleLevel,
    b.job_type AS jobType,
    c.mapped_account AS thirdAccount,
    c. COMMENT,
    d.type AS newType,
    d.operate_time AS operateTime
FROM
    guild_user a
LEFT JOIN user_account b ON a.user_id = b.openid
LEFT JOIN t_guild_third_account c ON b.account = c.mapped_account
LEFT JOIN guild_identity_convert d ON b.openid = d.user_id
AND d.`status` = 0
WHERE
    a.guild_id = 'xx'
AND locate('已注销', b.account) = 0
AND a. STATUS = '0'
ORDER BY
    a.created_date DESC
LIMIT 0,
 10

 

 优化点如上图

Using where; Using temporary; Using filesort

Using temporary表示由于排序没有走索引、使用union、子查询连接查询、使用某些视图等原因

看下sql，发现order by的created_date字段无索引，加上索引再看下

 

 无效，分析sql，需要增加几处索引

          表guild_user字段guild_id、user_id加索引、

          表t_guild_third_account mapped_account加索引、

          表guild_identity_convert user_id加索引