英文:
How get fast multiple rows in Go?
问题
我在使用SELECT * FROM table查询语句中，当获取20k-50k甚至更多行数据并使用rows.scan(&a, &b...)方法进行扫描时，遇到了性能问题。我不知道如何在这种情况下使用并发，因为我需要使用rows.Next()进行迭代，而无法在并发中执行此操作。
性能下降只发生在将查询结果从行扫描到结构体字段中的rows.Scan操作上。
查询本身只需要5-15毫秒，但扫描操作却需要（40k行数据）800-2000毫秒。
谢谢！
英文:
I have troubles with performance when I get 20k-50k and more rows from SELECT * FROM table in rows.scan(&a, &b...). I don't know how to use concurrency in this case because I need to use rows.Next() for iterating, and I can't do it in concurrency.
Performance drops only when I scan results from rows to structure's fields in rows.Scan.
The query takes 5-15 ms, but scanning takes (40k rows) 800-2000ms.
Thank you!
答案1
得分: 1
由于'rows.Next()'是顺序执行的，你需要将查询拆分为多个语句，然后并发地处理它们。
func main() {
queries := []string{
"SELECT * FROM abc where a < 10000",
"SELECT * FROM abc where a >= 10000 && a <= 30000",
"SELECT * FROM abc where a > 30000",
}
for _, query := range queries {
go dbCall(query)
}
}
func dbCall(query string) {
rows, _ := db.Query(query)
for rows.Next() {
var a, b, c int
_ = rows.Scan(&a, &b, &c)
// 处理行数据
}
}
根据你的使用情况，根据需要使用通道、锁等。
英文:
Since 'rows.Next()' is sequential, you would need to split your query into multiple statements, and then work concurrently on them.
func main() {
queries := []string{
"SELECT * FROM abc where a < 10000",
"SELECT * FROM abc where a >= 10000 && a <= 30000",
"SELECT * FROM abc where a > 30000",
}
for _, query := range queries {
go dbCall(query)
}
}
func dbCall(query string) {
rows, _ := db.Query(query)
for rows.Next() {
var a, b, c int
_ = rows.Scan(&a, &b, &c)
// Process row
}
}
Use channels, locks, etc when necessary, based on your use case.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论