mysql - Mysql函数耗时太长

Question

我有一个名为“item”的表，大约有 8500 行，还有另一个名为“stock”的表，该表按日期和位置存储商品库存(我有 2 个库存位置)。

Stock表结构如下:

stock_dt date           - Stores the Date of Stock,
loc_id varchar(3)       - Stores the Location of Stock,
item_id varchar(6)      - Stores the Item Code,
item_qty decimal(20,2)  - Stores the Item Stock Quantity,
item_cost decimal(20,2) - Stores the Item Stock Cost Value

每当任何商品在商店之间发生移动时，都会在库存表中插入一行，其中包含特定商品的更新库存数据。目前 Stock 表包含大约 125,000 行。

现在我有一个函数，它接受两个日期参数(adt_from 和 adt_to)，并检索两个日期每个位置的每个商品的库存，即每个位置的商品的开盘和期末库存，并对库存进行一些其他计算。 ..

下面是该函数花费异常长时间(几乎 15 分钟)的部分。

” -- 变量声明

declare cur_item cursor for
select item_id
from   item
order by item_id;

declare cur_loc cursor for
select loc_id
from   location;

declare continue handler for not found set not_found = true;

set not_found = false;

open cur_item;

fetch cur_item into s_item;

read_item:
loop
    if not_found then
        leave read_item;
    end if;

    set d_opng_qty = 0;
    set d_opng_cost = 0;
    set d_cls_qty = 0;
    set d_cls_cost = 0;

    open cur_loc;

    fetch cur_loc into s_loc;

    read_loc:
    loop
        if not_found then
            leave read_loc;
        end if;

        -- For Opening Stock:
        select item_qty,
               item_cost
        into   d_opng_qty,
               d_opng_cost
        from   stock
        where  stock_dt < adt_from
        and    loc_id = s_loc
        and    item_id = s_item
        order by stock_dt desc
        limit 1;

        -- For Closing Stock:
        select item_qty,
               item_cost
        into   d_cls_qty,
               d_cls_cost
        from   stock
        where  stock_dt <= adt_to
        and    loc_id = s_loc
        and    item_id = s_item
        order by stock_dt desc
        limit 1;

        fetch cur_loc into s_loc;
    end loop;

    fetch cur_item into s_item;      
end loop;

"

Stock 表根据 stock_dt、loc_id 和 item_id 建立索引。

我尝试启用query_cache并将query_cache_size设置为128M，但似乎没有任何改善。

我对 Stock 上的 2 个查询进行了评论，程序仅在几秒钟内就完成了其余的执行。有什么建议吗？

Answer 1

我猜这里的问题是由那些 ORDER BY 引起的。

假设您有一个关于(项目、位置、日期)的复合索引，我认为您需要

ORDER BY item desc, location desc, date desc

为了使搜索从正确的末尾开始并在 1 次命中后返回。

原因是，即使它将命中一个项目的复合索引(项目、位置、日期)，它也会以最重要列的方向遍历索引(隐式 ORDER BY 项目 ASC、位置 ASC) 。因此，它需要扫描该项目的整个部分历史记录以获得最新记录。

我会尝试注释掉 ORDER BY 并查看这是否是导致问题的原因。