MySQL分库分表（无中间件）
MySQL分库分表（无中间件）
一、 分库分表方案设计
1. 垂直拆分 (按业务模块)
1-- 原始单体数据库
2CREATE DATABASE ecommerce;
3USE ecommerce;
4
5-- 垂直拆分后的数据库
6-- 用户库
7CREATE DATABASE user_center;
8USE user_center;
9
10CREATE TABLE users (
11 user_id BIGINT PRIMARY KEY,
12 username VARCHAR(50) UNIQUE,
13 email VARCHAR(100) UNIQUE,
14 password VARCHAR(100),
15 mobile VARCHAR(20),
16 status TINYINT DEFAULT 1,
17 created_at DATETIME,
18 updated_at DATETIME
19);
20
21CREATE TABLE user_profiles (
22 user_id BIGINT PRIMARY KEY,
23 real_name VARCHAR(50),
24 avatar VARCHAR(200),
25 gender TINYINT,
26 birthday DATE,
27 bio TEXT
28);
29
30CREATE TABLE user_address (
31 address_id BIGINT PRIMARY KEY,
32 user_id BIGINT,
33 province VARCHAR(50),
34 city VARCHAR(50),
35 district VARCHAR(50),
36 detail VARCHAR(200),
37 is_default TINYINT DEFAULT 0,
38 INDEX idx_user_id (user_id)
39);
40
41-- 订单库
42CREATE DATABASE order_center;
43USE order_center;
44
45CREATE TABLE orders (
46 order_id BIGINT PRIMARY KEY,
47 user_id BIGINT,
48 total_amount DECIMAL(10,2),
49 status TINYINT,
50 payment_status TINYINT,
51 created_at DATETIME,
52 paid_at DATETIME
53);
54
55CREATE TABLE order_items (
56 item_id BIGINT PRIMARY KEY,
57 order_id BIGINT,
58 product_id BIGINT,
59 product_name VARCHAR(100),
60 price DECIMAL(10,2),
61 quantity INT,
62 INDEX idx_order_id (order_id)
63);
64
65-- 商品库
66CREATE DATABASE product_center;
67USE product_center;
68
69CREATE TABLE products (
70 product_id BIGINT PRIMARY KEY,
71 name VARCHAR(100),
72 category_id INT,
73 price DECIMAL(10,2),
74 stock INT,
75 status TINYINT,
76 created_at DATETIME
77);
78
79CREATE TABLE categories (
80 category_id INT PRIMARY KEY,
81 name VARCHAR(50),
82 parent_id INT
83);
2. 水平拆分 （按数据量）
用户表水平分表（10个分表）
1-- 在 user_center 数据库中创建分表
2USE user_center;
3
4-- 下面语句中的//为分隔符
5DELIMITER //
6CREATE PROCEDURE CreateUserShards()
7BEGIN
8 DECLARE i INT DEFAULT 0;
9 WHILE i < 10 DO
10 SET @sql = CONCAT('
11 CREATE TABLE users_', i, ' (
12 user_id BIGINT PRIMARY KEY,
13 username VARCHAR(50) NOT NULL,
14 email VARCHAR(100),
15 password VARCHAR(100),
16 mobile VARCHAR(20),
17 status TINYINT DEFAULT 1,
18 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
19 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
20 UNIQUE KEY uk_username_', i, ' (username),
21 UNIQUE KEY uk_email_', i, ' (email),
22 INDEX idx_mobile_', i, ' (mobile),
23 INDEX idx_created_', i, ' (created_at)
24 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
25 ');
26 PREPARE stmt FROM @sql;
27 EXECUTE stmt;
28 DEALLOCATE PREPARE stmt;
29 SET i = i + 1;
30 END WHILE;
31END//
32DELIMITER ;
33
34CALL CreateUserShards();
订单表水平分表（按用户ID分表）
1USE order_center;
2
3DELIMITER //
4CREATE PROCEDURE CreateOrderShards()
5BEGIN
6 DECLARE i INT DEFAULT 0;
7 WHILE i < 10 DO
8 SET @sql = CONCAT('
9 CREATE TABLE orders_', i, ' (
10 order_id BIGINT PRIMARY KEY,
11 user_id BIGINT NOT NULL,
12 total_amount DECIMAL(10,2),
13 status TINYINT DEFAULT 1,
14 payment_status TINYINT DEFAULT 0,
15 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
16 paid_at DATETIME,
17 INDEX idx_user_id_', i, ' (user_id),
18 INDEX idx_created_', i, ' (created_at),
19 INDEX idx_status_', i, ' (status)
20 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
21 ');
22 PREPARE stmt FROM @sql;
23 EXECUTE stmt;
24 DEALLOCATE PREPARE stmt;
25
26 -- 创建对应的订单项分表
27 SET @sql = CONCAT('
28 CREATE TABLE order_items_', i, ' (
29 item_id BIGINT PRIMARY KEY,
30 order_id BIGINT NOT NULL,
31 product_id BIGINT,
32 product_name VARCHAR(100),
33 price DECIMAL(10,2),
34 quantity INT,
35 INDEX idx_order_id_', i, ' (order_id),
36 INDEX idx_product_id_', i, ' (product_id)
37 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
38 ');
39 PREPARE stmt FROM @sql;
40 EXECUTE stmt;
41 DEALLOCATE PREPARE stmt;
42
43 SET i = i + 1;
44 END WHILE;
45END//
46DELIMITER ;
47
48CALL CreateOrderShards();
二、程序中处理（PHP）
1. 基础配置类
1<?php
2// Config.php
3class Config {
4 private static $instance;
5 private $config;
6
7 private function __construct() {
8 $this->config = [
9 'databases' => [
10 'user_center' => [
11 'host' => 'localhost',
12 'port' => 3306,
13 'username' => 'root',
14 'password' => 'password',
15 'dbname' => 'user_center'
16 ],
17 'order_center' => [
18 'host' => 'localhost',
19 'port' => 3306,
20 'username' => 'root',
21 'password' => 'password',
22 'dbname' => 'order_center'
23 ],
24 'product_center' => [
25 'host' => 'localhost',
26 'port' => 3306,
27 'username' => 'root',
28 'password' => 'password',
29 'dbname' => 'product_center'
30 ]
31 ],
32 'shard' => [
33 'user' => 10, // 用户表10个分片
34 'order' => 10 // 订单表10个分片
35 ]
36 ];
37 }
38
39 public static function getInstance() {
40 if (!self::$instance) {
41 self::$instance = new self();
42 }
43 return self::$instance;
44 }
45
46 public function get($key) {
47 $keys = explode('.', $key);
48 $value = $this->config;
49
50 foreach ($keys as $k) {
51 if (!isset($value[$k])) {
52 return null;
53 }
54 $value = $value[$k];
55 }
56
57 return $value;
58 }
59}
2. 分片路由类
1<?php
2// ShardRouter.php
3class ShardRouter {
4 private $shardConfig;
5
6 public function __construct() {
7 $config = Config::getInstance();
8 $this->shardConfig = $config->get('shard');
9 }
10
11 /**
12 * 根据用户ID计算分片
13 */
14 public function getUserShard($userId) {
15 return $userId % $this->shardConfig['user'];
16 }
17
18 /**
19 * 根据订单ID计算分片（订单按用户ID分片）
20 */
21 public function getOrderShard($orderId) {
22 // 假设订单ID包含用户ID信息
23 $userId = $this->extractUserIdFromOrderId($orderId);
24 return $userId % $this->shardConfig['order'];
25 }
26
27 /**
28 * 根据用户名计算分片（用于登录查询）
29 */
30 public function getShardByString($str, $type = 'user') {
31 $shardCount = $this->shardConfig[$type];
32 return crc32($str) % $shardCount;
33 }
34
35 /**
36 * 生成分布式ID（雪花算法）
37 */
38 public function generateId($shardId = 0, $timestamp = null) {
39 $timestamp = $timestamp ?? (int)(microtime(true) * 1000);
40 $workerId = $shardId % 1024;
41 $sequence = mt_rand(0, 4095);
42
43 return (($timestamp - 1577836800000) << 22)
44 | ($workerId << 12)
45 | $sequence;
46 }
47
48 /**
49 * 从订单ID提取用户ID（根据业务规则）
50 */
51 private function extractUserIdFromOrderId($orderId) {
52 // 实际业务中需要根据ID生成规则来解析
53 // 这里简单返回订单ID的低位部分作为用户ID
54 return $orderId & 0x3FFFFFFF;
55 }
56
57 /**
58 * 获取表名
59 */
60 public function getTableName($baseTable, $shardId) {
61 return $baseTable . '_' . $shardId;
62 }
63}
3. 数据库连接管理
1<?php
2// DatabaseManager.php
3class DatabaseManager {
4 private static $instance;
5 private $connections = [];
6 private $config;
7
8 private function __construct() {
9 $this->config = Config::getInstance();
10 }
11
12 public static function getInstance() {
13 if (!self::$instance) {
14 self::$instance = new self();
15 }
16 return self::$instance;
17 }
18
19 /**
20 * 获取数据库连接
21 */
22 public function getConnection($database, $shardId = null) {
23 $key = $database . ($shardId !== null ? '_' . $shardId : '');
24
25 if (!isset($this->connections[$key])) {
26 $dbConfig = $this->config->get("databases.{$database}");
27
28 if (!$dbConfig) {
29 throw new Exception("Database config not found: {$database}");
30 }
31
32 try {
33 $dsn = "mysql:host={$dbConfig['host']};port={$dbConfig['port']};dbname={$dbConfig['dbname']};charset=utf8mb4";
34
35 $this->connections[$key] = new PDO(
36 $dsn,
37 $dbConfig['username'],
38 $dbConfig['password'],
39 [
40 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
41 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
42 PDO::ATTR_EMULATE_PREPARES => false,
43 PDO::ATTR_PERSISTENT => false
44 ]
45 );
46 } catch (PDOException $e) {
47 throw new Exception("Database connection failed: " . $e->getMessage());
48 }
49 }
50
51 return $this->connections[$key];
52 }
53
54 /**
55 * 开始事务（单分片事务）
56 */
57 public function beginTransaction($database, $shardId = null) {
58 $connection = $this->getConnection($database, $shardId);
59 return $connection->beginTransaction();
60 }
61
62 /**
63 * 提交事务
64 */
65 public function commit($database, $shardId = null) {
66 $connection = $this->getConnection($database, $shardId);
67 return $connection->commit();
68 }
69
70 /**
71 * 回滚事务
72 */
73 public function rollback($database, $shardId = null) {
74 $connection = $this->getConnection($database, $shardId);
75 return $connection->rollback();
76 }
77}
4. 用户服务类示例（完整CRUD）
1<?php
2// UserService.php
3class UserService {
4 private $db;
5 private $router;
6
7 public function __construct() {
8 $this->db = DatabaseManager::getInstance();
9 $this->router = new ShardRouter();
10 }
11
12 /**
13 * 创建用户（写入操作）
14 */
15 public function createUser($userData) {
16 // 生成用户ID
17 $userId = $this->router->generateId();
18 $shardId = $this->router->getUserShard($userId);
19
20 $tableName = $this->router->getTableName('users', $shardId);
21 $connection = $this->db->getConnection('user_center', $shardId);
22
23 try {
24 // 开始事务
25 $this->db->beginTransaction('user_center', $shardId);
26
27 // 插入用户基础信息
28 $sql = "INSERT INTO {$tableName}
29 (user_id, username, email, password, mobile, created_at)
30 VALUES (?, ?, ?, ?, ?, NOW())";
31
32 $stmt = $connection->prepare($sql);
33 $stmt->execute([
34 $userId,
35 $userData['username'],
36 $userData['email'],
37 password_hash($userData['password'], PASSWORD_DEFAULT),
38 $userData['mobile'] ?? null
39 ]);
40
41 // 插入用户档案（垂直分库，但在同一个事务中）
42 $this->createUserProfile($userId, $userData);
43
44 // 提交事务
45 $this->db->commit('user_center', $shardId);
46
47 return $userId;
48
49 } catch (Exception $e) {
50 $this->db->rollback('user_center', $shardId);
51 throw new Exception("创建用户失败: " . $e->getMessage());
52 }
53 }
54
55 /**
56 * 更新用户信息
57 */
58 public function updateUser($userId, $updateData) {
59 $shardId = $this->router->getUserShard($userId);
60 $tableName = $this->router->getTableName('users', $shardId);
61 $connection = $this->db->getConnection('user_center', $shardId);
62
63 $allowedFields = ['email', 'mobile', 'password'];
64 $setParts = [];
65 $params = [];
66
67 foreach ($updateData as $field => $value) {
68 if (in_array($field, $allowedFields)) {
69 if ($field === 'password') {
70 $value = password_hash($value, PASSWORD_DEFAULT);
71 }
72 $setParts[] = "{$field} = ?";
73 $params[] = $value;
74 }
75 }
76
77 if (empty($setParts)) {
78 throw new Exception("没有有效的更新字段");
79 }
80
81 $params[] = $userId;
82
83 $sql = "UPDATE {$tableName} SET " . implode(', ', $setParts) . " WHERE user_id = ?";
84 $stmt = $connection->prepare($sql);
85
86 return $stmt->execute($params);
87 }
88
89 /**
90 * 根据用户ID查询（精确查询）
91 */
92 public function getUserById($userId) {
93 $shardId = $this->router->getUserShard($userId);
94 $tableName = $this->router->getTableName('users', $shardId);
95 $connection = $this->db->getConnection('user_center', $shardId);
96
97 $sql = "SELECT * FROM {$tableName} WHERE user_id = ?";
98 $stmt = $connection->prepare($sql);
99 $stmt->execute([$userId]);
100
101 $user = $stmt->fetch();
102 if ($user) {
103 $user['profile'] = $this->getUserProfile($userId);
104 $user['addresses'] = $this->getUserAddresses($userId);
105 }
106
107 return $user;
108 }
109
110 /**
111 * 根据用户名查询（需要计算分片）
112 */
113 public function getUserByUsername($username) {
114 $shardId = $this->router->getShardByString($username, 'user');
115 $tableName = $this->router->getTableName('users', $shardId);
116 $connection = $this->db->getConnection('user_center', $shardId);
117
118 $sql = "SELECT * FROM {$tableName} WHERE username = ?";
119 $stmt = $connection->prepare($sql);
120 $stmt->execute([$username]);
121
122 return $stmt->fetch();
123 }
124
125 /**
126 * 批量查询用户信息
127 */
128 public function getUsersByIds($userIds) {
129 $usersByShard = [];
130
131 // 按分片分组用户ID
132 foreach ($userIds as $userId) {
133 $shardId = $this->router->getUserShard($userId);
134 $usersByShard[$shardId][] = $userId;
135 }
136
137 $results = [];
138 foreach ($usersByShard as $shardId => $shardUserIds) {
139 $tableName = $this->router->getTableName('users', $shardId);
140 $connection = $this->db->getConnection('user_center', $shardId);
141
142 $placeholders = str_repeat('?,', count($shardUserIds) - 1) . '?';
143 $sql = "SELECT * FROM {$tableName} WHERE user_id IN ({$placeholders})";
144
145 $stmt = $connection->prepare($sql);
146 $stmt->execute($shardUserIds);
147
148 $results = array_merge($results, $stmt->fetchAll());
149 }
150
151 return $results;
152 }
153
154 /**
155 * 复杂查询：按条件搜索用户（需要遍历分片）
156 */
157 public function searchUsers($conditions, $page = 1, $pageSize = 20) {
158 $allUsers = [];
159 $shardCount = Config::getInstance()->get('shard.user');
160
161 for ($shardId = 0; $shardId < $shardCount; $shardId++) {
162 $tableName = $this->router->getTableName('users', $shardId);
163 $connection = $this->db->getConnection('user_center', $shardId);
164
165 $where = [];
166 $params = [];
167
168 if (!empty($conditions['username'])) {
169 $where[] = "username LIKE ?";
170 $params[] = $conditions['username'] . '%';
171 }
172
173 if (!empty($conditions['email'])) {
174 $where[] = "email LIKE ?";
175 $params[] = $conditions['email'] . '%';
176 }
177
178 if (!empty($conditions['mobile'])) {
179 $where[] = "mobile LIKE ?";
180 $params[] = $conditions['mobile'] . '%';
181 }
182
183 $whereClause = $where ? "WHERE " . implode(' AND ', $where) : "";
184 $offset = ($page - 1) * $pageSize;
185
186 $sql = "SELECT * FROM {$tableName} {$whereClause} LIMIT ? OFFSET ?";
187 $params[] = $pageSize;
188 $params[] = $offset;
189
190 $stmt = $connection->prepare($sql);
191 $stmt->execute($params);
192
193 $shardUsers = $stmt->fetchAll();
194 $allUsers = array_merge($allUsers, $shardUsers);
195 }
196
197 // 在PHP中进行排序和分页
198 usort($allUsers, function($a, $b) {
199 return strcmp($b['created_at'], $a['created_at']);
200 });
201
202 return array_slice($allUsers, 0, $pageSize);
203 }
204
205 /**
206 * 删除用户
207 */
208 public function deleteUser($userId) {
209 $shardId = $this->router->getUserShard($userId);
210 $tableName = $this->router->getTableName('users', $shardId);
211 $connection = $this->db->getConnection('user_center', $shardId);
212
213 try {
214 $this->db->beginTransaction('user_center', $shardId);
215
216 // 删除用户基础信息
217 $sql = "DELETE FROM {$tableName} WHERE user_id = ?";
218 $stmt = $connection->prepare($sql);
219 $stmt->execute([$userId]);
220
221 // 删除关联数据
222 $this->deleteUserProfile($userId);
223 $this->deleteUserAddresses($userId);
224
225 $this->db->commit('user_center', $shardId);
226 return true;
227
228 } catch (Exception $e) {
229 $this->db->rollback('user_center', $shardId);
230 throw new Exception("删除用户失败: " . $e->getMessage());
231 }
232 }
233
234 // 用户档案相关方法
235 private function createUserProfile($userId, $userData) {
236 $connection = $this->db->getConnection('user_center');
237
238 $sql = "INSERT INTO user_profiles
239 (user_id, real_name, avatar, gender, birthday, bio)
240 VALUES (?, ?, ?, ?, ?, ?)";
241
242 $stmt = $connection->prepare($sql);
243 return $stmt->execute([
244 $userId,
245 $userData['real_name'] ?? '',
246 $userData['avatar'] ?? '',
247 $userData['gender'] ?? 0,
248 $userData['birthday'] ?? null,
249 $userData['bio'] ?? ''
250 ]);
251 }
252
253 private function getUserProfile($userId) {
254 $connection = $this->db->getConnection('user_center');
255
256 $sql = "SELECT * FROM user_profiles WHERE user_id = ?";
257 $stmt = $connection->prepare($sql);
258 $stmt->execute([$userId]);
259
260 return $stmt->fetch();
261 }
262
263 private function getUserAddresses($userId) {
264 $connection = $this->db->getConnection('user_center');
265
266 $sql = "SELECT * FROM user_address WHERE user_id = ? ORDER BY is_default DESC";
267 $stmt = $connection->prepare($sql);
268 $stmt->execute([$userId]);
269
270 return $stmt->fetchAll();
271 }
272
273 private function deleteUserProfile($userId) {
274 $connection = $this->db->getConnection('user_center');
275
276 $sql = "DELETE FROM user_profiles WHERE user_id = ?";
277 $stmt = $connection->prepare($sql);
278 return $stmt->execute([$userId]);
279 }
280
281 private function deleteUserAddresses($userId) {
282 $connection = $this->db->getConnection('user_center');
283
284 $sql = "DELETE FROM user_address WHERE user_id = ?";
285 $stmt = $connection->prepare($sql);
286 return $stmt->execute([$userId]);
287 }
288}
5. 订单服务类示例
1<?php
2// OrderService.php
3class OrderService {
4 private $db;
5 private $router;
6
7 public function __construct() {
8 $this->db = DatabaseManager::getInstance();
9 $this->router = new ShardRouter();
10 }
11
12 /**
13 * 创建订单
14 */
15 public function createOrder($orderData) {
16 $userId = $orderData['user_id'];
17 $shardId = $this->router->getUserShard($userId);
18
19 $orderId = $this->router->generateId($shardId);
20 $orderTable = $this->router->getTableName('orders', $shardId);
21 $itemTable = $this->router->getTableName('order_items', $shardId);
22 $connection = $this->db->getConnection('order_center', $shardId);
23
24 try {
25 $this->db->beginTransaction('order_center', $shardId);
26
27 // 插入订单主表
28 $sql = "INSERT INTO {$orderTable}
29 (order_id, user_id, total_amount, status, payment_status, created_at)
30 VALUES (?, ?, ?, 1, 0, NOW())";
31
32 $stmt = $connection->prepare($sql);
33 $stmt->execute([
34 $orderId,
35 $userId,
36 $orderData['total_amount']
37 ]);
38
39 // 插入订单项
40 foreach ($orderData['items'] as $item) {
41 $itemId = $this->router->generateId($shardId);
42
43 $sql = "INSERT INTO {$itemTable}
44 (item_id, order_id, product_id, product_name, price, quantity)
45 VALUES (?, ?, ?, ?, ?, ?)";
46
47 $stmt = $connection->prepare($sql);
48 $stmt->execute([
49 $itemId,
50 $orderId,
51 $item['product_id'],
52 $item['product_name'],
53 $item['price'],
54 $item['quantity']
55 ]);
56 }
57
58 $this->db->commit('order_center', $shardId);
59 return $orderId;
60
61 } catch (Exception $e) {
62 $this->db->rollback('order_center', $shardId);
63 throw new Exception("创建订单失败: " . $e->getMessage());
64 }
65 }
66
67 /**
68 * 更新订单状态
69 */
70 public function updateOrderStatus($orderId, $status) {
71 $shardId = $this->router->getOrderShard($orderId);
72 $tableName = $this->router->getTableName('orders', $shardId);
73 $connection = $this->db->getConnection('order_center', $shardId);
74
75 $sql = "UPDATE {$tableName} SET status = ?, updated_at = NOW() WHERE order_id = ?";
76 $stmt = $connection->prepare($sql);
77
78 return $stmt->execute([$status, $orderId]);
79 }
80
81 /**
82 * 根据订单ID查询
83 */
84 public function getOrderById($orderId) {
85 $shardId = $this->router->getOrderShard($orderId);
86 $orderTable = $this->router->getTableName('orders', $shardId);
87 $itemTable = $this->router->getTableName('order_items', $shardId);
88 $connection = $this->db->getConnection('order_center', $shardId);
89
90 // 查询订单基本信息
91 $sql = "SELECT * FROM {$orderTable} WHERE order_id = ?";
92 $stmt = $connection->prepare($sql);
93 $stmt->execute([$orderId]);
94
95 $order = $stmt->fetch();
96 if ($order) {
97 // 查询订单项
98 $sql = "SELECT * FROM {$itemTable} WHERE order_id = ?";
99 $stmt = $connection->prepare($sql);
100 $stmt->execute([$orderId]);
101 $order['items'] = $stmt->fetchAll();
102 }
103
104 return $order;
105 }
106
107 /**
108 * 查询用户订单列表
109 */
110 public function getUserOrders($userId, $page = 1, $pageSize = 20) {
111 $shardId = $this->router->getUserShard($userId);
112 $tableName = $this->router->getTableName('orders', $shardId);
113 $connection = $this->db->getConnection('order_center', $shardId);
114
115 $offset = ($page - 1) * $pageSize;
116
117 $sql = "SELECT * FROM {$tableName}
118 WHERE user_id = ?
119 ORDER BY created_at DESC
120 LIMIT ? OFFSET ?";
121
122 $stmt = $connection->prepare($sql);
123 $stmt->execute([$userId, $pageSize, $offset]);
124
125 return $stmt->fetchAll();
126 }
127}
6. 使用示例
6.1 用户注册和订单创建
1<?php
2// register_and_order.php
3require_once 'Config.php';
4require_once 'ShardRouter.php';
5require_once 'DatabaseManager.php';
6require_once 'UserService.php';
7require_once 'OrderService.php';
8
9try {
10 // 初始化服务
11 $userService = new UserService();
12 $orderService = new OrderService();
13
14 // 1. 创建用户
15 $userData = [
16 'username' => 'zhangsan',
17 'email' => 'zhangsan@example.com',
18 'password' => '123456',
19 'mobile' => '13800138000',
20 'real_name' => '张三',
21 'avatar' => 'avatar.jpg'
22 ];
23
24 $userId = $userService->createUser($userData);
25 echo "用户创建成功，ID: {$userId}\n";
26
27 // 2. 为用户创建订单
28 $orderData = [
29 'user_id' => $userId,
30 'total_amount' => 299.00,
31 'items' => [
32 [
33 'product_id' => 1001,
34 'product_name' => 'iPhone 14',
35 'price' => 299.00,
36 'quantity' => 1
37 ]
38 ]
39 ];
40
41 $orderId = $orderService->createOrder($orderData);
42 echo "订单创建成功，ID: {$orderId}\n";
43
44 // 3. 查询用户信息
45 $user = $userService->getUserById($userId);
46 echo "用户信息: " . json_encode($user, JSON_UNESCAPED_UNICODE) . "\n";
47
48 // 4. 查询订单信息
49 $order = $orderService->getOrderById($orderId);
50 echo "订单信息: " . json_encode($order, JSON_UNESCAPED_UNICODE) . "\n";
51
52 // 5. 查询用户订单列表
53 $orders = $orderService->getUserOrders($userId);
54 echo "用户订单数量: " . count($orders) . "\n";
55
56} catch (Exception $e) {
57 echo "操作失败: " . $e->getMessage() . "\n";
58}
6.2 批量操作示例
1<?php
2// batch_operations.php
3require_once 'UserService.php';
4
5$userService = new UserService();
6
7// 批量查询用户信息
8$userIds = [123456, 234567, 345678, 456789];
9$users = $userService->getUsersByIds($userIds);
10echo "批量查询到 " . count($users) . " 个用户\n";
11
12// 复杂搜索
13$conditions = [
14 'username' => 'zhang',
15 'mobile' => '138'
16];
17$users = $userService->searchUsers($conditions, 1, 10);
18echo "搜索到 " . count($users) . " 个用户\n";
19
20// 更新用户信息
21$updateResult = $userService->updateUser(123456, [
22 'email' => 'new_email@example.com',
23 'mobile' => '13900139000'
24]);
25echo "更新结果: " . ($updateResult ? '成功' : '失败') . "\n";
三、 总结
写入操作：
先计算分片：根据分片键（如用户ID）计算目标分片
获取对应连接：连接到正确的数据库和分表
执行写入：在目标分片上执行INSERT/UPDATE
事务管理：同一分片内的事务可以保证一致性
查询操作：
精确查询：通过分片键直接定位到具体分片
批量查询：按分片分组，分别查询后合并结果
复杂查询：遍历所有分片，在PHP层进行结果聚合
跨库查询：避免跨分片JOIN，通过多次查询在应用层组装
更新操作：
定位分片：通过分片键找到数据所在分片
单分片更新：在单个分片内完成更新
批量更新：按分片分组执行
删除操作：
定位分片：找到数据所在分片
事务处理：删除主表和关联表数据
级联删除：在应用层处理关联数据删除
说明
这种方案虽然增加了应用层的复杂性，但避免了中间件的依赖，提供了更大的灵活性。
在实际应用中，还需要考虑连接池、缓存、监控等配套措施。
发表评论