2024分布式数据库TDSQL金融应用指南

2024录概 逻辑架 部署架 资源评估方 配置建 模型设 SQL编写规 数据安

（一）1.TDSQL特点TSQL（TDSL）是腾讯打造的一款企业级数据库产品，其定位是基于互联网分布式架构的金融级数据库。TDSQLMySQL、PostgreSQLOracleSQL2016JSON、空间TDQLDDL/DML/DCL分布式联合查询，数据强一致能力。TDQLDStoreHTPTDSQL群扩展能力。同时，TDSQLDBA、自动化运营、监控告警等配套设施，为用户提供完整的分布式数据库解决方案。（一）1.TDSQL特点:ATM所以，银行业对分布式数据库的需求可大致概括如下：首先支持业务系统安全稳定的运行是重中之重；其次是可承载多类型的海量数据，同时还可处理高并发的事务，可支持弹性扩容；再次是开发和运维成本要相对可控等。实时性，证券交易是实时的，系统需要能够快速响应并处理交易请求。三是安全性，证券业务涉及到大量的资金交易，因此系统的安全性是非常重要的。四是稳定性，证券业务系统需要保证交易时段不间断运行，不能出现任何故障。对于分布式数据库的需求，证券业主要有以下几点：一是高可用，分布式数据库需要保证在任何情况下都能提供服务，不能因为单点故小额且频次高，数据库负载呈现爆发式增长。三是活跃数据存储周期长，如寿险、健康险通常是按若干年为周期计算，对于数据一致性、分布式数据库TDSQL金融应用指 分布式数据库TDSQL分布式数据库TDSQL金融应用指南（二）TDSQL系统架构设计的核心思路是：标准化、模块化。例如TDSQL的模块设计是充分解耦的，彼此之间通过接口衔接，用户可以自NN中心的部署，如金融行业常见的同城双中心、同城三中心、两地三中心、两地四中心等高可用容灾部署架构，数据副本亦可做到一主N备（N≥0）；甚至在设计读写分离机制时，只读账户可根据主备延迟状态，选择是从备TDSQL的整个研发过程，给予用户足够的灵活性和选择权。TDSQLDBDBZKMeta1TDSQLSQLSQLSQL旁路模块：AgentDB决策集群：Zookeeper调度模块：anager/Scheduler主备切换、扩缩容、资源管理或者执行业务流程等。OSSHTTPPOSTPOSTbodyTDSQLwebTDSQL。赤兔管理台：TDSQLTDSQLTDSQL赤兔通过调用OSS接口进行任务下发到ZK组件进行任务节点创建，由Scheduler组件进行监听，当监听到相关任务节点后，调用Manager组件进行具体任务执行，并更新ZK任务节点，赤兔运维平台通过OSS查看任务进度状态。分布式数据库TDSQL分布式数据库TDSQL金融应用指南水平扩容机制：分布式实例对外呈现为一个完整的逻辑实例，后端数据实际分布在若干SET分片上（独立的物理节点）组成。逻辑实例机）数据库一样使用即可。同时支持实时在线扩容，扩容过程对业务完全透明，无需业务停机，扩容时仅部分Set（只SQLSQL引SQL优2TDSQL······分布式事 关联查 并行计 流式引SQLSQL对MySQL深度优化发展而来的TXSQL内核，使得在主备网络延迟3ms的情况下，能做到跨IDCAutoShardingTDSQL还提供DBbridge数据库同步工具、DBBrain分布式数据库TDSQL分布式数据库TDSQL金融应用指南（三）基于成本因素考虑，用户可根据自身业务数据的容灾要求，以及数据中心分布情况，选择不同的部署方案。TDSQL的数据中心架构，在数据可靠性与可用性上做出权衡，做到灵活部署。3数据库生产部署：TDSQL实例，一主五从（2+2+2，2副本），(RTO优先)。数据库生产部署：ZK，5个节点（2+2+1）1数据库容灾部署：ZK，3，24分布式数据库TDSQL分布式数据库TDSQL金融应用指南数据库生产部署：TDSQL实例，一主三从（2+2，两个中心分别2副本），同中心异步跨中心强同步，可退化模式(RTO优先)。数据库生产部署：ZK，5个节点（2+2+1）1数据库容灾部署：ZK，3，25数据库生产部署：TDSQL实例，一主两从，强同步，可退化模式(RTO优先)。数据库生产部署：ZK，3，2数据库容灾部署：ZK，3，2分布式数据库TDSQL分布式数据库TDSQL金融应用指南6数据库生产部署：TDSQL（2+2，2），(RTO数据库生产部署：ZK，5个节点（2+2+1）17分布式数据库TDSQL分布式数据库TDSQL金融应用指南数据库生产部署：TDSQL(RTO管理节点配置：数据库生产部署：ZK，3，2三层互 三层互管理、同步流量业务流量：L3策略路由 管理、备份与业务三种流量通过VLAN方式相互隔离 TDSQL集TDSQL集DSDS交换机进行转发DSDS交换机进行转发8VLAN√10Gb，3ms30ms√服务器建议双网卡bonding√Proxy（如LVS）Proxy与DB混合部署时，一张网卡在数据备份专用网。DB√DB分布式数据库TDSQL分布式数据库TDSQL金融应用指南11SH36000、2118、2181、2338、2558、2888、ZKKafka8480、8485、9864、9866、9002、9867、8019、HDFS15001---4001---UDP/4001-DB14001-UDP/20000-22000(mysqlagentOSSDB15001-Proxy4001---14001-DBUDP/20000-分布式数据库TDSQL分布式数据库TDSQL金融应用指南在普通的主从高可用基础上，TDSQLVIP（IP）不变；基于强同30DBA可支持x86、ARMVPC即业务系统可以直接在两个中心读写数据库。跨可用区部署特性为TDSQLIDCa+1Ab+1B......Aa+1Ab+1B......A多了数据，9D，通过XtrabackupAA宕机，通过选举机制，CAA1）同城单中心，RPO=0，RTOA同城双中心，RPO=0，RTO≤40s（含故障检测时间，可配置调整异地多中心，RPO15s（异步复制），RTO5min（含故障检测时间，异地需手工切换）（一）（一）DB数据容量规划方法对于非分布式实例，数据库实例存储空间不超过2TB，单表存储容量不超过10G对于分布式(Shard)实例，每个SET分片存储空间不超过2TB，单个分片表(shardtable)的记录数量建议300W-3000W行，对10G~100G。4～5CPU使用率，投产后峰值建议不超过磁盘分区使用率，要求有60%+的余量6）(binlog/redo/undo)+*20%*20%binlog*10%≥估算临时表容量Proxy对于非分布式实例，Proxy:SET1:2；(Shard)AZ（IDC），Proxy:SET1:1(Shard)(SET)<2TB网络流量预估：假设当前异地网络情况为异地带宽500Mbps，平均延时上海数据中心到深圳数据中心目前带宽只有500Mbps，根据凌晨批量导入780021520Mbps（78000000*0.3*8/120/1024），3~5某国有大行异地网络：异地带宽5000Mbps，平均延时小于30msCPU根据实践经验进行推算，一台两颗CPU（16）1%CPU137*（32/48）91.79根据前期POC测试，在联机场景中数据库服务器达到25%时，应用服务器处于60%～70%之间；按照联机交易日常峰值数据库CPU25%32TPS1835.8结合当前主流服务器配置，并根据服务器用途配置512GB/128GB两种内存规格，故此推荐配置为32物理核。分布式数据库TDSQL金融应用指 分布式数据库TDSQL分布式数据库TDSQL金融应用指南（二）CPUCPU：1:4，1:200，磁盘建议选择nvme-SSD√业务简单，以大量读写请求为主，即内存型业务：CPU：内存≥1:12，内存：磁盘≥1:200，磁盘建议选择nvme-SSD√业务简单，性能要求不高，但容量大，即存储型业务：CPU：内存≥1:8，内存：磁盘≥1:500，磁盘建议选择SAS-SSD√存在具体TPS、QPS性能需求，但无测试报告的，此时的TPS、QPS√存在具体TPS、QPS33（经验值）。22CPU（物理核数内存磁盘AB√数据库对性能较高，虚拟机的性能损耗大（I/O、网络等）I/Ohang√分布式数据库组件众多，虚拟机系统因为共享底层资源，在发生涉及底层的复杂故障时，因为排查链路较长，分析故障的时间和周期√LVMlvdata（用于程序安装，保存程序运行日志、coredump等）data1（用于存放数据库数据（表和索引）、RAID（nvmessdRaidssdnasRAID0、RAID10）√为应对极端情况下资源不足问题，为操作系统预留资源，磁盘空间一般上报80~90%，内存容量一般上报70%检查所有设备yum源是否正常ntpx86超线程模式：建议打开，整体性能会有30%提升（每个逻辑核心处理能力降为物理核心能力的60%-70%左右）NUMANUMA，网卡队列不绑核，测试发现此种场景下数据库默认配置NUMA提升性能需进行数据库深度调优，成本较高。I/O调度算法：SSD磁盘建议选择mq-deadline调度算法，此算法下I/OCPU8.0utf8utf8mb416k设置隔离级别，TDSQLRC设置存储引擎，TDSQLinnodb3TDSQL31）HASHhash√√TINYINT,SMALLINT,MEDIUMINT,INT,√分布式数据库TDSQL金融应用指南（一）TDSQL是一款针对OLTP（一）【建议】OLTP应用尽量避免大事务，OLTP和OLAP应用分离，尽量不要在OLTP（10000ms（60000ms）。///需要合理的前端架构，缓存应用数据减少数据库压力。MySQL错误号以及所连接的数据库信息（IPPORT，数据库用户名），用于后TDSQL数据库端做运算，比如：md5()、sha()TDSQL（Shard），水平拆分是基于分表键采用类似于一致性HashRangeList这种场景称为组拆分（Groupshard）,率。TDSQLLIST、RANGE、HASHRANGE、LISTTDSQLhash、range、listhashshardkeyrange分布式数据库TDSQL分布式数据库TDSQL金融应用指南mysql>mysql>createtabletest1(aint,bint,cchar(20),primarykey(a,b),uniquekeyu_1(a,c))QueryOK,0rowsaffected(0.07在分布式实例中，Shardkeymysql>mysql>createtabletest1(aint,bint,cchar(20),primarykey(a,b),uniquekeyu_1(a,c),uniquekeyu_2(b,c))此时有一个唯一索引u_2不包含shardkey，ERRORERROR1105(HY000):AUNIQUEINDEXmustincludeallcolumnsinthetable'spartitioninguniquekeyshardkeyShardkey【建议】Shardkey【建议】Shardkeyint，bigint，smallint/char/varcharcharvarchar【禁止】Shardkey字段的值不能为中文，因为Proxy【禁止】不要更新shardkey字段的值。【建议】Shardkey=aSQL【建议】访问的数据尽量包含Shardkey字段，否则不带Shardkey字段的SQLRANGErange√√TINYINT,SMALLINT,MEDIUMINT,INT,and√createcreatetablet1(aintkey,bint)tdsql_distributedbyrange(a)(s1valueslessthan(100),s2valueslessTIMESTAMPtimestamp2038charvarchar255LISTlist√√TINYINT,SMALLINT,MEDIUMINT,INT,and√TIMESTAMPtimestamp2038charvarchar255。local_table_option√在tdsql_distributedbylocal_table_optionslocal_table_optionCREATECREATETABLE[IFNOTEXISTS]*tbl_name*(*create_definition*,...)[*local_table_options*]TDSQL_DISTRIBUTEDBYrange|list....√local_table_optionCREATECREATETABLEt1aint(11)NOTPRIMARYKEY(a))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binPARTITIONBYLIST(a)(PARTITIONp1VALUESIN(1,2)ENGINE=InnoDB,PARTITIONp2VALUESIN(3,4)ENGINE=TDSQL_DISTRIBUTEDBYLIST(b)(s1valuesin('100'),s2valuesinCREATETABLEtb_sub_ev(idint(11)NOTNULL,purchaseddateNOTNULL,)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binPARTITIONBYRANGE(YEAR(purchased))SUBPARTITIONBYHASH((PARTITIONp0VALUESLESSTHAN(1990)(SUBPARTITIONs0ENGINE=InnoDB,SUBPARTITIONs1ENGINE=InnoDB),PARTITIONp1VALUESLESSTHAN(2000)(SUBPARTITIONs2ENGINE=InnoDB,SUBPARTITIONs3ENGINE=InnoDB))TDSQL_DISTRIBUTEDBYRANGE(id)(s1valueslessthan('100'),s2valueslessthanCREATECREATETABLEt1aint(11)NOTbint(11)DEFAULTNULL,PRIMARYKEY(a))ENGINE=InnoDBDEFAULTCHARSET=utf8TDSQL_DISTRIBUTEDBYRANGE(a)(s1valueslessthan('100'),s2valueslessthansqlforceindexprimaryupper/lowertdsql_distributedbyrange(lower(b))√TDSQLRangeListMySQL分区语RANGERange√DATE，DATETIME，TIMESTAMPyear，month，daydayTINYINT,SMALLINT,MEDIUMINT,INTBIGINT√支持year，month，dayTIMESTAMPtimestamp2038HiredDate'YYYY-MM-DD'HASH，二级RANGEMySQLMySQL[test]>CREATETABLEemployees_int(idINTkeyNOTNULL,lnamehiredseparatedDATENOTNULLDEFAULT'9999-12-job_codeINT,store_idINTPARTITIONBYRANGE(year(hired))(PARTITIONp1VALUESLESSTHAN(1996),PARTITIONp2VALUESLESSTHANinsertintoemployees_int(id,fname,lname,hired,separated,job_code,store_id)values(10,'a',insertintoemployees_int(id,fname,lname,hired,separated,job_code,store_id)values(11,'c',insertintoemployees_int(id,fname,lname,hired,separated,job_code,store_id)values(12,'e',insertintoemployees_int(id,fname,lname,hired,separated,job_code,store_id)values(13,'g',select*,year(hired)from+----+-------+-------+------------+------------+----------+---------- |id|fname|lname| |separated|job_code|store_id|year(hired)+----+-------+-------+------------+------------+----------+---------- |11|||1722-08-24|1880-12-31100020001722|10|||1989-12-01|1880-12-31100020001989|12|||1994-03-08|1880-12-31100020001994|13|||1998-02-09|1880-12-31100020001998+----+-------+-------+------------+------------+----------+---------- 4rowsinset(0.02如果插入的Hired是Int类型，则Proxy首先会转换成对应的Date格式，'YYYY-MM-DD'，MySQLMySQL[test]>CREATETABLEemployees_int(idINTkeyNOTNULL,lnamehiredseparatedDATENOTNULLDEFAULT'9999-12-job_codeINT,store_idPARTITIONBYRANGE(year(hired))(PARTITIONp1VALUESLESSTHAN(1996),PARTITIONp2VALUESLESSTHANinsertintoemployees_int(id,fname,lname,hired,separated,job_code,store_id)values(10,'a',insertintoemployees_int(id,fname,lname,hired,separated,job_code,store_id)values(11,'c',insertintoemployees_int(id,fname,lname,hired,separated,job_code,store_id)values(12,'e',insertintoemployees_int(id,fname,lname,hired,separated,job_code,store_id)values(13,'g',select*,year(hired)from+----+-------+-------+------------+------------+----------+---------- |id|fname|lname| |separated|job_code|store_id|year(hired)+----+-------+-------+------------+------------+----------+---------- |12|||1994-03-08|1880-12-31|1000|20001994|11|||1722-08-24|1880-12-31|1000|20001722|10|||1989-12-01|1880-12-31|1000|20001989|13|||1998-02-09|1880-12-31|1000|20001998+----+-------+-------+------------+------------+----------+---------- 4rowsinset(0.03TDSQL3Year，TDSQL3（201820192020）LISTList√DATE，DATETIME，TIMESTAMPTINYINT,SMALLINT,MEDIUMINT,INTBIGINTTIMESTAMPtimestamp2038charvarchar255。HASH，二级ListMySQLMySQL[test]>CREATETABLEcustomers_1(first_nameVARCHAR(25)key,last_nameVARCHAR(25),street_1VARCHAR(30),street_2VARCHAR(30),cityVARCHAR(15),renewal)shardkey=first_namePARTITIONBYLIST(city)(PARTITIONpRegion_3VALUESIN('Suzhou','Hangzhou','Xiamen'),PARTITIONpRegion_4VALUESIN('Shenzhen','Guangzhou','Chengdu')MySQLMySQLMySQL[test]>altertablecustomers_1droppartitionMySQL[test]>altertablecustomers_1addpartition(partitionpRegion_5VALUESIN('Wuhan','Nanjing',TDSQL不支持除Range和List二级分区以外的其他分区操作，例如，ReorganizeRANGE，二级LISTMySQLMySQL[test]>CREATETABLEtb_sub_r_l(idint(11)NOTNULL,order_idbigintNOTNULL,PRIMARYKEY(id,order_id))PARTITIONBY(PARTITIONp0VALUESin(2121122),PARTITIONp1VALUESin(38937383))TDSQL_DISTRIBUTEDBYRANGE(id)(s1valueslessthan(100),s2valueslessthan(1000));QueryOK,0rowsaffected,1warning(0.35sec)RANGE，二级RANGE创建语法如下：MySQL[test]>CREATETABLEtb_sub_r_r(idint(11)NOTorder_idtinyintNOTNULL,PRIMARYKEY(id,order_id))PARTITIONBYrange(order_id)PARTITIONp1VALUESlessthanTDSQL_DISTRIBUTEDBYRANGE(id)(s1valueslessthan(100),s2valueslessthan(1000));LIST，二级RANGEMySQLMySQL[test]>CREATETABLEt1_sub_l_raint(11)NOTPRIMARYKEY(a))ENGINE=InnoDBDEFAULTCHARSET=utf8PARTITIONBYrange(PARTITIONp1VALUESlessthan(1000)ENGINE=InnoDB,PARTITIONp2VALUESlessthan(2000)ENGINE=TDSQL_DISTRIBUTEDBYLIST(b)(s1valuesin('100'),s2valuesin('200'));LIST，二级LIST创建语法如下：MySQLMySQL[test]>CREATETABLEt1_sub_l_laint(11)NOTbint(11)DEFAULTNULL,PRIMARYKEY(a))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binPARTITIONBYLIST(a)(PARTITIONp1VALUESIN(1,2)ENGINE=InnoDB,PARTITIONp2VALUESIN(3,4)ENGINE=TDSQL_DISTRIBUTEDBYLIST(b)(s1valuesin(100),s2valuesinDDL变更、需要定期进行分区数据清理和裁√合理设计二级分区的粒度，二级分区的粒度建议不要划分得太细，避免产生过多的二级子表。比如流水表按月进行二级分区，而不是小时进行分区，避免文件系统上数据文件个数过多。SQL查询时，查询条件需要尽量带上一级分区和二级分区的键值，避免执行查询时需要打开很多的数据文件进noshardkey_allset广播表主要用于提升跨节点组（SET）JoinMySQLMySQL[test]>createtableglobal_table_a(aint,bintkey)QueryOK,0rowsaffected(0.05Join普通表：又名单片表（Noshard）shardkeytdsql_distributedbyMySQL完全一样，所有该类型表的全量数据默认存放在第一个物理节点组（SET）中，具体语句如下：MySQLMySQL[test]>createtablenoshard_table(aint,bintQueryOK,0rowsaffected(0.21单片表不支持shardkey（SET的负载过大。Join（业务处理中会进行修改），DML本节主要介绍DML语句中常用的Select（查询）、Insert（插入）、Replace（替换）、Update（更新）及Delete（删除）1）[ALL|DISTINCT|DISTINCTROW[FROMtable_references[PARTITIONpartition_list]][WHEREwhere_condition][HAVINGwhere_condition][ORDERBY{col_name|expr|position}[ASC|DESC],...[WITHROLLUP]][FOR{UPDATE|SHARE}[NOWAIT|SKIPLOCKED]|LOCKINSHAREdroptableifexiststest2;createtabletest2(aintkey,dint,echar(20))shardkey=a;selectt1.a,t1.b,t1.c,t2.a,t2.d,t2.efromtest1t1jointest2t2ont1.b=t2.d;selectt1.a,t1.b,t1.cfromtest1t1wheret1.ain(selectafromtest2);selectt1.a,count(1)fromtest1t1whereexists(selectt2.a,t2.d,t2.efromtest2t2wheret2.a=t1.a)groupbyt1.a;selectdistinctcount(1)fromtest1t1whereexists(selectt2.a,t2.d,t2.efromtest2t2wheret2.a=t1.a)groupbyt1.a;selectcount(distinctt1.a)fromtest1t1whereexists(selectt2.a,t2.d,t2.efromtest2t2wheret2.a=t1.a);TDSQLSELECTDELETEUPDATEjoinJoin。DROPDROPTABLEIFEXISTS`test_join_shard_table1`;CREATETABLE`test_join_shard_table1`(`id`int(10)NOT`b`varchar(10)NOTNULLDEFAULTPRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binINSERTINTOtest_join_shard_table1(id,b,c)VALUES(1,"test1",1),(2,"test2",2),(3,"test3",3),(4,"test4",4),(5,"test5",5),(7,"test7",7),(8,"test8",8),DROPTABLEIFEXISTSCREATECREATETABLE`test_join_shard_table2``id`int(10)NOT`d``c`int(10)NOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binINSERTINTOtest_join_shard_table2(id,d,c)VALUES(1,NOW(),1),(2,NOW(),2),(3,NOW(),3),(4,NOW(),4),(5,NOW(),5),(7,NOW(),7),(8,NOW(),8),INNERJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1INNERJOINtest_join_shard_table2test2ONtest1.c=test2.cORDERBYRIGHTJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1RIGHTJOINtest_join_shard_table2test2ONtest1.c=test2.cORDERBYFULLJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1CROSSJOINtest_join_shard_table2test2ORDERBYNAME;Join。DROPDROPTABLEIFEXISTS`test_join_shard_table1`;CREATETABLE`test_join_shard_table1`(`id`int(10)NOT`b`varchar(10)NOTNULLDEFAULTPRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binINSERTINTOtest_join_shard_table1(id,b,c)VALUES(1,"test1",1),(2,"test2",2),(3,"test3",3),(4,"test4",4),(5,"test5",5),(7,"test7",7),(8,"test8",8),CREATETABLE`test_join_group_table2`(`id`int(10)NOT`d`PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binINSERTINTOtest_join_group_table2(id,d,c)VALUES(1,NOW(),1),(2,NOW(),2),(3,NOW(),3),(4,NOW(),4),(5,NOW(),5),(7,NOW(),7),(8,NOW(),8),INNERJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1INNERJOINtest_join_group_table2test2ONtest1.c=test2.cORDERBYLEFTJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1LEFTJOINtest_join_group_table2test2ONtest1.c=test2.cORDERBYRIGHTJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1RIGHTJOINtest_join_group_table2test2ONtest1.c=test2.cORDERBYFULLJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1CROSSJOINtest_join_group_table2ORDERBYDROPDROPTABLEIFEXISTS`test_join_shard_table1`;CREATETABLE`test_join_shard_table1`(`id`int(10)NOT`b`varchar(10)NOTNULLDEFAULTPRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binINSERTINTOtest_join_shard_table1(id,b,c)VALUES(1,"test1",1),(2,"test2",2),(3,"test3",3),(4,"test4",4),(5,"test5",5),(7,"test7",7),(8,"test8",8),DROPTABLEIFEXISTS`test_join_noshard_table2`;CREATETABLE`test_join_noshard_table2`(`id`int(10)NOT`d`PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8INSERTINTOtest_join_noshard_table2(id,d,c)VALUES(1,NOW(),1),(2,NOW(),2),(3,NOW(),3),(4,NOW(),4),(5,NOW(),5),(7,NOW(),7),(8,NOW(),8),selectselect*fromINNERJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1INNERJOINtest_join_noshard_table2test2ORDERBYLEFTJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1LEFTJOINtest_join_noshard_table2test2ORDERBYRIGHTJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1RIGHTJOINtest_join_noshard_table2test2ORDERBYRIGHTJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1RIGHTJOINtest_join_noshard_table2test2ORDERBYFULLJOINSELECTSELECTSELECTtest1.id,test1.bASNAME,test2.dASTIMEFROMtest_join_shard_table1test1CROSSJOINtest_join_noshard_table2ORDERBYupdate/deletejoinDROPDROPTABLEIFEXISTS`test_join_shard_table1`;CREATETABLE`test_join_shard_table1`(`id`int(10)NOT`b`varchar(10)NOTNULLDEFAULTPRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binINSERTINTOtest_join_shard_table1(id,b,c)VALUES(1,"test1",1),(2,"test2",2),(3,"test3",3),(4,"test4",4),(5,"test5",5),(7,"test7",7),(8,"test8",8),CREATETABLE`test_join_shard_table2`(`id`int(10)NOT`d`PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binINSERTINTOtest_join_shard_table2(id,d,c)VALUES(1,NOW(),1),(2,NOW(),2),(3,NOW(),3),(4,NOW(),4),(5,NOW(),5),(7,NOW(),7),(8,NOW(),8),UPDATE…JOIN…ON…SETUPDATEUPDATEtest_join_shard_table1test1INNERJOINtest_join_shard_table2test2WHEREtest1.id>7;SELECT*FROMUPDATE…JOIN…ON…SET语句，同一表多字段：INNERJOINtest_join_shard_table2test2ONtest1.c=test2.cWHEREtest1.id>7;SELECT*FROMDELETE…FROM…JOIN…ON语句：DELETEDELETEtest1FROMtest_join_shard_table1test1INNERJOINtest_join_shard_table2test2WHERESELECT*FROMUnionSELECTUNION[ALLSELECTUNION[ALL|DISTINCT]SELECT...UNIONselectDROPTABLEIFEXISTSDROPTABLEIFEXISTScreatetablet1(aintprimarykey,bint)shardkey=a;DROPTABLEIFEXISTSt2;createtablet2(aintprimarykey,bint)select*fromt1wheret1.ain(selectafromt2)unionselect*fromt2whereDROPDROPTABLEIFEXISTScreatetables1(aintprimarykey,bint)shardkey=a;DROPTABLEIFEXISTSs2;createtables2(aintprimarykey,bint)DROPDROPTABLEIFEXISTScreatetablens1(aintprimarykey,bint);DROPTABLEIFEXISTSns2;createtablens2(aintprimarykey,bDROPDROPTABLEIFEXISTScreatetableg1(aintprimarykey,bint)shardkey=noshardkey_allset;DROPTABLEIFEXISTSg2;createtableg2(aintprimarykey,bint)DROPDROPTABLEIFEXISTScreatetablep1(aint,bint,PRIMARYKEY(a))shardkey=aPARTITIONBYrange(b)(PARTITIONp0valueslessthan(100),PARTITIONp1valueslessthan(200));DROPTABLEIFEXISTScreatetablep2(aint,bint,PRIMARYKEY(a))shardkey=aPARTITIONBYrange(b)(PARTITIONp0valueslessthan(100),PARTITIONp1valueslessthan(200));selectselect*froms1unionselect*froms2;select*fromns1unionselect*fromns2;select*fromg1unionselect*fromg2;select*froms1unionselect*fromns1;select*fromp1unionselect*fromp2;select*froms1wherenotexists(select*froms2wheres2.a=s1.aorderbys2.a)orb<10unionselect*froms2wheres2.a>22;selecta,sum(b)froms1groupbyaunionselect*froms2;selecta,sum(b)froms1unionselect*froms2;selectdistinct(a)froms1unionselectafroms2;selectdistinct(a),bfroms1unionselecta,bfroms2;SELECTSELECTFROMWHEREexpr(SELECTselect_listFROMCREATETABLE`test_shard_table1`(`id`int(10)NOT`b`varchar(10)NOTNULLDEFAULTPRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binINSERTINTOtest_shard_table1(id,b,c)VALUES(1,"test1",1),(2,"test2",2),(3,"test3",3),(4,"test4",4),(5,"test5",5),(7,"test7",7),(8,"test8",8),DROPTABLEifexists`test_shard_table2`;CREATETABLE`test_shard_table2`(`id`int(10)NOT`d`PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binINSERTINTOtest_shard_table2(id,d,c)VALUES(1,NOW(),1),(2,NOW(),2),(3,NOW(),3),(4,NOW(),4),(5,NOW(),5),(7,NOW(),7),(8,NOW(),8),SELECTWHEREidIN(SELECTcFROMtest_shard_table2WHERESELECTMAX(c),FROMtest_shard_table1WHEREc(SELECTcFROMtest_shard_table2WHEREid<8)ANDid>4ORDERBYc;INSERT[INTO][PARTITION(partition_name[,partition_name]...)][(col_name[,col_name]...)]{{VALUES|VALUE}(value_list)[,(value_list)]VALUES[ONDUPLICATEKEYUPDATEassignment_list]INSERT[IGNORE][INTO][PARTITION(partition_name[,partition_name]SET[ONDUPLICATEKEYUPDATEassignment_list]INSERT[IGNORE][INTO][PARTITION(partition_name[,partition_name]...)][(col_name[,col_name]...)]{SELECT...|TABLE[ONDUPLICATEKEYUPDATEassignment_list]{expr|DEFAULT}value[,value]...assignment[,assignment]nsertSharkeySQLPoxySQL端数据库节点位置。测试不带shardkey字段：MySQLMySQL[test]>DROPTABLEIFEXISTSQueryOK,0rowsaffected(0.12MySQL[test]>createtabletest1(aintnotnullprimarykey,bint,cchar(10))shardkey=a;QueryOK,0rowsaffected(2.64sec)MySQL[test]>insertintotest1(b,c)ERROR683(HY000):ProxyERROR:Getshardkeysreturnerror:insert/replacemustcontainshardkeycolumnMySQL[test]>insertintotest1(a,c)values(40,"records5");QueryOK,1rowaffected(0.03ignore，QueryOK,0rowsaffected(0.10sec)MySQL[test]>createtablet1_1_1(aintprimarykey,bint)shardkey=a;QueryOK,0rowsaffected(0.18sec)QueryOK,0rowsaffected(0.07sec)QueryOK,0rowsaffected(0.18sec)QueryOK,3rowsaffected(0.01sec)MySQL[test]>select*from+--- |a|b+--- |1|0|2|0|3|1+--- 3rowsinset(0.00MySQL[test]>insertintot1_1_2selectbfromERROR913(HY000):ProxyERROR:Joininternalerror:Duplicateentry'0'forkeyignore，MySQLMySQL[test]>insertignoreintot1_1_2selectbfromt1_1_1;QueryOK,2rowsaffected,1warning(0.00sec)MySQL[test]>select*fromt1_1_2orderby+---|a+---|0|1+---2rowsinset(0.00[INTO][PARTITION(partition_name[,partition_name]...)][(col_name[,col_name]...)]{{VALUES|VALUE}(value_list)[,(value_list)]VALUES[INTO][PARTITION(partition_name[,partition_name]...)]SETassignment_list[INTO][PARTITION(partition_name[,partition_name]...)][(col_name[,col_name]...)]{SELECT...|TABLEtable_name}{expr|DEFAULT}value[,value]...col_name=valueassignment[,assignment]eplacehardkeSQLPrxySQL--测试不带shardkey字段：MySQLMySQL[test]>DROPTABLEIFEXISTSMySQL[test]>createtabletest5(aintnotnullprimarykey,bint,cchar(10))shardkey=a;QueryOK,0rowsaffected(0.27sec)MySQL[test]>replaceintotest5(b,c)ERROR683(HY000):ProxyERROR:Getshardkeysreturnerror:insert/replacemustcontainshardkeycolumnMySQL[test]>replaceintotest5(a,b,c)values(3,40,"record1");QueryOK,1rowaffected(0.00MySQLMySQL[test]>replaceintotest5(a,b,c)values(4,50,"record2"),(5,60,"record3"),(6,70,"record4"),(7,80,"record5"),(8,QueryOK,6rowsaffected(0.00replaceselectcreatetablet1_1_1(aintnotnullprimarykey,bchar(10))shardkey=a;droptableifexistst1_1_2;createtablet1_1_2(aintnotnullprimarykey,bchar(10))shardkey=a;replaceintot1_1_1select*fromDELETEDELETE[QUICK][IGNORE]FROMtbl_name[[AS]tbl_alias][PARTITION(partition_name[,partition_name]...)][WHEREwhere_condition][ORDERBY[LIMITdeletewhereSQLshardkeyMySQLMySQL[test]>DROPTABLEIFEXISTSMySQL[test]>createtabletest3(aintnotnullprimarykey,bint,cchar(10))shardkey=a;MySQL[test]>insertintotest3(a,b,c)values(1,2,'A');MySQL[test]>deletefromtest3;ERROR913(HY000):ProxyERROR:Joininternalerror:deletequeryhasnowhereclauseMySQL[test]>deletefromtest3wherea=1;QueryOK,1rowsaffected(0.00createtablet1_1(aintprimarykey,bint)shardkey=a;droptableifexistst1_2;createtablet1_2(aintprimarykey,bint)shardkey=a;insertintot1_1(a,b)values(20,20);insertintot1_1(a,b)values(9,9);insertintot1_2(a,b)values(9,9);insertintot1_1(a,b)values(8,8);insertintot1_2(a,b)values(8,8);insertintot1_1(a,b)values(7,7);insertintot1_2(a,b)values(7,7);insertintot1_1(a,b)values(6,6);insertintot1_2(a,b)values(6,6);insertintot1_1(a,b)values(5,5);insertintot1_2(a,b)values(5,5);insertintot1_1(a,b)values(4,4);insertintot1_2(a,b)values(4,4);insertintot1_1(a,b)values(3,3);insertintot1_2(a,b)values(3,3);insertintot1_1(a,b)values(2,2);insertintot1_2(a,b)values(2,2);insertintot1_1(a,b)values(1,1);insertintot1_2(a,b)values(1,1);deletefromt1_1whereain(selectbfromt1_2wheredeletefromt1_1whereexists(select1fromt1_2wheret1_1.a=t1_2.bandignoredroptableifexistst8_2;droptableifexistst8_3;createtablet8_3(aintNOTNULL,bint,primarykey(a));ignoret8_1.a<t8_3.a);ERROR1242(21000):Subqueryreturnsmorethan1ignoreMySQLMySQL[test]>deleteignoret8_1.*,t8_2.*fromt8_1,t8_2wheret8_1.a=t8_2.aandt8_1.b<>(selectbfromt8_3wheret8_1.a<t8_3.a);QueryOK,2rowsaffected,2warnings(0.01UPDATEUPDATE[IGNORE]table_reference[ORDERBY...][LIMITrow_count]{expr|DEFAULT}col_name=valueassignment[,assignment]shardkey，需用显示开启事务，再执行deleteinsertupdateupdatesetupdatewhereSQL测试updateDROPDROPTABLEIFEXISTSCREATETABLE(place_idint(10)unsignedNOTshowsint(10)unsignedDEFAULT'0'NOTNULL,ishowsint(10)unsignedDEFAULT'0'NOTushowsushowsint(10)unsignedDEFAULT'0'NOTNULL,clicksint(10)unsignedDEFAULT'0'NOTNULL,iclicksint(10)unsignedDEFAULT'0'NOTNULL,uclicksint(10)unsignedDEFAULT'0'NOTNULL,tstimestamp,PRIMARYKEY(place_id,ts))UPDATEt1_1SETshows=shows+1,ishows=ishows+1,ushows=ushows+1,clicks=clicks+1,iclicks=iclicks+1,uclicks=uclicks+1WHEREplace_id=1ANDts>="2000-09-2800:00:00";createtablet1_1(aintprimarykey,bint)shardkey=a;droptableifexistst1_2;createtablet1_2(aintprimarykey,bint)shardkey=a;droptableifexistst1_3;createtablet1_3(aintprimarykey,bint)shardkey=a;insertintot1_1(a,b)values(10,10);insertintot1_1(a,b)values(9,9);insertintot1_1(a,b)values(8,8);insertintot1_1(a,b)values(7,7);insertintot1_1(a,b)values(6,6);insertintot1_1(a,b)values(5,5);insertintot1_1(a,b)values(4,4);insertintot1_1(a,b)values(3,3);insertintot1_1(a,b)values(2,2);insertintot1_1(a,b)values(1,1);insertintot1_2select*fromt1_1;insertintot1_3select*fromupdatet1_1setb=1whereexists(select*fromt1_2wheret1_1.a=t1_2.aorderby1)limit3;updatet1_1setb=-1whereain(selectbfromt1_2orderby1)orderbyalimit3;update不支持更新主键：MySQLMySQL[test]>updatet1_1seta=bwhereexists(select1fromt1_2whereERROR658(HY000):ProxyERROR:Joininternalerror:cannotupdateprimaryupdateMySQLMySQL[test]>updatet1_1seta=200whereERROR682(HY000):ProxyERROR:Somethingwentwrong:cannotupdatethedeletefromt1_1where不支持update列表中带有sumMySQLMySQL[test]>updatet1_1setb=(selectmax(b)fromt1_2wheret1_2.a=t1_1.a)whereERROR658(HY000):ProxyERROR:Joininternalerror:donotsupportsubquery/suminupdateMySQLMySQL[test]>updatet1_1,t1_2sett1_1.b=-1wheret1_1.a=t1_2.bandQueryOK,0rowsaffected(0.01orderbyMySQLMySQL[test]>updatet1_1,t1_2sett1_1.b=-1wheret1_1.a=t1_2.bandt1_2.a<3orderbyt1_1.alimitERROR658(HY000):ProxyERROR:Joininternalerror:IncorrectusageofUPDATEandMySQLMySQL[test]>updatet1_1,t1_2sett1_1.b=-1,t1_2.b=-1wheret1_1.a=t1_2.bandERROR658(HY000):ProxyERROR:Joininternalerror:multiupdateisnotsupportedvalueMySQLMySQL[test]>updatet1_1,t1_2sett1_1.b=t1_2.b+1wheret1_1.a=t1_2.bandQueryOK,2rowsaffected(0.01CREATETABLElist_user(idint,namevarchar(255),cityvarchar(255),primarykey(id))PARTITIONby(PARTITIONp0valuesin('Beijin','Shanghai','Shenzhen'),PARTITIONp1valuesin('Nanjin',insertintolist_user(id,name,city)valuesMySQL[test]>updatelist_usersetcity='Nanjin'whereidin(selectidfromlist_user,t1_1wheret1_1.a=list_user.idandt1_1.a<3);ERROR913(HY000):ProxyERROR:Joininternalerror:subpartitionedtabledonotsupportsuchupdateyet!MySQL[test]>updatelist_usersetcity='Nanjin'whereid=1;ERROR682(HY000):ProxyERROR:Somethingwentwrong:cannotupdatethedropdroptableifexistsrange_part;createtablerange_partPARTITIONBYrangePARTITIONp1valueslessthaninsertintorange_part(a,b)valuesMySQL[test]>updaterange_partsetb=11whereain(selectafromrange_part,t1_1wheret1_1.a=range_part.idandt1_1.a<3);ERROR913(HY000):ProxyERROR:Joininternalerror:subpartitionedtabledonotsupportsuchupdateMySQL[test]>updaterange_partsetb=11whereERROR682(HY000):ProxyERROR:Somethingwentwrong:cannotupdatetheEXPLAIN语句{EXPLAIN{EXPLAIN|DESCRIBE|tbl_name[col_name|{EXPLAIN|DESCRIBE|{explainable_stmt|FORCONNECTION{EXPLAIN|DESCRIBE|DESC}ANALYZE[FORMAT=TREE]select_statementexplain_type:{FORMAT=format_name:{||explainable_stmt:{SELECTstatement|TABLE|DELETE|INSERT|REPLACE|UPDATE查看执行计划，SQL不会真正执行。DBSQLDROPDROPTABLEifexistsemployees;CREATETABLEemployees(idINTkeyNOTNULL,fnamelnamelnamehiredseparatedDATENOTNULLDEFAULT'9999-12-job_codeINT,store_idINTMySQL[test]>explainselectid,fname,lnamefromemployeeswhereid:1select_type:SIMPLEtable:NULLpartitions:NULLtype:NULLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:NULLfiltered:Extra:nomatchingrowinconstinfo:set_1624363251_3,explainselectid,fname,lnamefrom`test`.`employees`where(id=20)1rowinset(0.00sec)select_type：select的类型。type：possible_keys：key：key_len：表示索引中使用的字节数，可以通过key_lenrows：Extra：info：SETsql，infodroptableifexistst2;2selectMySQL[test]>explainselect*Fromt1MySQL[te