mysql 优化order by limit 订单例子

使用表：订单表 order （100w+），商品表goods  ， 用户表 user 

优化前

 

SELECT o.order_id, u.username,g.title,g. STATUS,g.id AS gid,g.price,o.goods_num,(g.price * o.goods_num) AS totalPrice,o.w_time,u.phone,u.id AS uids,u.role,o.id 
 FROM self_order o 
 inner JOIN `self_user` `u` ON `u`.`uid` = `o`.`uid` 
 inner JOIN `self_goods` `g` ON `g`.`id` = `o`.`goods_id` 
where `o`.`id` in (select id from `self_order` ORDER BY `o`.`order_id` DESC, `o`.`w_time` DESC LIMIT 0, 15);



 
 

 

 

优化后

 SELECT o.order_id,u.username,g.title,g. STATUS,g.id AS gid,g.price,o.goods_num,(g.price * o.goods_num) AS totalPrice,o.w_time,u.phone,u.id AS uids,u.role,o.id 
 FROM self_order o 
 INNER JOIN (select id from self_order ORDER BY `order_id` DESC,`w_time` DESC LIMIT 0,15) as so on so.id = o.id 
 inner JOIN `self_user` `u` ON `u`.`uid` = `o`.`uid` 
 INNER JOIN `self_goods` `g` ON `g`.`id` = `o`.`goods_id`;

 

 

之前查询12s主要是因为查询主表100w+数据后在将这100+数据分别和self_user和self_goods进行连表查询，而优化的之后的sql语句是将15条数据和self_user和self_goods表里的数据进行连接，自然速度要快很多。所以，sql查询的时候一定要有where语句，并将where语句里的字段增家索引。

 

原文：https://blog.csdn.net/qq_33226422/article/details/80451284