mysql数据库模型规范

用户3291044225041
78 阅读2分钟

黑格尔说：秩序是自由的第一条件

数据库擅长存储与索引

规范理论

  1. 数据存储是以块来存储的，mysql的一个块是16K，数据读取与变更都是基于最小单位块
  2. mysql使用B树索引，二级索引的叶子节点存储着主键值

实例，库规范

  1. 数据库使用统一的版本，数据库使用版本>=5.7,建议新的业务使用8.0版本，如果有5.5版本的请尽快完成升级 mysql5.5与5.7的性能比较
  2. 数据库名根据业务进行分类，命名，保证唯一性
  3. 库中表个数控制<300应用创建表失控导致元数据不可用

表规范

  1. 表名，字段名全部小写，连接符号使用 "_"，不要超过30个字符
  2. 日志，备份的表，需要从开发阶段就考虑清理策略使用xxx_YYMMDD的命名方式，方便清理
  3. 表中意义相同的字段应同名
  4. 表，字段，视图，函数等数据库对象禁止使用关键字mysql 关键字
  5. 单表列数<=25
  6. 表，字段，枚举值需要有注释
  7. 表，字段禁止带入字符参数
name varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci, //禁止
ENGINE = InnoDB AUTO_INCREMENT = 1 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic //禁止
  1. 字段尽可能使用NOT NULL，在mysql中每个NULL值都是不同的值，会影响索引的性能，表中至少要有一个业务字段NOT NULL
  2. 表必须有数值类型主键 主从延迟案例
id int unsigned not null auto_increment primary key comment '自增主键'
  1. 表必须有创建,修改的时间字段
created_time datetime not null default current_timestamp comment '创建时间',
update_time datetime not null default current_timestamp on update current_timestamp comment '修改时间',
  1. 常用字段类型
类型存储占用默认值范围特殊应用场景
数值tinyint100-255枚举
int400-10^9
bigint800-10^19
字符串char()''
varchar()''
时间year1'1000'
date3'1000-01-01'
time3'00:00:01'
datetime5'1000-01-01 00:00:00'current_timestamp
json'{}' ,需要业务赋值不定字段
  1. 尽可能使用小数据类型
  2. 时间类型使用datetime ，不要使用timestamp，timestamp会产生时区转换的问题timestamp 数据类型引发的问题
  3. 字符类型尽可能小，不要使用过大的字符，例如varchar(255)
  4. 使用数值类型int 替代FLOAT 或 DOUBLE 存储浮点数，应用来处理小数位 ，例如业务字段/100
  5. 禁止使用text，blob大字段类型
  • 浪费磁盘和内存空间
  • 查询会淘汰掉热数据，导致内存命中率急速下降
  • 建议使用文本服务器，数据库中存储路径
  1. 不定字段可以考虑使用json，但是不要滥用
  2. 字段使用not null,给予默认值，在数据库中每一个null 都是不同的
  3. null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
  4. null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
  5. null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识
  6. 对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!='shenjian'，如果存在name为null值的记录，查询结果就不会包含name为null值的记录
  7. 单表数据量<=1000w，要对数据表进行预估，提前考虑分表和数据沉淀策略
  8. 字段加密
  • 常见涉密字段
姓名
电话，手机号
邮箱，qq号，微信号
身份证，驾照，营业执照
银行账号，支付宝账号
指纹，声纹

案例

create table user_account(
id int unsigned not null auto_increment primary key comment '自增主键',
name varchar(10) not null default '' comment '姓名',
addr varchar(50) not null default '' comment '地址',
vip_level tinyint not null default 0 comment 'vip级别',
created_time datetime not null default current_timestamp comment '创建时间',
update_time datetime not null default current_timestamp on update current_timestamp comment '修改时间',
user_info json comment '用户信息'，
KEY idx_update_time(update_time)
)comment '用户账号表';

索引规范

select a.name,b.goods_name 
from user_account a left join orders b on vip_level a.use_id=b.use_id and a.shop_id=b.shop_id
where user_id=9567 and vip_level=1;
alter table user_account add index idx_userid(user_id);
alter table orders add index idx_useid_shopid(use_id,shop_id);

Mysql 使用的是b+树索引 B+树索引

  1. 合适的索引字段是 不重复数据值/总行数>20%

select count(distinct id)/count(1) from xxx;

  1. 不要使用唯一索引，唯一性让业务判定实现，唯一索引会导致change buffer无用武之地，会降低数据库的tps，使用普通索引替代唯一索引
  2. 单表索引个数<6，索引字段<4，单字段索引长度<32
  3. 唯一索引的长度<24，唯一索引需要not null
  4. join 的关联条件一定要有索引，且注意关联字段的数据类型
  5. where 条件，group by,order by 字段建立索引
  6. 选择分辨率高的字段创建索引，枚举类的字段不要创建索引
  7. 合理使用复合索引，复合索引可以提高使用覆盖索引的机率，复合索引的长度<80,过长的索引，性能损耗大，需要把区分度高的字段放在前面
  8. 注意单列索引和符合索引不要重复
  • idx_useid_shopid(use_id,shop_id),idx_useid(use_id)

数据库其他对象规范

数据对象

自定义函数，存储过程，event，触发器，外键禁止使用，业务实现让应用逻辑实现

原因

  1. 数据库属于有状态的，这些对象让业务很难排查,协调困难
  2. 要求人员既熟悉数据库又熟悉业务，人员综合要求高，维护困难
  3. 数据库一般会部署集群，对这些对象的支持有限

sql 书写规范

sql优化工具:explain

sql上线前使用 explain select xxx; 查看sql的执行计划，优化sql

  1. sql 语句小写
  2. sql语句中不能带库名，禁止进行跨库操作
  3. 不要使用select * 会导致覆盖索引无法使用，回表性能差,大量的数据被加载，占用资源，cpu，io消耗大
user_account 数据量2000w,查询性能相差100倍
select * from user_account where last_update_time between '2021-01-01 00:00:00' and '2021-06-01 00:00:00' order by created_time asc;

select id,created_time from user_account where created_time between '2021-01-01 00:00:00' and '2021-06-01 00:00:00' order by created_time asc;
  1. 禁止insert into user_account values(xxx);容易出现数据插入错误 insert into user_account (xx,xx) values(xxx)
  2. in 的子查询改写成join,子查询的性能差
select id,name,addr from user_account where id not in (select id from account_vip where status=1);
改写
select t1.id,t1.name,t1.addr from user_account t1 join account_vip t2 on t1.id=t2.id 
where t2.id is null
  1. or 语句使用union 进行改写
  2. 字段处理，数值的隐式转换导致索引失效
  3. like '%xx'
  4. date_format(start_time,'')='2022-10-10 10:10:10'
  5. 关联查询 a.id 的字符集为utf8，b.id的字符集为utf8mb4
  6. 字段与值的数据类型不一致
  7. NOT in ,!=,<>,!<,!> 索引不可用，需要处理
  8. 深页sql优化

select id,name,addr from user_account where name='xxx' limit 50000,10

优化策略

1.主键带入

 select id,name,addr,max(id) from user_account where name='xxx' and id>xxx limit 10;

2.二级索引与主键join

select t1.* from user_account t1, (select a.id from user_account a where name='xxx' order by id desc limit 500000, 10) t2 where t1.id = t2.id;
  1. 复杂的sql ，合理拆解成多次查询,例如子查询
  2. 应用禁止create，drop，alter 表

应用开发配置规范

  1. 查询不要开启显示事务，开启显式事务会让数据库的tps-commit 值失真应用数据库查询开启显式事务导致的问题
  2. 应用的接口等待事务需要改造成异步，进行解耦，否则长的事务连接被断开，应用出现报错，该情况很难排查，影响因素包括，数据库的架构，参数配置，
  3. 禁止使用长事务，需要对长事务进行拆解，长事务会引发数据库的锁占用，引发数据库故障，数据库master资源有限，出现故障就是灾难级别
  4. 用户权限
  1. 应用提前考虑OLAP与OLTP业务，用户操作和后台任务的分离，预留读写分离接口

上线流程规范

  1. 一定要有测试环境,需要让问题在上线生产前进行暴露，要通过校验才能上线生产环境
  2. 测试环境后台配置了模型规范校验，监控等一系列的方案方便暴露测试环境的问题
avatar
文章
阅读
粉丝