最佳实践产品文档

Size: px

Start display at page:

Download "最佳实践产品文档"

毫蔡
7 years ago
Views:

1 云数据库 TDSQL 最佳实践产品文档

2 版权声明腾讯云版权所有本文档著作权归腾讯云单独所有, 未经腾讯云事先书面许可, 任何主体不得以任何形式复制修改抄袭传播全部或部分本文档内容商标声明及其它腾讯云服务相关的商标均为腾讯云计算 ( 北京 ) 有限责任公司及其关联公司所有本文档涉及的第三方主体的商标, 依法由权利人所有服务声明本文档意在向客户介绍腾讯云全部或部分产品服务的当时的整体概况, 部分产品服务的内容可能有所调整您所购买的腾讯云产品服务的种类服务标准等应由您与腾讯云之间的商业合同约定, 除非双方另有约定, 否则, 腾讯云对本文档内容不做任何明示或模式的承诺或保证第 2 页共 27 页

利人所有服务声明本文档意在向客户介绍腾讯云全部或部分产品服务的当时的整体概况, 部分产品服务的内容可能有所调整您所购买的腾讯云产品服务

3 文档目录文档声明... 2 编程与使用规范... 4 分布式版本编程与使用规范第 3 页共 27 页

4 编程与使用规范如对本规范有任何意见或建议, 欢迎联系腾讯云工作人员或直接发送邮件到 1. 引言 1.1. 文档目的本文档旨在明确 TDSQL 数据库技术与应用的规划与设计安装与部署运行管理与维护等全生命周期各阶段的主要技术标准和指导原则, 便于 TDSQL 数据库应用项目的统一建设和管理, 增加 TDSQL 数据库技术应用的规范性性能保障和可维护性 1.2. 预期读者使用 (D)CDB for TDSQL 的项目相关数据库设计开发管理和运行维护人员 2. 设计规范 2.1. 数据库设计原则 TDSQL 面向的是 OLTP 的应用场景, 并不适用于大多数复杂的 OLAP 反范式设计 : 不必强制满足第三范式, 尽量少使用外键外键用来保护参照完整性, 可在业务端实现适度的冗余设计, 减少多表 join 查询, 更适应 MPP 架构的横向扩展能力直接基于 I/O 和查询进行优化充分考虑业务逻辑和数据分离, 数据库只作为一个保证 ACID 特性的关系数据的持久化存储系统, 尽量避免使用自定义函数存储过程触发器和视图充分考虑数据库整体安全设计, 数据库管理和使用人员权限分离第 4 页共 27 页

1. 文档目的本文档旨在明确 TDSQL 数据库技术与应用的规划与设计安装与部署运行管理与维护等全生命周期各阶段的主要技术标准和指导原则, 便于 TDSQL 数据库应用项目的统一建设和管理, 增加 TDSQL 数据库技术应用的规范性

5 充分考虑具体数据对象的访问频度及性能需求, 结合主机存储等需求, 做好数据库性能设计充分考虑数据增长模型, 决策是否采用 groupshard 模式充分考虑业务数据安全等级, 设计合适的备份和恢复策略 2.2. 数据库模型设计规范基础规范只使用 InnoDB 存储引擎, 避免使用 MyISAM 引擎, 二者对比,InnoDB 具有如下特性完整的 ACID 支持崩溃自检恢复行级锁, 高并发的保证更能发挥多核 CPU 的性能自带缓存池, 更好的利用内存所有表使用统一的字符集, 强烈建议使用 UTF8 或 UTF8MB4 字符集不在数据库中存储图片二进制文件等大数据提前规划好单表规模, 行数和大小控制单行字段总长度, 合理设置 innodb_page_size, 尽量采用 COMPACT 行格式, 避免行溢出数据库对象命名规范数据库对象命名规规范的适用范围为使用 TDSQL 的数据库设计管理开发人员, 对于 TDSQL 产品自带的系统库表等对象不在本规范约束范围内数据库对象命名时整体遵循如下规范 : 对象命名要使用富有意义英文词汇, 除约定俗成外, 避免使用缩写在索引约束等对象命名时会同时包含表名字段名等多个名字, 此时可以使用缩写, 缩写规则和字符数要统一只使用小写字母数字和下划线的组合名字长度不要超过 32 个字符不要使用 SQL 关键字对象名字至少包括 : 对象类型父对象名对象名第 5 页共 27 页

烈建议使用 UTF8 或 UTF8MB4 字符集不在数据库中存储图片二进制文件等大数据提前规划好单表规模, 行数和大小控制单行字段总长度, 合理设置 innodb_page_size, 尽量采用 COMPACT 行格式, 避免行溢出 2.

6 下表列出了不同数据库对象的命名规则 : 数据库对象格式样例说明数据库 (SCHEMA) db_< 数据库名 > db_user 表示这个数据库下都是用户相关的数据表 tbl_< 表名 > tbl_employees 表示存储雇员信息的表主键 pk_< 表名 >_< 字段名 >[_ 字 pk_emlo_id_name 段名 ] 表示 tbl_employees 表的 id 和 name 字段共同组成主键但是在 TDSQL, 这里的主键名其实没有意义, 系统会忽略这个名字, 然后统一命名为 :PRIMARY 唯一索引 uidx_< 表名 >_< 字段名 >[_ uidx_empl_name_age_1 表示 tbl_employees 表的 na 字段名 ]_< 编号 > me 和 age 字段创建的第一个唯一索引普通索引 idx_< 表名缩写 >_< 列名缩 idx_empl_name_age_1 表示 tbl_employees 表的 na 写 >[_ 列名缩写 ]_< 编号 > me 和 age 字段创建的第一个普通索引外键 fk_< 表名 >_< 关联表名 >_< fk_empl_user_uid_id 字段名 >[_ 字段名 ] _< 关联字段名 >[_ 关联字段名 ] 表示 tbl_employees 表的 ui d 字段外键约束到 tbl_user 的 id 字段视图 v_< 视图名 > v_female - 存储过程 sp_< 存储过程名 > sp_add_empl - 自定义函数 f_< 函数名 > f_count_empl - 触发器 trg_< 触发器名 > trg_update_empl - 临时表 tmp_< 表名 > tmp_latecomer - 表格实例配置 TDSQL 实例在申请后需要初始化, 初始化过程中有 2 个重要指标需要指定 : 字符集 : 指定库表默认的字符集第 6 页共 27 页

uidx_empl_name_age_1 表示 tbl_employees 表的 na 字段名 ]_< 编号 > me 和 age 字段创建的第一个唯一索引普通索引 idx_< 表名缩写 >_< 列名缩 idx_empl_name_age_1 表示 tbl_employees 表的 na 写 >[_ 列名缩写 ]_< 编号 > me

7 -innodb_page_size:innodb 表文件存储中页的大小页是 InnoDB 引擎在文件中存储的最小单位, 原生 Mysql 的默认值为 16K, 该值的配置从以下几点考虑实例中主要表的单行数据大小 InnoDB 表的 ROW_FORMAT 配置是否使用 SSD 盘在初始化后, 还有些参数可以设置 : 根据业务需要, 可通过 SQL MODE 设置 STRICT_ALL_TABLES 或 STRICT_TRANS_TABLES 标志位开启 ST RICT 模式, 严格拒绝不符合字段类型定义的数据写入, 两个标志位的区别 : STRICT_ALL_TABLES: 对于不支持事务的存储引擎, 如果同时更新多行数据, 如果不符合定义的数据不是第一行, 则会导致该行前面的行数据更新, 该行及后续行数据不更新的情况 STRICT_TRANS_TABLES: 对于不支持事务的存储引擎, 如果不符合定义的数据是第一行, 则拒绝请求, 否则按照普通模式运行 TDSQL 默认会启动事务自动提交 (AUTOCOMMIT), 也建议保持开启 TDSQL 默认的事务隔离级别是 REPEATABLE-READ, 请根据需要决定是否调整为 READ- COMMITED(ORACLE 的默认级别 ) 数据库 TDSQL 中数据库的概念和 ORACLE 不同, 可以认为是一个 SCHEMA, 类似于一个表的文件夹, 方便 DBA 的分类管理创建数据库时, 除了指定数据库名, 强烈建议明确指定数据库默认的字符集参数, 例如 : CREATE DATABASE `db_user` CHARSET ='utf8' COLLATE='utf8_bin' 表表参数设置 InnoDB 表的默认 ROW_FORMAT 为 COMPACT, 请根据情况指定合适的值 : COMPACT: 在不产生行溢出的情况下, 一行数据存储在一个数据页中, 数据读取效率最好 DYNAMIC: 至少存储在 2 个页中, 数据页只存储溢出页的指针, 数据存在溢出页中, 这种结构的索引存储最集中, 索引访问效率较高 REDUNDANT: 老的格式, 不要使用第 7 页共 27 页

于不支持事务的存储引擎, 如果同时更新多行数据, 如果不符合定义的数据不是第一行, 则会导致该行前面的行数据更新, 该行及后续行数据不更新的情况 STRICT_TRANS_TABLES: 对于不支持事务的存储引擎, 如果不符合定义的数据是

8 COMPRESSED: 会对数据进行压缩后存储, 最高的空间利用率, 不过会增加 CPU 和内存消耗, 增加系统响应时间, 降低吞吐量, 谨慎使用强烈建议明确指定表默认的字符集参数文件存放路径等物理相关的参数是不允许指定的主外键约束设计 InnoDB 表必须指定主键建议不使用具有实际意义的字段做主键, 如果一定要使用, 确保该字段具有现实世界的唯一且不变性 ( 例如 : 银行交易流水号 ), 以尽量避免主键修改减少外键的使用, 特别是在 groupshard 模式下, 考虑到数据库的自动分库分表, 建议不使用外键组成主键的字段总长度越短, 效率越高整数类型的字段做主键最合适如果存在多个唯一键, 考虑最常用的唯一键作为主键建议使用自增字段作为主键字段属性尽量加上 NOT NULL 约束以及默认值, 使用 NULL 值会导致如下副作用 : 含义不明, 对很多运算符不管用, 增加复杂度例如 a!=5 是无法匹配到 a 为 NULL 的行很难进行查询优化含 NULL 的复合索引失效不要使用 CHECK 约束,TDSQL(Mysql) 虽然不报错, 但会忽略, 可以使用 ENUM 类型代替字段类型设计整数类型请根据实际存储值的范围选择合适的类型无符号整数类型请添加 UNSIGNED 关键字字符串类型请根据实际存储值的情况确定选择定长类型还是变长类型根据实际存储值的情况, 尽量减少字符串类型的长度尽量避免使用 TEXT/BLOB 类型, 不要在数据库中存储图片二进制文件等数据非整数尽量使用精确的 DECIMAL, 避免使用浮点数下表是推荐使用的主要数据类型取值范围和存储需求 : 类型值范围存储需求 TINYINT[(M)] -128 到 127 或 0 到个字节 SMALLINT[(M)] 到或 0 到个字节 INT[(M)] 到或 4 个字节第 8 页共 27 页

groupshard 模式下, 考虑到数据库的自动分库分表, 建议不使用外键组成主键的字段总长度越短, 效率越高整数类型的字段做主键最合适如果存在多个唯一键, 考虑最常用的唯一键作为主键建议使用自增字段作为主键字段属性尽量

9 类型值范围存储需求 0 到 BIGINT[(M)] 到个字节或 0 到 DECIMAL[(M[,D])] 整数最大位数 (M) 为 65, 小数位数变长最大 (D) 为 30 DATE ' ' 到 ' ' 3 个字节 TIME[(<microsecond precision>)] '-838:59: ' 到 '838:59: 个字节 99999' DATETIME[(microsecond precision)] TIMESTAMP[(<microsecond precision)] ' :00: ' 到 ' :59: ' ' :00:01' (UTC) 到 ' :14:07' (UTC) 8 个字节 4 个字节 CHAR[(M)] 0<M<=255 M 的整数倍, 和字符集设置有关 VARCHAR[(M)] 0<M<65532/N N 的取值和实际字符集设置有关 TEXT[(M)] 0<M<65535/N N 的取值和实际字符集设置有关表格索引低选择性的列不加索引, 如 : 性别在组合索引中, 常用的字段放在前面, 选择性高的字段放在前面需要经常排序的字段可加到索引中, 字段顺序和最常用的排序一致对于较长的字符串类型字段, 建议使用前缀索引合理合并索引, 避免冗余, 例如 (a,b) 和 (a), 应该去掉 (a) 单张表的索引控制在 5 个以内单个索引的字段不要超过 5 个 TDSQL 的 InnoDB 引擎支持全文索引, 但目前限于英文第 9 页共 27 页

9 5 个字节 99999' DATETIME[(microsecond precision)] TIMESTAMP[(<microsecond precision)] '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.

10 分页设计分页是应用中最常见的访问模型, 我们用下面几种分页方式的实际测试情况来看看如何设计合理的分页模型 : /* * id 是表 post 的主键 */ MySQL> SELECT sql_no_cache * FROM post LIMIT 20000,10; 10 row in set (0.13 sec) MySQL> SELECT sql_no_cache * FROM post LIMIT 80000,10; 10 rows in set (0.58 sec) MySQL> SELECT sql_no_cache id FROM post LIMIT 80000,10; 10 rows in set (0.02 sec) MySQL> SELECT sql_no_cache * FROM post WHERE id>= LIMIT 10; 10 rows in set (0.01 sec) MySQL> SELECT * FROM post WHERE id >= ( SELECT sql_no_cache id FROM post LIMIT ,1 ) LIMIT 10 ; 10 rows in set (0.02 sec) 上面的结果很明显, 要尽量避免直接使用 LIMIT m,n 这种分页方式 2.3. 数据库安全设计数据库用户安全设计原则数据库用户权限授权按照最小分配原则数据库用户要分为管理应用维护备份四类用户数据库用户权限设计规范除 TDSQL 和核心维护人员外, 其他用户不能拥有 SUPER 权限账号避免使用简单密码第 10 页共 27 页

(0.13 sec) MySQL> SELECT sql_no_cache * FROM post LIMIT 80000,10; 10 rows in set (0.58 sec) MySQL> SELECT sql_no_cache id FROM post LIMIT 80000,10; 10 rows in set (0.

11 TDSQL 的权限支持到字段级别, 权限的主体对象要按最小原则控制开发测试和生产环境中用户权限设置要保持一致严格禁止在数据库中存储任何形式的密码明文开发规范 SQL 编码规范每行不要超过 80 个字符, 超过就换行缩进使用两个空格来缩进代码关键词要大写, 比如 :SELECT 常数符号要大写, 比如 :NULL 合理使用注释建表语句前要对表的用途进行详细注释每个字段后使用 COMMNET 子句添加字段的注释建表语句最后面使用 COMMENT 子句添加对表的一句话注释去掉多余的扩容, 例如 : ((a AND b) AND c OR (((a AND b) AND (c AND d)))) 应该优化为 : (a AND b AND c) OR (a AND b AND c AND d) 去掉重叠的条件, 例如 : (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) 第 11 页共 27 页

途进行详细注释每个字段后使用 COMMNET 子句添加字段的注释建表语句最后面使用 COMMENT 子句添加对表的一句话注释去掉多余的扩容, 例如 : ((a AND b) AND c OR (((a AND

12 应该优化为 : B=5 OR B=6 3.2.SQL 语句规范索引和分区合理使用 USE INDEX 和 IGNORE INDEX 进行索引的选择查询条件尽量使用索引注意字段类型, 避免类型转换类型转换除了会增加 CPU 消耗, 如果转换失败, 还会导致索引失效对于复合索引, 查询条件必须包含所有前缀字段才管用, 例如索引 (a,b,c), 查询条件必须是 a 或者 a b 或者 a b c 才能使用到索引 SELECT 列和 WHERE 条件尽量不要让数据库做算术运算, 交给应用层来做, 例如 : SELECT a FROM tbl WHERE id*10=100; 尽量不要直接 SELECT *, 直接列出需要查询的字段尽量使用 UNION ALL, 而不是 UNION UNION 会做去重和排序 WHERE 子句使用的原则 : 尽量使用索引, 尽量简单, 尽量匹配更少的行 WHERE 子句中多使用等值操作符, 少使用非等值操作符, 等等值操作符通常会导致索引失效 (Mysql 不支持范围索引 ) 就算有索引,WHERE 子句匹配的行数不要超过表的 30%, 否则效率还是很低,InnoDB 引擎还很有可能直接放弃使用索引, 采用全表扫描 LIKE 子句的条件中,% 不要是第一个字符, 尽量靠后更复杂的需求考虑使用全文索引 OR 条件大于 3 个 : 不同字段的, 使用 UNION ALL 代替相同字段的, 用 IN 代替第 12 页共 27 页

才管用, 例如索引 (a,b,c), 查询条件必须是 a 或者 a b 或者 a b c 才能使用到索引 3.2.

13 尽量使用 WHERE 子句代替 HAVING 子句, 例如 : SELECT id,count(*) FROM tbl GROUP BY id HAVING age>=30; 应该替换为 : SELECT id,count(*) FROM tbl WHERE age>30 GROUP BY id; 一个表的 ORDER BY 和 GROUP BY 的组合都不应该超过 3 种, 否则从业务逻辑考虑进行优化或者分成多张表如果不需要排序,GROUP BY 子句写成 :GROUP BY NULL WHERE 子句尽量使用主键 InnoDB 表尽量避免使用类似 COUNT(*) 的全表扫描查询, 从设计上考虑另外用一张表存这个计数值尽量避免使用子查询 DML 语句 UPDATE DELETE 等语句尽量带上 WHERE 子句, 且使用索引字段, 最好使用主键使用 INSERT... ON DUPLICATE KEY update (INSERT IGNORE) 来避免不必要的查询 INSERT UPDATE DELETE 语句中不要使用不确定值的函数, 例如 :RAND() 和 NOW() 多条 INSERT 语句要合并成一条批量提交, 一次不要超过 500 行数据禁止在 UPDATE 语句中, 讲 "," 写成 AND, 非常危险, 例如 : UPDATE tbl SET fid=fid+1000, gid=gid+1000 WHERE id > 2; 如果写成 UPDATE tbl SET fid=fid+1000 AND gid=gid+1000 WHERE id > 2; 第 13 页共 27 页

计数值尽量避免使用子查询 3.2.3.DML 语句 UPDATE DELETE 等语句尽量带上 WHERE 子句, 且使用索引字段, 最好使用主键使用 INSERT.

14 此时 fid+1000 AND gid=gid+1000 将作为值赋给 fid, 且没有 Warning 删除一张表用 TRUNCATE, 而不是 DELETE 多表 JOIN 多表 join 时, 各表的顺序按照各表在 WHERE 子句条件下返回的行数从小到大排列, 行数最小的在最左边避免大表间的 JOIN, 一般表的记录数不超过 10W 条的情况下, 才建议使用 JOIN 其它合理使用 PREPARE Statement, 提供性能还能防 SQL 注入拆分负载 SQL 为多条 SQL, 避免大事务如果要对表进行 DDL 操作, 尽量将对一张表的 DDL 在一条 SQL 语句完成, 例如 : 要给表 t 增加一个字段 b, 然后给已有字段 a 建立索引, 可以用下面一条语句完成 : ALTER TABLE t ADD COLUMN b VARCHAR(10), ADD INDEX idx_a(a); 4. 数据库管理规范 4.1. 基本要求 TDSQL 自带的安装程序会在安装前进行系统环境检查, 包括以下项目 : 检查项操作系统版本期望结果 Linux 系列文件句柄大于时间同步 NTP 配置正确第 14 页共 27 页

其它合理使用 PREPARE Statement, 提供性能还能防 SQL 注入拆分负载 SQL 为多条 SQL, 避免大事务如果要对表进行 DDL 操作, 尽量将对一张表的 DDL 在一条 SQL 语句完成, 例如 : 要给表 t 增加一个字段 b, 然后给已

15 检查项用户安装目录数据目录期望结果检查安装指定的用户是否存在检查安装目录是否存在且有写权限检查指定的数据目录是否存在且有写权限表格 NOSHARD 与 GROUP SHARD 的选择当满足以下条件时, 可以考虑使用 GROUP SHARD: 表中数据会持续增长, 在可预见的时间内, 超过单机最大存储量表的读写量很大, 超过了单机最大吞吐量充分考虑了分库分表会导致的在表设计和使用上的各种限制关于分布式版本 (GROUP SHARD 版本 ) 在使用上的限制, 请参考 : 分布式版本开发指南 4.3. 主从配置与容灾目前云数据库已支持下列能力 TDSQL 支持一主多从配置, 且能在主机宕机时自动选举一台从机为主机从机个数可以自定义, 且多个从机提供负载均衡的只读能力, 所以增加从机可以扩容读能力 ( 从机的数据有延迟 ) TDSQL 支持强同步和异步两种主从同步方式 : 强同步能保证主机宕机不丢数据, 因此建议至少配置 2 个从机因为如果只有 1 个从机, 当主机宕机, 为保证数据不丢失, 剩下 1 个从机只能提供只读服务强同步性能会低于异步, 损失情况视主从机之间的网络质量而定一般建议只在同机房, 或者同城多个机房之间使用强同步 TDSQL 还支持将 2 个逻辑实例设置为主从, 一般用于异地数据容灾 4.4. 备份与恢复第 15 页共 27 页

上的各种限制关于分布式版本 (GROUP SHARD 版本 ) 在使用上的限制, 请参考 : 分布式版本开发指南 4.3.

16 目前云数据库已支持下列能力为 TDSQL 集群配置一个 HDFS 集群, 用来存储冷备和 BINLOG 备份 TDSQL 支持配置定期冷备到 HDFS BINLOG 会实时备份到 HDFS 利用冷备和 BINLOG 备份,TDSQL 支持指定时间点的回档 4.5. 用户和权限 TDSQL 的使用者应该分为集群管理者数据库实例管理者和数据库使用者 : 集群管理者主要通过运维管理平台完成以下工作 : 管理整个集群的资源池, 负责资源的上下架和合理的分配为实例管理者分配数据库实例协助实例管理者进行实例管理负责管理所有实例的冷备和 BINLOG 备份数据关注集群和实例的运行指标, 保证集群和各实例的正常运行数据库实例管理者主要通过数据库管理平台完成以下工作 : 提交新建扩容删除实例的申请负责维护实例的个性化参数负责管理数据库实例的账号和密码关注实例的运行指标, 进行数据库性能管理通过查看慢查询分析错误日志慢查询日志等信息, 进行数据库的性能优化分析数据库使用者通过 SQL 客户端连接数据库, 提交 SQL 语句, 存取数据 4.6. 日志管理 TDSQL 集群的日志主要包括 : 集群中各个组件的运行日志实例的网关日志第 16 页共 27 页

例管理者进行实例管理负责管理所有实例的冷备和 BINLOG 备份数据关注集群和实例的运行指标, 保证集群和各实例的正常运行数据库实例管理者主要通过数据库管理平台完成以下工作 : 提交新建扩容删除实例的申请负责维护实例

17 实例的数据库运行日志慢查询日志错误日志实例的数据库 BINLOG 文件 HDFS 中存储的各种日志备份所有日志都根据需要配置合理的清理策略 5. 性能优化建议 5.1. 性能优化原则数据库性能优化通常涵盖方法论体系特性及工具 2 个方面方法论体系包含 3 部分内容 : 性能规划 : 深入了解应用与数据库的交互特征, 确立良好的设计开发测试迭代过程, 上线前消除模型上的性能瓶颈实例调优 : 建立性能基准, 对比调节数据库操作系统存储网络等的配置, 主动监控消除瓶颈 SQL 调优 : 书写高效 SQL, 优化相关数据库对象, 充分借助优化器, 确定最佳执行计划 5.2. 性能优化步骤首先执行下面的初始检查 : 获取直接用户的使用反馈, 确定性能目标和范围获取性能表现好与坏时的操作系统数据库应用统计信息对数据库做一次全面健康检查定期收集数据库统计信息根据收集的信息, 以及对应用特性的了解, 构建性能概念模型, 明确性能瓶颈所在, 以及导致性能的根本原因提出一系列针对的优化措施, 并根据它们对性能改善的重要程度排序, 然后逐一加以实施不要一次执行所有的优化措施, 必须逐条尝试, 逐步对比通过获取直接用户的反馈验证调节是否已经产生预期的效果, 否则, 需要重新提炼性能概念模型, 直到对应用特性了解进一步准确重复上述, 直到性能达到目标或由于客观约束无法进一步优化 5.3. 常用优化技巧第 17 页共 27 页

性能基准, 对比调节数据库操作系统存储网络等的配置, 主动监控消除瓶颈 SQL 调优 : 书写高效 SQL, 优化相关数据库对象, 充分借助优化器, 确定最佳执行计划 5.2.

18 查看实例 long_query 和 long_query_rate 指标, 分析慢查询出现频率及规律使用慢查询分析工具, 从出现频率最高耗时最长的 SQL 语句开始分析, 通过优化 SQL 语句, 添加索引等方式解决查看实例 mem_hit_rate 和 mem_available 指标, 分析 innodb 的缓存池是否足够, 内存是否是瓶颈查看 cpu_usage_rate, 结合慢查询分析 CPU 消耗是否合理,CPU 是否是瓶颈调整 innodb_page_size 参数, 对比性能测试, 找到最合适的配置对常用语句使用 EXPLAIN 进行查询分析, 找出潜在的设计问题根据业务场景设计合适的用例对不同规格实例进行性能测试第 18 页共 27 页

瓶颈查看 cpu_usage_rate, 结合慢查询分析 CPU 消耗是否合理,CPU 是否是瓶颈调整 innodb_page_size 参数, 对比性能测试, 找到最合适的配

19 分布式版本编程与使用规范分布式版本编程与使用规范如对本规范有任何意见或建议, 欢迎联系腾讯云工作人员或直接发送邮件到本文未特定知名的情况下,TDSQL 均指代分布式云数据库 DCDB for TDSQL 1. 概述 : 1.1 分布式简介 DCDB for TDSQL 是部署在腾讯云公有云上的一种兼容 MySQL 协议和语法, 支持自动水平拆分的分布式数据库分布式数据库即业务获取是完整的逻辑库表, 后端却将库表均匀的拆分到多个物理分片节点目前,DCDB for TDSQL 默认部署主备架构且提供了容灾备份恢复监控迁移等方面的全套解决方案, 适用于 TB 或 PB 级的海量数据库场景分布式云数据库 DCDB for TDSQL 采用的水平切分的分布式方案, 即根据表中的数据的逻辑关系, 将同一个表中的数据按照某种条件拆分到多台数据库 ( 主机 ) 上面, 返种切分称之为数据的水平 ( 横向 ) 切分 1.2 名词解释 DCDB for TDSQL: 是 TDSQL 的分布式版本 ( 又称为 groupsharding), 本文因不涉及普通版本, 因此后续简称 TDSQL 垂直切分 : 只按照业务逻辑将表分类, 分布到不同的数据库上面, 这就将一个单一数据库的压力分担到不同的数据库上面水平切分 : 不是将表在做分类, 而是按照某个字段的某种规则来分散到多个库中, 每个库叫做一个分片, 一个分片仅包括一部分数据第 19 页共 27 页

1 分布式简介 DCDB for TDSQL 是部署在腾讯云公有云上的一种兼容 MySQL 协议和语法, 支持自动水平拆分的分布式数据库分布式数据库即业务获取是完整的逻辑库表, 后端却将库表均匀的拆分到多个物理分片节点目前,DCDB for

20 分片规则 : 即拆分数据的规则, 关系型数据库是一个二维的模型, 所以一般有两种典型的方案进行拆分 : 采用某个有规律的字段进行人工划分 : 如日期 ( 如 2015 年一个分片,2016 年一个分片 ) 用户身份证号码对特定字段进行 hash: 将根据特定范围字段分布到不同库中 ; 这个特定字段就叫做 ShardKey 实例 : 多个逻辑上统一的分片组成一个逻辑实例分片 : 由数据库引擎组成的物理逻辑上的实例, 由一个主节点 (Master) 若干备节点 (Slave_n) 若干异地备份节点 (Watcher_m) 节点 : 承载分片的物理设备当然, 节点可以死物理机, 也可以是虚拟机, 也可能是一个小集群逻辑表 : 水平切分通常只是物理上的切分, 在逻辑层面, 切分后的数据库或数据库表仍然是一个完整的数据库表 proxy :TDSQL 通过 Tproxy 实现自动分库分表逻辑, 管理底层的多个物理数据库实例, 对客户端提供唯一的兼容 mys ql 数据库服务端口 shard: 一个物理的数据库实例, 用户看到的一个逻辑实例由多个物理实例构成 2. 使用规范 2.1 数据库连接分布式云数据库 TDSQL 仍然提供了唯一的 IP, 端口供用户访问和使用, 例如 : mysql -h P3306 -utest12 -ptestpassword 2.2 不兼容概述第 20 页共 27 页

点 (Watcher_m) 节点 : 承载分片的物理设备当然, 节点可以死物理机, 也可以是虚拟机, 也可能是一个小集群逻辑表 : 水平切分通常只是物理上的切分, 在逻辑层面, 切分后的数据库或数据库表仍然是一个完整的数据库表 proxy

21 分布式云数据库 TDSQL 与 MySQL ( 金融级 ) 云数据库 CDB for TDSQL 存在下列兼容点建表时, 需指明 shardkey( 即分区字段 ), 代码示例如下 : create table test(a int, b int, ) shardkey=b 跨节点的 join transaction 批量插入等如果拆分到两个不同的分片, 将不支持 ; 这时候仅支持 shardkey 取值在一样的情况下的批量操作视图存储过程触发器自建分区不支持 MySQL 以下的版本 SSL 压缩协议兼容性 : 兼容性说明建表建表的时候必须在最后面指定 shardkey 的值, 该值为表中的一个字段名字, 会用于 SQL 的路由选择 : mysql> create table test1 ( a int, b int, c char(20) ); ERROR 1005 (07000): Proxy Warning - sql is not legal,tokenizer_gram went wrong mysql> create table test1 ( a int, b int, c char(20) ) shardkey=a; Query OK, 0 rows affected (1.56 sec) shardkey 字段有如下限定要求 : 1. 如存在主键或者唯一索引, 则 shardkey 字段必须是主键或者唯一索引的一部分 2. 如包含多个唯一索引 ( 包括主键 ), 则这些索引必须有交集字段, 且此字段必须为 shardkey 3. shardkey 字段的类型必须是 int,bigint,smallint/char/varchar 4. shardkey 字段的值不应该有中文, 网关不会转换字符集, 所以不同字符集可能会路由到不同的分区 5. 不要 update shardkey 字段的值 6. shardkey=a 放在 sql 的最后面第 21 页共 27 页

22 2.2.2 普通 sql select: 建议您带上 shardkey 字段, 若不带上也可以使用, 但此时书记先需要全表扫描, 然后网关进行结果集聚合, 对性能影响很大 : mysql> select * from test1 where a=2; a b c record2 2 4 record rows in set (0.00 sec) insert/replace: 字段必须包含 shardkey, 否则会拒绝执行该 sql, 因为 proxy 无法确认应该插入到哪一个分片 : mysql> insert into test1 (b,c) values(4,"record3"); ERROR 1105 (07000): Proxy Warning - sql have no shardkey mysql> insert into test1 (a,c) values(4,"record3"); Query OK, 1 row affected (0.01 sec) delete/update: 为了安全考虑, 我们要求执行该类 sql 的时候必须带有 where 条件, 否则拒绝执行该 sql 命令 : mysql> delete from test1; ERROR 1005 (07000): Proxy Warning - sql is not legal,tokenizer_gram went wrong mysql> delete from test1 where a=1; Query OK, 1 row affected (0.01 sec) 第 22 页共 27 页

23 2.2.3 join join: 当前 TDSQL 只支持单个 shard 内的 join 操作, 单个 shard 意味着在一个事务内的所有 sql 必须操作同一个 s hard, 因此必须指定 shardkey 字段 mysql> create table test1 ( a int, b int, c char(20) ) shardkey=a; Query OK, 0 rows affected (1.56 sec) mysql> create table test2 ( a int, d int, e char(20) ) shardkey=a; Query OK, 0 rows affected (1.46 sec) mysql> insert into test1 (a,b,c) values(1,2,"record1"),(2,3,"record2"); Query OK, 2 rows affected (0.02 sec) mysql> insert into test2 (a,d,e) values(1,3,"test2_record1"),(2,3, "test2_record2"); Query OK, 2 rows affected (0.02 sec) mysql> select * from test1 join test2 on test1.a=test2.a; ERROR 1105 (07000): Proxy Warning - join shardkey error mysql> select * from test1 join test2 on test1.a=test2.a where test1.a=1; a b c a d e record1 1 3 test2_record row in set (0.00 sec) 注意 :mysql> select * from test1 join test2 on test1.a=test2.a; 语句在后续版本中会支持, 但有如下前置条件 : 1. 必须是 inner join 2. inner join 的 where 条件必须是 2 张表的 shardkey 字段相等第 23 页共 27 页

24 2.2.4 事务 TDSQL 支持单个 shard 内的事务, 单个 shard 意味着在一个事务内的所有 sql 必须操作同一个 shard 执行 mysql> select * from test1; a b c record2 1 2 record rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test1 (a,b,c) values(2,4,"record3"); Query OK, 1 row affected (0.00 sec) mysql> insert into test1 (a,b,c) values(1,4,"record3"); ERROR 1105 (07000): Proxy ERROR - In transaction,this sql use a diffent backend mysql> select * from test1; ERROR 1105 (07000 ): Proxy ERROR - In transaction,this sql use more than one backend mysql> rollback; Query OK, 0 rows affected (0.01 sec) 注意 : 后续版本将支持部分简单 DML 语句第 24 页共 27 页

25 2.2.5 自增字段 TDSQL 支持一定意义上的自增字段, 保证某个字段全局唯一, 但是不保证单调递增, 具体使用方法如下 : 创建 : create table auto_inc ( a int,b int,c int auto_increment,d int) shardkey=d; 插入 : mysql> insert into shard.auto_inc ( a,b,d,c) values(1,2,3,0),(1,2,3,0); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from shard.auto_inc; a b c d rows in set (0.01 sec) 值得说明的是, 如果在 proxy 调度切换重启等过程中, 自增长字段中间会有空洞, 例如 : 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 第 25 页共 27 页

26 rows in set (0.01 sec) 更改当前值 alter table auto auto_increment=100 当前版本不支持自增字段作为 shardkey, 后续版本计划支持 SQL 命令限制 TDSQL 目前支持的 sql 命令如下 : 1. delele,update,insert,replace,select 2. alter,create,drop,truncate 3. show,describe(desc,explain),help 4. start,begin,commit,rollback,savepoint 5. set 注意 : 对于一般的查询数据库状态信息的 sql,proxy 会发往默认 shard, 这样查询统计信息的话, 看到的结果是单个 shard 的信息用户权限相关限制暂不支持使用 sql 命令通过 proxy 进行用户权限相关的设置, 请至 " 腾讯云管理中心 > 云数据库 >TDSQL> 管理第 26 页共 27 页

27 Powered by TCPDF ( 最佳实践产品文档进行操作其它不支持的 MySQL 特性 1. 跨节点的 join transaction( 注 : 不跨节点, 即同一个 shardkey 下的数据操作,join 和 transaction 是可以支持的 ) 2. 视图存储过程触发器 3. 自建分区 4. 批量数据导入 : 不支持 load data local infile 之类的 sql, 需要转成 insert 语句, 类似 insert into values(xxx,xxx),(xxx,xxx); 5. 子查询,having 子句聚合函数限制 1. 如果需要 distinct 后再做聚合, 那么 where 条件必须带 shardkey: 如 :select count(distinct a),sum(distinct a),avg(distinct a) from table where sk=\*\* 2. 对于 distinct order by group by 如果后面跟函数的话, 该函数必须出现在 select 的字段中, 而且必须定义别名, 对应 distinct order by group by 后使用别名 : 如 :select concat(...) as substr from table where... order by substr 3. group by 的字段必须在 select 的字段中 : 如 :select count(a),b from test group by b, 其中 select 中必须包含字段 b 第 27 页共 27 页

说明为了反映教运行的基本状态, 为校和院制定相关政策和进行教建设与改革提供据依据, 校从程资源 ( 开类别开量规模 ) 教师结构程考核等维度, 对 2015 年春季期教运行基

说明为了反映教运行的基本状态, 为校和院制定相关政策和进行教建设与改革提供据依据, 校从程资源 ( 开类别开量规模 ) 教师结构程考核等维度, 对 2015 年春季期教运行基内部资料东北师范大教运行基本状态据报告 2015 年春季期教务处 2015 年 10 月 27 日说明为了反映教运行的基本状态, 为校和院制定相关政策和进行教建设与改革提供据依据, 校从程资源 ( 开类别开量规模 ) 教师结构程考核等维度,