1.1 MySQL 逻辑架构

1.1.1
1.1.2 优化与执行

  • MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定表的读取顺序、选择合适的索引等。

  • 用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程。

  • 可以请求优化器解释（explain）优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置。

对于select语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），如果能找到对应查询，服务器就不必再执行查询解析、优化和执行整个过程。

1.2 并发控制

MySQL在两个层面的并发控制：服务器层与存储引擎层

1.2.1 读写锁 1.2.2 锁粒度

  • 读写锁
  • 锁粒度

1.3 事务

A（atomicity）C（consistency）I（isolation）D（durability）

1.3.1 隔离级别 READ UNCOMMITTED （未提交读） READ COMMITTED （读提交） REPEATABLE READ （可重复读） SERIALIZABLE （可串行化）

1.3.2 死锁 数据库实现了各种死锁检测和死锁超时机制

InnoDB目前处理死锁的方法是：将持有最少行级排他锁的事务进行回滚。

死锁发生后，只有部分或完全回滚其中一个事务，才能打破死锁。对于事务型的系统，这是无法避免的，所有应用程序在设计时必须考虑如何处理死锁。

1.3.3 事务日志

  • 事务日志可以帮助提高事务的效率，不必每次都将修改的数据本身持久化到磁盘。
  • 事务日志采用的是追加的方式，写日志操作是磁盘上一小块区域的顺序I/O，比每次修改都写回数据库对应磁盘位置可能导致的随机I/O要快
  • 事务日志持久化后，可以慢慢地刷回磁盘。

以上这种设计，被称为预写式日志 （Write-Ahead Logging）

隐式和显式锁定
InnoDb采用的是两段锁协议（two-phase locking protocol）
事务执行的过程中，随时都可以锁定，但是只有在commit和rollback时同时释放。

InnoDB支持显示锁定：（非标准SQL）

1
2

select ... lock in share mode 
select ... for update

1.4 多版本并发控制 （MVCC）

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然不是实际的时间值，而是版本号（system version number）。每开一个新的事务，系统版本号都会自动递增。

  • select
    • InnoDB只查找版本小于等于于当前事务版本的数据行
    • 行的删除版本要么未定义，要么大于当前版本号
  • insert
    • InnoDB为新插入的每一行保存当前系统版本号作为行版本号
  • delete
    • 保存当前系统版本号作为行删除标识
  • update
    • 新插入一行，保存当前系统版本号为行版本号，同时保存当前系统版本号到原来的行作为删除标识

保存这两个额外的系统版本号，使得大多数读操作都可以不用加锁
MVCC只在可重复读和读提交两个隔离级别下工作

1.5 MySQL的存储引擎

在文件系统中，MySQL将每个数据库保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。

不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在MySQL服务层统一处理。

show table status

1.5.1 InnoDB 存储引擎
InnoDB采用MVCC来支持高并发，实现了四个标准的隔离级别，其默认的隔离级别是可重复读，并通过间隙锁策略防止幻读的出现。 （间隙锁防止幻影行的插入）

InnoDB表是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能，它的二级索引（secondary index 非主键索引） 中必须包含主键列。如果主键列很大的话，其他的索引都会很大，所以如果索引很多，那么主键应该尽可能的小。

InnoDB的存储格式是平台独立的。

InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引（adaptive hash index），以及能够加速插入的插入缓冲区（insert buffer）。

支持热备份

MyISAM引擎 Memory引擎

Archive引擎 Blackhole引擎 CSV引擎

选择存储引擎的方法：

  • 事务
  • 备份
  • 崩溃恢复
  • 特性

场景：

  • 日志型应用
  • 只读或者大部分情况下只读的表
  • 订单处理
  • 电子公告牌和主题讨论论坛

1.5.6 转换表的引擎 alter table ； 适用于任何存储引擎，但是需要执行很长的时间。

如果转换表的存储引擎，将会失去和原引擎相关的所有特性（如InnoDB转MyISAM然后再转InnoDB将会失去原来所定义的外键）

导出与导入 mysqldump

创建与查询

1
2
3

create table innodb_table like myisam_table;
alter table innodb_table engine=innodb;
insert into innodb_table select * from myisam_table;

如果表大，则分批导入，避免大事务产生过多的undo