color: #0077bb; /* 将标题改为蓝色 */
}
mysql基础概念笔记 part1
mysql 基础概念 基础原理，逻辑架构，事务，并发控制，读写锁
1、前言
作为一个运维工程师，mysql怎么强调其重要性都不为过，因此mysql的基础概念应该好好地梳理一下。 毕竟基础不牢地动山摇。
1.1mysql简介
1、MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle（被收购） 2、MySQL是目前最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational DatabaseManagement System，关系数据库管理系统) 应用软件之一。 3、 MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 4、MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。 5、MySQL 软件采用了双授权政策：它分为社区版和商业版。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。由于其社区版的性能卓越，搭配 PHP 和 Apache（nginx） 可组成良好的开发环境
2、mysql简单历史
简单来说，MySQL数据库的发展可以概括为三个阶段： 1、初期开源数据库阶段。 2、Sun MySQL阶段。 3、Oracle MySQL阶段。
2.1mysql与MariaDB
mysql与MariaDB的区别简而言之就是： （1）因为mysql被sun公司给收购了，而sun公司又被oracle公司给收购了，而oracle是一个商业数据库公司，创始人害怕开源的mysql变成不开源，因此用mysql的源代码创立了MariaDB。 （2）MariaDB名字来源于创始人的女儿。 （3）centos7yum源中MariaDB已经替代了mysql
3、mysql逻辑架构
下图展示了mysql的逻辑架构图： 
（1）最上层的一层结构是客户端，这并非mysql所独有，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。如授权认证，链接处理，安全等等。
（2）第二层架构包含了大多数mysql的核心服务功能，
其中包括查询、解析、分析、优化、缓存以及所有内置函数，
所有跨数据引擎的功能都在这一层实现：存储过程、触发器、视图等等。
（3）第三层包含了存储引擎。
在mysql中存储引擎负责数据的存储和提取。
每个存储引擎都有其优势和劣势。
服务器通过API与存储引擎进行通信。
这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询透明。
存储引擎API包含几十个底层模型，
用于执行诸如 “开始一个事务”或者“根据主键提取一行数据”等操作。
但是存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信，而只是简单的响应上层服务器的请求
3.1连接管理与安全性
(1)每个客户端连接都会在服务器进程中拥有一个线程， 这个连接的查询只会在这个单独的线程中执行， 该线程只能轮流在某个CPU核心或者CPU运行。 服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。 (2)当客户端连接mysql服务器时,服务器会对客户端进行认证。 该认证基于用户名、密码、原始主机信息。 一旦客户端连接成功，服务器会继续验证客户端是否有执行某些操作的权限。
3.2优化与执行
(1)mysql会解析查询，并创建内部数据结构（解析树）然后对其进行各种优化。
(2)这些优化包括：重写查询、决定表的读取顺序、选择合适的索引。
(3)优化器并不关心表使用的是什么存储引擎，但是存储引擎对优化查询有影响
(4)其中对于“SELECT”语句，在解析查询时，会先检查查询缓存（Query Cache），
如果能在其中找到结果，则服务器不执行整个查询解析、优化、执行的过程。直接从缓存中返回数据。（cache is key）
4.0并发控制
无论何时，只要有多个查询在同一时刻修改数据，都会产生并发控制的问题。
当同一时刻两个进程同时对一个文件进行操作，这时会发生什么情况？
显然文件数据会出错。
这时我们就需要通过锁来防止数据被破坏。如果一个进程试图对文件进行操作，但是这时已经又一个进程在对该文件进行操作了。
这时该文件已经被锁住，这时该进程若要对文件进行操作需等待解锁。
4.1读写锁
（1）有时候对一个文件进行并发读取也没有什么问题，因为读取不会修改数据，所以不会出错。
但是如果当一个文件正在被一个读取时，另一个用户试图对该文件进行删除操作，这时就可能会产生很多问题。
而在mysql中也有类似问题，因为mysql支持多用户并行处理。
（2）解决这些问题的方法就是并发控制：在处理并发读或者并发写的的时候，可以通过实现一个有两种类型的锁组成的锁系统来解决问题。
（3）这些锁通常被称为共享锁（shared lock）和排他锁（exclusive lock）.
这些锁也可以称为读锁（read lock）和写锁（write lock） （4）读锁：读锁是共享的，也可以说是互相不阻塞，多个用户在同一时刻可以同时读取一个资源而互不干扰。
（5）写锁：写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是基于安全才这样考虑。
这样能确保在给定的时间里，只有一个用户能执行写入操作，并防止其他用户读取正在写入的同一资源。 （6）在实际的数据库系统中，每时每刻都在发生锁定，当用户修改某一个数据时，mysql会通过锁防止其他用户读取同一数据。
4.2锁粒度和锁策略
(1)封锁粒度，数据库中为了实现并发控制而采用封锁技术，封锁对象的大小称为封锁粒度（Granularity）。
(2)锁定的粒度与系统的并发度和并发控制的开销密切相关。一般地，锁定的粒度越大，需要锁定的对象就越少，可选择性就越小，并发度就越小，开销就越小；反之，锁定的粒度越小，需要锁定的对象就越多，可选择性就越大，并发度就越大，开销就越大。
(3)一种可以提升共享资源并发性的方式就是让锁对象更有选择性——即只锁定需要修改的部分数据，而不是所有资源。
(4)更理想的的方式是：只对会修改的数据片进行锁定。
(5)加锁也需要消耗资源:锁的各种操作都会增加系统开销。如果数据库系统花费大量的时间来管理锁而不是，那么数据库系统的性能可能受到影响。
(6)所谓的锁策略，就是在系统开销和安全性上求平衡，但是这种平衡也会影响到性能。 (7)大多数商业数据库并没有提供更多的锁策略供用户选择
一般都是在表上施加行级锁（row——level lock）,并以各种复杂的方式进行实现。 以便在锁比较多的情况下。
(8)而在mysql中提供了多种选择,每一种Mysql存储引擎都可以实现自己的锁策略和锁粒度。
在存储引擎的设计中，锁管理是一个非常重要的决定。 (9)将锁粒度固定在某一个级别，可以为某些特定应用提供更好地性能，但同时也会失去一些应用场景下的良好支持。
(10)幸运的是Mysql提供了支持多个存储引擎的架构，所以不需要单一的通用解决方案。以下是两种最重要的锁策略：
表锁（table lock）
行级锁（row lock）
4.2.1 表锁
（1）表锁是Mysql中最基本的锁策略，并且是系统开销最小的策略。
（2）表锁会锁定整张表。一个用户在对表进行写入操作（插入、更新等）前，先需要获得写锁。
这时mysql将会阻塞其他用户对该表的所有读写操作。 （3）只有当该用户释放写锁时，其他读取的用户才能获得读锁，读锁之间是相互不阻塞的。
（4）写锁有比读锁更高的优先级，因此一个写锁请求可能会被插入到读锁的队列的前面。
即：写锁可以插入到锁队列中读锁的前面，反之读锁不能插入到写锁的前面。
4.2.2行级锁
（1）行级锁可以最大程度的支持并发处理（同时也带来了最大的的锁开销）
（2）在innoDB和XtraDB中以及其他一些存储引擎中实现了行级锁。
（3）行级锁只在存储引擎中实现，z在Mysql服务器层没有实现。
5.0事务
（1）事务是一种原子性的SQL语句查询，或者说一个独立的工作单元。
（2）事务确保“如果数据库引擎能成功的对数据库应用该组查询的全部语句，那么就执行该组查询”
反之“如果其中有任何一条语句因为崩溃或者其他原因无法执行，则所有的语句都不会执行”
（3）总结为：事务内的语句要么全部执行成功，要不全部执行失败。
5.1 事务的重要意义
以一个经典的例子来举例事务的必要性：
假设一个银行的数据库有两张表：支票（checking）和储蓄（savings）。现在如果要从用户Jane的支票账户转移转移200美元到她的储蓄账户。
那么至少需要以下3个步骤：
（1）检查支票账户的余额是否大于200美元。
（2）如果为真则，从支票账户减去200美元。
（3）在储蓄账户余额中增加200美元。 显而易见，这3步必须打包在一个事务中，任何一个步骤失败，必须全部回滚。 假设，在执行到第3步时服务器崩溃了，这时会发生什么情况？——用户很可能会损失200美元。又或者在执行第二步和第三步之间时，另外一个进程要删除支票账户的所有余额，这时又会发生什么情况？——银行很可能白白给用户200美元。
所以除非数据库通过ACID测试，否则空谈数据并没有意义。
5.2ACID
ACID表示：原子性（atomicity）、一致性（consistency）、隔离性（Isolation）、持久性（durability）
A：原子性（atomicity）整个事务中的所有操作要么全部成功执行，要么全部失败后回滚；
C：一致性（consistency）数据库总是从一个一致性状态转换为另一个一致性状态；
I：隔离性（Isolation）一个事务所做出的操作在提交之前，是不能为其它所见；隔离有多种隔离级别；
D：持久性（durability）一旦事务提交，其所做的修改会永久保存于数据库中；
（1）事务的ACID特性保证了银行不会弄丢你的钱，但是要在应用逻辑中要实现这点非常难，一个兼容ACID的数据库系统，想要做很多复杂但用户可能并没有察觉到的工作，才能保证ACID的实现。
（2）就像锁粒度的升级会增加系统的开销一样，一个实现了ACID的数据库系统会比没有的占用更多的系统资源。
（3）mysql的优点可以根据不同的业务来选择事务型的引擎或者非事务型引擎。
5.2事务隔离级别
在SQL标准当中定义了四种隔离级别：
READ UNCOMMITTED (读未提交):zai在该级别，事务中的修改即使没提交，对其他事务来说都是可见的。事务可以读取未提交的数据，这也被称为脏读（DrityRead）；除非真的非常有必要，在实际应用中很少使用。 READ COMMITTED
(读提交):在大多数数据库系统的默认隔离级别都是该级别（Mysql不是）。该级别保证了“一个事务开始时，只能看见已经提交的事务所做的修改，换句话说，一个事务开始直到提交前，所做的任何修改对其他事务都是不可见的。但是该级别会产生不可重复读即执行同样的两次查询可能得到不同的结果”
REPEATABLE READ
(可重读):该级别解决了脏读问题，且保证了再同一个事务中多次读取同样的记录的结果是一致的即解决了不可重复读的问题。但是该隔离级别还无法解决幻读的问题。
幻读指当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时会产生幻行（Phantom
Row）,InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。 该级别是Mysql的默认事务隔离级别。
SERIALIZABILE
(可串行化):该级别是最高的事务隔离级别。他通过强制事务串行执行避免了前面说的幻读问题。简单地说该级别会在读取的每行数据上都加锁，因此可能导致带来的超时和锁争用的问题。
实际应用中很少采用该级别，只有非常需要确保数据一致性且可以接受没有并发的情况下可以使用。
5.2.1事务并发问题
脏读（Dirty Read）：
A 看到 B 进行中更新的数据，并以此为根据继续执行相关的操作；B 回滚，导致 A 操作的是脏数据。不可重复读（Non-repeatable Read）： A 先查询一次数据，然后 B 更新之并提交，A 再次查询，得到和上一次不同的查询结果。 幻读（Phantom Read）： A 查询一批数据，B 插入或删除了某些记录并提交，A 再次查询，发现结果集中出现了上次没有的记录，或者上次有的记录消失了。 第二类丢失更新 (覆盖丢失)： A 和 B 更新同一条记录并提交，后提交的数据将覆盖先提交的，通常这是没问题的，但是在某些情况下，如在程序中自增自减、程序中的读-改-全量更新，就会出现并发问题。这类问题更像是应用层面的，不属于DB范畴。
5.2.2死锁
（1）死锁是指两个或者多个事务在同一资源上互相占用，且请求锁定对方占用的资源，从而导致的恶性现象。
（2）当多个事务试图以不同的顺序锁定资源时，就会产生死锁。 （3）多个事务同时锁定同一个资源时，也会产生死锁。
（4）产生死锁的后果就是：请求的资源会会被各个事务互相锁定，然后所有事务又等待对方释放锁,同时又持有对方需要的锁，这时就陷入了恶性循环了。除非有外部因素介入才可能解除死锁。
（5）为了解决该问题，数据库系统实现了各种死锁检测机制和死锁超时机制。越复杂的系统越能检测到死锁的循环依赖，并立即返回一个错误。
还有另一种方式，就是当查询的时间达到设定的锁等待超时时间后放弃锁请求，这种方法不太好
（7）InnoDB目前处理死锁的方法是：将持有最少行级排它锁的事务进行回滚（这是相对比较简单的事务回滚算法）
（8）锁的行为和顺序是和存储引擎相关的：
1、以同样的顺序执行语句,有些引擎会产生死锁，而有些不会。
2、所以死锁的产生有两种原因：
真正的数据冲突，这个很难避免
存储引擎的实现方式 （9）死锁发生后，只有回滚部分或者完全的事务，才能打破死锁。
对于事务型的数据库系统，这是无法避免的
因此应用程序在设计时必须考虑如何处理死锁
大多数情况下只需要重新执行因死锁回滚的事务即可。
5.3事务日志
（1）事务日志可以提高事务的执行效率。使用事务日志，存储引擎在修改表的数据时就只需要xiugai修改其内存拷贝，再把该修改行为记录到硬盘上的事务日志，而不用每次都把修改的数据本身持久到硬盘上。
（2）事务日志的写入类型为“追加”，因此其操作为“顺序IO”，不需要像随机I/O一样需要在磁盘的多个地方移动磁头，因此采用asw事务日志的方式相对快多了
（3）此日志通常也被称为“预写式日志(write ahead logging)”
（4）事务日志持久后，内存中修改的数据，可以在后台慢慢刷回磁盘 （5）以上是大多数存储引擎的实现方式。
（6）如果数据的修改已经记录到事务日志并持久化，但是数据本身还没有写回磁盘，这时服务器崩溃，存储引擎在重启时能够自动恢复这部分修改的数据，具体的恢复方式视存储引擎而定。
5.4中的事务
（1）MySQL提供了两种事务型的存储引擎：InnoDB和NDB cluster.
（2）一些第三方存储引擎也支持事务，比较知名的有XtraDB和PBXT.
5.4.1 AUTOCOMMIT
（1）MySQL默认采用AUTOCOMMIT模式。 （2）当不是显式的开始一个事务，则每一个查询都被当成一个事务执行提交操作。
（3）你可以通过设置AUTOCOMMIT变量来开关该功能。
5.4.2 在事务中混合使用存储引擎
（1）MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。
（2）如果在事务中混合使用了事务型和非事务型的表，在正常提交的情况下不会有什么问题。
（3）但是，一旦事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这样非常难以修复，事务的最终执行结果将难以确定。
（4）因此严禁混用存储引擎。
5.4.3 显式锁定和隐式锁定
（1）隐式锁：当多个客户端并发访问同一个数据的时候，为了保证数据的一致性，数据库管理系统会自动的为该数据加锁、解锁，这种被称为隐式锁。隐式锁无需开发人员维护（包括锁粒度、加锁时机、解锁时机等）
（2）显式锁：当在某些特殊的情况下需要开发人员手动的进行加锁、解锁，这种锁方式被称为显式锁。对于显式锁而言，开发人员不仅要确定锁的粒度，还需要确定加锁的时机（何时加锁）、解锁的时机（何时解锁）以及所的类型。
（3）InnoDB存储引擎采用二阶段加锁协议（Two-PhaseLocking），事务中只加锁不释放，事务结束一起释放。
5.4.4 MVCC 多版本并发控制
（1）MVCC（Multi-VersionConcurrencyControl，多版本并发控制）：
为一条记录维护多个不同的snapshot，并记录各snapshot对应的版本号（事务ID），每个事务可以读到的snapshot是受限的，从而隔离其他事务的并发动作。
（2）MVCC并发控制中，读操作分为两类：
快照读 (snapshot read)与当前读 (current read)。
前者读取的是记录的snapshot(有可能是历史版本)，不用加锁；
后者读取的是记录的最新版本，且会加上锁，保证其他事务不会并发修改这条记录。
（3）快照读：
普通的select均为快照读，不用加锁
当前读：
select… lock in shared mode: 读锁
select… for update: 写锁
DML（insert/delete/update）：写锁
（4）MVCC 只工作在READ COMMITTED和 REPEATABLE READ两个隔离级别下
其他两个隔离级别都不能实现MVCC。
因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的行。
因为SERIALIZABILE会对所有的读取的行加锁。
原创文章，作者：Net17_desperado，如若转载，请注明出处：http://www.178linux.com/46558
