从SQL Server到MySql(1) : MySql 基础知

用了多年的SQL Server 后, 最近开始转用Mysql 作为主要的数据库. 此系列文章记录了此间学到了知识和经验.

作为开篇, 首先介绍的是MySql 的一些基础知识.

1. MySql 逻辑架构

MySq 存储引擎最与众不同的特性是它的存储引擎架构. 这种架构将查询处理(Query Processing) 以及其它系统任务(Server Task)和数据的存储/提取 进行了分离.

这样存储和处理相分离的设计, 可以在使用时根据性能,特性,以及其它需求来选择数据存储的方式.

例如, 可以主表使用InnoDB, 而用MyISAM作为缓存表的引擎, 将会得到更小的索引暂用空间, 并且可以做全文搜索.

从逻辑上, MySql 架构分为三层: 连接/线程处理层; 核心服务层; 存储引擎.

1.1 连接/线程处理层

类似大多数基于网络的C/S 工具或服务. 盖层主要负责连接处理, 授权认证, 安全等等.

每个客户端连接在服务器进程中拥有一个线程. 服务器会负责缓存线程, 从而不需要频繁新建销毁.

1.2 核心服务层

包含查询解析, 分析, 优化, 缓存以及所有的内置函数.

该层同时实现了所有跨存储引起的功能: 存储过程, 触发器, 视图等.

1.3 存储引擎

• 负责数据的存储和提取.
• 除了InnoDB会解析外键定义(因为MySql 服务器本身没有实现) 外, 存储引起不会去解析SQL.
• 核心服务层通过存储引擎API与存储引擎通信. 该API使得不同存储引擎的差异对上层的查询过程透明.
• 不同的存储引擎间, 不会互通信, 而只是简单地响应上层服务器的请求.

2 并发控制时的锁

• 锁粒度的选择, 是在锁的开销和数据的安全性之间寻求平衡.
• 多数数据库会在表上施加row-level 锁, 并采用手段保证锁较多时的性能.
• MySql 每种存储引擎都可以实现自己的锁策略和锁粒度.

3 MySql 中的事务

• MySql 提供了两种事务型的存储引擎: InnoDB和NDB Cluster.
• MySql 默认采用自动提交(AutoCommit)模式. 若不是显式地开始一个事务,则每个查询都被当做一个事务执行提交操作.
• 对于MyISAM或内存表, 没有Commit 或Rollback 的概念. 相当于一直处于AutoCommit 模式.
• 有一些命令, 在执行之前会强制执行Commit 提交当前的活动事务. 典型的例子, 在DDL中, 如果是会导致大量数据改变的操作, 如Alter Table, Lock Tables.
• MySql 服务层不管理事务, 事务由下层的存储引擎实现. 如果在同一事务中,使用多种存储引擎是不可靠的.

3.1 隐式和显式锁定

• InnoDB采用的是两阶段锁定协议. 在事务执行过程中, 随时都可以执行锁定. 锁只有在执行Commit 或者RollBack 时才会在同一时刻释放.
• InnoDB 会根据隔离级别在需要时自动加锁.
• 另外,也支持 Lock tables 和UnLock Tables语句来进行显式加锁.
• 并且, MySql 在服务层也实现了Lock tables 和UnLock Tables的支持. 但并不能替代事务处理. 如需用到事务, 还是应该选择事务型存储引擎.

3.2 事务日志

• 存储引擎在修改表数据时, 只需修改其内存拷贝, 再把该修改行为记录到持久在硬盘上的事务日志中, 而不用每次都将修改数据本身持久到硬盘.
• 采用追加的方式. 写日志是磁盘上一小块区域内的顺序I/O, 速度更快.
• 日志持久化后, 内存中修改的数据, 在后天慢慢地刷回硬盘.

事务日志也被成为预写式日志(Write-Ahead Logging). 修改数据需要两次写磁盘.
若在数据还未写回磁盘时崩溃, 会在重启后自动修复该部分修改的数据.

3.3 多版本并发控制(MVCC)

• 通过保存数据在某个时间点的快照来实现.
  • 在每行记录上保存两个隐藏的列, 行的创建时间和过期(删除)时间. 时间值是系统版本号.
  • 每开始一个事务, 系统版本号递增.
  • 事务开始时刻的系统版本号作为事务的版本号, 用来和查询到的每行记录的版本号进行比较.
• 使多数读操作不需要加锁. 但需要额外的空间和检查工作.
• 对隔离级别的支持.
  • 只支持Repeatable Read和Read Committed隔离级别.
  • Read unCommitted 总读取最新的数据, 而不是当前事务版本的数据行.
  • Serialisable 会对所有读取的行都加锁.

4 MySql 存储引擎

4.1 存储

• MySql 使用文件系统的目录和文件保存数据库(Schema)和表的定义.
• 库是数据目录的子目录.
• 表会有同名.frm文件保存表的定义.
• 所以, 大小写敏感性和具体的平台密切相关.

4.2 InnoDB

• 数据存储在表空间(tablespace)中, 表空间是由InnoDB管理的黑盒子, 由一系列的文件组成.
• 采用MVCC 来支持高并发. 并且实现了四个标准的隔离级别.
• 表基于聚族索引建立的. 对铸件查询有很高的性能, 但是二级索引中必须包含主键列. 所以主键列应尽可能的小.
• 适合场景: 处理大量的短期事务(多数情况下正常提交,很少被回滚), 支持自动崩溃恢复.

4.3 MyISAM

• 提供了全文检索,压缩,空间函数等特性.
• 不支持事务和行级锁. 且无法做到崩溃后恢复.
  • 加锁与并发. 读取时对表加共享锁, 写入时对表加排它锁.
• 适合场景: 对于只读数据, 或者表比较小, 能够忍受修复操作.
• 引擎选择的因素: 事务, 备份, 崩溃恢复, 特有的特性.