MySQL : Storing business logic in table

Question

我有一个名为 test 的表:

create table demo (name varchar(10), mark1 int, mark2 int);

我多次需要每一行的 mark1 和 mark2 的总数。

select name, (mark1 + mark2) as total from demo;

我被告知效率不高。我不允许在表中添加新的总计列。

我可以将这样的业务逻辑存储在索引中吗？

我创建了一个 View

CREATE VIEW view_total AS SELECT name, (mark1 + mark2) as 'total' from demo;

我用以下内容填充了演示表:

DELIMITER $$
CREATE PROCEDURE InsertRand(IN NumRows INT)
    BEGIN
        DECLARE i INT;
        SET i = 1;
        START TRANSACTION;
        WHILE i <= NumRows DO
            INSERT INTO demo VALUES (i,i+1,i+2);
            SET i = i + 1;
        END WHILE;
        COMMIT;
    END$$
DELIMITER ;

CALL InsertRand(100000);

执行时间

select * from view_total;

和

select * from demo;

相同，10 毫秒。所以我没有获得任何好处。我尝试在 View 上创建索引:

create index demo_total_view on view_total (name, total);

因错误而失败:

ERROR 1347 (HY000): 'test.view_total' is not BASE TABLE

关于如何防止对列进行总计的冗余操作的任何指示？

Answer 1

作为一般规则，永远不要将您可以在退出时计算的内容存储在表中。例如，你想要年龄，你应该存储出生日期。如果你想要两列的总和，你应该存储这两列，没有别的。

维护数据库中的数据完整性、质量和一致性应该是您最关心的问题。如果第三列(前两列之和)可能不同步，那么这样做就不值得了。

因为如果不将计算嵌入到将数据插入表中的所有代码中(将来可能会被遗忘并且更新可能会破坏它)或每次插入内容时都触发触发器(大量额外的工作)，您将无法维护该列) 你不应该这样做。

您的情况是 View 的完美用例。您需要以相同的方式始终如一地计算列。如果你让每个人都按照他们的意愿计算这个，那么就会出现与插入计算列相同的问题，你需要保证它总是以相同的方式计算。执行此操作的方法是在您的表上创建一个 View ，该 View 以标准方式预先计算列，这对每个用户都是相同的。

Calculating a sum hundreds of time would be much costlier then reading it from somewhere... right?

不一定，这个完全看你自己的情况。如果你有较慢的磁盘，那么读取数据可能很容易比计算它更昂贵。特别是因为这是一个极其简单的计算。

很可能它根本没有任何区别，但如果它是一个主要的性能问题，您应该测试这两种情况并确定潜在的数据质量损失和维护表中计算的额外开销是否值得从数据库中提取奇数纳秒。