MySQL——逻辑架构知识梳理与精华点睛

MySQL逻辑架构

最上层不是MySQL独有的 负责连接处理、授权认证、安全等等 

第二层是核心服务功能，包括查询解析、分析、优化、缓存以及所有的内置函数，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等

第三层包含存储引擎。服务器通过API与存储引擎进行通信。存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信。

第一步：连接管理与安全性

第二步：优化与执行

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

优化器并不关心表使用的是什么存储引擎，但是优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。

对于SELECT语句，在解析查询之前，服务器会先检查查询缓存

并发控制

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

读写锁

锁粒度

表锁

开销最小

写锁可以插入到锁队列中读锁的前面

行级锁

最大程度地支持并发处理，带来最大的锁开销

在InnoDB和XtraDB中实现了行级锁

行级锁只在存储引擎层实现

事务

MySQL提供了两种事务型的存储引擎：InnoDB和NDBCluster

自动提交

如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。

在DDL中，如果是导致大量数据改变的操作，比如ALTER TABLE，还有LOCK TABLES等其他语句，也会导致强制执行COMMIT提交当前的活动事务

MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别，隔离级别会在下一个事务开始的时候生效

在事务中混合使用存储引擎

在同一个事务中，混合使用多种存储引擎是不可靠的

如果该事务需要回滚，在非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态

隐式和显式锁定

InnoDB采用两阶段锁定协议

隐式锁定：事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK才会释放

显式锁定：不属于MySQL范围，通过特定的语句

eg：SELECT ... LOCK IN SHARE MODE

SELECT ... FOR UPDATE

MySQL也支持LOCK TABLES和UNLOCK TABLES，这是在服务器层实现的

多版本并发控制（MVCC）

可以认为是行级锁的一个变种，在很多情况下避免了加锁操作，开销更低

通过保存数据在某个时间点的快照实现。根据事务开始的时间不同，每个事务对于同一张表，看同一时刻看到的数据可能是不一样的。

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的

一个保存行的创建时间，一个保存行的过期时间（或删除时间），存储的是系统版本号

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

在可重复读隔离级别下：

SELECT：

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

a.只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，事务开始前以及存在，或者事务自身插入或者修改过的

b.行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除

INSERT：

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

DELETE：

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

UPDATE：

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

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

MySQL存储引擎

在文件系统中，MySQL将每个数据库保存为数据目录下的一个子目录。

创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。表的定义是在MySQL服务层统一处理的

SHOW TABLE STATUS

 

 

InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎

InnoDB的数据存储在表空间，表空间由一系列的数据文件组成

InnoDB可以将每个表的数据和索引存放在单独的文件中

InnoDB采用MVCC来支持高并发，实现了四个标准的隔离级别，其默认级别是可重复读，并且通过间隙锁策略防止幻读的出现，不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

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

InnoDB的存储格式是平台独立的，可以将数据和索引文件从Intel平台复制到PowerPC或者Sun SRARC平台

InnoDB通过一些机制和工具支持真正的热备份

MyISAM存储引擎

MyISAM5.1及之前的版本，MyISAM是默认的存储引擎。

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁。缺陷是崩溃后无法安全恢复。

对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以使用MyISAM

MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名

MyISAM可以存储的行记录数，一般受限于可用的磁盘空间

MyISAM对整张表加锁，读取时加共享锁，写入时加排他锁

MyISAM支持全文索引

创建MyISAM表的时候，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘

如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。压缩表可用极大地减少磁盘空间占用，减少磁盘IO。压缩表也支持索引，但索引也是只读的。

其他存储引擎

Archive引擎：

只支持INSERT和SELECT操作，每次SELECT都需要全盘扫描，适合日志和数据采集类应用。

支持行级锁和专用的缓冲区，可以实现高并发的插入。不是一个事务型的引擎。

Blackhole引擎：

没用实现任何的存储机制，会丢弃所有插入的数据，不做任何保存。

在一些特殊的复制架构和日志审核时发挥作用

CSV引擎：

将普通的CSV文件作为MySQL的表来处理，但这种表不支持索引

CSV引擎可用在数据库运行时拷入或者拷出文件

如果将数据写入到一个CSV引擎表，其他的外部程序也能立即从表的数据文件中读取CSV格式的数据

作为一个数据交换的机制，非常有用

Federated引擎：

Federated引擎是访问其他MySQL服务器的一个代理，它会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据

提供了一种很好的跨服务器的灵活性

Memory引擎：

适用于需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系

所有的数据都保存在内存中，不需要进行磁盘I/O

Memory表的结构的重启以后还会保留，但数据会丢失

适用场景：

• 用于查找或者映射表，例如将邮编和州名映射的表
• 用于缓存周期性聚合数据的结果
• 用于保存数据分析中产生的中间数据

Memery表支持Hash索引，因此查找操作非常快

Memory表是表级锁，因此并发写入的性能较低

不支持BLOB或TEXT类型的列，并且每行的长度是固定的，即使指定了VARCHAR列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费‘

如果MySQL在执行查询的过程中需要使用临时表来保存中间结果，内部使用的临时表就是Memory表

Merge引擎：

Merge表是由多个MyISAM表合并而来的虚拟表

NDB集群引擎：

被称为MySQL集群，NDB集群存储引擎，作为SQL和NDB原生协议之间的接口，MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing的，容灾的、高可用的NDB数据库的组合

选择合适的引擎

除非需要用到某些InnoDB不具备的特性，并且没有其它办法可以替代，否则都应该优先选择InnoDB引擎

如果不在乎可扩展能力和并发能力，也不在乎崩溃后的数据丢失问题，却对InnoDB的空间占用过多比较敏感，这时候选择MyISAM

事务：

如果需要事务支持，选择InnoDB

只需要SELECT和INSERT操作，选择MyISAM

备份：

如果需要在线热备份，选择InnoDB

崩溃恢复：

选择InnoDB

日志型应用：

这一类的应用对插入速度有很高的要求，数据库不能成为瓶颈。

MyISAM或者Archieve存储引擎合适，他们开销低，插入速度快

读多写少的业务：

不介意MyISAM的崩溃问题的话，选择MyISAM

但是不要轻易相信”MyISAM比InnoDB快“，InnoDB用到聚簇索引，可以让MyISAM望尘莫及

订单处理：

首先支持事务，其次考虑存储引擎对外键的支持，选择InnoDB

大数据量：

很多InnoDB数据库的数据量在3~5TB之间，这是单台机器上的量

如果数据量持续增长到10TB以上的级别，可能就需要建立数据仓库。Infobright是MySQL数据仓库最成功的解决方案。

转换表的引擎

三种方法：

ALTER TABLE：

mysql> ALTER TABLE mytable ENGINE = InnoDB；

使用任何存储引擎，但是执行时间长。MySQL会按行将数据从原表复制到一张新的表中，复制期间消耗系统的IO能力，同时原表上会加上读锁

如果转换表的存储引擎，将会失去和原引擎相关的所有特性。如果将一张InnoDB表转换为MyISAM，再转换回来，原InnoDB表上的外键将丢失

导出与导入：

1.使用mysqldump工具将数据导出到文件

2.修改文件中CREATE TABLE语句的存储引擎选项，注意同时修改表名，一个数据库中不能存在相同的表名

3.注意mysqldump默认会自动在CREATE TABLE语句前加上DROP TABLE语句

创建与查询：

先创建一个新的存储引擎的表，然后利用INSERT SELECT语法来导入数据

数据量不大的话，这样做很好。数据量大的话，考虑做分批处理

有一个pt-online-schema-change工具，可以简单方便的执行上述过程