MySQL-SQL-优化：优化 case when 转为 union

一、SQL样例

1、原始SQL

select
*
from
(
select
     merchant.type,
    merchant.barCode,
    merchant.merchant_id,
    case
      when (
        store.itemTitle is null
        or store.itemTitle = ''
      ) then merchant.itemTitle
      else store.itemTitle
    END AS itemTitle
from
(
select
     *
from
    producer
where
merchant_id = 10007
and store_id = 0
) merchant
left join (
select
    *
from
    producer
where
merchant_id = 10007
and store_id = 20007
) store on merchant.barCode = store.barCode
) t
where
1 = 1
AND t.itemTitle like concat('%', '士尼-哈哈', '%')
AND t.type = 1
limit
0, 10;

2、执行计划

 

二、优化分析

1、分析

1）改写SQL

可将case where 拆分成 uion 

2）新建索引

有like 查询，可以使用索引下推方法来新建索引，优化慢SQL

2、改写SQL

select
*
from
(
select
merchant.type,
merchant.barCode,
merchant.merchant_id,
store.itemTitle
from
(
select
    *
from
    producer
where
merchant_id = 10007
and store_id = 0
) merchant
join (
select
    *
from
    producer store
where
merchant_id = 10007
and store_id = 20007
and itemTitle like concat('%', '士尼-哈哈', '%')
and type = 1
) store on merchant.barCode = store.barCode
) t
union all
select
*
from
(
select
merchant.type,
merchant.barCode,
merchant.merchant_id,
merchant.itemTitle
from
(
select
    *
from
    producer
where
merchant_id = 10007
and store_id = 0
and itemTitle like concat('%', '士尼-哈哈', '%')
and type = 1
) merchant
left join (
select
    *
from
    producer store
where
merchant_id = 10007
and store_id = 20007
and (store.itemTitle is null
or store.itemTitle = '')
) store on merchant.barCode = store.barCode
) t
limit
0, 10;

3、新建索引

alter table producer  add index idx_store_id(store_id,merchant_id,itemTitle);

4、执行计划