PHP对大量数据的自然排序内存问题

I'm using php's natural sorting algorithm natsort but I have a consideration about memory usage.

我正在使用php的自然排序算法natsort,但我考虑了内存使用情况。

This is how it goes. Script pulls data from mysql and put results into an array and than apply natsort over it. But here is the catch. Row's text can be long and there could be hundreds of rows.

这是怎么回事。脚本从mysql中提取数据并将结果放入数组中,然后在其上应用natsort。但这是抓住了。 Row的文本可能很长,可能有数百行。

Example code:

$array = array();
while ($row = $db->getResults()) {
  $array[$row->code] = $row->text;
}

if (empty($array)) {
  uksort($array, "strnatcmp");
}

I wonder how is this affecting memory? Is this appropriate approach or should I do something more efficient, more memory pleasant?

我想知道这会如何影响记忆?这是合适的方法,还是应该做一些更高效,更愉快的记忆?

2 个解决方案

#1

One thing you can do is store a new column which duplicates the column you want to sort, but stores it in a transformed format that will sort naturally when using the regular sort algorithm.

您可以做的一件事是存储一个新列,该列复制您要排序的列,但将其存储为转换格式,在使用常规排序算法时将自然排序。

Conceptually, you can do this by left-padding digit sequences with zeros to a length that will be as long as the longest possible numeric sequence that could occur in your string.

从概念上讲,您可以通过用零填充数字序列到一个长度来完成此操作,该长度将与字符串中可能出现的最长数字序列一样长。

My solution isn't totally rugged, but if your strings just have digit sequences of known maximum lengths, then you can left pad them with zeros to that known max length. For example, if you had cd track titles with the track number embedded into the title like:

我的解决方案并不完全坚固,但如果你的字符串只有已知最大长度的数字序列,那么你可以用零填充它们到已知的最大长度。例如,如果您有CD轨道标题,并且标题中嵌入了轨道号,例如:

1 Foo
2 Bar
...
10 Baz

Maybe you decide that the longest numeric sequence possible would be 3(999 possible tracks), so you would pad the numeric sequences like

也许你决定最长的数字序列可能是3(999个可能的轨道),所以你可以填充像这样的数字序列

001 Foo
002 Bar
...
010 Baz

This works fine with strings that have multiple numeric sequences.

这适用于具有多个数字序列的字符串。

Sample php code, although you could write a mysql stored function to do this, and then use insert and update triggers on the table so that it's maintained transparently.

示例php代码,虽然您可以编写一个mysql存储函数来执行此操作,然后在表上使用insert和update触发器,以便透明地维护它。

$input = 'a44b1c399d4';
$nat = preg_replace_callback('#\d+#', function($m) {
    return str_pad($m[0], 3, '0', STR_PAD_LEFT);
}, $input);
echo $nat; // a044b001c399d004

Then just sort in mysql via

然后只需在mysql中排序

order by natsort_column

This also lets you put an index on that column, giving you good sort performance.

这也允许您在该列上放置索引,从而为您提供良好的排序性能。

#2

-1

You need to use the MySQL WHERE, GROUP BY, and ORDER BY clauses so you don't waste time at the PHP level parsing thousands of unneeded records.

您需要使用MySQL WHERE,GROUP BY和ORDER BY子句,这样您就不会在PHP级别上浪费时间来解析数千个不需要的记录。

#1