高性能MySQL

MySQL逻辑架构 1.每个客户连接在服务器进程中都拥有自己的线程，每个连接所属的查询都会在指定的某个单独线程中完成，这些线程轮流运行在某个CPU核心或CPU上。服务器负责缓存线程，因此不需要为每个新的连接重建或撤销线程。 2.MySQL会解析查询，并创建一个

MySQL逻辑架构

1. 每个客户连接在服务器进程中都拥有自己的线程，每个连接所属的查询都会在指定的某个单独线程中完成，这些线程轮流运行在某个CPU核心或CPU上。服务器负责缓存线程，因此不需要为每个新的连接重建或撤销线程。

2. MySQL会解析查询，并创建一个内部数据结构(解析树)，然后对其进行各种优化。其中包括重写查询，决定查询的读表顺序，以及选择需使用的索引等。用户可以通过特殊的关键字给优化器传递各种提示，影响它的决策过程。另外还可以请求服务器给出优化过程的各种说明，使用户可以知晓服务器是如何进行优化决策的，为用户提供一个参考基准，方便用户重写查询、架构和修改相关配置，便于应用尽可能高效地运行。

优化器并不关心某个表使用哪种存储引擎，但存储引擎对服务器的查询优化过程有影响。优化器会请求存储引擎为某种具体操作提供性能与开销方面的信息，以及表内数据的统计信息。

不过，在解析查询之前，服务器会"询问"查询缓存，它只能保存SELECT语句和相应的结果。如果能在缓存中找到将要执行的查询，服务器就不必重新解析、优化或重新执行查询，只需直接返回已有结果即可。

并发控制

1. 读锁(Read Lock)/写锁(Write Lock)：并发控制的概念很简单-在处理并发写或并发读时，系统会使用一套锁系统来解决问题。这种锁系统由两类锁组成，通常称之为 共享锁 (Shared Lock)和 排他锁 (Exclusive Lock)，或者叫 读锁 (Read Lock)和 写锁 (Write Lock)。

2. 锁粒度(Lock Granularity)：一种提高共享资源并发性的方法就是让锁定对象更有选择性。要记住只锁定部分需修改的数据，而不是所有的资源。任何时间，在给定的资源上，被加锁的数据量越小，就可以允许更多的并发修改，只要相互之间互不冲突即可。问题是加锁也会消耗系统资源。每一种锁操作，如获得锁、检查锁是否已解除，以及释放锁等，都会增加系统的开销。所谓的锁策略，就是在锁开销和数据安全之间寻求一种平衡。为此，MySQL提供了多种选择。每种MySQL存储引擎都可以实现独有的锁策略或锁粒度。

表锁(Table Lock)：开销最小的锁策略

行级锁(Row Locks)：最大并发处理的锁策略

事务

事务是一组原子性的SQL查询语句，也可以被看作一个工作单元。如果数据库引擎能够成功地对数据库应用所有的查询语句，它就会执行所有查询，但是，如果任何一条查询语句因为崩溃或其他原因而无法执行，那么所有的语句就都不会执行。也就是说，事务内的语句要么全部执行，要么一句也不执行。

ACID :

原子性(Atomicity) - 一个事务必须被视为一个单独的内部"不可分"的工作单元，以确保整个事务要么全部执行，要么全部回滚。

一致性(Consistency) - 数据库总是从一种一致性状态转换到另一种一致性状态。

隔离性(Isolation) - 某个事务的结果只有在完成后才对其他事务可见。

持久性(Durability) - 一旦一个事务提交，事务所做的数据改变将是永久的。

1. 隔离级：SQL标准定义了4类隔离级，

READ UNCOMMITTED(读取未提交内容) - 在READ UNCOMMITTED隔离级，所有事务都可以"看到"未提交事务的执行结果。在这种级别上，可能会产生很多问题，所以很少用于实际应用。读取未提交数据，也被称为" 脏读 "(Dirty Read)。

READ COMMITTED(读取提交内容) - 大多数数据库系统的默认隔离级别是READ COMMITTED(但这不是MySQL默认的！)。它满足了隔离性的早先定义。也支持所谓的" 不可重复读 "(Nonrepeatable Read)。这意味着用户运行同一语句两次，看到的结果是不同的。

REPEATABLE READ(可重复读) - 确保同一事务的多个实例在并发读取数据时，会"看到同样的"数据行。不过理论上，这会导致另一个问题： 幻读 (Phantom Read)。简单来说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的"幻影"行。InnoDB和Falcon存储引擎通过多版本并发控制机制解决了幻读问题。（MySQL的默认事务隔离级）

SERIALIZABLE(可串行化) - 最高级别的隔离级，通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，SERIALIZABLE是在每个读的数据行上加锁。在这个级别，可能导致大量的超时(Timeout)现象和锁竞争(Lock Contention)现象

多版本并发控制

MVCC(Multiversion Concurrency Control)是通过及时保存在某些时刻的数据快照，而得以实现的。这意味着同一事务的多个实例，在同时运行时，无论实例运行多久，它们看到的数据视图是一致的。而同一时间，对于同一张表，不同事务看到的数据却是不同的。

InnoDB通过为每个数据行增加两个隐含值的方式来实现MVCC。这两个隐含值记录了行的创建时间，以及它的过期时间(或者叫删除时间) 。每一行都存储了事件发生时的系统版本号，用来替代事件发生时的实际时间。每一次，开始一个新事务时，版本号都会自动递增。每个事务都会保存它在开始时的"当前系统版本" 的记录，而每个查询都会根据事务的版本号，检查每行数据的版本号。

MySQL的存储引擎

在文件系统中，MySQL会把每个数据库保存为数据目录下的一个子目录。当创建一个表时，MySQL会在和表名同名的、以.frm为后缀的文件中存储表的定义。因为MySQL使用文件系统来存储数据库名和表定义，大小写敏感将依赖于具体的平台。在Windows平台上，MySQL的实例(Instance)名、表名、数据库名都是大小写不敏感的; 在Unix类平台上，则是大小写敏感的。

1. MyISAM引擎

一般来说，MyISAM将每个表存为两个文件：数据文件和索引文件。两个文件的扩展名分别为.MYD和.MYI。MyISAM的格式是平台通用的，这意味着用户可以在不同架构的服务器上毫无问题地相互拷贝数据文件和索引文件。

MyISAM表可以包含动态行和静态行(即固定长度行)。MySQL会根据表定义决定选用何种格式。MyISAM表的可容纳的行总数，一般只受限于数据库服务器的可用磁盘空间大小，以及操作系统允许创建的最大文件大小。

MyISAM的特性：

-加锁与并发 - MyISAM对整张表进行加锁，而不是行。

-...

2. InnoDB引擎

InnoDB专为事务处理设计的一款存储引擎，特别是用于处理大量短期(Short-lived)事务，短期事务是指一般能正常完成，不需要回滚的事务。

InnoDB将所有数据共同存储在一个或几个数据文件中，这种数据文件一般称之为 表空间 。在MySQL 4.1版以及更新版本中，InnoDB也支持将每个表和相关索引存储为单独的分离文件。

InnoDB使用MVCC机制获取高并发性能，并且实现所有四个标准隔离级别。默认隔离级别是REPEATABLE READ，在这个隔离级上，使用间隙锁策略防止"幻读"问题的产生。

InnoDB表是基于 聚簇索引 建立的，能提供一种非常快速的主键查找性能。不过，它的辅助索引(Secondary Index，也就是非主键索引)也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义很多索引，则争取尽量把主键定义得小一些。InnoDB不会压缩索引。

选择合适的引擎

考虑的因素：事务，并发，备份，崩溃后恢复，特有特性

参考资料

1. 高性能MySQL

2. 什么是脏读、不可重复读、幻读

原文地址：高性能MySQL - 1.MySQL架构, 感谢原作者分享。