前言

分表字段选择作为分库分表架构设计中的核心环节，直接影响着数据分片的均匀性、查询性能和系统扩展性。通过合理的分表字段选择，能够有效避免数据倾斜，提高查询效率，确保系统的稳定运行。本文从分表字段策略到选择方案，从基础实现到企业级方案，系统梳理分表字段选择的完整解决方案。

一、分表字段架构设计

1.1 分表字段整体架构

1.2 分表字段选择策略架构

二、分表字段选择策略

2.1 主键字段分片

2.1.1 主键字段分片服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
/**
 * 主键字段分片服务
 */
@Service
public class PrimaryKeyShardingService {

    @Autowired
    private ShardingAlgorithmService shardingAlgorithmService;

    @Autowired
    private DatabaseConnectionManager connectionManager;

    private final int SHARD_COUNT = 8;

    /**
     * 根据主键分片
     */
    public int shardByPrimaryKey(String primaryKey) {
        try {
            // 使用取模算法
            return shardingAlgorithmService.moduloSharding(primaryKey, SHARD_COUNT);

        } catch (Exception e) {
            log.error("主键分片失败: {}", primaryKey, e);
            throw new ShardingException("主键分片失败", e);
        }
    }

    /**
     * 根据主键获取表名
     */
    public String getTableNameByPrimaryKey(String tablePrefix, String primaryKey) {
        int shardIndex = shardByPrimaryKey(primaryKey);
        return String.format("%s_%d", tablePrefix, shardIndex);
    }

    /**
     * 根据主键执行SQL
     */
    public Object executeByPrimaryKey(String tablePrefix, String primaryKey, String sql, Object... params) {
        try {
            // 获取分片表名
            String tableName = getTableNameByPrimaryKey(tablePrefix, primaryKey);

            // 替换SQL中的表名
            String shardedSQL = replaceTableName(sql, tableName);

            // 获取数据源
            int shardIndex = shardByPrimaryKey(primaryKey);
            DataSource dataSource = connectionManager.getDataSource(shardIndex);

            // 执行SQL
            return executeSQL(dataSource, shardedSQL, params);

        } catch (Exception e) {
            log.error("根据主键执行SQL失败", e);
            throw new ShardingException("根据主键执行SQL失败", e);
        }
    }

    /**
     * 批量根据主键执行SQL
     */
    public Map<String, Object> batchExecuteByPrimaryKey(String tablePrefix, Map<String, String> primaryKeySqlMap) {
        Map<String, Object> results = new HashMap<>();

        // 按分片分组
        Map<Integer, List<PrimaryKeyOperation>> groupedOperations = new HashMap<>();

        for (Map.Entry<String, String> entry : primaryKeySqlMap.entrySet()) {
            String primaryKey = entry.getKey();
            String sql = entry.getValue();

            int shardIndex = shardByPrimaryKey(primaryKey);
            groupedOperations.computeIfAbsent(shardIndex, k -> new ArrayList<>())
                    .add(new PrimaryKeyOperation(primaryKey, sql));
        }

        // 并行执行各分片操作
        groupedOperations.entrySet().parallelStream().forEach(entry -> {
            int shardIndex = entry.getKey();
            List<PrimaryKeyOperation> operations = entry.getValue();

            try {
                DataSource dataSource = connectionManager.getDataSource(shardIndex);
                String tableName = String.format("%s_%d", tablePrefix, shardIndex);

                for (PrimaryKeyOperation operation : operations) {
                    String shardedSQL = replaceTableName(operation.getSql(), tableName);
                    Object result = executeSQL(dataSource, shardedSQL);
                    results.put(operation.getPrimaryKey(), result);
                }

            } catch (Exception e) {
                log.error("分片{}执行失败", shardIndex, e);
            }
        });

        return results;
    }

    /**
     * 替换SQL中的表名
     */
    private String replaceTableName(String sql, String tableName) {
        // 简单的表名替换，实际应用中需要更复杂的SQL解析
        return sql.replaceAll("\\{table_name\\}", tableName);
    }

    /**
     * 执行SQL
     */
    private Object executeSQL(DataSource dataSource, String sql, Object... params) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {

            // 设置参数
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i + 1, params[i]);
            }

            // 执行SQL
            if (sql.trim().toUpperCase().startsWith("SELECT")) {
                ResultSet resultSet = statement.executeQuery();
                return convertResultSetToList(resultSet);
            } else {
                return statement.executeUpdate();
            }

        } catch (SQLException e) {
            log.error("SQL执行失败", e);
            throw new DatabaseException("SQL执行失败", e);
        }
    }

    /**
     * 转换ResultSet为List
     */
    private List<Map<String, Object>> convertResultSetToList(ResultSet resultSet) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<>();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()) {
            Map<String, Object> row = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = resultSet.getObject(i);
                row.put(columnName, value);
            }
            results.add(row);
        }

        return results;
    }
}

2.2 外键字段分片

2.2.1 外键字段分片服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
/**
 * 外键字段分片服务
 */
@Service
public class ForeignKeyShardingService {

    @Autowired
    private ShardingAlgorithmService shardingAlgorithmService;

    @Autowired
    private DatabaseConnectionManager connectionManager;

    private final int SHARD_COUNT = 8;

    /**
     * 根据外键分片
     */
    public int shardByForeignKey(String foreignKey) {
        try {
            // 使用哈希算法
            return shardingAlgorithmService.hashSharding(foreignKey, SHARD_COUNT);

        } catch (Exception e) {
            log.error("外键分片失败: {}", foreignKey, e);
            throw new ShardingException("外键分片失败", e);
        }
    }

    /**
     * 根据外键获取表名
     */
    public String getTableNameByForeignKey(String tablePrefix, String foreignKey) {
        int shardIndex = shardByForeignKey(foreignKey);
        return String.format("%s_%d", tablePrefix, shardIndex);
    }

    /**
     * 根据外键执行SQL
     */
    public Object executeByForeignKey(String tablePrefix, String foreignKey, String sql, Object... params) {
        try {
            // 获取分片表名
            String tableName = getTableNameByForeignKey(tablePrefix, foreignKey);

            // 替换SQL中的表名
            String shardedSQL = replaceTableName(sql, tableName);

            // 获取数据源
            int shardIndex = shardByForeignKey(foreignKey);
            DataSource dataSource = connectionManager.getDataSource(shardIndex);

            // 执行SQL
            return executeSQL(dataSource, shardedSQL, params);

        } catch (Exception e) {
            log.error("根据外键执行SQL失败", e);
            throw new ShardingException("根据外键执行SQL失败", e);
        }
    }

    /**
     * 关联查询处理
     */
    public List<Map<String, Object>> executeJoinQuery(String mainTablePrefix, String mainForeignKey, 
            String joinTablePrefix, String joinForeignKey, String joinSQL) {
        try {
            // 获取主表分片
            int mainShardIndex = shardByForeignKey(mainForeignKey);
            String mainTableName = String.format("%s_%d", mainTablePrefix, mainShardIndex);

            // 获取关联表分片
            int joinShardIndex = shardByForeignKey(joinForeignKey);
            String joinTableName = String.format("%s_%d", joinTablePrefix, joinShardIndex);

            // 检查是否在同一分片
            if (mainShardIndex == joinShardIndex) {
                // 同分片关联查询
                return executeSameShardJoinQuery(mainTableName, joinTableName, joinSQL, mainShardIndex);
            } else {
                // 跨分片关联查询
                return executeCrossShardJoinQuery(mainTableName, joinTableName, joinSQL, mainShardIndex, joinShardIndex);
            }

        } catch (Exception e) {
            log.error("关联查询失败", e);
            throw new ShardingException("关联查询失败", e);
        }
    }

    /**
     * 同分片关联查询
     */
    private List<Map<String, Object>> executeSameShardJoinQuery(String mainTableName, String joinTableName, 
            String joinSQL, int shardIndex) {
        try {
            // 替换SQL中的表名
            String shardedSQL = joinSQL.replaceAll("\\{main_table\\}", mainTableName)
                    .replaceAll("\\{join_table\\}", joinTableName);

            // 获取数据源
            DataSource dataSource = connectionManager.getDataSource(shardIndex);

            // 执行查询
            return executeQuery(dataSource, shardedSQL);

        } catch (Exception e) {
            log.error("同分片关联查询失败", e);
            throw new ShardingException("同分片关联查询失败", e);
        }
    }

    /**
     * 跨分片关联查询
     */
    private List<Map<String, Object>> executeCrossShardJoinQuery(String mainTableName, String joinTableName, 
            String joinSQL, int mainShardIndex, int joinShardIndex) {
        try {
            List<Map<String, Object>> results = new ArrayList<>();

            // 分别查询两个分片
            DataSource mainDataSource = connectionManager.getDataSource(mainShardIndex);
            DataSource joinDataSource = connectionManager.getDataSource(joinShardIndex);

            // 查询主表数据
            String mainSQL = joinSQL.replaceAll("\\{main_table\\}", mainTableName)
                    .replaceAll("\\{join_table\\}", "1=0"); // 避免关联
            List<Map<String, Object>> mainResults = executeQuery(mainDataSource, mainSQL);

            // 查询关联表数据
            String joinSQLQuery = joinSQL.replaceAll("\\{main_table\\}", "1=0") // 避免关联
                    .replaceAll("\\{join_table\\}", joinTableName);
            List<Map<String, Object>> joinResults = executeQuery(joinDataSource, joinSQLQuery);

            // 在应用层进行关联
            results = performApplicationJoin(mainResults, joinResults);

            return results;

        } catch (Exception e) {
            log.error("跨分片关联查询失败", e);
            throw new ShardingException("跨分片关联查询失败", e);
        }
    }

    /**
     * 应用层关联
     */
    private List<Map<String, Object>> performApplicationJoin(List<Map<String, Object>> mainResults, 
            List<Map<String, Object>> joinResults) {
        List<Map<String, Object>> results = new ArrayList<>();

        // 构建关联表索引
        Map<String, List<Map<String, Object>>> joinIndex = new HashMap<>();
        for (Map<String, Object> joinResult : joinResults) {
            String joinKey = joinResult.get("join_key").toString();
            joinIndex.computeIfAbsent(joinKey, k -> new ArrayList<>()).add(joinResult);
        }

        // 关联查询
        for (Map<String, Object> mainResult : mainResults) {
            String mainKey = mainResult.get("main_key").toString();
            List<Map<String, Object>> matchedJoins = joinIndex.get(mainKey);

            if (matchedJoins != null) {
                for (Map<String, Object> matchedJoin : matchedJoins) {
                    Map<String, Object> joinedResult = new HashMap<>();
                    joinedResult.putAll(mainResult);
                    joinedResult.putAll(matchedJoin);
                    results.add(joinedResult);
                }
            }
        }

        return results;
    }

    /**
     * 执行查询
     */
    private List<Map<String, Object>> executeQuery(DataSource dataSource, String sql) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {

            return convertResultSetToList(resultSet);

        } catch (SQLException e) {
            log.error("查询执行失败", e);
            throw new DatabaseException("查询执行失败", e);
        }
    }

    /**
     * 替换SQL中的表名
     */
    private String replaceTableName(String sql, String tableName) {
        return sql.replaceAll("\\{table_name\\}", tableName);
    }

    /**
     * 转换ResultSet为List
     */
    private List<Map<String, Object>> convertResultSetToList(ResultSet resultSet) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<>();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()) {
            Map<String, Object> row = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = resultSet.getObject(i);
                row.put(columnName, value);
            }
            results.add(row);
        }

        return results;
    }
}

2.3 时间字段分片

2.3.1 时间字段分片服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
/**
 * 时间字段分片服务
 */
@Service
public class TimeFieldShardingService {

    @Autowired
    private ShardingAlgorithmService shardingAlgorithmService;

    @Autowired
    private DatabaseConnectionManager connectionManager;

    private final int SHARD_COUNT = 12; // 按月分片

    /**
     * 根据时间分片
     */
    public int shardByTime(Date time) {
        try {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(time);

            // 按月分片
            int month = calendar.get(Calendar.MONTH);
            return month % SHARD_COUNT;

        } catch (Exception e) {
            log.error("时间分片失败: {}", time, e);
            throw new ShardingException("时间分片失败", e);
        }
    }

    /**
     * 根据时间获取表名
     */
    public String getTableNameByTime(String tablePrefix, Date time) {
        int shardIndex = shardByTime(time);
        return String.format("%s_%d", tablePrefix, shardIndex);
    }

    /**
     * 根据时间范围获取表名列表
     */
    public List<String> getTableNamesByTimeRange(String tablePrefix, Date startTime, Date endTime) {
        List<String> tableNames = new ArrayList<>();

        Calendar startCalendar = Calendar.getInstance();
        startCalendar.setTime(startTime);

        Calendar endCalendar = Calendar.getInstance();
        endCalendar.setTime(endTime);

        // 计算时间范围内的所有月份
        while (startCalendar.before(endCalendar) || startCalendar.equals(endCalendar)) {
            int shardIndex = shardByTime(startCalendar.getTime());
            String tableName = String.format("%s_%d", tablePrefix, shardIndex);

            if (!tableNames.contains(tableName)) {
                tableNames.add(tableName);
            }

            startCalendar.add(Calendar.MONTH, 1);
        }

        return tableNames;
    }

    /**
     * 根据时间范围执行SQL
     */
    public List<Map<String, Object>> executeByTimeRange(String tablePrefix, Date startTime, Date endTime, 
            String sql, Object... params) {
        try {
            List<Map<String, Object>> allResults = new ArrayList<>();

            // 获取时间范围内的所有表名
            List<String> tableNames = getTableNamesByTimeRange(tablePrefix, startTime, endTime);

            // 并行查询所有表
            tableNames.parallelStream().forEach(tableName -> {
                try {
                    String shardedSQL = replaceTableName(sql, tableName);
                    DataSource dataSource = connectionManager.getDataSource(0); // 假设单库多表

                    List<Map<String, Object>> results = executeQuery(dataSource, shardedSQL, params);

                    synchronized (allResults) {
                        allResults.addAll(results);
                    }

                } catch (Exception e) {
                    log.error("表{}查询失败", tableName, e);
                }
            });

            return allResults;

        } catch (Exception e) {
            log.error("时间范围查询失败", e);
            throw new ShardingException("时间范围查询失败", e);
        }
    }

    /**
     * 时间分片数据迁移
     */
    public void migrateDataByTime(String sourceTablePrefix, String targetTablePrefix, Date startTime, Date endTime) {
        try {
            // 获取时间范围内的所有表名
            List<String> sourceTableNames = getTableNamesByTimeRange(sourceTablePrefix, startTime, endTime);

            for (String sourceTableName : sourceTableNames) {
                // 计算目标表名
                String timeStr = sourceTableName.substring(sourceTableName.lastIndexOf("_") + 1);
                int shardIndex = Integer.parseInt(timeStr);
                String targetTableName = String.format("%s_%d", targetTablePrefix, shardIndex);

                // 执行数据迁移
                migrateTableData(sourceTableName, targetTableName);
            }

        } catch (Exception e) {
            log.error("时间分片数据迁移失败", e);
            throw new ShardingException("时间分片数据迁移失败", e);
        }
    }

    /**
     * 迁移表数据
     */
    private void migrateTableData(String sourceTableName, String targetTableName) {
        try {
            DataSource dataSource = connectionManager.getDataSource(0);

            // 创建目标表
            String createTableSQL = String.format("CREATE TABLE %s LIKE %s", targetTableName, sourceTableName);
            executeUpdate(dataSource, createTableSQL);

            // 迁移数据
            String migrateSQL = String.format("INSERT INTO %s SELECT * FROM %s", targetTableName, sourceTableName);
            executeUpdate(dataSource, migrateSQL);

            log.info("表数据迁移完成: {} -> {}", sourceTableName, targetTableName);

        } catch (Exception e) {
            log.error("表数据迁移失败: {} -> {}", sourceTableName, targetTableName, e);
            throw new ShardingException("表数据迁移失败", e);
        }
    }

    /**
     * 执行查询
     */
    private List<Map<String, Object>> executeQuery(DataSource dataSource, String sql, Object... params) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {

            // 设置参数
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i + 1, params[i]);
            }

            // 执行查询
            ResultSet resultSet = statement.executeQuery();
            return convertResultSetToList(resultSet);

        } catch (SQLException e) {
            log.error("查询执行失败", e);
            throw new DatabaseException("查询执行失败", e);
        }
    }

    /**
     * 执行更新
     */
    private int executeUpdate(DataSource dataSource, String sql) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {

            return statement.executeUpdate();

        } catch (SQLException e) {
            log.error("更新执行失败", e);
            throw new DatabaseException("更新执行失败", e);
        }
    }

    /**
     * 替换SQL中的表名
     */
    private String replaceTableName(String sql, String tableName) {
        return sql.replaceAll("\\{table_name\\}", tableName);
    }

    /**
     * 转换ResultSet为List
     */
    private List<Map<String, Object>> convertResultSetToList(ResultSet resultSet) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<>();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()) {
            Map<String, Object> row = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = resultSet.getObject(i);
                row.put(columnName, value);
            }
            results.add(row);
        }

        return results;
    }
}

三、分表字段选择方案

3.1 分表字段评估

3.1.1 分表字段评估服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
/**
 * 分表字段评估服务
 */
@Service
public class ShardingFieldEvaluationService {

    @Autowired
    private DatabaseConnectionManager connectionManager;

    @Autowired
    private DataAnalysisService dataAnalysisService;

    /**
     * 评估分表字段
     */
    public ShardingFieldEvaluationResult evaluateShardingField(String tableName, String fieldName, 
            ShardingFieldType fieldType) {
        try {
            ShardingFieldEvaluationResult result = new ShardingFieldEvaluationResult();
            result.setTableName(tableName);
            result.setFieldName(fieldName);
            result.setFieldType(fieldType);

            // 1. 数据分布评估
            DataDistributionEvaluation distributionEval = evaluateDataDistribution(tableName, fieldName);
            result.setDataDistribution(distributionEval);

            // 2. 查询频率评估
            QueryFrequencyEvaluation frequencyEval = evaluateQueryFrequency(tableName, fieldName);
            result.setQueryFrequency(frequencyEval);

            // 3. 关联查询评估
            JoinQueryEvaluation joinEval = evaluateJoinQuery(tableName, fieldName);
            result.setJoinQuery(joinEval);

            // 4. 热点数据评估
            HotspotDataEvaluation hotspotEval = evaluateHotspotData(tableName, fieldName);
            result.setHotspotData(hotspotEval);

            // 5. 综合评分
            double overallScore = calculateOverallScore(distributionEval, frequencyEval, joinEval, hotspotEval);
            result.setOverallScore(overallScore);

            // 6. 推荐建议
            String recommendation = generateRecommendation(overallScore, fieldType);
            result.setRecommendation(recommendation);

            return result;

        } catch (Exception e) {
            log.error("分表字段评估失败", e);
            throw new ShardingException("分表字段评估失败", e);
        }
    }

    /**
     * 评估数据分布
     */
    private DataDistributionEvaluation evaluateDataDistribution(String tableName, String fieldName) {
        try {
            DataSource dataSource = connectionManager.getDataSource(0);

            // 查询数据分布统计
            String sql = String.format("SELECT %s, COUNT(*) as count FROM %s GROUP BY %s ORDER BY count DESC", 
                    fieldName, tableName, fieldName);

            List<Map<String, Object>> results = executeQuery(dataSource, sql);

            DataDistributionEvaluation evaluation = new DataDistributionEvaluation();

            if (results.isEmpty()) {
                evaluation.setScore(0.0);
                evaluation.setDescription("无数据分布信息");
                return evaluation;
            }

            // 计算数据分布均匀性
            List<Long> counts = results.stream()
                    .map(result -> Long.parseLong(result.get("count").toString()))
                    .collect(Collectors.toList());

            double uniformity = calculateUniformity(counts);
            evaluation.setUniformity(uniformity);

            // 计算数据倾斜度
            double skewness = calculateSkewness(counts);
            evaluation.setSkewness(skewness);

            // 计算评分
            double score = calculateDistributionScore(uniformity, skewness);
            evaluation.setScore(score);

            // 生成描述
            String description = String.format("数据分布均匀性: %.2f, 数据倾斜度: %.2f", uniformity, skewness);
            evaluation.setDescription(description);

            return evaluation;

        } catch (Exception e) {
            log.error("数据分布评估失败", e);
            return new DataDistributionEvaluation();
        }
    }

    /**
     * 评估查询频率
     */
    private QueryFrequencyEvaluation evaluateQueryFrequency(String tableName, String fieldName) {
        try {
            // 从查询日志分析查询频率
            List<QueryLog> queryLogs = dataAnalysisService.getQueryLogsByTable(tableName);

            QueryFrequencyEvaluation evaluation = new QueryFrequencyEvaluation();

            if (queryLogs.isEmpty()) {
                evaluation.setScore(0.0);
                evaluation.setDescription("无查询频率信息");
                return evaluation;
            }

            // 统计字段在WHERE条件中的出现频率
            long fieldQueryCount = queryLogs.stream()
                    .filter(log -> log.getSql().toLowerCase().contains(fieldName.toLowerCase()))
                    .count();

            double queryRatio = (double) fieldQueryCount / queryLogs.size();
            evaluation.setQueryRatio(queryRatio);

            // 计算评分
            double score = calculateQueryFrequencyScore(queryRatio);
            evaluation.setScore(score);

            // 生成描述
            String description = String.format("字段查询频率: %.2f%%", queryRatio * 100);
            evaluation.setDescription(description);

            return evaluation;

        } catch (Exception e) {
            log.error("查询频率评估失败", e);
            return new QueryFrequencyEvaluation();
        }
    }

    /**
     * 评估关联查询
     */
    private JoinQueryEvaluation evaluateJoinQuery(String tableName, String fieldName) {
        try {
            // 从查询日志分析关联查询
            List<QueryLog> joinQueryLogs = dataAnalysisService.getJoinQueryLogsByTable(tableName);

            JoinQueryEvaluation evaluation = new JoinQueryEvaluation();

            if (joinQueryLogs.isEmpty()) {
                evaluation.setScore(0.0);
                evaluation.setDescription("无关联查询信息");
                return evaluation;
            }

            // 统计字段在JOIN条件中的出现频率
            long fieldJoinCount = joinQueryLogs.stream()
                    .filter(log -> log.getSql().toLowerCase().contains(fieldName.toLowerCase()))
                    .count();

            double joinRatio = (double) fieldJoinCount / joinQueryLogs.size();
            evaluation.setJoinRatio(joinRatio);

            // 计算评分
            double score = calculateJoinQueryScore(joinRatio);
            evaluation.setScore(score);

            // 生成描述
            String description = String.format("字段关联查询频率: %.2f%%", joinRatio * 100);
            evaluation.setDescription(description);

            return evaluation;

        } catch (Exception e) {
            log.error("关联查询评估失败", e);
            return new JoinQueryEvaluation();
        }
    }

    /**
     * 评估热点数据
     */
    private HotspotDataEvaluation evaluateHotspotData(String tableName, String fieldName) {
        try {
            DataSource dataSource = connectionManager.getDataSource(0);

            // 查询热点数据统计
            String sql = String.format("SELECT %s, COUNT(*) as count FROM %s GROUP BY %s ORDER BY count DESC LIMIT 10", 
                    fieldName, tableName, fieldName);

            List<Map<String, Object>> results = executeQuery(dataSource, sql);

            HotspotDataEvaluation evaluation = new HotspotDataEvaluation();

            if (results.isEmpty()) {
                evaluation.setScore(0.0);
                evaluation.setDescription("无热点数据信息");
                return evaluation;
            }

            // 计算热点数据集中度
            List<Long> counts = results.stream()
                    .map(result -> Long.parseLong(result.get("count").toString()))
                    .collect(Collectors.toList());

            double concentration = calculateConcentration(counts);
            evaluation.setConcentration(concentration);

            // 计算评分
            double score = calculateHotspotDataScore(concentration);
            evaluation.setScore(score);

            // 生成描述
            String description = String.format("热点数据集中度: %.2f", concentration);
            evaluation.setDescription(description);

            return evaluation;

        } catch (Exception e) {
            log.error("热点数据评估失败", e);
            return new HotspotDataEvaluation();
        }
    }

    /**
     * 计算数据分布均匀性
     */
    private double calculateUniformity(List<Long> counts) {
        if (counts.isEmpty()) {
            return 0.0;
        }

        double mean = counts.stream().mapToLong(Long::longValue).average().orElse(0.0);
        double variance = counts.stream()
                .mapToDouble(count -> Math.pow(count - mean, 2))
                .average()
                .orElse(0.0);

        double standardDeviation = Math.sqrt(variance);
        return 1.0 - (standardDeviation / mean);
    }

    /**
     * 计算数据倾斜度
     */
    private double calculateSkewness(List<Long> counts) {
        if (counts.isEmpty()) {
            return 0.0;
        }

        long maxCount = counts.stream().mapToLong(Long::longValue).max().orElse(0L);
        long minCount = counts.stream().mapToLong(Long::longValue).min().orElse(0L);

        if (maxCount == 0) {
            return 0.0;
        }

        return (double) (maxCount - minCount) / maxCount;
    }

    /**
     * 计算数据分布评分
     */
    private double calculateDistributionScore(double uniformity, double skewness) {
        return (uniformity * 0.7 + (1.0 - skewness) * 0.3) * 100;
    }

    /**
     * 计算查询频率评分
     */
    private double calculateQueryFrequencyScore(double queryRatio) {
        return Math.min(queryRatio * 200, 100);
    }

    /**
     * 计算关联查询评分
     */
    private double calculateJoinQueryScore(double joinRatio) {
        return Math.min(joinRatio * 150, 100);
    }

    /**
     * 计算热点数据评分
     */
    private double calculateHotspotDataScore(double concentration) {
        return (1.0 - concentration) * 100;
    }

    /**
     * 计算热点数据集中度
     */
    private double calculateConcentration(List<Long> counts) {
        if (counts.isEmpty()) {
            return 0.0;
        }

        long totalCount = counts.stream().mapToLong(Long::longValue).sum();
        long maxCount = counts.stream().mapToLong(Long::longValue).max().orElse(0L);

        if (totalCount == 0) {
            return 0.0;
        }

        return (double) maxCount / totalCount;
    }

    /**
     * 计算综合评分
     */
    private double calculateOverallScore(DataDistributionEvaluation distributionEval, 
            QueryFrequencyEvaluation frequencyEval, JoinQueryEvaluation joinEval, 
            HotspotDataEvaluation hotspotEval) {

        double distributionScore = distributionEval.getScore();
        double frequencyScore = frequencyEval.getScore();
        double joinScore = joinEval.getScore();
        double hotspotScore = hotspotEval.getScore();

        // 加权平均
        return (distributionScore * 0.4 + frequencyScore * 0.3 + joinScore * 0.2 + hotspotScore * 0.1);
    }

    /**
     * 生成推荐建议
     */
    private String generateRecommendation(double overallScore, ShardingFieldType fieldType) {
        if (overallScore >= 80) {
            return "强烈推荐作为分表字段";
        } else if (overallScore >= 60) {
            return "推荐作为分表字段";
        } else if (overallScore >= 40) {
            return "可以考虑作为分表字段";
        } else {
            return "不推荐作为分表字段";
        }
    }

    /**
     * 执行查询
     */
    private List<Map<String, Object>> executeQuery(DataSource dataSource, String sql) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {

            return convertResultSetToList(resultSet);

        } catch (SQLException e) {
            log.error("查询执行失败", e);
            throw new DatabaseException("查询执行失败", e);
        }
    }

    /**
     * 转换ResultSet为List
     */
    private List<Map<String, Object>> convertResultSetToList(ResultSet resultSet) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<>();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()) {
            Map<String, Object> row = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = resultSet.getObject(i);
                row.put(columnName, value);
            }
            results.add(row);
        }

        return results;
    }
}

3.2 复合分片键

3.2.1 复合分片键服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
/**
 * 复合分片键服务
 */
@Service
public class CompositeShardingKeyService {

    @Autowired
    private ShardingAlgorithmService shardingAlgorithmService;

    @Autowired
    private DatabaseConnectionManager connectionManager;

    private final int SHARD_COUNT = 8;

    /**
     * 根据复合分片键分片
     */
    public int shardByCompositeKey(CompositeShardingKey compositeKey) {
        try {
            // 计算复合分片键的哈希值
            String combinedKey = combineShardingKeys(compositeKey);

            // 使用一致性哈希算法
            return shardingAlgorithmService.consistentHashSharding(combinedKey, SHARD_COUNT);

        } catch (Exception e) {
            log.error("复合分片键分片失败", e);
            throw new ShardingException("复合分片键分片失败", e);
        }
    }

    /**
     * 组合分片键
     */
    private String combineShardingKeys(CompositeShardingKey compositeKey) {
        StringBuilder combinedKey = new StringBuilder();

        // 按权重排序分片键
        List<ShardingKeyComponent> sortedComponents = compositeKey.getComponents().stream()
                .sorted(Comparator.comparing(ShardingKeyComponent::getWeight).reversed())
                .collect(Collectors.toList());

        for (ShardingKeyComponent component : sortedComponents) {
            combinedKey.append(component.getKey()).append("_");
        }

        return combinedKey.toString();
    }

    /**
     * 根据复合分片键获取表名
     */
    public String getTableNameByCompositeKey(String tablePrefix, CompositeShardingKey compositeKey) {
        int shardIndex = shardByCompositeKey(compositeKey);
        return String.format("%s_%d", tablePrefix, shardIndex);
    }

    /**
     * 根据复合分片键执行SQL
     */
    public Object executeByCompositeKey(String tablePrefix, CompositeShardingKey compositeKey, 
            String sql, Object... params) {
        try {
            // 获取分片表名
            String tableName = getTableNameByCompositeKey(tablePrefix, compositeKey);

            // 替换SQL中的表名
            String shardedSQL = replaceTableName(sql, tableName);

            // 获取数据源
            int shardIndex = shardByCompositeKey(compositeKey);
            DataSource dataSource = connectionManager.getDataSource(shardIndex);

            // 执行SQL
            return executeSQL(dataSource, shardedSQL, params);

        } catch (Exception e) {
            log.error("根据复合分片键执行SQL失败", e);
            throw new ShardingException("根据复合分片键执行SQL失败", e);
        }
    }

    /**
     * 批量根据复合分片键执行SQL
     */
    public Map<String, Object> batchExecuteByCompositeKey(String tablePrefix, 
            Map<CompositeShardingKey, String> compositeKeySqlMap) {
        Map<String, Object> results = new HashMap<>();

        // 按分片分组
        Map<Integer, List<CompositeKeyOperation>> groupedOperations = new HashMap<>();

        for (Map.Entry<CompositeShardingKey, String> entry : compositeKeySqlMap.entrySet()) {
            CompositeShardingKey compositeKey = entry.getKey();
            String sql = entry.getValue();

            int shardIndex = shardByCompositeKey(compositeKey);
            groupedOperations.computeIfAbsent(shardIndex, k -> new ArrayList<>())
                    .add(new CompositeKeyOperation(compositeKey, sql));
        }

        // 并行执行各分片操作
        groupedOperations.entrySet().parallelStream().forEach(entry -> {
            int shardIndex = entry.getKey();
            List<CompositeKeyOperation> operations = entry.getValue();

            try {
                DataSource dataSource = connectionManager.getDataSource(shardIndex);
                String tableName = String.format("%s_%d", tablePrefix, shardIndex);

                for (CompositeKeyOperation operation : operations) {
                    String shardedSQL = replaceTableName(operation.getSql(), tableName);
                    Object result = executeSQL(dataSource, shardedSQL);
                    results.put(operation.getCompositeKey().toString(), result);
                }

            } catch (Exception e) {
                log.error("分片{}执行失败", shardIndex, e);
            }
        });

        return results;
    }

    /**
     * 复合分片键优化
     */
    public CompositeShardingKey optimizeCompositeKey(CompositeShardingKey originalKey, 
            Map<String, Double> fieldWeights) {
        try {
            CompositeShardingKey optimizedKey = new CompositeShardingKey();

            // 根据字段权重重新计算分片键权重
            for (ShardingKeyComponent component : originalKey.getComponents()) {
                String fieldName = component.getFieldName();
                double fieldWeight = fieldWeights.getOrDefault(fieldName, 1.0);

                ShardingKeyComponent optimizedComponent = new ShardingKeyComponent();
                optimizedComponent.setFieldName(fieldName);
                optimizedComponent.setKey(component.getKey());
                optimizedComponent.setWeight(component.getWeight() * fieldWeight);

                optimizedKey.addComponent(optimizedComponent);
            }

            return optimizedKey;

        } catch (Exception e) {
            log.error("复合分片键优化失败", e);
            throw new ShardingException("复合分片键优化失败", e);
        }
    }

    /**
     * 复合分片键性能测试
     */
    public CompositeShardingKeyPerformanceTestResult testCompositeKeyPerformance(
            CompositeShardingKey compositeKey, int testCount) {
        try {
            CompositeShardingKeyPerformanceTestResult result = new CompositeShardingKeyPerformanceTestResult();
            result.setCompositeKey(compositeKey);
            result.setTestCount(testCount);

            long startTime = System.currentTimeMillis();

            // 执行分片测试
            for (int i = 0; i < testCount; i++) {
                shardByCompositeKey(compositeKey);
            }

            long endTime = System.currentTimeMillis();
            long totalTime = endTime - startTime;

            result.setTotalTime(totalTime);
            result.setAverageTime((double) totalTime / testCount);
            result.setThroughput((double) testCount / totalTime * 1000); // 每秒处理数

            return result;

        } catch (Exception e) {
            log.error("复合分片键性能测试失败", e);
            throw new ShardingException("复合分片键性能测试失败", e);
        }
    }

    /**
     * 执行SQL
     */
    private Object executeSQL(DataSource dataSource, String sql, Object... params) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {

            // 设置参数
            for (int i = 0; i < params.length; i++) {
                statement.setObject(i + 1, params[i]);
            }

            // 执行SQL
            if (sql.trim().toUpperCase().startsWith("SELECT")) {
                ResultSet resultSet = statement.executeQuery();
                return convertResultSetToList(resultSet);
            } else {
                return statement.executeUpdate();
            }

        } catch (SQLException e) {
            log.error("SQL执行失败", e);
            throw new DatabaseException("SQL执行失败", e);
        }
    }

    /**
     * 替换SQL中的表名
     */
    private String replaceTableName(String sql, String tableName) {
        return sql.replaceAll("\\{table_name\\}", tableName);
    }

    /**
     * 转换ResultSet为List
     */
    private List<Map<String, Object>> convertResultSetToList(ResultSet resultSet) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<>();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()) {
            Map<String, Object> row = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = resultSet.getObject(i);
                row.put(columnName, value);
            }
            results.add(row);
        }

        return results;
    }
}

四、企业级分表字段应用

4.1 电商分表字段方案

4.1.1 电商分表字段设计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
/**
 * 电商分表字段设计服务
 */
@Service
public class EcommerceShardingFieldService {

    @Autowired
    private ShardingFieldEvaluationService evaluationService;

    @Autowired
    private CompositeShardingKeyService compositeShardingKeyService;

    /**
     * 用户表分表字段设计
     */
    public ShardingFieldDesignResult designUserTableShardingField() {
        try {
            ShardingFieldDesignResult result = new ShardingFieldDesignResult();
            result.setTableName("user");

            // 评估候选分表字段
            List<ShardingFieldEvaluationResult> evaluations = new ArrayList<>();

            // 1. 用户ID字段评估
            ShardingFieldEvaluationResult userIdEval = evaluationService.evaluateShardingField(
                    "user", "user_id", ShardingFieldType.PRIMARY_KEY);
            evaluations.add(userIdEval);

            // 2. 手机号字段评估
            ShardingFieldEvaluationResult phoneEval = evaluationService.evaluateShardingField(
                    "user", "phone", ShardingFieldType.BUSINESS_FIELD);
            evaluations.add(phoneEval);

            // 3. 注册时间字段评估
            ShardingFieldEvaluationResult registerTimeEval = evaluationService.evaluateShardingField(
                    "user", "register_time", ShardingFieldType.TIME_FIELD);
            evaluations.add(registerTimeEval);

            // 选择最佳分表字段
            ShardingFieldEvaluationResult bestField = evaluations.stream()
                    .max(Comparator.comparing(ShardingFieldEvaluationResult::getOverallScore))
                    .orElse(null);

            if (bestField != null) {
                result.setRecommendedField(bestField.getFieldName());
                result.setFieldType(bestField.getFieldType());
                result.setScore(bestField.getOverallScore());
                result.setRecommendation(bestField.getRecommendation());
            }

            // 设计复合分片键
            CompositeShardingKey compositeKey = designCompositeShardingKey("user");
            result.setCompositeShardingKey(compositeKey);

            return result;

        } catch (Exception e) {
            log.error("用户表分表字段设计失败", e);
            throw new ShardingException("用户表分表字段设计失败", e);
        }
    }

    /**
     * 订单表分表字段设计
     */
    public ShardingFieldDesignResult designOrderTableShardingField() {
        try {
            ShardingFieldDesignResult result = new ShardingFieldDesignResult();
            result.setTableName("order");

            // 评估候选分表字段
            List<ShardingFieldEvaluationResult> evaluations = new ArrayList<>();

            // 1. 订单ID字段评估
            ShardingFieldEvaluationResult orderIdEval = evaluationService.evaluateShardingField(
                    "order", "order_id", ShardingFieldType.PRIMARY_KEY);
            evaluations.add(orderIdEval);

            // 2. 用户ID字段评估
            ShardingFieldEvaluationResult userIdEval = evaluationService.evaluateShardingField(
                    "order", "user_id", ShardingFieldType.FOREIGN_KEY);
            evaluations.add(userIdEval);

            // 3. 创建时间字段评估
            ShardingFieldEvaluationResult createTimeEval = evaluationService.evaluateShardingField(
                    "order", "create_time", ShardingFieldType.TIME_FIELD);
            evaluations.add(createTimeEval);

            // 选择最佳分表字段
            ShardingFieldEvaluationResult bestField = evaluations.stream()
                    .max(Comparator.comparing(ShardingFieldEvaluationResult::getOverallScore))
                    .orElse(null);

            if (bestField != null) {
                result.setRecommendedField(bestField.getFieldName());
                result.setFieldType(bestField.getFieldType());
                result.setScore(bestField.getOverallScore());
                result.setRecommendation(bestField.getRecommendation());
            }

            // 设计复合分片键
            CompositeShardingKey compositeKey = designCompositeShardingKey("order");
            result.setCompositeShardingKey(compositeKey);

            return result;

        } catch (Exception e) {
            log.error("订单表分表字段设计失败", e);
            throw new ShardingException("订单表分表字段设计失败", e);
        }
    }

    /**
     * 商品表分表字段设计
     */
    public ShardingFieldDesignResult designProductTableShardingField() {
        try {
            ShardingFieldDesignResult result = new ShardingFieldDesignResult();
            result.setTableName("product");

            // 评估候选分表字段
            List<ShardingFieldEvaluationResult> evaluations = new ArrayList<>();

            // 1. 商品ID字段评估
            ShardingFieldEvaluationResult productIdEval = evaluationService.evaluateShardingField(
                    "product", "product_id", ShardingFieldType.PRIMARY_KEY);
            evaluations.add(productIdEval);

            // 2. 分类ID字段评估
            ShardingFieldEvaluationResult categoryIdEval = evaluationService.evaluateShardingField(
                    "product", "category_id", ShardingFieldType.FOREIGN_KEY);
            evaluations.add(categoryIdEval);

            // 3. 上架时间字段评估
            ShardingFieldEvaluationResult publishTimeEval = evaluationService.evaluateShardingField(
                    "product", "publish_time", ShardingFieldType.TIME_FIELD);
            evaluations.add(publishTimeEval);

            // 选择最佳分表字段
            ShardingFieldEvaluationResult bestField = evaluations.stream()
                    .max(Comparator.comparing(ShardingFieldEvaluationResult::getOverallScore))
                    .orElse(null);

            if (bestField != null) {
                result.setRecommendedField(bestField.getFieldName());
                result.setFieldType(bestField.getFieldType());
                result.setScore(bestField.getOverallScore());
                result.setRecommendation(bestField.getRecommendation());
            }

            // 设计复合分片键
            CompositeShardingKey compositeKey = designCompositeShardingKey("product");
            result.setCompositeShardingKey(compositeKey);

            return result;

        } catch (Exception e) {
            log.error("商品表分表字段设计失败", e);
            throw new ShardingException("商品表分表字段设计失败", e);
        }
    }

    /**
     * 设计复合分片键
     */
    private CompositeShardingKey designCompositeShardingKey(String tableName) {
        CompositeShardingKey compositeKey = new CompositeShardingKey();

        switch (tableName) {
            case "user":
                // 用户表复合分片键：用户ID + 手机号
                ShardingKeyComponent userIdComponent = new ShardingKeyComponent();
                userIdComponent.setFieldName("user_id");
                userIdComponent.setKey("user_id");
                userIdComponent.setWeight(0.7);
                compositeKey.addComponent(userIdComponent);

                ShardingKeyComponent phoneComponent = new ShardingKeyComponent();
                phoneComponent.setFieldName("phone");
                phoneComponent.setKey("phone");
                phoneComponent.setWeight(0.3);
                compositeKey.addComponent(phoneComponent);
                break;

            case "order":
                // 订单表复合分片键：订单ID + 用户ID
                ShardingKeyComponent orderIdComponent = new ShardingKeyComponent();
                orderIdComponent.setFieldName("order_id");
                orderIdComponent.setKey("order_id");
                orderIdComponent.setWeight(0.6);
                compositeKey.addComponent(orderIdComponent);

                ShardingKeyComponent orderUserIdComponent = new ShardingKeyComponent();
                orderUserIdComponent.setFieldName("user_id");
                orderUserIdComponent.setKey("user_id");
                orderUserIdComponent.setWeight(0.4);
                compositeKey.addComponent(orderUserIdComponent);
                break;

            case "product":
                // 商品表复合分片键：商品ID + 分类ID
                ShardingKeyComponent productIdComponent = new ShardingKeyComponent();
                productIdComponent.setFieldName("product_id");
                productIdComponent.setKey("product_id");
                productIdComponent.setWeight(0.8);
                compositeKey.addComponent(productIdComponent);

                ShardingKeyComponent categoryIdComponent = new ShardingKeyComponent();
                categoryIdComponent.setFieldName("category_id");
                categoryIdComponent.setKey("category_id");
                categoryIdComponent.setWeight(0.2);
                compositeKey.addComponent(categoryIdComponent);
                break;
        }

        return compositeKey;
    }

    /**
     * 分表字段性能测试
     */
    public ShardingFieldPerformanceTestResult testShardingFieldPerformance(String tableName, 
            String fieldName, int testCount) {
        try {
            ShardingFieldPerformanceTestResult result = new ShardingFieldPerformanceTestResult();
            result.setTableName(tableName);
            result.setFieldName(fieldName);
            result.setTestCount(testCount);

            long startTime = System.currentTimeMillis();

            // 执行分片测试
            for (int i = 0; i < testCount; i++) {
                String testValue = generateTestValue(fieldName, i);
                // 这里应该调用实际的分片算法
                // int shardIndex = shardByField(tableName, fieldName, testValue);
            }

            long endTime = System.currentTimeMillis();
            long totalTime = endTime - startTime;

            result.setTotalTime(totalTime);
            result.setAverageTime((double) totalTime / testCount);
            result.setThroughput((double) testCount / totalTime * 1000); // 每秒处理数

            return result;

        } catch (Exception e) {
            log.error("分表字段性能测试失败", e);
            throw new ShardingException("分表字段性能测试失败", e);
        }
    }

    /**
     * 生成测试值
     */
    private String generateTestValue(String fieldName, int index) {
        switch (fieldName) {
            case "user_id":
                return "user_" + index;
            case "order_id":
                return "order_" + index;
            case "product_id":
                return "product_" + index;
            case "phone":
                return "138" + String.format("%08d", index);
            default:
                return "test_" + index;
        }
    }
}

4.2 社交分表字段方案

4.2.1 社交分表字段设计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
/**
 * 社交分表字段设计服务
 */
@Service
public class SocialShardingFieldService {

    @Autowired
    private ShardingFieldEvaluationService evaluationService;

    @Autowired
    private CompositeShardingKeyService compositeShardingKeyService;

    /**
     * 用户表分表字段设计
     */
    public ShardingFieldDesignResult designUserTableShardingField() {
        try {
            ShardingFieldDesignResult result = new ShardingFieldDesignResult();
            result.setTableName("user");

            // 评估候选分表字段
            List<ShardingFieldEvaluationResult> evaluations = new ArrayList<>();

            // 1. 用户ID字段评估
            ShardingFieldEvaluationResult userIdEval = evaluationService.evaluateShardingField(
                    "user", "user_id", ShardingFieldType.PRIMARY_KEY);
            evaluations.add(userIdEval);

            // 2. 用户名字段评估
            ShardingFieldEvaluationResult usernameEval = evaluationService.evaluateShardingField(
                    "user", "username", ShardingFieldType.BUSINESS_FIELD);
            evaluations.add(usernameEval);

            // 3. 注册时间字段评估
            ShardingFieldEvaluationResult registerTimeEval = evaluationService.evaluateShardingField(
                    "user", "register_time", ShardingFieldType.TIME_FIELD);
            evaluations.add(registerTimeEval);

            // 选择最佳分表字段
            ShardingFieldEvaluationResult bestField = evaluations.stream()
                    .max(Comparator.comparing(ShardingFieldEvaluationResult::getOverallScore))
                    .orElse(null);

            if (bestField != null) {
                result.setRecommendedField(bestField.getFieldName());
                result.setFieldType(bestField.getFieldType());
                result.setScore(bestField.getOverallScore());
                result.setRecommendation(bestField.getRecommendation());
            }

            // 设计复合分片键
            CompositeShardingKey compositeKey = designCompositeShardingKey("user");
            result.setCompositeShardingKey(compositeKey);

            return result;

        } catch (Exception e) {
            log.error("用户表分表字段设计失败", e);
            throw new ShardingException("用户表分表字段设计失败", e);
        }
    }

    /**
     * 帖子表分表字段设计
     */
    public ShardingFieldDesignResult designPostTableShardingField() {
        try {
            ShardingFieldDesignResult result = new ShardingFieldDesignResult();
            result.setTableName("post");

            // 评估候选分表字段
            List<ShardingFieldEvaluationResult> evaluations = new ArrayList<>();

            // 1. 帖子ID字段评估
            ShardingFieldEvaluationResult postIdEval = evaluationService.evaluateShardingField(
                    "post", "post_id", ShardingFieldType.PRIMARY_KEY);
            evaluations.add(postIdEval);

            // 2. 用户ID字段评估
            ShardingFieldEvaluationResult userIdEval = evaluationService.evaluateShardingField(
                    "post", "user_id", ShardingFieldType.FOREIGN_KEY);
            evaluations.add(userIdEval);

            // 3. 创建时间字段评估
            ShardingFieldEvaluationResult createTimeEval = evaluationService.evaluateShardingField(
                    "post", "create_time", ShardingFieldType.TIME_FIELD);
            evaluations.add(createTimeEval);

            // 选择最佳分表字段
            ShardingFieldEvaluationResult bestField = evaluations.stream()
                    .max(Comparator.comparing(ShardingFieldEvaluationResult::getOverallScore))
                    .orElse(null);

            if (bestField != null) {
                result.setRecommendedField(bestField.getFieldName());
                result.setFieldType(bestField.getFieldType());
                result.setScore(bestField.getOverallScore());
                result.setRecommendation(bestField.getRecommendation());
            }

            // 设计复合分片键
            CompositeShardingKey compositeKey = designCompositeShardingKey("post");
            result.setCompositeShardingKey(compositeKey);

            return result;

        } catch (Exception e) {
            log.error("帖子表分表字段设计失败", e);
            throw new ShardingException("帖子表分表字段设计失败", e);
        }
    }

    /**
     * 评论表分表字段设计
     */
    public ShardingFieldDesignResult designCommentTableShardingField() {
        try {
            ShardingFieldDesignResult result = new ShardingFieldDesignResult();
            result.setTableName("comment");

            // 评估候选分表字段
            List<ShardingFieldEvaluationResult> evaluations = new ArrayList<>();

            // 1. 评论ID字段评估
            ShardingFieldEvaluationResult commentIdEval = evaluationService.evaluateShardingField(
                    "comment", "comment_id", ShardingFieldType.PRIMARY_KEY);
            evaluations.add(commentIdEval);

            // 2. 帖子ID字段评估
            ShardingFieldEvaluationResult postIdEval = evaluationService.evaluateShardingField(
                    "comment", "post_id", ShardingFieldType.FOREIGN_KEY);
            evaluations.add(postIdEval);

            // 3. 用户ID字段评估
            ShardingFieldEvaluationResult userIdEval = evaluationService.evaluateShardingField(
                    "comment", "user_id", ShardingFieldType.FOREIGN_KEY);
            evaluations.add(userIdEval);

            // 选择最佳分表字段
            ShardingFieldEvaluationResult bestField = evaluations.stream()
                    .max(Comparator.comparing(ShardingFieldEvaluationResult::getOverallScore))
                    .orElse(null);

            if (bestField != null) {
                result.setRecommendedField(bestField.getFieldName());
                result.setFieldType(bestField.getFieldType());
                result.setScore(bestField.getOverallScore());
                result.setRecommendation(bestField.getRecommendation());
            }

            // 设计复合分片键
            CompositeShardingKey compositeKey = designCompositeShardingKey("comment");
            result.setCompositeShardingKey(compositeKey);

            return result;

        } catch (Exception e) {
            log.error("评论表分表字段设计失败", e);
            throw new ShardingException("评论表分表字段设计失败", e);
        }
    }

    /**
     * 设计复合分片键
     */
    private CompositeShardingKey designCompositeShardingKey(String tableName) {
        CompositeShardingKey compositeKey = new CompositeShardingKey();

        switch (tableName) {
            case "user":
                // 用户表复合分片键：用户ID + 用户名
                ShardingKeyComponent userIdComponent = new ShardingKeyComponent();
                userIdComponent.setFieldName("user_id");
                userIdComponent.setKey("user_id");
                userIdComponent.setWeight(0.8);
                compositeKey.addComponent(userIdComponent);

                ShardingKeyComponent usernameComponent = new ShardingKeyComponent();
                usernameComponent.setFieldName("username");
                usernameComponent.setKey("username");
                usernameComponent.setWeight(0.2);
                compositeKey.addComponent(usernameComponent);
                break;

            case "post":
                // 帖子表复合分片键：帖子ID + 用户ID
                ShardingKeyComponent postIdComponent = new ShardingKeyComponent();
                postIdComponent.setFieldName("post_id");
                postIdComponent.setKey("post_id");
                postIdComponent.setWeight(0.6);
                compositeKey.addComponent(postIdComponent);

                ShardingKeyComponent postUserIdComponent = new ShardingKeyComponent();
                postUserIdComponent.setFieldName("user_id");
                postUserIdComponent.setKey("user_id");
                postUserIdComponent.setWeight(0.4);
                compositeKey.addComponent(postUserIdComponent);
                break;

            case "comment":
                // 评论表复合分片键：评论ID + 帖子ID
                ShardingKeyComponent commentIdComponent = new ShardingKeyComponent();
                commentIdComponent.setFieldName("comment_id");
                commentIdComponent.setKey("comment_id");
                commentIdComponent.setWeight(0.5);
                compositeKey.addComponent(commentIdComponent);

                ShardingKeyComponent commentPostIdComponent = new ShardingKeyComponent();
                commentPostIdComponent.setFieldName("post_id");
                commentPostIdComponent.setKey("post_id");
                commentPostIdComponent.setWeight(0.5);
                compositeKey.addComponent(commentPostIdComponent);
                break;
        }

        return compositeKey;
    }
}

五、性能优化与监控

5.1 性能优化

5.1.1 分表字段性能优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
/**
 * 分表字段性能优化服务
 */
@Service
public class ShardingFieldPerformanceOptimizationService {

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    @Autowired
    private CaffeineCache localCache;

    private final String SHARDING_FIELD_CACHE_PREFIX = "sharding_field_cache:";

    /**
     * 缓存分片结果
     */
    public int getShardIndexWithCache(String fieldName, String fieldValue, String algorithm) {
        String cacheKey = SHARDING_FIELD_CACHE_PREFIX + fieldName + ":" + algorithm + ":" + DigestUtil.md5Hex(fieldValue);

        // 1. 尝试从本地缓存获取
        Integer cachedResult = (Integer) localCache.getIfPresent(cacheKey);
        if (cachedResult != null) {
            return cachedResult;
        }

        // 2. 从Redis获取
        String redisCacheKey = "redis_cache:" + cacheKey;
        Integer redisResult = (Integer) redisTemplate.opsForValue().get(redisCacheKey);
        if (redisResult != null) {
            localCache.put(cacheKey, redisResult);
            return redisResult;
        }

        // 3. 计算分片
        int shardIndex = calculateShardIndex(fieldName, fieldValue, algorithm);

        // 4. 写入缓存
        localCache.put(cacheKey, shardIndex);
        redisTemplate.opsForValue().set(redisCacheKey, shardIndex, Duration.ofHours(1));

        return shardIndex;
    }

    /**
     * 批量分片优化
     */
    public Map<String, Integer> batchShardingOptimized(Map<String, String> fieldValueMap, String algorithm) {
        Map<String, Integer> shardResults = new HashMap<>();

        // 使用并行流处理
        fieldValueMap.entrySet().parallelStream().forEach(entry -> {
            String fieldName = entry.getKey();
            String fieldValue = entry.getValue();

            try {
                int shardIndex = getShardIndexWithCache(fieldName, fieldValue, algorithm);
                shardResults.put(fieldName, shardIndex);
            } catch (Exception e) {
                log.error("批量分片失败: {}", fieldName, e);
                shardResults.put(fieldName, -1);
            }
        });

        return shardResults;
    }

    /**
     * 预热分片缓存
     */
    @PostConstruct
    public void warmupShardingCache() {
        // 预热常用分片字段值
        Map<String, List<String>> commonFieldValues = getCommonFieldValues();
        String defaultAlgorithm = "modulo";

        for (Map.Entry<String, List<String>> entry : commonFieldValues.entrySet()) {
            String fieldName = entry.getKey();
            List<String> fieldValues = entry.getValue();

            for (String fieldValue : fieldValues) {
                try {
                    getShardIndexWithCache(fieldName, fieldValue, defaultAlgorithm);
                } catch (Exception e) {
                    log.error("预热分片缓存失败", e);
                }
            }
        }
    }

    /**
     * 清理过期缓存
     */
    @Scheduled(fixedRate = 300000) // 5分钟
    public void cleanupExpiredCache() {
        // 清理本地缓存
        localCache.cleanUp();

        // 清理Redis过期缓存
        cleanupRedisExpiredCache();
    }

    /**
     * 计算分片索引
     */
    private int calculateShardIndex(String fieldName, String fieldValue, String algorithm) {
        switch (algorithm) {
            case "modulo":
                return Math.abs(fieldValue.hashCode()) % 8;
            case "hash":
                return Math.abs(fieldValue.hashCode()) % 8;
            case "consistent_hash":
                return Math.abs(fieldValue.hashCode()) % 8;
            default:
                return Math.abs(fieldValue.hashCode()) % 8;
        }
    }

    /**
     * 获取常用字段值
     */
    private Map<String, List<String>> getCommonFieldValues() {
        Map<String, List<String>> commonValues = new HashMap<>();

        // 用户ID
        commonValues.put("user_id", Arrays.asList("user_1", "user_2", "user_3", "user_4", "user_5"));

        // 订单ID
        commonValues.put("order_id", Arrays.asList("order_1", "order_2", "order_3", "order_4", "order_5"));

        // 商品ID
        commonValues.put("product_id", Arrays.asList("product_1", "product_2", "product_3", "product_4", "product_5"));

        return commonValues;
    }

    /**
     * 清理Redis过期缓存
     */
    private void cleanupRedisExpiredCache() {
        try {
            Set<String> cacheKeys = redisTemplate.keys("redis_cache:" + SHARDING_FIELD_CACHE_PREFIX + "*");

            for (String key : cacheKeys) {
                Long ttl = redisTemplate.getExpire(key);
                if (ttl != null && ttl <= 0) {
                    redisTemplate.delete(key);
                }
            }
        } catch (Exception e) {
            log.error("清理Redis过期缓存失败", e);
        }
    }
}

5.2 监控告警

5.2.1 监控指标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
/**
 * 分表字段监控指标
 */
@Component
public class ShardingFieldMetrics {

    private final MeterRegistry meterRegistry;

    public ShardingFieldMetrics(MeterRegistry meterRegistry) {
        this.meterRegistry = meterRegistry;
    }

    /**
     * 记录分片字段查询
     */
    public void recordShardingFieldQuery(String fieldName, String algorithm) {
        Counter.builder("sharding.field.query.count")
                .description("分片字段查询次数")
                .tag("field_name", fieldName)
                .tag("algorithm", algorithm)
                .register(meterRegistry)
                .increment();
    }

    /**
     * 记录分片字段计算时间
     */
    public void recordShardingFieldCalculationTime(String fieldName, String algorithm, long duration) {
        Timer.builder("sharding.field.calculation.time")
                .description("分片字段计算时间")
                .tag("field_name", fieldName)
                .tag("algorithm", algorithm)
                .register(meterRegistry)
                .record(duration, TimeUnit.MILLISECONDS);
    }

    /**
     * 记录分片字段缓存命中率
     */
    public void recordShardingFieldCacheHit(String fieldName, String algorithm) {
        Counter.builder("sharding.field.cache.hit")
                .description("分片字段缓存命中次数")
                .tag("field_name", fieldName)
                .tag("algorithm", algorithm)
                .register(meterRegistry)
                .increment();
    }

    /**
     * 记录分片字段缓存未命中
     */
    public void recordShardingFieldCacheMiss(String fieldName, String algorithm) {
        Counter.builder("sharding.field.cache.miss")
                .description("分片字段缓存未命中次数")
                .tag("field_name", fieldName)
                .tag("algorithm", algorithm)
                .register(meterRegistry)
                .increment();
    }

    /**
     * 记录分片字段数据分布
     */
    public void recordShardingFieldDataDistribution(String fieldName, int shardIndex, long count) {
        Gauge.builder("sharding.field.data.distribution")
                .description("分片字段数据分布")
                .tag("field_name", fieldName)
                .tag("shard_index", String.valueOf(shardIndex))
                .register(meterRegistry, count);
    }

    /**
     * 记录分片字段热点数据
     */
    public void recordShardingFieldHotspot(String fieldName, String fieldValue, long count) {
        Counter.builder("sharding.field.hotspot")
                .description("分片字段热点数据")
                .tag("field_name", fieldName)
                .tag("field_value", fieldValue)
                .register(meterRegistry)
                .increment(count);
    }
}

5.2.2 告警规则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# prometheus-rules.yml
groups:
- name: sharding_field_alerts
  rules:
  - alert: HighShardingFieldCalculationTime
    expr: sharding_field_calculation_time{quantile="0.95"} > 100
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "分片字段计算时间过长"
      description: "分片字段计算时间P95超过100ms，当前值: {{ $value }}ms"
  
  - alert: LowShardingFieldCacheHitRate
    expr: rate(sharding_field_cache_hit[5m]) / (rate(sharding_field_cache_hit[5m]) + rate(sharding_field_cache_miss[5m])) < 0.8
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "分片字段缓存命中率过低"
      description: "分片字段缓存命中率低于80%，当前值: {{ $value }}"
  
  - alert: HighShardingFieldHotspot
    expr: rate(sharding_field_hotspot[5m]) > 1000
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "分片字段热点数据过多"
      description: "分片字段热点数据频率超过1000次/分钟，当前值: {{ $value }}"
  
  - alert: ShardingFieldDataDistributionSkewed
    expr: max(sharding_field_data_distribution) / min(sharding_field_data_distribution) > 3
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "分片字段数据分布倾斜"
      description: "分片字段数据分布倾斜度超过3:1，当前值: {{ $value }}"

六、总结

分表字段选择作为分库分表架构设计中的核心环节，直接影响着数据分片的均匀性、查询性能和系统扩展性。本文从分表字段策略到选择方案，从基础实现到企业级应用，系统梳理了分表字段选择的完整解决方案。

6.1 关键要点

  1. 字段类型选择：主键字段、外键字段、时间字段、业务字段各有特点，需要根据业务场景选择合适的字段类型
  2. 评估指标：数据分布、查询频率、关联查询、热点数据等指标需要综合考虑
  3. 复合分片键：通过组合多个字段形成复合分片键，提高分片的均匀性
  4. 性能优化：通过缓存、批量处理等手段提高分片字段计算性能
  5. 监控告警：建立完善的监控体系，及时发现和处理问题

6.2 最佳实践

  1. 字段选择：根据业务特征、数据特征、查询模式选择合适的分表字段
  2. 评估分析：通过数据分析和性能测试评估分表字段的效果
  3. 复合设计：使用复合分片键提高数据分布的均匀性
  4. 性能优化：使用缓存和批量处理提高分片计算性能
  5. 监控告警：建立完善的监控体系，确保系统稳定运行

通过以上措施，可以构建一个高效、稳定、可扩展的分表字段选择方案，为企业的各种业务场景提供数据分片支持。