MySQL 数据库设计经验总结

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

背景

此文仅在数据库设计层面进行探讨,数据库的运维与底层调优不在讨论范围之内。

更丰富的知识可以在 MySQL 官网文档 查阅。

学习官方文档也是一种好的习惯,能更系统更全面的掌握某一领域的知识,具体知识点也可以通过搜索引擎快速获取,但是很难让你深入到细节或者上升到宏观层面。

基础知识

存储引擎

字符集

MySQL 可以在 server 级、database 级、table 级、column 级进行字符集的设置。

数据库设计

总则

数据库层面还是推荐使用“_”作为分割,这里多说几点:
1、约定俗成。长久以来不仅 MySQL ,其他数据库也推荐使用“_”,这是一种 SQL 规范。
2、JSON 返回的数据一般也会将驼峰转化为“_”来分割。
3、JAVA POJO 对象还是使用驼峰命名,现在的 JSON 转换工具, ORM 工具可以很便捷的指定参数来设置驼峰或者下划线的偏好。

如果仅是使用 Mybatis ,为了减少配置,也可以考虑使用驼峰命名数据库字段,或者使用 Mybatis 的 mapUnderscoreToCamelCase 的参数来解决。
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true" />
    </settings>
</configuration>
注意:resultType="hashMap" 的时候,不会生效,一定要有对应的实体。
Spring Boot 中指定 mybatis.configuration.mapUnderscoreToCamelCase=true

尽量避免使用触发器,视图,储存过程,将业务逻辑与统计逻辑分离。不要在业务库中大量统计计算。避免使用各种数据库函数。

库设计

表设计

一般来说以“t_”开头是为了表示这是一张表,区别于视图,触发器,存储过程等。而现在互联网架构一般不建议使用视图,触发器,存储过程等。而且很多企业只有一个库,全是 t_ 开头的数十上百张表,根本不知道怎么快速检索。

如果已经按照功能模块做了数据库的拆分,可以不要使用任何前缀,直接使用表名,如 order 。如果还在使用一个库放所有的表的话,那就最好已模块名开头,比较客服系统的工单表,可以命名为 cs_job 。

注意:不要与数据库关键字冲突

字段设计

扩展知识点:第二范式 聚簇索引 非聚簇索引

更新时间,更新者ID,更新者姓名等字段可以根据自己喜好设置初始值或者为空
支付时间,退款时间,发货时间,收货时间等业务上后续发生的行为可以为空
其他字段一律不为空,更好的数据库约束,减少理解不一致和性能损耗。

B.4.4.3 Problems with NULL Values

22.2.7 How MySQL Partitioning Handles NULL

这一点很多开发人员都没有注意过。我们还是看一下官网的描述。11.1.1 Numeric Type Overview
int(11) 中的 11 只是展示位数,并不影响实际存储的值,配合 ZEROFILL ,改变的只是查询的值,比如数据库字段定义为 INT(4),值为 1,启用了 ZEROFILL, SELECT 的结果展示为 0001。

INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

M indicates the maximum display width for integer types. The maximum display width is 255. Display width is unrelated to the range of values a type can contain, as described in Section 11.2, “Numeric Types”. For floating-point and fixed-point types, M is the total number of digits that can be stored.

MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. As an extension to the standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT.

如果要考虑数据迁移的话,尽量使用 INT 或者 SMALLINT。

整型的UNSIGNED很魔性,需要结合实际场景使用。ZEROFILL 默认使用 UNSIGNED。UNSIGNED 修饰有两大作用:一是保证列为非负数,二是可以扩大使用范围,个人推荐使用 UNSIGNED。

不能用一个字段来表示两种逻辑含义,还有就是字段要存储直接的字面量,不要存储需要计算的值,比如使用类 Linux 文件系统权限的模式来处理状态

如果使用了分布式文件储存系统,或者用了第三方的文件存储服务。可以不用存储域名前缀,应用层实现拼接或者前端处理。

确实需要存储的可以考虑拆表,将基本字段与扩展大字段分开。甚至可以考虑采用 MongoDB 之类的数据库才存储大量的文本表。

索引设计

索引失效的几种情况:
1、字段值的区分度太小,比如性别的 0,1
2、like 使用了前模糊匹配 like '%asdf'
3、使用了函数 date(birthday) 或者进行了计算 a + 50
4、违反左匹配原则 如联合索引 a, b 实际查询使用顺序为 b, a
5、索引列中含有 NULL
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看