Mysql

Select 是如何执行的

MySQL 按架构可以分为如下

• 客户端
• 服务器
  • 连接器
  • 查询缓存
  • 分析器
  • 优化器
• 存储引擎
• 文件系统

连接器负责维护网络连接并对客户端进行授权管理.

查询语句会先进入查询缓存进行检查, 未命中缓存, 才会进行后续步骤, 并将接入写入查询缓存. (MySQL 8.0废弃)

分析器通过词法分析, 提取select关键字, 并提取相应的表名和字段名等信息. 接着进行语法分析, 判断语句是否符合 MySQL 的语法.

优化器会分析字段的索引结构、多表关联的联结顺序, 决定最后的操作.

执行器在检查用户权限之后, 调用存储引擎的查询接口.

执行器通过存储引擎, 获得表内的数据.

存储引擎通过缓冲区和索引树, 加载数据.

Adaptive Hash Index

InnoDB会在运行时记录寻路路径较长的查询, 会在内存缓冲区中建立自适应索引, 作为索引的索引, 加速查询.

Join

• left join, 以左表为基础表, 结果集为所有左表和右表在ON条件上匹配的记录
• right join, 以右表为基础表, 结果集为所有左表和右表在ON条件上匹配的记录
• inner join, 无基础表, 结果集为所有左表和右表在ON条件上匹配的记录

在左连接和右连接中, ON语句只影响左表和右表的匹配结果, 若不匹配, 基础表的记录也会添加到结果集中.

如果想结果集中排除不符合条件的基础表的内容, 需要使用 Where 语句进行过滤.

Log

Redo Log

RedoLog 记录了对数据页的修改操作.

事务在提交后, 首先会将对某个数据页的写入操作, 顺序写入到 RedoLog, 再将数据写入到缓冲区.

根据刷盘策略, 缓冲区的页数据会写入到系统缓存, 最终落到磁盘上.

当系统奔溃后, 再重启时可以根据RedoLog里对数据页的写入记录, 进行恢复.

如果此时判断出现页数据错误, 可以根据Double Write Buffer在磁盘上的写入记录, 进行恢复.

不同的刷盘策略, 可能会导致不同的性能和一致性问题.

Undo Log

事务提交前, 会将数据的旧版本写入 UndoLog. 如果事务回滚或系统崩溃, 可以利用旧版本数据, 撤销未提交事务操作对数据的影响.

事务提交后, 对应回滚段里的 UndoLog记录也会删除.

对应插入操作, UndoLog 记录 PK, 回滚时直接删除对应数据.

Buffer

Log Buffer

日志缓冲区, 保存需要写入到UndoLog、RedoLog 的日志数据.

Buffer Pool

MySQL 按页读取数据, 会通过预加载磁盘上的数据, 来减少对磁盘的 IO 操作.

当读写操作发生时, 都会优先对缓冲区进行操作.

存储在缓冲区的数据页和索引页, 按照 LRU 算法进行缓存管理.

将缓冲区分为新老生代, 按照数据在其中的停留时间和访问次数, 进行移动.

新老生代的区分优化了缓存失效的问题.

依据停留时间的移动优化了因短时间内加载大量页数据导致的缓存污染.

从磁盘加载数据后, 会合并写缓冲的变更记录, 来保证数据一致性.

Change Buffer

MySQL 在写入操作时, 除了写入日志等操作外, 还会记录缓冲变更.

当下一次加载数据页到缓冲区时和缓冲变更里的记录进行合并, 保证缓冲区是最新的数据.

写缓冲也会根据刷盘策略, 被定期刷盘到写缓冲系统表空间.

写缓冲仅针对未唯一索引的优化.

当针对唯一索引进行写入操作时, 必须从磁盘将页数据加载到缓冲池, 而不是仅记录写缓冲变更.

Double Write Buffer

1. 将页数据从内存拷贝到 DWB
2. 将 DWB 的内存顺序写追加到 DWB 磁盘
3. 页数据落盘

DWB 作为页数据的副本, 避免redolog落盘时系统奔溃导致页数据损坏. 如果 DWB 在步骤 2成功之前系统奔溃, 可以借助 redolog的日志来恢复. 如果 DWB 在步骤 3 时系统奔溃, 可以借助DWB在磁盘上的日志来恢复.

innoDB

innoDB 中通过 UndoLog 回滚撤销已提交的事务保证原子性. innoDB 中通过 RedoLog、Double Write Buffer 记录事务的写入操作和页数据的副本来保证持久性. innoDB 中通过锁和 MVCC 来保障隔离性.

innoDB 的逻辑存储结构如下

• Tablespace
• Segment
  • Leaf node segment
  • Non-leaf node segment
  • Rollback segment
• Extent
• Page
• Row
  • DB_TRX_ID
  • DB_ROLL_PTR
  • DB_ROW_ID

Locks

Auto-inc Locks

自增锁, 表锁. 当一个事务正在往表里插入数据时使用. 其余事务需要等待该事务结束.

Shared and Exclusive Locks

共享/排他锁, 行锁.

读锁可以共享, 写锁必须唯一.

Intention Locks

意向锁, 表锁.

Record Lock

在唯一索引上使用唯一条件进行查询时使用, 行锁.

隔离级别

Snapshot Read

除非在 select时显式加锁, 否则select查询语句执行的都是快照读, 通过回滚段的

innoDB 通过 MVCC 数据多版本控制

1. 写操作时, 克隆一份数据, 并加以版本号区分
2. 写操作直至提交
3. 读操作时, 继续读取旧版本的数据, 不需要阻塞

ReadView

在读取 UndoLog 版本链时, 通过对比 UndoLog 的trx_id和ReadView的属性, 来判断该数据是否已提交.

版本链的数据按记录顺序, 进行对比.

通过读视图的creator_trx_id来判断数据是否由当前事务更改. 通过读视图的min_trx_id来判断数据是否已经提交. 通过读视图的max_trx_id来判断数据是否在读视图生成后被修改. 通过读视图的m_ids来判断数据是否由当前仍未提交的事务修改.

RC

在每一次执行快照读的时候生成读视图.

RR

仅在第一次执行快照读的时候生成读视图, 后续复用.

Background Threads

Master Thread

核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收 。

IO Thread

• Read thread
• Write thread
• Log thread
• Insert buffer thread

Purge Thread

回收 UndoLog

Page Cleaner Thread

协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

Tips

ACID

• RedoLog可以保证写入记录不丢失, 但是无法保证页数据损坏. 需要配合 DWB 记录页数据的写入结果
• RedoLog的innodb_flush_log_at_trx_commit值可以决定数据的一致性.
• RR的隔离级别 只能解决快照读时带来的幻读问题, 无法解决当前读带来的幻读问题

Constraints

- 级联外键操作不会激活触发器

Cache

• 在较新的版本中, 通过执行mysql_reset_connection, 可以重新初始化资源, 而不会导致已有连接失效.
• innodb_log_buffer_size 增加日志缓冲区的大小可以节省磁盘 IO
• innodb_buffer_pool_size、innodb_old_blocks_pct、innodb_old_blocks_time参数决定了缓冲区的效果.
• innodb_change_buffer_max_size 放大写缓冲区, 针对写多读少的非唯一索引写入操作, 可以有效提升性能
• innodb_change_buffering 配置支持写缓冲的操作

Select

• 负向查询可能会导致索引失效.
• 索引字段允许空值, 在查询中, 结果集可能不包含索引字段为空的记录.
• or条件查询可能会导致索引实现, 可以尝试使用 union 合并结果集.
• 联合索引可以避免回表查询

Insert

• 如果手动插入自增列的值为 0或者 null, 系统会忽略并使用默认自增列的值.
• delete数据后, 自增列的值不会从头开始.

Delete

• truncate 清除表时不会激活触发器, 速度很快. delete 按行删除, 会激活触发器.
• 当表中有列被其他表作为外键时, truncate会失败. delete可以成功.

Authorization

• 对用户权限的修改, 表和列的权限, 将会在下一次请求生效
• 对用户权限的修改, 数据库的权限, 将会在下一次USE db生效
• 对用户权限的修改, 全局权限和密码的改变, 将会在下一次连接生效

Why

• or在什么时候会导致索引失效
• 负向查询什么情况下会导致索引失效
• 为什么联合索引可以避免回表