sqxp/backend/scripts/add_delivery_tasks_perf_indexes.sql

-- /api/web/delivery-tasks 列表性能索引补强
-- 目标查询特征：
--   1) WHERE is_del=0 ORDER BY create_at DESC（默认列表）
--   2) WHERE is_del=0 AND region_id=? AND status=? ORDER BY create_at DESC
--   3) WHERE is_del=0 AND deliver_id=? ORDER BY create_at DESC
--   4) 列表中按 task_id 聚合明细数量（biz_delivery_task_item）

-- 1) 默认列表与时间倒序分页
ALTER TABLE biz_delivery_task
  ADD INDEX idx_dt_isdel_createat (is_del, create_at);

-- 2) 区域 + 状态过滤场景
ALTER TABLE biz_delivery_task
  ADD INDEX idx_dt_isdel_region_status_createat (is_del, region_id, status, create_at);

-- 3) 配送员维度过滤场景
ALTER TABLE biz_delivery_task
  ADD INDEX idx_dt_isdel_deliver_createat (is_del, deliver_id, create_at);

-- 4) 供应商维度过滤场景（供应商端任务列表）
ALTER TABLE biz_delivery_task
  ADD INDEX idx_dt_isdel_supplier_createat (is_del, supplier_id, create_at);

-- 5) 列表 itemCount 聚合：WHERE task_id IN (...) AND is_del=0 GROUP BY task_id
ALTER TABLE biz_delivery_task_item
  ADD INDEX idx_dti_task_isdel (task_id, is_del);