Mysql 多表联合查询时如果需要排序分页，如何保证效率高？
关注者
140
被浏览
87,872
10 个回答
需求：
3个表，主键都是id，t1、t2数据量在百万级，t3 数据量是1万，按 t3 的 time 字段排序分页，同时从t1、t2取出相关联的数据，我想用一条查询解决：select t1.*,t2.*,t3.* from t1,t2,t3 where t1.id = t3.id and t2.id = t3.id order by t3.time limit 4000,20 这个查询当 limit 后面的数比较大时就越来越慢
分析：
1.你的SQL语句为INNER JOIN 若是LEFT JOIN可以另外解决；
2.WHERE部分除了链接操作，无其他过滤条件，也就无特殊的手段；
3.不清楚你们的业务情况和数据分布关系，也即没有办法给出非常特殊的解决手段；
4.不可能叫你们修改数据库表结构；
5.你一次性返回的数据量太大且排序，会造成二次回表的算法，这样查询性能会骤降；
基于上述分析的因素，建议做法：
如 @陈俊儒 所言分2条SQL执行；
SELECT t3.ID from t1,t2,t3 where t1.id = t3.id and t2.id = t3.id order by t3.time limit 4000,20;
SELECTt1.*,t2.*,t3.* FROM t1,t2,t3 WHERE t1.id = t3.id and t2.id = t3.id AND T3.id IN(id_value...);
若是对t3表创建一个组合索引，格式为(t3.ID,t3.time)那么第一条SQL就可以使用 索引覆盖技术 完成，而且这张表的数据量也只有1W条，猜测可能属于配置性数据，变动少，可以考虑创建这样的索引；
请提问者内部测试下上述给出的技术方案！
1）这条SQL主要首先是把t1、t2、t3所有的数据进行join匹配，然后再对所有的数据通过t3.time进行order by 和limit操作，筛选出符合条件的数据。效率慢的原因是因为将很多无用的数据选择出来进行sort排序，这样其实是消耗I/O资源的。
2）换另外一个思路。首先通过t3.time对符合条件的t3记录进行筛选，然后再把筛选出来的记录跟t1和t2关联，第一步就把无用数据筛掉，减少资源消耗。
select t1.*, t2.* from (select t3.* from t3 order by t3.time limit 4000,20) as temp where t1.id = temp.id and t2.id = temp.id 。
3）感谢赵云同学的反馈，昨天忽略了id为主键这一条件。如果id不是主键，只是索引，那么sql语句改为select t1.*, t2.* from (select t3.* from t3 order by t3.time limit 1, 4020+K) as temp where t1.id = temp.id and t2.id = temp.id limit 4000, 20 。这两个limit的具体取值只能实际情况出发（因为表的N对M关系，对取值有影响）。