MySQL学习1

1、外键和主键之间的更新、删除级联规则——实际的MySQL操作

1.1、外键与主键：

• 部门表（Departments）:
  • dept_id: 部门ID（主键）
  • name: 部门名称
• 员工表（Employees）:
  • emp_id: 员工ID（主键）
  • name: 员工姓名
  • dept_id: 所属部门ID（外键）

这里的dept_id在部门表中是主键，在员工表中是外键。

1.2、级联删除

从部门表中删除一个部门时，所有属于该部门的员工记录也将被自动删除——这叫做级联删除（ON DELETE CASCADE）。

• 删除部门表中的部门ID为1的记录。
• 所有员工表中部门ID为1的记录也被删除。

1.3、级联更新

更改了一个部门的ID，所有员工表中旧部门ID的记录自动更新为新的部门ID——这叫做级联更新（ON UPDATE CASCADE）。

• 更新部门表中的部门ID为1的记录到新的部门ID为10。
• 所有员工表中部门ID为1的记录也被更新为10。

1.4、其他选择：

• NO ACTION / RESTRICT：如果删除或更新部门会导致员工表中的记录违反外键约束，则不允许进行操作。这通常意味着如果有员工仍然隶属于该部门，则不能删除该部门。

• SET NULL：如果删除或更新部门，那么员工表中相关的部门ID会被设为NULL。这意味着员工不再与任何部门关联。

• SET DEFAULT：如果删除或更新部门，那么员工表中相关的部门ID会被设为预定义的默认值。例如，所有员工可能都会被分配给一个默认的部门。

2、索引

MySQL 中的索引是一种特殊的文件结构，使得数据库能够快速定位到数据表中的特定记录，从而提高数据检索的速度。可以将索引想象成一本书的目录，通过目录可以直接跳转到书中的特定章节，而不需要逐页查找。

2.1、常见索引有：

1、单列索引（Single-column index）：

• 这是最常见的索引类型，它仅基于表中的一个列创建。
• 单列索引对于加速基于单一列的查询非常有效。

2、组合索引又名复杂索引（Composite index 或 Multi-column index）

• 组合索引是基于表中的多个列创建的索引，组合索引可以被视为一个多维排序列表，它按照定义的顺序对行进行排序。
• 它们可以加速涉及多个列的查询，尤其是在这些列经常一起出现在 WHERE 子句中时。

2.2、注意事项：

• 索引不是免费的：虽然索引可以提高查询性能，但是它们也会占用磁盘空间，并且在插入、更新和删除数据时会增加额外的开销。
• 合理选择索引：并不是每个列都需要索引，通常只对经常用于查询条件的列创建索引。
• 组合索引的顺序很重要：索引定义中的第一个列在查询优化器决定是否使用索引时扮演着重要角色。通常应该把选择性最强的列（能够过滤掉最多不相关行的列）放在前面。

3、规范化和反规范化

3.1、规范化：

MySQL中的数据规范化原则主要是指通过一系列的设计准则来消除数据冗余、提高数据的一致性和完整性。一般数据库设计表应至少满足3NF（第三范式）。

• 第一范式 (1NF)：每一列都是原子性的——“联系信息”这列中包含电话号码和电子邮件地址，那么这个表就不符合第一范式。
• 第二范式 (2NF)：消除部分依赖——第二范式建立在第一范式的基础上，并要求表中的每一列都完全依赖于主键，不能部分依赖。例如，一个表包含订单信息和产品信息，而订单ID和产品ID共同构成主键，那么产品描述等信息不应该出现在这个表中，而应该存在于一个专门的产品表中。
• 第三范式 (3NF)：消除传递依赖——进一步要求表中的每一列都直接依赖于主键，而不是依赖于其他非主键列。例如，一个订单表包括订单id，商品id，商品name，而商品name依赖于商品id，这时候就出现了订单id——>商品id——>商品name的传递依赖关系，不符合第三范式。
• 第四范式 (4NF)：消除多值依赖——如果一个非候选键的列组决定另一个列，而且这个决定与主键无关，那就是多值依赖。
• 第五范式 (5NF)：消除连接依赖——连接依赖是指，当从多个表中提取数据并通过连接操作组合时，原始表中的数据才能正确重构。

3.2、反规范化：

反规范化（Denormalization）是一种数据库设计技术，它通过引入冗余数据来提高查询性能。反规范化是一种权衡策略，它牺牲了一定程度的数据一致性和存储空间来换取更高的查询性能。在实施反规范化之前，需要仔细评估应用程序的具体需求，并确保维护冗余数据的一致性。

3.2.1、例子：

假设有一个订单表 orders 和一个产品表 products，每个订单可能包含多个产品。经常需要查询每个订单的总金额，就可以采用反规范化的方式。

正常设计：

正常设计每次计算总金额都需要连接查询大量的表。

• orders（orders_id，customer_id）——订单表
• order_items（orders_id——外键，product_id——外键，quantity，price）——订单单种商品详情
• products（product_id，product_name，unit_price）——商品表

反规范化设计：

增添order_total_amount表，虽然增加了冗余数据，但是在查询订单总金额时会提高查询性能。

• order_total_amount（orders_id，customer_id，total_amount）

3.2.2、反规范化的优点：

• 提高查询性能：通过预先计算和存储常用的数据组合，可以减少连接操作和复杂的查询，从而提高查询速度。
• 简化查询：减少了连接多个表的需求，使得查询更简单、更容易编写和维护。

3.2.3、反规范化的缺点：

• 数据冗余：增加了存储空间的需求，因为相同的数据可能存储在多个地方。
• 数据不一致性：如果更新逻辑不正确或维护不当，可能会导致数据不一致。