MySQL-基于数据库视图修改/填充表数据

Question

我有一个Spring引导和Mysql应用程序。对于其中一个页面，我从一个名为all_inventory的视图中获取数据。该视图很复杂，包含对多个表和列的动态排序。问题是，随着数据量的增加，页面加载时间呈指数级增长。

为了提高页面加载，我创建了一个中间表，调用all_inventory_table，并计划使用计划存储过程将视图中的数据填充到表中。Spring将读取此表而不是视图，从而提高了性能。

有两种可能性

1. 视图结果的末尾出现了一些变化。在这种情况下，我只需将视图中存在但表中不存在的新记录插入到all_inventory_table中
2. 观察中心发生了一些变化。在这种情况下，我将截断表，并将整个数据从视图转储到表

我的问题是

检查视图结果中更改内容的最佳方法是什么。基本上，我想在视图中添加触发器，但MySQL不支持。我尝试使用联接编写选择查询，但执行起来需要很多时间。

此外，我不能将触发器写入底层表，因为底层表将不知道更改是朝向视图末尾结果还是在视图中心结果。

以下是的信息视图

CREATE OR replace VIEW all_inventory
AS
SELECT row_number()
over (
ORDER BY tx.date desc, tx.type desc, tx.keyid desc) as id,
tx.type,
tx.keyid,
tx.entryid,
tx.date, -- index
tx.contactid,
tx.warehouseid,
tx.Productid,
tx.quantity,
tx.closingstock,
tx.creationDate,
tx.lastModifiedDate,
tx.Product_name, -- index
tx.category_name, -- index
tx.measurementunit,
c.name,
c.mobileno,
c.emailid,
c.contacttype,
tx.warehouse_id,
tx.warehousename -- index
FROM   (SELECT 'Inward'                         AS type,
ii.inwardid                      as keyid,
ioe.entryid                      as entryid,
Date_format(ii.DATE, "%Y-%m-%d") AS date,
ii.contactid                     AS contactid,
ii.warehouse_id                  AS warehouseid,
ioe.Productid                    AS Productid,
ioe.quantity,
ioe.closingstock,
ioe.creationDate,
ioe.lastModifiedDate,
p.Product_name,
cat.category_name,
p.measurementunit,
w.warehouse_id,
w.warehousename
FROM   inward_inventory ii
inner join inwardinventory_entry iie
ON ii.inwardid = iie.inwardid
inner join inward_outward_entries ioe
ON iie.entryid = ioe.entryid
inner join Product p
on p.Productid = ioe.Productid
INNER JOIN Category cat
on p.categoryId = cat.categoryId
inner join Warehouse w
ON w.warehouse_id = ii.warehouse_id
WHERE  ii.is_deleted = 0
UNION ALL
SELECT 'Outward'                        AS type,
oi.outwardid                     as keyid,
ioe.entryid                      as entryid,
Date_format(oi.DATE, "%Y-%m-%d") AS date,
oi.contactid                     AS contactid,
oi.warehouse_id                  AS warehouseid,
ioe.Productid                    AS Productid,
ioe.quantity,
ioe.closingstock,
ioe.creationDate,
ioe.lastModifiedDate,
p.Product_name,
cat.category_name,
p.measurementunit,
w.warehouse_id,
w.warehousename
FROM   outward_inventory oi
inner join outwardinventory_entry oie
ON oi.outwardid = oie.outwardid
inner join inward_outward_entries ioe
ON oie.entryid = ioe.entryid
inner join Product p
on p.Productid = ioe.Productid
INNER JOIN Category cat
on p.categoryId = cat.categoryId
inner join Warehouse w
ON w.warehouse_id = oi.warehouse_id
WHERE  oi.is_deleted = 0
UNION ALL
SELECT 'Lost-Damaged'                    AS type,
lostdamagedid                     as keyid,
lostdamagedid                     as entryid,
Date_format(ldi.DATE, "%Y-%m-%d") AS date,
''                                AS contactid,
ldi.warehousename                 AS warehouseid,
ldi.Productid                     AS Productid,
ldi.quantity,
ldi.closingstock,
ldi.creationDate,
ldi.lastModifiedDate,
p.Product_name,
cat.category_name,
p.measurementunit,
w.warehouse_id,
w.warehousename
FROM   lost_damaged_inventory ldi
inner join Product p
on p.Productid = ldi.Productid
INNER JOIN Category cat
on p.categoryId = cat.categoryId
inner join Warehouse w
ON w.warehouse_id = ldi.warehousename
where  ldi.is_deleted = 0) AS tx
left join contacts c
ON c.contactid = tx.contactid;

Answer 1

从重新排列开始。似乎每个内部Selects都连接到Warehouse以获得名称，只是为了获得warehousename。因此，移除那些内部连接，并将一个副本移动到外部选择。(Contacts已经这样工作了。((也许其他一些Joins也可以类似地重新排列？(

这一点以及以下大部分内容旨在加快查询速度。尽管它可能不会让你达到以某种方式做"；更新"；，这可能是值得做的。

你真的需要ROW_NUMBER吗？它增加了复杂性，可能是查询中成本最高的部分。

其中一些索引可能有助于提高性能：

ii:  INDEX(is_deleted, inwardid,  `DATE`, contactid, warehouse_id)
oi:  INDEX(is_deleted, outwardid, `DATE`, contactid, warehouse_id)
ldi:  INDEX(is_deleted, Productid)

要进一步了解，我们需要查看SHOW CREATE TABLE(至少要了解索引(以及您需要什么"；更新"；。

如果ii.DATE属于DATE类型，则将Date_format(ii.DATE, "%Y-%m-%d")简化为ii.DATE。如果它的类型是DATETIME，则简化为DATE(ii.DATE)。

至于使视图可更新(或达到目标的其他所需路径(，请考虑如何编写最小VIEW，只关注收集导致更新的数据然后看看这是否可以成为你向我们展示的大视图的基础。