MySQL进阶详解教程，看过的都说好

1.索引

1.1概述

​ 索引是对数据库表中一个或多个列的值进行排序的结构。所有MySQL列类型都可以被索引，对相关列使用索引是提高SELECT操作性能的最佳途径。

1.2分类

• **B-Tree索引：**最常见的索引类型，大部分引擎都支持B树索引。

• **HASH索引：**只有Memory引擎支持，使用场景简单。

• **R-Tree索引（空间索引）：**空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型。

• **Full-text（全文索引）：**全文索引也是 MyISAM 的一个特殊索引类型，主要用于全文索引。

  

1.3设计索引的原则

• 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。
• 使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。
• 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
• 利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
• 不要过度索引。个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。

1.4 B-TREE索引

​ B-Tree索引是最常见的索引，构造类似二叉树，能根据键值提供一行或者一个行集的快速访问，通常只需要很少的读操作就可以找到正确的行。不过，需要注意B-Tree索引中的B 不代表二叉树（binary），而是代表平衡树（balanced）。B-Tree索引并不是一棵二叉树。

1.5 MySQL中能够使用索引的典型场景

• 匹配全值（Match the full value），对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。
• 匹配值的范围查询（Match a range of values），对索引的值能够进行范围查找。
• 匹配最左前缀（Match a leftmost prefix），仅仅使用索引中的最左边列进行查找，
• 仅仅对索引进行查询（Index only query），当查询的列都在索引的字段中时，查询的效率更高；
• 匹配列前缀（Match a column prefix），仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。
• 能够实现索引匹配部分精确而其他部分进行范围匹配（Match one part exactly and match a range on another part）。
• 如果列名是索引，那么使用 column_name is null就会使用索引（区别于Oracle）。
• Index Condition Pushdown（ICP），Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎。

1.6存在索引但不能使用索引的典型场景

• 以%开头的LIKE查询不能够利用B-Tree索引，执行计划中key的值为NULL表示没有使用索引.
• 数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串。
• 复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则Leftmost，是不会使用复合索引的。
• 如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。
• 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

1.7索引使用情况与优化

1.7.1查看索引使用情况

​ 如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

​ Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next的值较高，则通常说明表索引不正确或写入的查询没有利用索引。

​ 语法：show status like ‘Handler_read%’;

1.7.2定期分析表和检查表

​ 分析表的语法如下：

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] . .

​ 本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM、BDB和 InnoDB表有作用。对于MyISAM表，本语句与使用myisamchk -a相当.

​ 检查表的语法如下：

CHECK TABLE tbl_name [, tbl_name] . . [option] . . option = {QUICK | FAST |MEDIUM | EXTENDED | CHANGED}

​ 检查表的作用是检查一个或多个表是否有错误。CHECK TABLE对MyISAM和InnoDB表有作用。对于MyISAM表，关键字统计数据被更新。CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在，

​ 优化表的语法如下：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] . .

​ 如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE命令只对MyISAM、BDB和InnoDB表起作用。

​ 对于InnoDB引擎的表来说，通过设置innodb_file_per_table参数，设置InnoDB为独立表空间模式，这样每个数据库的每个表都会生成一个独立的ibd文件，用于存储表的数据和索引，这样可以一定程度上减轻InnoDB表的空间回收问题。另外，在删除大量数据后，InnoDB表可以通过 alter table但是不修改引擎的方式来回收不用的空间.

2.视图

2.1概念

​ 视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

​ 视图的特点：

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单地实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

2.2视图操作

​ 视图的操作包括创建或者修改视图、删除视图，以及查看视图定义。

2.2.1创建或者修改视图

​ 创建视图需要有CREATE VIEW的权限，并且对于查询涉及的列有SELECT权限。如果使用CREATE OR REPLACE或者ALTER修改视图，那么还需要该视图的DROP权限。

​ 创建视图的语法为：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

​ 修改视图的语法为：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

​ 视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的。

​ 包含以下关键字的SQL语句：

• 聚合函数（SUM、MIN、MAX、COUNT等）、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL。
• 常量视图。
• SELECT中包含子查询。
• JION。
• FROM一个不能更新的视图。
• WHERE字句的子查询引用了FROM字句中的表。

2.2.2删除视图

​ 用户可以一次删除一个或者多个视图，前提是必须有该视图的DROP权限。

DROP VIEW [IF EXISTS] view_name [, view_name] . .[RESTRICT | CASCADE]

2.2.3查看视图

​ 使用SHOW TABLES命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的SHOW VIEWS命令。

3.存储过程和函数

3.1什么是存储过程和函数

​ 存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

​ 存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的。

​ MySQL的存储过程和函数中允许包含DDL语句，也允许在存储过程中执行提交（Commit，即确认之前的修改）或者回滚（Rollback，即放弃之前的修改），但是存储过程和函数中不允许执行LOAD DATA INFILE语句。此外，存储过程和函数中可以调用其他的过程或者函数。

3.2创建、修改存储过程或者函数

​ 创建、修改存储过程或者函数的语法如下：

CREATE PROCEDURE sp_name ([proc_parameter[,. .]])

[characteristic . .] routine_body

CREATE FUNCTION sp_name ([func_parameter[,. .]])

RETURNS type

[characteristic . .] routine_body

proc_parameter:

[ IN | OUT | INOUT ] param_name type

func_parameter:

param_name type

type:

Any valid MySQL data type

characteristic:

LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL| NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT ‘string’

routine_body:

Valid SQL procedure statement or statements

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic . .]

characteristic:

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT ‘string’

​ 调用过程的语法如下：

CALL sp_name([parameter[,. .]])

​ MySQL的存储过程和函数中允许包含DDL语句，也允许在存储过程中执行提交（Commit，即确认之前的修改）或者回滚（Rollback，即放弃之前的修改），但是存储过程和函数中不允许执行LOAD DATA INFILE语句。此外，存储过程和函数中可以调用其他的过程或者函数。

​ 下面对characteristic特征值的部分进行简单的说明。

• LANGUAGE SQL：说明下面过程的BODY是使用SQL语言编写，这条是系统默认的，为今后MySQL会支持的除SQL外的其他语言支持的存储过程而准备。
• [NOT] DETERMINISTIC：DETERMINISTIC确定的，即每次输入一样输出也一样的程序，NOT DETERMINISTIC非确定的，默认是非确定的。当前，这个特征值还没有被优化程序使用。
• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：这些特征值提供子程序使用数据的内在信息，这些特征值目前只是提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况。CONTAINS SQL表示子程序不包含读或写数据的语句。NO SQL表示子程序不包含SQL语句。READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA表示子程序包含写数据的语句。如果这些特征没有明确给定，默认使用的值是CONTAINS SQL。
• SQL SECURITY { DEFINER | INVOKER }：可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是DEFINER。
• COMMENT ‘string’：存储过程或者函数的注释信息。

3.3删除存储过程或者函数

​ 一次只能删除一个存储过程或者函数，删除存储过程或者函数需要有该过程或者函数的ALTER ROUTINE权限，具体语法如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

3.4 查看存储过程或者函数

​ 存储过程或者函数被创建后，用户可能需要查看存储过程、函数的状态、定义等信息，便于了解存储过程或者函数的基本情况。

1. 查看存储过程或者函数的状态
   • SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]
2. 查看存储过程或者函数的定义
   • SHOW CREATE {PROCEDURE | FUNCTION} sp_name
3. 通过查看 information_schema. Routines了解存储过程和函数的信息

3.5变量的使用

3.5.1变量的定义

​ 通过DECLARE可以定义一个局部变量，该变量的作用范围只能在BEGIN…END块中，可以用在嵌套的块中。变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。可以一次声明多个相同类型的变量。如果需要，可以使用DEFAULT赋默认值。

​ 定义一个变量的语法如下：

DECLARE var_name[,. .] type [DEFAULT value]

3.5.2变量的赋值

​ 变量可以直接赋值，或者通过查询赋值。直接赋值使用SET，可以赋常量或者赋表达式，具体语法如下：

SET var_name = expr [, var_name = expr] . .

3.6定义条件和处理

​ 条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤。

3.6.1条件的定义

DECLARE condition_name CONDITION FOR condition_value

condition_value:

SQLSTATE [VALUE] sqlstate_value

| mysql_error_code

3.6.2条件的处理

DECLARE handler_type HANDLER FOR condition_value[,. .] sp_statement

handler_type:

CONTINUE

| EXIT

| UNDO

condition_value:

SQLSTATE [VALUE] sqlstate_value

| condition_name

| SQLWARNING

| NOT FOUND

| SQLEXCEPTION

| mysql_error_code

​ handler_type现在还只支持CONTINUE和EXIT两种，CONTINUE表示继续执行下面的语句，EXIT则表示执行终止，UNDO现在还不支持。

​ condition_value的值可以是通过DECLARE定义的condition_name，可以是SQLSTATE的值或者mysql-error-code的值或者 SQLWARNING、NOT FOUND、SQLEXCEPTION，这 3个值是3种定义好的错误类别，分别代表不同的含义。

• SQLWARNING是对所有以01开头的SQLSTATE代码的速记。
• NOT FOUND是对所有以 02开头的SQLSTATE代码的速记。
• SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

3.7光标的使用

​ 在存储过程和函数中，可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH和CLOSE，其语法分别如下。

1. 声明光标：
   • DECLARE cursor_name CURSOR FOR select_statement
2. OPEN光标：
   • OPEN cursor_name
3. FETCH光标：
   • FETCH cursor_name INTO var_name [, var_name] . .
4. CLOSE光标：
   • CLOSE cursor_name

3.8流程控制

​ 可以使用IF、CASE、LOOP、LEAVE、ITERATE、REPEAT及WHILE语句进行流程的控制。

3.8.1 IF语句

​ IF实现条件判断，满足不同的条件执行不同的语句列表，具体语法如下：

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list] . .

[ELSE statement_list]

END IF

3.8.2 CASE语句

​ CASE实现比IF更复杂一些的条件构造，具体语法如下：

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list] . .

[ELSE statement_list]

END CASE

或者：

CASEWHEN search_condition THEN statement_list

[WHEN search_condition THEN statement_list] . .

[ELSE statement_list]

END CASE

3.8.3 LOOP语句

​ LOOP实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用LEAVE语句实现，具体语法如下：

[begin_label:] LOOP

statement_list

END LOOP [end_label]

​ 如果不在statement_list中增加退出循环的语句，那么LOOP语句可以用来实现简单的死循环。

3.8.4 LEAVE语句

​ 用来从标注的流程构造中退出，通常和BEGIN … END或者循环一起使用。

3.8.5 ITERATE语句

​ ITERATE语句必须用在循环中，作用是跳过当前循环的剩下的语句，直接进入下一轮循环。

3.8.6 REPEAT语句

​ 有条件的循环控制语句，当满足条件的时候退出循环，具体语法如下：

[begin_label:] REPEAT

statement_list

UNTIL search_condition

END REPEAT [end_label]

3.8.7 WHILE语句

​ WHILE 语句实现的也是有条件的循环控制语句，即当满足条件时执行循环的内容，具体语法如下：

[begin_label:] WHILE search_condition DO

statement_list

END WHILE [end_label]

​ WHILE循环和REPEAT循环的区别在于：WHILE是满足条件才执行循环，REPEAT是满足条件退出循环；WHILE在首次循环执行之前就判断条件，所以循环最少执行0次，而REPEAT是在首次执行循环之后才判断条件，所以循环最少执行1次。

3.9事件调度器

​ 事件调度器可以将数据库按自定义的时间周期触发某种操作，可以理解为时间触发器，类似Linux系统下的任务调度器crontab。

​ 下面是一个最简单的事件调度器：

CREATE EVENT myevent

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

DO

UPDATE myschema.mytable SET mycol = mycol + 1;

​ 其中：

• 事件名称在 create event关键字后指定；
• 通过ON SCHEDULE子句指定事件在何时执行及执行频次；
• 通过DO子句指定要执行的具体操作或事件。

​ 上述创建的调度事件首先创建了myevent调度事件，然后执行更新操作，起始执行时间为调度器创建时间，后续在起始时间的基础上每隔1小时触发一次。

​ 事件调度器的优势、适用场景及使用中的注意事项：

4.触发器

4.1概念

​ 触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

4.2创建触发器

​ 触发器只能创建在永久表（Permanent Table）上，不能对临时表（Temporary Table）创建触发器。对同一个表相同触发时间的相同触发事件，只能定义一个触发器。

​ 创建触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

​ 其中trigger_time是触发器的触发时间，可以是BEFORE或者AFTER，BEFORE的含义指在检查约束前触发，而AFTER是在检查约束后触发，trigger_event就是触发器的触发事件，可以是INSERT、UPDATE或者DELETE。

4.3删除触发器

​ 一次可以删除一个触发程序，如果没有指定 schema_name，默认为当前数据库，具体语法如下：

DROP TRIGGER [schema_name.]trigger_name

4.4查看触发器

可以通过执行SHOW TRIGGERS命令查看触发器的状态、语法等信息，但是因为不能查询指定的触发器，所以每次都返回所有的触发器的信息，使用起来不是很方便.

4.5触发器的使用

​ 触发器执行的语句有以下两个限制。

• 触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL 语句，但是允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
• 不能在触发器中使用以显式或隐式方式开始或结束事务的语句，如 START TRANS-ACTION、COMMIT或ROLLBACK。

MySQL的触发器是按照BEFORE触发器、 行操作、 AFTER触发器的顺序执行的，其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为一个事务被回滚（Rollback），但是如果是对非事务表进行的操作，那么已经更新的记录将无法回滚.

5.事务

5.1事务及属性

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

• 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

5.2并发事务处理带来的问题

• 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题—最后的更新覆盖了由其他事务所做的更新。
• 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
• 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变或某些记录已经被删除了！这种现象就叫做“不可重复读”。
• 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

5.3事务隔离级别

​ 数据库实现事务隔离的方式，基本上可分为两种:

• 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
• 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

​ 数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。

5.4事务控制和锁定语句

​ MySQL支持对MyISAM和MEMORY存储引擎的表进行表级锁定，对BDB存储引擎的表进行页级锁定，对InnoDB存储引擎的表进行行级锁定。默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。但是在有的情况下，用户需要明确地进行锁表或者进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

5.4.1LOCK TABLE和UNLOCK TABLE

• LOCK TABLES可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
• UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁.

具体语法如下：

LOCK TABLES

tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] . .

UNLOCK TABLES

5.5事务控制

​ 默认情况下，MySQL 是自动提交（Autocommit）的，如果需要通过明确的Commit 和Rollback来提交和回滚事务，那么就需要通过明确的事务控制命令来开始事务，MySQL通过SET AUTOCOMMIT、START TRANSACTION、COMMIT和ROLLBACK等语句支持本地事务，具体语法如下：

• START TRANSACTION | BEGIN [WORK]
  • START TRANSACTION或BEGIN语句可以开始一项新的事务。
• COMMIT [WORK] [AND [NO] CHAIN] [[NO]
  • COMMIT和ROLLBACK用来提交或者回滚事务。
• RELEASE]ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
  • CHAIN和RELEASE子句分别用来定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，RELEASE 则会断开和客户端的连接。
• SET AUTOCOMMIT = {0 | 1}
  • SET AUTOCOMMIT 可以修改当前连接的提交方式，如果设置了 SET AUTOC-OMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。

5.6分布式事务

​ 一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。

5.6.1分布式事务的原理

​ 在MySQL中，使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器。

• 资源管理器（RM）用于提供通向事务资源的途径。数据库服务器是一种资源管理器。该管理器必须可以提交或回滚由RM管理的事务。
• 事务管理器（TM）用于协调作为一个分布式事务一部分的事务。TM 与管理每个事务的RMs进行通信。在一个分布式事务中，各个单个事务均是分布式事务的“分支事务”。分布式事务和各分支通过一种命名方法进行标识。

​ 用于执行分布式事务的过程使用两阶段提交，发生时间在由分布式事务的各个分支需要进行的行动已经被执行之后。

• 在第一阶段，所有的分支被预备好。即它们被TM告知要准备提交。通常，这意味着用于管理分支的每个RM会记录对于被稳定保存的分支的行动。分支指示是否它们可以这么做。这些结果被用于第二阶段。
• 在第二阶段，TM 告知 RMs 是否要提交或回滚。如果在预备分支时，所有的分支指示它们将能够提交，则所有的分支被告知要提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚。

5.6.2分布式事务的语法

​ 分布式事务（XA事务）的SQL语法主要包括：

• XA {START|BEGIN} xid [JOIN|RESUME]

​ XA START xid用于启动一个带给定 xid值的XA事务。每个XA事务必须有一个唯一的xid值，因此该值当前不能被其他的XA事务使用。xid是一个XA事务标识符，用来唯一标识一个分布式事务。xid值由客户端提供，或由MySQL服务器生成。xid值包含1～3个部分：

​ xid: gtrid [, bqual [, formatID ]]

• gtrid 是一个分布式事务标识符，相同的分布式事务应该使用相同的 gtrid，这样可以明确知道XA事务属于哪个分布式事务。
• bqual 是一个分支限定符，默认值是空串。对于一个分布式事务中的每个分支事务， bqual值必须是唯一的。
• formatID是一个数字，用于标识由gtrid和bqual值使用的格式，默认值是1。

6.锁

​ 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

6.1 MySQL锁概述

​ MySQL不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

6.2 MyISAM表锁

6.2.1查询表级锁争用情况

​ 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺。如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

6.2.2 MySQL表级锁的锁模式

​ MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

​ 对 MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！根据如表20-2 所示的例子可以知道，当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

6.2.3加表锁

​ MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁.

​ 给 MyISAM 表显式加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。

​ 在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。在自动加锁的情况下也是如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。

​ 当使用LOCK TABLES时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错！

6.2.4并发插入

​ 在一定条件下，MyISAM表支持查询和插入操作的并发进行。MyISAM 存储引擎有一个系统变量 concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

• 当concurrent_insert设置为0时，不允许并发插入。
• 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
• 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

​ 可以利用MyISAM存储引擎的并发插入特性来解决应用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE 语句来整理空间碎片，收回因删除记录而产生的中间空洞。

6.2.5MyISAM的锁调度

​ 一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。

​ 即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。幸好我们可以通过一些设置来调节MyISAM的调度行为。

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
• 过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

6.3 InnoDB锁问题

6.3.1获取 InnoDB行锁争用情况

​ 通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况。

​ 如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，可以通过查询 information_schema 数据库中相关的表来查看锁情况，或者通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

6.3.2 InnoDB的行锁模式及加锁方法

​ InnoDB实现了两种类型的行锁。

• **共享锁（S）：**允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• **排他锁（X）：**允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

​ 事务可以通过以下语句显示给记录集加共享锁或排他锁。

• 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。
• 排他锁（X）：SELECT * FROM table_name WHERE … FOR UPDATE。

​ 用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

6.3.3 InnoDB行锁实现方式

​ InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB行锁分为3种情形。

• Record lock：对索引项加锁。
• Gap lock：对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。
• Next-key lock：前两种的组合，对记录及其前面的间隙加锁。

6.3.4什么时候使用表锁

​ 对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

• 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
• 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。

​ 当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。在InnoDB下，使用表锁要注意以下两点。

• 使用 LOCK TABLES虽然可以给 InnoDB加表级锁，但必须说明的是，表锁不是由 InnoDB 存储引擎层管理的，而是由其上一层─MySQL Server 负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知 InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
• 在用LOCK TABLES对 InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用 UNLOCK TABLES释放表锁，因为 UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。

6.3.5死锁

​ 死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

​ 产生死锁的条件：

• 互斥
• 请求和保持
• 不剥夺
• 环路等待

​ 发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。

​ 通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的 SQL 语句，绝大部分死锁都可以避免。

• 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
• 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
• 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
  进程称为死锁进程。

​ 产生死锁的条件：

• 互斥
• 请求和保持
• 不剥夺
• 环路等待

​ 发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。

​ 通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的 SQL 语句，绝大部分死锁都可以避免。

• 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
• 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
• 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
• 当隔离级别为 READ COMMITTED 时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。