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

Question

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

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

有两种可能性

在视图结果的末尾，

1. 发生了一些变化。在这种情况下，我将只将新记录插入到视图中的table
2. Something中，而不是在中心视图结果中更改的all_inventory_table中。在本例中，我将截断表并将整个数据从视图转储到表.

。

我的问题是

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

另外，我不能向底层表写入触发器，因为基础表将不知道更改是指向视图结果的结尾还是位于视图结果的中心。

下面是获取信息的视图。

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

从重新安排事情开始。似乎每个内部选择都加入到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，只关注于收集导致更新的数据。然后看看这是否能成为你展示给我们的“大视图”的基础。