php - 内存使用从数据库导出到 PHP 中的 csv

Question

我需要将数据从 mysql 导出到 csv。我必须从几个表中选择数据并将它们放入数组中，然后处理它们并将它们作为 .csv 返回给浏览器。我注意到数组消耗了大量的行。例如，我在数据库中导入了一个1.8M的.csv，然后我尝试从.csv中的数据库中导出此数据。 memory_get_peak_usage() 显示超过 128M 用于存储带有数据的数组。

例如这个小数组需要 700 多个字节:

$startMemory = memory_get_usage();  
        //get constant fields of the subscriber
        $data = array(array('subscriber_id' => 1315444, 'email_address' => 'test0@gmail.com',
                            'first_name' => 'Michael', 'last_name' => 'Allen'));
        echo memory_get_usage() - $startMemory;

因此即使导出数兆字节的数据，在 php 脚本中也需要数百兆字节的内存。有没有办法解决这个问题？表格:

    CREATE TABLE `subscribers` (
     `subscriber_id` int(10) unsigned NOT NULL auto_increment,
     `list_id` int(10) unsigned NOT NULL,
     `account_id` int(10) unsigned NOT NULL,
     `email_address` varchar(100) collate utf8_unicode_ci NOT NULL,
     `first_name` varchar(50) collate utf8_unicode_ci NOT NULL default '',
     `last_name` varchar(50) collate utf8_unicode_ci NOT NULL default '',
     `ip` int(10) unsigned default NULL COMMENT '\nThe ip address of the subscriber that we can get when he opens the \nthe email or subscribe using subsribe form.\nTheoretically it can be used to segment by Location (which is not correct if someone uses proxy).',
     `preferred_format` tinyint(4) NOT NULL default '0' COMMENT 'Preferred format of \n0 - HTML, \n1 -Text,\n2 - Mobile',
     `state` tinyint(4) NOT NULL default '1' COMMENT '1 - subscribed\n2 - unsubscribed\n3 - cleaned\n4 - not confirmed, it means the user subscribed but has not confirmed it yet.\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n',
     `cause_of_cleaning` tinyint(4) NOT NULL default '0' COMMENT '\nThis field is the cause of moving the subscriber to the \n0 - not used\n1 - spam complaint\n2 - hard bounce\n3 - several soft bounces',
     `date_added` datetime NOT NULL COMMENT 'The data when the subscriber was added. I suppose this field can be used in the conditions forming the segment',
     `last_changed` datetime NOT NULL,
     PRIMARY KEY  (`subscriber_id`),
     UNIQUE KEY `email_list_id` (`email_address`,`list_id`),
     KEY `FK_list_id` (`list_id`),
     CONSTRAINT `FK_list_id` FOREIGN KEY (`list_id`) REFERENCES `lists` (`list_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB 
    CREATE TABLE `subscribers_multivalued` (
     `id` int(10) unsigned NOT NULL auto_increment,
     `subscriber_id` int(10) unsigned NOT NULL,
     `field_id` int(10) unsigned NOT NULL,
     `value` varchar(100) collate utf8_unicode_ci NOT NULL,
     `account_id` int(10) unsigned NOT NULL COMMENT '\nThe identifier of the account',
     PRIMARY KEY  (`id`),
     KEY `subscriber_fk` (`subscriber_id`),
     KEY `field_fk` (`field_id`),
     CONSTRAINT `field_fk_string_multivalued` FOREIGN KEY (`field_id`) REFERENCES `custom_fields` (`field_id`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `subscriber_fk_multivalued` FOREIGN KEY (`subscriber_id`) REFERENCES `subscribers` (`subscriber_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB
CREATE TABLE `subscribers_custom_data_string` (
 `subscriber_id` int(10) unsigned NOT NULL,
 `field_id` int(10) unsigned NOT NULL,
 `value` varchar(255) collate utf8_unicode_ci NOT NULL,
 `account_id` int(10) unsigned NOT NULL COMMENT '\nThe identifier of the account',
 PRIMARY KEY  (`subscriber_id`,`field_id`),
 KEY `subscriber_fk` (`subscriber_id`),
 KEY `field_fk` (`field_id`),
 CONSTRAINT `field_fk_string` FOREIGN KEY (`field_id`) REFERENCES `custom_fields` (`field_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `subscriber_fk_string` FOREIGN KEY (`subscriber_id`) REFERENCES `subscribers` (`subscriber_id`) ON DELETE CASCADE ON UPDATE CASCADE
) 

还有其他字段表类似于数字、日期字符串表。对于他们来说，主键是 subscriber_id、field_id。

当查询失败时(比如我们有几个自定义字段):

SELECT subscribers.email_address, subscribers.first_name, subscribers.姓氏,GROUP_CONCAT(t1.value SEPARATOR '|') 作为颜色，GROUP_CONCAT(t2.value SEPARATOR '|') 作为语言FROM 订阅者LEFT JOIN subscribers_multivalued AS t1 ON subscribers.subscriber_id=t1.subscriber_id AND t1.field_id=112LEFT JOIN subscribers_multivalued AS t2 ON subscribers.subscriber_id=t2.subscriber_id AND t2.field_id=111哪里 (list_id=40)GROUP BY subscribers.email_address, subscribers.first_name, subscribers. 分组姓氏

它会返回这个:

test1000@gmail.com Michelle Bush 红色|红色|蓝色|蓝色 英语|西类牙语|英语|西类牙语而不是test1000@gmail.com 米歇尔·布什 红|蓝英语|西类牙语

感谢您提供任何信息。

Answer 1

仅使用两个表:

您的原始查询:

SELECT subscribers.email_address, 
       subscribers.first_name, 
       subscribers.last_name, 
       t1.value AS Languages 
  FROM subscribers 
  LEFT JOIN (SELECT subscriber_id, 
                    field_id, 
                    GROUP_CONCAT(value SEPARATOR '|') AS value 
               FROM subscribers_multivalued 
              WHERE field_id=37 
              GROUP BY subscriber_id, field_id
            ) AS t1 
         ON subscribers.subscriber_id=t1.subscriber_id 
        AND t1.field_id=37 
 WHERE (list_id=49) 
   AND (state=1)

给出了一个解释计划:

id  select_type  table                    type  possible_keys  key         key_len  ref    rows  Extra
1   PRIMARY      subscribers              ref   FK_list_id     FK_list_id  4        const  2     Using where
1   PRIMARY      <derived2>               ALL   NULL           NULL        NULL     NULL   5      
2   DERIVED      subscribers_multivalued  ALL   field_fk       field_fk    4               11    Using filesort

我的加入建议:

SELECT subscribers.email_address, 
       subscribers.first_name, 
       subscribers.last_name, 
       GROUP_CONCAT(t1.value SEPARATOR '|') AS Languages 
  FROM subscribers 
  LEFT JOIN subscribers_multivalued t1 
         ON subscribers.subscriber_id=t1.subscriber_id 
        AND t1.field_id=37 
 WHERE (list_id=49) 
   AND (state=1)
 GROUP BY subscribers.email_address, 
          subscribers.first_name, 
          subscribers.last_name

给出了一个解释计划:

id  select_type  table        type  possible_keys           key            key_len  ref                             rows  Extra
1   SIMPLE       subscribers  ref   FK_list_id              FK_list_id     4        const                           2     Using where; Using filesort
1   SIMPLE       t1           ref   subscriber_fk,field_fk  subscriber_fk  4        test.subscribers.subscriber_id  1      

虽然我只用非常少量的数据填充了这两个表，但这向我表明我的查询版本将对数据库更有效地执行，因为它没有使用您的查询生成的派生表。

其他表可以以几乎相同的方式链接到查询中，整个结果直接假脱机到一个 csv 文件，而不是用 PHP 进一步解析。

这应该会让您的运行速度更快，内存效率更高。

编辑

SELECT subscribers.email_address, 
       subscribers.first_name, 
       subscribers.last_name, 
       GROUP_CONCAT(DISTINCT t1.value SEPARATOR '|') AS Colors, 
       GROUP_CONCAT(DISTINCT t2.value SEPARATOR '|') AS Languages 
  FROM subscribers 
  LEFT JOIN subscribers_multivalued AS t1 
         ON subscribers.subscriber_id=t1.subscriber_id 
        AND t1.field_id=112 
  LEFT JOIN subscribers_multivalued AS t2 
    ON subscribers.subscriber_id=t2.subscriber_id 
   AND t2.field_id=37 
 WHERE (list_id=49) 
 GROUP BY subscribers.email_address, 
          subscribers.first_name, 
          subscribers.last_name

注意 GROUP_CONCAT() 函数中 DISTINCT 的使用