一、NewSQL简介NewSQL是新一代分布式关系型数据库结合了SQL的便利性和NoSQL的可扩展性解决的问题传统关系型数据库无法水平扩展NoSQL不支持完整的SQL和事务需要强一致性但又要高可扩展性代表产品TiDBPingCAPCockroachDBCockroachLabsOceanBase蚂蚁金服YugabyteDB二、TiDB实战1. 架构设计┌─────────────────────────────────────────────────────────┐ │ TiDB Server │ │ SQL层无状态 │ └────────────────────────┬────────────────────────────────┘ │ ┌──────────────┼──────────────┐ ▼ ▼ ▼ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ TiKV 1 │ │ TiKV 2 │ │ TiKV 3 │ │ (Region)│ │ (Region)│ │ (Region)│ └──────────┘ └──────────┘ └──────────┘ │ │ │ └──────────────┼──────────────┘ ▼ ┌───────────────────────────────────────────┐ │ PD Server │ │ 调度器管理Region分布 │ └───────────────────────────────────────────┘核心组件TiDB ServerSQL解析和执行无状态TiKV分布式KV存储引擎RocksDBPD集群调度器2. Docker部署version:3services:pd:image:pingcap/pd:latestcontainer_name:pdports:-2379:2379volumes:-./pd-data:/pd-datacommand:--namepd--client-urlshttp://0.0.0.0:2379--advertise-client-urlshttp://pd:2379--initial-clusterpdhttp://pd:2380tikv1:image:pingcap/tikv:latestcontainer_name:tikv1ports:-20160:20160volumes:-./tikv1-data:/tikv-datadepends_on:-pdcommand:--addr0.0.0.0:20160--advertise-addrtikv1:20160--pdpd:2379--data-dir/tikv-datatikv2:image:pingcap/tikv:latestcontainer_name:tikv2ports:-20161:20160volumes:-./tikv2-data:/tikv-datadepends_on:-pdcommand:--addr0.0.0.0:20160--advertise-addrtikv2:20160--pdpd:2379--data-dir/tikv-datatidb:image:pingcap/tidb:latestcontainer_name:tidbports:-4000:4000depends_on:-pd-tikv1-tikv2command:--storetikv--pathpd:23793. MySQL兼容TiDB完全兼容MySQL协议-- 创建表完全兼容MySQL语法CREATETABLEorders(idBIGINTAUTO_INCREMENTPRIMARYKEY,order_noVARCHAR(32)NOTNULL,user_idBIGINTNOTNULL,shop_idBIGINTNOTNULL,order_amountDECIMAL(12,2)NOTNULL,order_statusTINYINTDEFAULT1,create_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMP,INDEXidx_user_id(user_id),INDEXidx_shop_id(shop_id),INDEXidx_order_no(order_no))ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 分布式事务乐观锁BEGIN;INSERTINTOorders(order_no,user_id,shop_id,order_amount)VALUES(ORDER001,1001,2001,99.99);UPDATEinventorySETstockstock-1WHEREproduct_id3001;COMMIT;-- 悲观锁TiDB特有BEGINPESSIMISTIC;SELECT*FROMordersWHEREid1FORUPDATE;UPDATEordersSETorder_status2WHEREid1;COMMIT;4. Java客户端dependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion8.0.33/version/dependency// TiDB完全兼容MySQL JDBCConfigurationpublicclassTiDBConfig{BeanpublicDataSourcedataSource(){HikariConfigconfignewHikariConfig();config.setJdbcUrl(jdbc:mysql://localhost:4000/order_db);config.setUsername(root);config.setPassword();config.setMaximumPoolSize(50);config.setMinimumIdle(10);config.setConnectionTimeout(30000);returnnewHikariDataSource(config);}}ServicepublicclassOrderService{AutowiredprivateDataSourcedataSource;TransactionalpublicvoidcreateOrder(OrderRequestrequest){// TiDB自动处理分布式事务try(ConnectionconndataSource.getConnection();PreparedStatementpsconn.prepareStatement(INSERT INTO orders (order_no, user_id, shop_id, order_amount) VALUES (?, ?, ?, ?))){ps.setString(1,request.getOrderNo());ps.setLong(2,request.getUserId());ps.setLong(3,request.getShopId());ps.setBigDecimal(4,request.getAmount());ps.executeUpdate();}}}5. HTAP能力TiDB同时支持OLTP和OLAP-- 创建TiFlash副本分析型副本ALTERTABLEordersSETTIFLASH REPLICA2;-- 分析查询自动路由到TiFlashSELECTDATE(create_time)asorder_date,shop_id,COUNT(*)asorder_count,SUM(order_amount)astotal_amountFROMordersWHEREcreate_time2024-01-01GROUPBYDATE(create_time),shop_idORDERBYtotal_amountDESCLIMIT10;三、CockroachDB实战1. 架构设计┌─────────────────────────────────────────────────────────┐ │ Gateway Node │ │ 请求路由无状态 │ └────────────────────────┬────────────────────────────────┘ │ ┌────────────────────┼────────────────────┐ ▼ ▼ ▼ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ Node 1 │◄───────►│ Node 2 │◄───────►│ Node 3 │ │(Store1) │ │(Store2) │ │(Store3) │ └─────────┘ └─────────┘ └─────────┘ │ │ │ └────────────────────┼────────────────────┘ ▼ ┌─────────────────────┐ │ Range Replication │ │ (默认3副本强一致) │ └─────────────────────┘核心特性多副本强一致性Raft协议自动分片和负载均衡故障自动恢复地理分布支持2. Docker部署version:3services:cockroach1:image:cockroachdb/cockroach:v23.1container_name:cockroach1ports:-26257:26257# SQL-8080:8080# Admin UIcommand:start--insecure--joincockroach1,cockroach2,cockroach3volumes:-./cockroach1-data:/cockroach/cockroach-datacockroach2:image:cockroachdb/cockroach:v23.1container_name:cockroach2ports:-26258:26257command:start--insecure--joincockroach1,cockroach2,cockroach3volumes:-./cockroach2-data:/cockroach/cockroach-datacockroach3:image:cockroachdb/cockroach:v23.1container_name:cockroach3ports:-26259:26257command:start--insecure--joincockroach1,cockroach2,cockroach3volumes:-./cockroach3-data:/cockroach/cockroach-data3. SQL语法-- 创建数据库CREATEDATABASEIFNOTEXISTSorder_db;USEorder_db;-- 创建表CREATETABLEorders(id UUIDDEFAULTgen_random_uuid(),order_noVARCHAR(32)NOTNULL,user_id INT64NOTNULL,shop_id INT64NOTNULL,order_amountDECIMAL(12,2)NOTNULL,order_status INT8DEFAULT1,create_timeTIMESTAMPDEFAULTnow(),PRIMARYKEY(id),INDEXidx_user_id(user_id),INDEXidx_shop_id(shop_id),UNIQUEINDEXidx_order_no(order_no));-- 分布式事务BEGIN;INSERTINTOorders(order_no,user_id,shop_id,order_amount)VALUES(ORDER001,1001,2001,99.99);UPDATEinventorySETstockstock-1WHEREproduct_id3001;COMMIT;-- 变更数据捕获CDCCREATECHANGEFEEDFORTABLEordersINTOkafka://localhost:9092WITHupdated,key_in_value;4. Java客户端dependencygroupIdio.cockroachdbc/groupIdartifactIdcockroachdb-vendored/artifactIdversion23.1.0/version/dependencyConfigurationpublicclassCockroachDBConfig{BeanpublicDataSourcedataSource(){PGSimpleDataSourcedsnewPGSimpleDataSource();ds.setUrl(jdbc:postgresql://localhost:26257/order_db?sslmodedisable);ds.setUser(root);ds.setPassword();ds.setConnectionOptions(application_nameorder_service);returnds;}}ServicepublicclassOrderService{AutowiredprivateDataSourcedataSource;// 使用Savepoint实现重试publicvoidcreateOrderWithRetry(OrderRequestrequest,intmaxRetries){intretry0;while(retrymaxRetries){try(ConnectionconndataSource.getConnection()){conn.setAutoCommit(false);// 插入订单insertOrder(conn,request);// 扣减库存updateInventory(conn,request);conn.commit();return;}catch(SQLTransactionRetryExceptione){retry;try{Thread.sleep(100*retry);}catch(InterruptedExceptionie){Thread.currentThread().interrupt();}}catch(SQLExceptione){thrownewRuntimeException(e);}}thrownewRuntimeException(重试次数耗尽);}}5. 地理分布-- 创建地理分布的表CREATETABLEorders(id UUIDDEFAULTgen_random_uuid(),order_noVARCHAR(32)NOTNULL,user_id INT64NOTNULL,region STRINGNOTNULL,create_timeTIMESTAMPDEFAULTnow(),PRIMARYKEY(id,region))PARTITIONBYLIST(region)(PARTITIONus_eastVALUESIN(NY,MA,FL),PARTITIONus_westVALUESIN(CA,WA,OR),PARTITIONeuVALUESIN(UK,DE,FR),PARTITIONasiaVALUESIN(CN,JP,KR));-- 配置副本分布ALTERTABLEordersSETlocalityREGIONALBYTABLEASROW;四、TiDB vs CockroachDB对比维度TiDBCockroachDB创始公司PingCAPCockroachLabs底层存储RocksDB (TiKV)RocksDB一致性强一致强一致SQL兼容MySQLPostgreSQLHTAP支持TiFlash不支持地理分布一般优秀性能优秀较好生态丰富一般许可证Apache 2.0BSL 1.1社区活跃活跃五、选型建议选择TiDB的场景MySQL迁移项目需要HTAP能力同时支持TP和AP现有MySQL技术栈数据量级TB级选择CockroachDB的场景PostgreSQL迁移项目需要强地理分布需要全球化部署对许可证敏感六、迁移方案从MySQL迁移到TiDB# 使用DMData Migration工具# 1. 配置迁移任务cattask.yamlEOF name: mysql-to-tidb task-mode: all target-database: host: tidb-host port: 4000 user: root mysql-instances: - source-id: mysql-source block-allow-list: instance-1 routes: route-1: schema-pattern: order_db target-schema: order_db EOF# 2. 启动迁移dmctl --master-addr127.0.0.1:8261 start-task task.yaml# 3. 查看状态dmctl --master-addr127.0.0.1:8261 query-status七、总结NewSQL是分布式关系型数据库的未来TiDBMySQL兼容 HTAP能力强CockroachDBPostgreSQL兼容 地理分布强强一致Raft协议保证水平扩展自动分片和负载均衡选型建议MySQL技术栈 → TiDBPostgreSQL技术栈 → CockroachDB需要分析能力 → TiDB需要全球化部署 → CockroachDB个人观点仅供参考