13. 锁定读（locking read）
如果你想在一个事务中先查询然后插入或更新相关数据，则普通select语句无法提供足够的保护。其它事务可能更新或删除了你刚查询到的数据行。InnoDB提供了两种类型的锁定读（lock read），它们提供了额外的保护：
在读取到的行上设置共享锁。其它会话可以读取行，但在你的事务提交之前它们无法修改这些行。如果这些行中的任何一个被另一个尚未提交的事务更改，则你的查询将等待直到该事务结束，然后使用最新值
凡搜索中遇到的索引记录，锁定行和相关的索引条目，就好像你对这些行发出了update语句一样。其它的事务不能更新这些行，不能对这些行执行select ... lock in share mode，在某些隔离级别下甚至无法读取这些行。一致性读（consistent read）会忽略read视图中的记录上的锁。（旧版记录不能被锁定，它们是通过在该记录的内存副本中应用撤销日志（undo log）来重构的）
13.1 用途
这两个子句对于处理树或图结构的数据，非常有用，无论是在单个表中，还是在多个表之间进行拆分。你可以沿着边或树的分支从一个位置遍历到另一个位置，然后返回，这样你就拥有了对这些行的占有权，可以任意修改这些“指针”值
13.2 说明
当事务提交或回滚后，所有由lock in share mode和for update查询设置的锁都将被释放
13.3 限制
使用select for update锁定行（以备更新），只适用于autocommit被禁用时（通过start transaction开始事务或将autocommit设置为0）。如果启用了autocommit，则匹配到的行不会被锁定
13.4 示例
假设你要将一个新行插入到表child中，并确保该行在表parent中具有对应的父行。你的应用程序代码可以按如下操作顺序来确保引用完整性
首先，使用一致性读操作（consistent read）查询表parent，已验证父行存在。现在你可以将新行安全的插入到表child中吗？不，因为其它会话可能在你的select和insert操作之间的删除了父行，而你不会知道
为了避免这个潜在的问题，请使用lock in share mode执行select：
在lock in share mode查询返回父行'Jones'后，你可以将新行安全地插入到表child并提交事务。任何试图获取parent表中对应行上排他锁的事务都将等待你的操作完成，即直到所有表中的数据处于一致的状态为止
另一个例子，考虑一个表child_codes中的整数计数器字段，它用于为每个添加到child表的孩子分配唯一标识符。不要使用一致性读（consistent read）和共享模式读（shared mode read）来读取计数器的当前值，因为数据库的两个用户可能看到相同的计数器值，如果两个事务尝试添加行，则会导致child表出现重复键错误
而且在这里，lock in share mode不是一个好的解决方案，因为如果两个用户同时读计数器，那么当他们尝试更新计数器时，至少会有一个用户出现死锁
为了实现读取和递增计数器，首先使用for update对计数器执行锁定读取，然后递增计数器。例如：
select ... for update读取最新的可用数据，并在其读取的每一行上设置排他锁。它与带搜索的SQL update设置的锁相同
前面的描述仅仅是select ... for update如何工作的一个例子。在MySQL中，生成唯一标识符的具体任务实际上可以使用对表的单一访问来实现：
select语句仅检索标识符信息（特定于当前连接），它不访问任何表
12. DDL
数据定义语言，一组用于操作数据库本身而非单独的表行的SQL语句。它包括所有形式的create，alter和drop语句，还包括truncate语句，因为它与delete from table_name语句的工作方式不同，尽管最终效果相似
DDL语句自动提交当前事务；它们不能回滚
InnoDB在线DDL功能提高了create index，drop index和许多类型的alter table操作的性能。此外，InnoDB的file-per-table设置也会影响drop table和truncate table操作的行为
11. DML
数据操作语言，用于执行insert，update和delete操作的SQL语句。select语句有时也被视为一个DML语句，因为select ... for update与insert，update和delete的锁定相同
InnoDB表的DML语句在事务的上下文中操作，因此它们的效果可以作为单个单元提交或回滚
10. 一致性非锁定读（consistent nonlocking read）
一致性读意味着InnoDB使用多版本（multi-versioning）将数据库在某个时间点上的快照呈现给查询。该查询可以看到在此时间点之前提交的事务所做的更改，之后及未提交的事务所做的更改则不可见。此规则的例外是，同一事务中先前的语句所做的更改，查询可以看到。这个例外会导致下面的异常：如果你在表中更新了某些行，那么select将看到更新行的最新版本，但它也可能看到任何行的旧版本。如果其它会话同时更改了该表，这意味着你可能会看到该表在数据库中从未出现过的状态
如果事务隔离级别是repeatable read（默认级别），那么同一事务中所有一致性读操作读取的都是该事务中第一个一致性读操作所建立的快照。你可以提交当前事务，并重新发起查询，从而获取最新的快照
read committed隔离级别中，事务中每个一致性读操作都设置并读取自己的快照
一致性读是read committed和repeatable read隔离级别下InnoDB处理select语句的默认模式。一致性读不会在其访问的表上设置任何锁，因此当一致性读操作正在执行时，其它事务可以随时修改这些表
假设你运行在repeatable read隔离级别下。当你发出一致性读操作（即普通的select语句）时，InnoDB会为你的事务提供一个时间点，你的查询将会据此查看数据库，也就是说，你的查询看到的将是该时间点的数据库快照。如果另一个事务删除了一行，并在你的时间点被分配后提交，那么你看不到该行已被删除。插入和更新的情况相同
数据库状态快照适用于事务中的select语句，不适用于DML语句。如果你插入或修改了某些行，然后提交了该事务，则由另一个并发的repeatable read级别下的事务发出的delete或update语句可能会作用到刚刚提交的那些行，尽管在该会话中无法查询到它们。如果事务确实更新或删除了由其它事务提交的行，则这些更改将对当前事务变得可见。例如，你可能会遇到一下情况：
你可以提交你当前的事务，然后通过发起另一个select或start transaction with consistent snapshot来推进你的时间点
这被称为多版本并发控制（multi-versioned concurrency control）
在以下示例中，只有当B已经提交了insert且A也已经提交的情况下，会话A才会看到由B插入的行，此时A的时间点已经超过了B提交插入的时间点：
如果要查看数据库的“最新”状态，请使用read committed隔离级别或锁定读：
read committed隔离级别下，事务中的每个一致性读操作都设置并读取自己的新鲜快照。使用lock in share mode时，会发生锁定读：select操作将被阻塞，直到包含最新行的事务结束
一致性读对某些DDL语句无效：
① 一致性读对drop table不起作用，因为MySQL不能使用已删除的表；
② 一致性读对alter table不起作用，因为该语句会生成原始表的一个临时副本，并在临时副本构建完后删除原始表。当你在事务中重新发出一致性读操作时，新表中的行不可见，因为当事务的快照在建立时，这些行并不存在
insert into ... select，update ... (select) 和 create table ... select中的select子句的读取方式稍有不同，假设这些语句并未指定for update或lock in share mode：
① 默认情况下，InnoDB使用更强的锁，这些语句的select部分就像read committed一样，即使在同一事务中，每个一致性读操作设置并读取自己的新鲜快照；
② 要在这种情况下使用一致性读，可以启用innodb_locks_unsafe_for_binlog选项，并将事务的隔离级别设置为read uncommitted, read committed或repeatable read（任何除serializable以外的隔离级别）。此时，从表中读取的行上不会被设置锁
9. 幻行（phantom rows）
所谓的幻影问题，就是在同一个事务内，相同的查询在不同的时间产生不同的行集合。例如，如果一个select被执行两次，但是第二次返回了第一次未返回的行，则该行是一个「幻行」。
假设在child表的id列上有一个索引，现在你想要读取并锁定id值大于100的所有行，以便稍后更新所选行中的某些列：
该查询从id大于100的第一条记录开始扫描索引。假设表中包含这么两行，它们的id分别为90和102。如果仅锁定扫描范围内的索引记录，而不锁定插入间隙（在本例中为90和102之间的间隙），则另一个会话可以在表中插入一个id为101的新行。如果在同一事务中执行相同select，则会在查询返回的结果集中看到一个id为101的新行（「幻行」）。如果我们将行的集合视为数据项，幻行的出现则破坏了事务的隔离原则：一个事务运行期间，其所读取的数据不会发生改变。
为了防止幻影，InnoDB使用一种称为next-key lock的算法，它将索引行锁（index-row lock）和间隙锁（gap lock）结合在一起。InnoDB以这样的方式执行行级锁定，当它搜索或扫描一个表的索引时，它会在它遇到的索引记录上设置共享或排他锁。因此，行级锁（row-level lock）实际上是索引记录锁（index-record lock）。此外，一个索引记录上的next-key lock也会影响该索引记录之前的“间隙”。也就是说，next-key lock是索引记录锁（index-record lock），加上该索引记录之前的间隙上的间隙锁（gap lock）。如果一个会话在索引中的记录R上具有共享或排他锁，则另一个会话不能在索引顺序中紧邻R之前的间隙中插入新的索引记录。
当InnoDB扫描索引时，它也可以锁定索引中最后一条记录之后的间隙。前面的例子中：为了防止任何id大于100的行的插入，InnoDB对id值102之后的间隙也加了锁。
你可以使用next-key lock在应用程序中实现唯一性检查：如果你以共享模式读取数据，并且没有看到你要插入的行的副本，那么你可以安全地插入行，并且知道在读取期间，在行的后继上设置的next-key lock可以阻止任何人在同一时间插入重复的行。因此，next-key使你能够“锁定”表中某些不存在的东西。
8. 事务隔离级别
四种事务隔离级别：
说明：MySQL的默认事务隔离级别是repeatable read
问题：
问题解决难度：dirty read < unrepeatable read < phantom read
事务隔离级别：committed read
8.1 脏读（dirty read）
检索不可靠数据的操作，即由另一个事务更新但尚未提交的数据。只有在未提交读（read uncommited）的隔离级别下才有可能发生
这种操作不符合数据库设计的ACID原则。它被认为是非常危险的，因为数据可能回滚，或者在提交之前做进一步更改，从而导致做脏读的事务使用了不准确的数据
它的对立面是一致性读（consistent read）：InnoDB确保事务不会读取由另一个事务更新的信息，即使另一个事务在此期间提交
8.2 不可重复读（non-repeatable read）
同一个事务内，同一个查询前后两次返回的结果不同（可能是被另一个已提交的事务更改过），被称为不可重复读
这种操作违背了数据库设计的ACID原则。在一个事务中，数据应该是一致的，可预测且稳定的
在不同的隔离级别中，串行化读（serializable read）和可重复读（repeatable read）可以阻止不可重复读问题，而一致性读（consistent read）和未提交读（read uncommited）级别则无法阻止
8.3 幻影（phantom）
同一个事务内，后一次查询的结果集中出现了前一次查询结果集中不存在的行，这就是「幻影」问题。在后一次查询结果集中出现的行，不在前一次查询的结果集中，这样的行被称为「幻行」。例如，如果一个查询在一个事务中执行了两次，并且在此期间，另一个事务插入了新行或更新了一行，使得更新完后该行能够匹配前面查询的where子句，然后提交事务，就会导致前一个事务中的查询出现「幻影」问题
幻读比不可重复读更难防范，因为锁定前一次查询结果集中的所有行并不能阻止导致幻影出现的更新操作
在不同的隔离级别中，可以通过串行化读（serializable read）级别防止幻读，而可重复读（repeatable read），一致性读（consistent read）和未提交读（read uncommitted）级别都可能出现幻读
8.4 一致性读（consistent read）
一种使用基于时间点的快照信息来呈现查询结果的读取操作，而不管同时运行的其它事务执行的更改如何。如果查询的数据已被另一个事务更改，则根据撤销日志的内容重建原始数据。这种技术避免了通过强制事务等待其它事务完成，以致并发性降低的锁问题
repeatable read隔离级别中，快照基于第一个读操作的时间；read committed隔离级别中，每次一致性读操作都会重置快照
一致性读是read committed和repeatable read隔离级别下InnoDB处理select语句的默认模式。因为一致性读操作不会在其访问的表上设置任何锁，所以当一致性读操作正在执行时，其它事务可以修改这些表
7. 聚簇索引
每个InnoDB表都有一个特殊的索引，称为聚簇索引，其中存储着数据行。通常聚簇索引与主键是同义词
归属：聚簇索引是InnoDB存储引擎所特有的
说明：
◇ 当你为表定义主键时，InnoDB会将其作为表的聚簇索引。请为你创建的每个表都显式地定义主键。如果不存在逻辑上唯一且非空的列或列组合，请添加一个自增列，其值能够自动填充；
◇ 如果你没有为表定义主键，InnoDB将查找第一个全部列都是NOT NULL的唯一索引，将其用作聚簇索引；
◇ 如果表没有主键，也没有符合条件的唯一索引，InnoDB将在一个包含行ID的合成列上生成隐藏的聚簇索引。此时数据行将根据InnoDB分配给数据行的ID值进行排序。行ID是一个6字节的字段，随着新行插入到表中而单调递增。因此，根据行ID排序实际上就是根据行的插入顺序排序；
为了使查询、插入和其它数据库操作的性能最佳，你必须了解InnoDB如何使用聚簇索引来优化每个表中最常见的查询和DML操作
聚簇索引如何加速查询
通过聚簇索引访问数据行是很快的，因为索引搜索能够直接定位到包含该行全部数据所在的页面。如果表很大，与行数据和索引记录存储在不同页面的存储组织相比，聚簇索引结构通常能够节省一次磁盘I/O操作
辅助索引和聚簇索引的关系
除聚簇索引以外的所有索引都称为辅助索引。在InnoDB中，辅助索引中的每个记录既包含辅助索引列，也包含主键列。InnoDB使用主键值搜索聚簇索引中的数据行。如果主键很长，辅助索引使用的空间将会更多，因此一个短的主键是有利的
6. 索引
索引的类型：
◇ B-Tree索引
◇ 哈希索引
◇ 空间索引
◇ 全文索引
主键：InnoDB与MyISAM都拥有主键的概念，结构上都是一棵B-Tree，但区别如下：
InnoDB的主键即聚簇索引，聚簇索引即主键；
MyISAM的主键与辅助索引结构上没有差别，MyISAM的主键即普通的唯一非空索引
5. 存储引擎
存储引擎分类：
◇ InnoDB
◇ MyISAM
◇ 其它的MySQL内置存储引擎，如ARCHIVE, MEMORY
◇ 第三方存储引擎
4. 字符串字面值
△ 字符串是由单引号或双引号包裹的字节或字符序列，例如：
'a string'
"another string"
△ 彼此相邻的字符串，会被连接到一起，形成一个新的字符串，例如下面两行等价：
'hello world'
'hello' " " "world"
△ 字符串内的某些字符序列有特殊的含义，它们被称为转义序列，转义序列以转义字符「\」开头，MySQL识别的转义序列如下图所示
对于其它的转义序列，反斜杠自动被忽略，也就是说被转义字符当作无转义字符对待，例如，\x相当于x
转义序列，大小写敏感，例如，\b相当于退格符，而\B相当于B
模式匹配上下文中，「%」和「_」作为通配符存在，所以在模式匹配上下文中，如需引用「%」和「_」，需要对其转义，例如「\%」「\_」。而在模式匹配上下文之外，「\%」「\_」代表的就是「\%」「\_」，而非「%」和「_」
△ 在字符串中包含引号字符的方式如下：
① '里面包含'时，字符串里的单引号需写两次''
② "里面包含"时，字符串里的双引号需写两次""
③ 对引号字符进行转义，字符前加反斜杠
④ "里面包含'，或'里面包含"时，无需特别对待
示例：
3. 文件结构
MySQL中，每个数据库，对应数据目录中的一个单独的目录；数据库中的表，对应数据库目录中一个或多个文件
2. 关键字和保留字
「interval」「write」
完整保留字列表
https://dev.mysql.com/doc/refman/5.5/en/keywords.html
1. 标识符
MySQL内的某些对象，如数据库、表、索引、列、别名、视图、存储过程、分区、表空间和其它一些对象，它们的名字被称作标识符
① 标识符中的引号字符是`反引号`
② 使用标识符时，可以加，也可以不加反引号，但是当标识符为下列情况之一时，使用时必须加反引号：
△ 包含特殊字符，比如「减号-」
△ 本身是保留字
④⑤⑥
③ 标识符中可以包含任何引号字符，提前是标识符已被反引号修饰。当标识符内含的引号与引用标识符的引号相同时，你需要将标识符内含的引号写两次
示例：创建一个名为a`b的表，表中包含一个名为c"d的整数列
△ Unix MySQL下，数据库名，表名，大小写敏感；列名，索引名，大小写不敏感
1197
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
