mysql基础学习

mysql 日常优化的场景

sql LIMIT offset, size查询导致的频繁扫表问题

• 问题本质：
  • 不是跳过offset，直接获取size条数
  • 而是先找到offset + size条，然后舍弃前面的offset条，只取size条
• 解决：
  • 通过游标分页的方式解决
  • 先查id
• 备注： 如果同时涉及回表的情况，会产生大量的随机io

多表left join 导致结果集膨胀，接口响应延迟

• 问题本质：
  • 左表中的每一行，会与右表中所有满足 ON 条件的记录进行匹配
  • 如果右表存在多条匹配记录时，左表记录会被重复展开
• 举例说明

如果连续 JOIN 多个一对多关系：

sql
    user
    LEFT JOIN orders
    LEFT JOIN addresses

• 假设存在

js
1个用户
10个订单
5个地址

• 结果就会是50条记录

sql
1 * 10 * 5 = 50

• 会发生结果集膨胀的场景

sql
 1 ：1 不会
 N : 1 不会  case: 查订单id以及对应的用户名
 1 : N 会
 N : N 会，并且很严重

• 带来的影响
  • 返回数据量暴增
  • 排序成本增加
  • 网络传输增加
  • COUNT 统计错误
  • GROUP BY 开销变大
  • 临时表和磁盘排序
• 优化方式
  • 方案1：先聚合、再join
  • 方案2：用 exists 代替 join
    • 场景： 判断数据是否存在
  • 方案3：拆sql ，拆分为查主数据->批量查关联数据->程序组装
    • 场景：主流方案

join 的使用场景

• 明确的一对一或者多对一查询
• 聚合统计
  • 统计每个用户的订单数
• 需要取两张表的数据

exists 的使用场景

• 只关注是否存在
  • 查询有订单的用户

  SELECT * FROM user u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id=u.id );

• 优势：一旦匹配到记录，立即停止

三大范式

• 第一范式：要求一张表的每个字段，设计时都必须具备原子性，即单个列只表示一个值，不可再分。
• 第二范式：要求一张表的所有字段，都必须依赖于主键，也就是一张表只能存同一个业务属性的字段。
• 第三范式：要求表中每一列数据不能与主键之外的字段有直接关系，也就是表中只允许一个主属性存在

acid

• A/Atomicity：原子性
  • 指组成一个事务的一组SQL要么全部执行成功，要么全部执行失败。
• C/Consistency：一致性
  • 指任何一个事务发生的前后，库中的数据变化必须一致。
• I/Isolation：独立性/隔离性
  • 指同时存在多个并发事务时，各个事务之间执行的操作不会相互影响。
• D/Durability：持久性
  • 指一个事务但凡提交之后，就必须确保事务变更过的数据永远不会丢失。

内存模型

• 全局内存区域 ： 在 MySQL 启动时分配，由所有客户端连接共享
  • 缓冲池：混存索引和数据表，将磁盘io转变为内存访问
  • 重做日志缓存区： 事务提交前，暂存 redo_log
• 线程内存区域 ： 每当有一个客户端连接到 MySQL，服务器都会为其分配独立的内存空间。如果连接数过多，这部分内存会迅速耗尽系统资源
  • order buffer
  • join Buffer
  • read Buffer 用于全表或者索引扫描时的顺序缓存
  • binlog Cache 在事务提交期间，用来记录binlog的临时内存

mvcc

• 是什么
  • 即多版本并发控制
  • 是 InnoDB 为提高并发性能而采用的一种机制。
  • 它通过 Undo Log 保存数据历史版本，
  • 结合 Read View、以及每行记录的隐藏字段（如 DB_TRX_ID、DB_ROLL_PTR
  • 实现了在不加锁的情况下读取历史版本数据，即快照读。
  • 这样读操作不会阻塞写操作，写操作也不会阻塞读操作，
  • 大幅提高了数据库并发能力。
  • 同时，在可重复读（Repeatable Read）隔离级别下，
    • MVCC 还能保证同一事务多次读取结果一致，从而避免不可重复读。
  • 需要注意的是，
    • MVCC 主要用于快照读，
    • 而当前读（如 SELECT ... FOR UPDATE、UPDATE、DELETE）仍然依赖锁机制
• 每行记录都有三个隐藏字段
  • DB_TRX_ID (6字节)：最后一次修改该记录的 事务 ID。
  • DB_ROLL_PTR (7字节)：回滚指针，指向该记录的上一个版本（在 Undo Log 中）。
  • DB_ROW_ID (6字节)：隐藏的主键（如果表没有定义主键，则使用这个字段）。
• review视图中包含几个核心字段
  • creator_trx_id：创建该 Read View 的事务ID
  • m_ids：当前活跃的事务（未提交） ID 列表。
  • min_trx_id：活跃事务中最小的 ID。
  • max_trx_id：预分配给下一个事务的 ID（即当前最大事务 ID + 1）。

主从复制

• 步骤一：主库的更新事件(update、insert、delete)被写到binlog
• 步骤二：从库发起连接，连接到主库。
• 步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库。
• 步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
• 步骤五：还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

explain 对应的字段值