MySQL基本概述

MySQL是非常灵活的一款数据库，虽然它不是绝对完美，但它的灵活足够适应很多高要求的环境。为了发挥MySQL的性能并很好的使用它，我们就得先了解其设计。MySQL的灵活主要体现在我们可以通过不同的配置使他在不同的硬件上都能运行的很好。但是MySQL最重要，与种不同的特性是它的存储引擎架构，这种架构将查询处理及其他系统任务和数据的存储/提取相分离。

1.MySQL的逻辑架构

如上图，我们可以简单的将其逻辑架构分为3层

(1)网络接口层:主要负责接受连接，并读写连接对应的内容，这点和我们平时所写的网络服务器的I/O接口层基本相似
(2)请求处理层:如上图所示，该层主要是对请求SQL语句进行解析，解析之后或是进一步对请求的SQL语句进行优化，或是直接在缓存中找到请求的内容
(3)存储引擎层:该层主要负责数据的提取和存储

(1)连接管理

MySQL的每个客户端连接都会在服务器拥有一个线程，该线程的查询只会在这个单独的线程中去执行。服务器会负责缓存该线程，因此不需要为每个新连接创建或销毁线程(线程池)

(2)优化与执行

MySQL会解析查询，并创建内部数据结构(解析树)，然后对其进行各种优化，包括重写查询，决定表的读写顺序，以及选择合适的索引等。用户可以通过explain来查看查询优化过程，从而可知服务器是如何进行查询优化决策的。需要注意的是优化器并不需要知道存储引擎是什么，但不同的存储引擎对优化是有影响的

2.并发控制

(1)读写锁

当我们的多个用户同时对数据库中的一张表进行读操作，这并不会产生什么问题。但是如果此时有一个进程在修改表中的某条数据，这就会产生不可预料的事情了。为了防止类似的事情发生，MySQL采用了俩中锁:共享锁和排他锁。这俩中锁从概念上就很好理解，共享意思持有这些锁的线程互相不阻塞，对应此处就是一张表几个线程可以同时读。排他则意味着只要有一个线程获得该锁，则其他的所有线程无论读写都会被阻塞

(2)锁粒度

能够提高并发性的一种方式就是让锁的对象更具有选择性。尽量只锁定真正需要修改的数据，而不是有关的无关的都锁。锁定的资源(临界区)越少，则并发度也就越高，只要相互之间不发生竞争即可。刚才的说法其实只是一种理想，真实环境下，我们创建锁，加锁，解锁，销毁锁也都会也一定的代价，那么你把锁的对象缩小，对应的锁粒度就会增加，锁粒度一旦增加，那么大量的加锁，解锁操作是不是会降低性能。所以我们是不是因该根据实际的应运场景来在这俩者中寻找出最适合该场景的一个平衡点?

MySQL的各种引擎支持如下俩种锁策略

表锁:表锁是MySQL中最基本的策略，并且是开销最小的策略。表锁会锁定整张表，一个用户对表进行写操作(插入，删除，跟新)前，需先获得写锁，这样就会阻塞其他用户对表进行的所有操作。只有没有写锁时，其他用户才能获得该表的读锁，读锁之间是不阻塞的上文有提到的
行锁:行锁可以最大程度的支持并发处理(也同时带来了最大的锁开销)InnoDB支持的就是行锁，这里所说的锁都是只在存储引擎中的实现，服务器是完全不了解存储引擎的锁实现的

3.事务

对数据库而言事务就是一组原子的SQL查询(注意这里所所的查询可不单指select哦)，或者说是一个独立的单元。如果数据库引擎能够成功的对数据库进行该组内的全部查询语句，那么就执行该组查询。如果有一条不能执行(例如程序崩溃导致)，那么所有的语句都不会执行

事物具有ACID特性，分别如下

原子性:一个事务必须被视为一个不可分割的最小单位，整个事务的操作要么全部提交成功，要么失败回滚
一致性:数据库总是从一个一致性的状态转换到另一个一致性的状态
隔离性:通常来说在一个事务所做的修改在最终提交之前，对其他事务是不可见的
持久性:一旦事务提交，则其所做的修改就会永久保存

就像锁粒度的升级会增加系统开销一样，这种事务处理过程中额外的安全性，也同样会使数据库做更多的工作。一个实现ACID的数据库比没有实现的要复杂太多。所以MySQL提供多种引擎，用户可根据需求在不需要事务特性的使用中选择其他引擎

(1)事务的隔离级别

隔离其实是很复杂的，在SQL中定义了4种隔离级别，每一种级别都规定了一个事务所做的修改，哪些事务和事务间是可见的哪些是不可见的。较低级别的隔离可以执行更高的并发，系统的开销也更低

几种隔离级别

未提交读:事务中的修改即使没有提交，对其他事务也都是可见的。事务可以读取为提交的数据，这也被称为脏读。这种级别性能也就那样，但却会导致很多问题
提交读:大多数数据库默认的就是该级别。一个事务开始时只能看到已提交的事务。换句话说，一个事务从开始到提交前，所做的任何修改对其他事物都是不可见的
可重复读:解决了脏读问题，但是却无法解决幻读问题(是指，当某个事务读取某个范围内的记录时，另一个事务又在该范围内插入了新数据，当该事务在此读取该范围内的数据时，会产生幻行)InnoDB通过MVVC解决了幻读问题
可串行化是最高级别的隔离，它通过强制事务串行化执行，避免了前面所说的幻读问题

(2)死锁

当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。

实例如下

如上图中的事务1和事务2所示，如果俩事务都先执行完了第一条UPDATE操作，那么当分别同时在执行第二条时，就会陷入死锁状态即相互等

为了解决上述问题InnoDB通过检测死锁循环依赖，并立即返回一个错误。这种方式很有效，否则死锁会导致查询变的非常慢。InnoDB目前处理死锁的方法是，将持有最少行级锁的事物进行回滚(这是相对比较简单的死锁回滚算法)

(3)事务日志

先假设这样一个问题，如果让你来设计日志持久化，你会咋样做，没插入一个数据，就把它写如磁盘对应该数据的位置么？要知道，插入的数据可不是随便插入的，你得保证你插入的数据就在磁盘对应该表的准确位置，而不是像我们平时写文件一样直接把数据写到文件尾部就可以了。所以如果采用你的方式对数据库文件进行持久化的话，我想效率也太低了，每次在磁盘I/O就不知要阻塞多长时间(需要在磁盘的多个地方移动磁头)。所以MySQL持久化数据写把数据写到内存，然后通过往事务日志尾部添加进行第一步持久化。日志持久化(这一步是备胎)之后，我们就可以慢慢的在后台把内存中的数据持久化到磁盘中去了(这一步才是真正的持久化)，此时即使机器死机，也可以在重启之后通过事物务日志来恢复未真正持久化的数据。所以MySQL的数据持久化会经历俩次磁盘I/O

(4)MySQL中的事务

自动提交

MySQL提供了俩中事务型的存储引擎:InnoDB和NDB Cluster。事务型的存储引擎默认采取自动提交(AUTOCOMMIT)模式。每个查询都会默认被当做一个事务执行提交操作

读者可以用上述命令来查看自己的存储引擎是否默认采取自动提交
其中ON表示开启，OFF表示关闭

混合使用存储引擎

这个其实是非常不可取的，如果你混合使用了事务和非事务型的存储引擎，那么当需要事务回滚时该咋办?

隐式和显示锁定

InnoDB采用俩阶段锁协议。在事务执行过程中随时加锁，在COMMIT或ROLLBACK时将所有锁同时全部释放，这是隐式锁定。当然你也可以用SQL语句进行显示锁定

多版本并发控制

之前在讲隔离级别时有个幻读的概念，那么如何解决此问题呢？正是这里的多版本控制，这里有个对杭机锁控制的专业名词MVCC，InnoDB的MVCC是通过在每行记录后面保存俩个隐藏的列来实现的，这俩个列一个保存创建时的版本号，另一个保存过期或删除的版本号。每次开始一个新的事务这些版本号都会自增一次。事务开始时刻的版本号会作为事务的版本号，用来和查询的每行记录做比较

表一
表明为Test

行号 用户列1 用户列2 行版本号 删除版本号 行1 a1 b1 0 0 行2 a2 b2 0 0 行3 a3 b3 0 0

假设事务1先开始给其一个系统版本号(就是默认的每一行对应隐藏列的所有号，而不是单独的每个号)(即快照1)
事务1所有执行的事务为
“`
(1)delete from Test where 用户列2 = b2;

事务2开始给其的系统号快照2(假设同时获得的快照此时快照1.2是相同的其实)事务2所要执行的事务为

(2)select *from Test;
“`

执行(1)之后真实表变为如下

行号 用户列1 用户列2 行版本号 删除版本号 行1 a1 b1 0 0 行2 a2 b2 0 1 行3 a3 b3 0 0

(2)
事务2执行查询操作时，每一步都会和其快照的版本号进行比对

1.只查找小于或等于当前事务版本号的行
行的删除大与该事务系统版本号的不找

根据上述俩规则，则而的执行结果如下

行号 用户列1 用户列2 行版本号 删除版本号 行1 a1 b1 0 0 行3 a3 b3 0 0

(5)MySQL的存储引擎

InnoDB是MySQL默认引擎，也是最重要最广泛的存储引擎。它被用来处理大量短期事务，短期事务大部分情况下是正常提交，很少会被回滚。InnoDB的性能和自动崩溃恢复的特性，使得它在非关系型的存储需求中也很流行。除非有特别的需求，负责因该优先考虑InnoDB引擎

InnoDB的概述

InnoDB数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。现在版本的InnoDB可以将每个表的数据和索引存放在单独的文件中。InnoDB采用MVCC来支持高并发，并且通过间隙锁来防止幻读
InnoDB是基于聚簇索引建立的，聚簇索引对主键的查询会有很高的性能。不过它的二级索引中必须包含主键列，所以如果主键列很大的话，其他的索引都会很大。因此若表上的索引较多的话，主键应当尽可能的小
InnoDB内部做了很多优化，包括从磁盘读取数据时预判性读，能够自动在内存中建立hash，以及能够加速插入操作的插入缓冲区等
InnoDB通过一些机制和工具支持热备份

MyISAM存储引擎

MyISAM使用全文索引，不支持事务和行锁等，它的缺陷就是不能安全恢复在对只读数据或者表比较小，可以忍受修复的操作，则可以使用MyISAM
MyISAM会将数据和索引存在俩个表中
MyISAM特性
加锁与并发:其对整张表加锁，读时加读锁，写加写锁，但是在读时也可以插入，这就是其并发插入
修复:可以手动执行简单的修复
对于MyISAM表，即使是text等长字段也可以选其前500字节做索引

MyISAM的性能
MyISAM设计简单，数据以紧密格式存储，所以在某些场景下性能很好