理解MySQL架构与概念.docx

理解MySQL架构与概念.docx

《理解MySQL架构与概念.docx》由会员分享，可在线阅读，更多相关《理解MySQL架构与概念.docx（24页珍藏版）》请在冰豆网上搜索。

理解MySQL架构与概念

写在前面：

最早接触的MySQL是在三年前，那时候MySQL还是4.x版本，很多功能都不支持，比如，存储过程，视图，触发器，更别说分布式事务等复杂特性了。

但从5.0（2005年10月）开始，MySQL渐渐步入企业级数据库的行列了；复制、集群、分区、分布式事务，这些企业级的特性，使得现在的MySQL，完全可以应用于企业级应用环境（很多互联网公司都用其作为数据库服务器，尽管节约成本是一个因素，但是没有强大功能作后盾，则是不可想象的）。

虽然，MySQL还有很多不足，比如，复制、分区的支持都十分有限、查询优化仍需要改进，但是MySQL已经是一个足够好的DBMS了，更何况它是opensource的。

这段时间没有事，出于好奇，略微的研究了一下MySQL，积累了一些资料，欲总结出来。

这些资料打算分为两部分，上部主要讨论MySQL的优化，其中主要参考了《MySQLManual》和《HighPerformanceMySQL》，如果有时间，以后在下部分析一下MySQL的源码。

如果你是MySQL高手，希望你不吝赐教；如果你是新手，希望对你有用。

第一章、MySQL架构与概念

1、MySQL的逻辑架构

 最上面不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等。

中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。

同时它还提供跨存储引擎的功能，包括存储过程、触发器和视图等。

最下面是存储引擎，它负责存取数据。

服务器通过storageengineAPI可以和各种存储引擎进行交互。

1.1、查询优化和执行（OptimizationandExecution）

MySQL将用户的查询语句进行解析，并创建一个内部的数据结构——分析树，然后进行各种优化，例如重写查询、选择读取表的顺序，以及使用哪个索引等。

查询优化器不关心一个表所使用的存储引擎，但是存储引擎会影响服务器如何优化查询。

优化器通过存储引擎获取一些参数、某个操作的执行代价、以及统计信息等。

在解析查询之前，服务器会先访问查询缓存（querycache）——它存储SELECT语句以及相应的查询结果集。

如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。

它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。

 1.2、并发控制

MySQL提供两个级别的并发控制：

服务器级（theserverlevel）和存储引擎级（thestorageenginelevel）。

加锁是实现并发控制的基本方法，MySQL中锁的粒度：

（1）   表级锁：

MySQL独立于存储引擎提供表锁，例如，对于ALTERTABLE语句，服务器提供表锁（table-levellock）。

（2）   行级锁：

InnoDB和Falcon存储引擎提供行级锁，此外，BDB支持页级锁。

InnoDB的并发控制机制，下节详细讨论。

另外，值得一提的是，MySQL的一些存储引擎（如InnoDB、BDB）除了使用封锁机制外，还同时结合MVCC机制，即多版本两阶段封锁协议（Multiversiontwo-phraselockingprotocal），来实现事务的并发控制，从而使得只读事务不用等待锁，提高了事务的并发性。

注：

并发控制是DBMS的核心技术之一（实际上，对于OS也一样），它对系统性能有着至关重要的影响，以后再详细讨论。

1.3、事务处理

MySQL中，InnoDB和BDB都支持事务处理。

这里主要讨论InnoDB的事务处理（关于BDB的事务处理，也十分复杂，以前曾较为详细看过其源码，以后有机会再讨论）。

1.3.1、事务的ACID特性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性（JimGray在《事务处理：

概念与技术》中对事务进行了详尽的讨论）。

（1）原子性（Atomicity）：

事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

（2）一致性（Consistent）：

在事务开始和完成时，数据都必须保持一致状态。

这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

（3）隔离性（Isolation）：

数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。

这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

（4）持久性（Durable）：

事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

1.3.2、事务处理带来的相关问题

由于事务的并发执行，带来以下一些著名的问题：

（1）更新丢失（LostUpdate）：

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。

（2）脏读（DirtyReads）：

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。

这种现象被形象地叫做"脏读"。

（3）不可重复读（Non-RepeatableReads）：

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！

这种现象就叫做“不可重复读”。

（4）幻读（PhantomReads）：

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

1.3.3、事务的隔离性

SQL2标准定义了四个隔离级别。

定义语句如下：

SETTRANSACTIONISOLATIONLEVEL

[READUNCOMMITTED| 

READCOMMITTED |

REPEATABLEREAD |

SERIALIZABLE]

这与JimGray所提出的隔离级别有点差异。

其中READUNCOMMITTED即Jim的10（浏览）；READCOMMITTED即20，游标稳定性；REPEATABLEREAD为2.99990隔离（没有幻像保护）；SERIALIZABLE隔离级别为30，完全隔离。

SQL2标准默认为完全隔离（30）。

各个级别存在问题如下：

隔离级

脏读

不可重复读

幻象读

读未提交

（Readuncommitted）

可能

可能

可能

读提交

（Readcommitted）

不可能

可能

可能

可重复读

（Repeatableread）

不可能

不可能

可能

可串行化

（Serializable）

不可能

不可能

不可能

各个具体数据库并不一定完全实现了上述4个隔离级别，例如，Oracle只提供READCOMMITTED和Serializable两个标准隔离级别，另外还提供自己定义的Readonly隔离级别；SQLServer除支持上述ISO/ANSISQL92定义的4个隔离级别外，还支持一个叫做“快照”的隔离级别，但严格来说它是一个用MVCC实现的Serializable隔离级别。

MySQL支持全部4个隔离级别，其默认级别为Repeatableread，但在具体实现时，有一些特点，比如在一些隔离级别下是采用MVCC一致性读。

国产数据库DM也支持所有级别，其默认级别为READCOMMITTED。

1.3.4、InnoDB的锁模型

InnoDB的行级锁有两种类型：

（1）共享锁（sharedlock，S）：

允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

（2）排它锁（exclusivelock，X）：

允许获得排它锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

此外，InnoDB支持多粒度加锁（multiplegranularitylocking），从而允许对记录和表同时加锁。

为此，InnoDB引入意向锁（intentionlocks），意向锁是针对表的：

（1）意向共享锁（IS）：

事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

（2）意向排他锁（IX）：

事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

例如，SELECT...LOCKINSHAREMODE加IS锁，SELECT...FORUPDATE加IX锁，意向锁的规则如下：

（1）事务在对表T中的记录获取S锁前，先要获取表T的IS锁或者更强的锁；

（2）事务在获取表T中记录的X锁前，先要获取表T的IX锁。

InnoDB的锁相容性矩阵：

 如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

意向锁只会阻塞其它事务对表的请求，例如，LOCKTABLES…WRITE，意向锁的主要目的是表明该事务将要或者正在对表中的记录加锁。

使用封锁机制来进行并发控制，一个比较重要的问题就是死锁。

来看一个死锁的例子：

例1-1

Session1

Session2

mysql>CREATETABLEt（iINT）ENGINE=InnoDB;

QueryOK,0rowsaffected（0.22sec）

mysql>INSERTINTOt（i）VALUES

（1）;

QueryOK,1rowaffected（0.08sec）

mysql>STARTTRANSACTION;

QueryOK,0rowsaffected（0.00sec）

mysql>SELECT*FROMtWHEREi=1LOCKINSHAREMODE;

+------+

|i    |

+------+

|    1|

+------+

1rowinset（0.01sec）

mysql>

mysql>STARTTRANSACTION;

QueryOK,0rowsaffected（0.00sec）

mysql>DELETEFROMtWHEREi=1;

等待…

mysql>DELETEFROMtWHEREi=1;

等待…

ERROR1213（40001）:

Deadlockfoundwhentryingtogetlock;tryrestartingtransaction

QueryOK,1rowaffected（0.00sec）

 1.3.5、一致性非阻塞读

一致性读是MySQL的重要特点之一，InnoDB通过MVCC机制表示数据库某一时刻的查询快照，查询可以看该时刻之前提交的事务所做的改变，但是不能看到该时刻之后或者未提交事务所做的改变。

但是，查询可以看到同一事务中之前语句所做的改变，例如：

例1-2

Session1

Session2

mysql>select*fromt;

Emptyset（0.00sec）

mysql>INSERTINTOt（i）VALUES

（1）;

QueryOK,1rowaffected（0.00sec）

mysql>select*fromt;

+------+

|i   |

+------+

|   1|

+------+

1rowinset（0.00sec）

mysql>setautocommit=0;

QueryOK,0rowsaffected（0.01sec）

mysql>updatetseti=3;

QueryOK,1rowaffected（0.00sec）

Rowsmatched:

1 Changed:

1 Warnings:

0

mysql>select*fromt;

+------+

|i   |

+------+

|   3|

+------+

1rowinset（0.00sec）

mysql>setautocommit=0;

QueryOK,0rowsaffected（0.00sec）

mysql>select*fromt;

+------+

|i    |

+------+

|    1|

+------+

1rowinset（0.00sec）

mysql> commit;

QueryOK,0rowsaffected（0.06sec）

mysql>select*fromt;

+------+

|i    |

+------+

|    1|

+------+

1rowinset（0.00sec）

mysql> commit;

QueryOK,0rowsaffected（0.00sec）

mysql>select*fromt;

+------+

|i    |

+------+

|    3|

+------+

1rowinset（0.00sec）

 如果事务的隔离级别为REPEATABLEREAD（默认），同一个事务中的所有一致性读都是读的事务的第一次读操作创建的快照。

你可以提交当前事务，然后在新的查询中即可看到最新的快照，如上所示。

如果事务的隔离级别为READCOMMITTED，一致性读只是对事务内部的读操作和它自己的快照而言的，结果如下：

例1-3

Session1

Session2

mysql>SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;

QueryOK,0rowsaffected（0.01sec）

mysql>setautocommit=0;

QueryOK,0rowsaffected（0.00sec）

mysql>select*fromt;

+------+

|i    |

+------+

|    3|

+------+

1rowinset（0.00sec）

mysql>SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;

QueryOK,0rowsaffected（0.01sec）

mysql>setautocommit=0;

QueryOK,0rowsaffected（0.00sec）

mysql>select*fromt;

+------+

|i    |

+------+

|    3|

+------+

1rowinset（0.00sec）

mysql>updatetseti=5;

QueryOK,1rowaffected（0.00sec）

Rowsmatched:

1 Changed:

1 Warnings:

0

mysql>select*fromt;

+------+

|i    |

+------+

|    3 |

+------+

1rowinset（0.00sec）

mysql>commit;

QueryOK,0rowsaffected（0.06sec）

mysql>select*fromt;

+------+

|i    |

+------+

|    5 |

+------+

1rowinset（0.00sec）

 注意，session2发生了不可重复读。

当InnoDB在READCOMMITTED和REPEATABLEREAD隔离级别下处理SELECT语句时，一致性读是默认的模式。

一致性读不会对表加任何锁，所以，其它连接可以同时改变表。

假设事务处于REPEATABLEREAD级别，当你正在进行一致性读时，InnoDB根据查询看到的数据给你一个时间点。

如果其它的事务在该时间点之后删除一行，且提交事务，你不会看到行已经被删除，插入和更新操作一样。

但是，InnoDB与其它DBMS的不同是，在REPEATABLEREAD隔离级别下并不会造成幻像。

一致性读不与DROPTABLE或者ALTERTABLE一起工作。

在nodb_locks_unsafe_for_binlog变量被设置或者事务的隔离级别不是SERIALIZABLE的情况下，InnoDB对于没有指定FORUPDATE或LOCKINSHAREMODE的INSERTINTO...SELECT,UPDATE...（SELECT）,和CREATETABLE...SELECT语句使用一致性读，在这种情况下，查询语句不会对表中的元组加锁。

否则，InnoDB将使用锁。

1.3.6、SELECT...FORUPDATE和SELECT...LOCKINSHAREMODE的加锁读（lockingread）

在一些场合，一致性读并不是很方便，此时，可以用加锁读。

InnoDB支持两种加锁读：

（1）   SELECT...LOCKINSHAREMODE：

对读取的元组加S锁。

（2）   SELECT...FORUPDATE：

在扫描索引记录的过程中，会阻塞其它连接的SELECT...LOCKINSHAREMODE和一定事务隔离级别下的读操作。

InnoDB使用两阶段封锁协议，事务直到提交或回滚时才会释放所有的锁，这都是系统自动执行的。

此外，MySQL支持LOCKTABLES和UNLOCKTABLES，但这些都是在服务器层实现的，而不是在存储引擎。

它们有用处，但是不能取代存储引擎完成事务处理，如果你需要事务功能，请使用事务型存储引擎。

来考虑lockingread的应用，假设你要在表child插入一个新的元组，并保证child中的记录在表parent有一条父记录。

如果你用一致性读来读parent表，确实可以将要插入的childrow的parentrow，但是可以安全的插入吗？

不，因为在你读parent表时，其它连接可能已经删除该记录。

（一致性读是针对事务内而言的，对于数据库的状态，它应该叫做“不一致性读”）

此时，就可以使用SELECTLOCKINSHAREMODE，它会对读取的元组加S锁，从而防止其它连接删除或更新元组。

另外，如果你想在查询的同时，进行更新操作，可以使用SELECT...FORUPDATE，它读取最新的数据，然后对读到的元组加X锁。

此时，使用SELECT...LOCKINSHAREMODE不是一个好主意，因为此时如果有两个事务进行这样的操作，就会造成死锁。

注：

SELECT...FORUPDATE仅在自动提交关闭（即手动提交）时才会对元组加锁，而在自动提交时，符合条件的元组不会被加锁。

1.3.7、记录锁（recordlok）、间隙锁（gaplock）和后码锁（next-keylock）

InnoDB有以下几种行级锁：

（1）记录锁：

对索引记录（indexrecords）加锁，InnoDB行级锁是通过给索引的索引项加锁来实现的，而不是对记录实例本身加锁。

如果表没有定义索引，InnoDB创建一个隐藏的聚簇索引，然后用它来实现记录加锁（关于索引与加锁之间的关系的详细介绍请看下一章）。

（2）间隙锁：

对索引记录之间的区间，或者第一个索引记录之前的区间和最后一个索引之后的区间加锁。

（3）后码锁：

对索引记录加记录锁，且对索引记录之前的区间加锁。

默认情况下，InnoDB的事务工作在REPEATABLEREAD的隔离级别，而且系统变量innodb_locks_unsafe_for_binlog为关闭状态。

此时，InnoDB使用next-key锁进行查找和索引扫描，从而达到防止“幻像”的目的。

Next-key锁是记录锁和间隙的结合体。

当InnoDB查找或扫描表的索引时，对它遇到的索引记录加S锁或者X锁，所以，行级锁（row-levellock）实际上就是索引记录锁（index-recordlock）；此外，它还对索引记录之前的区间加锁。

也就是说，next-key锁是索引记录锁，外加索引记录之前的区间的间隙锁。

如果一个连接对索引中的记录R持有S或X锁，其它的连接不能按照索引的顺序在R之前的区间插入一个索引记录。

假设索引包含以下值：

10,11,13和20，则索引的next-key锁会覆盖以下区间（“（”表示不包含，“[”表示包含）：

（negativeinfinity,10]

（10,11]

（11,13]

（13,20]

（20,positiveinfinity）

对于最后一个区间，next-key锁将锁住索引最大值以上的区间，上界虚记录（“supremum”pseudo-record）的值比索引中的任何值都大，其实，上界不是一个真实的索引记录，所以，next-lock将对索引的最大值之后的区间加锁。

间隙锁对查询唯一索引中的唯一值是没有必要的，例如，id列有唯一索引，则下面的查询仅对id=100的元组加索引记录锁（index-recordlock），而不管其它连接是否在之前的区间插入元组。

SELECT*FROMchildWHEREid=100;

如果id没有索引，或者非唯一索引，则语句会锁住之前的空间。

例1-4

Session1

Session2

mysql>createuniqueindexi_indexont（i）;

QueryOK,0rowsaffected（0.19sec）

Records:

0 Duplicates:

0 Warnings:

0

mysql>select*fromt;

+------+

|i   |

+------+

|   4|

|  10|

+------+

2rowsinset（0.00sec）

mysql>setautocommit=0;

QueryOK,0rowsaffected（0.00sec）

mysql>selectifromtwherei=10lockinsharemode;

+------+

|i   |

+------+

|  10|

+------+

1rowinset（0.00sec）

mysql>insertintot（i）values（9）;

QueryOK,1rowaffected（0.03sec）

mysql>select*fromt;

+------