我可以强制MySQL在查询完成之前输出结果吗？

发布于 2024-10-22 03:44:01 字数 328 浏览 1 评论 0原文

我有一个大型 MySQL 表（大约 7.5 亿行），我只想提取几列。

SELECT id, delid FROM tbl_name;

没有加入或选择标准或任何东西。两个字段（分别）都有一个索引。

原则上，它可以开始读取表格并立即吐出值，但实际上整个系统只会消耗内存并基本上停止运行。

似乎整个查询正在执行，并且输出在生成任何输出之前存储在某处......

我已经搜索了取消缓冲、关闭缓存等，但找不到答案。

（mysqldump几乎是我想要的，除了它转储整个表 - 但至少它立即开始生成输出）

原文

I have a large MySQL table (about 750 million rows) and I just want to extract a couple of columns.

SELECT id, delid FROM tbl_name;

No joins or selection criteria or anything. There is an index on both fields (separately).

In principle, it could just start reading the table and spitting out the values immediately, but in practice the whole system just chews up memory and basically grinds to a halt.

It seems like the entire query is being executed and the output stored somewhere before ANY output is produced...

I've searched on unbuffering, turning off caches etc, but just cannot find the answer.

(mysqldump is almost what I want except it dumps the whole table - but at least it just starts producing output immediately)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

北音执念 2024-10-29 03:44:01

您是否看过加载数据文件和选择进入？

我认为以下内容接近您想要做的事情：

select id,delid 
  into outfile 'C:\\ronnis.csv'
       fields terminated by ','
  from tbl_name;

<perform unix sort>

load data infile 'C:\\ronnis.csv' 
into table new_tbl_name
fields terminated by ',';

Have you looked at load data infile and select into?

I think the following is close to what you want to do:

select id,delid 
  into outfile 'C:\\ronnis.csv'
       fields terminated by ','
  from tbl_name;

<perform unix sort>

load data infile 'C:\\ronnis.csv' 
into table new_tbl_name
fields terminated by ',';

回复收藏 0 原文