【MySQL】优化方向+表连接

时间：2024-11-18 07:25:16

目录

数据库表连接

表的关系与外键

数据库设计

规范化

反规范化

事务一致性

表优化

索引优化

表结构优化

查询优化

数据库表连接

表的关系与外键

表之间的关系

常见表关系总结

  • 一对一关系：每一条记录在表A中对应表B的唯一一条记录，反之也是（例如一个用户和一个用户的详细信息表是一一对应的）
  • 一对多关系：表A中的一条记录可以关联到表B中的多条记录，而表B中的每条记录只关联到A中的一条记录（例如部门和员工表，一个部分可以有多个员工，而一个员工只可以属于一个部门）
  • 多对多的关系：表A中的多条记录可以关联到表B中的多条记录（例如学生和课程的关系，一个学生可以选修多门课，一门课也可以有多名学生选修）

例如A\B\C表中，A查看C表

  • 如果A表和C表之间有外键关系，那么就可以通过A表的外键直接查询到C表的数据
  • 如果A表和C表之间没有直接关系，但可能通过B表间接关联，这个时候就要通过JOIN来实现多表查询

外键

外键是用来在两个表之间创建关联关系的关键。例如如果A表的某列是B表的外键，那么每个表A的记录在这列中的存储值，必须是表B中的有效记录。这样就可以防止出现孤立的记录，也就是一个表中有数据，但是另一个表中找不到对应的数据。

例如三张表通过外键建立关系

  • A表：员工表，其中存储着员工的ID（主键）、姓名、部门
  • B表：部门表，存储着各个部门的信息，id（主键，部门的编号）、name（部门名称）
  • C表：公司表，公司ID、公司名称（name）、公司地点
  • 假设
    • 每个员工都属于一个部门，A表中部门与B中的部门相关联
    • 每个部门又属于一个公司

A想要查询C中的数据

  • 此时表A和表C之间没有直接关系，但是可以通过B表对其进行关联
    • 表A通过部门ID找到表B
    • 表B可以通过公司ID找到关联表C
  • 查询操作
    • 使用JOIN实现，先将AB表连接起来，然后将BC连将起来，这样A就可以通过B找到C了
SELECT A.name AS employee_name, C.name AS company_name
FROM A
JOIN B ON A.department_id = B.id
JOIN C ON B.company_id = C.id;

JOIN操作

JOIN是SQL中的一个关键字，主要就是用来将多个表的数据根据某种关系连接在一起，从而进行查询。

INNER JOIN（内连接）

返回两个表中符合条件的匹配记录，也就是只返回两个表中匹配到的记录。如果某行在其中一个表中没有对应的匹配就来，那么它就不会出现在最终结果中。

LEFT JOIN（左连接）

也就是返回左表中的所有记录，即使右表中没有匹配的记录。如果右表中没有对应的匹配记录，则会返回NULL。

当需要保留左表中的全部记录的时候，无论右表是否有匹配记录，使用左连接

RIGHT JOIN（右连接）

返回右表中的所有记录，即使左表中没有匹配的记录。如果左表中没有找到对应的匹配记录，那么就会返回NULL。应用在需要保留右表中的所有记录，无论左表是否有匹配记录的时候。

FULL JOIN（全连接）

返回两个表中的所有记录，如果某行在其中一个表中没有匹配记录，就会返回NULL。适用于希望获取两个表的所有记录，无论两者是否存在匹配关系。

交叉连接（CROSS JOIN）

返回两个表的笛卡尔积，也就是返回左表每一行和右表每一行的组合，不需要关联条件，通常返回的结果集数量非常庞大，除非表非常小。

数据库设计

规范化

规范化主要通过其应用范式来进行设计。规范化是一种设计数据库表的结构，目的就是减少数据冗余、消除数据不一致性的，同时通过分解表来确保数据的依赖性。

  • 第一范式：表中的每一列都只包含不可再分的原子值
  • 第二范式：满足第一范式的基础上，并且每一个非主键字段完全依赖于主键，而不是主键的一部分
  • 第三范式：满足第二范式的基础上，非主键字段不可以依赖于其他字段的主键

理解三大范式

  • 第一范式：每一列放置的信息都是唯一的。也就是说假如有一个家庭联系人名单，如果某一列中同时放置了其电话号码和手机号码，那么就不符合第一范式的情况。应该为设置两列，分别放置手机和电话号码，才满足其第一范式
  • 第二范式：一个表应该只有一个主键。假设班级学生表，如果学号和班级编号共同作为主键，但是学生姓名只依赖于学号，班级名称缺是依赖于班级编号，那么该种情况就不符合第二范式。如果学生姓名和班级名称全部都依赖于学号，那么就是符合第二范式。
  • 第三范式：假设有一个商品表，其中有商品名称、价格以及种类。商品价格和名称都应该直接依赖于商品的唯一标识（例如商品ID），而不是通过商品的种类去决定商品的价格。如果商品表中依赖于种类，而不是直接依赖于其ID，那么就是不符合第三范式。如果商品价格和种类都是直接依赖商品ID那么就符合第三范式。

规范设计订单处理

通过设计Orders表没有冗余的存储客户和商品的详细信息，而是通过customer_id和product_id引入Customers和Products表

反规范化

为了提高查询效率，故意将部分数据冗余存储，从而减少JOIN操作带来的开销。反序列化也就是通过在表中重复一些信息，避免频繁的跨表查询，从而加快查询速度，但是可能会导致数据不一致的情况。

订单查询实践

例如如果一个表中需要频繁查询客户表和订单表，呢么就可以将这两个字段进行冗余存储，从而减少连接操作。

-- 反规范化的订单表设计
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),  -- 冗余存储客户名称
    product_id INT,
    product_name VARCHAR(100),   -- 冗余存储商品名称
    order_date DATE
);

-- 插入订单时冗余插入客户名称和商品名称
INSERT INTO Orders (order_id, customer_id, customer_name, product_id, product_name, order_date)
VALUES (201, 1, 'Alice', 101, 'Laptop', '2024-01-01'),
       (202, 2, 'Bob', 102, 'Phone', '2024-01-02');


//查询指令
-- 查询订单详情，不需要 JOIN
SELECT order_id, customer_name, product_name, order_date
FROM Orders;

事务一致性

事务回顾

事务就是数据库的一组操作序列，这些操作要么全部成功要么全部失败，一个事务中可以包含有多个SQL语句，事务有四大特性ACID，原子性、一一致性、隔离性、持久性。

事务一致性就是在多表操作的时候，为了保证数据一致性，事务能够确保所有操作要么全部成功要么全部失败，在表之间有外键关联的时候，需要通过事务来支持确保数据的正确性。

事务和数据一致性的重要性

  • 防止脏读数据写入：多表关联情况下，如果没有使用事务，一部分表的数据写入成功，但是其他表写入失败，这样就会导致数据不一致的情况。例如订单信息插入成功，但是支付信息没有成功插入，这样就会导致存在订单却没有支付记录的脏数据

  • 维护外键的完整性：事务可以确保在涉及外键关联的多个表中，所有操作都正确的进行。例如如果插入的订单的时候没有客户信息或者插入支付信息中没有对应订单信息，外键约束就会被破坏，使用事务能够在发生错误的时候回滚，从而保证外键完整性。

  • 确保多表操作的原子性：多表操作正常都是涉及到多个操作，事务保证了这些操作的原子性，这些操作要么全部完成，要么全部失败。

事务操作的一些技巧

SAVEPOINT

也就是在事务中创建保存点，可以在事务的某个步骤回滚到这个保存点钟，而不是整个事务。例如在执行复杂操作的时候，某些部分是可以单独回滚的。

START TRANSACTION;

INSERT INTO Orders (order_id, customer_id, order_date) 
VALUES (1002, 1, '2024-01-02');

SAVEPOINT savepoint1;

INSERT INTO OrderItems (order_item_id, order_id, product_name) 
VALUES (5002, 1002, 'Phone');

-- 如果插入订单商品失败，可以回滚到插入订单之前
ROLLBACK TO savepoint1;

-- 提交事务
COMMIT;

LOCK TABLES

高并发环境下，可以通过显式锁定表来确保事务的隔离性。通过隔离锁从而避免并发写入冲突，确保事务操作的安全性。

LOCK TABLES Orders WRITE, OrderItems WRITE;

-- 插入订单和订单商品
INSERT INTO Orders (order_id, customer_id, order_date) 
VALUES (1003, 1, '2024-01-03');

INSERT INTO OrderItems (order_item_id, order_id, product_name) 
VALUES (5003, 1003, 'Tablet');

-- 解锁
UNLOCK TABLES;

表优化

仅提供思路，具体的实现后期补充

索引优化

通过优化索引，可以提高查询效率，减少数据库IO操作

  • 根据实际需求创建索引，根据表中的实际需求，为常用的子句创建索引
  • 避免冗余索引：定期检查和删除不再使用或者效果不好的索引，减少索引维护的开销
  • 使用覆盖索引：索引中应该包含查询所需要的所有列，避免回表操作
  • 前缀索引：对于长文本字段，应该使用前缀索引以节省空间

表结构优化

优化表结构，可以提高数据存取效率，节省存储空间

  • 遵循第三范式，消除数据的冗余，从而确保数据的一致性
  • 适度反规范化，为了查询性能的提高，可以适当的冗余存储部分数据，从而减少表关联操作
  • 合理数据结构，根据存储数据的特点选择合适的数据结构
  • 字段长度的优化，避免字段过长而导致空间的浪费
  • 垂直拆分，对于不常用的列分离到新表中，减少单表的宽度，从而提高缓存命中率
  • 水平拆分，就是对数据量巨大的表，按照特定的规则分散到多个表或者库中

查询优化

通过提高SQL查询的执行效率，从而降低响应时间

  • 避免全表扫描：确保查询条件中的列都有合适的索引支持
  • 优化SQL语句，避免使用select*，只查询必要的字段，避免在索引列上进行函数或者运算操作
  • 减少EXPLAIN分析执行计划：了解SQL的执行路径，发现并优化
  • 减少子查询的使用：尽量使用JSON来替代子查询，从而提高查询效率
  • 分页查询优化：对于大数据量进行分页，使用延迟关联或者子查询优化

相关文章