已使用索引但MariaDB查询仍缓慢，求优化建议

原始查询信息 #

执行情况 #

查询耗时约10秒，返回5k+条记录。

原始查询语句 #

select
    sum(dnpi_qty * ((so_item_qty - so_item_delivered_qty) / so_item_qty))
from
    (select stock_qty as dnpi_qty, qty as so_item_qty,
            delivered_qty as so_item_delivered_qty, so_item.parent, so_item.name
     from `tabSales Order Item` so_item USE INDEX (item_code_warehouse_delivered_by_supplier_qty_delivered)
     where item_code = 'BA-0' and warehouse = 'W1'
       and so_item.delivered_by_supplier = 0
       and so_item.qty > so_item.delivered_qty
       and exists (select name from `tabSales Order` so where so.name=so_item.parent
               and so.docstatus = 1
               and so.status in ('On Hold','To Deliver','To Deliver and Bill')
       )
    ) tab
where
    so_item_qty >= so_item_delivered_qty;

环境信息 #

• MariaDB版本：10.4.14-MariaDB-1:10.4.14+maria~bionic-log
• tabSales Order Item表结构：

CREATE TABLE `tabSales Order Item` (
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `creation` datetime(6) DEFAULT NULL,
  `modified` datetime(6) DEFAULT NULL,
  `modified_by` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `owner` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `docstatus` int(1) NOT NULL DEFAULT 0,
  `parent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parentfield` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `item_code` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `item_name` varchar(350) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `base_net_amount` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `net_rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `stock_uom` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `base_price_list_rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `warehouse` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `delivered_qty` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `price_list_rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `transaction_date` date DEFAULT NULL,
  `item_group` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `planned_qty` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `amount` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `customer_item_code` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `net_amount` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `returned_qty` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `target_warehouse` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ordered_qty` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `supplier` varchar(140) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `delivered_by_supplier` int(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`),
  KEY `prevdoc_docname` (`prevdoc_docname`),
  KEY `parent` (`parent`),
  KEY `modified` (`modified`),
  KEY `item_code_warehouse_delivered_by_supplier` (`item_code`,`warehouse`,`delivered_by_supplier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED 

具体优化措施 #

1. 优化tabSales Order Item的索引 #

当前查询的过滤条件是item_code、warehouse、delivered_by_supplier，同时需要qty、delivered_qty、parent、stock_qty字段进行计算和关联。现有的item_code_warehouse_delivered_by_supplier索引仅包含过滤字段，导致查询需要回表读取额外数据。

创建覆盖索引避免回表：

CREATE INDEX idx_so_item_filter_cover ON `tabSales Order Item` 
(item_code, warehouse, delivered_by_supplier, qty, delivered_qty, parent, stock_qty);

• 覆盖索引包含了查询所需的所有字段，数据库无需回表即可获取数据，大幅减少IO开销。
• 删除查询中的USE INDEX强制指定，让优化器自动选择最优索引。

2. 简化查询逻辑 #

• 外层so_item_qty >= so_item_delivered_qty条件已被内层qty > delivered_qty包含，可直接删除，减少不必要的过滤步骤。
• 移除冗余子查询，直接在主查询中计算，同时将exists子查询的SELECT name改为SELECT 1（exists仅需判断存在性，无需返回具体字段）：

优化后的查询语句：

SELECT SUM(stock_qty * ((qty - delivered_qty) / qty))
FROM `tabSales Order Item` so_item
WHERE item_code = 'BA-0' 
  AND warehouse = 'W1'
  AND delivered_by_supplier = 0
  AND qty > delivered_qty
  AND EXISTS (
      SELECT 1 
      FROM `tabSales Order` so 
      WHERE so.name = so_item.parent
        AND so.docstatus = 1
        AND so.status IN ('On Hold','To Deliver','To Deliver and Bill')
  );

3. 优化tabSales Order表的索引 #

exists子查询需要通过name关联父订单，并过滤docstatus和status字段，创建联合索引加速子查询：

CREATE INDEX idx_so_parent_status ON `tabSales Order` (name, docstatus, status);

该索引可以让数据库快速定位符合条件的父订单，避免全表扫描。

4. 更新表统计信息 #

确保优化器能基于最新的表数据生成最优执行计划，执行以下语句：

ANALYZE TABLE `tabSales Order Item`;
ANALYZE TABLE `tabSales Order`;

内容的提问来源于stack exchange，提问作者Jonathan F Lie