Repository: pingcap/tidb-tools (go)

Issue:
sync_diff_inspector:Add hint use_index when verifying SQL using primary key/unique key paging
**Is your feature request related to a problem? Please describe:**
The SQL validation will default to using primary key/unique key paging. These SQL statements may use full table scann due to optimizer bugs or inaccurate statistic. Resulting in a long verification time.

**Describe the feature you'd like:**
When using primary key/unique key paging, add hint use_index to force index scan.

**Describe alternatives you've considered:**

**Teachability, Documentation, Adoption, Migration Strategy:**


Pull request:
sync-diff-inspector: add `sql-hint-use-index` in config for automatically apply hint in check SQL
<!--
Thank you for contributing to TiDB! Please read TiDB's [CONTRIBUTING](https://github.com/pingcap/tidb/blob/master/CONTRIBUTING.md) document **BEFORE** filing this PR.
-->

### What problem does this PR solve?
<!--
Please create an issue first to describe the problem.

There MUST be one line starting with "Issue Number:  " and 
linking the relevant issues via the "close" or "ref".

For more info, check https://pingcap.github.io/tidb-dev-guide/contribute-to-tidb/contribute-code.html#referring-to-an-issue.
-->

Issue Number: close #836

Add hint `/*+ USE_INDEX(dbName.tblName, indexName) */` to force index scan in checksum SQL. Note this index hint is only applied on TiDB source.

How to use?

```
[data-sources]
[data-sources.source]
host = "127.0.0.1"
port = 4000
user = "root"
password = ""

# Automatically detects and applies existing indexes, only applies on TiDB source.
sql-hint-use-index = "auto"
```

### What is changed and how it works?

In each chunk, we will store the column names of the index used to split chunks. These column names will be used to match exact index name to build the index hint.

The reason we store **column names** rather than store index name directly is to avoid different index names between upstream and downstream.

For example:

- upstream has table `t1(id int, col1 int, unique index i1(id, col1))`
- downstream has table `t1(id int, col1 int, unique index i2(id, col1))`

### Check List <!--REMOVE the items that are not applicable-->

Tests <!-- At least one of them must be included. -->

 - Unit test
 - Integration test
 - Manual test (add detailed scripts or steps below)
 - No code

Code changes

 - Has exported function/method change
 - Has exported variable/fields change
 - Has interface methods change
 - Has persistent data change

Side effects

 - Possible performance regression
 - Increased code complexity
 - Breaking backward compatibility

Related changes

 - Need to cherry-pick to the release branch
 - Need to update the documentation
 - Need to be included in the release note


Patch:
diff --git a/OWNERS b/OWNERS
index b9fbc15..973d1bf 100644
--- a/OWNERS
+++ b/OWNERS
@@ -40,6 +40,7 @@ approvers:
   - imtbkcat
   - jackysp
   - jiyfhust
+  - JmPotato
   - joccau
   - kennytm
   - lance6716
@@ -56,12 +57,15 @@ approvers:
   - morgo
   - MyonKeminta
   - ngaut
+  - nolouch
   - nrc
+  - okJiang
   - overvenus
   - qiuyesuifeng
   - qw4990
   - rebelice
   - Reminiscent
+  - rleungx
   - shenli
   - siddontang
   - sticnarf
@@ -101,7 +105,6 @@ reviewers:
   - fzzf678
   - gengliqi
   - ghazalfamilyusa
-  - JmPotato
   - js00070
   - lamxTyler
   - LittleFall
@@ -109,10 +112,8 @@ reviewers:
   - lyzx2001
   - mengxin9014
   - mornyx
-  - nolouch
   - pingyu
   - RidRisR
-  - rleungx
   - Rustin170506
   - SeaRise
   - shihongzhi
diff --git a/sync_diff_inspector/chunk/chunk.go b/sync_diff_inspector/chunk/chunk.go
index 6ac71cf..47799de 100644
--- a/sync_diff_inspector/chunk/chunk.go
+++ b/sync_diff_inspector/chunk/chunk.go
@@ -22,6 +22,7 @@ import (
 	"github.com/pingcap/errors"
 	"github.com/pingcap/log"
 	"github.com/pingcap/tidb-tools/pkg/dbutil"
+	"github.com/pingcap/tidb/pkg/parser/model"
 	"go.uber.org/zap"
 )
 
@@ -150,6 +151,10 @@ type Range struct {
 	Where string        `json:"where"`
 	Args  []interface{} `json:"args"`
 
+	// IndexColumnNames store column names of index splitting chunks.
+	// It's used to find index name and generate index hint in checksum query.
+	IndexColumnNames []model.CIStr `json:"index-column-names,omitempty"`
+
 	columnOffset map[string]int
 }
 
@@ -386,6 +391,7 @@ func (c *Range) Update(column, lower, upper string, updateLower, updateUpper boo
 
 func (c *Range) Copy() *Range {
 	newChunk := NewChunkRange()
+	newChunk.IndexColumnNames = c.IndexColumnNames
 	for _, bound := range c.Bounds {
 		newChunk.addBound(&Bound{
 			Column:   bound.Column,
@@ -401,6 +407,7 @@ func (c *Range) Copy() *Range {
 
 func (c *Range) Clone() *Range {
 	newChunk := NewChunkRange()
+	newChunk.IndexColumnNames = c.IndexColumnNames
 	for _, bound := range c.Bounds {
 		newChunk.addBound(&Bound{
 			Column:   bound.Column,
diff --git a/sync_diff_inspector/config/config.go b/sync_diff_inspector/config/config.go
index b0f311e..6c5c0dd 100644
--- a/sync_diff_inspector/config/config.go
+++ b/sync_diff_inspector/config/config.go
@@ -108,12 +108,13 @@ type Security struct {
 
 // DataSource represents the Source Config.
 type DataSource struct {
-	Host     string             `toml:"host" json:"host"`
-	Port     int                `toml:"port" json:"port"`
-	User     string             `toml:"user" json:"user"`
-	Password utils.SecretString `toml:"password" json:"password"`
-	SqlMode  string             `toml:"sql-mode" json:"sql-mode"`
-	Snapshot string             `toml:"snapshot" json:"snapshot"`
+	Host            string             `toml:"host" json:"host"`
+	Port            int                `toml:"port" json:"port"`
+	User            string             `toml:"user" json:"user"`
+	Password        utils.SecretString `toml:"password" json:"password"`
+	SqlMode         string             `toml:"sql-mode" json:"sql-mode"`
+	Snapshot        string             `toml:"snapshot" json:"snapshot"`
+	SQLHintUseIndex string             `toml:"sql-hint-use-index" json:"sql-hint-use-index"`
 
 	Security *Security `toml:"security" json:"security"`
 
diff --git a/sync_diff_inspector/source/mysql_shard.go b/sync_diff_inspector/source/mysql_shard.go
index afeb056..2a25d81 100644
--- a/sync_diff_inspector/source/mysql_shard.go
+++ b/sync_diff_inspector/source/mysql_shard.go
@@ -103,7 +103,7 @@ func (s *MySQLSources) GetCountAndMd5(ctx context.Context, tableRange *splitter.
 
 	for _, ms := range matchSources {
 		go func(ms *common.TableShardSource) {
-			count, checksum, err := utils.GetCountAndMd5Checksum(ctx, ms.DBConn, ms.OriginSchema, ms.OriginTable, table.Info, chunk.Where, chunk.Args)
+			count, checksum, err := utils.GetCountAndMd5Checksum(ctx, ms.DBConn, ms.OriginSchema, ms.OriginTable, table.Info, chunk.Where, "", chunk.Args)
 			infoCh <- &ChecksumInfo{
 				Checksum: checksum,
 				Count:    count,
diff --git a/sync_diff_inspector/source/tidb.go b/sync_diff_inspector/source/tidb.go
index 4504b27..6a09186 100644
--- a/sync_diff_inspector/source/tidb.go
+++ b/sync_diff_inspector/source/tidb.go
@@ -86,6 +86,7 @@ type TiDBSource struct {
 	tableDiffs     []*common.TableDiff
 	sourceTableMap map[string]*common.TableSource
 	snapshot       string
+	sqlHint        string
 	// bucketSpliterPool is the shared pool to produce chunks using bucket
 	bucketSpliterPool *utils.WorkerPool
 	dbConn            *sql.DB
@@ -126,7 +127,31 @@ func (s *TiDBSource) GetCountAndMd5(ctx context.Context, tableRange *splitter.Ra
 	chunk := tableRange.GetChunk()
 
 	matchSource := getMatchSource(s.sourceTableMap, table)
-	count, checksum, err := utils.GetCountAndMd5Checksum(ctx, s.dbConn, matchSource.OriginSchema, matchSource.OriginTable, table.Info, chunk.Where, chunk.Args)
+	indexHint := ""
+	if s.sqlHint == "auto" && len(chunk.IndexColumnNames) > 0 {
+		// If sqlHint is set to "auto" and there are index column names in the chunk,
+		// we attempt to find a matching index in the source table to use as an index hint.
+		// This is necessary because the index name might differ between the source and target tables,
+		// even if the index columns are the same. For example:
+		// 		Source: idx1(c1, c2)
+		// 		Target: idx2(c1, c2)
+		// In this case, we use the column names [c1, c2] from idx1 to find the corresponding index name (idx2) in the source table.
+		if tableInfos, err := s.GetSourceStructInfo(ctx, tableRange.GetTableIndex()); err == nil {
+			for _, index := range dbutil.FindAllIndex(tableInfos[0]) {
+				if utils.IsIndexMatchingColumns(index, chunk.IndexColumnNames) {
+					indexHint = fmt.Sprintf("/*+ USE_INDEX(%s, %s) */",
+						dbutil.TableName(matchSource.OriginSchema, matchSource.OriginTable),
+						dbutil.ColumnName(index.Name.O),
+					)
+					break
+				}
+			}
+		}
+	}
+
+	count, checksum, err := utils.GetCountAndMd5Checksum(
+		ctx, s.dbConn, matchSource.OriginSchema, matchSource.OriginTable, table.Info,
+		chunk.Where, indexHint, chunk.Args)
 
 	cost := time.Since(beginTime)
 	return &ChecksumInfo{
@@ -275,6 +300,7 @@ func NewTiDBSource(ctx context.Context, tableDiffs []*common.TableDiff, ds *conf
 		dbConn:            ds.Conn,
 		bucketSpliterPool: bucketSpliterPool,
 		version:           utils.TryToGetVersion(ctx, ds.Conn),
+		sqlHint:           ds.SQLHintUseIndex,
 	}
 	return ts, nil
 }
diff --git a/sync_diff_inspector/splitter/bucket.go b/sync_diff_inspector/splitter/bucket.go
index 9d88649..4c95ab5 100644
--- a/sync_diff_inspector/splitter/bucket.go
+++ b/sync_diff_inspector/splitter/bucket.go
@@ -278,6 +278,7 @@ func (s *BucketIterator) produceChunks(ctx context.Context, startRange *RangeInf
 		leftCnt := c.Index.ChunkCnt - c.Index.ChunkIndex - 1
 		if leftCnt > 0 {
 			chunkRange := chunk.NewChunkRange()
+			chunkRange.IndexColumnNames = utils.GetColumnNames(s.indexColumns)
 
 			for i, column := range s.indexColumns {
 				chunkRange.Update(column.Name.O, "", nextUpperValues[i], false, true)
@@ -310,6 +311,7 @@ func (s *BucketIterator) produceChunks(ctx context.Context, startRange *RangeInf
 		}
 
 		chunkRange := chunk.NewChunkRange()
+		chunkRange.IndexColumnNames = utils.GetColumnNames(s.indexColumns)
 		for j, column := range s.indexColumns {
 			var lowerValue, upperValue string
 			if len(lowerValues) > 0 {
@@ -347,6 +349,7 @@ func (s *BucketIterator) produceChunks(ctx context.Context, startRange *RangeInf
 
 	// merge the rest keys into one chunk
 	chunkRange := chunk.NewChunkRange()
+	chunkRange.IndexColumnNames = utils.GetColumnNames(s.indexColumns)
 	if len(lowerValues) > 0 {
 		for j, column := range s.indexColumns {
 			chunkRange.Update(column.Name.O, lowerValues[j], "", true, false)
diff --git a/sync_diff_inspector/splitter/random.go b/sync_diff_inspector/splitter/random.go
index 33fe727..de80274 100644
--- a/sync_diff_inspector/splitter/random.go
+++ b/sync_diff_inspector/splitter/random.go
@@ -61,6 +61,44 @@ func NewRandomIteratorWithCheckpoint(ctx context.Context, progressID string, tab
 	}
 
 	chunkRange := chunk.NewChunkRange()
+
+	// Below logic is modified from BucketIterator
+	// It's used to find the index which can match the split fields in RandomIterator.
+	iFields := &indexFields{cols: fields, tableInfo: table.Info}
+	var indices = dbutil.FindAllIndex(table.Info)
+NEXTINDEX:
+	for _, index := range indices {
+		if index == nil {
+			continue
+		}
+		if startRange != nil && startRange.IndexID != index.ID {
+			continue
+		}
+
+		indexColumns := utils.GetColumnsFromIndex(index, table.Info)
+
+		if len(indexColumns) < len(index.Columns) {
+			// some column in index is ignored.
+			continue
+		}
+
+		if !iFields.MatchesIndex(index) {
+			// We are enforcing user configured "index-fields" settings.
+			continue
+		}
+
+		// skip the index that has expression column
+		for _, col := range indexColumns {
+			if col.Hidden {
+				continue NEXTINDEX
+			}
+		}
+
+		// Found the index, store column names
+		chunkRange.IndexColumnNames = utils.GetColumnNames(indexColumns)
+		break
+	}
+
 	beginIndex := 0
 	bucketChunkCnt := 0
 	chunkCnt := 0
diff --git a/sync_diff_inspector/utils/utils.go b/sync_diff_inspector/utils/utils.go
index b7277db..ed3e6b9 100644
--- a/sync_diff_inspector/utils/utils.go
+++ b/sync_diff_inspector/utils/utils.go
@@ -31,6 +31,7 @@ import (
 	"github.com/pingcap/tidb-tools/pkg/dbutil"
 	"github.com/pingcap/tidb-tools/sync_diff_inspector/chunk"
 	"github.com/pingcap/tidb/pkg/meta/model"
+	pmodel "github.com/pingcap/tidb/pkg/parser/model"
 	"github.com/pingcap/tidb/pkg/parser/mysql"
 	"go.uber.org/zap"
 )
@@ -774,7 +775,7 @@ func GetTableSize(ctx context.Context, db *sql.DB, schemaName, tableName string)
 }
 
 // GetCountAndMd5Checksum returns checksum code and count of some data by given condition
-func GetCountAndMd5Checksum(ctx context.Context, db *sql.DB, schemaName, tableName string, tbInfo *model.TableInfo, limitRange string, args []interface{}) (int64, uint64, error) {
+func GetCountAndMd5Checksum(ctx context.Context, db *sql.DB, schemaName, tableName string, tbInfo *model.TableInfo, limitRange string, indexHint string, args []interface{}) (int64, uint64, error) {
 	/*
 		calculate MD5 checksum and count example:
 		mysql> SELECT COUNT(*) as CNT, BIT_XOR(CAST(CONV(SUBSTRING(MD5(CONCAT_WS(',', `id`, `name`, CONCAT(ISNULL(`id`), ISNULL(`name`)))), 1, 16), 16, 10) AS UNSIGNED) ^ CAST(CONV(SUBSTRING(MD5(CONCAT_WS(',', `id`, `name`, CONCAT(ISNULL(`id`), ISNULL(`name`)))), 17, 16), 16, 10) AS UNSIGNED)) as CHECKSUM FROM `a`.`t`;
@@ -804,8 +805,15 @@ func GetCountAndMd5Checksum(ctx context.Context, db *sql.DB, schemaName, tableNa
 		columnIsNull = append(columnIsNull, fmt.Sprintf("ISNULL(%s)", name))
 	}
 
-	query := fmt.Sprintf("SELECT COUNT(*) as CNT, BIT_XOR(CAST(CONV(SUBSTRING(MD5(CONCAT_WS(',', %s, CONCAT(%s))), 1, 16), 16, 10) AS UNSIGNED) ^ CAST(CONV(SUBSTRING(MD5(CONCAT_WS(',', %s, CONCAT(%s))), 17, 16), 16, 10) AS UNSIGNED)) as CHECKSUM FROM %s WHERE %s;",
-		strings.Join(columnNames, ", "), strings.Join(columnIsNull, ", "), strings.Join(columnNames, ", "), strings.Join(columnIsNull, ", "), dbutil.TableName(schemaName, tableName), limitRange)
+	query := fmt.Sprintf("SELECT %s COUNT(*) as CNT, BIT_XOR(CAST(CONV(SUBSTRING(MD5(CONCAT_WS(',', %s, CONCAT(%s))), 1, 16), 16, 10) AS UNSIGNED) ^ CAST(CONV(SUBSTRING(MD5(CONCAT_WS(',', %s, CONCAT(%s))), 17, 16), 16, 10) AS UNSIGNED)) as CHECKSUM FROM %s WHERE %s;",
+		indexHint,
+		strings.Join(columnNames, ", "),
+		strings.Join(columnIsNull, ", "),
+		strings.Join(columnNames, ", "),
+		strings.Join(columnIsNull, ", "),
+		dbutil.TableName(schemaName, tableName),
+		limitRange,
+	)
 	log.Debug("count and checksum", zap.String("sql", query), zap.Reflect("args", args))
 
 	var count sql.NullInt64
@@ -1055,3 +1063,26 @@ func IsBinaryColumn(col *model.ColumnInfo) bool {
 	// varbinary or binary
 	return (col.GetType() == mysql.TypeVarchar || col.GetType() == mysql.TypeString) && mysql.HasBinaryFlag(col.GetFlag())
 }
+
+// IsIndexMatchingColumns checks if the given index matches the provided columns.
+// It uses the number of columns and their names to do the check.
+func IsIndexMatchingColumns(index *model.IndexInfo, columnNames []pmodel.CIStr) bool {
+	if len(index.Columns) != len(columnNames) {
+		return false
+	}
+	for i, col := range index.Columns {
+		if col.Name.L != columnNames[i].L {
+			return false
+		}
+	}
+	return true
+}
+
+// GetColumnNames extract column names from column infos
+func GetColumnNames(columns []*model.ColumnInfo) []pmodel.CIStr {
+	columnNames := make([]pmodel.CIStr, 0, len(columns))
+	for _, c := range columns {
+		columnNames = append(columnNames, c.Name)
+	}
+	return columnNames
+}


Test patch:
diff --git a/sync_diff_inspector/checkpoints/checkpoints_test.go b/sync_diff_inspector/checkpoints/checkpoints_test.go
index 95cdd2f..4e93935 100644
--- a/sync_diff_inspector/checkpoints/checkpoints_test.go
+++ b/sync_diff_inspector/checkpoints/checkpoints_test.go
@@ -23,6 +23,7 @@ import (
 	"time"
 
 	"github.com/pingcap/tidb-tools/sync_diff_inspector/chunk"
+	"github.com/pingcap/tidb/pkg/parser/model"
 	"github.com/stretchr/testify/require"
 )
 
@@ -56,6 +57,7 @@ func TestSaveChunk(t *testing.T) {
 							HasUpper: i != rounds,
 						},
 					},
+					IndexColumnNames: []model.CIStr{model.NewCIStr("col1"), model.NewCIStr("col2")},
 				},
 
 				State: SuccessState,
@@ -83,6 +85,7 @@ func TestLoadChunk(t *testing.T) {
 	ctx := context.Background()
 	rounds := 100
 	wg := &sync.WaitGroup{}
+	testColNames := []model.CIStr{model.NewCIStr("col1"), model.NewCIStr("col2")}
 	for i := 0; i < rounds; i++ {
 		wg.Add(1)
 		go func(i int) {
@@ -103,6 +106,7 @@ func TestLoadChunk(t *testing.T) {
 						ChunkIndex:       i % 10,
 						ChunkCnt:         10,
 					},
+					IndexColumnNames: testColNames,
 				},
 			}
 			checker.Insert(node)
@@ -117,4 +121,5 @@ func TestLoadChunk(t *testing.T) {
 	node, _, err := checker.LoadChunk("TestLoadChunk")
 	require.NoError(t, err)
 	require.Equal(t, node.GetID().Compare(id), 0)
+	require.Equal(t, node.ChunkRange.IndexColumnNames, testColNames)
 }
diff --git a/sync_diff_inspector/chunk/chunk_test.go b/sync_diff_inspector/chunk/chunk_test.go
index b5d62dd..b5189ec 100644
--- a/sync_diff_inspector/chunk/chunk_test.go
+++ b/sync_diff_inspector/chunk/chunk_test.go
@@ -16,6 +16,7 @@ package chunk
 import (
 	"testing"
 
+	"github.com/pingcap/tidb/pkg/parser/model"
 	"github.com/stretchr/testify/require"
 )
 
@@ -420,6 +421,28 @@ func TestChunkToString(t *testing.T) {
 	require.Equal(t, chunk.String(), `{"index":null,"type":0,"bounds":[{"column":"a","lower":"1","upper":"1","has-lower":true,"has-upper":true},{"column":"b","lower":"3","upper":"3","has-lower":true,"has-upper":true},{"column":"c","lower":"6","upper":"6","has-lower":true,"has-upper":true}],"is-first":false,"is-last":false,"where":"","args":null}`)
 	require.Equal(t, chunk.ToMeta(), "range in sequence: (1,3,6) < (a,b,c) <= (1,3,6)")
 
+	// with index names
+	chunk = &Range{
+		Bounds: []*Bound{
+			{
+				Column:   "a",
+				Lower:    "1",
+				Upper:    "1",
+				HasLower: true,
+				HasUpper: true,
+			}, {
+				Column:   "b",
+				Lower:    "3",
+				Upper:    "3",
+				HasLower: true,
+				HasUpper: true,
+			},
+		},
+		IndexColumnNames: []model.CIStr{model.NewCIStr("a"), model.NewCIStr("b")},
+	}
+
+	require.Equal(t, chunk.String(), "{\"index\":null,\"type\":0,\"bounds\":[{\"column\":\"a\",\"lower\":\"1\",\"upper\":\"1\",\"has-lower\":true,\"has-upper\":true},{\"column\":\"b\",\"lower\":\"3\",\"upper\":\"3\",\"has-lower\":true,\"has-upper\":true}],\"is-first\":false,\"is-last\":false,\"where\":\"\",\"args\":null,\"index-column-names\":[{\"O\":\"a\",\"L\":\"a\"},{\"O\":\"b\",\"L\":\"b\"}]}")
+	require.Equal(t, chunk.ToMeta(), "range in sequence: (1,3) < (a,b) <= (1,3)")
 }
 
 func TestChunkInit(t *testing.T) {
diff --git a/sync_diff_inspector/config/config_test.go b/sync_diff_inspector/config/config_test.go
index d07286f..331c90f 100644
--- a/sync_diff_inspector/config/config_test.go
+++ b/sync_diff_inspector/config/config_test.go
@@ -50,10 +50,10 @@ func TestParseConfig(t *testing.T) {
 
 	// we might not use the same config to run this test. e.g. MYSQL_PORT can be 4000
 	require.JSONEq(t, cfg.String(),
-		"{\"check-thread-count\":4,\"split-thread-count\":5,\"export-fix-sql\":true,\"check-struct-only\":false,\"dm-addr\":\"\",\"dm-task\":\"\",\"data-sources\":{\"mysql1\":{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule2\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null},\"mysql2\":{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule2\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null},\"mysql3\":{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule3\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null},\"tidb0\":{\"host\":\"127.0.0.1\",\"port\":4000,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":null,\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":{\"max_execution_time\":86400,\"tidb_opt_prefer_range_scan\":\"ON\"}}},\"routes\":{\"rule1\":{\"schema-pattern\":\"test_*\",\"table-pattern\":\"t_*\",\"target-schema\":\"test\",\"target-table\":\"t\"},\"rule2\":{\"schema-pattern\":\"test2_*\",\"table-pattern\":\"t2_*\",\"target-schema\":\"test2\",\"target-table\":\"t2\"},\"rule3\":{\"schema-pattern\":\"test2_*\",\"table-pattern\":\"t2_*\",\"target-schema\":\"test\",\"target-table\":\"t\"}},\"table-configs\":{\"config1\":{\"target-tables\":[\"schema*.table*\",\"test2.t2\"],\"Schema\":\"\",\"Table\":\"\",\"ConfigIndex\":0,\"HasMatched\":false,\"IgnoreColumns\":[\"\",\"\"],\"Fields\":[\"\"],\"Range\":\"age \\u003e 10 AND age \\u003c 20\",\"TargetTableInfo\":null,\"Collation\":\"\",\"chunk-size\":0}},\"task\":{\"source-instances\":[\"mysql1\",\"mysql2\",\"mysql3\"],\"source-routes\":null,\"target-instance\":\"tidb0\",\"target-check-tables\":[\"schema*.table*\",\"!c.*\",\"test2.t2\"],\"target-configs\":[\"config1\"],\"output-dir\":\"/tmp/output/config\",\"SourceInstances\":[{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule2\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null},{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule2\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null},{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule3\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null}],\"TargetInstance\":{\"host\":\"127.0.0.1\",\"port\":4000,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"security\":null,\"route-rules\":null,\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":{\"max_execution_time\":86400,\"tidb_opt_prefer_range_scan\":\"ON\"}},\"TargetTableConfigs\":[{\"target-tables\":[\"schema*.table*\",\"test2.t2\"],\"Schema\":\"\",\"Table\":\"\",\"ConfigIndex\":0,\"HasMatched\":false,\"IgnoreColumns\":[\"\",\"\"],\"Fields\":[\"\"],\"Range\":\"age \\u003e 10 AND age \\u003c 20\",\"TargetTableInfo\":null,\"Collation\":\"\",\"chunk-size\":0}],\"TargetCheckTables\":[{},{},{}],\"FixDir\":\"/tmp/output/config/fix-on-tidb0\",\"CheckpointDir\":\"/tmp/output/config/checkpoint\",\"HashFile\":\"\"},\"ConfigFile\":\"config_sharding.toml\",\"PrintVersion\":false}")
+		"{\"check-thread-count\":4,\"split-thread-count\":5,\"export-fix-sql\":true,\"check-struct-only\":false,\"dm-addr\":\"\",\"dm-task\":\"\",\"data-sources\":{\"mysql1\":{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"sql-hint-use-index\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule2\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null},\"mysql2\":{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"sql-hint-use-index\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule2\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null},\"mysql3\":{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"sql-hint-use-index\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule3\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null},\"tidb0\":{\"host\":\"127.0.0.1\",\"port\":4000,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"sql-hint-use-index\":\"\",\"security\":null,\"route-rules\":null,\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":{\"max_execution_time\":86400,\"tidb_opt_prefer_range_scan\":\"ON\"}}},\"routes\":{\"rule1\":{\"schema-pattern\":\"test_*\",\"table-pattern\":\"t_*\",\"target-schema\":\"test\",\"target-table\":\"t\"},\"rule2\":{\"schema-pattern\":\"test2_*\",\"table-pattern\":\"t2_*\",\"target-schema\":\"test2\",\"target-table\":\"t2\"},\"rule3\":{\"schema-pattern\":\"test2_*\",\"table-pattern\":\"t2_*\",\"target-schema\":\"test\",\"target-table\":\"t\"}},\"table-configs\":{\"config1\":{\"target-tables\":[\"schema*.table*\",\"test2.t2\"],\"Schema\":\"\",\"Table\":\"\",\"ConfigIndex\":0,\"HasMatched\":false,\"IgnoreColumns\":[\"\",\"\"],\"Fields\":[\"\"],\"Range\":\"age \\u003e 10 AND age \\u003c 20\",\"TargetTableInfo\":null,\"Collation\":\"\",\"chunk-size\":0}},\"task\":{\"source-instances\":[\"mysql1\",\"mysql2\",\"mysql3\"],\"source-routes\":null,\"target-instance\":\"tidb0\",\"target-check-tables\":[\"schema*.table*\",\"!c.*\",\"test2.t2\"],\"target-configs\":[\"config1\"],\"output-dir\":\"/tmp/output/config\",\"SourceInstances\":[{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"sql-hint-use-index\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule2\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null},{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"sql-hint-use-index\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule2\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null},{\"host\":\"127.0.0.1\",\"port\":3306,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"sql-hint-use-index\":\"\",\"security\":null,\"route-rules\":[\"rule1\",\"rule3\"],\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":null}],\"TargetInstance\":{\"host\":\"127.0.0.1\",\"port\":4000,\"user\":\"root\",\"password\":\"******\",\"sql-mode\":\"\",\"snapshot\":\"\",\"sql-hint-use-index\":\"\",\"security\":null,\"route-rules\":null,\"Router\":{\"Selector\":{}},\"Conn\":null,\"session\":{\"max_execution_time\":86400,\"tidb_opt_prefer_range_scan\":\"ON\"}},\"TargetTableConfigs\":[{\"target-tables\":[\"schema*.table*\",\"test2.t2\"],\"Schema\":\"\",\"Table\":\"\",\"ConfigIndex\":0,\"HasMatched\":false,\"IgnoreColumns\":[\"\",\"\"],\"Fields\":[\"\"],\"Range\":\"age \\u003e 10 AND age \\u003c 20\",\"TargetTableInfo\":null,\"Collation\":\"\",\"chunk-size\":0}],\"TargetCheckTables\":[{},{},{}],\"FixDir\":\"/tmp/output/config/fix-on-tidb0\",\"CheckpointDir\":\"/tmp/output/config/checkpoint\",\"HashFile\":\"\"},\"ConfigFile\":\"config_sharding.toml\",\"PrintVersion\":false}")
 	hash, err := cfg.Task.ComputeConfigHash()
 	require.NoError(t, err)
-	require.Equal(t, hash, "553515158a9da2724195f6f71eb8daeebb605bc7de8f3dd9b7447468d6f385b9")
+	require.Equal(t, hash, "5a978bf48039d41b81403d635332493f031bb890a6d4e4d7df77f75e0ccc29f3")
 
 	require.True(t, cfg.TableConfigs["config1"].Valid())
 
diff --git a/sync_diff_inspector/splitter/splitter_test.go b/sync_diff_inspector/splitter/splitter_test.go
index 18577c6..70c22d6 100644
--- a/sync_diff_inspector/splitter/splitter_test.go
+++ b/sync_diff_inspector/splitter/splitter_test.go
@@ -27,6 +27,7 @@ import (
 	"github.com/pingcap/tidb-tools/sync_diff_inspector/source/common"
 	"github.com/pingcap/tidb-tools/sync_diff_inspector/utils"
 	"github.com/pingcap/tidb/pkg/parser"
+	"github.com/pingcap/tidb/pkg/parser/model"
 	"github.com/stretchr/testify/require"
 )
 
@@ -934,3 +935,125 @@ func TestChunkSize(t *testing.T) {
 	require.NoError(t, err)
 
 }
+
+func TestBucketSpliterHint(t *testing.T) {
+	db, mock, err := sqlmock.New()
+	require.NoError(t, err)
+	ctx := context.Background()
+
+	testCases := []struct {
+		tableSQL      string
+		indexCount    int
+		expectColumns []model.CIStr
+	}{
+		{
+			"create table `test`.`test`(`a` int, `b` int, `c` int, primary key(`a`, `b`), unique key i1(`c`))",
+			0,
+			[]model.CIStr{model.NewCIStr("a"), model.NewCIStr("b")},
+		},
+		{
+			"create table `test`.`test`(`a` int, `b` int, `c` int, unique key i1(`c`))",
+			0,
+			[]model.CIStr{model.NewCIStr("c")},
+		},
+		{
+			"create table `test`.`test`(`a` int, `b` int, `c` int, key i2(`b`))",
+			1,
+			[]model.CIStr{model.NewCIStr("b")},
+		},
+	}
+
+	for _, tc := range testCases {
+		tableInfo, err := dbutil.GetTableInfoBySQL(tc.tableSQL, parser.New())
+		require.NoError(t, err)
+
+		tableDiff := &common.TableDiff{
+			Schema: "test",
+			Table:  "test",
+			Info:   tableInfo,
+		}
+
+		createFakeResultForBucketIterator(mock, tc.indexCount)
+
+		iter, err := NewBucketIteratorWithCheckpoint(ctx, "", tableDiff, db, nil, utils.NewWorkerPool(1, "bucketIter"))
+		require.NoError(t, err)
+		chunk, err := iter.Next()
+		require.NoError(t, err)
+		require.Equal(t, tc.expectColumns, chunk.IndexColumnNames)
+	}
+}
+
+func TestRandomSpliterHint(t *testing.T) {
+	db, mock, err := sqlmock.New()
+	require.NoError(t, err)
+	ctx := context.Background()
+
+	testCases := []struct {
+		tableSQL      string
+		expectColumns []model.CIStr
+	}{
+		{
+			"create table `test`.`test`(`a` int, `b` int, `c` int, primary key(`a`, `b`), unique key i1(`c`))",
+			[]model.CIStr{model.NewCIStr("a"), model.NewCIStr("b")},
+		},
+		{
+			"create table `test`.`test`(`a` int, `b` int, `c` int, unique key i1(`c`), key i2(`b`))",
+			[]model.CIStr{model.NewCIStr("c")},
+		},
+		{
+			"create table `test`.`test`(`a` int, `b` int, `c` int, key i2(`b`))",
+			[]model.CIStr{model.NewCIStr("b")},
+		},
+		{
+			"create table `test`.`test`(`a` int, `b` int, `c` int)",
+			nil,
+		},
+	}
+
+	for _, tc := range testCases {
+		tableInfo, err := dbutil.GetTableInfoBySQL(tc.tableSQL, parser.New())
+		require.NoError(t, err)
+
+		for _, tableRange := range []string{"", "c > 100"} {
+			tableDiff := &common.TableDiff{
+				Schema: "test",
+				Table:  "test",
+				Info:   tableInfo,
+				Range:  tableRange,
+			}
+
+			mock.ExpectQuery("SELECT COUNT*").WillReturnRows(sqlmock.NewRows([]string{"CNT"}).AddRow("320"))
+
+			iter, err := NewRandomIteratorWithCheckpoint(ctx, "", tableDiff, db, nil)
+			require.NoError(t, err)
+			chunk, err := iter.Next()
+			require.NoError(t, err)
+			require.Equal(t, tc.expectColumns, chunk.IndexColumnNames)
+		}
+	}
+}
+
+func createFakeResultForBucketIterator(mock sqlmock.Sqlmock, indexCount int) {
+	/*
+		+---------+------------+-------------+----------+-----------+-------+---------+-------------+-------------+
+		| Db_name | Table_name | Column_name | Is_index | Bucket_id | Count | Repeats | Lower_Bound | Upper_Bound |
+		+---------+------------+-------------+----------+-----------+-------+---------+-------------+-------------+
+		| test    | test       | PRIMARY     |        1 |         0 |    64 |       1 | (0, 0)      | (63, 11)    |
+		| test    | test       | PRIMARY     |        1 |         1 |   128 |       1 | (64, 12)    | (127, 23)   |
+		| test    | test       | PRIMARY     |        1 |         2 |   192 |       1 | (128, 24)   | (191, 35)   |
+		| test    | test       | PRIMARY     |        1 |         3 |   256 |       1 | (192, 36)   | (255, 47)   |
+		| test    | test       | PRIMARY     |        1 |         4 |   320 |       1 | (256, 48)   | (319, 59)   |
+		+---------+------------+-------------+----------+-----------+-------+---------+-------------+-------------+
+	*/
+	statsRows := sqlmock.NewRows([]string{"Db_name", "Table_name", "Column_name", "Is_index", "Bucket_id", "Count", "Repeats", "Lower_Bound", "Upper_Bound"})
+	for _, indexName := range []string{"PRIMARY", "i1", "i2", "i3", "i4"} {
+		for i := 0; i < 5; i++ {
+			statsRows.AddRow("test", "test", indexName, 1, (i+1)*64, (i+1)*64, 1, fmt.Sprintf("(%d, %d)", i*64, i*12), fmt.Sprintf("(%d, %d)", (i+1)*64-1, (i+1)*12-1))
+		}
+	}
+	mock.ExpectQuery("SHOW STATS_BUCKETS").WillReturnRows(statsRows)
+
+	for i := 0; i < indexCount; i++ {
+		mock.ExpectQuery("SELECT COUNT\\(DISTINCT *").WillReturnRows(sqlmock.NewRows([]string{"SEL"}).AddRow("5"))
+	}
+}
diff --git a/sync_diff_inspector/utils/utils_test.go b/sync_diff_inspector/utils/utils_test.go
index bb7c598..ea6aba6 100644
--- a/sync_diff_inspector/utils/utils_test.go
+++ b/sync_diff_inspector/utils/utils_test.go
@@ -271,7 +271,7 @@ func TestGetCountAndMd5Checksum(t *testing.T) {
 
 	mock.ExpectQuery("SELECT COUNT.*FROM `test_schema`\\.`test_table` WHERE \\[23 45\\].*").WithArgs("123", "234").WillReturnRows(sqlmock.NewRows([]string{"CNT", "CHECKSUM"}).AddRow(123, 456))
 
-	count, checksum, err := GetCountAndMd5Checksum(ctx, conn, "test_schema", "test_table", tableInfo, "[23 45]", []interface{}{"123", "234"})
+	count, checksum, err := GetCountAndMd5Checksum(ctx, conn, "test_schema", "test_table", tableInfo, "[23 45]", "", []interface{}{"123", "234"})
 	require.NoError(t, err)
 	require.Equal(t, count, int64(123))
 	require.Equal(t, checksum, uint64(0x1c8))