背景:
简单罗列下之前做过的优化,人懒。
所以没能把所有的整理统一放出来。
情况:
系统查询过慢,没能命中索引。
<!--对账单数量 -->
<
select
id
=
"selectCount"
resultType=
"java.lang.Integer"
parameterType=
"map"
>
select
count(*) from monthly_statements where store_id =
#{storeId}
<
if
test
=
"billNo != null"
>
and bill_no =
#{billNo}
<
/if
>
<
if
test
=
"beginTime != null and endTime != null"
>
and date_format(created_at,
"%Y-%m-%d"
) between
#{beginTime,jdbcType=VARCHAR}
and
#{endTime,jdbcType=VARCHAR}
<
/if
>
<
if
test
=
"isDeadline == 0"
> <!-- 未到账期时间 -->
and expiration_time > now()
<
/if
>
<
if
test
=
"isDeadline == 1"
> <!-- 已到账期时间 -->
and expiration_time <= now()
<
/if
>
<
/select
>
|
案例:
- 优化前
select * from cc where date_format(dd,
'%Y-%m-%d'
)=
"2016-02-22"
;
|
- 优化后
select * from orders where `created_at` >
'2016-02-22'
and `created_at` < date_add(
'2016-02-22'
,interval
1
day);
|