Mysql之表设计浅析

在后端开发中,经常需要设计数据库表,下面简单聊一下mysql的表设计原则。
1、命名规范
数据库表名、字段名、索引名等都需要统一命名规范,且需要具有可读性,英文或英文简写,切忌中英文混用。
反例:

acc_no,
zhanghao

正例:

account_no,
account_number
  • 表名、字段名须使用小写字母或数字,禁止使用数字开头,禁止使用拼音,用‘_’连接;
  • 主键索引名为pk_字段名,唯一索引名为uk_字段名,普通索引名为idx_字段名;

2、字段类型
设计表时,选择合适的字段类型:

  • 尽可能选择存储空间小的字段类型,如数字类型的,从tinyint、smallint、int、bigint从左往右开始选择;
  • 小数类型选择decimal,如金额,禁止使用float和double;
  • varchar是可变长字符串,不预先分配存储空间,长度不要超过5000;
  • 若存储的值太大,建议字段类型选择text,且独立出单个表,通过主键与之对应;

3、主键
主键设计的话,最好不要与业务逻辑有关联,如身份证,虽然是唯一的,但不建议。主键最好是毫无意义的一串独立不重复的数字,如自增的主键或雪花算法生成的主键;

4、字段长度
在mysql中,varchar和char类型表示字符长度,而其他类型表示的长度都是字节长度。如char(10)表示字符长度为10,而bigint(4)表示字节长度为4,但因为bigint实际长度是8个字节,所以bigint(4)的实际长度就是8个字节。
在设计表时,需要充分考虑一个字段的长度,一般设置为2的次方。

5、逻辑删除与物理删除

  • 逻辑删除:表结构中添加一个字段,如is_deleted,用于标记该条记录是否为逻辑删除;
  • 物理删除:将数据从硬盘中删除,可释放存储空间;

物理删除执行的是delete操作,逻辑删除执行的是update操作。由于物理删除后恢复数据困难,在业务表中的数据建议做逻辑删,即更改数据的状态,否则容易引发关联问题。

6、create_time、update_time
通常在设计表结构时,需要考虑添加通用字段,如主键、create_time,update_time,create_id,update_id,remark等。

7、一张表的字段不宜过多
在建表时,一张表的字段不宜过多,一般尽量不要超过20个字段。若一张表中的字段过多,表中存储的数据可能就会很大,查询效率就会降低。若业务需求很多字段时,可以把一张大的表拆成多张小的表,然后进行关联。

8、NOT NULL
若没有要求,一般建议将字段定义为NOT NULL。NOT NULL可以防止出现空指针问题,而NULL可能会导致索引失效,且也会占用空间,运算更为复杂。

9、索引

  • 索引不要建的太多,一般单表索引个数不要超过5个,因为过多会导致写慢;
  • 数据量大时再创建索引;
  • 区分度不高的字段不能加索引,如性别;

10、不搞外键关联
外键foreign key,是表中的一个字段设置为外键,用于将两个表连接在一起的健。外键是用来保证数据的一致性和完整性的。
阿里的java规范中建议不得使用外键与级联,一切外键概念须在应用层解决。因为使用外键容易引发性能问题、并发死锁问题、使用不便等。

11、字符集
数据库、表、程序等都需要通用字符集,通常中英文环境使用utf-8。

  • utf8:支持中英文混合场景,3个字节长度;
  • utf8mb4:完全兼容utf8,4个字节长度,一般存储emoji表情需要使用;
  • GBK:支持中文,但是不支持国际通用字符集,2个字节长度;

12、时间类型

在mysql中,时间类型有date、datetime、time、timestamp、year

  • date:表示的日期值, 格式yyyy-mm-dd,范围1000-01-01 到 9999-12-31,3字节;
  • time:表示的时间值,格式 hh:mm:ss,范围-838:59:59 到 838:59:59,3字节;
  • datetime:表示的日期时间值,格式yyyy-mm-dd hh:mm:ss,范围1000-01-01 00:00:00到9999-12-31 23:59:59```,8字节,跟时区无关;
  • timestamp:表示的时间戳值,格式为yyyymmddhhmmss,范围1970-01-01 00:00:01到2038-01-19 03:14:07,4字节,跟时区有关;
  • year:年份值,格式为yyyy。范围1901到2155,1字节;

推荐优先使用datetime类型来保存日期和时间,因为存储范围更大,且跟时区无关。

13、分库分表

  • 分库:就是一个数据库分成多个数据库,部署到不同机器;
  • 分表:就是一个数据库表分成多个表;

数据量太大的话,SQL的查询就会变慢。如果一个查询SQL没命中索引,千百万数据量级别的表可能会拖垮整个数据库。即使SQL命中了索引,如果表的数据量超过一千万的话,查询也是会明显变慢的。这是因为索引一般是B+树结构,数据千万级别的话,B+树的高度会增高,查询就变慢。建议超过500万数据量级别时进行分库分表。

14、sql语句

  • 查询sql尽量不要使用select * ,而是具体字段;
  • 若是知道查询结果为一条/最大/最小,建议使用limit 1;
  • 避免在where中使用or连接;
  • 避免在索引列上使用mysql的内置函数;
  • 避免在where 子句中使用!=或<>操作符;

15、性能分析工具
show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log;
show processlist;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值