目前正在使用视图，我应该使用硬表吗？

Question

我目前正在争论我的表mapping_uGroups_uProducts 是否是由下表形成的视图：

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 
    SQL SECURITY DEFINER VIEW `db`.`mapping_uGroups_uProducts` 
    AS select distinct `X`.`upID` AS `upID`,`Z`.`ugID` AS `ugID` from 
    ((`db`.`mapping_uProducts_Products` `X` join `db`.`productsInfo` `Y` 
            on((`X`.`pID` = `Y`.`pID`))) join `db`.`mapping_uGroups_Groups` `Z` 
            on((`Y`.`gID` = `Z`.`gID`)));

我当前的查询是：

    SELECT upID FROM uProductsInfo \
        JOIN fs_uProducts USING (upID) column \
        JOIN mapping_uGroups_uProducts USING (upID) -- could be faster if we use hard table and index \
        JOIN mapping_fs_key USING (fsKeyID)  \
    WHERE fsName="OVERALL"  \
        AND ugID=1          \
    ORDER BY score DESC     \
    LIMIT 0,30;

这非常慢。 （对于 30 个结果，大约需要 10 秒）。我认为我的查询如此慢的原因肯定是由于该特定查询依赖于没有索引来加快速度的视图。

+----+-------------+----------------+--------+----------------+---------+---------+---------------------------------------+-------+---------------------------------+
        | id | select_type | table          | type   | possible_keys  | key     | key_len | ref                                   | rows  | Extra                           |
        +----+-------------+----------------+--------+----------------+---------+---------+---------------------------------------+-------+---------------------------------+
        |  1 | PRIMARY     | mapping_fs_key | const  | PRIMARY,fsName | fsName  | 386     | const                                 |     1 | Using temporary; Using filesort | 
        |  1 | PRIMARY     | <derived2>     | ALL    | NULL           | NULL    | NULL    | NULL                                  | 19706 | Using where                     | 
        |  1 | PRIMARY     | uProductsInfo  | eq_ref | PRIMARY        | PRIMARY | 4       | mapping_uGroups_uProducts.upID        |     1 | Using index                     | 
        |  1 | PRIMARY     | fs_uProducts   | ref    | upID           | upID    | 4       | db.uProductsInfo.upID                 |   221 | Using where                     | 
        |  2 | DERIVED     | X              | ALL    | PRIMARY        | NULL    | NULL    | NULL                                  | 40772 | Using temporary                 | 
        |  2 | DERIVED     | Y              | eq_ref | PRIMARY        | PRIMARY | 4       | db.X.pID                              |     1 | Distinct                        | 
        |  2 | DERIVED     | Z              | ref    | PRIMARY        | PRIMARY | 4       | db.Y.gID                              |     2 | Using index; Distinct           | 
        +----+-------------+----------------+--------+----------------+---------+---------+---------------------------------------+-------+---------------------------------+
        7 rows in set (0.48 sec)

这里的解释看起来非常神秘，我不知道是否应该删除视图并编写一个脚本以将视图中的所有内容插入到硬表中。 （显然，由于映射变化非常频繁，它将失去视图的灵活性）。

有谁知道如何更好地优化我的架构？

Answer 1

您当前的计划使用视图作为驱动表：使用 fsName = 'OVERALL' 扫描 mapping_fs_key 中的每条记录

您可以使用以下函数替换视图：

SELECT  upID FROM uProductsInfo
JOIN    fs_uProducts USING (upID)
JOIN    mapping_fs_key USING (fsKeyID)
WHERE   fsName='OVERALL'
        AND upID IN
        (
        SELECT  upID
        FROM    mapping_uGroups_Groups Z
        JOIN    productsInfo Y
        ON      y.gID = z.gID
        JOIN    mapping_uProducts_Products X
        ON      x.pID = y.pID
        WHERE   z.ugID = 1
        )
ORDER BY
        score DESC
LIMIT 0,30