MySQL:按块检索较大的选择 [英] MySQL : retrieve a large select by chunks

问题描述

我选择了更多然后

7000万行

70 milion rows

我想将所选数据保存到win2012 R2上的一个大型csv文件中

I'd like to save the selected data into the one large csv file on win2012 R2

问:如何通过查询从MySQL检索数据以获得更好的性能?

Q: How to retrive the data from MySQL by chanks for better performance ?

因为当我尝试保存一个我得到的大选择

because when I try to save one the large select I got

内存不足错误

out of memory errors

推荐答案

您可以尝试使用LIMIT功能.如果您这样做:

You could try using the LIMIT feature. If you do this:

SELECT * FROM MyTable ORDER BY whatever LIMIT 0,1000

您将获得前1,000行.第一个LIMIT值(0)定义结果集中的起始行.它的索引为零，因此0表示第一行".第二个LIMIT值是要检索的最大行数.要获得接下来的几套1,000，请执行以下操作:

You'll get the first 1,000 rows. The first LIMIT value (0) defines the starting row in the result set. It's zero-indexed, so 0 means "the first row". The second LIMIT value is the maximum number of rows to retrieve. To get the next few sets of 1,000, do this:

SELECT * FROM MyTable ORDER BY whatever LIMIT 1000,1000 -- rows 1,001 - 2,000
SELECT * FROM MyTable ORDER BY whatever LIMIT 2000,1000 -- rows 2,001 - 3,000

以此类推.当SELECT不返回任何行时，您就完成了.

And so on. When the SELECT returns no rows, you're done.

这本身还不够，因为在您一次处理1K行时对表所做的任何更改都将取消订单.要及时冻结结果，请先将结果查询到临时表中

This isn't enough on its own though, because any changes done to the table while you're processing your 1K rows at a time will throw off the order. To freeze the results in time, start by querying the results into a temporary table:

CREATE TEMPORARY TABLE MyChunkedResult AS (
  SELECT *
  FROM MyTable
  ORDER BY whatever
);

旁注:最好先确保临时表不存在:

Side note: it's a good idea to make sure the temporary table doesn't exist beforehand:

DROP TEMPORARY TABLE IF EXISTS MyChunkedResult;

无论如何，一旦临时表到位，就从那里拉出行块:

At any rate, once the temporary table is in place, pull the row chunks from there:

SELECT * FROM MyChunkedResult LIMIT 0, 1000;
SELECT * FROM MyChunkedResult LIMIT 1000,1000;
SELECT * FROM MyChunkedResult LIMIT 2000,1000;
.. and so on.

我将留给您创建逻辑，该逻辑将在每个块之后计算极限值并检查结果的结尾.我还建议使用比1000条记录大得多的数据块；这只是我从空中挑出来的数字.

I'll leave it to you to create the logic that will calculate the limit value after each chunk and check for the end of results. I'd also recommend much larger chunks than 1,000 records; it's just a number I picked out of the air.

最后，这是在完成后删除临时表的好形式:

Finally, it's good form to drop the temporary table when you're done:

DROP TEMPORARY TABLE MyChunkedResult;

这篇关于MySQL:按块检索较大的选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！