---

Mysql架构

• 最上层负责处理连接处理，授权认证和安全等
• 第二层负责查询解析,分析，优化，缓存以及所有的内置函数，所有跨存储引擎的功能都在这层实现: 存储过程，触发器，视图。
• 第三层包含了存储引擎，负责MySQL中数据的存储和提取，服务器规定了一组通用的API接口，不同的存储引擎底层实现可以不同，但是都要实现相关接口才可以。存储引擎包含了十几个底层函数，主要负责处理: 事务的开始，根据主键提取记录等，但存储引擎不会去解析SQL。

---

优化与执行

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

用户可以通过特殊的关键字提示优化器，影响它的决策过程，也可以请求优化器解释(explain)优化过程的各个因素，使用户知道服务器是如何进行优化决策的，便于进行参考，并进行查询重构等手动优化操作。

优化器不关心底层的存储引擎是哪种，但是存储引擎会影响查询的优化，优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息,例如: 某些存储引擎的某种索引可能对特定的查询有优化。

对于select查询语句，在解析查询前，服务器会先检查查询缓存，如果有缓存对应的插件，那服务器就直接返回查询缓存中的结果集，而不需要执行查询解析，优化和执行的整个过程了。

---

并发控制

无论何时，只要有多个查询在同一时刻修改数据，都会产生并发控制问题

下面我想从两个层面来剖析MySQL是如何处理并发控制的，这两个层面分别是: 服务器层和存储引擎层

读写锁

MySQL中有两种类型的锁，分别是共享锁(读锁)，排它锁(写锁)。

读锁是共享的，而写锁会阻塞其他的写锁和读锁。

在实际的数据库系统中，每时每刻都在发生锁定，当用户在修改一部分数据的时候，MYSQL会通过锁定防止其他用户读取同一个数据，大多数时候，MYSQL锁的管理都是透明的。

---

锁粒度

在给定的资源上，锁定的数据量越少，系统并发度越高。

表锁

表锁是MYSQL中最基本的锁策略,并且是开销最小的策略。

表锁会锁定整张表,一个用户再对表进行写操作前，先需要获得写锁，这会阻塞其他用户对当前表的读写操作，而在灭有写锁的时候，用户才能在读取的时候获得读锁，读锁之间是不会相互阻塞的

写锁比读锁有更高的优先级，因此一个写锁请求可能会被插入到锁队列中读锁的前面

尽管存储引擎可以管理自己的锁，MYSQL本身还是会使用各种有效的表锁来实现不同的目的。例如：服务器会为ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制

---

行级锁

行级锁可以最大程度支持并发操作，但同时也带来了最大的锁开销。

在innoDB和XtraDB以及一些其他的存储引擎中实现了行级锁，行级锁只在存储引擎层实现，而MySQL服务器层没有实现，服务器层完全不了解存储引擎中的锁实现

---

事务

事务就是一组原子性的SQL语句，事务内的语句要么全部执行成功，要么全部执行失败

事务有ACID四大特性:

• 原子性: 一个事务必须被视为一个不可分割的最小工作单元，事务内的语句要么全部执行成功，要么全部执行失败
• 一致性: 如果在事务执行过程中，事务中的一条sql语句报错，那么因为事务没有提交，因此事务中所做的修改不会保存到数据库中
• 隔离性：一个事务做出的修改，在最终提交前，对其他事务不可见
• 持久性:事务一旦提交，做出的修改就会保存在数据库中

一个实现了ACID的数据库往往比没有实现ACID的数据库需要更好的CPU处理能力和磁盘空间

对于一些只需要查询的业务，可以选择非事务型的存储引擎来获得更高的性能，即使存储引擎不支持事务，也可以通过LOCK TABLES语句来为应用提供一定程度的保护

---

隔离级别

• 读未提交: 事务中做出的修改，在没有提交的情况下，对其他事务也是可见的。这样会导致其他事务可以读取到当前事务修改但是还未提交的数据，这也被称为脏读。
• 提交读: 事务中做出的修改，直到提交后，才会对其他事务可见。但是会导致其他事务在当前事务提交事务前后，两次查询结果的不同，这也被称为不可重复读。
• 可重复读: 保证了同一个事务中多次读取同样的记录，结果都是一致的。但是如果其他事务在当前事务读取某个范围内的记录时，在该范围内又插入一条新记录，那么当前事务在插入动作发生后，会读取到该新插入的记录，也被称为幻行，即幻读
• 可串行化:强制事务串行执行，避免了幻读的发生，但是该级别会在读取每一行记录时，都上锁，所以会导致大量的超时和锁争用问题。

---

死锁

当两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源时，会产生死锁现象

举例:

#事务1:
start transaction;
update dhy set name="xpy1" where id=1;
update dhy set name="xpy2" where id=2;
commit;

#事务2:
start transaction;
update dhy set name="xpy2" where id=2;
update dhy set name="xpy1" where id=1;
commit;

同时执行第一条更新语句后，双方在尝试去执行第二行，发现资源被对方锁定，造成死锁

解决死锁方式有很多，例如: 检测到死锁，返回一个错误，或者锁等待超时后放弃锁

上面的解决方法不够优雅，innoDB的做法是: 将持有最少行排它锁的事务进行回滚

死锁产生一般有两种原因: 1.真正的数据冲突 2.存储引擎执行方式不同导致的

大多数情况下，只需要重新执行因为死锁回滚的事务即可

---

事务日志

事务日志可以提高事务的效率，使用事务日志，存储引擎在修改表的数据时，只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到硬盘。

事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序IO，而不是像随机IO需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对较快。

事务日志持久后，内存中被修改的数据在后台可以慢慢刷回磁盘，现在大多数存储引擎都是这样实现的，我们称之为"预写式日志"，修改数据需要写两次磁盘。

数据的修改已经写到了事务日志并持久化，但是数据本身还没有写回磁盘，此时系统崩溃，存储引擎可以在重启后自动恢复这部分修改的数据项。

---

MYSQL中的事务

自动提交

MYSQL默认采用自动提交，即如果不是显式开启一个事务，那么每个查询都会被当做一个事务提交操作，可以通过AUTOCOMMNIT变量来启用或者禁止自动提交模式。

set AUTOCOMMNIT=1或者ON :启用事务,默认值
set AUTOCOMMNIT=0或者OFF :关闭事务

当AUTOCOMMIT值为0的时候，所有的查询都是在一个事务中，直到显式地执行COMMIT或者ROLLBACK回顾，该事务结束，同时开启一个新事务

修改AUTOCOMMIT的值对于非事务类型的表，例如:MyISAM或者内存表来说，没有影响。

有些命令会在执行前，强制当前事务提交，例如会导致大量数据改变的操作，比如ALTER TABLE,LOCK TABLE等

我们可以通过

set session transaction isolation level read committed 

来设置隔离级别，新的隔离级别会在下一个事务开始的时候生效，可以在配置文件中设置整个数据库的隔离级别或者当前会话的隔离级别

---

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

MySQL服务器层不管理事务，事务是通过下层的存储引擎实现的，所以在同一个事务中，使用多种存储引擎是不可靠的。

如果在事务中混合使用了事务型和非事务型的表，会导致事务在回滚的时候，非事务型上的变更无法撤销，进而导致数据库处于不一致的状态，因此需要为每张表选择合适的存储引擎。

---

隐式和显示锁定

InnoDB采用的是两阶段锁定协议，在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK时，才会被释放，并且所有的锁是在同一时刻被释放的。

前面描述的锁定都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。

MYSQL支持LOCK TABLES和UNLOCK TABLES语句，这是在服务器层面实现的，和存储引擎无关，他们有自己的用途，但是不能代替事务处理，如果需要使用到事务，还是应该选择事务型存储引擎。

LOCK TABLES的显示使用，不但没必要，还会拖垮性能，InnoDB的行级锁工作性能更好。

建议除了事务中禁用了AUTOCOMMIT外，可以使用LOCK TABLES，其他任何时候，都不要显示使用LOCK TABLES,不管用的是什么存储引擎。

---

多版本并发控制

多版本并发控制(MVCC)是行级锁的一个变种，他可以在很多情况下避免加锁操作，开销更低。

虽然实现机制有所不同，但是大多都实现了非阻塞读操作，写操作也只锁定必要的行。

MVCC通过保存数据在某个时间点的快照实现，不管需要执行多长时间，每个事物看到的数据都是一致的，根据事物开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的

不同存储引擎对MVCC实现是不同的，主要有以下两种：

• 乐观并发控制
• 悲观并发控制

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

每开始一个新的事务，系统版本号都会自增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

下面看一下在可重复读的隔离级别下，MVCC具体是如何操作的:

• SELECT

1.InnoDB只会查询版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始前已经存在，要么是事务自身插入或者修改过的
2.行的删除版本要么未定义，要么大于当前事务的版本号，这可以确保事务读取到的行，在事务开始前未被删除。
只有符合上面两个条件的记录，才能返回作为查询结果

• INSERT

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

• DELETE

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

• UPDATE

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

MVCC只在可重复读和读提交下面生效，其他两个隔离级别都和MVCC不兼容，因为读未提交总是读取到最新的数据行，而不是符合当前事务版本的数据行，而串行化则会对所有读取的行都加锁

---

存储引擎

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

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

可以通过

show table status like 表名

命令显示表的相关信息。

下面重点对InnoDB引擎和MyISAM引擎做一下介绍，当然也只是介绍，不会深入讲解，后面系列会深入进行剖析

---

InnoDB存储引擎

InnoDB是MYSQL默认存储引擎，他被设计用来处理大量短期的事务，短期事务大部分都是正常提交且很少回滚的。

InnoDB的性能和自动奔溃恢复特性，使得它在非事务存储的需求也很流行。

• InnoDB的数据存储在表空间，表空间是由InnoDB管理的一个黑盒子，由一系列数据文件组成。
• InnoDB可将每个表的数据和索引存放在单独的文件中。
• InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离接级别，默认为可重复读，并且通过间隙锁来防止幻读发生。

间隙锁使得InnoDB不仅仅可以锁定查询涉及行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

• InnoDB基于聚簇索引建立，聚簇索引对主键查询有很高的性能，不过他的二级索引(非主键索引)中必须包含主键列，所以如果主键列很大的话，其他所有的索引都会很大，因此，主键应当尽可能的小为好。

InnoDB其他特性这里暂时不讲，后面深入分析时再聊

---

MyISAM存储引擎

在MYSQL 5.1版本之前，MYISAM是默认的存储引擎，MYISAM提供了大量的特性，包括全文索引，压缩，空间函数等，但其不支持事务和行级锁，而且故障后不能安全恢复,所以建议还是使用INNODB，除非表只读很少写，并且可以忍受修复操作，可以考虑使用

MYISAM最大的问题在于锁的粒度，MYISAM对整张表加锁，而不是针对行

这里对MYISAM的新特性就不展开讲解了

---

转换表引擎

alter table mytable engine = InnoDB

上面的语法适合于所有存储引擎，但是这个命令会非常耗时，因此MYSQL需要将数据从原表复制到一张新表中，在复制期间可能会消耗系统所有的I/O能力，同时原表上会加上读锁，因此我们可以采用手动进行表的复制方法，来作为一个替代方案。

转换表的引擎后，将会失去和原本引擎相关的特性，例如: INNODB—>MYISAM—>INNODB会导致INNODB表上面的所有外键丢失

下面介绍两种转换表引擎的方式

---

导入和导出

通过mysqldump将数据导出到文件，然后修改文件中的create table语句的存储引擎选项，并修改表名。

注意:mysqldump会自动在create table前面加上drop table语句,不注意这点，可能会导致数据丢失

---

创建和查询

先创建一个新的存储引擎的表，然后利用insert…select语法来导数据

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

如果数据量很大，可以考虑分批处理，针对每一段数据进行事务提交，避免大事务产生过多的undo

start transaction;
insert into innodb_table select * from myisam_table where id between x and y;
commit; 

操作完成后，得到的是原表的全量复制而来的新表，如果有必要，可以考虑对原表加锁，确保原表和新表的数据一致。