php - mysql 中处理大量数据的最快查询

Question

我遇到了 mysql 查询的问题。查询运行但有时会使 php 超出最大内存。我有大约 80 或 90.000 行坐标，包括发动机速度和其他信息。我必须创建 KML 文件才能单独显示路线。如果发动机转速不为空，则汽车正在行驶，如果为空，则汽车停止。表中一半的发动机转速包含 0。当我迭代记录时，在创建路由数组后，我也会同时删除记录，但它运行速度非常慢，有时会耗尽内存。是因为数据库数据量大还是代码中存在逻辑错误？这是代码:

public function getPositions($device_id) {
        $db = connect_database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, DB_PORT);
        $sql = "SELECT * FROM coordinates_log WHERE imei=:imei ORDER BY device_time ASC";
        $statement = $db->prepare($sql);
        $statement->execute(array(':imei' => $device_id));
        $positions = array();
        $delete_sql = "DELETE FROM coordinates_log WHERE id=:id";
        $delete_statement = $db->prepare($delete_sql);
        $counter = 0;
        $flag = 0;
        while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
            //here I flag the last started route
            if ($row['vehicle_engine_speed'] <= 0) {
                $flag = $counter;
            }
            $positions[] = $row;
            $counter++;
        }

        if (!empty($positions)) {
            $last_key = count($positions)-1;
            //here I check if the route is completed yet, or he is on his way
            if ($positions[$last_key]['vehicle_engine_speed'] != 0) {

                for($i = $flag; $i<=$last_key; $i++){
                    unset($positions[$i]);
                }
            }
            foreach ($positions as $position) {
                $delete_statement->execute(array(':id' => $position['id']));
            }
            return $positions;
        } else {
            return FALSE;
        }
    }

Answer 1

PHP 中的 PDO 子系统提供两种查询:缓冲查询和非缓冲查询。如果您没有专门请求非缓冲查询，那么您将获得缓冲查询。缓冲查询会消耗 PHP 引擎中更多的 RAM，因为 PDO 将整个结果集提取到 RAM 中，然后在您使用 $statement->fetch() 时一次将其返回给您的程序一行。

因此，如果您的结果集非常大并且您可以一次处理一行，那么在无缓冲模式下您将使用更少的 RAM。您处理每一行，然后获取下一行，而不是尝试将它们同时保存在 RAM 中。

这是有关无缓冲模式的文章。

http://php.net/manual/en/mysqlinfo.concepts.buffering.php

缓冲模式通常更易于程序员使用，因为 PDO 从每个查询中读取整个结果集并隐式关闭语句对象。即使您尚未处理结果集中的所有信息，这也会使您的连接可用于下一个 sql 语句。使用无缓冲模式，如果您想在处理结果集时运行其他 mysql 语句，则需要另一个数据库连接来执行此操作。

您应该为您的SELECT * FROM坐标...结果集尝试无缓冲模式。

专业提示:如果您避免使用SELECT *，而是使用SELECT col, col, col，您可能可以减少查询的开销，特别是如果您不这样做的话实际上需要所有列。