我有一个MySQL InnoDB表,其中有100万行,我选择了100K行进行导出。表有大约200列。
到目前为止我做了什么:
-
不使用*全选
选择列1、列2。。。FROM my_table WHERE deleted=0--加载100k条记录
-
使用带有flush 的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++; }
但我仍然有致命错误:允许的内存大小为。。。字节已用尽
还有其他方法可以优化吗?我想我可能可以用其他方式从数据库加载数据,比如在第一轮中只加载id,然后选择in(10k_ids),但我还没有测试这个想法。
谢谢你的意见。
我有一个与如何导出具有100K记录的HTML表非常相似的问题,该表具有数字格式,而不会耗尽内存
但是没有办法实现低内存消耗。
我找到了解决方案,问题是我加载了很多数据。
我进行了3次升级:
-
使用函数记录内存限制
/** * @param $message */ protected function logMemoryUsage($message) { Debugger::log($message . ": " . memory_get_usage()/1048576 ." MB"); }
-
然后我使用fopen+fwrite+fclose而不是file_put_contents
$file = fopen($this->getFilePath(), 'a+'); fwrite($file, $writer->flush(TRUE)); fclose($file);
-
循环加载数据(一次仅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