MySQL总结-概述

Mysql服务器逻辑架构

最上层：连接/线程处理，提供连接处理、授权认证、安全等服务（与其他客户端/服务器C/S架构类似）

第二层：包含了MySql大多数的核心服务功能，包括查询解析、分析、优化、缓存以及所有内置函数（日期、时间、数学、加密……），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。

最下层：存储引擎，存储引擎负责Mysql中数据的存储和提取，上层通过存储引擎API与存储引擎通信，这些API屏蔽了不同存储引擎之间的差异，执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。除了InnoDB外，存储引擎不会解析SQL（InnoDB会解析外键定义，因为MySql服务器本身没有实现这一功能），不同存储引擎之间不会相互通信。

连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核中执行。

并发读写

在处理并发读写时，可以通过共享锁（Shared Lock，也叫读锁read lock）和排它锁（Exclusive Lock，也叫写锁write lock）来控制。

读锁是共享的，读锁之间互不阻塞，多个客户端可以在同一个时刻读取同一个资源而互不干扰；写锁是排它的，写锁会阻塞其它写锁或读锁。这样可以保证在某个时刻只有一个用户能执行写入。锁策略就是在锁的开销和数据安全性之间寻求平衡，大多数商业数据库都是在表上施加行级锁（row-level lock），并以多种方式实现，以便在锁比较多的情况下提供尽可能好的性能。MySql则提供了更多的选择，MySql存储引擎可以实现自己的锁策略和锁粒度。

表锁（table lock）是MySql中最基本的锁，也是开销最小的锁，它会锁定整个表。写操作（插入、删除、更新）时必须先获取写锁，这会阻塞其它用户对该表的所有读写操作。写锁比读锁有更高的优先级，写锁请求可能会被插入到读锁队列前面。除了存储引擎可以管理自己的锁外，MySql本身也会使用表锁来实现不同的目的，例如Alter Table时MySql会使用表锁。行锁(row lock)可以最大程度地支持并发处理，同时也带来了最大的锁开销（InnoDB和XtraDb）中实现了行锁，行锁只在存储引擎中实现，MySql服务器中没有实现。

事务

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果MySql能够成功执行该组查询的全部语句就执行该组查询。如果其中有任何一条语句因为崩溃或其它原因无法执行，那么所有语句都不会执行。也就是说，事务内部的语句要么全部执行成功，要么全部执行失败。

事务的ACID属性

原子性（atomicity），一个事务必须被视为不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能执行其中的一部分操作。
一致性（consistency），系统总是从一个一致性的状态转换到另一个一致性的状态，例如转账时转入的数目必须与转出的数目相等。
隔离性（isolation），一个事务所做的修改在最终提交前对其他事务是不可见的。
持久性（durability），一旦事务提交，事务所做的修改就会永久保存到数据库中。

实现ACID需要更多的系统开销，可以根据业务是否需要事务处理来选择合适的存储引擎，对于一个不需要事务的查询类应用，选择一个非事务型的存储引擎可以获得更高的性能。即使存储引擎不支持事务，也可以通过LOCK TABLES为应用提供一定程度的保护。

隔离级别

READ UNCOMMITTED（未提交读），事务中的修改，即使没有提交，对其它事务也都是可见的。读取未提交的数据被称为脏读（dirty read），该级别的性能并不会比其它级别高，实际应用中很少使用。
READ COMMITTED（提交读），一个事务从开始直到提交前，所做的任何修改对其它事务都是不可见的。该级别也叫不可重复读（nonrepeatable read）。因为在并发修改的情况下，执行两次（写操作提交前后）同样的读操作，可能得到不一致的结果。（大多数数据库系统的默认隔离级别）
REPEATABLE READ（可重复读），该级别解决了脏读和不可重复读的问题，但是不能解决幻读（Phantom READ）问题，即某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入新的记录（幻行Phantom），不可重复读问题可以通过加锁解决，但是幻读问题不能通过加锁解决，因为无法对新插入的记录加锁。InnoDB和XtraDBM通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读问题。（MySql的默认隔离级别）
SERIALIZABLE（可串行化），Seriablizable是最高的隔离级别，它通过强制事务串行执行，避免了脏读、不可重复读和幻读问题。Serializable会在读取的每一行数据都加锁，可能导致大量的超时和锁争用问题，实际很少采用。

死锁

死锁是指2个或者多个事务占有已申请的资源并并相互申请持有对方资源的情况，当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。InnoDB解决死锁问题的方法是：将持有最少行级排它锁的事务进行回滚，然后重新执行。

事务日志

使用事务日志，存储引擎在修改表的数据时只需要修改其内存的拷贝，再把修改行为记录持久到硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，写日志的操作是在磁盘上的小块顺序区域内，所以采用事务日志会快得多。事务日志持久后，内存中被修改的数据在后台可以慢慢刷回到磁盘。目前大多数存储引擎都是这样实现的（预写式日志，Write-Ahead Logging），修改数据需要写磁盘两次。

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，系统发生崩溃，存储引擎在重启时就能够自动恢复这部分修改的数据。

自动提交

Mysql默认采用自动提交（AUTOCOMMIT）模式，也就是说，如果不显式地开始一个事务，则每次查询都被当做一个事务执行提交操作。还有一些命令在执行之前会强制执行COMMIT提交当前的活动事务。例如数据定义语言（DDL）中的ALTER TABLE。

多版本并发控制MVCC

MVCC是行级锁的一个变种，但在很多情况下避免了加锁操作，开销更低。MVCC可以提供基于某个时间点的快照,使得对于事务看来,总是可以提供与事务开始时刻相一致的数据,而不管这个事务执行的时间有多长.所以在不同的事务看来,同一时刻看到的相同行的数据可能是不一样的。
为了实现mvcc, innodb对每一行都加上了两个隐含的列,其中一列存储行的创建时间,另外一列存储行的过期时间. 但是innodb存储的并不是绝对的时间,而是与时间对应的数据库系统的版本号,每当一个事务开始的时候,innodb都会给这个事务分配一个递增的版本号,所以版本号也可以被认为是事务号.对于每一个”查询”语句,innodb都会把这个查询语句的版本号同这个查询语句遇到的行的版本号进行对比,然后结合不同的事务隔离等级,来决定是否返回该行。

SELECT
对于select语句，只有同时满足了下面两个条件的行，才能被返回:
- 行的被修改版本号小于或者等于该事务号
- 行的被删除版本号要么没有被定义,要么大于事务的版本号:行的删除版本号如果没有被定义,说明该行没有被删除过;如果删除版本号大于当前事务的事务号,说明该行是被该事务后面启动的事务删除的,由于是repeatable read隔离等级,后开始的事务对数据的影响不应该被先开始的事务看见,所以该行应该被返回.
INSERT
　　对新插入的行,行的更新版本被修改为该事务的事务号
DELETE
　　对于删除,innodb直接把该行的被删除版本号设置为当前的事务号,相当于标记为删除,而不是实际删除
UPDATE
　　在更新行的时候,innodb会把原来的行复制一份到回滚段中,并把当前的事务号作为原来行的删除版本号

存储引擎

InnoDB

InnoDB(in-no-db)采用采用MVCC来支持高并发，并且实现四个标准的隔离级别，默认隔离级别为REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读，间隙锁使得InnoDB不仅仅锁定涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

InnoDB表基于聚簇索引建立，对主键查询有很高的性能，不过它的二级索引（secondary index非主键索引）中必须包含主键列。如果表的索引很多的话，主键应当尽可能的小。

MyISAM

MyISAM（my-z[ei]-m）不支持事务和行级锁，而且崩溃后无法安全恢复。MyISAM设计简单，在写少读多的情况下，速度很快。默认情况下应该选择InnoDB，只要在需要快速读取并且不需要事务支持和崩溃恢复的情况下才考虑使用MyISAM。

Noob