腾讯云数据库TDSQL MySQL版开发指南（四）

版本日期：2024.03（最新版本以腾讯云官网产品文档为准，本链接为2024年3月版）

版权声明

本文档著作权归腾讯云计算（北京）有限责任公司（以下简称“腾讯云”）单独所有，未经腾讯云事先书面许可，任何主体不得以任何方式或理由使用本文档，包括但不限于复制、修改、传播、公开、剽窃全部或部分本文档内容。

本文档及其所含内容均属腾讯云内部资料，并且仅供腾讯云指定的主体查看。如果您非经腾讯云授权而获得本文档的全部或部分内容，敬请予以删除，切勿以复制、披露、传播等任何方式使用本文档或其任何内容，亦请切勿依本文档或其任何内容而采取任何行动。

免责声明

本文档旨在向客户介绍本文档撰写时，腾讯云相关产品、服务的当时的整体概况，部分产品或服务在后续可能因技术调整或项目设计等任何原因，导致其服务内容、标准等有所调整。因此，本文档仅供参考，腾讯云不对其准确性、适用性或完整性等做任何保证。您所购买、使用的腾讯云产品、服务的种类、内容、服务标准等，应以您和腾讯云之间签署的合同约定为准，除非双方另有约定，否则，腾讯云对本文档内容不做任何明示或默示的承诺或保证。

全局唯一字段

关键字auto_increment，即支持一个全局的自增字段，auto_increment 可以保证该表某个字段全局唯一，但不保证单调递增，具体使用方法如下：

• 只支持在 shard 表（不支持二级分区、TDSQL_DISTRIBUTED 表、全局表和单表）中定义全局唯一索引。
• 支持在建表时指定全局唯一索引。
• 必须要有主键，且主键和全局唯一索引的总数不能超过64。
• 索引表名不能超过 DB 上最长表名64字节的限制。
• 暂不支持唯一索引列有默认值。
• 索引列不允许 timestamp 存在 ON UPDATE CURRENT_TIMESTAMP。
• 创建带全局唯一索引的表时，需要创建时打开 sql_require_primary_key=0 参数。

创建

mysql> create table auto_inc (a int,b int,c int auto_increment,d int,key auto(c),primary key p(a,d)) shardkey=d;

Query OK, 0 rows affected (0.12 sec)

插入

mysql> insert into shard.auto_inc ( a,b,d,c) values(1,2,3,0),(1,2,4,0);

Query OK, 2 rows affected (0.05 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from shard.auto_inc;

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

| a | b | c | d |

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

| 1 | 2 | 2 | 4 |

| 1 | 2 | 1 | 3 |

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

2 rows in set (0.03 sec)

如果发生切换、重启等过程，自增长字段中间会有空洞，例如：

mysql> insert into shard.auto_inc ( a,b,d,c) values(11,12,13,0),(21,22,23,0);

Query OK, 2 rows affected (0.03 sec)

mysql> select * from shard.auto_inc;

+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+

| a | b | c | d |

+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+

| 21 | 22 | 2002 | 23 |

| 1 | 2 | 2 | 4 |

| 1 | 2 | 1 | 3 |

| 11 | 12 | 2001 | 13 |

+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐+

4 rows in set (0.01 sec)

更改当前值：

alter table auto_inc auto_increment=100

通过select last_insert_id()获取最近一个自增值：

mysql> insert into auto_inc ( a,b,d,c) values(1,2,3,0),(1,2,4,0);

Query OK, 2 rows affected (0.73 sec)

mysql> select * from auto_inc;

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

| a | b | c | d |

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

| 1 | 2 | 4001 | 3 |

| 1 | 2 | 4002 | 4 |

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

2 rows in set (0.00 sec)

mysql> select last_insert_id();

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

| last_insert_id() |

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

| 4001 |

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

1 row in set (0.00 sec)

目前 select last_insert_id() 只能跟 shard 表和广播表的自增字段一起使用，不支持 noshard 表。

数据导出导入

导出数据

TDSQL MySQL版 支持通过 mysqldump 导出数据，导出前须设置 net_write_timeout 参数：set global net_write_timeout=28800，命令行有权限限制，请通过 TDSQL MySQL版 控制台 操作。

mysqldump --compact --single-transaction --no-create-info -c db_name table_name -utest -h10.xx.xx.34 -P3336 -ptest123

• db 和 table 名参数根据实际情况选择，如果导出的数据要导入到另外一套 TDSQL MySQL版 环境的话，必须加上 -c 选项。
• 导出账号需拥有 select on *.* 的权限。
• 高版本 mysqldump 导出低版本数据库时，可能发生报错，可设置参数-column-statistics= 0，用于解决此类问题。

导入数据

TDSQL MySQL版 提供专门的导入数据工具，完成 load data outfile 对应数据的导入，该工具的原理是把源文件按照 shardkey 的路由规则，切分成多个文件，然后把每个单独透传到对应的后端数据库。

下载工具

[tdengine@TENCENT64 ~/]$./load_data

format:./load_data mode0/mode1 proxy_host proxy_port user password db_table shardkey_index file field_terminate filed_enclosed

example:./load_data mode1 10.xx.xx.10 3336 test test123 shard.table 1 '/tmp/datafile' ' ' ''

• 源文件必须以 '\n' 作为换行符。
• mode0 只切分源文件，不做数据导入，一般用于调试，正式导入数据使用 mode1。
• shardkey_index 从0开始，如果 shardkey 在第2个字段，则 shardkey_index 为1。

数据库管理语句

状态查询

通过 SQL 可以查看 proxy 的配置以及状态信息，目前支持如下命令：

• /*proxy*/help;
• /*proxy*/show config;
• /*proxy*/show status;

如果使用 MySQL 客户端，需要在使用客户端时增加-c选项，如 mysql -hxxx.xxx.xxx.xxx -Pxxxx -uxxx -pxxx -c。

示例如下：

mysql> /*proxy*/help;

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

| command | description |

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

| show config | show config from conf |

| show status | show proxy status,like route,shardkey and so on |

| set sys_log_level=N | change the sys debug level N should be 0,1,2,3 |

| set inter_log_level=N | change the interface debug level N should be 0,1 |

| set inter_time_open=N | change the interface time debug level N should be 0,1 |

| set sql_log_level=N | change the sql debug level N should be 0,1 |

| set slow_log_level=N | change the slow debug level N should be 0,1 |

| set slow_log_ms=N | change the slow ms |

| set log_clean_time=N | change the log clean days |

| set log_clean_size=N | change the log clean size in GB |

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

10 rows in set (0.00 sec)

mysql> /*proxy*/show config;

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

| config_name | value |

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

| version | V2R120D001 |

| mode | group shard |

| rootdir | /shard_922 |

| sys_log_level | 0 |

| inter_log_level | 0 |

| inter_time_open | 0 |

| sql_log_level | 0 |

| slow_log_level | 0 |

| slow_log_ms | 1000 |

| log_clean_time | 1 |

| log_clean_size | 1 |

| rw_split | 1 |

| ip_pass_through | 0 |

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

14 rows in set (0.00 sec)

mysql> /*proxy*/show status;

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

| status_name | value |

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

| cluster | group_1499858910_79548 |

| set_1499859173_1:ip | 10.49.118.165:5025;10.175.98.109:5025@1@IDC_4@0,10.231.23.241:5025@1@IDC_2@0 |

| set_1499859173_1:hash_range | 0---31 |

| set_1499911640_3:ip | 10.49.118.165:5026;10.175.98.109:5026@1@IDC_4@0,10.231.23.241:5026@1@IDC_2@0 |

| set_1499911640_3:hash_range | 32---63 |

| set | set_1499859173_1,set_1499911640_3 |

同时 proxy 增强了 explain 的返回结果，显示 proxy 修改后的 SQL。

mysql> explain select * from test1;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | info |

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

| 1 | SIMPLE | test1 | ALL | NULL | NULL | NULL | NULL | 16 | | set_2,explain select * from shard.test1 |

| 1 | SIMPLE | test1 | ALL | NULL | NULL | NULL | NULL | 16 | | set_1,explain select * from shard.test1 |

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

2 rows in set (0.03 sec)

透传 SQL

TDSQL MySQL版 实例会对 SQL 进行语法解析，有一定的限制，如果用户想在某个节点（set）中执行 MySQL 支持，但分布式实例不支持的 SQL 时，可以使用透传 SQL 的功能。

• 透传 SQL 时，proxy 不会解析 SQL，如果是往两个 set 进行透传写操作，不会使用分布式事务，特殊情况下会发生不一致问题，因此对于写操作建议一次透传一个 set。
• 为保证透传语法生效，连接 MySQL 时请使用 -c 参数。

MySQL [test]> repair table test.t1;

ERROR 664 (HY000): Proxy ERROR:SQL is too complex, only applicable to noshard table: Shard table do not support repair

MySQL [test]> /*sets:allsets*/repair table test.t1;

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

| Table | Op | Msg_type | Msg_text | info |

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

| test.t1 | repair | status | OK | set_1544429866_3 |

| test.t1 | repair | status | OK | set_1544429718_1 |

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

2 rows in set (0.01 sec)

具体语法：

• sets:set_1,set_2：代表指定某几个 set，set 名字可以通过/*proxy*/show status查询。
• sets:allsets：代表指定全部 set。
• shardkey:10：代表支持透传 SQL 到 shardkey 对应值上的 set。
• shardkey_hash:10：透传到负责 hash 值为10的 set，如果为0，则发送到第一个 set 上。

预处理

SQL 类型的支持：

• PREPARE Syntax
• EXECUTE Syntax

二进制协议的支持：

• COM_STMT_PREPARE
• COM_STMT_EXECUTE

示例：

mysql> select * from test1;

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

| a | b |

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

| 5 | 6 |

| 3 | 4 |

| 1 | 2 |

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

3 rows in set (0.03 sec)

mysql> prepare ff from "select * from test1 where a=?";

Query OK, 0 rows affected (0.00 sec)

Statement prepared

mysql> set @aa=3;

Query OK, 0 rows affected (0.00 sec)

mysql> execute ff using @aa;

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

| a | b |

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

| 3 | 4 |

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

1 row in set (0.06 sec)

二级分区

TDSQL MySQL版 目前支持 Range 和 List 两种格式的二级分区，具体建表语法和 MySQL 分区语法类似。

二级分区语法

一级 Hash，二级 List 分区示例如下：

MySQL [test]> CREATE TABLE customers_1 (

first_name VARCHAR(25) key,

last_name VARCHAR(25),

street_1 VARCHAR(30),

street_2 VARCHAR(30),

city VARCHAR(15),

renewal DATE

) shardkey=first_name

PARTITION BY LIST (city) (

PARTITION pRegion_1 VALUES IN('Beijing', 'Tianjin', 'Shanghai'),

PARTITION pRegion_2 VALUES IN('Chongqing', 'Wulumuqi', 'Dalian'),

PARTITION pRegion_3 VALUES IN('Suzhou', 'Hangzhou', 'Xiamen'),

PARTITION pRegion_4 VALUES IN('Shenzhen', 'Guangzhou', 'Chengdu')

);

一级 Range，二级 List 创建语法如下：

MySQL [test]> CREATE TABLE tb_sub_r_l (

id int(11) NOT NULL,

order_id bigint NOT NULL,

PRIMARY KEY (id,order_id)) 

PARTITION BY list(order_id)

(PARTITION p0 VALUES in (2121122),

PARTITION p1 VALUES in (38937383))

TDSQL_DISTRIBUTED BY RANGE(id) (s1 values less than (100),s2 values less than (1000));

Query OK, 0 rows affected, 1 warning (0.35 sec)

Range 支持类型

• DATE，DATETIME，TIMESTAMP。
• 支持 year、month、day 函数，函数为空和 day 函数一样。
• TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。
• 支持 year、month、day 函数，此时传入的值转换为年月日，然后和分表信息进行对比。

List 支持类型

• DATE、DATETIME、TIMESTAMP。
• 支持年月日函数。
• TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、VARCHAR。

• 建议不要使用 TIMESTAMP 类型作为分区键，因为 TIMESTAMP 受到时区的影响，同时只能使用到2038年。
• 如果分区键是 char 或者 varchar 类型，建议长度不超255。

使用场景和方法建议

建议业务尽量都使用一级分区表。

• 使用前根据业务长期场景合理设计表结构，二级分区适用于表结构创建后长期都不需要 DDL 变更、需要定期进行分区数据清理和裁剪的场景，如日志流水表。
• 合理设计二级分区的粒度，二级分区的粒度建议不要划分得太细，避免产生过多的二级子表。如流水表按月进行二级分区，而不是按天/小时进行分区，避免文件系统上数据文件个数过多。
• 在对二级分区表进行 SQL 查询时，查询条件需要尽量带上一级分区和二级分区的键值，避免执行查询时需要打开很多的数据文件进行搜索。
• 在对二级分区表进行 join 查询时，如果查询条件未能带上一级分区和二级分区的键值，操作性能效率较低，建议不要使用。
• 表的主键或唯一索引需要包含分区键，否则无法保证数据唯一性。