《高性能mysql总结》

一、mysql架构

1. mysql逻辑架构

mysql服务器逻辑架构图
（1）最上层的服务并不是Mysql所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。主要负责连接处理，身份验证，安全性等。
（2）第二层架构师Mysql的核心部分，大多数mysql的核心服务都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数，同时，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
（3）第三层包含了存储引擎，存储引擎负责mysql中数据的存储和提取。存储引擎不会去解析sql，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求。

（1）连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个cpu核心或者cpu中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。
当客户端连接到mysql服务器时，服务器需要对其进行认证，认证基于用户名，原始主机信息和密码。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询权限。

（2）优化与执行

mysql会解析查询，并创建内部数据结构，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。
优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询时有影响的，优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。
对于select语句，在解析查询之前，服务器会先检查查询缓存，如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

2. 并发控制

（1）读写锁
如果某个客户正在读取邮箱，同时另外一个用户试图删除编号为25的邮件，会产生什么结果？
结论是不确定，读的客户可能会报错退出，也可能读取到不一致的邮箱数据。
解决这类经典问题的方法是并发控制，在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁（S锁）和排他锁（X锁），也叫读锁和写锁。
a. 读锁
读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一资源，而互不干扰。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不是加X锁，知道T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
b. 写锁
写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
（2）锁粒度
一个提高共享资源并发性的方式就是让锁定对象更有选择性，尽量只锁定需要修改的部分数据，而不是所有的资源。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。
a.表锁
表锁是mysql中最基本的锁策略，并且是开销最小的策略，它会锁定整张表。一个用户在对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表得所有读写操作。
b.行锁
行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。在InnoDB和XtraDB，以及其他一些存储引擎中实现了行级锁。

3. 事务

如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该语句。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行，也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。
（1）事务的ACID
ACID表示原子性、一致性、隔离性和持久性，一个运行良好的事务处理系统，必须具备这些标准特征。
a. 原子性：
一个事务必须被视为一个不可分割的最小工作单元，整个事务的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
b. 一致性：
数据库总是从一个一致性的状态转换到另外一个一致性的状态。即如果事务执行过程中系统崩溃，也不会有任何损失，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。
c. 隔离性：
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。具体要看数据库引擎的隔离级别来定。
d. 持久性：
一旦事务提交，则其所做的修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。
（2）事务的隔离级别
a. 未提交读：
在该隔离级别，从字面意思上便可以看出，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。除非真的有非常必要的理由，在实际应用中一般很少使用。
例如，用户A向用户B转账100元，对应的SQL命令如下：

 update account set money = money + 100 where name =’B’;  (此时A通知B)
 update account set money= money - 100 where name= ’A’;

当只执行第一条SQL时，A通知B查看账户，B发现钱确实到账了(脏读)，而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B再次查看账户时就会发现钱其实并没有转。

b. 提交读：
在该隔离级别，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。
这个级别有时候也叫做不可重复读，因为两次执行同样的查询，可能会得到不一样的结果。大多数数据库系统默认的隔离级别都是提交读，但mysql不是。

c. 可重复读：
该级别保证了同一个事务中多次读取同样的记录的结果是一致的。可重复读解决了脏读的问题，但是无法解决幻读的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，便会产生幻行。InnoDB和XtrDB存储引擎通过多版本并发控制解决了幻读的问题。可重复读是mysql的默认隔离级别。
幻读和不可重复读都是读取了另一条已经提交的事务，所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

d. 可串行化：
可串行化是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读。该隔离级别会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。
（3）死锁
死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象，当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。
为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。

4. 多版本并发控制

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的，这两个列，一个保存了行的创建时间，一个保存了行的过期时间，当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事物开始时刻的版本号会作为事物的版本号，用来和查询到的每行记录的版本号进行比较。MVCC只在可重复读和提交读两个隔离级别下工作，因为不可提交读存在脏读，而串行化会加表锁，自然也不存在行的版本控制问题。
（1）insert
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

（2）update
InnoDB会插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。