mysql 优化杂记

慢日志分析

1. SELECT user_id AS userId, charm AS num FROM db_charm WHERE user_id NOT IN (?) ORDER BY num DESC, userId LIMIT ?

• 性能分析：not in不走索引 转换成 left join
• 解决方式：

            SELECT c.user_id AS userId, c.charm AS num

            FROM db_charm c

            LEFT JOIN (SELECT user_id FROM db_charm WHERE user_id IN (1282)) b

            ON c.user_id = b.user_id

           WHERE b.user_id IS NULL LIMIT 1000