php - 使用 PDO 获取列类型(getColumnMeta is/slow/)

Question

尝试编写一些东西以自动将任意数据库结果(即并非总是来自表 x)转换为适当的 PHP 类型结果。

我扩展了 PDOStatement 类，

class Statement extends PDOStatement {
    protected $pdo;
    protected $transformer;

    protected function __construct(PDO $pdo) {
        $this->pdo = $pdo;
        $this->transformer = $pdo->getTransformer();
    }

    public function fetchAll() {
        $results = parent::fetchAll(PDO::FETCH_ASSOC);

        if ($this->getTransformer()) $results = $this->completeResults($results);

        return $results;
    }

    private function completeResults(array $results = []) {
        if ($results == null || count($results) == 0) return null;
        if ($results[0] == false || !is_array($results[0])) return null;

        $index = 0;
        $typeMap = [];

        foreach ($results[0] as $column => $result) {
            $meta = $this->getColumnMeta($index); // this is very painful
            $typeMap[$column] = $meta['native_type'];
            $index++;
        }

        $transformer = $this->getTransformer();
        foreach ($results as $index => &$result) {
            array_walk($result, function(&$value, $key) use ($typeMap, $transformer) {
                $type = $typeMap[$key];
                $value = $transformer->transformToPhpValue($value, $type);
            });
        }

        return $results;
    }
}

以前，在我意识到 PDO 抽象之前，我一直在使用(在我的特定情况下)标准 pg_...() 方法。使用 pg_field_type($resource, $column);，我可以获取列类型，而且速度相对较快。

现在，使用新的(对我而言)PDO 方法。如果我注释掉我进行转换的代码部分，并运行 7 个连续查询:

time to complete: 9.5367431640625E-7 seconds 
time to complete: 1.1920928955078E-6 seconds 
time to complete: 9.5367431640625E-7 seconds 
time to complete: 0 seconds 
time to complete: 9.5367431640625E-7 seconds 
time to complete: 0 seconds 
time to complete: 0 seconds

启用它:

time to complete: 0.5777850151062 seconds 
time to complete: 0.49124097824097 seconds 
time to complete: 0.28375911712646 seconds 
time to complete: 0.5946729183197 seconds 
time to complete: 0.42177200317383 seconds 
time to complete: 5.0067901611328E-6 seconds 
time to complete: 0.42121982574463 seconds 

那是/疯了/。

通过查看我的 Postgres 日志，我可以知道它正在一个接一个地获取列信息:

LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=1114
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=1114
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=25
... like 30 more of these ...
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=25
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=23
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=23
LOG:  statement: SELECT TYPNAME FROM PG_TYPE WHERE OID=23

查询的复杂程度从

SELECT 
    p.modified_at, ... ~ 30 fields ..., r.level AS id_level
FROM table_p AS p
LEFT JOIN table_a AS a ON (p.owner = a.id)
LEFT JOIN table_a0 AS a0 ON (p.reporter = a0.id)
LEFT JOIN table_r AS r ON (p.id = r.id)
WHERE (p.id = 1)

只是 SELECT * FROM table_a AS a;

所以，我想问题是:有没有更好的方法来做到这一点？有没有一种方法可以在不影响代码速度的情况下执行此操作？ 7 个查询处于每个请求运行的连续查询的低端，因此这是我想要处理的事情。

Answer 1

首先，PDOStatement::getColumnMeta() 是实验性的，所以在使用它时要非常小心(希望你会设置自动测试以使用任何 php/pdo 版本更新)。

至于检索元数据的速度，我进行了一些测试，结果表明 SELECT TYPNAME FROM PG_TYPE WHERE OID=% 查询的运行速度极快:

explain analyze SELECT TYPNAME FROM PG_TYPE WHERE OID=25;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_type_oid_index on pg_type  (cost=0.27..8.29 rows=1 width=64) (actual time=0.051..0.055 rows=1 loops=1)
   Index Cond: (oid = 25::oid)
 Planning time: 0.165 ms
 Execution time: 0.100 ms
(4 rows)

explain analyze SELECT TYPNAME FROM PG_TYPE WHERE OID=1114;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_type_oid_index on pg_type  (cost=0.27..8.29 rows=1 width=64) (actual time=0.083..0.085 rows=1 loops=1)
   Index Cond: (oid = 1114::oid)
 Planning time: 0.192 ms
 Execution time: 0.139 ms
(4 rows)

explain analyze SELECT TYPNAME FROM PG_TYPE WHERE OID=600;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_type_oid_index on pg_type  (cost=0.27..8.29 rows=1 width=64) (actual time=0.063..0.064 rows=1 loops=1)
   Index Cond: (oid = 600::oid)
 Planning time: 0.261 ms
 Execution time: 0.125 ms
(4 rows)

PG 选择该数据大约需要 0.0001 秒，即使将其中的 30 个相加也不会在 0.5 秒或类似的时间内求和。

我建议您在服务器上为 pg_type 查询运行 explain analyze 并查看那里的时间安排。

打赌你没有使用与数据库的持久连接，这会增加元数据调用的大量时间。