php - 使用 MySQL 巨大更新进行扩展

Question

我们刚刚构建了一个在午夜汇总其数据的系统。它必须遍历多个表组合才能汇总所需的数据。不幸的是，UPDATE 查询将永远持续下去。我们拥有我们预测用户群的 1/1000，每天仅使用我们的测试版用户就已经需要 28 分钟来汇总我们的数据。

由于主要延迟是UPDATE 查询，因此可能很难委派服务器来处理数据。优化数百万个 UPDATE 查询的其他选项有哪些？下面的代码是我的缩放问题吗？:

        $sql = "SELECT ab_id, persistence, count(*) as no_x FROM $query_table ftbl
                WHERE ftbl.$query_col > '$date_before' AND ftbl.$query_col <= '$date_end'
                GROUP BY ab_id, persistence";

        $data_list = DatabaseManager::getResults($sql);

        if (isset($data_list)){
            foreach($data_list as $data){

                $ab_id = $data['ab_id'];
                $no_x = $data['no_x'];
                $measure = $data['persistence'];

                $sql = "SELECT ab_id FROM $rollup_table WHERE ab_id = $ab_id AND rollup_key = '$measure' AND rollup_date = '$day_date'";
                if (DatabaseManager::getVar($sql)){
                        $sql = "UPDATE $rollup_table SET $rollup_col = $no_x WHERE ab_id = $ab_id AND rollup_key = '$measure' AND rollup_date = '$day_date'";
                                DatabaseManager::update($sql);
                } else {
                        $sql = "INSERT INTO $rollup_table (ab_id, rollup_key, $rollup_col, rollup_date) VALUES ($ab_id, '$measure', $no_x, '$day_date')";
                                DatabaseManager::insert($sql);
                }
            }
        }

Answer 1

在解决 SQL 扩展问题时，最好始终对有问题的 SQL 进行基准测试。在这种情况下，即使在 PHP 级别也很好，因为您在 PHP 中运行查询。

如果您的第一个查询可能会返回数百万条记录，那么您最好将该查询作为 MySQL 存储过程来运行。这将最大限度地减少必须在数据库服务器和 PHP 应用程序服务器之间传输的数据量。即使两者是同一台机器，您仍然可以实现显着的性能提升。

需要考虑的一些问题可能有助于解决您的问题:

• 如果没有 UPDATE 或 INSERT 语句，您的 SELECT 查询需要多长时间才能处理？
• 您的查询的分割百分比是多少 - 通过 SQL 选择以及 INSERT 和 UPDATE？使用该信息可以更轻松地帮助确定解决方案。
• 是否有可能存在更大的瓶颈，而这些瓶颈可能会解决您的性能问题？
• 是否有必要在 PHP 源代码级别而不是 MySQL 存储过程级别遍历您的数据？
• 是否有必要按程序迭代您的记录，或者是否有可能通过基于集合的操作来完成同样的事情？
• 您的 rollup_table 是否有覆盖 UPDATE 查询列的索引？
• 此外，SELECT 查询恰好在您的 UPDATE 查询似乎具有相同的 WHERE 子句之前运行。这似乎是一种冗余。如果您可以只运行 WHERE 子句一次，那么最大的瓶颈就会减少很多时间。

如果您不熟悉编写 MySQL 存储过程，则该过程非常简单。参见 http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx举个例子。 MySQL 在这方面也有很好的文档。存储过程是在 MySQL 数据库进程中运行的程序，在处理可能返回数百万行的查询时可能有助于提高性能。

基于集合的数据库操作通常比过程操作更快。 SQL 是一种基于集合的语言。您可以使用单个 UPDATE 语句更新数据库表中的所有行，即 UPDATE customers SET total_owing_to_us = 1000000 更新客户表中的所有行，而无需像您在示例代码中创建的那样创建编程循环。如果您有 100,000,000 个客户条目，基于集合的更新将明显快于程序更新。网上有很多有用的资源，您可以阅读相关内容。这是开始的 SO 链接:Why are relational set-based queries better than cursors? .