您的位置: 首页  >  文章  >  「笔记」MySQL实战 45讲 - 基础篇

「笔记」MySQL实战 45讲 - 基础篇

分类: 文章 • 2024-09-27 19:19:22

MySQL 的逻辑架构图

「笔记」MySQL实战 45讲 - 基础篇

  • Server 层涵盖 MySQL 的大多数核心服务功能以及所有的内置函数

  • 所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等

  • 存储引擎层负责数据的存储和提取（架构模式是插件式的

    • MySQL 5.5.5 版本开始 InnoDB 成为了默认存储引擎
    • 不同的存储引擎共用一个 Server 层

  • 连接器

    • 负责跟客户端建立连接、获取权限、维持和管理连接

    • 成功建立连接后，更换该账户权限不会影响已经存在的连接权限

      • 新建的连接才会使用新的权限设置

    • show processlist

      「笔记」MySQL实战 45讲 - 基础篇

      • 空闲连接：Command 列显示为 “Sleep” 的行
      • 参数 wait_timeout 控制空闲连接自动断开的（默认： 8 小时
      • 断开后，客户端显示 Lost connection to MySQL server during query

    • 长连接：连接成功后，如果客户端持续有请求，则一直使用同一个连接

      • 建立连接的过程通常是比较复杂的，推荐尽量使用长连接
      • 内存占用过大：执行过程中临时使用的内存是管理在连接对象里面的，只在连接断开时才释放
      • 解决方式：
        • 定期断开长连接（再使用时重连
        • MySQL 5.7 以后，通过执行 mysql_reset_connection 来重新初始化连接资源

    • 短连接：每次执行完很少的几次查询就断开连接，下次查询再重新建立一个

  • 查询缓存

    • 缓存：key-value 对的形式
    • 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空
      • 更新压力大的数据库来说，查询缓存的命中率会非常低
      • 静态表推荐可以使用（类似于 配置表
    • 按需使用：query_cache_type 设置成 DEMAND 时，只先显式使用 SQL_CACHE 才使用查询缓存
    • MySQL 8.0 版本 开始移除此功能

  • 分析器

    • 词法分析：识别出长字符串里各个的子字符串分别是什么，代表什么
    • 语法分析：根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法

  • 优化器

    • 决定使用哪个索引
    • 决定各个表的连接顺序

  • 执行器

    • precheck 验证权限：是否有该表的查询或更新权限
    • 根据表的引擎定义，去使用这个引擎提供的接口
    • 引擎扫描行数跟 rows_examined 并不是完全相同的

重要的日志模块

  • redo log（重做日志）

    • Write-Ahead Logging：先写日志，再写磁盘
      • 流程：先写到 redo log 里面，并更新内存，此时则标志着本次更新完成
    • crash-safe ：有了 redo log ，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失

  • binlog（归档日志）

    • 归档能力、主从复制等

    • redo log 与 binlog 主要三点区别

      • redo log 是 InnoDB 引擎特有的；binlog 是 Server 层实现的，所有引擎都可以使用

    • redo log 是物理日志，binlog 是逻辑日志

      • redo log 是固定大小，循环写入；bingo 是追加式写入；

    • 更新操作执行流程
      「笔记」MySQL实战 45讲 - 基础篇

  • 两阶段提交：保证 redo log 写入 与 binlog 写入是原子性操作
    - 让这两个状态保持逻辑上的一致
    - 跨系统维持数据逻辑一致性时常用的一个方案

事务隔离

  • 事务关联知识点：《MySQL 技术内幕》事务
  • 实现
    • 实际上每条记录在更新的时候都会同时记录一条回滚操作
    • 记录上的最新值，通过回滚操作，都可以得到前一个状态的值
    • 栗子：假设将 1 按顺序改成 2、3、4，在回滚日志里就会有类似如下记录
      • 图
    • 系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除
  • 启动事务
    • begin/start transaction ：执行到第一个操作 InnoDB 表语句时才启动事务
    • start transaction with consistent snapshot：立即启动事务
    • transaction id：唯一、事务开始时向系统申请、严格递增
    • row trx_id：事务更新数据时生成一个新的数据版本，由 transaction id 赋值而来
  • InnoDB 利用了 “所有数据都有多个版本” 的这个特性，实现了 “秒级创建快照” 的能力
  • 当前读：更新数据都是先读后写的，而这个读，只能读当前的值
    • select 语句如果加锁，也是当前读

深入浅出索引

  • 索引的常见模型

    • 哈希表
      • 适用于只有等值查询的场景
    • 有序数组
      • 在等值查询和范围查询场景中的性能就都非常优秀
      • 适用于静态存储引擎
    • N 叉树
      • 由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中

  • InnoDB 的索引模型

    • B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数

    • 索引组织表：表都是根据主键顺序以索引的形式存放的

    • 聚簇/主键索引：主键索引的叶子节点存的是整行数据

    • 二级索引：非主键索引的叶子节点内容是主键的

      • 回表：回到主键索引树搜索的过程

  • 索引维护

    • 页分裂操作对性能与数据页利用率均有影响
    • 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
      • 建议创建一个自增主键

  • 覆盖索引

    • 减少树的搜索次数，显著提升查询性能

  • 最左前缀原则

    • 要点：建立联合索引时，如何安排索引内的字段顺序
      • 如果通过调整顺序，可以少维护一个索引，这个顺序通常可以优先考虑
      • 存储索引空间（「大，小」 「小」 >> 「小，大」「大」

  • 索引下推

    • MySQL 5.6 引入，索引遍历过程中，对索引中包含的字段先做判断，直接过滤不满足条件，减少回表次数

  • 重建索引

    • 二级索引
      • 背景：索引可能因为删除，或者页分裂等原因，导致数据页有空洞
      • 作用：数据按顺序插入，页面利用率最高，索引更紧凑、更省空间
    • 主键索引
      • 重建主键索引将会导致整个表重建（包括所有的二级索引均会重建
      • 操作：alter table T engine=InnoDB
      • 扩展：删除表部分记录，不会对二级索引有影响（空间未释放）
        • 实例：表实际大小才10G，索引就占了30G，删数据，索引空间未减小

锁

  • 全局锁
    • Flush tables with read lock (FTWRL)：加全局读锁
      • 阻塞：数据更新语句、数据定义语句和更新类事务的提交语句
      • 典型场景：做全库逻辑备份
    • mysqldump：官方自带的逻辑备份工具
      • 使用参数–single-transaction ，导数据之前就会启动一个事务，来确保拿到一致性视图
      • 只适用于所有的表使用事务引擎的库
    • 不使用 set global readonly=true 的原因
      • 在有些系统中，readonly 的值会被用来做其他逻辑（判断主从库
      • 在异常处理机制上有差异（FTWRL 异常时主动释放锁，而后者不会
    • 加全局锁后，DML 与 DDL 操作都会被阻塞
      • DDL（Data Definition Languages）语句：即数据库定义语句
      • DML（Data Manipulation Language）语句：即数据操纵语句
      • DCL（Data Control Language）语句：即数据控制语句
      • TCL（Transaction Control Language）语句：事务控制语句
  • 表级锁
    • 表锁
      • 表锁的语法：lock tables … read/write
      • 可以主动释放锁，也可以在客户端断开的时候自动释放
      • 不仅限制别的线程，也限定自身线程的操作
        • 例如：线程 A 执行后lock tables t1 read, t2 write，不允许写 t1
    • MDL（metadata lock)

      • 不需要显式使用，访问一个表的时候会被自动加上，保证读写的正确性

      • MySQL 5.5 版本中引入了 MDL，增删改查加读锁，修改表结构加写锁

      • 天坑例子：给一个小表加个字段，导致整个库挂掉

        「笔记」MySQL实战 45讲 - 基础篇

        • session C 需要 MDL 写锁，因此只能被阻塞，从而造成 session D 申请读锁也阻塞
        • 查询超时重试，数据库线程被打满，表现出完全不可读写
        • 破局之道
          • 解决长事务（暂停DDL 或 kill 掉此事务
          • alter table 语句里面设定等待时间，超时自动放弃
            • ALTER TABLE tbl_name WAIT N add column …
  • 行锁
    • 行锁是在引擎层由各个引擎自己实现的
    • 两阶段锁协议：在 InnoDB 事务中，行锁是在需要的时候才加上，在事务结束时才释放
    • 最佳实践：如果在一个事务中需要锁住多行，将最容易造成锁冲突、最影响并发度的锁申请放在后面
    • 死锁检测
      • 超时机制参数 innodb_lock_wait_timeout 的默认值是 50s
      • 主动死锁检测（等待图），参数 innodb_deadlock_detect （默认是 on
        • 在热点行更新的场景下，需要耗费大量的 CPU 资源
        • 解决方法
          • 在保证不会出现死锁的情况下，可以临时将死锁检测关掉
          • 控制并发度（客户端做并发控制 或 拆分行数据减少锁冲突

相关推荐