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

2024-02-01 15:52
文章标签 mysql 设计 经验 汇总 database 精华版 看表

本文主要是介绍MySQL必看表设计经验汇总-下(精华版)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

本内容一共分上下两篇

上：MySQL必看表设计经验汇总-上(精华版)-CSDN博客

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

目录

7.定义字段尽可能not null

8.合理添加索引

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

11.避免使用MySQL保留字

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

13.字段要注释

14.时间类型的选择

附加内容

有关SQL编写的一些优化经验

前言

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

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, -- 用户IDorder_date date    not null, -- 订单日期product_id int(11) not null, -- 产品IDquantity   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,                -- 用户IDorder_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编写的一些优化经验

  1. 避免使用SELECT*FROM 语句，应该只选择需要的列，以减少网络传输和提高查询性能。
  2. 使用索引来提高查询速度，特别是在对大型表进行查询时
  3. 避免使用外键约束，因为它们可能会导致性能问题，特别是在对大型表进行插入、更新和删除操作时。
  4. 使用LIMIT1来限制查询结果只有一条记录。
  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的计划。主要用来看sql走没走期望的索引

这篇关于MySQL必看表设计经验汇总-下(精华版)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/667721。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
不懂推荐算法也能设计推荐系统

不懂推荐算法也能设计推荐系统

本文以商业化应用推荐为例，告诉我们不懂推荐算法的产品，也能从产品侧出发， 设计出一款不错的推荐系统。 相信很多新手产品，看到算法二字，多是懵圈的。 什么排序算法、最短路径等都是相对传统的算法（注：传统是指科班出身的产品都会接触过）。但对于推荐算法，多数产品对着网上搜到的资源，都会无从下手。特别当某些推荐算法 和 “AI”扯上关系后，更是加大了理解的难度。 但，不了解推荐算法，就无法做推荐系
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...
怎么让1台电脑共享给7人同时流畅设计

怎么让1台电脑共享给7人同时流畅设计

在当今的创意设计与数字内容生产领域，图形工作站以其强大的计算能力、专业的图形处理能力和稳定的系统性能，成为了众多设计师、动画师、视频编辑师等创意工作者的必备工具。 设计团队面临资源有限，比如只有一台高性能电脑时，如何高效地让七人同时流畅地进行设计工作，便成为了一个亟待解决的问题。 一、硬件升级与配置 1.高性能处理器（CPU）：选择多核、高线程的处理器，例如Intel的至强系列或AMD的Ry
阅读更多...
MySQL-CRUD入门1

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2