PHP、MySQL - 加载大约 100k 记录并将其导出到 xml

Question

我有一个包含 100 万行的 MySQL InnoDB 表，我选择了 100K 行进行导出。表大约有 200 列。

到目前为止我做了什么:

1. 不选择所有带*的

   SELECT column1, column2, ... FROM my_table WHEREdeleted=0 -- 加载 10 万条记录

2. 使用带有刷新功能的 XMLWriter php 库

   $writer = new XMLWriter();
   $writer->openMemory();
   $writer->setIndent(true);
   $writer->startDocument('1.0', 'UTF-8');
   $writer->startElement('export');
   
   $iterator = 0;
   $data = $this->getData();
   
   foreach($adverts as $advert) {
       $writer->startElement('ad');
       $writer->writeElement('id', $data->id);
       // .. other columns
   
       $writer->endElement(); // end ad
   
       if (0 == $iterator % 1000) {
           file_put_contents($this->getFilePath(), $writer->flush(TRUE), FILE_APPEND);
       }
   
       $iterator++;
   }
   

但我仍然有 fatal error :允许的内存大小...字节耗尽

还有其他方法可以优化吗？我想我也许可以通过其他方式从数据库加载数据，比如在第一轮中仅加载 ids，然后选择 IN (10k_ids)，但我还没有测试这个想法。

感谢您的意见。

<小时/>

我有与 How to export HTML table with 100K records with number formatting without memory exhaust 中非常相似的问题

但是没有办法实现低内存消耗。

Answer 1

我找到了解决方案，问题是我加载了很多数据。

我进行了 3 次升级:

1. 使用函数记录内存限制

   /**
    * @param $message
    */
   protected function logMemoryUsage($message)
   {
       Debugger::log($message . ": " . memory_get_usage()/1048576 ." MB");
   }
   

2. 然后我使用 fopen + fwrite + fclose 而不是 file_put_contents

   $file = fopen($this->getFilePath(), 'a+');
   fwrite($file, $writer->flush(TRUE));
   fclose($file);
   

3. 循环加载数据(一次仅加载10k条记录)

   $this->logMemoryUsage("Memory usage before load");
   $data = $this->getData($lastId);
   
   do {
       $this->logMemoryUsage("Memory usage");
       $lastId = NULL;
   
       foreach($data as $item) {
           $writer->startElement('ad');
           $writer->writeElement('id', $item->id);
           ...
   
           if (0 == $iterator % 5000) {
               fwrite($file, $writer->flush(TRUE));
           }
   
           $lastId = $item->id;
           $iterator++;
       }
   
       $data = $this->getData($lastId);
   
   } while(!empty($data));
   
   $this->logMemoryUsage("Memory usage after");
   fwrite($file, $writer->flush(TRUE));
   fclose($file);
   
   
   
   public function getData($fromId = NULL, $limit = 10000)
   {
       $data = db::query("SELECT a,b,c FROM my_table WHERE deleted=0 AND id>? ORDER BY id ASC LIMIT ?", $fromId, $limit)->fetchAll();
   }
   

现在的输出是:

    export start
    Memory usage before load: 3.6202011108398 MB
    Memory usage: 59.487106323242 MB
    Memory usage: 124.53610229492 MB
    Memory usage: 124.89745330811 MB
    Memory usage: 124.43883514404 MB
    Memory usage: 124.20503234863 MB
    Memory usage: 124.2151184082 MB
    Memory usage: 124.46990966797 MB
    Memory usage: 106.50185394287 MB
    Memory usage: 53.009048461914 MB
    export end