TDSQL TCA 分布式实例特点初探分布表和SQL透传

TDSQL分布式实例通过Proxy接口提供和mysql兼容的连接方式，用户通过IP地址、端口号以及用户名、密码进行连接：

（注意：公有云TDSQL需要在实例页面申请公网连接地址）

连接示例：mysql -h172.21.32.13 （proxy地址） -P3306（proxy端口） -utest （数据库账号） -p

与普通的mysql连接方法一致，分布式实例兼容mysql的协议和语法，支持SSL加密等功能。当然，您也可以使用navicat、 jdbc、 odbc、 php、 Python等来连接分布式TDSQL实例。

1、TDSQL分布式实例支持表的类型介绍

a、分布式表： 即水平拆分表，也成为“分表”，该表从业务视角是一张完整的逻辑表，但后端根据分表键（shardkey）的HASH值将数据分布到不同的物理节点组（SET）中。

b、普通表： 又名Noshard表，即无需拆分的表，和传统集中式数据库中的表一致，且没有做任何特殊处理的表，目前分布式实例将该表默认存放在第一个物理节点组（set）中。

c、广播表： 又名小表广播技术，即设置为广播表后，该表的所有 *** 作都将广播到所有物理节点组（set）中，每个set都有该表的全量数据，常用于业务系统关联查询较多，修改较少的小表或配置表等。

表类型选用注意事项：

在分布式实例中，如果两张表分表键相等，这意味着两张表**相同的分表键对应的行**，存储在相同的物理节点组中。这种场景通常被称为组拆分（groupshard）,会极大的提升业务联合查询等语句的处理效率。由于单表默认放置在第一个set上，如果在分布式实例中建立大的单表，则会导致第一个set的负载太大。除非特别需要，在分布式实例中尽量使用分布式表，这也是分布式实例的特点之一。

2、TDSQL分布式实例表的创建

接下来我们来看下分布式数据库TDSQL所支持的三种类型表的使用方法和注意事项。

a、分布式表的使用

简述：普通的分表创建时必须在最后面**指定分表键（shardkey）的值，该值为表中的一个字段名字，会用于后续sql的路由选择。连接到TDSQL分布式实例后，我们创建一个本次 *** 作使用的数据库名为：testdb

mysql>create database testdb

mysql>use testdb

接下来我们创建分布式表，命名以分布式拼音首字母命名

**建表语句1：**

MySQL testdb>create table fbs ( a int, b int, c char(20),primary key (a),unique key u_1(a,c) ) shardkey=a

Query OK, 0 rows affected (0.07 sec)

**建表语句2：**

MySQL testdb>create table fbs2 ( a int, b int, c char(20), primary key (a,b) ) shardkey=a

Query OK, 0 rows affected (0.09 sec)

b、广播表的创建

简述：支持建小表（广播表），此时该表在所有set中都是全部数据，这个主要方用于跨set的join *** 作，同时通过分布式事务保证修改 *** 作的原子性，使得所有set的数据是完全一致的 。

**语句：**

MySQL testdb>create table gbb(a int,b int key) **shardkey=noshardkey_allset**

Query OK, 0 rows affected (0.03 sec)

c、传统普通表

简述：支持建立普通的表，语法和传统mysql完全一样，此时该表的数据全量存在第一个set节点中，所有该类型的表都放在第一个set中。

MySQL testdb>create table ptb(a int ,b varchar(10))

Query OK, 0 rows affected (0.03 sec)

注意事项：

1、在分布式实例中，分布式表shardkey对应后端数据库的分区字段，因此必须是主键以及所有唯一索引的一部分， 否则可能无法完成建表 *** 作。

2、分布式表shardkey字段的值不包含中文, 否则proxy会转换字符集可能会出错。另外SQL语法上如：shardkey=a 一般放在SQL语句最后来写。

3、TDSQL分布式实例表的数据 *** 作

为了更好的发挥分布式架构的优势，在进行SQL *** 作时和传统数据库还是有部分差异。接下来我们从数据库的插入，更新，删除方面分别来看有哪些注意事项。

======INSERT插入 *** 作=======

**插入语句1：**

MySQL testdb>insert into fbs(a,b) values(10,1000)

Query OK, 1 row affected (0.00 sec)

**插入语句2：**

MySQL testdb>insert into fbs values(1,10,1000)

或

MySQL testdb>insert into test1 (b,c) values(100,"record3")

ERROR 810 (HY000): Proxy ERROR:sql is too complex,need to send to only noshard table.Shard table insert must has field spec

注意：语句2报错的原因insert时字段需要包含shardkey，否则会拒绝执行该sql，因为Proxy不知道该sql发往哪个后端分片节点。

=====UPDATE、DELETE更新、删除 *** 作=====

更新语句1：

MySQL testdb>update fbs set b=2000 where a=10

Query OK, 1 row affected (0.00 sec)

更新语句2：

MySQL testdb>update fbs set b=2000

ERROR 658 (HY000): Proxy ERROR: Join internal error: update query has no where clause

删除 *** 作：

MySQL testdb>delete from fbs

ERROR 913 (HY000): Proxy ERROR:Join internal error: delete query has no where clause

注意事项：

1、出于数据 *** 作安全上和减少人为误 *** 作导致数据丢失情况的出现，TDSQL禁止update 无 where 条件的更新动作。

2、同样的delete *** 作无where条件也会被禁止执行，如果确认要删除表数据或表，建议备份后用truncate或drop方式 *** 作。

3、同样的update *** 作时尽量避免更新shardkey字段，因为影响Proxy中的路由更新，会导致错误。

1、TDSQL透传功能介绍

对于分布式实例，会对SQL进行语法解析，有一定的限制，如果用户想在某个set中获取单个节点数据，或在指定节点执行SQL，可以使用TDSQL的透传SQL的功能。

使用透传功能，我们需要重新连接登录TDSQL分布式实例时指定 **- c选项**。普通登录方式，不支持指定节点执行SQL的透传功能。

登录如下：

mysql -h172.21.32.13 （proxy地址） -utest -P3306 -p -c(透传必须指定-c)

2、TDSQL透传 *** 作演示

首先我们重新登陆TDSQL分布式实例： mysql -h172.21.32.13 -utest -P3306 -p -c

仍旧切换使用testdb数据库。

a、查看分布式实例set节点

使用/*proxy*/show status 查看当前的TDSQL分布式实例的节点信息，共有两个set ，分别为set_1605181898_1、set_1605181972_3

MySQL testdb>/*proxy*/show status

+-----------------------------+-------------------------------------------------------------------+

| status_name | value |

+-----------------------------+-------------------------------------------------------------------+

| cluster | group_1605181791_302290 |

| **set_1605181898_1:ip | 10.53.179.14:4322s1@10.53.178.227:4322@1@IDC_GZ_YDSS0301_79263@0 |

| set_1605181898_1:hash_range | 0---31 |

| **set_1605181972_3:ip | 10.53.179.14:4323s1@10.53.178.227:4323@1@IDC_GZ_YDSS0301_79263@0 |

| set_1605181972_3:hash_range | 32---63 |

| set | set_1605181898_1,set_1605181972_3 |

+-----------------------------+-------------------------------------------------------------------+

6 rows in set (0.00 sec)

b、演示数据插入

我们针对之前创建的fbs分布式表进行数据的插入

MySQL testdb>insert into fbs(a,b,c) values(10,1,'AAA'),(20,2,'bbb'),(30,3,'ccc'),(40,4,'dddd'),(50,5,'eee'),(60,6,'fff'),(70,7,'ggg'),(80,8,'hhhh')

MySQL testdb>select * from fbs order by 1

+----+------+------+

| a | b | c |

+----+------+------+

| 10 | 1 | AAA |

| 20 | 2 | bbb |

| 30 | 3 | ccc |

| 40 | 4 | dddd |

| 50 | 5 | eee |

| 60 | 6 | fff |

| 70 | 7 | ggg |

| 80 | 8 | hhhh |

+----+------+------+

8 rows in set (0.00 sec)

c、透传查看数据在各个节点的分布情况

MySQL testdb>/*proxy*/show status

+-----------------------------+-------------------------------------------------------------------+

| status_name | value |

+-----------------------------+-------------------------------------------------------------------+

| cluster | group_1605181791_302290 |

| **set_1605181898_1:ip | 10.53.179.14:4322s1@10.53.178.227:4322@1@IDC_GZ_YDSS0301_79263@0 |

| set_1605181898_1:hash_range | 0---31 |

| set_1605181972_3:ip | 10.53.179.14:4323s1@10.53.178.227:4323@1@IDC_GZ_YDSS0301_79263@0 |

| set_1605181972_3:hash_range | 32---63 |

| set | set_1605181898_1,set_1605181972_3 |

+-----------------------------+-------------------------------------------------------------------+

6 rows in set (0.00 sec)

查看数据在set_1605181898_1 节点上的分布

MySQL testdb>/*sets:set_1605181898_1*/select * from fbs order by 1

+----+------+------+------------------+

| a | b | c | info |

+----+------+------+------------------+

| 10 | 1 | AAA | set_1605181898_1 |

| 30 | 3 | ccc | set_1605181898_1 |

| 40 | 4 | dddd | set_1605181898_1 |

| 50 | 5 | eee | set_1605181898_1 |

| 80 | 8 | hhhh | set_1605181898_1 |

+----+------+------+------------------+

5 rows in set (0.00 sec)

查看数据在set_1605181972_3节点上的分布

MySQL testdb>/*sets:set_1605181972_3*/select * from fbs order by 1

+----+------+------+------------------+

| a | b | c | info |

+----+------+------+------------------+

| 20 | 2 | bbb | set_1605181972_3 |

| 60 | 6 | fff | set_1605181972_3 |

| 70 | 7 | ggg | set_1605181972_3 |

+----+------+------+------------------+

3 rows in set (0.00 sec)

d、通过shardkey分片号查看数据

MySQL testdb>/*shardkey:2*/select * from fbs order by 1

+----+------+------+

| a | b | c |

+----+------+------+

| 20 | 2 | bbb |

| 60 | 6 | fff |

| 70 | 7 | ggg |

+----+------+------+

3 rows in set (0.00 sec)

支持透传种类和格式：

1、set名字可以通过/*proxy*/show status查询

2、/*sets:set_1名称*/ 透传指定节点

3、/*sets:allsets*/ 透传所有节点

4、/*shardkey:10*/ 透传到shardkey分片对应的set

5、支持透传sql到对应的一个或者多个set

分布式表的DDL部分的语句限制：

暂不支持CREATE TABLE ... LIKE

暂不支持CREATE TABLE ... SELECT

暂不支持CREATE TEMPORARY TABLE

暂不支持CREATE/DROP/ALTER SERVER/LOGFILE GROUP/

暂不支持ALTER对分表键(shardkey)进行重命名，不过可以修改类型

分布式表的DML部分的语句限制：

暂不支持SELECT INTO OUTFILE/INTO DUMPFILE/INTO LOAD DATA导出

暂不支持INSERT ... SELECT

暂不支持UPDATE 分布式shardkey列的值

本 *** 作主要是面向传统数据库的开发者或者DBA用户，让大家能够初步入手了解分布式数据库的特点。另外分布式数据库在架构上提供了灵活的读写分离模式，在SQL上支持全局的order by， group by， limit *** 作，支持聚合函数，跨set节点的join、子查询、支持分布式事务，传统数据库所支持的大部分 *** 作在分布式数据库中得到继承。分布式数据库是在传统数据库的基础之上发展起来的，对传统集中式的数据库有较好的兼容性，对SQL语句语法的使用上兼容大部分SQL1999,SQL2003标准，且对SQL的ACID特性都予以支持。分布式数据库在逻辑上是一个独立完整的数据库，但在架构上和物理上采用 多节点分片方式，经过内部算法将数据打散分布来到不同节点存储数据，对前端业务屏蔽后端的复杂架构，并且自身具备数据的最终一致性访问，可用性和分区容灾等特性的数据库。希望本次 *** 作能给大家带来一些对分布式数据库TDSQL的一些认识和收获。

*禁止转载，可转发（转发文章请注明出处）

TDPub企业级分布式关系数据库

建一张单库单表，不做任何拆分。

假设已经建好的分库数为 8，建一张表，只分库不分表，分库方式为根据 id 列哈希。

可以使用日期函数 MM/DD/WEEK/MMDD 来作为分表的拆分算法

当拆分算法不指定任何拆分字段时，系统默认使用主键作为拆分字段。

子句BROADCAST用来指定创建广播表。广播表是指将这个表复制到每个分库上，在分库上通过同步机制实现数据一致，有秒级延迟。

这样做的好处是可以将 JOIN *** 作下推到底层的 RDS（MySQL)，来避免跨库 JOIN。

需求背景：ETL离线作业，需要实时监控运行状况，由于调度使用Azkaban，故同步获取其后台配置库Mysql；本文记录第一步：获取projects工程码表，并作为广播变量，供后续使用。

Flink + Scalikejdbc + Scala

注意事项：

---