Improve query building routines of DML event queries, reducing time and allocations

[danieljoos]

Description

This pull request adapts the query building routines for DML event queries. Parts that never change throughout the migration will only be computed once.

Measurements

Tests with production load showed an increase in throughput of around 5-10%. The tests have been conducted on separate testing replicas.

(🔵 blue: current version, 🔴 red: updated version)

The results of synthetic benchmarks can be found here: https://github.com/github/gh-ost/pull/1459#issuecomment-2431632260

---

In case this PR introduced Go code changes:

• [x] contributed code is using same conventions as original code
• [x] script/cibuild returns with no formatting errors, build errors or unit test errors.

[meiji163]

I believe the failing test case generated-columns-unique is unrelated. The error can be reproduced on the master branch by adding an UPDATE to the test case (https://github.com/github/gh-ost/pull/1461)

[danieljoos]

Find below the results of some synthetic benchmarks, using the Go benchmarking capabilities. The benchmarks have been conducted on Codespace hosts. Benchmark functions can be found in the details.

Benchmark Build DML Delete Query

Benchmark function

```go func BenchmarkApplierBuildDMLEventQuery_Delete(b *testing.B) { columns := sql.NewColumnList([]string{"id", "item_id", "name"}) migrationContext := base.NewMigrationContext() migrationContext.DatabaseName = "test" migrationContext.OriginalTableName = "test" migrationContext.OriginalTableColumns = columns migrationContext.SharedColumns = columns migrationContext.MappedSharedColumns = columns migrationContext.UniqueKey = &sql.UniqueKey{ Name: "unique", Columns: *columns, } applier := NewApplier(migrationContext) applier.prepareQueries() // comment out in current version columnValues := sql.ToColumnValues([]interface{}{123, 456, "abc"}) binlogEvent := &binlog.BinlogDMLEvent{ DatabaseName: "test", DML: binlog.DeleteDML, WhereColumnValues: columnValues, } for range b.N { _ = applier.buildDMLEventQuery(binlogEvent) } } ```

Current version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Delete-2       460914          2994 ns/op        1112 B/op         45 allocs/op
PASS
ok      github.com/github/gh-ost/go/logic   1.416s

Updated version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Delete-2      5844512           186.2 ns/op       128 B/op          4 allocs/op
PASS
ok      github.com/github/gh-ost/go/logic   1.306s

Benchmark Build DML Insert Query

Benchmark function

```go func BenchmarkApplierBuildDMLEventQuery_Insert(b *testing.B) { columns := sql.NewColumnList([]string{"id", "item_id"}) migrationContext := base.NewMigrationContext() migrationContext.DatabaseName = "test" migrationContext.OriginalTableName = "test" migrationContext.OriginalTableColumns = columns migrationContext.SharedColumns = columns migrationContext.MappedSharedColumns = columns migrationContext.UniqueKey = &sql.UniqueKey{ Name: "unique", Columns: *columns, } applier := NewApplier(migrationContext) applier.prepareQueries() // comment out in current version columnValues := sql.ToColumnValues([]interface{}{1234, 5678}) binlogEvent := &binlog.BinlogDMLEvent{ DatabaseName: "test", DML: binlog.InsertDML, NewColumnValues: columnValues, } for range b.N { _ = applier.buildDMLEventQuery(binlogEvent) } } ```

Current version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Insert-2       774986          1713 ns/op         600 B/op         27 allocs/op
PASS
ok      github.com/github/gh-ost/go/logic   1.353s

Updated version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Insert-2      9303057           141.0 ns/op       104 B/op          3 allocs/op
PASS
ok      github.com/github/gh-ost/go/logic   1.453s

Benchmark Build DML Update Query

Benchmark function

```go func BenchmarkApplierBuildDMLEventQuery_Update(b *testing.B) { columns := sql.NewColumnList([]string{"id", "item_id"}) migrationContext := base.NewMigrationContext() migrationContext.DatabaseName = "test" migrationContext.OriginalTableName = "test" migrationContext.OriginalTableColumns = columns migrationContext.SharedColumns = columns migrationContext.MappedSharedColumns = columns migrationContext.UniqueKey = &sql.UniqueKey{ Name: "unique", Columns: *columns, } applier := NewApplier(migrationContext) applier.prepareQueries() // comment out in current version columnValues := sql.ToColumnValues([]interface{}{1234, 5678}) binlogEvent := &binlog.BinlogDMLEvent{ DatabaseName: "test", DML: binlog.UpdateDML, NewColumnValues: columnValues, WhereColumnValues: columnValues, } for range b.N { _ = applier.buildDMLEventQuery(binlogEvent) } } ```

Current version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Update-2       400082          3062 ns/op        1136 B/op         52 allocs/op
PASS
ok      github.com/github/gh-ost/go/logic   1.266s

Updated version

goos: linux
goarch: amd64
pkg: github.com/github/gh-ost/go/logic
cpu: Intel(R) Xeon(R) Platinum 8370C CPU @ 2.80GHz
BenchmarkApplierBuildDMLEventQuery_Update-2      3431607           363.4 ns/op       232 B/op          6 allocs/op
PASS
ok      github.com/github/gh-ost/go/logic   1.611s