Mysql学习笔记--架构与历史

一.逻辑架构

存储引擎不解析SQL(InnoDB除外，会解析外键定义，Mysql服务器本身无此功能)，不同存储引擎之前不会通信，只是简单的相应上层服务器的请求。

Mysql会优化查询，创建内部数据结构（解析树），然后对其优化，包括重写查询、决定表的读取顺序、选择合适的索引等。可以通过特殊的关键字(hint)影响优化过程，可以使用(explain)查看服务器是如何进行优化的。

对于SELECT语句，在解析查询之前会检查查询缓存，若有缓存直接返回结果集。

二.并发控制

读锁：共享，不阻塞

写锁：独享，阻塞其他读锁与写锁

表锁：Mysql中最基本的锁策略，开销最小。

行锁：只在InnoDB和XtraDB等引擎实现，MySQL服务器层没有实现。服务器层完全不了解存储引擎中的锁实现。

三.事务

事务是一组原子性的SQL查询，或者说是一个独立的工作单元。

事务需要系统实现ACID。

A:原子性

一个事务必须被视为一个不可分割的最小工作单元。

C:一致性

数据库的一致性，即在事务最终提交前，不会修改数据。

I:隔离性

一个事务在最终提交前，对其他事务是不可见的。

D:持续性

一旦提交，所做的修改会永久保存到数据库中，即使系统崩溃，数据也不会丢失。

隔离级别：

• READ UNCOMMITTED 未提交读

       事务中的修改，即使没有提交，对其他事务也是可见的。

       脏读：事务可以读取为提交的数据。

• READ COMMITTED 提交读

       一个事务开始时，只能看见已提交的事务所做的修改。也可叫做不可重复读，因为两次执行同样的查询，可能得到不同的结果。

• REPEATABLE READ 重复读

      解决了脏读的问题。

     保证了在同一事务中多次读取同样的记录结果是一样的。无法解决幻读。

     幻读：当某个事务在读取某一范围记录时，另一个事务又在此范围插入新的记录，之前事务再次读取时，会产生幻行。

     InnoDB和XtraDB通过MVCC解决了此问题。

     此级别为MySQL默认事务隔离级别。

• SERIALIZABLE 可串行化

     最高隔离级别。通过强制事务串行执行，避免幻读。可能导致超时和锁争用问题。

死锁：两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

MySQL中事务：

自动提交，若未显示的开始一个事务，每个查询都被当作一个事务提交。

SHOW VARIABLES LIKE 'AUTOCOMMIT';       #  查看当前自动提交状态  1/ON为开启;0/OFF为关闭
SET AUTOCOMMIT=1;                       #  设置自动提交状态
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; # 设置事务隔离级别，下次事务生效

当AUOTCOMMIT为0时，所有查询都在一个事务中，直到显式的COMMIT或ROLLBACK，该事务结束，同时又开启一个新的事务。修改AUTOCOMMIT对非事务型表无影响。

有一些命令在执行前会强制COMMIT当前活动事务，如ALTER TABLE,LOCK TABLE等

同一事务中事务不同存储引擎的数据表是不可靠的。

InnoDB采用两阶段锁定协议：事务中随时随时可以执行锁定；事务提交或回滚时释放所有锁。

四.MVCC

通过保存数据在某个时间点的快照来实现。不管需要执行多长时间，每个事务看到的数据是一致的。只在REPEATABLE READ和READ COMMITTED两个隔离级别生效。

InnoDB的MVCC是通过每行记录后的两个隐藏列来实现的。一个保存行的创建时间，一个保存行的过期时间(或删除时间)。存储的不是时间值，而是系统版本号。没开始一个新的事务，系统版本号就会递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。在REPETABLE READ隔离级别下，MVCC如此操作：

• SELECT

     InnoDB根据一下两个条件检查每行记录：

1. 只查找早于当前事务版本的数据行，也就是行的系统版本号小于或等于事务版本号，确保事务读取的行，要么是在事务开始前已存在，要么是事务自身修改或插入；
2. 行的删除版本要么未定义，要么大于当前事务版本，确保读取到的行在事务开始之前未被删除。
   

• INSERT

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

• UPDATE

      插入新行，保存当前系统版本号作为行版本号；同时保存当前系统版本号到老行作为删除标识。

• DELETE

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

五.存储引擎

• InnoDB

     支持事务，行级锁；被设计用来处理大量短期事务；自动崩溃恢复；采用MVCC处理高并发；默认隔离级别为REPEATABLE READ，通过间隙锁防止幻读；基于聚簇索引，对主键查询有很高性能；二级索引(非主键索引)必须包含主键索引，如果主键索引很大，其他索引也会很大；存储格式平台独立；支持热备份；

• MyISAM

     不支持事务；表级锁；支持全文索引、压缩、空间函数等；崩溃后无法完全恢复；MYD存储数据，MYI存储索引；使用myisampack对表进行打包，如需修改需要先解压；压缩后占用空间减小，支持索引；延时写入，将输入写入内存中，等待系统写入磁盘；

• 内建其他引擎

1. Archive  只支持INSERT和SELECT，使用zlib压缩插入行，比MyISAM表IO更少；SELECT会全表扫描；适合日志和数据采集类应用；支持行级锁和专用缓冲区，实现高并发插入；在一个SELECT开始后，会阻止其他查询；不支持事务；
2. Blackhole 没有实现任何存储机制；丢弃所有插入数据，不做保存；服务器会记录Blcakhole日志，可用于复制数据库或只是记录到日志；
3. CSV 支持把CSV文件当作数据表处理；不支持索引；在数据库运行时可将CSV文件拷入或拷出；将CSV文件复制到数据库目录，即可用MySQL处理，对CSV文件的处理，其他软件也可立即读取到；
4. Federated  访问其他MySQL服务器的一个代理，会创建一个到远程服务器的客户端链接，将查询传递到远程执行，并获得结果集；默认禁用
5. Memory  所有数据存储于内存中；数据库崩溃，数据丢失；支持hash索引，查询速度快；表级锁，并发写入性能低；不支持BLOG/TEXT列，每行长度固定；即使指定了VARCHAR也会转为char造成空间浪费；内部使用临时表为Memory，若超出限制或含有BLOB/TEXT列，会转成MyISAM
6. Merge  MyISAM的变种；Merge表是由多个MyISAM表合并而来的虚表；用于日志或数据仓库类应用；引入分区功能后，此引擎被放弃；
7. NDB  集群存储引擎

• 引擎的选择 事务；备份；崩溃恢复；特有特性
  

转换表引擎：

ALTER TABLE table1 ENGINE=InnoDB;