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加索引