2024分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南

2024錄概 邏輯架 部署架 資源評估方 配置建 模型設(shè) SQL編寫規(guī) 數(shù)據(jù)安

（一）1.TDSQL特點(diǎn)TSQL（TDSL）是騰訊打造的一款企業(yè)級數(shù)據(jù)庫產(chǎn)品，其定位是基于互聯(lián)網(wǎng)分布式架構(gòu)的金融級數(shù)據(jù)庫。TDSQLMySQL、PostgreSQLOracleSQL2016JSON、空間TDQLDDL/DML/DCL分布式聯(lián)合查詢，數(shù)據(jù)強(qiáng)一致能力。TDQLDStoreHTPTDSQL群擴(kuò)展能力。同時，TDSQLDBA、自動化運(yùn)營、監(jiān)控告警等配套設(shè)施，為用戶提供完整的分布式數(shù)據(jù)庫解決方案。（一）1.TDSQL特點(diǎn):ATM所以，銀行業(yè)對分布式數(shù)據(jù)庫的需求可大致概括如下：首先支持業(yè)務(wù)系統(tǒng)安全穩(wěn)定的運(yùn)行是重中之重；其次是可承載多類型的海量數(shù)據(jù)，同時還可處理高并發(fā)的事務(wù)，可支持彈性擴(kuò)容；再次是開發(fā)和運(yùn)維成本要相對可控等。實(shí)時性，證券交易是實(shí)時的，系統(tǒng)需要能夠快速響應(yīng)并處理交易請求。三是安全性，證券業(yè)務(wù)涉及到大量的資金交易，因此系統(tǒng)的安全性是非常重要的。四是穩(wěn)定性，證券業(yè)務(wù)系統(tǒng)需要保證交易時段不間斷運(yùn)行，不能出現(xiàn)任何故障。對于分布式數(shù)據(jù)庫的需求，證券業(yè)主要有以下幾點(diǎn)：一是高可用，分布式數(shù)據(jù)庫需要保證在任何情況下都能提供服務(wù)，不能因?yàn)閱吸c(diǎn)故小額且頻次高，數(shù)據(jù)庫負(fù)載呈現(xiàn)爆發(fā)式增長。三是活躍數(shù)據(jù)存儲周期長，如壽險、健康險通常是按若干年為周期計算，對于數(shù)據(jù)一致性、分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指 分布式數(shù)據(jù)庫TDSQL分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南（二）TDSQL系統(tǒng)架構(gòu)設(shè)計的核心思路是：標(biāo)準(zhǔn)化、模塊化。例如TDSQL的模塊設(shè)計是充分解耦的，彼此之間通過接口銜接，用戶可以自NN中心的部署，如金融行業(yè)常見的同城雙中心、同城三中心、兩地三中心、兩地四中心等高可用容災(zāi)部署架構(gòu)，數(shù)據(jù)副本亦可做到一主N備（N≥0）；甚至在設(shè)計讀寫分離機(jī)制時，只讀賬戶可根據(jù)主備延遲狀態(tài)，選擇是從備TDSQL的整個研發(fā)過程，給予用戶足夠的靈活性和選擇權(quán)。TDSQLDBDBZKMeta1TDSQLSQLSQLSQL旁路模塊：AgentDB決策集群：Zookeeper調(diào)度模塊：anager/Scheduler主備切換、擴(kuò)縮容、資源管理或者執(zhí)行業(yè)務(wù)流程等。OSSHTTPPOSTPOSTbodyTDSQLwebTDSQL。赤兔管理臺：TDSQLTDSQLTDSQL赤兔通過調(diào)用OSS接口進(jìn)行任務(wù)下發(fā)到ZK組件進(jìn)行任務(wù)節(jié)點(diǎn)創(chuàng)建，由Scheduler組件進(jìn)行監(jiān)聽，當(dāng)監(jiān)聽到相關(guān)任務(wù)節(jié)點(diǎn)后，調(diào)用Manager組件進(jìn)行具體任務(wù)執(zhí)行，并更新ZK任務(wù)節(jié)點(diǎn)，赤兔運(yùn)維平臺通過OSS查看任務(wù)進(jìn)度狀態(tài)。分布式數(shù)據(jù)庫TDSQL分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南水平擴(kuò)容機(jī)制：分布式實(shí)例對外呈現(xiàn)為一個完整的邏輯實(shí)例，后端數(shù)據(jù)實(shí)際分布在若干SET分片上（獨(dú)立的物理節(jié)點(diǎn)）組成。邏輯實(shí)例機(jī)）數(shù)據(jù)庫一樣使用即可。同時支持實(shí)時在線擴(kuò)容，擴(kuò)容過程對業(yè)務(wù)完全透明，無需業(yè)務(wù)停機(jī)，擴(kuò)容時僅部分Set（只SQLSQL引SQL優(yōu)2TDSQL······分布式事 關(guān)聯(lián)查 并行計 流式引SQLSQL對MySQL深度優(yōu)化發(fā)展而來的TXSQL內(nèi)核，使得在主備網(wǎng)絡(luò)延遲3ms的情況下，能做到跨IDCAutoShardingTDSQL還提供DBbridge數(shù)據(jù)庫同步工具、DBBrain分布式數(shù)據(jù)庫TDSQL分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南（三）基于成本因素考慮，用戶可根據(jù)自身業(yè)務(wù)數(shù)據(jù)的容災(zāi)要求，以及數(shù)據(jù)中心分布情況，選擇不同的部署方案。TDSQL的數(shù)據(jù)中心架構(gòu)，在數(shù)據(jù)可靠性與可用性上做出權(quán)衡，做到靈活部署。3數(shù)據(jù)庫生產(chǎn)部署：TDSQL實(shí)例，一主五從（2+2+2，2副本），(RTO優(yōu)先)。數(shù)據(jù)庫生產(chǎn)部署：ZK，5個節(jié)點(diǎn)（2+2+1）1數(shù)據(jù)庫容災(zāi)部署：ZK，3，24分布式數(shù)據(jù)庫TDSQL分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南數(shù)據(jù)庫生產(chǎn)部署：TDSQL實(shí)例，一主三從（2+2，兩個中心分別2副本），同中心異步跨中心強(qiáng)同步，可退化模式(RTO優(yōu)先)。數(shù)據(jù)庫生產(chǎn)部署：ZK，5個節(jié)點(diǎn)（2+2+1）1數(shù)據(jù)庫容災(zāi)部署：ZK，3，25數(shù)據(jù)庫生產(chǎn)部署：TDSQL實(shí)例，一主兩從，強(qiáng)同步，可退化模式(RTO優(yōu)先)。數(shù)據(jù)庫生產(chǎn)部署：ZK，3，2數(shù)據(jù)庫容災(zāi)部署：ZK，3，2分布式數(shù)據(jù)庫TDSQL分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南6數(shù)據(jù)庫生產(chǎn)部署：TDSQL（2+2，2），(RTO數(shù)據(jù)庫生產(chǎn)部署：ZK，5個節(jié)點(diǎn)（2+2+1）17分布式數(shù)據(jù)庫TDSQL分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南數(shù)據(jù)庫生產(chǎn)部署：TDSQL(RTO管理節(jié)點(diǎn)配置：數(shù)據(jù)庫生產(chǎn)部署：ZK，3，2三層互 三層互管理、同步流量業(yè)務(wù)流量：L3策略路由 管理、備份與業(yè)務(wù)三種流量通過VLAN方式相互隔離 TDSQL集TDSQL集DSDS交換機(jī)進(jìn)行轉(zhuǎn)發(fā)DSDS交換機(jī)進(jìn)行轉(zhuǎn)發(fā)8VLAN√10Gb，3ms30ms√服務(wù)器建議雙網(wǎng)卡bonding√Proxy（如LVS）Proxy與DB混合部署時，一張網(wǎng)卡在數(shù)據(jù)備份專用網(wǎng)。DB√DB分布式數(shù)據(jù)庫TDSQL分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南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-分布式數(shù)據(jù)庫TDSQL分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南在普通的主從高可用基礎(chǔ)上，TDSQLVIP（IP）不變；基于強(qiáng)同30DBA可支持x86、ARMVPC即業(yè)務(wù)系統(tǒng)可以直接在兩個中心讀寫數(shù)據(jù)庫?？缈捎脜^(qū)部署特性為TDSQLIDCa+1Ab+1B......Aa+1Ab+1B......A多了數(shù)據(jù)，9D，通過XtrabackupAA宕機(jī)，通過選舉機(jī)制，CAA1）同城單中心，RPO=0，RTOA同城雙中心，RPO=0，RTO≤40s（含故障檢測時間，可配置調(diào)整異地多中心，RPO15s（異步復(fù)制），RTO5min（含故障檢測時間，異地需手工切換）（一）（一）DB數(shù)據(jù)容量規(guī)劃方法對于非分布式實(shí)例，數(shù)據(jù)庫實(shí)例存儲空間不超過2TB，單表存儲容量不超過10G對于分布式(Shard)實(shí)例，每個SET分片存儲空間不超過2TB，單個分片表(shardtable)的記錄數(shù)量建議300W-3000W行，對10G~100G。4～5CPU使用率，投產(chǎn)后峰值建議不超過磁盤分區(qū)使用率，要求有60%+的余量6）(binlog/redo/undo)+*20%*20%binlog*10%≥估算臨時表容量Proxy對于非分布式實(shí)例，Proxy:SET1:2；(Shard)AZ（IDC），Proxy:SET1:1(Shard)(SET)<2TB網(wǎng)絡(luò)流量預(yù)估：假設(shè)當(dāng)前異地網(wǎng)絡(luò)情況為異地帶寬500Mbps，平均延時上海數(shù)據(jù)中心到深圳數(shù)據(jù)中心目前帶寬只有500Mbps，根據(jù)凌晨批量導(dǎo)入780021520Mbps（78000000*0.3*8/120/1024），3~5某國有大行異地網(wǎng)絡(luò)：異地帶寬5000Mbps，平均延時小于30msCPU根據(jù)實(shí)踐經(jīng)驗(yàn)進(jìn)行推算，一臺兩顆CPU（16）1%CPU137*（32/48）91.79根據(jù)前期POC測試，在聯(lián)機(jī)場景中數(shù)據(jù)庫服務(wù)器達(dá)到25%時，應(yīng)用服務(wù)器處于60%～70%之間；按照聯(lián)機(jī)交易日常峰值數(shù)據(jù)庫CPU25%32TPS1835.8結(jié)合當(dāng)前主流服務(wù)器配置，并根據(jù)服務(wù)器用途配置512GB/128GB兩種內(nèi)存規(guī)格，故此推薦配置為32物理核。分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指 分布式數(shù)據(jù)庫TDSQL分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南（二）CPUCPU：1:4，1:200，磁盤建議選擇nvme-SSD√業(yè)務(wù)簡單，以大量讀寫請求為主，即內(nèi)存型業(yè)務(wù)：CPU：內(nèi)存≥1:12，內(nèi)存：磁盤≥1:200，磁盤建議選擇nvme-SSD√業(yè)務(wù)簡單，性能要求不高，但容量大，即存儲型業(yè)務(wù)：CPU：內(nèi)存≥1:8，內(nèi)存：磁盤≥1:500，磁盤建議選擇SAS-SSD√存在具體TPS、QPS性能需求，但無測試報告的，此時的TPS、QPS√存在具體TPS、QPS33（經(jīng)驗(yàn)值）。22CPU（物理核數(shù)內(nèi)存磁盤AB√數(shù)據(jù)庫對性能較高，虛擬機(jī)的性能損耗大（I/O、網(wǎng)絡(luò)等）I/Ohang√分布式數(shù)據(jù)庫組件眾多，虛擬機(jī)系統(tǒng)因?yàn)楣蚕淼讓淤Y源，在發(fā)生涉及底層的復(fù)雜故障時，因?yàn)榕挪殒溌份^長，分析故障的時間和周期√LVMlvdata（用于程序安裝，保存程序運(yùn)行日志、coredump等）data1（用于存放數(shù)據(jù)庫數(shù)據(jù)（表和索引）、RAID（nvmessdRaidssdnasRAID0、RAID10）√為應(yīng)對極端情況下資源不足問題，為操作系統(tǒng)預(yù)留資源，磁盤空間一般上報80~90%，內(nèi)存容量一般上報70%檢查所有設(shè)備yum源是否正常ntpx86超線程模式：建議打開，整體性能會有30%提升（每個邏輯核心處理能力降為物理核心能力的60%-70%左右）NUMANUMA，網(wǎng)卡隊列不綁核，測試發(fā)現(xiàn)此種場景下數(shù)據(jù)庫默認(rèn)配置NUMA提升性能需進(jìn)行數(shù)據(jù)庫深度調(diào)優(yōu)，成本較高。I/O調(diào)度算法：SSD磁盤建議選擇mq-deadline調(diào)度算法，此算法下I/OCPU8.0utf8utf8mb416k設(shè)置隔離級別，TDSQLRC設(shè)置存儲引擎，TDSQLinnodb3TDSQL31）HASHhash√√TINYINT,SMALLINT,MEDIUMINT,INT,√分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南（一）TDSQL是一款針對OLTP（一）【建議】OLTP應(yīng)用盡量避免大事務(wù)，OLTP和OLAP應(yīng)用分離，盡量不要在OLTP（10000ms（60000ms）。///需要合理的前端架構(gòu)，緩存應(yīng)用數(shù)據(jù)減少數(shù)據(jù)庫壓力。MySQL錯誤號以及所連接的數(shù)據(jù)庫信息（IPPORT，數(shù)據(jù)庫用戶名），用于后TDSQL數(shù)據(jù)庫端做運(yùn)算，比如：md5()、sha()TDSQL（Shard），水平拆分是基于分表鍵采用類似于一致性HashRangeList這種場景稱為組拆分（Groupshard）,率。TDSQLLIST、RANGE、HASHRANGE、LISTTDSQLhash、range、listhashshardkeyrange分布式數(shù)據(jù)庫TDSQL分布式數(shù)據(jù)庫TDSQL金融應(yīng)用指南mysql>mysql>createtabletest1(aint,bint,cchar(20),primarykey(a,b),uniquekeyu_1(a,c))QueryOK,0rowsaffected(0.07在分布式實(shí)例中，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字段的值不能為中文，因?yàn)镻roxy【禁止】不要更新shardkey字段的值?！窘ㄗh】Shardkey=aSQL【建議】訪問的數(shù)據(jù)盡量包含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分區(qū)語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首先會轉(zhuǎn)換成對應(yīng)的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二級分區(qū)以外的其他分區(qū)操作，例如，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創(chuàng)建語法如下：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創(chuàng)建語法如下：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變更、需要定期進(jìn)行分區(qū)數(shù)據(jù)清理和裁√合理設(shè)計二級分區(qū)的粒度，二級分區(qū)的粒度建議不要劃分得太細(xì)，避免產(chǎn)生過多的二級子表。比如流水表按月進(jìn)行二級分區(qū)，而不是小時進(jìn)行分區(qū)，避免文件系統(tǒng)上數(shù)據(jù)文件個數(shù)過多。SQL查詢時，查詢條件需要盡量帶上一級分區(qū)和二級分區(qū)的鍵值，避免執(zhí)行查詢時需要打開很多的數(shù)據(jù)文件進(jìn)noshardkey_allset廣播表主要用于提升跨節(jié)點(diǎn)組（SET）JoinMySQLMySQL[test]>createtableglobal_table_a(aint,bintkey)QueryOK,0rowsaffected(0.05Join普通表：又名單片表（Noshard）shardkeytdsql_distributedbyMySQL完全一樣，所有該類型表的全量數(shù)據(jù)默認(rèn)存放在第一個物理節(jié)點(diǎn)組（SET）中，具體語句如下：MySQLMySQL[test]>createtablenoshard_table(aint,bintQueryOK,0rowsaffected(0.21單片表不支持shardkey（SET的負(fù)載過大。Join（業(yè)務(wù)處理中會進(jìn)行修改），DML本節(jié)主要介紹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端數(shù)據(jù)庫節(jié)點(diǎn)位置。測試不帶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，需用顯示開啟事務(wù)，再執(zhí)行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查看執(zhí)行計劃，SQL不會真正執(zhí)行。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：表示索引中使用的字節(jié)數(shù)，可以通過key_lenrows：Extra：info：SETsql，infodroptableifexistst2;2selectMySQL[test]>explainselect*Fromt1MySQL[te