sql分页及in字段优化

sql分页及in字段优化

分页优化
先分页查询id 或者查询一个有索引的字段 再联查出所需要的字段

SELECT a.* FROM USER a
INNER JOIN
(SELECT id FROM USER WHERE age = 10 LIMIT 100000,10) b
ON a.id = b.id;

或者深度分页 这样有一个弊端就是插入数据后不可物理删除

select * from user where id>#(pageNum) limit 0,size

in字段优化
需要将in 括号中的条件多嵌套一次

SELECT e.* ,ec.name as questionTypeName, ect.name as classTypeName,er.user_answer
FROM t_exercises e
LEFT JOIN t_exercises_class ec ON e.class = ec.id
LEFT JOIN t_exercises_class_type ect ON e.class_type = ect.id
LEFT JOIN (SELECT * FROM t_exercise_record WHERE menuId_one = 1127 AND memberId = 920 AND exercise_type_id = 5 )AS er ON e.id = er.exercises_id
WHERE e.id
IN (select exercises_id FROM (SELECT exercises_id FROM t_exercises_mryl WHERE menu_id = 1127) as hh) #重点在这一句 需要多嵌套一层查询