数据库分库分表与分布式详解1. 分库分表概述当单机数据库无法满足业务增长需求时需要进行分库分表将数据分散到多个数据库或表中。2. 垂直拆分2.1 垂直分库按业务模块拆分-- 用户库 CREATE DATABASE user_db; CREATE TABLE user_db.users (...); -- 订单库 CREATE DATABASE order_db; CREATE TABLE order_db.orders (...);2.2 垂直分表按字段拆分-- 主表 CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT, total_amount DECIMAL(10,2), status VARCHAR(20) ); -- 扩展表 CREATE TABLE orders_ext ( id BIGINT PRIMARY KEY, order_id BIGINT, shipping_address TEXT, billing_info TEXT );3. 水平拆分3.1 按ID范围分表func getTableByID(id int64) string { tableID : id / 1000000 return fmt.Sprintf(orders_%d, tableID) }3.2 按Hash分表func getTableByHash(userID int64, tableCount int) string { hash : userID % int64(tableCount) return fmt.Sprintf(orders_%d, hash) }3.3 按时间分表func getTableByTime(t time.Time) string { return fmt.Sprintf(orders_%s, t.Format(2006_01)) }4. 分片中间件4.1 ShardingSphereschemaName: sharding_db dataSources: ds_0: url: jdbc:mysql://localhost:3306/ds_0 username: root password: ds_1: url: jdbc:mysql://localhost:3306/ds_1 username: root password: rules: - !sharding tables: t_order: actualDataNodes: ds_${0..1}.t_order_${0..3} tableStrategy: standard: shardingColumn: order_id shardingAlgorithmName: t_order_inline bindingTables: - t_order4.2 MyCAT配置schema nametest checkSQLschemafalse sqlMaxLimit100 table nameorders dataNodedn1,dn2 rulemod-long/ /schema dataNode namedn1 dataHostdh1 databasedb1/ dataNode namedn2 dataHostdh2 databasedb2/5. 分布式ID5.1 Snowflake算法type Snowflake struct { mu sync.Mutex timestamp int64 nodeID int64 sequence int64 } func NewSnowflake(nodeID int64) *Snowflake { return Snowflake{ nodeID: nodeID, } } func (s *Snowflake) Generate() int64 { s.mu.Lock() defer s.mu.Unlock() now : time.Now().UnixNano() / 1000000 if now s.timestamp { s.sequence (s.sequence 1) 4095 if s.sequence 0 { for now s.timestamp { now time.Now().UnixNano() / 1000000 } } } else { s.sequence 0 } s.timestamp now return (now-1609459200000)22 | s.nodeID12 | s.sequence }5.2 UUIDimport github.com/google/uuid // 生成UUID id : uuid.New().String()6. 分布式事务6.1 两阶段提交type TwoPhaseCommit struct { participants []Participant } func (tx *TwoPhaseCommit) Prepare() error { for _, p : range tx.participants { if err : p.Prepare(); err ! nil { tx.Rollback() return err } } return nil } func (tx *TwoPhaseCommit) Commit() error { for _, p : range tx.participants { if err : p.Commit(); err ! nil { // 处理失败 } } return nil }6.2 Saga模式type SagaStep struct { Name string Execute func() error Compensate func() error } func ExecuteSaga(steps []SagaStep) error { completed : []SagaStep{} for _, step : range steps { if err : step.Execute(); err ! nil { // 回滚已完成的步骤 for i : len(completed) - 1; i 0; i-- { completed[i].Compensate() } return err } completed append(completed, step) } return nil }7. 跨分片查询7.1 禁止跨分片操作// 错误示例无法在应用层完成 SELECT * FROM orders WHERE user_id IN (1, 2, 3) // 1和2可能在不同的分片7.2 广播表// 配置广播表所有分片都有完整副本 // 适合字典表、配置表 rules: - !sharding tables: t_config: broadcastTables: - t_config7.3 ES替代方案// 将数据同步到Elasticsearch type ESService struct { client *elastic.Client } func (s *ESService) Search(query map[string]interface{}) ([]map[string]interface{}, error) { searchResult, err : s.client.Search(). Index(orders). Query(query). Do(ctx) return searchResult.Hits.Hits, nil }8. 数据迁移8.1 双写策略func CreateOrder(order *Order) error { // 同时写入MySQL和ES if err : mysqlDB.Create(order).Error; err ! nil { return err } if err : esClient.Index().Index(orders).BodyJson(order).Do(ctx); err ! nil { log.Printf(ES write failed: %v, err) } return nil }8.2 数据同步工具Canal监听MySQL binlog同步数据Debezium支持多种数据库Flink CDC实时数据同步9. 总结分库分表是解决数据库扩展性的重要手段需要根据业务特点选择合适的拆分策略。同时需要注意分布式ID、分布式事务、跨分片查询等问题合理使用中间件和设计模式。
