您当前的位置:首页 > 文章 > MySQL必看表设计经验汇总-下(精华版

MySQL必看表设计经验汇总-下(精华版)

作者:蒾酒 时间:2024-04-29 阅读数:122 人阅读
目录
7.定义字段尽可能not null
8.合理添加索引
9.不需要严格遵守3NF,通过业务字段冗余来减少表关联
11.避免使用MySQL保留字
12.不搞外键关联,一般都在代码维护
13.字段要注释
14.时间类型的选择

前言
本内容共分上下两篇建议从上篇开始看,下篇衔接上篇

7.定义字段尽可能not null
如果没有特殊的理由,一般都建议将字段定义为NOT NULL。为什么呢?
首先,NOT NULL 可以防止出现空指针问题
其次,NULL值存储也需要额外的空间的,它世会导致比较运算更为复杂,使优化器难以优化SQL。
NULL值有可能会导致索引失效
如果将字段默认设置成一个空字符串或常量值并没什么不同,且都不会影响到应用逻辑,那就可以将这个字段设置为NOT NULL。

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

1.根据查询条件进行选择(高频使用)::如果在查询中使用了某个字段作为查询条件,那么这个字段就应该建立索引。例如,在用户表中,如果需要根据用户的姓名进行查询,那么就应该为姓名字段建立索引。

2.区分度高的字段优先:如果一字段的取值范围非常小,例如性别只有男女两种可能,那么这个字段就不适合建立索引。相反,如果一个字段的取值范围很大且区分度高,例如用户ID,那么这个字段就非常适合建立索引。

3.不要建立过多的索引:每个表所建立的索引数量应该控制在一个合理的范围内,一般不要超过5个。因为过多的索引会导致写入速度变慢,并占用更多的存储空间。

4.联合索引优化:在某些情况下,可以通过联合索引的方式来优化查询速度,减少所需的索引数量。例如,在用户表中,如果需要根据用户姓名和年龄进行查询,那么可以将这两个字段组合成联合索引。
假设你有一个订单表,包含订单ID、用户ID、订单金额、订单状态等字段。现在需要根据用户ID和订单状态进行查询,可以考虑为用户ID和订单状态这两个字段建立联合索引。

9.不需要严格遵守3NF,通过业务字段冗余来减少表关联
简单来说就是反范式设计常见形式是在第三范式(3NF)的基础上进一步进行冗余,从而减少表关联

数据库三范式(3NF):

第一范式:对属性的原子性,要求属性具有原子性,不可再分解
第二范式::记录的唯一性,要求记录有唯一标识,即实体的唯一性,即不存在部分依赖
第三范式:对字段的冗余性,要求任何字段不能由其他字段派生出来,它要求字段没有冗余,即不存在传递依赖
假设需要设计个产品订单表,包含以下字段: 订单ID、用户ID、订单日期、产品名称、产品价格品数量以及订单总价。正常情况下,可能会分别设计订单表和产品表,并使用外键进行关联,例如:
create table orders
(
    id         int(11) not null auto_increment,
    user_id    int(11) not null, -- 用户ID
    order_date date    not null, -- 订单日期
    product_id int(11) not null, -- 产品ID
    quantity   int(11) not null, -- 数量
    primary key (id),
    foreign key (product_id) references product (id)
);
create table product
(
    id    int(11)        not null auto_increment,
    name  varchar(256)   not null, -- 产品名称
    price decimal(10, 2) not null, -- 价格
    primary key (id)
);
这种设计在使用时会比较麻烦,你要先查订单表再查产品表通过数量跟单价才可以计算出总价。

这个设计方式符合范式要求,但在查询时需要进行表关联操作,可能会降低查询效率。为了提高查询效率,我们可以使用反范式的设计方式,将订单表中的产品名称、产品价格和订单总价冗余存储到订单表中,从而避免关联查询。例如:
create table orders
(
    id            int(11)        not null auto_increment, -- 订单ID,自增类型
    user_id       int(11)        not null,                -- 用户ID
    order_date    date           not null,                -- 下单日期
    product_name  varchar(256)   not null,                -- 产品名称
    product_price decimal(10, 2) not null,                -- 产品单价,保留两位小数
    quantity      int(11)        not null,                -- 购买数量
    total_price   decimal(10, 2) not null,                -- 订单总价,保留两位小数
    primary key (id)
);
通过这种反范式的设计方式,我们可以避免表关联操作,提高查询效率,但也带来了一些缺点,如数据冗余,数据更新困难等,所以在实际应用中根据情况做取舍。

11.避免使用MySQL保留字
如果库名、表名、字段名等属性含有保留字时,SQL语句必须用反引号(`)来引用属性名称,这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂
如果你需要使用这些保留字作为表名、列名或其他标识符,你可以考虑以下方法来避免冲突:
在标识符前或后添加下划线:例如,将表名命名为“my_table",列名命名为“column_name
使用不同的单词或短语:例如,将表名命名为"orders_table",列名命名为"order_status"
使用反引号(`)将标识符括起来:例如,将表名命名为“table",列名命名为"column""。请注意在使用反引号时要小心,确保使用正确的语法和规范

MySQL常见保留字:
SELECT、INSERT、UPDATE、DELETE、CREATE、ALTER、DROP、FROM、WHERE、AND、OR、NOT、ORDER BY、GROUP BY

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

可能会导致性能问题,尤其是在对大型数据集进行操作时。这是因为每次插入、更新或删除操作都需要进行约束检查,这可能会导致额外的开销和延迟。
可能会限制数据库的灵活性和可扩展性。例如,如果需要对数据库进行分区或垂直分割,外键关联可能会导致额外的复杂性和限制。
可能会导致死锁和死循环,特别是在进行并发操作时。这可能会导致数据库出现不稳定的状态,从而影响系统的性能和可用性。
可能会导致数据库的维护和管理成本的增加。这是因为外键关联需要额外的管理和维护工作,例如添加、修改或删除外键约束时需要额外的测试和验证。
因此,在决定是否使用外键关联时,需要考虑实际业务需求和场景,并进行权衡和决策。在某些情况下,可以采用其他方法来保证数据的完整性和一致性,例如使用应用程序逻辑或数据库触发器来实现约束检查和数据操作。同时,需要注意数据库设计的基本原则和最佳实践,例如避免数据几余、遵循规范化原则和正常化理论等。

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

 'order_status' varchar(2) not null comment '订单状态 01:待支付,02:已支付,03:已发货,04:已完成,05:已取消'

14.时间类型的选择
时间类型的选择一般都要好好考虑,因为不同的类型存储的格式不同。
对于MySQL来说,主要有datedatetime、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类型来保存日期和时间,因为存储范围更大,且跟时区无关

附加内容
有关SQL编写的一些优化经验
避免使用SELECT*FROM 语句,应该只选择需要的列,以减少网络传输和提高查询性能。
使用索引来提高查询速度,特别是在对大型表进行查询时
避免使用外键约束,因为它们可能会导致性能问题,特别是在对大型表进行插入、更新和删除操作时。
使用LIMIT1来限制查询结果只有一条记录。
避免在where子句中使用OR来连接条件,应使用UNION来连接查询。
注意优化LIMIT深分页问题,可以使用OFFSET来替代LIMIT.。
使用where条件限制要查询的数据,避免返回多余的行。
尽量避免在索引列上使用MySQL的内置函数,这可能导致索引失效。
应尽量避免在where子句中对字段进行表达式操作,这可能导致索引失效。
应尽量避免在where子句中使用!=或<>操作符,这可能导致索引失效。
使用联合索引时,注意索引列的顺序,一般遵循最左匹配原则。
对查询进行优化,应考虑在where及order by涉及的列上建立索引。
如果插入数据过多,考虑批量插入。
在适当的时候,使用覆盖索引(查询列都是索引列),避免了回表。
使用EXPLAIN 分析你SQL的计划。主要用来看sql走没走期望的索引
————————————————
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。                        
原文链接:https://blog.csdn.net/qq_62262918/article/details/135891065

本站大部分文章、数据、图片均来自互联网,一切版权均归源网站或源作者所有。

如果侵犯了您的权益请来信告知我们删除。邮箱:1451803763@qq.com