mysql 5.7.16-ucloudrel1-log 索引 order by desc 执行慢

知识问答作者:U大使日期:2021-07-15点击:54

mysql版本:
image.png
表:

CREATE TABLE `heart_rate_analysis` (  `autoid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键'  `id` varchar(50) DEFAULT NULL COMMENT 'uuid'  `user_id` int(10) unsigned NOT NULL COMMENT '用户id'  ...  ... -- 其他非索引列   ...  `measurement_date` datetime NOT NULL COMMENT '测量开始时间'  `date_stamp` int(10) unsigned NOT NULL COMMENT '年月日'  `updated` bigint(20) unsigned NOT NULL COMMENT '更新时间'  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'  PRIMARY KEY (`autoid``create_time`)  UNIQUE KEY `uniq_userId_dateStamp_createTime` (`user_id``date_stamp``create_time`)  KEY `idx_userId_measurementDate` (`user_id``measurement_date`)  KEY `idx_userId_updated` (`user_id``updated`)  KEY `idx_updateTime` (`update_time`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=4573938 DEFAULT CHARSET=utf8;-- 使用create_time创建了按月的分区,PARTITION BY RANGE (to_days(create_time))
数据量大小3.3G

慢sql(执行时间超过1S):

SELECT * FROM heart_rate_analysisWHERE user_id = '214984' AND date_stamp < '20210713'AND silent_heart_rate != 0ORDER BY date_stamp desc LIMIT 7
注:该慢sql 在5.6.20-ucloudrel1-log版本中,是正常耗时的

排序修改为asc(执行时间小于100ms):

SELECT * FROM heart_rate_analysisWHERE user_id = '214984' AND date_stamp < '20210713'AND silent_heart_rate != 0ORDER BY date_stamp asc LIMIT 7
或者替换排序字段measurement_date desc,也是正常耗时

explain:
image.png

image.png
sql执行耗时图:
image.png

image.png

结论:
到底是bug 还是设置了什么呢?

你们这个发布问题有bug,发布出来只展示了部分内容,[email protected]

慢sql(执行时间超过1S):

SELECT * FROM heart_rate_analysisWHERE user_id = '214984' AND date_stamp < '20210713'AND silent_heart_rate != 0ORDER BY date_stamp desc LIMIT 7
注:该慢sql 在5.6.20-ucloudrel1-log版本中,是正常耗时的

排序修改为asc(执行时间小于100ms):

SELECT * FROM heart_rate_analysisWHERE user_id = '214984' AND date_stamp < '20210713'AND silent_heart_rate != 0ORDER BY date_stamp asc LIMIT 7
或者替换排序字段measurement_date desc,也是正常耗时

explain:
image.png

image.png
sql执行耗时图:
image.png

image.png

结论:
到底是bug 还是设置了什么呢?

下一篇       上一篇