了解MySQL的内存使用情况导致PHP（PDO）

I am trying to understand why the memory usage for a single PDO result is so high. Here are a few things to know about the query/result:

我试图理解为什么单个PDO结果的内存使用率如此之高。以下是有关查询/结果的一些信息：

I am pulling a single VARCHAR(6) column from a single table
我从单个表中提取单个VARCHAR（6）列
The result is less than 30K rows
结果不到30K行
This fetching this result uses ~12MB of memory in PHP (source: memory_get_usage)
获取此结果在PHP中使用~12MB的内存（源：memory_get_usage）
If I json_encode the result and dump it to a file, the actual data (in text form) is only ~1MB
如果我对结果进行json_encode并将其转储到文件中，那么实际数据（文本形式）只有~1MB
Using PHP7, MySQL 5.7, deployed on Ubuntu 14.04.
使用部署在Ubuntu 14.04上的PHP7，MySQL 5.7。

My question is, where exactly does the 11MB of bloat come in? If the actual data in text form is only about 1MB, then 11MB seems like a lot of overhead just to parse the data in PHP. Is there a reason for this? Am I missing something?

我的问题是，11MB的膨胀到底在哪里？如果文本形式的实际数据只有大约1MB，那么11MB似乎只是为了解析PHP中的数据而花费很多。是否有一个原因？我错过了什么吗？

Edit:

编辑：

Just to clarify, I am looking for a technical explanation as to why the bloat exists, not a workaround for the issue.

只是为了澄清，我正在寻找技术解释为什么存在膨胀，而不是问题的解决方法。

2 个解决方案

#1

Ok, so thanks to Ollie Jones's answer, I realized I've been looking in the wrong place for my information. This isn't a PDO memory usage issue but an issue with the way PHP stores its arrays. (Maybe issue isnt the right word, it is what it is)

好的，感谢Ollie Jones的回答，我意识到我一直在寻找错误的地方以获取我的信息。这不是PDO内存使用问题，而是PHP存储其数组的方式的问题。（也许问题不是正确的词，它是什么）

After a bit of digging I found this incredibly helpful article which gives a great breakdown of how PHP allocates memory for array elements:

经过一番挖掘后，我发现了这篇非常有用的文章，它给出了PHP如何为数组元素分配内存的一个很好的细分：

https://nikic.github.io/2011/12/12/How-big-are-PHP-arrays-really-Hint-BIG.html

Spoiler alert, it uses a TON of memory for each element. Apparently it has gotten a lot better in PHP7. The article states that for a simple integer array (in PHP5) element it will use about 18 times the more memory than the size of the integer itself. Since i'm seeing about a 12* increase on associative string data, id say that is indeed a vast improvement over PHP5.

扰流器警报，它为每个元素使用TON内存。显然它在PHP7中已经变得更好了。文章指出，对于一个简单的整数数组（在PHP5中），它将使用大约18倍于整数本身大小的内存。因为我看到关联字符串数据增加了12 *，所以id说这确实是对PHP5的巨大改进。

According to the article, memory is being allocated for the following:

根据这篇文章，内存正在分配给以下内容：

zvalue_value union, which relates to the weak typecasting that PHP allows
zvalue_value union，它涉及PHP允许的弱类型转换
Type storage and garbage collection data
输入存储和垃圾回收数据
The Zend Memory Manager allocation
Zend内存管理器分配
Hash table buckets
哈希表桶

If you had some interest in this as well, I highly recommend reading that article, its a quick read and has a lot of great information.

如果您对此也有兴趣，我强烈建议您阅读该文章，快速阅读并提供大量重要信息。

Thanks again to Ollie Jones for pointing me in the right direction.

再次感谢Ollie Jones指出我正确的方向。

#2

It's hard to give a specific answer without seeing your specific code. That being said, PHP data structures like arrays are associative. The PHP designers intentionally made a tradeoff to use extra RAM to save time on array access.

如果没有看到具体的代码，很难给出具体的答案。话虽这么说，像数组这样的PHP数据结构是关联的。 PHP设计人员故意做出权衡，使用额外的RAM来节省阵列访问的时间。

You can save memory in a couple of ways. For one thing, you can fetch each row of your result set as a numeric, rather than an associative array. Read this. http://php.net/manual/en/mysqli-result.fetch-array.php

您可以通过几种方式节省内存。首先，您可以将结果集的每一行作为数字而不是关联数组获取。读这个。 http://php.net/manual/en/mysqli-result.fetch-array.php

For another thing, PHP slurps all the rows in your result set at once unless you tell it not to. This slurp operation consumes a lot of RAM. You don't need that if you're planning to process your large result set one row at a time. You need an unbuffered query to do that. Read this: http://php.net/manual/en/mysqlinfo.concepts.buffering.php

另一方面，PHP会立即篡改结果集中的所有行，除非你告诉它不要。这种slurp操作消耗了大量的RAM。如果您计划一次处理一行大型结果集，则不需要这样做。您需要一个无缓冲的查询来执行此操作。请阅读：http：//php.net/manual/en/mysqlinfo.concepts.buffering.php

#1