数据库中有两张表
t1 存储消息信息
+-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | code | int(10) unsigned | NO | MUL | NULL | | | detail | varchar(200) | NO | | NULL | | | object | varchar(50) | NO | | NULL | | | create_at | datetime | NO | | NULL | | +-----------+------------------+------+-----+---------+----------------+
t2 存储消息类型
+--------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------------+------+-----+---------+----------------+ | code | int(10) unsigned | NO | PRI | NULL | auto_increment | | level | tinyint(3) unsigned | NO | MUL | NULL | | | type | varchar(30) | NO | | NULL | | | content | varchar(50) | NO | | NULL | | | status | tinyint(3) unsigned | NO | | NULL | | +--------------+---------------------+------+-----+---------+----------------+
现在需要对这两张表进行分页排序联查。
t1 的记录数 230万条,t2表200条
写了三种查询语句如下:
1)最慢 select t1.*, t2.* from t1, t2 where t1.code = t2.code order by t1.create_at desc limit 1065701,20; 耗时:14.48秒 2)使用left join SELECT t1.*,t2.level AS level, t2.content AS content FROM t1left join t2on t1.code=t2.code order by create_at desc limit 1065701,20; 耗时:9.71秒 3)先查缩小t1表中的范围,再和t2表进行联查 select t1.*, t2.level AS level, t2.content AS content FROM t1 left join t2 on t1.code=t2.code join (SELECT id from t1 order by create_at desc limit 1065721,20)t where t.id=t1.id; 耗时:1.41秒
总结:
在多表联查分页排序,最大的瓶颈在排序,因此我们应该最大化的优化排序。鉴于此,我们先在t1表中分页排序查出符合条件的id,然后使用这个结果集去查t1、并与t2进行联查获取所有需要的信息。
不知道还有没有更好的优化方式,还请大牛指导:)
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:mysql 两表联查分页排序效率优化 - Python技术站