优化 SELECT INTO OUTFILE
我有一个 SELECT INTO OUTFILE 查询，最多可以返回约 160,000 行或几十兆字节的数据。跑起来需要很长时间——比如20分钟。如果我执行LIMIT 100，查询只需要几分之一秒的时间，所以看起来我的查询可能已经得到了尽可能好的优化。
是否有特定于 SELECT INTO OUTFILE 的性能改进技术？例如，my.cnf 中可能有一些内存设置可以提供帮助。
注意：这个问题与查询优化无关，所以如果您想要查看我的查询，请不要要求查看我的查询。其他地方有很多这方面的帮助。我要求提供专门改进 SELECT INTO OUTFILE 查询的性能技术。
我使用的是 Ubuntu，如果这有什么区别的话。
I have a SELECT INTO OUTFILE query that can return up to ~160,000 rows or a few dozen megabytes of data. It takes a long time to run - like 20 minutes. If I do LIMIT 100 the query takes a fraction of a second, so it seems like my query is probably optimized nearly as well as it can be.
Are there any performance-improving techniques specific to SELECT INTO OUTFILE? For example, maybe there's some memory setting in my.cnf that helps or something.
Note: this question is not about query optimization so please don't ask to see my query if that's what you're after. There's plenty of help on that available elsewhere. I'm asking for performance techniques that specifically improve SELECT INTO OUTFILE queries.
I'm on Ubuntu, if that makes a difference.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这是通过网络吗？在同一拓扑中复制结果文件需要多长时间？
使用“LIMIT 100”进行测量并不一定是对任何事物的很好的测试。
一般来说，SELECT 和 SELECT INTO OUTFILE 之间的区别在于将数据传输到磁盘上的某个位置所需的时间。这可能涉及很多你没有告诉我们的事情。
Is this across a network? How long does it take to copy the result file across the same topology?
Measuring with "LIMIT 100" isn't necessarily a very good test for anything.
Generally the difference between SELECT and SELECT INTO OUTFILE is going to be the time it takes to get the data onto a disk somewhere. And that can involve lots of things you aren't telling us much about.