Willson Chen

Stay Hungry, Stay Foolish.

MySQL 基础（二）

TTS

MySQL 基础（二）

锁

  • 锁是协调多线程并发访问某一资源的机制。
  • 锁机制可以保证数据并发访问的安全性，但是也会导致数据库的并发性能下降。

锁的分类

  • 写锁（排他锁）：一次只能加一个写锁，一旦加了写锁无法再加其他锁。
  • 读锁（共享锁）：可以多次加读锁，一旦加了读锁无法再加写锁。
  • 乐观锁：
    • 假设出现并发写资源的概率较低。
    • 全程不加锁，只有提交数据时，才会判断是否违反数据完整性。
    • 实现方式：对数据加版本号，写入时把之前读取的版本号作为条件同时对版本号加 1，执行后检查影响行数。
    • 如果读取后版本号发生过变更，那么将会出现写入失败。
  • 悲观锁：假设并发读写资源的概率较高，读写锁属于悲观锁。

锁的粒度

  • 全局锁：对整个数据库加锁，阻塞所有写操作，用于数据库备份等维护操作。
  • 表锁：粒度大，加锁快，不会出现死锁，并发性差。
  • 行锁：粒度小，加锁慢，会出现死锁，并发性好，包括：
    • 记录锁，锁一行记录。
    • 间隙锁，锁一段范围，不包括记录本身，用于防止范围内插入新记录。

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

日志

慢查询日志

  • 记录执行时间超过阈值的 SQL 语句。
  • 默认关闭，可以通过设置参数 slow_query_log 临时开启。
  • 默认阈值是 10s。
  • 可以用自带的 mysqldumpslow 命令分析日志。

二进制日志（Binlog）

  • 记录对数据进行修改的操作日志，用于数据恢复和主从复制。
  • 默认关闭，需要修改配置开启。
  • 格式：
    • Statement：记录写数据的原始 sql，当有函数时可能出现不一致。
    • Row：记录修改的数据，日志量较大。
    • Mixed：混合模式，根据执行的 SQL 语句选择日志记录方式。

重放日志（Redolog）

  • 记录对数据页物理改动的日志。
  • 用于数据库崩溃后的数据恢复，确保事务的持久性。
  • 与Binlog差别：
    • Binlog 是逻辑日志，效率低，理论上无限大.
    • Redolog 是物理日志，效率高，循环写.

回滚日志（Undolog）

  • 记录用于回滚的日志。
  • 对于插入，只记录主键，回滚时删除则可。
  • 对于删除和修改，除了原记录外还记录用于 MVCC 的字段。
  • 事务在快照读时，会生成一个读视图，基于回滚日志生成。

查询优化

  • 减少 select 中的字段数量，避免使用复杂查询。
  • 使用索引。
  • 优化表结构，避免可空类型，合理设置数据类型和长度。
  • 使用分区表。
  • 分析执行计划，在 sql 前加 explain，输出信息中：
    • type 列，从快到慢分别为：
    • system：系统表，不需要磁盘 IO
    • const：常量，固定值
    • eq_ref：主键或唯一索引，返回结果最多只有一行
    • ref：非唯一索引，返回结果可能有多一行
    • range：索引范围扫描
    • index：索引全扫描
    • ALL：全表扫描
    • key 列为使用的索引。
    • extra 列，包含以下信息时可能索引失效，需要优化
    • Using filesort：无法利用索引排序，使用文件排序
    • Using temporary：使用了临时表，效率较差。
    • Using index：使用了覆盖索引，效率较高。
    • rows 列表示找到记录需要读取的行数，越少越好。

分区

  • 表分区用于将表数据分成多个文件存储。
  • 只能水平拆分（按行），不能垂直拆分。

表的文件结构

  • InnoDB，一张表存储为2个文件：表结构，表数据和索引。
  • MyISAM，一张表存储为3个文件：表结构，表数据，表索引。

分区好处

  • 提升查询性能，只扫描特定分区，而不是全表。
  • 易于管理和维护，可以只处理特定分区的文件。
  • 更好的数据安全性和可用性，不同分区存储在不同的设备上，如将热数据放在高速存储上。

分区缺点

  • 增加复杂性，需要设计合理的分区策略。
  • 索引效率下降，跨分区查询效率降低。

分区表限制

  • 无法使用外键约束。
  • 分区数量有限，5.6.7 之后 最多8192 个分区。
  • 分区键必须是主键或唯一索引的部分或全部字段。

分区类型

  • RANGE：按范围分区。
  • LIST：按离散值分区。
  • HASH：按哈希值分区，分区键必须是整数。
  • KEY：类似按哈希值分区，分区键支持除 BLOB 和 TEXT 外的类型。

常见场景：按日期字段的年份分区：

create table tbl(
  id int not null,
  content varchar(255),
  created_at timestamp not null
) partition by range (year(created_at)) (
partition p0 values less than (2023),
partition p1 values less than (2024),
partition p2 values less than MAXvalue
);

分库分表

垂直分表

  • 定义：将一张表按列拆分到多张表中。
  • 何时需要垂直分表：表字段过多影响读写效率，将冷热字段拆分到不同表中。
  • 带来的问题：
    • 跨表查询复杂，需要表连接。
    • 修改多张表时需要用事务保证原子性。
    • 增加维护成本。

水平分表

  • 定义：将一张表的数据按行拆分到多张表中。

何时需要水平分表

  • 单表数据量超过 1KW 时，B+树可能超过3层导致查询时IO次数过多性能下降。
  • 一般，单表数据量超过500W 需要考虑分表。
  • 如果预估数据量会超过500W，可以提前规划分表。

如何选择分表键

  • 原则：数据均匀分布，避免触发全表扫描。
  • 查询条件尽可能利用分表键过滤。
  • 根据业务，如按时间、地区、用户ID等。

非分表键如何查询

  • 数据冗余到 ES 查询，推荐做法。
  • 遍历所有表。

分表策略（类似分区策略）

  • 范围：有利于扩容，可能存在分布不均问题。
  • 哈希取模：扩容麻烦，分布较均匀。
  • 一致性哈希：用哈希环，避免扩容时大量数据迁移。
  • 范围+哈希取模：结合两种策略。

分布式 ID

  • 分表后不能依赖表自增ID会重复，需使用分布式ID保证唯一性。
  • 雪花算法：
    • 将64位整数分成三部分：时间、机器、序列号。
    • 第 1位符号位，不用。
    • 41 位时间戳，表示毫秒级的时间，最多表示69年，需要约定开始时间。
    • 10 位机器 ID，可以部署 1024 个节点。
    • 12 位序列号，同一毫秒内最多生成 4096 个 ID。

不停服拆表

  • 加数据库访问代理层，通过配置开关决定访问新旧 DAO。
  • 读旧表，双写，新增和修改在新旧表都执行。
  • 通过脚本进行数据迁移。
  • 读新表，仍然维持双写
  • 稳定运行一段时间后再停写旧表。

分表中间件

  • 简化开发。
  • 如 Sharding-JDBC、go-orm/sharding。

分库

  • 定义：将一个库的数据拆分到多个库中。

何时需要分库

  • 单库数据量超过 5KW 时，需要拆分。

跨库表连接问题解决

  • 字段冗余，避免连接。
  • 全局表，所有库都保存一份。
  • 应用层组装。

跨库事务问题解决

  • 使用分布式事务。
  • 如2PC、3PC、TCC、SAGA 等。

集群架构

集群作用

  • 提高可用性，避免单点故障。
  • 提高性能，分摊计算压力。

主从复制

  • 主从复制是实现集群的基础。
  • 同步复制：主库必须等待从库复制完成才能返回写入成功。
  • 异步复制：主库不需要等待，只负责写入，从库负责复制。
  • 半同步复制：主库等待至少一个从库复制完成，再返回成功。
  • 并行复制：从库多线程处理数据同步，降低复制延迟。

集群模式

一主多从

  • 读写分离，读负载均衡。
  • 使用MHA（Master High Availablity），可以实现主库的故障切换。

级联复制

  • 部分从库不连接主节点，而是连接从节点复制。
  • 用于避免主从复制增加主节点负载。

双主复制

  • 互为主从，相互复制。
  • 复杂容易出现不一致，不建议使用。

多主一从

  • 用于多源复制，即汇总多个不同库的数据到一个库中。
  • 垂直或水平分库之后，可能使用此模式。

主从复制实现原理

  • 主库开启 binlog。
  • 从库两个线程，一个 IO 线程，一个 SQL 线程。
  • IO 线程请求主库 binlog，写入relay log（中继日志）。
  • SQL 线程读取 relay log，回放写入操作。
  • 主库通过 log dump 线程，给从库传 binlog。