sql

select 'false' as QUERYID, id, uid, order_id, start_position, end_position, reply_order_price, finish_order_price, member_level, scene_id, member_score_type, member_score, rob_result_time, record_type, gmt_create, gmt_modify, is_deleted, is_member, pk_result 
from member_rob_order_record 
WHERE  is_deleted = 0 and uid = 7916485614431901 and rob_result_time between '2024-11-19 16:56:46' and '2024-12-19 16:56:46' ;

现状

当前已存在的索引是 uid&rob_result_time
慢sql监控显示很多sql需要100ms以上

调整一

因为只需要数量&价格，所以只返回少量信息，修改sql如下

select count(1) as count,sum(finish_order_price) as orderPrice from member_rob_order_record where uid = 7951668099743255 AND rob_result_time >= '2024-11-26 11:40:11' AND rob_result_time <= '2024-12-26 11:40:11' and record_type = 1 and finish_order_price >0 AND is_deleted = 0;

观察

发现sql慢查现象并未减少，并且发现，uid&rob_result_time 命中的数据有800+条，故这800多条均需要回表，为了减少回表，建以下索引

ALTER TABLE member_rob_order_record ADD INDEX idx_uid_time_type_status_price (uid,is_deleted,record_type,rob_result_time,finish_order_price);
  • rob_result_time 是范围查询，故在后面

再次观察

D-Chat_20241226120357

无慢查，故问题出在回表上