请问这两段条件有什么不同吗，查询效率相差好大

条件 1：
(user_credit_change.pre_status is null and user_credit_change.after_status is null) or (user_credit_change.pre_status = 0 and user_credit_change.after_status = 30) or (user_credit_change.pre_status = 0 and user_credit_change.after_status = 41)

条件 2：
(user_credit_change.pre_status = 0 and user_credit_change.after_status = 41)

条件 1 耗时 100ms，条件 2 耗时 20 多 s，哪位大神可以解惑下，谢谢啦

条件

请问

相差

查询

6 replies • 2019-10-10 10:46:11 +08:00

msaionyc

Oct 9, 2019 via Android

explain

lituancheng

Oct 9, 2019

@msaionyc explain 的结果是一样的

lituancheng

Oct 9, 2019

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user NULL ALL NULL NULL NULL NULL 15 10.00 Using where; Using temporary; Using filesort
1 SIMPLE user_credit_apply NULL ref PRIMARY,uniq_user_status uniq_user_status 258 cashloan.user.uuid 10 50.00 Using index condition
1 SIMPLE user_credit_change NULL eq_ref uniq_start_identifier,uniq_end_identifier uniq_end_identifier 259 cashloan.user_credit_apply.request_id,const 1 5.00 Using where
1 SIMPLE user_id_card NULL eq_ref uniq_user uniq_user 134 cashloan.user_credit_apply.user_uuid,const 1 100.00 Using index condition

lituancheng

Oct 9, 2019

@lituancheng explain 结果

wangyzj

Oct 9, 2019

你这问的没法回答啊

Aresxue

Oct 10, 2019

两条 sql 都不是等价的。感觉像是 null 导致的问题，但是没有执行计划也说不准