go - 为什么多个sql查询顺序运行而不是同时运行?
问题描述
我同时在单独的 go 例程中运行 mysql“选择”查询。然而,mysql 服务似乎会收集这些查询,按顺序(非同时)运行它们,然后同时返回所有结果集,并且只有在所有查询都运行后才返回。
我的问题是:
1. 为什么这些查询是按顺序运行而不是同时运行的?
2. 为什么mysql要等到所有查询都运行完才同时返回每个查询的结果集(即使每个单独的结果集属于不同的goroutine并且据说也使用单独的连接)?
另一件事:当我设置“SetMaxOpenConns(2)”时,它会同时返回两个结果集。如果设置为 3,则同时返回 3 个结果集。但是,它们仍然始终按顺序运行。
有人知道这里发生了什么吗?
package main
import (
"database/sql"
"fmt"
"sync"
"time"
_ "github.com/go-sql-driver/mysql"
)
var database *sql.DB
func init() {
var err error
databaseURI := "root:toor@tcp(192.168.200.10:3306)/ahc"
database, err = sql.Open("mysql", databaseURI)
if err != nil {
fmt.Println(err)
} else {
fmt.Println("DB Connection Established")
//database.SetMaxIdleConns(0)
//database.SetMaxOpenConns(2)
}
}
func test(device string, wg *sync.WaitGroup) {
fmt.Println("Thread: " + device + " started")
start := time.Now()
var count string
//using go-sql-driver
sqlStatement, err := database.Prepare("select count(cpeName) from report where errMessage <> \"ok\" and cpeName = ? and jobID = ?")
if err != nil {
fmt.Println(err)
}
defer sqlStatement.Close()
err = sqlStatement.QueryRow(device, "11534").Scan(&count)
sqlStatement.Close()
duration := time.Since(start)
fmt.Println("Thread: " + device + " Duration: " + duration.String() + "\n")
wg.Done()
}
func main() {
var wg sync.WaitGroup
var deviceList = []string{"xx-swrk-ca-gen-s-002", "xx-leus-ca-ust-ap-068", "xx-sgvn-ca-lug-ap-004", "xx-swrk-ca-vez-s-005", "xx-swrk-ca-vez-ap-006", "xx-leus-ca-ust-ap-065", "xx-leus-ca-ust-ap-073", "xx-leus-ca-ust-ap-076", "xx-leus-ca-ust-ap-077", "xx-swrk-ca-gen-s-001"}
total := time.Now()
for _, device := range deviceList {
wg.Add(1)
go test(device, &wg)
}
wg.Wait()
duration := time.Since(total)
fmt.Println("\n\nTotal: Duration: " + duration.String() + "\n")
}
这是输出
DB Connection Established
Thread: xx-leus-ca-ust-ap-068 started
Thread: xx-sgvn-ca-lug-ap-004 started
Thread: xx-leus-ca-ust-ap-065 started
Thread: xx-leus-ca-ust-ap-073 started
Thread: xx-swrk-ca-vez-ap-006 started
Thread: xx-swrk-ca-vez-s-005 started
Thread: xx-leus-ca-ust-ap-076 started
Thread: xx-leus-ca-ust-ap-077 started
Thread: xx-swrk-ca-gen-s-002 started
Thread: xx-swrk-ca-gen-s-001 started
Thread: xx-leus-ca-ust-ap-076 Duration: 7.291656143s
Thread: xx-swrk-ca-gen-s-002 Duration: 7.304134404s
Thread: xx-leus-ca-ust-ap-065 Duration: 7.307958641s
Thread: xx-swrk-ca-vez-s-005 Duration: 7.313591747s
Thread: xx-leus-ca-ust-ap-077 Duration: 7.313992638s
Thread: xx-swrk-ca-vez-ap-006 Duration: 7.314905664s
Thread: xx-swrk-ca-gen-s-001 Duration: 7.320466323s
Thread: xx-leus-ca-ust-ap-073 Duration: 7.322158337s
Thread: xx-leus-ca-ust-ap-068 Duration: 7.324745097s
Thread: xx-sgvn-ca-lug-ap-004 Duration: 7.326001783s
Total: Duration: 7.326096238s
使用 database.SetMaxOpenConns(1) 时,输出如下:
DB Connection Established
Thread: xx-leus-ca-ust-ap-068 started
Thread: xx-swrk-ca-gen-s-001 started
Thread: xx-swrk-ca-vez-ap-006 started
Thread: xx-leus-ca-ust-ap-065 started
Thread: xx-leus-ca-ust-ap-073 started
Thread: xx-swrk-ca-gen-s-002 started
Thread: xx-leus-ca-ust-ap-077 started
Thread: xx-sgvn-ca-lug-ap-004 started
Thread: xx-leus-ca-ust-ap-076 started
Thread: xx-swrk-ca-vez-s-005 started
Thread: xx-leus-ca-ust-ap-068 Duration: 1.131790286s
Thread: xx-leus-ca-ust-ap-077 Duration: 2.128919333s
Thread: xx-swrk-ca-gen-s-001 Duration: 3.073559464s
Thread: xx-leus-ca-ust-ap-073 Duration: 4.002964333s
Thread: xx-swrk-ca-vez-s-005 Duration: 4.932256684s
Thread: xx-sgvn-ca-lug-ap-004 Duration: 5.853361245s
Thread: xx-swrk-ca-gen-s-002 Duration: 6.785042625s
Thread: xx-leus-ca-ust-ap-065 Duration: 7.705957815s
Thread: xx-swrk-ca-vez-ap-006 Duration: 8.633000734s
Thread: xx-leus-ca-ust-ap-076 Duration: 9.550948572s
Total: Duration: 9.551103129s
解决方案
查看它们是并行运行还是串行运行的一种简单技术:让每个连接都执行
SELECT SLEEP(1);
如果是并行的,则该集合不会超过 1 秒。如果串行,则 N 秒。
如果它们是按顺序运行的,但在所有完成之前没有输出,那将是一个 GO 问题。
如果你的 7.3s 真的是并行的,而 9.5 是串行的,那么这就说明了为什么并行运行东西是不值得的——它们会相互踩踏,并且不会更快地完成。冲突可能是 CPU 或 I/O 或互斥锁或网络或其他东西;这取决于查询。(我的sleep
测试是非常无创的,但需要的时间是可以预测的。)