使用有限的内存处理来自mysql的大型结果集 [英] Handling large result set from mysql with limited memory

问题描述

我有一个大型数据库，其中包含1500个人的实验结果.每个人都有96个数据点.我编写了以下脚本来汇总然后格式化数据，以便分析软件可以使用它.起初一切都很好，直到我有500多人为止.现在我的内存不足了.

I have a large database that contains results of an experiment for 1500 individuals. Each individual has 96 data points. I wrote the following script to summarize and then format the data so it can be used by the analysis software. At first all was good until I had more than 500 individuals. Now I am running out of memory.

我想知道现在是否有人建议在不牺牲速度的情况下克服内存限制问题.

I was wondering if anyone has a suggestion on now to overcome the memory limit problem without sacrificing speed.

这是表格在数据库中的外观

This is how the table look in the database

fishId assayId allele1 allele2

fishId assayId allele1 allele2

14_1_1 1 A T

14_1_1 1 A T

14_1_1 2 A A

14_1_1 2 A A

$mysql = new PDO('mysql:host=localhost; dbname=aquatech_DB', $db_user, $db_pass);
$query = $mysql->prepare("SELECT genotyped.fishid, genotyped.assayid, genotyped.allele1, genotyped.allele2, fishId.sex, " .
"fishId.role FROM `fishId` INNER JOIN genotyped ON genotyped.fishid=fishId.catId WHERE fishId.projectid=:project");
$query->bindParam(':project', $project, PDO::PARAM_INT);
$query->execute();  

这是对数据库的调用.它从两个表中加入信息以构建我需要的文件.

So this is the call to the database. It is joining information from two tables to build the file I need.

 if(!$query){
    $error = $query->errorInfo();
    print_r($error);
} else { 
    $data = array();
    $rows = array();
    if($results = $query->fetchAll()){
        foreach($results as $row)
        {
            $rows[] = $row[0];
            $role[$row[0]] = $row[5];
            $data[$row[0]][$row[1]]['alelleY'] = $row[2];
            $data[$row[0]][$row[1]]['alelleX'] = $row[3];
        }
        $rows = array_unique($rows);
        foreach($rows as $ids)
        {
            $col2 = $role[$ids];
            $alelleX = $alelleY = $content = "";
            foreach($snp as $loci)
            {
                $alelleY = convertAllele($data[$ids][$loci]['alelleY']);
                $alelleX = convertAllele($data[$ids][$loci]['alelleX']);
                $content .= "$alelleY\t$alelleX\t";
            }
            $body .= "$ids\t$col2\t" . substr($content, 0, -1) . "\n";

这将解析数据.在文件中，我需要每个人只有一行，而不是每个人只有96行，这就是为什么必须格式化数据的原因.在脚本的最后，我只是将$ body写入文件.

This parses the data. In the file I need I have to have one row per individual rather than 96 rows per individual, that is why the data has to be formatted. In the end of the script I just write $body to a file.

我需要输出文件

FishId分析1分析2

FishId Assay 1 Assay 2

14_1_1 A T A A

14_1_1 A T A A

$location = "results/" . "$filename" . "_result.txt";
$fh = fopen("$location", 'w') or die ("Could not create destination file");
if(fwrite($fh, $body))

推荐答案

与其使用fetchAll()将数据库查询的整个结果读取到变量中，不如逐行读取它:

Instead of reading the whole result from your database query into a variable with fetchAll(), fetch it row by row:

while($row = $query->fetch()) { ... }

这篇关于使用有限的内存处理来自mysql的大型结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！