网页设计与网站建设在线第二章,wordpress路由规则,dw软件免费下载,目前最火的互联网项目背景#xff1a;人脸闸机#xff0c;每刷一次人脸#xff0c;就会有一条记录插入到通行记录表。而闸机可能会多次识别同一个人的人脸#xff0c;那么这时通行记录表就会插入多次同一个人的记录#xff0c;同一个人的记录中#xff0c;只不过通行时间不同而已
需求#…背景人脸闸机每刷一次人脸就会有一条记录插入到通行记录表。而闸机可能会多次识别同一个人的人脸那么这时通行记录表就会插入多次同一个人的记录同一个人的记录中只不过通行时间不同而已
需求查询出最新的5个人的通行记录信息通行记录表100w条数据mysql语法编写
你会怎么写这条sql
原sql
SELECTa.*
FROMykt_ryface_records a
INNER JOIN ( SELECT max( record_time ) AS record_time, person_id, max( data_id ) id FROM ykt_ryface_records GROUP BY person_id
) AS c ON a.data_id c.id
ORDER BY record_Time DESC
LIMIT 0,5
ykt_ryface_records 通行记录表 data_id 主键 person_id 刷脸人的id record_time 通行时间
这条sql的意思是子查询中根据刷脸人id分组获取最大通行时间最大的主键id 作为一个c表然后再匹配通行记录表a这么写会导致全表扫描肯定慢的执行了几十秒才有结果
本想着在c表中加一个 limit 500应该也可以了当如果4个人刷了500条数据呢那么会导致最终查询出来的数据不准确。虽然现实中这个需求不可能出现。毕竟刷脸刷几次不行他早走别的通道去了。
后来想着在结合java代码程序执行就是执行查500条数据的sql返回结果集判断结果集是否有5条数据如果没有再查500条一直到满足5条数据为止。这样是可行的但感觉不爽我喜欢一条sql搞定一切 优化的sql本来没写出来去趟洗手间回来就写出来了哈哈
SELECTa.*
FROMykt_ryface_records aINNER JOIN ( SELECT a.person_id,a.person_name,idStr,count,dataid idfrom ykt_ryface_records aINNER JOIN (select idStr:0,count:0,dataid:0) b on (IF(find_in_set(IFNULL(a.person_id,-999),idStr)0,concat(idStr:CONCAT(idStr,,,IFNULL(a.person_id,-999)),count:(LENGTH(idStr) - LENGTH(REPLACE(idStr,,,))),dataid:a.data_id),-1) 0 ) order by a.record_Time DESC LIMIT 5) AS c ON a.data_id c.id
ORDER BY record_Time DESC
精华都在c表中的inner join以前我都是在select 列名这用临时变量灵机一动在inner join 的on条件后也可以用。c表的sql大概意思是降序查询每条记录然后对每条记录的的刷脸人id主键id放到临时变量中。查询逻辑如先查第一条记录记录了相关id这时候的条件是on 00 然后查第二条发现和第一条记录的相关id一样就返回-1那么条件就是 on -1 0因为是内连接所以第二条记录就没有了。按照这样的逻辑一次类推巧妙使用limit 5就找出5个人的最新数据了。然后再作为c表关联a表查询就避免了全表扫描了。该sql执行时间在0.1秒以下 感想
巧妙利用临时变量解决很多复杂的sql查询提高效率。
