1.MySQL的架构与历史一览

1.1 MySQL逻辑架构   

         客户端

             ↓

     连接/线程处理

             ↓

   查询缓存  ⬅  解析器

             ↓

         优化器

             ↓

        存储引擎

1.1.1 连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，查询也在单独的线程中执行，该线程只能轮流在某个CPU核心中运行。服务器会缓存线程，因此不需要为每一个新建的连接创建或销毁线程。

1.1.2 优化与执行

解析查询，内部创建解析树，然后优化。

1.2 并发控制

1.2.1 读写锁

读锁也叫共享锁，写锁也叫排他锁。 当一个写锁在进行时，会阻塞其他的写锁和读锁。数据库系统中，每时每刻都在发生锁定。

1.2.2 锁粒度

加锁是消耗资源的，例如 获取锁，检查锁是否释放。 1.表锁，开销最小。写锁比读锁有更高的优先级，尽管存储引擎可以管理自己的锁，但MySQL本身还是会使用各种有效的表锁实现不同的目的，例如：ALTER TABLE之类的语句使用表锁，这时会忽略存储引擎的锁机制。   2.行锁，最大程度地支持并发处理，同时也带来了最大的锁开销。

MySQL InnoDB支持三种行锁定方式：
行锁（Record Lock）：锁直接加在索引记录上面。
间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。
Next-Key Lock：行锁与间隙锁组合起来用就叫做Next-Key Lock。

 

间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

　　最近用户反馈说系统老是出现insert时，等待超时了，最后发现是insert间隙锁！间隙锁是innodb中行锁的一种， 但是这种锁锁住的却不止一行数据，他锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读，但是它会把锁定范围扩大，有时候也会给我们带来麻烦，我们就遇到了。 在数据库参数中， 控制间隙锁的参数是：

　　innodb_locks_unsafe_for_binlog，这个参数默认值是OFF， 也就是启用间隙锁， 他是一个bool值， 当值为true时表示disable间隙锁。

　　那为了防止间隙锁是不是直接将innodb_locaks_unsafe_for_binlog设置为true就可以了呢？ 不一定！而且这个参数会影响到主从复制及灾难恢复， 这个方法还尚待商量。

　　间隙锁的出现主要集中在同一个事务中先delete后 insert的情况下， 当我们通过一个参数去删除一条记录的时候， 如果参数在数据库中存在，那么这个时候产生的是普通行锁，锁住这个记录， 然后删除， 然后释放锁。如果这条记录不存在，问题就来了， 数据库会扫描索引，发现这个记录不存在， 这个时候的delete语句获取到的就是一个间隙锁，然后数据库会向左扫描扫到第一个比给定参数小的值，向右扫描扫描到第一个比给定参数大的值， 然后以此为界，构建一个区间， 锁住整个区间内的数据， 一个特别容易出现死锁的间隙锁诞生了。

 

1.3 事务

ACID: 原子（最小单元），一致（数据一致），隔离（事务之间不可见），持久（事务被永久保存到数据库）。

1.3.1 隔离级别

1.未提交读 ： 事务没有提交对其他事务也可见（脏读）

2.提交读：也叫不可重复读，但两次同样的查询可能结果不一样（幻读：某个事务读取某范围内的记录时，另一个事务插入新记录，之前的事务再次读取该范围的记录时产生幻行）

3.可重复读(MySQL默认级别) ：理论上，还是无法解决幻读的问题。 InnoDB和XtraDB通过多版本并发控制MVCC解决幻读的问题。

4.可串行化：读取的每一行都加锁，导致大量的超时和锁抢占的问题。

1.3.2 死锁

相互锁定占用。

为了解决这个问题，数据库实现各种死锁检测，死锁超时机制。InnoDB处理死锁的方式：持有最少行级排它锁的事务进行回滚（死锁回滚算法）

1.3.3 事务日志

提高事务效率，存储引擎改表数据时，只需要修改其内存拷贝，再把行为记录持久到硬盘的事务日志。写日志操作是磁盘上一小块区域内的顺序I/O，相对快很多。

事务日志持久后，内存修改的数据会慢慢刷回到磁盘。称为：预写式日志，修改数据需写两次磁盘。

1.3.4 MySQL中的事务

隐式和显式锁定：锁只有在执行COMMIT或者ROLLBACK的时候才会释放，并且所有锁都是在同一时刻被释放，InnoDB会根据隔离级别在需要的时候自动加锁

另外，InnoDB也支持通过特定的语句显式锁定，这些语句不属于SQL规范

SELECT ... LOCK IN SHARE MODE

SELECT ... FOR UPDATE

MySQL也支持 LOCK TABLES 和 UNLOCK TABLE语句，这是服务器实现的，和存储引擎无关。另外，除了事务中禁用了AutoCommit以外，其他任何时候，任何存储引擎都不要使用LOCK TABLES.

1.4 多版本并发控制

MVCC没有一个统一的实现标准，可以认为MVCC是行级锁的一个变种，但在很多情况下避免了加锁操作，所以开销更低，且大都实现了非阻塞的读操作，写操作也只有锁定必要的行。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。不同存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制。

InnoDB的MVCC，通过每行记录后面保存两个隐藏的咧来实现的。一个保存行的创建时间，一个保存行的过期时间（或删除时间），当然存储的并不是实际的时间值，而是系统版本号(system version number)。

每开始一个新的事务，系统版本号都会自动递增。

SELECT：

a. InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样就确保了行，要么事务开始前已经存在，要么事务自身插入或修改过的。

b.行的删除版本要么未定义，要么大于当前事务版本号。

INSERT：

InnoDB为新插入的每一行保存当前系统版本号作为行版本号

DELETE:

InnoDB为删除的每一行保存当前系统版本号作为删除标识

UPDATE:

InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

MVCC只在 可重复提交 和 读提交 这两个隔离级别下工作

1.5 MySQL的存储引擎

SHOW TABLE STATUS LIKE 'user' \G 查看表相关信息

1.5.1 InnoDB存储引擎

InnoDB表是基于聚族索引建立的。聚族索引对主键查询有很高的性能，不过它的二级索引（非主键索引）中必须包含主键列。

1.5.2 MyISAM存储引擎

支持全文索引、压缩、空间函数GIS等，不支持事务和行级锁，崩溃无法安全恢复。

1.5.3 MySQL内建的其他存储引擎

Archive引擎，只支持INSERT和SELECT,适合存日志和数据采集类应用。

Blackhole引擎:会丢弃所有插入的数据，适用只记录Blackhole表的日志。

CSV引擎:处理CSV文件，把CSV作为MySQL表来处理，不支持索引。

Federated引擎：代理。

Memory引擎：重启后，表结构保留，数据会丢失。表级锁。

Merge引擎：MyISAM的一种变种，大概适用于数据仓库。

NDB集群引擎：share-nothing、容灾、高可用的NDB数据库组合。

1.5.4 第三方存储引擎：

OLTP类引擎

XtraDB引擎：InnoDB的改进版，支持InnoDB所有查询。

TokuDB引擎：分形树的索引数据结构，大数据存储引擎。

RethinkDB：为固态硬盘设计的，写时复制B树(copy on write B-Tree)

Falcon存储引擎：下一代存储引擎。

面向列的存储引擎

Infobright:最有名的列式存储引擎。数10TB时，工作良好，作为数据仓库。

社区存储引擎

Aria:

Groonga:

OQGraph:

Q4M:

SphinxSE:

Spider:

VPForMySQL:

1.5.5 选择合适的引擎

1.5.6 转换表的引擎

ALTER TABLE mytable ENGINE = InnoDB;  将数据从原表复制大新表，消耗系统所有I/O，原表加读锁。 

导出导入 mysqldump

创建与查询CREATE和SELECT

另外还可以： Percona Toolkit提供了一个 py-online-schema-change的工具

1.6 MySQL时间线

1.7 MySQL的开发模式