开发者社区 > 数据库 > 关系型数据库 > 正文

PolarDB的order by + limit 不走列存储查询,怎么解决?

PolarDB的order by + limit 不走列存储查询,怎么解决? SELECT t1.sell_record_id id,t1.sell_record_id,t1.sell_record_code,t1.deal_code_list,t1.sale_channel_code,t1.store_code,t1.shop_code,t1.express_code,t1.delivery_time,t1.plan_send_time,t1.record_time,t1.pay_time,t1.buyer_name,t1.express_no,t1.express_money,t1.payable_money,t1.paid_money,t1.fx_express_money,t1.fx_payable_money,t1.goods_weigh,t1.goods_num,t1.pay_type,t1.is_back,t1.pay_status,t1.order_status,t1.shipping_status,t1.must_occupy_inv,t1.lock_inv_status,t1.is_pending,t1.is_problem,t1.is_combine_new,t1.is_split_new,t1.is_change_record,t1.is_handwork,t1.is_copy,t1.sale_mode,t1.is_fenxiao,t1.is_rush,t1.is_replenish,t1.order_remark,t1.store_remark,t1.buyer_remark,t1.seller_remark,t1.seller_flag,t1.order_type,t1.is_hx,t1.cn_date,t1.return_status,t1.receiver_name,t1.receiver_phone,t1.receiver_mobile,t1.receiver_address,t1.is_bj,t1.bj_je,t1.invoice_status,main.transport_mode,main.virtual_store_code FROM oms_sell_record t1 LEFT JOIN oms_sell_record_main main ON t1.sell_record_code=main.record_code WHERE t1.shop_code IN('tb002','12697403','13961149','14632902','10688625','10805738','10688722','11665749','13706526','14034891','10783567','14765793','12597064','14398503','13552442','13074907','12854030','12697486','12707476','13552464','13236068','13400475','14594666','14590426','14993880','14994782','15016784','jd034','ht036','ht037','15035749','15070232') AND (t1.store_code IN ('WXCK001','WXCK002','WXCK003','WXCK004','WXCK005','WXCK006','WXCK007','WXCK008','SZCK001','JSJCK001','JSJCK002','JSJCK003','JSJCK004','JSJCK005','JSJCK006','JSJCK007','WXCK001-70','SZCK001-20','CGTEST') OR t1.store_code='' OR t1.store_code is NULL) AND t1.is_problem = 1 AND t1.order_status = 0 ORDER BY t1.record_time DESC LIMIT 0,1000

展开
收起
三分钟热度的鱼 2024-03-20 17:06:17 40 0
2 条回答
写回答
取消 提交回答
  • PolarDB优化ORDER BY+LIMIT的策略通常依赖于匹配的B-tree索引。由于ORDER BY t1.record_time DESC,您需要一个record_time列上的降序索引,且空值排在前面。您可以尝试创建这样一个索引:

    CREATE INDEX idx_record_time_desc ON oms_sell_record (record_time DESC NULLS LAST);
    

    创建此索引后,查询优化器可能会利用它直接获取排序后的前1000行,而无需全表扫描和额外排序。索引维护会占用存储空间,并可能影响写操作性能,因此需权衡查询优化与额外开销。可看下官方文档
    image.png

    2024-07-08 16:38:47
    赞同 展开评论 打赏
  • order by上有innodb索引,同时有limit,代价模型是根据经验公式计算查询代价的,这条sql算出来的代价是2600。show status like 'last_query_cost_for_imci';

    行存实际执行时间是4s,cost算的偏低。已经优化了,再试试吧。此回答整理自钉群“PolarDB专家面对面 - HTAP(列存索引) ”

    2024-03-20 17:16:17
    赞同 1 展开评论 打赏

阿里云关系型数据库主要有以下几种:RDS MySQL版、RDS PostgreSQL 版、RDS SQL Server 版、PolarDB MySQL版、PolarDB PostgreSQL 版、PolarDB分布式版 。

相关产品

  • 云原生数据库 PolarDB
  • 相关电子书

    更多
    云栖大会:开源 PolarDB 架构演进、关键技术与社区建设 立即下载
    2023云栖大会:和客户一起玩转PolarDB新特性 立即下载
    2023云栖大会:PolarDB for AI 立即下载