请教店铺订单 SQL 优化

This topic created in 1436 days ago, the information mentioned may be changed or developed.

数据库用的是 mysql8.0 ，explain 如下

场景：带关键词分页查询某个店铺的订单，截图里 explain 的 SQL 是统计符合要求的订单总数，

表的关联关系如下：

订单表 o 会关联订单明细表 oi ，

订单明细表 oi 会关联商品表 p ，

订单表 o 会在非注册用户下单以及注册用户下单情况下，根据 account_id 是否有值关联到客户表 c 的不同字段，因为这个原因，不想写成 or 查询，这个会导致客户表 c 全表扫描，所以把 SQL 拆成了 union 的形式

订单表 o 会关联到收货地址表 a

从监控上看，这个 SQL 大概耗时在 600~700ms 左右，这个店铺的订单总数大概在 5k 左右，想问下有什么办法再优化这个 SQL 到 500ms 以下么？

感谢。

Supplement 1 · Jul 5, 2022

完整SQL如下：

explain
select count(distinct tmp.id)
        from (
        select o.id, o.created_date
        from `order` o, order_item oi, product p, customer c
        where o.deleted=false
            and ((o.account_id>0 and o.account_id=c.account_id))
            and o.shop_id=12345
            and o.id=oi.order_id and oi.product_id=p.id
            and
             (  
                (
                o.id like "%"'abc'"%"
                or o.contact_information like "%"'abc'"%"
                or c.last_name like "%"'abc'"%"
                or c.email like "%"'abc'"%"
                or p.title like "%"'abc'"%"
                
                or exists (
                    select refer_id from address a where  o.id=a.refer_id
                        and ( a.last_name like "%"'abc'"%"
                            or a.address like "%"'abc'"%")
                    )
                )
             ) 

        union all

        select o.id, o.modified_date
        from `order` o, order_item oi, product p, customer c
        where o.deleted=false
            and o.account_id=0 and o.customer_id=c.id
            and o.shop_id=12345
            and o.id=oi.order_id and oi.product_id=p.id
            and
             (  
                (
                o.id like "%"'abc'"%"
                or o.contact_information like "%"'abc'"%"
                or c.last_name like "%"'abc'"%"
                or p.title like "%"'abc'"%"
                or exists (
                    select refer_id from address a where  o.id=a.refer_id
                        and ( a.last_name like "%"'abc'"%"
                            or a.address like "%"'abc'"%")
                    )
                )
             ) 
        ) tmp

SQL

订单

关联

明细表

10 replies • 2022-07-06 19:01:22 +08:00