从sql server 到mysql_从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

提供了全文检索,压缩,空间函数等特性.

不支持事务和行级锁. 且无法做到崩溃后恢复.

加锁与并发. 读取时对表加共享锁, 写入时对表加排它锁.

适合场景: 对于只读数据, 或者表比较小, 能够忍受修复操作.

引擎选择的因素: 事务, 备份, 崩溃恢复, 特有的特性.