mysql 数据库表结构设计与规范

数据库 同时被 2 个专栏收录
15 篇文章 0 订阅
12 篇文章 0 订阅

mysql 数据库表结构设计与规范

DDL(data difinition language)就是数据定义语言。

1.sql语句的界定符

[code]– 默认情况下” ; ” 代表sql语句的结束
delimiter 新的界定符
– 修改 // 为界定符
delimiter //

2.创建数据库

[code]#1.创建数据库
create database 数据库名字 [charset = uft8 ] [collate = 排序规则]

一般都写成这样

create database student charset=utf8;
可以用如下命令查看数据库支持的字符编码

[code]show charset;

用如下命令查看数据库排序规则:

show collation;

可以看到有197种排序规则,注意每种数据库的编码都有相应的排序规则可选,排序规则不是乱写。

3.修改数据库

[code]alter database 数据库名 charset 字符编码 collate 排序规则;

修改数据库的编码和排序规则

alter database mydb charset gbk collate gbk_chinese_ci;
alter database mydb charset utf8 collate utf8_general_ci;

4.显示所有的数据库

[code]show databases; – s 莫忘记

5.显示一个数据库的创建语句

[code]show create database 数据库名;

6.使用某个数据库

[code]use 数据库名

7.删除数据库

[code]#if exists 居于“安全运行的考虑” ,如果数据库不存在,也不会报错,最好写上
drop database [if exists] 数据库名

8.命名和大小写问题

mysql中的各种系统关键字和命令名本身是不区分大小写的,命名规则如下:

  1. 可以自己命名的名字,称为标识符,包括:数据库名, 表名,字段名,视图名,函数名,过程名,变量名,用户名,等等。

  2. 可以命名标识符的字符比常规的语言多,但特别建议只用:字母数字和下划线,并不用数字开头

  3. 非常规字符或系统关键字虽然可以作为标识符使用,但最好要包在反引号(数字1左边那个反撇 ` )中,并且不推荐。

  4. 对数据库名,表名,和视图名,在window系统中不区分大小写,而其他系统中区分,建议全使用小写,并采用下划线分割法

  5. 对其他自己命名的标识符(字段名,函数名,过程名),不区分大小写,但也建议全使用小写,并采用下划线分割法

关于单引号和反引号

反引号是为了区分MySQL的保留字和普通字符,其他例如SQL语句用双引号

MySQL中反引号,是以对象为单位的,表,或者库等,不能把a.name都括起来,而是应该`a`.`name`,因为如果`a.name`的话,MySQL会认为你是以a.name为名字的,名字里面包含了.,而这个名字就是不存在的

mysql数据库使用的一些规范

通常来讲,各个互联网公司的数据库分为5个数据库环境:

  • dev : 开发环境, 开发可读写,可修改表结构; 常用的163的数据库表; 开发人员可以修改表结构, 可以随意修改其中的数据; 但是需要保证不影响其他开发同事

  • qa : 测试环境, 开发可读写, 开发人员可以通过工具修改表结构

  • sim: 模拟环境, 开发可读写, 通过web平台;发起上线请求时,会先在这个环境上进行预执行, 这个环境也可供部署上线演练或压力测试使用 可以读写

  • real: 生产数据库从库(准实时同步),只读环境,不允许修改数据,不允许修改表结构; 供线上问题查找,数据查询等使用

  • online: 线上环境;开发人员不允许直接在线上环境进行数据库操作,如果需要操作必须找DBA进行操作并进行相应记录

这些环境的机器,一定要做到权限划分明确,读写帐号分离,并且有辨识度,能区分具体业务。例如用户名w_wap, r_wap 能看出来,读写帐号是wap应用的

数据库开发规范

开发规范本身也包含几部分:基本命名和约束规范,字段设计规范,索引规范,使用规范等

规范存在意义

  • 保证线上数据库schema规范

  • 减少出问题概率

  • 方便自动化管理

  • 规范需要长期坚持,对开发和DBA是一个双赢的事情

  • 约束规范

  • 表字符集选择UTF8 ,如果需要存储emoj表情,需要使用UTF8mb4(MySQL 5.5.3以后支持)

  • 存储引擎使用InnoDB

  • 变长字符串尽量使用VARCHAR VARBINARY

  • 不在数据库中存储图片、文件

  • 设计表的时候需要添加注释

  • 单表数据量控制在1亿以下,单表物理大小不超过10GB,行平均长度不超过8KB

  • 禁止在线上做数据库压⼒测试

  • 禁止从测试、开发环境直连数据库

demo:

CREATE TABLE `employees` (
`emp_no` INT NOT NULL AUTO_INCREMENT COMMENT ‘员工的id’,
`birth_date` DATE NOT NULL COMMENT ‘员工的生日’,
`first_name` VARCHAR(14) NOT NULL COMMENT ‘员工的first name’,
`last_name` VARCHAR(16) NOT NULL COMMENT ‘员工的last name’,
`gender` CHAR(1) NOT NULL COMMENT ‘员工的性别’,
`hire_date` DATE NOT NULL COMMENT ‘员工的入职日期’,
PRIMARY KEY (emp_no)
)ENGINE=InnoDB default charset = utf8;

基本命名规范

  • 库名、表名、字段名禁止使用保留字

  • 库名、表名、字段名、索引名使用小写字母,以下划线分割 ,需要见名知意

  • 库名、表名、字段名、索引名不要设计过长,禁止超过32个字符,尽可能用最少的字符表达出表的用途

  • 临时库、临时表名必须以tmp为前缀,并以日期为后缀

  • 备份库、表必须以bak为前缀,并以日期为后缀

  • 库名、表名、字段名、索引名使用名词作为数据库名称,并且只用英文,不用中文拼音

  • 库名使用英文字母,全部小写,控制在3-7个字母以内

  • 库名如果有多个单词,则使用下划线隔开,不建义驼峰命名

分表规范

  • 禁止使用分区表

  • 拆分大字段和访问频率低的字段,分离冷热数据

  • 使用HASH进行散表,表名后缀使用十进制数,下标从0开始

  • 按⽇期时间分表需符合YYYY[MM][DD][HH]格式

  • 采用合适的分库分表策略

字段规范

  • 所有字段均定义为NOT NULL ,除非你真的想存NULL,但是我想不到需要用Null的情况

  • 字段类型在满足需求条件下越小越好,使用UNSIGNED存储非负整数 ,实际使用时候存储负数场景不多

  • 使用TIMESTAMP存储时间,使用UNSIGNED INT存储IPv4 地址而不是CHAR(15) ,这种方式只能存储IPv4,存储不了IPv6

  • 使用VARCHAR存储变长字符串 ,当然要注意varchar(M)里的M指的是字符数不是字节数;

  • 使用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

  • 尽可能不用BLOB TEXT

  • 使用TINYINT来代替ENUM类型,将字符转化为数字

  • 禁止在数据库中存储明文密码

  • 使用VARBINARY存储大小写敏感的变⻓字符串

索引规范

  • 单个索引字段数不超过5,单表索引数量不超过5,索引设计遵循B+ Tree索引最左前缀匹配原则

  • 选择区分度高的列作为索引,区分度高的放在前面

  • 对字符串使用前缀索引,前缀索引长度不超过8个字符

  • 建议优先考虑前缀索引,必要时可添加伪列并建立索引

  • 建立的索引能覆盖80%主要的查询,不求全,解决问题的主要矛盾

  • DML和order by和group by字段要建立合适的索引

  • 避免索引的隐式转换

  • 避免冗余索引

  • 关于主键:表必须有主键 ;不使用更新频繁的列 ;不选择字符串列 ;不使用UUID MD5 HASH ;默认使用非空的唯一键 ,建议选择自增或发号器

  • 重要的SQL必须被索引:UPDATE、DELETE语句的WHERE条件列;

  • 核心SQL优先考虑覆盖索引

  • 不在低基数列上建立索引,例如“性别”

  • 不在索引列进行数学运算和函数运算

  • 尽量不使外键 ,外键用来保护参照完整性,可在业务端实现;对父亲和子表的操作会相互影响,降低可用性 ;INNODB本身对online DDL的限制

  • 不使%前导的查询,如like “%ab”

  • 不使用负向查询,如not in/like “无法使用索引,导致全表扫描

  • 隐式转换例子,字段定义为varchar,但传入的值是个int,就会导致全表扫描,要求程序端要做好类型检查

SQL类规范

  • 使⽤预编译语句,只传参数,比传递SQL语句更高效,降低SQL注用概率

  • 充分利用前缀索引

  • 尽量不使用存储过程、触发器、函数等,让数据库做最擅长的事

  • 避免使用大表的JOIN,MySQL优化器对join优化策略过于简单

  • 避免在数据库中进行数学运算和其他大量计算任务

  • SQL合并,主要是指的DML时候多个value合并,减少和数据库交互

  • 合理的分页,尤其大分页

  • UPDATE、DELETE语句不使用LIMIT ,容易造成主从不一致

  • 使用in代替or,in的值不超过1000个

  • 禁止使用order by rand()

  • sql语句避免使用临时表

  • 使用union all而不是union

  • 程序应有捕获SQL异常的处理机制

  • 禁止单条SQL语句同时更新多个表

  • 读取数据时,只选取所需要的列,不要每次都SELECT *,避免产生严重的随机读问题,尤其是读到一些TEXT/BLOB列

  • 通常情况下,子查询的性能比较差,建议改造成JOIN写法

  • 多表联接查询时,关联字段类型尽量一致,并且都要有索引

  • 多表连接查询时,把结果集小的表(注意,这里是指过滤后的结果集,不一定是全表数据量小的)作为驱动表

  • 多表联接并且有排序时,排序字段必须是驱动表里的,否则排序列无法用到索引

  • 多用复合索引,少用多个独立索引,尤其是一些基数(Cardinality)太小(比如说,该列的唯一值总数少于255)的列就不要创建独立索引了

  • 类似分页功能的SQL,建议先用主键关联,然后返回结果集,效率会高很多

DBA规范

主要内容

  • SQL审核,DDL审核和操作时间,尤其是OnlineDDL

  • 高危操作检查,Drop前做好数据备份

  • 日志分析,主要是指的MySQL慢日志和错误日志

  • 数据备份方案

  • Online DDL

原生MySQL执行DDL时需要锁表,且锁表期间业务是无法写入数据的,对服务影响很大,MySQL对这方面的支持是比较差的

推荐使用pt-online-schema-change

使用pt-online-schema-change的优点有:

  • 无阻塞写入

  • 完善的条件检测和延时负载策略控制

使用pt-online-schema-change的限制有:

  • 改表时间会比较长(相比直接alter table改表)

  • 修改的表需要有唯一键或主键

  • 在同一端口上的并发修改不能太多

MySQL集群方案

  • 基于主从复制;

  • 基于中间件/proxy

  • 基于NDB引擎

  • 基于Galera协议

优先推荐MHA:可以采用一主多从,或者双主多从的模式,这种模式下,可以采用MHA或MMM来管理整个集群,最新的MHA也已支持MySQL 5.6的GTID模式了

  • MHA的优势很明显:

  • 开源,用Perl开发,代码结构清晰,二次开发容易;

  • 方案成熟,故障切换时,MHA会做到较严格的判断,尽量减少数据丢失,保证数据一致性;

  • 提供一个通用框架,可根据自己的情况做自定义开发,尤其是判断和切换操作步骤;

  • 支持binlog server,可提高binlog传送效率,进一步减少数据丢失风险。

不过MHA也有些限制:

  • 需要在各个节点间打通ssh信任,这对某些公司安全制度来说是个挑战,因为如果某个节点被黑客攻破的话,其他节点也会跟着遭殃;

  • 自带提供的脚本还需要进一步补充完善,当然了,一般的使用还是够用的。

拆分问题

  • 解决单机写入压力过大和容量问题

  • 有垂直拆分和水平拆分两种方式

  • 拆分要适度,切勿过度拆分

  • 有中间层控制拆分逻辑最好,否则拆分过细管理成本会很高

数据备份

  • 全量备份 VS 增量备份

  • 热备 VS 冷备

  • 物理备份 VS 逻辑备份

  • 延时备份

  • 全量binlog备份

建议方式:

  • 热备+物理备份

  • 核心业务:延时备份+逻辑备份

  • 全量binlog备份

主要做的几点:

  • 备份策略集中式调度管理

  • xtrabackup热备

  • 备份结果统计分析

  • 备份数据一致性校验

  • 采用分布式文件系统存储备份

  • 1
    点赞
  • 0
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值