编程与使用规范

最近更新时间:2019-08-07 16:23:44

1.引言

1.1.文档目的

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

1.2.预期读者

使用MariaDB(TDSQL)的项目相关数据库设计、开发管理和运行维护人员。

2.设计规范

2.1.数据库设计原则

  • MariaDB(TDSQL)面向的是OLTP的应用场景,并不适用于大多数复杂的OLAP
  • 反范式设计:
    • 不必强制满足第三范式,尽量少使用外键
      • 外键用来保护参照完整性,可在业务端实现
      • 适度的冗余设计,减少多表join查询,更适应MPP架构的横向扩展能力
      • 直接基于I/O和查询进行优化
  • 充分考虑业务逻辑和数据分离,数据库只作为一个保证ACID特性的关系数据的持久化存储系统,尽量避免使用自定义函数、存储过程、触发器和视图
  • 充分考虑数据库整体安全设计,数据库管理和使用人员权限分离
  • 充分考虑具体数据对象的访问频度及性能需求,结合主机、存储等需求,做好数据库性能设计
  • 充分考虑数据增长模型,决策是否采用“分布式(水平拆分)”模式
  • 充分考虑业务数据安全等级,设计合适的备份和恢复策略

2.2.数据库模型设计规范

2.2.1.基础规范

  • 只使用InnoDB存储引擎,避免使用MyISAM引擎,二者对比,InnoDB具有如下特性
    • 完整的ACID支持
    • 崩溃自检恢复
    • 行级锁,高并发的保证
    • 更能发挥多核CPU的性能
    • 自带缓存池,更好的利用内存
  • 所有表使用统一的字符集,强烈建议使用UTF8或UTF8MB4字符集
  • 不在数据库中存储图片、二进制文件等大数据
  • 提前规划好单表规模,行数和大小
  • 控制单行字段总长度,合理设置innodb_page_size,尽量采用COMPACT行格式,避免行溢出。

2.2.2.数据库对象命名规范

数据库对象命名规规范的适用范围为使用MariaDB(TDSQL)的数据库设计、管理、开发人员,对于MariaDB(TDSQL)产品自带的系统库表等对象不在本规范约束范围内。

数据库对象命名时整体遵循如下规范:

  • 对象命名要使用富有意义英文词汇,除约定俗成外,避免使用缩写
  • 在索引约束等对象命名时会同时包含表名、字段名等多个名字,此时可以使用缩写,缩写规则和字符数要统一
  • 只使用小写字母、数字和下划线的组合
  • 名字长度不要超过32个字符
  • 不要使用SQL关键字
  • 对象名字至少包括:对象类型、父对象名、对象名

下表列出了不同数据库对象的命名规则:

数据库对象 格式 样例 说明
数据库(SCHEMA) db_<数据库名> db_user 表示这个数据库下都是用户相关的数据
tbl_<表名> tbl_employees 表示存储雇员信息的表
主键 pk_<表名>_<字段名>[_字段名] pk_emlo_id_name 表示tbl_employees表的id和name字段共同组成主键。但是在MariaDB(TDSQL),这里的主键名其实没有意义,系统会忽略这个名字,然后统一命名为:PRIMARY
唯一索引 uidx_<表名>_<字段名>[_字段名]_<编号> uidx_empl_name_age_1 表示tbl_employees表的name和age字段创建的第一个唯一索引
普通索引 idx_<表名缩写>_<列名缩写>[_列名缩写]_<编号> idx_empl_name_age_1 表示tbl_employees表的name和age字段创建的第一个普通索引
外键 fk_<表名>_<关联表名>_<字段名>[_字段名] _<关联字段名>[_关联字段名] fk_empl_user_uid_id 表示tbl_employees表的uid字段外键约束到tbl_user的id字段
视图 v_<视图名> v_female -
存储过程 sp_<存储过程名> sp_add_empl -
自定义函数 f_<函数名> f_count_empl -
触发器 trg_<触发器名> trg_update_empl -
临时表 tmp_<表名> tmp_latecomer -

2.2.3.实例配置

MariaDB(TDSQL)实例在申请后需要初始化,初始化过程中有2个重要指标需要指定:

  • 字符集:指定库表默认的字符集
    -innodb_page_size:InnoDB表文件存储中页的大小。页是InnoDB引擎在文件中存储的最小单位,原生Mysql的默认值为16K,该值的配置从以下几点考虑
    • 实例中主要表的单行数据大小
    • InnoDB表的ROW_FORMAT配置
    • 是否使用SSD盘

在初始化后,还有些参数可以设置:

  • 根据业务需要,可通过SQL MODE设置STRICT_ALL_TABLESSTRICT_TRANS_TABLES标志位开启STRICT模式,严格拒绝不符合字段类型定义的数据写入,两个标志位的区别:
    • STRICT_ALL_TABLES:对于不支持事务的存储引擎,如果同时更新多行数据,如果不符合定义的数据不是第一行,则会导致该行前面的行数据更新,该行及后续行数据不更新的情况
    • STRICT_TRANS_TABLES:对于不支持事务的存储引擎,如果不符合定义的数据是第一行,则拒绝请求,否则按照普通模式运行
  • MariaDB(TDSQL)默认会启动事务自动提交(AUTOCOMMIT),也建议保持开启
  • MariaDB(TDSQL)默认的事务隔离级别是REPEATABLE-READ,请根据需要决定是否调整为READ-COMMITED(ORACLE的默认级别)

2.2.4.数据库

MariaDB(TDSQL)中数据库的概念和ORACLE不同,可以认为是一个SCHEMA,类似于一个表的文件夹,方便DBA的分类管理。创建数据库时,除了指定数据库名,强烈建议明确指定数据库默认的字符集参数,例如:

 CREATE DATABASE `db_user` CHARSET ='utf8'  COLLATE='utf8_bin' 

2.2.5.表

2.2.5.1.表参数设置
  • InnoDB表的默认ROW_FORMAT为COMPACT,请根据情况指定合适的值:
    • COMPACT:在不产生行溢出的情况下,一行数据存储在一个数据页中,数据读取效率最好
    • DYNAMIC:至少存储在2个页中,数据页只存储溢出页的指针,数据存在溢出页中,这种结构的索引存储最集中,索引访问效率较高
    • REDUNDANT:老的格式,不要使用
    • COMPRESSED:会对数据进行压缩后存储,最高的空间利用率,不过会增加CPU和内存消耗,增加系统响应时间,降低吞吐量,谨慎使用
  • 强烈建议明确指定表默认的字符集参数
  • 文件存放路径等物理相关的参数是不允许指定的
2.2.5.2.主外键、约束设计
  • InnoDB表必须指定主键
  • 建议不使用具有实际意义的字段做主键,如果一定要使用,确保该字段具有现实世界的唯一且不变性(例如:银行交易流水号),以尽量避免主键修改
  • 减少外键的使用,特别是在“分布式(水平拆分)”模式下,考虑到数据库的自动分库分表,建议不使用外键
  • 组成主键的字段总长度越短,效率越高。整数类型的字段做主键最合适
  • 如果存在多个唯一键,考虑最常用的唯一键作为主键
  • 建议使用自增字段作为主键
  • 字段属性尽量加上NOT NULL约束以及默认值,使用NULL值会导致如下副作用:
    • 含义不明,对很多运算符不管用,增加复杂度。例如a!=5是无法匹配到a为NULL的行
    • 很难进行查询优化
    • 含NULL的复合索引失效
  • 不要使用CHECK约束,MariaDB(TDSQL)(Mysql)虽然不报错,但会忽略,可以使用ENUM类型代替。
2.2.5.3.字段类型设计
  • 整数类型请根据实际存储值的范围选择合适的类型
  • 无符号整数类型请添加UNSIGNED关键字
  • 字符串类型请根据实际存储值的情况确定选择定长类型还是变长类型
  • 根据实际存储值的情况,尽量减少字符串类型的长度
  • 尽量避免使用TEXT/BLOB类型,不要在数据库中存储图片、二进制文件等数据
  • 非整数尽量使用精确的DECIMAL,避免使用浮点数

下表是推荐使用的主要数据类型取值范围和存储需求:

类型 值范围 存储需求
TINYINT[(M)] -128到127 或 0到255 1个字节
SMALLINT[(M)] -32768到32767 或 0到65535 2个字节
INT[(M)] -2147483648到214748364或 0到4294967295 4个字节
BIGINT[(M)] -9223372036854775808到9223372036854775807或 0到18446744073709551615 8个字节
DECIMAL[(M[,D])] 整数最大位数(M)为65,小数位数最大(D)为30 变长
DATE '1000-01-01' 到 '9999-12-31' 3个字节
TIME[(<microsecond precision>)] '-838:59:59.999999'到'838:59:59.999999' 5个字节
DATETIME[(microsecond precision)] '1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999' 8个字节
TIMESTAMP[(<microsecond precision)] '1970-01-01 00:00:01' (UTC)到'2038-01-19 05:14:07' (UTC) 4个字节
CHAR[(M)] 0<M<=255 M的整数倍,和字符集设置有关
VARCHAR[(M)] 0<M<65532/N N的取值和实际字符集设置有关
TEXT[(M)] 0<M<65535/N N的取值和实际字符集设置有关

2.2.6.索引

  • 低选择性的列不加索引,如:性别
  • 在组合索引中,常用的字段放在前面,选择性高的字段放在前面
  • 需要经常排序的字段可加到索引中,字段顺序和最常用的排序一致
  • 对于较长的字符串类型字段,建议使用前缀索引
  • 合理合并索引,避免冗余,例如(a,b)和(a),应该去掉(a)
  • 单张表的索引控制在5个以内
  • 单个索引的字段不要超过5个
  • MariaDB(TDSQL)的InnoDB引擎支持全文索引,但目前限于英文

2.2.7.分页设计

分页是应用中最常见的访问模型,我们用下面几种分页方式的实际测试情况来看看如何设计合理的分页模型:

/*
 * 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>=323423 LIMIT 10; 
10 rows in set (0.01 sec) 

MySQL> SELECT * FROM post WHERE id >= ( SELECT sql_no_cache id FROM post LIMIT 80000,1 ) LIMIT 10 ; 
10 rows in set (0.02 sec)

上面的结果很明显,要尽量避免直接使用LIMIT m,n 这种分页方式

2.3.数据库安全设计

  • 数据库用户安全设计原则
    • 数据库用户权限授权按照最小分配原则
    • 数据库用户要分为管理、应用、维护、备份四类用户
  • 数据库用户权限设计规范
    • 除MariaDB(TDSQL)和核心维护人员外,其他用户不能拥有SUPER权限账号
    • 避免使用简单密码
    • MariaDB(TDSQL)的权限支持到字段级别,权限的主体对象要按最小原则控制
    • 开发、测试和生产环境中用户权限设置要保持一致
  • 严格禁止在数据库中存储任何形式的密码明文

3.开发规范

3.1.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)
  • 应该优化为:
    B=5 OR B=6

3.2.SQL语句规范

3.2.1.索引和分区

  • 合理使用USE INDEX和IGNORE INDEX进行索引的选择
  • 查询条件尽量使用索引
  • 注意字段类型,避免类型转换。类型转换除了会增加CPU消耗,如果转换失败,还会导致索引失效
  • 对于复合索引,查询条件必须包含所有前缀字段才管用,例如索引(a,b,c),查询条件必须是a或者a、b或者a、b、c才能使用到索引

3.2.2.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代替
  • 尽量使用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(*)的全表扫描查询,从设计上考虑另外用一张表存这个计数值
  • 尽量避免使用子查询

3.2.3.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;
    此时fid+1000 AND gid=gid+1000将作为值赋给fid,且没有Warning
  • 删除一张表用TRUNCATE,而不是DELETE

3.2.4.多表JOIN

  • 多表join时,各表的顺序按照各表在WHERE子句条件下返回的行数从小到大排列,行数最小的在最左边
  • 避免大表间的JOIN,一般表的记录数不超过10W条的情况下,才建议使用JOIN

3.2.5.其它

  • 合理使用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.基本要求

MariaDB(TDSQL)自带的安装程序会在安装前进行系统环境检查,包括以下项目:

检查项 期望结果
操作系统版本 Linux系列
文件句柄 大于100000
时间同步 NTP配置正确
用户 检查安装指定的用户是否存在
安装目录 检查安装目录是否存在且有写权限
数据目录 检查指定的数据目录是否存在且有写权限

4.2.“常规(不分片)”与“分布式(水平拆分)”的选择

当满足以下条件时,可以考虑使用“分布式(水平拆分)”:

  • 表中数据会持续增长,在可预见的时间内,超过单机最大存储量
  • 表的读写量很大,超过了单机最大吞吐量
  • 充分考虑了分库分表会导致的在表设计和使用上的各种限制

关于“分布式(水平拆分)”版本在使用上的限制,请参考: 分布式版本开发指南

4.3.主从配置与容灾

目前云数据库已支持下列能力:

  • MariaDB(TDSQL)支持一主多从配置,且能在主机宕机时自动选举一台从机为主机
  • 从机个数可以自定义,且多个从机提供负载均衡的只读能力,所以增加从机可以扩容读能力(从机的数据有延迟)
  • MariaDB(TDSQL)支持强同步和异步两种主从同步方式:
    • 强同步能保证主机宕机不丢数据,因此建议至少配置2个从机。因为如果只有1个从机,当主机宕机,为保证数据不丢失,剩下1个从机只能提供只读服务
    • 强同步性能会低于异步,损失情况视主从机之间的网络质量而定。一般建议只在同机房,或者同城多个机房之间使用强同步
  • MariaDB(TDSQL)还支持将2个逻辑实例设置为主从,一般用于异地数据容灾

4.4.备份与恢复

目前云数据库已支持下列能力:

  • 为MariaDB(TDSQL)集群配置一个HDFS集群,用来存储冷备和BINLOG备份
  • MariaDB(TDSQL)支持配置定期冷备到HDFS
  • BINLOG会实时备份到HDFS
  • 利用冷备和BINLOG备份,MariaDB(TDSQL)支持指定时间点的回档

4.5.用户和权限

MariaDB(TDSQL)的使用者应该分为集群管理者、数据库实例管理者和数据库使用者:

  • 集群管理者主要通过运维管理平台完成以下工作:
    • 管理整个集群的资源池,负责资源的上下架和合理的分配
    • 为实例管理者分配数据库实例
    • 协助实例管理者进行实例管理
    • 负责管理所有实例的冷备和BINLOG备份数据
    • 关注集群和实例的运行指标,保证集群和各实例的正常运行
  • 数据库实例管理者主要通过数据库管理平台完成以下工作:
    • 提交新建、扩容、删除实例的申请
    • 负责维护实例的个性化参数
    • 负责管理数据库实例的账号和密码
    • 关注实例的运行指标,进行数据库性能管理
    • 通过查看慢查询分析、错误日志、慢查询日志等信息,进行数据库的性能优化分析
  • 数据库使用者通过SQL客户端连接数据库,提交SQL语句,存取数据

4.6.日志管理

MariaDB(TDSQL)集群的日志主要包括:

  • 集群中各个组件的运行日志
  • 实例的网关日志
  • 实例的数据库运行日志、慢查询日志、错误日志
  • 实例的数据库BINLOG文件
  • HDFS中存储的各种日志备份

所有日志都根据需要配置合理的清理策略

5.性能优化建议

5.1.性能优化原则

数据库性能优化通常涵盖方法论体系、特性及工具2个方面。方法论体系包含3部分内容:

  • 性能规划:深入了解应用与数据库的交互特征,确立良好的设计、开发、测试迭代过程,上线前消除模型上的性能瓶颈。
  • 实例调优:建立性能基准,对比调节数据库、操作系统、存储、网络等的配置,主动监控、消除瓶颈。
  • SQL调优:书写高效SQL,优化相关数据库对象,充分借助优化器,确定最佳执行计划。

5.2.性能优化步骤

  • 首先执行下面的初始检查:
    • 获取直接用户的使用反馈,确定性能目标和范围。
    • 获取性能表现好与坏时的操作系统、数据库、应用统计信息。
    • 对数据库做一次全面健康检查。
  • 定期收集数据库统计信息。
  • 根据收集的信息,以及对应用特性的了解,构建性能概念模型,明确性能瓶颈所在,以及导致性能的根本原因。
  • 提出一系列针对的优化措施,并根据它们对性能改善的重要程度排序,然后逐一加以实施。不要一次执行所有的优化措施,必须逐条尝试,逐步对比。
  • 通过获取直接用户的反馈验证调节是否已经产生预期的效果,否则,需要重新提炼性能概念模型,直到对应用特性了解进一步准确。
  • 重复上述,直到性能达到目标或由于客观约束无法进一步优化。

5.3.常用优化技巧

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