Optimization: statements reuse previous column name (#1711)
#1708 added `[]mysqlField` cache to stmt. It was used only for MariaDB cached metadata.
This commit allows MySQL to also benefit from the metadata cache. If the
column names are the same as the cached metadata, it reuses them instead
of allocating new strings.
goos: darwin
goarch: arm64
pkg: github.com/go-sql-driver/mysql
cpu: Apple M1 Pro
│ master.txt │ reuse.txt │
│ sec/op │ sec/op vs base │
ReceiveMetadata-8 1.273m ± 2% 1.269m ± 2% ~ (p=1.000 n=10)
│ master.txt │ reuse.txt │
│ B/op │ B/op vs base │
ReceiveMetadata-8 88.17Ki ± 0% 80.39Ki ± 0% -8.82% (p=0.000 n=10)
│ master.txt │ reuse.txt │
│ allocs/op │ allocs/op vs base │
ReceiveMetadata-8 1015.00 ± 0% 16.00 ± 0% -98.42% (p=0.000 n=10)
diff --git a/benchmark_test.go b/benchmark_test.go
index b246f4a..e735776 100644
--- a/benchmark_test.go
+++ b/benchmark_test.go
@@ -490,14 +490,13 @@
valuePtrs[j] = &values[j]
}
- b.ReportAllocs()
- b.ResetTimer()
-
// Prepare a SELECT query to retrieve metadata
stmt := tb.checkStmt(db.Prepare("SELECT * FROM large_integer_table LIMIT 1"))
defer stmt.Close()
// Benchmark metadata retrieval
+ b.ReportAllocs()
+ b.ResetTimer()
for range b.N {
rows := tb.checkRows(stmt.Query())
diff --git a/connection.go b/connection.go
index 58c763f..5648e47 100644
--- a/connection.go
+++ b/connection.go
@@ -231,7 +231,7 @@
if columnCount > 0 {
if mc.extCapabilities&clientCacheMetadata != 0 {
- if stmt.columns, err = mc.readColumns(int(columnCount)); err != nil {
+ if stmt.columns, err = mc.readColumns(int(columnCount), nil); err != nil {
return nil, err
}
} else {
@@ -448,7 +448,7 @@
}
// Columns
- rows.rs.columns, err = mc.readColumns(resLen)
+ rows.rs.columns, err = mc.readColumns(resLen, nil)
return rows, err
}
diff --git a/packets.go b/packets.go
index 1319f9e..b8f0612 100644
--- a/packets.go
+++ b/packets.go
@@ -702,8 +702,11 @@
// Read Packets as Field Packets until EOF-Packet or an Error appears
// http://dev.mysql.com/doc/internals/en/com-query-response.html#packet-Protocol::ColumnDefinition41
-func (mc *mysqlConn) readColumns(count int) ([]mysqlField, error) {
+func (mc *mysqlConn) readColumns(count int, old []mysqlField) ([]mysqlField, error) {
columns := make([]mysqlField, count)
+ if len(old) != count {
+ old = nil
+ }
for i := range count {
data, err := mc.readPacket()
@@ -731,7 +734,12 @@
return nil, err
}
pos += n
- columns[i].tableName = string(tableName)
+ if old != nil && old[i].tableName == string(tableName) {
+ // avoid allocating new string
+ columns[i].tableName = old[i].tableName
+ } else {
+ columns[i].tableName = string(tableName)
+ }
} else {
n, err = skipLengthEncodedString(data[pos:])
if err != nil {
@@ -752,7 +760,12 @@
if err != nil {
return nil, err
}
- columns[i].name = string(name)
+ if old != nil && old[i].name == string(name) {
+ // avoid allocating new string
+ columns[i].name = old[i].name
+ } else {
+ columns[i].name = string(name)
+ }
pos += n
// Original name [len coded string]
diff --git a/rows.go b/rows.go
index e41fda6..190e75f 100644
--- a/rows.go
+++ b/rows.go
@@ -186,7 +186,7 @@
return err
}
- rows.rs.columns, err = rows.mc.readColumns(resLen)
+ rows.rs.columns, err = rows.mc.readColumns(resLen, nil)
return err
}
@@ -208,7 +208,7 @@
return err
}
- rows.rs.columns, err = rows.mc.readColumns(resLen)
+ rows.rs.columns, err = rows.mc.readColumns(resLen, nil)
return err
}
diff --git a/statement.go b/statement.go
index 0f6c65a..2db8960 100644
--- a/statement.go
+++ b/statement.go
@@ -74,7 +74,7 @@
// Columns
if metadataFollows && stmt.mc.extCapabilities&clientCacheMetadata != 0 {
// we can not skip column metadata because next stmt.Query() may use it.
- if stmt.columns, err = mc.readColumns(resLen); err != nil {
+ if stmt.columns, err = mc.readColumns(resLen, stmt.columns); err != nil {
return nil, err
}
} else {
@@ -125,7 +125,7 @@
if resLen > 0 {
rows.mc = mc
if metadataFollows {
- if rows.rs.columns, err = mc.readColumns(resLen); err != nil {
+ if rows.rs.columns, err = mc.readColumns(resLen, stmt.columns); err != nil {
return nil, err
}
stmt.columns = rows.rs.columns
