阿里RDS一张200w的订单表,部分数据查询快,部分数据查询慢,求解?

laozhong__ 发布于 2018/08/28 08:49
阅读 1K+
收藏 1

查询字段是个datetime格式的create_time,有索引,全部都是按照主键id倒序排,如果去除id倒序排的话没有问题:

基本格式如:SELECT * FROM `tb_orders` WHERE `create_time` BETWEEN '2018-01-21 00:00:00' AND '2018-01-21 23:59:59'  ORDER BY `id` DESC

1. `create_time` between '2018-01-21 00:00:00' and '2018-01-23 23:59:59' 耗时2.343s 结果集22099条

2. `create_time` between '2018-01-21 00:00:00' and '2018-01-21 23:59:59' 耗时2.390s 结果集8956条

3. `create_time` between '2018-07-21 00:00:00' and '2018-07-23 23:59:59' 耗时0.250s 结果集15403条

4. `create_time` between '2018-07-21 00:00:00' and '2018-07-21 23:59:59' 耗时0.015s 结果集5638条

给我的感觉,是1月份的数据量差不多,但是慢很多,7月份的快很多!其他月份也有快有慢!

表数据是187w多,40多个字段,pma显示大小为数据440MB,索引256MB,innodb引擎;

这会是什么原因呢!

 

explain如下,为何会有filesort :

EXPLAIN SELECT *  FROM `tb_orders`  WHERE `create_time`  BETWEEN '2018-01-21 00:00:00' AND '2018-01-21 23:59:59' ORDER BY id DESC

 

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tb_orders range create_time create_time 5 NULL 8955 Using index condition; Using filesort
Status Duration CPU_user CPU_system Context_voluntary Context_involuntary Block_ops_out Page_faults_minor Source_function Source_file Source_line
Creating sort index 0.109387 0.073989 0.028996 366 8 26424 274 sort_table sql_executor.cc 2519

以下是问题补充:

@laozhong__:// 1. 改用create_time排序,能解决查询慢的问题 tips at 2018-08-28 09:15 (2018/08/28 09:16)
@laozhong__:// 2. 如果只查询id列,或者create_time列,速度快,没问题,我补充了profiling数据 tips at 2018-08-28 11:24 (2018/08/28 11:25)
@laozhong__:// 3. 我感觉现有的回答都没用很好的解释,1、4月份的数据慢,其他的快,当然17年的几个月就更加慢了,尽管量还少些!说缓存也对吧,但是这么点数据量,实在不应该啊!tips at 2018-08-28 15:35 (2018/08/28 15:35)
加载中
2
_snake_
_snake_

订单ID使用 10位数的时间镞 加上固定位数的随机数

查询时候时间转成时间镞补0站位,根据ID卡范围,千万级别数据也是毫秒级查询 

自增ID卵用没有

_snake_
_snake_
回复 @laozhong__ : 保证主索引顺序的唯一的,还能根据日期定位就成了,百分之80的查询都搞的定
_snake_
_snake_
回复 @laozhong__ : 看我如下的截图,或者你新建一张表和你的订单表关联,应该很轻松根据日志卡订单范围。
laozhong__
laozhong__
那直接加一列时间戳,或者create_time用时间戳形式
2
_snake_
_snake_

 

模拟数据是2018-05-30至2018-10-05

共计11100040条

查询数据范围内任意一天 ,内网这个开发用的库比较繁忙0.42秒

laozhong__
laozhong__
感谢你这么认真的回答,你可以将ordersn列加上索引,用一样的查询方式,试试效率怎么样!
红薯官方
红薯官方
看见你这么认真就给你个TOP,其实这种ID为日期时间戳的办法也是不错的,如果嫌这个显示麻烦,那么就加一列订单Code做业务编码给外部用吧。
1
k
kaelgo

id是自增的,create_time也是自增的,那就用order by create_time desc来排序 应该会比较好

laozhong__
laozhong__
是的,可以这样,速度也上来了,create_time是根数据一起写入的,也能保证排序,可以还是不懂啊,为什么还会出现这个情况?explain也没有filesort了
1
k
kaelgo

可以用profiling具体看看

laozhong__
laozhong__
看了下就是Creating sort index不太正常,我上面贴出来了。 网上总结说:在使用列的默认排序时,不应该再order by该列
1
OSC_YIdCag
OSC_YIdCag

SELECT t1.* FROM `tb_orders` t1 INNER JOIN (SELECT `id` FROM `tb_orders` WHERE `create_time` BETWEEN '2018-01-21 00:00:00' AND '2018-01-21 23:59:59') t2 ON t1.id=t2.id ORDER BY t1.id DESC

OSC_YIdCag
OSC_YIdCag
回复 @laozhong__ : BETWEEN AND 确实会影响效率,建议用 >= 和 < 来处理。
OSC_YIdCag
OSC_YIdCag
回复 @蓝水晶飞机 : 数据库真的是很笨,在索引上的处理还是有一些技巧的,之前在千万级的表内查很慢,但是用索引先查出聚集索引数据,然后在调出所有的数据是非常快的,不信可以试试看。
laozhong__
laozhong__
我这是后台管理用的,所以都是直接查
laozhong__
laozhong__
01-21 AND 01-23 耗时0.1719秒 01-21 AND 01-21 耗时0.0781秒 01-21 AND 01-28 耗时0.4687秒 explain 显示 table<derived2> rows123090 Extra(Using temporary; Using filesort)
红薯官方
红薯官方
你的想法应该是按日期筛选处理主键的数据然后再提取结果吧,缩小计算的数据量。数据库怎么就那么笨呢,没有像你这么干的。
0
hzajie
hzajie

大哥,你是用RDS吗,RDS你要知道,不是你一个人在用,当RDS集群负载高的时候,你的查询自然

laozhong__
laozhong__
回复 @WeiYongxin : 感觉只能这么解释了,因为我发现量少的17的查询更慢!
游侠v
7月份的数据区块频繁使用已经缓存了,1月份使用频率低,没缓存?
laozhong__
laozhong__
胸弟,这不能解释吧,因为我每次查1月份的数据就慢,7月份的就快!
0
魔力猫
魔力猫

因为MySQL只能使用一个索引,已经用了createtime进行范围查询了,order 的又是另一个字段,索引无效。

接下来的速度其实就是看数据分布情况和内存情况了。日期近的,大概率也被别的业务调用,所以大概率内存有缓存。返回数量少或者正好当时内存比较空余,在内存中运算不使用或者少使用临时文件,就快一些。

魔力猫
魔力猫
回复 @laozhong__ : 你可以试试。不过因为排序用的是索引第二列,这个能不能被有效使用真的无法保证。MySQL的索引,用惯Oracle的强大,真的受不了。
laozhong__
laozhong__
是否建立create_time、id的联合索引会好些呢? 我也怀疑分布情况,缓存应该不是,我都查了n多次了
0
j
jfinal_lag

ID有建索引吗

laozhong__
laozhong__
id是自增主键啦
0
sxgkwei
sxgkwei

和数据库应该是一个原理吧。就是排在前面的(或因为缓存机制或因为排序)就查起来快,后面的就慢的很。

laozhong__
laozhong__
可是查询多次依然慢,甚至2、3月快,4月又慢了,然后5、6、7、8都快
0
qycms_cn
qycms_cn
加上sql_no_cache测试才准,同时细看表结构,有没有分区!
laozhong__
laozhong__
没有分区,同时鄙视阿里云rds平台的索引优化建议,公司直接就一键操作了
返回顶部
顶部