MySQL --- group by 分组 取最大值,百万数据查询0.8s- 1s

group by 分组 取最大值

  1. 网上查询的方法
  2. 我们想要取出来 u_id分组,z值最大的每一条数据。

    我只是筛选了u_id = 1 的数据,此时id为2 的这列数据为最大的数据

方法一:

. select id,url,z from (
       select *
       from show_video
       order by z desc 
     ) t
group by t.u_id

在这里插入图片描述
方法一的结果不是我们想要的数据。
此时的数据 为 1 用户的 id为1的数据。 结果是不可取的。

方法二:

select a.id,a.z,a.u_id from show_video a inner join (select u_id,max(z) score from show_video group by u_id)b on a.
u_id=b.u_id and a.z = b.score GROUP BY u_id

在这里插入图片描述
这个方法查询出来的结果是OK的。 时间在0.810s - 0.914s之间。

总结:方法一:
查询 group by 分组之后又默认的id排序机制。
导致的出来数据不是我们分组所需要的数据。
方法二: 查询的效率还可以,如果大家有什么意见和建议,可以提出来,不知道这种方式是不是最优的,一起学习实践呗。

发布了7 篇原创文章 · 获赞 0 · 访问量 1804
展开阅读全文

mysql 百万级数据 sql优化

05-06

百万级数据,要求3s内出结果,索引都加上了 贴上sql ``` SELECT T.lockbalance_status AS lockbalanceStatus, T.order_merinstalltype AS orderMerinstalltype, T.order_id AS orderId, T.serverUserId, date_format( T.order_time, '%Y-%m-%d %H:%i:%s' ) AS orderTime, T.order_no AS orderNo, T.is_project_order AS isProjectOrder, T.STATUS, T.user_id AS userId, T.insert_user AS userName, T.serverUserId AS serverUserName, T.organ_name AS organName, T.leadingoffice_name AS leadingofficeName, T.order_type AS orderType, T.is_self_paying AS isSelfPaying, T.account_name AS accountName, T.account_mobile AS accountMobile, T.account_address AS accountAddress, T.province_id AS provinceId, T.city_id AS cityId, T.area_id AS areaId, T.install_userid AS installUserName, date_format( T.install_time, '%Y-%m-%d %H:%i:%s' ) AS installTime, date_format( T.order_completetime, '%Y-%m-%d %H:%i:%s' ) AS completetime, T.is_ergent AS isErgent, date_format( T.allocationtime, '%Y-%m-%d %H:%i:%s' ) AS allocationtime, date_format( T.install_time_again, '%Y-%m-%d %H:%i:%s' ) AS installTimeAgain, date_format( T.assign_again_time, '%Y-%m-%d %H:%i:%s' ) AS assignAgainTime, T.dealOver AS dealOver, date_format( T.alarm_time_again, '%Y-%m-%d %H:%i:%s' ) AS alarmTimeAgain, date_format( T.order_takingtime, '%Y-%m-%d %H:%i:%s' ) AS orderTakingtime, date_format( T.logistics_time, '%Y-%m-%d %H:%i:%s' ) AS logisticsTime, T.is_logistics_time AS isLogisticsTime, T.firstperson AS firstPersonId, T.firstperson AS firstPersonName, T.program_id AS programId, T.program_name AS programName, T.program_manager AS programManager, date_format( T.insert_time, '%Y-%m-%d %H:%i:%s' ) AS programTime, date_format( T.p_setProject_time, '%Y-%m-%d' ) AS setProjectTime, date_format( T.noSettlementTime, '%Y-%m-%d %H:%i:%s' ) AS noSettlementTime, T.installremark AS installRemark, T.lockSmithRemark AS lockSmithRemark, T.order_remark AS orderRemark, T.audit_status AS auditStatus, CASE WHEN T.firstperson = 'cdbcd902dbe411e89c777cd30ae00302' THEN 0 WHEN T.serverUserId = 'cdbcd902dbe411e89c777cd30ae00302' THEN 1 ELSE 2 END AS flag FROM t_order T WHERE T.is_project_order = '0' AND T.del_status = '0' ORDER BY flag, T.order_time DESC LIMIT 10 ``` 截图为原来执行计划 ![图片说明](https://img-ask.csdn.net/upload/201905/06/1557121468_320238.png) 根据各位大神提供的方案,优化了一波,贴上优化后的执行计划 ![图片说明](https://img-ask.csdn.net/upload/201905/06/1557125323_506958.png) 但是依旧达不到速度要求 flag 是业务需要,客户希望与自己有关的数据优先展示 目前执行下来sql需要10s多 求各位大神支招,在线等。 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览