深入mysql（一） mysql基础底层技术

mysql的逻辑架构

最上层 的服务并不是mysql所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。例如连接处理，授权认证、安全等。

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

第三层 包含了存储引擎。存储引擎负责mysql中数据的存储和提取。和linux下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API包含几十个底层函数，用于执行诸如 “开始一个事务” 或者 “根据主键提取一行记录” 等操作。但存储引擎不会去解析SQL，不同的存储引擎之间也不会下相互通信，而是简单地响应上层服务器的请求

连接管理与安全性

每个客户端都会在服务器的进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器负责缓存线程，因此不需要为每一个新建的线程创建连接后者销毁线程。（mysql5.5或更新的版本提供一个API，支持线程池插件，可以使用池中少量的线程来服务的大量连接）

优化与执行

对于select 语句，在解析查询之前，服务器会先检查查询缓存，如果能够在其中找到对应的查询，服务器就不必在执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

并发控制

在处理并发的读或者写的时候，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为

共享锁（读锁）：所谓的读锁是共享的，或者说是互相不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰。

排它锁（写锁）：写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是处于安全策略的考虑，只有这样才能确保 在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。

在实际的数据库中，每时每刻都发生锁定，当某个用户在修改某一部分数据时，mysql会通过锁防止其他用户读取同一数据。

锁粒度

一种提高共享资源并发性的方式就是让锁定对象更具有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。

更理想的方式是只对修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高， 只要相互之间不发生冲突即可。

问题是：加锁需要消耗各种系统资源，如果花费大量时间去管理锁，而不是存储数据，那么系统的性能可能因此受到影响。

锁策略

所谓的锁策略就是在锁的开销和数据的安全性之间寻求平衡，当然这种平衡也会影响到性能。

mysql则提供了多种选择。每种mysql存储引擎都可以实现自己的锁策略和锁粒度。在存储引擎的设计汇总，锁管理是非常重要的决定

两种重要的锁策略：表锁、行级锁

表锁（TABLE LOCK）：

表锁是mysql中最基本的锁策略，并且是开销最小的策略。表锁非常类似于邮箱加锁机制，它会锁定整张表。一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他的读取用户才能获得读锁，读锁之间是不相互阻塞的。

在特定的场景中，表锁也可能有良好的性能。例如，READ LOCAL 表锁支持某些类型的并发写操作。另外写锁也比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面（写锁可以插入到锁队列中读锁的前面，反之写锁则不能插入到写锁的前面）

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

行级锁（ROW LOCK）:

行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。众所周知，在InnoDB和XtraDB,以及其他一些存储引擎中实现了行级锁，行级锁只在存储引擎层实现，而mysql服务层没有实现。服务器层完全不了解存储引擎中锁的实现。所有的存储引擎都一自己的方式实现了锁机制。

事务

说起事务来估计各位也应该都很清楚。事务 就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功的对数据库应用该组查询的全部语句，那么就一个执行该组查询。如果其中有任何一条语句因为奔溃或者其他原因无法执行，那么所有的语句有的不执行，也就是说要么全部执行，要不全部执行失败

最经典的案例莫过于转账案例，转账账户钱的扣除 和 被转账账户钱的增长的SQL 都是一起失败，一起成功的。没有说这边扣完钱了，那边增加失败了。也没有说那边增加完了，这边扣除失败了的。都是一起成功，一起失败。

可以使用 START TRANSACTION 开启一个事务
执行个各种SQL语句…
然后使用 COMMIT 将事务提交，或者使用 ROLLBACK 将事务回滚

那么这个就是事务的基本用法了，除非系统通过严格的 ACDI 测试，否则空谈事务的概念是不够的。ACDI 表示为

原子性（Atomicity）:

一个事务必须被视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交成功，要么全部回滚，对于一个事务来说，不可能只执行其中的一部分操作，这个就是事务的原子性。

一致性（Consistency）:

数据库总是从一个一致性的状态转到另一个一致性的状态，在前面的转账的例子中，一致性确保了，即使系统在减少金额或者增加金额的语句之间奔溃，账户中也不会因此亏损金额,因为事务没有提交，所以事务中所做的修改也不会报存到数据库中

隔离性（Isolation）:

通常的来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。在前面的转账例子，在系统运行到减少或者增加金额的之前的时候，有另一个程序查看这两个账户的时候，则看到其账户的还是原金额，在之后讨论 隔离级别（Isolation Level）的时候，就会知道这里为什么说的是 “通常来说”。

持久性（Durability）:

一旦事务提交，则其所做的修改就会永久保存到数据库中。即使系统崩溃，修改的数据也不会丢失。持久性是个模糊的概念，因为实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，有些则未必。而且不肯能有能做到100%的持久性保证的策略。

隔离级别

隔离级别要比想象中的复杂很多，在SQL标准中定义了四种隔离级别，没一种级别都规定一个事务事务中所做的修改，哪些在事务内和事物间是可见的，哪些是不可见的。较低的隔离级别可以执行更高的并发，系统的开销也越低。

每种存储引擎实现的 隔离级别都不尽相同。

下面介绍一下四种隔离级别

READ UNCOMMITTED（未提交读）

在 READ UNCOMMITTED 级别，事务的修改，即使没有提交，对其他的事务也都是可见的。事务可以读取未提交的数据，这也被成为 脏读（Dirty Read）。这个级别会导致很多问题，从性能上说， READ UNCOMMITTED 不会比其他的级别好太多，但缺乏其他级别的很多好处，除非有非常必要的理由，在实际应用中一般很少使用。`

READ COMMITTED（提交读）

大多数的数据库系统默认隔离级别都是 READ COMMITTED （但MySQL不是）。 READ COMMITTED 满足前面提到的隔离性的简单定义：一个事务开始时，只能 “看见” 已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做 不可重复读 ，因为两次执行同样的查询，可能得到的结果不一致

REPEATABLE READ（可重复读）

REPEATABLE READ 解决了脏读的问题。该级别也保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复度隔离级别还是无法解决另一个 幻读 的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB 和 XtraDB 存储引擎通过 多版本并发控制（mvcc） 解决了幻读的问题。(BUT,这里需要注意一下，MVCC解决的幻读只是在普通的select 语句中生效，也就是通过只读取当前事物版本和比当前事物版本低的数据，而不会读取新插入的比当前版本要高的数据，但是一旦查询升级为当前度，也就是 select * from test where id<5 ... for update 这种语句，就会触发 Next-Key Lock（间隙锁+行锁）来防止id小于5的数据插入，会索定 (0,5] ,如果最小的id为0话），也就是说对于当前读，mysql采用的是 Next-Key Lock解决的幻读

mysql间隙锁的简单介绍

SERIALIZABLE（可串行化）

SERIALIZABLE 是最高的隔离级别，它通过强制事务串行执行，避免了前面提到的幻读的问题。简单的说，SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际情况下也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发发情况下，才会考虑采用该级别

死锁

死锁指的是两个或者多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。

为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，比如 InnoDB 存储引擎，越能检测到死锁的循环依赖，并且返回一个错误。InnoDB 目前处理死锁的方法是将持有最少行级排它锁的事务进行回滚（这是相对简单的死锁回滚算法）

MySQL事务

MySQL提供了两种事务性的存储引擎：InnoDB 和 NDB Cluster。另外还有一些第三方的存储引擎也支持事务，比较知名的包括 XtraDB 和 PBXT。

自动提交（AUTOCOMMIT）

MySQL默认采用自动提交模式。也及就是说，如果不是显示地开始一个事务，则每个查询都被当作一个事务执行提交操作。

可以通过设置 AUTOCOMMIT 来启用或者禁用自动提交模式

查看

SHOW VARIABLES LIKE 'AUTOCOMMIT'

修改

SET AUTOCOMMIT =1;

1 或者 ON 表示启用，0 或者 OFF 表示禁用 当 AUTOCOMMIT=0时，所有的查询都在一个事务中，直到显示地执行 COMMIT 提交或者 ROLLBACK 回滚，该事务结束，又开始了另一个新事务。但是对于非事务型的表，比如 MyISAM 或者内存表，不会有任何影响。对这类表来说，没有 COMMMIT 或者 ROLLBACK 的概念，也可以说是相当于一直处于 AUTOCOMMIT 启动的模式。

另外还有一些命令，在执行前会强制执行 COMMIT 提交当前的事务活动。比如会导致大量数据改变的操作，比如 ALERT TABLE… ，另外还有LOCK TABLES…也会导致同样的结果

设置隔离级别

MySQL可以通过执行 SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL能够识别所有的4个隔离级别，InnoDB 引擎也支持所有的隔离级别。

多版本并发控制（MVCC）

MySQL的多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了 多版本并发控制（MVCC）。不仅是MySQL，包括Oracle、PostgreSQL等其他数据库系统也实现了MVCC，但是各自的实现机制不尽相同，因为MVCC没有一个统一的标准。

可以认为MVCC 是一个行级锁的变种，但是它在很多情况下避免了加锁的操作，因此开销更低 。虽然实现的机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC的实现，是通过保存数据某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一时刻，同一张表看到的数据可能是不一样的。如果没有之前这方面的概念，这句话理解起来就有点迷惑

不同存储引擎的MVCC实现是不同的，典型的有 乐观并发控制 和 悲观并发控制。

InnoDB的MVCC

InnoDB的MVCC，是通过每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号就会递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到每行记录的版本号进行比较。

下面来看一下 REPEATABLE READ（可重复读） 隔离级别下，MVCC具体是如何操作的。

SELECT

InnoDB 会根据以下两个条件检查每行记录：

1. InnoDB 只查找版本查找于当前事务版本的数据行（也就是，行的系统版本号小于或等于事物的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么事务自身插入或者修改过的。
2. 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前为被删除。

INSERT

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

DELETE

1. InnoDB 为删除的每一行保存当前系统版本号作为行删除标示。

UPDATE

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

保存这两个额外的系统版本号，使绝大多数读写操作都可以不用加锁。这样的设计使读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

MVCC只在 REPEATABLE READ（可重复读） 和 READ COMMITTED（提交读） 两个隔离级别下工作。其他的两个隔离级别都和MVCC不兼容，因为 READ UNCOMMITTED（未提交读） 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE（可串行化）则对所有读取的行都加锁。

MVCCC并没有正式的规范，所以各个存储引擎和数据库系统的实现都是各异的，没有人能说其他的实现方式是错误的

MySQL存储引擎

在文件系统中，MySQL将每个数据库（也称schema）保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义。

例如： 创建一个MyTable的表，MySQL就会在MyTable.frm 文件中保存该表的定义。

因为MySQL使用文件系统的目录和文件保存数据库和表的定义，大小写敏感性和和具体的平台密切相关。在Windows中，大小写是不敏感的；而在linux系统中则是敏感的。不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在MySQL服务层统一处理的。

可以使用 SHOW TABLE STATUS 命令显示表的相关信息，在MySQL5.0以后的版本中，也可以查询INFORMATION_SCHEMA中对应的表

SHOW TABLE STATUS LIKE 'user' \G;

介绍一下这些参数的含义：

name：
表名

Engine:
表的存储引擎。在旧的版本中，改了的名称叫Type,而不是 Engine

Row_format:
行的格式。对于MyISAM表，可选的值为 Dynamic、Fixed或者 Compressed。Dynamic的行长度是可变的，一般包含可别变长度的字段，如Varchar或者BLOB。Fixed 的行长度是固定的，只包含固定长度的列，如CHAR 和 INTEGER。Compressed的行则只在压缩表中的存在。

Rows
表中的行数，对于MyISAM和其他一些存储引擎，该值是精确的，但对于InnoDB，该值是估计值

Avg_row_length
平均每行包含的字节数

Data_length
表数据的大小（字节单位）

Max_data_length
表数据的最大容量，该值和存储引擎有关

Index_length
索引的大小（字节单位）

Data_free
对于MyISAM表，表示已分配但目前没有使用的空间。这部分空间包括之前删除的行，以及后续可以被INSERT利用到的空间

AUTO_increment
下一个 AUTO_INCREMENT的值

Create_time
表的创建时间

Update_time
表数据的最后修改时间

Check_time
使用CKECK TABLE 命令或者 myisamchk 工具最后一次检查表的时间。

Collation
表的默认字符集和字符列排序规则

Checksum
如果启用，保存的是整个表的实时效验集合

Create_options
创建表时指定的其他选项

Comment
该列包含了一些其他的额外信息。对于MyISAM表，保存的是表在创建时带的注释。对于InnoDB表，则保存的是InnoDB表空间的剩余空间信息。如果是一个视图，则该列包含 “VIEW” 的文本字样。

InnoDB

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

InnoDB的历史

InnoDB有者复杂的发布历史，2008年，发布了所谓的InnoDB plugin,适用于MySQL5.1版本，但这是Orcle创建的下一代InnoDB引擎，其拥有者是InnoDB而不是MySQL。MySQL默认还是选择了集成旧的InnoDB引擎。在Oracle收购了Sun公司发布的MySQL5.5值班费才彻底使用了InnoDB plugin替代了旧版本中的InnoDB。很多人在使用MySQL5.1的时候没有使用 InnoDB plugin，如果你正在使用的是MySQL5.1，一定要使用InnoDB plugin,真的比旧版本的InnoDB好的多。

InnoDB 概述

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，有一系列的数据文件组成。在MySQL4.1以后的版本中，InnoDB可以将每个表的数据和索引存储在单独的我呢间中。InnoDB也可以使用裸设备作为表空间的存储介质，但现代的文件系统使得裸设备不再是必要的选择。

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

InnoDB表是基于聚簇索引简历的，InnoDB的所有结构和MySQL其他的存储引擎有很大不同。聚簇索引对主键查询有很高的性能。不过它的二级索引（非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大，因此，若表上的所有较多的话，主键应该尽可能的小。InnoDB的存储格式是平台独立的。也就是说可以将数据和索引文件从Intel平台复制到PowerPC或者Sun SPARC平台。

InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓存区等等。

InnoDB的行为是非常复杂的，如果想了解的话建议去阅读一下官方手册中的《InnoDB事务模型和锁》。