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
| # ========== 分表策略 ==========
class ShardingStrategy:
"""分片策略"""
def __init__(self, shard_count: int):
self.shard_count = shard_count
def hash_sharding(self, key: str) -> int:
"""哈希分片"""
hash_value = hash(key)
return hash_value % self.shard_count
def range_sharding(self, key: int) -> int:
"""范围分片"""
# 假设key是自增ID
shard_size = 1000000 # 每个分片100万条
return key // shard_size
def modulo_sharding(self, key: int) -> int:
"""取模分片"""
return key % self.shard_count
def consistent_hash_sharding(self, key: str) -> int:
"""一致性哈希分片"""
import hashlib
hash_value = int(hashlib.md5(key.encode()).hexdigest(), 16)
return hash_value % self.shard_count
class ShardingManager:
"""分片管理器"""
def __init__(self, shard_configs: List[dict]):
self.shards = {}
for config in shard_configs:
shard_id = config['id']
self.shards[shard_id] = {
'connection': self._create_connection(config),
'config': config
}
self.strategy = ShardingStrategy(len(shard_configs))
def _create_connection(self, config: dict):
"""创建数据库连接"""
import pymysql
return pymysql.connect(
host=config['host'],
port=config['port'],
user=config['user'],
password=config['password'],
database=config['database']
)
def get_shard(self, shard_key: str, sharding_type: str = 'hash'):
"""获取分片连接"""
if sharding_type == 'hash':
shard_id = self.strategy.hash_sharding(shard_key)
elif sharding_type == 'consistent':
shard_id = self.strategy.consistent_hash_sharding(shard_key)
elif sharding_type == 'modulo':
shard_id = self.strategy.modulo_sharding(int(shard_key))
else:
raise ValueError(f"Unknown sharding type: {sharding_type}")
return self.shards[shard_id]['connection']
def execute_on_shard(self, shard_key: str, sql: str, params=None):
"""在指定分片执行SQL"""
conn = self.get_shard(shard_key)
with conn.cursor() as cursor:
cursor.execute(sql, params or ())
return cursor.fetchall()
def query_all_shards(self, sql: str, params=None):
"""查询所有分片"""
results = []
for shard_id, shard in self.shards.items():
conn = shard['connection']
with conn.cursor() as cursor:
cursor.execute(sql, params or ())
shard_results = cursor.fetchall()
# 添加分片标识
for row in shard_results:
if isinstance(row, dict):
row['_shard_id'] = shard_id
results.extend(shard_results)
return results
def broadcast_write(self, sql: str, params=None):
"""广播写入所有分片"""
results = []
for shard_id, shard in self.shards.items():
conn = shard['connection']
with conn.cursor() as cursor:
cursor.execute(sql, params or ())
conn.commit()
results.append({
'shard_id': shard_id,
'affected_rows': cursor.rowcount
})
return results
# ========== 分表路由 ==========
class TableRouter:
"""表路由"""
def __init__(self, sharding_manager: ShardingManager):
self.manager = sharding_manager
self.table_rules = {}
def add_rule(self, table_name: str, sharding_key: str, sharding_type: str):
"""添加分表规则"""
self.table_rules[table_name] = {
'sharding_key': sharding_key,
'sharding_type': sharding_type
}
def get_table_name(self, original_table: str, shard_key: str) -> str:
"""获取实际表名"""
rule = self.table_rules.get(original_table)
if not rule:
return original_table
# 计算分片ID
if rule['sharding_type'] == 'hash':
shard_id = hash(shard_key) % self.manager.strategy.shard_count
elif rule['sharding_type'] == 'modulo':
shard_id = int(shard_key) % self.manager.strategy.shard_count
else:
shard_id = 0
return f"{original_table}_{shard_id:04d}"
def execute_query(self, table_name: str, query_template: str, **kwargs):
"""执行分表查询"""
# 获取分片键值
rule = self.table_rules.get(table_name)
if not rule:
# 不分表，直接执行
return self.manager.execute_on_shard('0', query_template, kwargs)
shard_key_value = kwargs.get(rule['sharding_key'])
if not shard_key_value:
raise ValueError(f"Missing sharding key: {rule['sharding_key']}")
# 获取实际表名
actual_table = self.get_table_name(table_name, str(shard_key_value))
# 替换表名
actual_query = query_template.replace(f'FROM {table_name}', f'FROM {actual_table}')
return self.manager.execute_on_shard(str(shard_key_value), actual_query, kwargs)
# 使用示例
shard_configs = [
{
'id': 0,
'host': 'db0.example.com',
'port': 3306,
'user': 'root',
'password': 'password',
'database': 'app_db_0'
},
{
'id': 1,
'host': 'db1.example.com',
'port': 3306,
'user': 'root',
'password': 'password',
'database': 'app_db_1'
},
{
'id': 2,
'host': 'db2.example.com',
'port': 3306,
'user': 'root',
'password': 'password',
'database': 'app_db_2'
},
{
'id': 3,
'host': 'db3.example.com',
'port': 3306,
'user': 'root',
'password': 'password',
'database': 'app_db_3'
}
]
sharding_manager = ShardingManager(shard_configs)
table_router = TableRouter(sharding_manager)
# 配置orders表按user_id分表
table_router.add_rule('orders', 'user_id', 'modulo')
# 插入订单（自动路由到正确的分片）
table_router.execute_query(
'orders',
"INSERT INTO orders (user_id, order_no, total_amount) VALUES (:user_id, :order_no, :total_amount)",
user_id=12345,
order_no='ORD2024010112345',
total_amount=9999.99
)
# 查询订单（自动路由到正确的分片）
results = table_router.execute_query(
'orders',
"SELECT * FROM orders WHERE user_id = :user_id",
user_id=12345
)
|