当前位置:网站首页>Tdsql TCA distributed instance characteristics -- distribution table and SQL transparent transmission

Tdsql TCA distributed instance characteristics -- distribution table and SQL transparent transmission

2020-12-07 19:20:37 User 7689089

TDSQL Distributed database data table application practice

The first part : Public cloud or private cloud TDSQL How to connect

TDSQL Distributed reality 例 adopt Proxy Interface provides and mysql Compatible connections , User pass IP Address 、 Port number and user name 、 Password in 行 Connect :

( Be careful : Public cloud TDSQL You need to apply for a public network connection address on the instance page )

Connection example :mysql -h172.21.32.13 (proxy Address ) -P3306(proxy port ) -utest ( Database account ) -p

With the ordinary mysql The connection method is consistent , Distributed reality 例 compatible mysql The protocol and syntax of , Support SSL Encryption and other functions . Of course , You can also use the navicat、 jdbc、 odbc、 php、 Python Distributed connectivity TDSQL example .

The second part :TDSQL Introduction and application of table types supported by distributed instances

1、TDSQL The type of distributed instance support table is introduced

a、 Distributed table : That is, split the table horizontally , Has become a “ table ”, The table is a complete logical table from the business perspective , But the back end is based on the sub table key (shardkey) Of HASH Values distribute data to different groups of physical nodes (SET) in .

b、 Common watch : also called Noshard surface , That is, tables that do not need to be split , It is consistent with the tables in the traditional centralized database , A table without any special treatment , Now distributed reality 例 The table is stored in the first physical node group by default (set) in .

c、 Broadcast table : Also known as small watch broadcasting technology , After setting it to broadcast table , All operations of the table are broadcast to the property 理 Node groups (set) in , Every set All have the full data of the table , It is often used in business system, associated query is more , Small tables or configuration tables with less modification .

Table type selection considerations :

In a distributed instance , If the key of two tables is equal , That means two tables ** The same sub table key corresponds to 行**, Stored in the same thing 理 In the node group . This scenario is often referred to as group splitting (groupshard), It will greatly improve the processing efficiency of business union query and other statements . Because the single table is placed in the first by default set On , If you create a large single table in a distributed instance , It will lead to the first set Too much load on . Unless there is a special need for , Try to use distributed tables in distributed instances , This is one of the characteristics of distributed instances .

2、TDSQL The creation of distributed instance table

Next, let's take a look at distributed databases TDSQL The usage and precautions of the three types of tables supported .

a、 The use of distributed tables

sketch : When creating a common sub table, it must be at the end of the table ** Specify sub table key (shardkey) Value , The value is the name of a field in the table , It will be used for follow-up sql Routing of . Connect to TDSQL After the distributed instance , We create a database named :testdb

mysql> create database testdb;

mysql>use testdb;

Next we create a distributed table , The name is named after the first letter of distributed Pinyin

** Create table statement 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)

** Create table statement 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、 Broadcast table creation

sketch : Support the creation of small tables ( Broadcast table ), At this point, the table is in all set It's all data , This main party is used to cross set Of join operation , At the same time, the atomicity of modification is guaranteed by distributed transaction , Make all set The data are exactly the same .

** sentence :**

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

Query OK, 0 rows affected (0.03 sec)

c、 Traditional ordinary watch

sketch : Support the creation of common tables , Grammar and tradition mysql Exactly the same as , At this time, there is the first data in the table set In nodes , All tables of this type are placed first set in .

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

Query OK, 0 rows affected (0.03 sec)

matters needing attention :

1、 In a distributed instance , Distributed table shardkey The partition field corresponding to the back-end database , So it has to be part of the primary key and all unique indexes , Otherwise, you may not be able to complete the table creation operation .

2、 Distributed table shardkey The value of the field does not contain Chinese , otherwise proxy Will convert the character set, may make mistakes . in addition SQL Grammatically, it's like :shardkey=a Generally placed on SQL At the end of the sentence .

3、TDSQL Data operation of distributed instance table

by 了 Better play to the advantages of distributed architecture , It's going on SQL There are some differences between traditional database and operation . Next we insert from the database , to update , In terms of deletion, we can see what should be noted .

======INSERT The insert =======

** Insert statement 1:**

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

Query OK, 1 row affected (0.00 sec)

** Insert statement 2:**

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

or

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

Be careful : sentence 2 Reasons for reporting errors insert When the field needs to contain shardkey, Otherwise, they will refuse to execute the sql, because Proxy I don't know sql Which backend fragment node to send to .

=====UPDATE、DELETE to update 、 Delete operation =====

UPDATE statement 1:

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

Query OK, 1 row affected (0.00 sec)

UPDATE statement 2:

MySQL testdb> update fbs set b=2000 ;

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

Delete operation :

MySQL testdb> delete from fbs;

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

matters needing attention :

1、 For the safety of data operation and reduce the occurrence of data loss caused by human error operation ,TDSQL prohibit update nothing where Condition update action .

2、 alike delete No operation where Conditions will also be prohibited , If you are sure you want to delete table data or table , It is recommended to use after backup truncate or drop Way to operate .

3、 alike update Try to avoid updating during operation shardkey Field , Because of the impact Proxy Route update in , Can cause errors .

The third part :TDSQL Distributed instances SQL Transparent transmission function ( Specify node access to )

1、TDSQL Introduction of transparent transmission function

For distributed instances , Would be right SQL Parsing , There are certain restrictions , If the user wants to be in some set Get the data of a single node in the , Or at the specified node SQL, have access to TDSQL Transparent SQL The function of .

Use the passthrough function , We need to re connect and log in TDSQL When distributed instances are specified **- c Options **. Normal login method , Specified node execution is not supported SQL The transparent transmission function of .

Log in as follows :

mysql -h172.21.32.13 (proxy Address ) -utest -P3306 -p -c( Transmission must specify -c)

2、TDSQL Transparent operation demonstration

First of all, we re-enter TDSQL Distributed instances : mysql -h172.21.32.13 -utest -P3306 -p -c

Still switch to use testdb database .

a、 View distributed instances set node

Use /*proxy*/show status View the current TDSQL Node information of distributed instance , There are two set , Respectively 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:4322;s1@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:4323;s1@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、 Demonstrate data insertion

We're aiming at fbs Distributed table for data insertion

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、 Transparent transmission to view the distribution of data in each node

MySQL testdb> /*proxy*/show status;

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

| status_name | value |

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

| cluster | group_1605181791_302290 |

| **set_1605181898_1:ip | 10.53.179.14:4322;s1@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:4323;s1@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)

Look at the data in set_1605181898_1 Distribution on nodes

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)

Look at the data in set_1605181972_3 Distribution on nodes

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、 adopt shardkey View data by slice number

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)

Support transparent transmission types and formats :

1、set Names can be passed by /*proxy*/show status Inquire about

2、/*sets:set_1 name */ Pass through the specified node

3、/*sets:allsets*/ Pass through all nodes

4、/*shardkey:10*/ Transparence shardkey The fragment corresponds to set

5、 Support transparent transmission sql To the corresponding one or more set

Distributed table DDL Part of the sentence limit :

Temporary does not support CREATE TABLE ... LIKE

Temporary does not support CREATE TABLE ... SELECT

Temporary does not support CREATE TEMPORARY TABLE

Temporary does not support CREATE/DROP/ALTER SERVER/LOGFILE GROUP/

Temporary does not support ALTER The bisector key (shardkey) Rename , But you can change the type

Distributed table DML Part of the sentence limit :

Temporary does not support SELECT INTO OUTFILE/INTO DUMPFILE/INTO LOAD DATA export

Temporary does not support INSERT ... SELECT

Temporary does not support UPDATE Distributed shardkey The value of the column

Content summary :

This operation is mainly for developers of traditional database or DBA user , So that we can start to understand the characteristics of distributed database . In addition, the distributed database provides a flexible separation mode between read and write , stay SQL Support the overall situation order by, group by, limit operation , Support aggregate functions , Span set Node join、 Subquery 、 Support distributed transactions , Most of the operations supported by traditional databases are inherited in distributed databases . The foundation of distributed database is based on traditional database , It has good compatibility with traditional centralized database , Yes SQL Sentence syntax is compatible with most of SQL1999,SQL2003 standard , And right SQL Of ACID Features are supported . Distributed database is logically an independent and complete database , But both architecturally and physically Multi node fragmentation mode , Through the internal algorithm, the data is scattered and distributed to different nodes to store data , Shield the complex architecture of the back end for front-end business , And it has the final consistent access to the data , Availability and partition disaster recovery and other characteristics of the database . I hope this operation can bring you some information about distributed database TDSQL Some of the knowledge and harvest of .

Original statement , This article is authorized by the author + Community publication , Unauthorized , Shall not be reproduced .

If there is any infringement , Please contact the yunjia_community@tencent.com Delete .

版权声明
本文为[User 7689089]所创,转载请带上原文链接,感谢
https://chowdera.com/2020/11/20201114001107130b.html