MySQL必看表设计经验汇总-下(精华版)
目录
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.字段要注释
设计表时每个字段的含义要注释清楚，包括枚举类型。比如说
时间类型的选择一般都要好好考虑，因为不同的类型存储的格式不同。
对于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
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