记一次接口优化的经历

借用我老大的话，接口优化其实主要是提高代码质量和执行效率，无非就是从三个方面入手：1、检查代码中SQL语句，优化SQL; 2、数据库层面优化，比如加索引等； 3、检查代码逻辑，尤其是for循环、递归等；

我这次优化，是因为数据量大的情况下，接口响应速度太慢了（30s左右）。最后一番检查，找到根源是SQL语句问题，于是我就对其中一条执行时间长的SQL语句进行了优化。不想码字了，直接上代码看吧。

## 优化前
SELECT u.user_id,COUNT(g.id) isVIP 
FROM user_info u 
LEFT JOIN (SELECT id, user_id FROM gz_vip_personage WHERE is_delete = 0 AND expired_date > NOW() GROUP BY user_id ) g on g.user_id=u.user_id   
WHERE u.school_id= 430 AND u.grade_id = 1 AND u.class_name = '3班'
GROUP BY u.user_id 
HAVING isVIP = 1
ORDER BY isVIP DESC,u.user_id;

## 优化后
SELECT u1.user_id, COUNT(g.id) isVIP 
FROM
( SELECT u.user_id FROM user_info u WHERE u.school_id = 430 AND u.grade_id = 1 AND u.class_name = '3班' ) u1
 LEFT JOIN ( SELECT id, user_id FROM gz_vip_personage WHERE is_delete = 0 AND expired_date > NOW( )) g ON g.user_id = u1.user_id GROUP BY u1.user_id 
HAVING isVIP = 1
ORDER BY isVIP DESC,u1.user_id;