一文详解-MySQL 事务和锁

当多个用户访问同一份数据时，一个用户在更改数据的过程中，可能有其他用户同时发起更改请求，为保证数据库记录的更新从一个一致性状态变为另外一个一致性状态，使用事务处理是非常必要的，事务具有以下四个特性：

MySQL 提供了多种事务型存储引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事务。为了支持事务，InnoDB 存储引擎引入了与事务处理相关的 REDO 日志和 UNDO 日志，同时事务依赖于 MySQL 提供的锁机制

事务执行时需要将执行的事务日志写入日志文件，对应的文件为 REDO 日志。当每条 SQL 进行数据更新 *** 作时，首先将 REDO 日志写进日志缓冲区。当客户端执行 COMMIT 命令提交时，日志缓冲区的内容将被刷新到磁盘，日志缓冲区的刷新方式或者时间间隔可以通过参数 innodb_flush_log_at_trx_commit 控制

REDO 日志对应磁盘上的 ib_logifleN 文件，该文件默认为 5MB，建议设置为 512MB，以便容纳较大的事务。MySQL 崩溃恢复时会重新执行 REDO 日志的记录，恢复最新数据，保证已提交事务的持久性

与 REDO 日志相反，UNDO 日志主要用于事务异常时的数据回滚，具体内容就是记录数据被修改前的信息到 UNDO 缓冲区，然后在合适的时间将内容刷新到磁盘

假如由于系统错误或者 rollback *** 作而导致事务回滚，可以根据 undo 日志回滚到没修改前的状态，保证未提交事务的原子性

与 REDO 日志不同的是，磁盘上不存在单独的 UNDO 日志文件，所有的 UNDO 日志均存在表空间对应的 .ibd 数据文件中，即使 MySQL 服务启动了独立表空间

在 MySQL 中，可以使用 BEGIN 开始事务，使用 COMMIT 结束事务，中间可以使用 ROLLBACK 回滚事务。MySQL 通过 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等语句支持本地事务

MySQL 定义了四种隔离级别，指定事务中哪些数据改变其他事务可见、哪些数据该表其他事务不可见。低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少

InnoDB 系统级事务隔离级别可以使用以下语句设置：

查看系统级事务隔离级别：

InnoDB 会话级事务隔离级别可以使用以下语句设置：

查看会话级事务隔离级别：

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据称为脏读（Dirty Read），即是：首先开启 A 和 B 两个事务，在 B 事务更新但未提交之前，A 事务读取到了更新后的数据，但由于 B 事务回滚，导致 A 事务出现了脏读现象

所有事务只能看见已经提交事务所做的改变，此级别可以解决脏读，但也会导致不可重复读（Nonrepeatable Read）：首先开启 A 和 B 两个事务，A事务读取了 B 事务的数据，在 B 事务更新并提交后，A 事务又读取到了更新后的数据，此时就出现了同一 A 事务中的查询出现了不同的查询结果

MySQL 默认的事务隔离级别，能确保同一事务的多个实例在并发读取数据时看到同样的数据行，理论上会导致一个问题，幻读（Phontom Read）。例如，第一个事务对一个表中的数据做了修改，这种修改会涉及表中的全部数据行，同时第二个事务也修改这个表中的数据，这次的修改是向表中插入一行新数据，此时就会发生 *** 作第一个事务的用户发现表中还有没有修改的数据行

InnoDB 通过多版本并发控制机制（MVCC）解决了该问题：InnoDB 通过为每个数据行增加两个隐含值的方式来实现，这两个隐含值记录了行的创建时间、过期时间以及每一行存储时间发生时的系统版本号，每个查询根据事务的版本号来查询结果

通过强制事务排序，使其不可能相互冲突，从而解决幻读问题。简而言之，就是在每个读的数据行上加上共享锁实现，这个级别会导致大量的超时现象和锁竞争，一般不推荐使用

为了解决数据库并发控制问题，如走到同一时刻客户端对同一张表做更新或者查询 *** 作，需要对并发 *** 作进行控制，因此产生了锁

共享锁的粒度是行或者元组（多个行），一个事务获取了共享锁以后，可以对锁定范围内的数据执行读 *** 作

排他锁的粒度与共享锁相同，一个事务获取排他锁以后，可以对锁定范围内的数据执行写 *** 作

有两个事务 A 和 B，如果事务 A 获取了一个元组的共享锁，事务 B 还可以立即获取这个元组的共享锁，但不能获取这个元组的排他锁，必须等到事务 A 释放共享锁之后。如果事务 A 获取了一个元组的排他锁，事务 B 不能立即获取这个元组的共享锁，也不能立即获取这个元组的排他锁，必须等到 A 释放排他锁之后

意向锁是一种表锁，锁定的粒度是整张表，分为意向共享锁和意向排他锁。意向共享锁表示一个事务有意对数据上共享锁或者排他锁。有意表示事务想执行 *** 作但还没真正执行

锁的粒度主要分为表锁和行锁

表锁的开销最小，同时允许的并发量也是最小。MyISAM 存储引擎使用该锁机制。当要写入数据时，整个表记录被锁，此时其他读/写动作一律等待。一些特定的动作，如 ALTER TABLE 执行时使用的也是表锁

行锁可以支持最大的并发，InnoDB 存储引擎使用该锁机制。如果要支持并发读/写，建议采用 InnoDB 存储引擎

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品，是最流行的关系型数据库管理系统之一。

端口是3306。

表很多时，使用linux脚本，需要根据需要修改一下：

和创建一样，可以加上 if exists

可两篇文章：

如：

用于在已有的表中添加、删除或修改列。

添加 ADD

或

默认是添加到最后，但可以指定位置。FIRST ：添加最前

AFTER 字段名> ：添加指定字段之后

例子：

删除 DROP

修改 MODIFY 主要修改原列的类型或约束条件 同样可以用 FIRST 和 AFTER 字段名> ，代表的是修改到哪里。

修改字段名 CHANGE

可以把表2的数据复制到表1中，但 不能复制约束性条件 。

单行

多行，注意 只有一个VALUES ：

不写 (行1, 行2...) 这一部分的话，默认一一对应

除了以上方法外，还可以用SET为每一行附上相应的值。

假如没有筛选的话，就给全部都修改了。可以用 WHERE 筛选。

假如 没有筛选的话，就给全部删除了 。相当于清空。

清空

先把表删除，然后再建一个。与 DELETE FROM 相比， TRUNCATE 的效率更快，因为 DELETE FROM 是把记录逐条删除的。

查询执行的顺序

FROM -->WHERE -->SELECT -->GROUP BY -->HAVING -->ORDER BY -->LIMIT

注意

当数据很大，上百万的时候，使用LIMIT ... OFFSET ..的方式进行分页十分浪费资源且耗时长。最好是结合WHERE使用，如：

REGEXP 使用正则表达进行匹配。 查询时，需要搭配WHERE或HAVING使用 。

两个表之间有交集且要用到两个表的数据时，可以使用内连接查询。

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

用法：

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。 把LEFT JOIN的表1、表2调换顺序，就是REGHT JOIN 。

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行. 相当于结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

但 MySQL中不支持 FULL OUTER JOIN 。

即SELECT嵌套。

IN 一个查询结果作为另一个查询的条件。 如：

EXISTS 用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。True时执行。 如：

索引的本质是一种排好序的数据结构。利用索引可以提高查询速度。

常见的索引有：

MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。 外键的使用条件：

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联 *** 作。

对已有的两个表增加外键 比如：主表为A，子表为B，外键为aid，外键约束名字为a_fk_b

为子表添加一个字段，当做外键

为子表添加外键约束条件

假如删除记录报错： [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...)

这是因为MySQL中设置了foreign key关联，造成无法更新或删除数据。可以通过设置 FOREIGN_KEY_CHECKS 变量来避免这种情况。 第一步：禁用外键约束，我们可以使用： SETFOREIGN_KEY_CHECKS=0 第二步：删除数据 第三步：启动外键约束，我们可以使用： SETFOREIGN_KEY_CHECKS=1 查看当前FOREIGN_KEY_CHECKS的值，可用如下命令： SELECT @@FOREIGN_KEY_CHECKS

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要 保留 相同行，使用 UNION ALL 。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后 。

内置函数很多, 见: MySQL 函数

我们一般使用 START TRANSACTION 或 BEGIN 开启事务, COMMIT 提交事务中的命令, SAVEPOINT : 相当于设置一个还原点, ROLLBACK TO : 回滚到某个还原点下

一般的使用格式如下:

开启事务时, 默认加锁

根据类型可分为共享锁（SHARED LOCK）和排他锁（EXCLUSIVE LOCK）或者叫读锁（READ LOCK）和写锁（WRITE LOCK）。

根据粒度划分又分表锁和行锁。表锁由数据库服务器实现，行锁由存储引擎实现。

除此之外,我们可以显示加锁

加锁时, 如果没有索引，会锁表，如果加了索引，就会锁行

InnoDB默认支持行锁，获取锁是分步的，并不是一次性获取所有的锁，因此在锁竞争的时候就会出现死锁的情况

解决方法:

即ACID特性:

由于并发事务会引发上面这些问题, 我们可以设置事务的隔离级别解决上面的问题.

MySQL的默认隔离级别（可重复读）

查看当前会话隔离级别

方式1

方式2

设置隔离级别

主从集群的示意图如下:

主要涉及三个线程： binlog 线程、 I/O 线程和 SQL 线程。

同步流程:

由于MySQL主从集群只会从主节点同步到从节点, 不会反过来同步, 所以需要读写分离

读写分离需要在业务层面实现 , 写数据只能在主节点上完成, 而读数据可以在主节点或从节点上完成

索引是帮助MySQL高效获取数据的排好序的数据结构

MySQL的索引有

推荐两个在线工具:

简单来说, B树是在红黑树(一个平衡二叉树)的基础上将一个节点存放多个值, 实现的, 降低了树的高度, 每个节点都存放索引及对应数据指针, 同一层的节点是递增的

而B+树在B树的基础上进行优化, 非叶子节点存放 子节点的开始的索引, 叶子节点存放索引和数据的指针, 且叶子节点之间有双向的指针

如下示意图:

不同的引擎, 主键索引存放的数据也不一样, 比如常见的 MyISAM 和 InnoDB

MyISAM 的B+树叶子节点存放表数据的指针, InnoDB 的B+树叶子节点存放处主键外的数据

其他的:

即多个列组成一个索引, 语法:

由于联合索引的B+树的结构, 根据列建立, 所以我们的查找条件也要根据索引列的顺序( where column1=x, column2=y,columnN... ), 否则会全表扫描

如果你对列进行了 （+，-，*，/，!） , 那么都将不会走索引。

OR 引起的索引失效

OR 导致索引是在特定情况下的，并不是所有的 OR 都是使索引失效，如果OR连接的是 同 一个字段，那么索引 不会失效 ， 反之索引失效 。

这个我相信大家都明白，模糊搜索如果你前缀也进行模糊搜索，那么不会走索引。

这两种用法，也将使索引失效。另 IN 会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描, 见: MySQL中使用IN会不会走索引

不走索引。

走索引。

所以设计表的时候, 建议不可为空, 而是将默认值设置为 "" ( NOT NULL DEFAULT "" )

---