MySQL表设计经验汇总篇

发布时间:2024-03-05 13:14:34
 

表设计是每一个后端程序员都无法避开的一块砖,而且这块砖一不小心就很容易烫手,本篇笔记就是为了帮助大家在设计表时能够轻松拿捏。

1.命名规范

数据库表名、字段名、索引名等都需要命名规范。命名可读性要高,尽量使用英文,采用驼峰或者下划线分割的方式,让人见名知意。

反例:这些命名过于简单,缺乏描述性,很难让人理解它的含义。

  1. 表名:a、b、c
  2. 字段名:aaa、bbb、ccc
  3. 索引名:index1、index2、index3

正例:这些命名就让人见名知意。

  1. 表名:customers、orders、products
  2. 字段名:customer_id、order_date、product_name
  3. 索引名:idx_customer_id、idx_order_date

Tips:

  • 表名、字段名必须使用小写字母,禁止使用数字开头,禁止使用拼音,并且一般不使用英文缩写。
  • 主键索引名为pk_字段名;唯一索引名为uk_字段名;普通索引名则为idx_字段名。

2.选择合适的字段类型

设计表时,需要选择合适字段类型,比如说:

  • 根据数据类型选择字段类型:不同的数据类型应该使用不同的字段类型。
    • 整数型数据可以使用 INT 或 BIGINT 类型
    • 浮点型数据可以使用 FLOAT 或 DOUBLE 类型
    • 字符型数据可以使用 VARCHAR 或 CHAR 类型
  • 考虑数据长度:字段类型应该根据所需存储的数据长度来选择。
    • 如果某个字段的数据长度不会超过 10 个字符,则可以使用 CHAR(10) 类型代替 VARCHAR(50) 类型,以节省空间。
    • 如果存储的值太大,建议字段类型修改为text,同时抽出单独一张表,用主键与之对应
  • 注意精度和小数位数:对于需要精确数值计算的字段(如货币和百分比),应该选择带有精度和小数位数的字段类型(如 DECIMAL )。
  • 考虑数据完整性:字段类型也应该考虑到数据完整性。
    • 日期型数据应该使用 DATE 或 DATETIME 类型,以确保输入的日期格式正确。

3. 主键设计要合理

主键的设计在数据库中非常重要,它用于唯一标识表中的每一行数据,并且在数据操作和查询中起到关键作用。通常主键的设计,不要与业务相关联,因为业务是会发生变化的,应当使用自增的 id,并且保持主键的连续性。比如说可以使用优化的雪花 id 等等。

4. 选择合适的字段长度

首先问大家一个问题,数据库字段长度表示字符长度还是字节长度?

MySQL中,varchar和char类型表示字符长度,而其他类型表示的长度都表示字节长度。

char(10)表示字符长度是10。

bigint(4)表示显示长度是4个字节,但是因为bigint实际长度是8个字节,所以bigint(4)的实际长度就是8个字节。

所以在设计表时需要充分考虑一个字段的长度,比如一个用户名字段(它的长度5~20个字符),你觉得应该设置多长呢?可以考虑设置为 varchar(32)。需要注意字段长度一般设置为2的n次方。

5.优先考虑逻辑删除,而不是物理删除

什么是物理删除?什么是逻辑删除?

  • 物理删除:把数据从硬盘中删除,可释放存储空间
  • 逻辑删除:给数据添加一个字段,比如is_deleted,以标记该数据已经逻辑删除。

为什么推荐用逻辑删除,不推荐物理删除呢?

  • 数据恢复困难。
  • 物理删除会导致索引树重构

6.每个表都需要添加通用字段

  • id: 主键,一个表必须得有主键,必须
  • create_time: 创建时间
  • creator :创建人
  • update_time: 修改时间,必须,更新记录时,需要更新它
  • update_by :修改人,非必须
  • remark :数据记录备注,非必须

7.一张表的字段不宜过多

建表的时候一张表的字段不要太多了。尽量不超过 20 个。超出的话优先考虑拆分,也就是通常的查询表,详情表。

  • 查询效率:当表中保存的数据数量很大时,查询操作需要检索的数据也会随之增加。如果表的字段数过多,查询操作就需要读取更多的数据,这会导致查询效率变慢。
  • 存储空间:表的字段数越多,每一行数据占用的存储空间也就越大。对于大型数据库来说,这可能会导致磁盘空间的浪费。
  • 数据库设计复杂性:当表的字段数过多时,数据库的设计和维护变得更加复杂。这可能涉及到索引和关联表的设计,以确保数据的完整性和一致性。

 

 

 

 

 

8.定义字段尽可能not null

如果没有特殊的理由, 一般都建议将字段定义为 NOT NULL 。为什么呢?

  • 首先,NOT NULL 可以防止出现空指针问题。
  • 其次,NULL值存储也需要额外的空间的,它也会导致比较运算更为复杂,使优化器难以优化SQL。
  • NULL值有可能会导致索引失效

如果将字段默认设置成一个空字符串或常量值并没有什么不同,且都不会影响到应用逻辑, 那就可以将这个字段设置为NOT NULL。

9.合理添加索引

当设计表时,需要考虑哪些字段需要加索引,可以遵循以下几个原则:

  • 根据查询条件进行选择(高频使用):如果在查询中使用了某个字段作为查询条件,那么这个字段就应该建立索引。例如,在用户表中,如果需要根据用户的姓名进行查询,那么就应该为姓名字段建立索引。
  • 区分度高的字段优先:如果一个字段的取值范围非常小,例如性别只有男女两种可能,那么这个字段就不适合建立索引。相反,如果一个字段的取值范围很大且区分度高,例如用户ID,那么这个字段就非常适合建立索引。
  • 不要建立过多的索引:每个表所建立的索引数量应该控制在一个合理的范围内,一般不要超过5个。因为过多的索引会导致写入速度变慢,并占用更多的存储空间。
  • 联合索引优化:在某些情况下,可以通过联合索引的方式来优化查询速度,减少所需的索引数量。例如,在用户表中,如果需要根据用户姓名和年龄进行查询,那么可以将这两个字段组合成联合索引。

假设你有一个订单表,包含订单ID、用户ID、订单金额、订单状态等字段。现在需要根据用户ID和订单状态进行查询,可以考虑为用户ID和订单状态这两个字段建立联合索引,例如:

CREATE TABLE order_tab (
    id int(11) NOT NULL AUTO_INCREMENT,
    user_id int(11) NOT NULL,
    amount decimal(10,2) NOT NULL,
    status varchar(20) NOT NULL,
    create_time datetime NOT NULL,
    PRIMARY KEY (id),
    KEY idx_user_status (user_id, status) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

10.不需要严格遵守 3NF,通过业务字段冗余来减少表关联

简单来说就是反范式设计。常见形式是在第三范式(3NF)的基础上,进一步进行冗余,从而减少表关联。

回顾下什么是数据库三范式(3NF)?

  • 第一范式:对属性的原子性,要求属性具有原子性,不可再分解;
  • 第二范式:对记录的唯一性,要求记录有唯一标识,即实体的唯一性,即不存在部分依赖;
  • 第三方式:对字段的冗余性,要求任何字段不能由其他字段派生出来,它要求字段没有冗余,即不存在传递依赖;

假设需要设计一个产品订单表,包含以下字段:订单ID、用户ID、订单日期、产品名称、产品价格、产品数量以及订单总价。正常情况下,可能会分别设计订单表和产品表,并使用外键进行关联,例如:

CREATE TABLE order (
    `id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`order_date` date NOT NULL,
`product_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE product (
    `id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这个设计方式符合范式要求,但在查询时需要进行表关联操作,可能会降低查询效率。为了提高查询效率,我们可以使用反范式的设计方式,将订单表中的产品名称、产品价格和订单总价冗余存储到订单表中,从而避免关联查询。例如:

CREATE TABLE order (
    `id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`order_date` date NOT NULL,
`product_name` varchar(255) NOT NULL,
`product_price` decimal(10,2) NOT NULL,
`quantity` int(11) NOT NULL,
`total_price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

通过这种反范式的设计方式,我们可以避免表关联操作,提高查询效率。但同时也带来了一些缺点,例如数据冗余、数据更新困难等。因此,在实际应用中需要根据具体情况进行选择。

11. 避免使用MySQL保留字

如果库名、表名、字段名等属性含有保留字时,SQL语句必须用反引号来引用属性名称,这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。

如果你需要使用这些保留字作为表名、列名或其他标识符,你可以考虑以下方法来避免冲突:

  1. 在标识符前或后添加下划线:例如,将表名命名为 "my_table",列名命名为 "column_name"。
  2. 使用不同的单词或短语:例如,将表名命名为 "orders_table",列名命名为 "order_status"。
  3. 使用反引号()将标识符括起来:例如,将表名命名为 "table",列名命名为 "column`"。请注意,在使用反引号时要小心,确保使用正确的语法和规范
ADD
ALL
ALTER
AND
AS
BETWEEN
BY
CASE
DELETE
FROM
GROUP
HAVING
INSERT
INTO
JOIN
LIKE
NOT
OR
SELECT
UPDATE
WHERE

12.不搞外键关联,一般都在代码维护

在数据库设计中,使用外键关联是一种良好的实践,可以确保数据的完整性和一致性。外键关联可以帮助维护表之间的关系,防止无效或不一致的数据插入、更新或删除操作。然而,在某些情况下,也存在一些缺点,这可能是导致现在不太推荐使用外键关联的原因之一。以下是一些这种情况:

  1. 可能会导致性能问题,尤其是在对大型数据集进行操作时。这是因为每次插入、更新或删除操作都需要进行约束检查,这可能会导致额外的开销和延迟。
  2. 可能会限制数据库的灵活性和可扩展性。例如,如果需要对数据库进行分区或垂直分割,外键关联可能会导致额外的复杂性和限制。
  3. 可能会导致死锁和死循环,特别是在进行并发操作时。这可能会导致数据库出现不稳定的状态,从而影响系统的性能和可用性。
  4. 可能会导致数据库的维护和管理成本的增加。这是因为外键关联需要额外的管理和维护工作,例如添加、修改或删除外键约束时需要额外的测试和验证。

因此,在决定是否使用外键关联时,需要考虑实际业务需求和场景,并进行权衡和决策。在某些情况下,可以采用其他方法来保证数据的完整性和一致性,例如使用应用程序逻辑或数据库触发器来实现约束检查和数据操作。同时,需要注意数据库设计的基本原则和最佳实践,例如避免数据冗余、遵循规范化原则和正常化理论等。

13.字段注释

设计表时每个字段的含义要注释清楚,包括枚举类型。比如说:

`order_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT '订单状态 01:待支付 02:已支付 03:已发货 04:已完成 05:已取消'

14.时间的类型选择

时间类型的选择一般都要好好考虑,因为不同的类型存储的格式不同。

对于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类型来保存日期和时间,因为存储范围更大,且跟时区无关。

15.SQL 编写的一些优化经验

  1. 避免使用SELECT * FROM 语句,应该只选择需要的列,以减少网络传输和提高查询性能。
  2. 使用索引来提高查询速度,特别是在对大型表进行查询时。
  3. 避免使用外键约束,因为它们可能会导致性能问题,特别是在对大型表进行插入、更新和删除操作时。
  4. 使用LIMIT 1来限制查询结果只有一条记录。
  5. 避免在where子句中使用OR来连接条件,应使用UNION来连接查询。
  6. 注意优化LIMIT深分页问题,可以使用OFFSET来替代LIMIT。
  7. 使用where条件限制要查询的数据,避免返回多余的行。
  8. 尽量避免在索引列上使用MySQL的内置函数,这可能导致索引失效。
  9. 应尽量避免在where子句中对字段进行表达式操作,这可能导致索引失效。
  10. 应尽量避免在where子句中使用!=或<>操作符,这可能导致索引失效。
  11. 使用联合索引时,注意索引列的顺序,一般遵循最左匹配原则。
  12. 对查询进行优化,应考虑在where及order by涉及的列上建立索引。
  13. 如果插入数据过多,考虑批量插入。
  14. 在适当的时候,使用覆盖索引。
  15. 使用EXPLAIN 分析你SQL的计划。

 
上一篇 分享7种SQL的进阶用法
下一篇 不知道的Spring知识点

文章素材均来源于网络,如有侵权,请联系管理员删除。

标签: Java教程Java基础Java编程技巧面试题Java面试题