Mysql事务(MVCC实现原理)、锁、sql优化

一.事务

数据库事务就是访问、操作各种数据的一个数据库操作序列, 是由事务开始到事务结束之间全部的执行过程组成的, 事务处理可以用来维护数据库的完整性, 保证成批的sql要么全部执行要么全部都不执行, 当然在mysql中只有使用了Innodb数据库引擎的数据库或表才有事务.

事务的特性:

1.原子性: 在一个事务的所有的操作中, 要么全部执行完成, 要么全部不执行, 如果执行过程中某个环节出现了错误, 那么会回滚到事务开始执行前的状态, 保证事务没有发生过.

2.持久性: 事务处理结束后, 对数据的修改是永久的, 即便系统出现了故障, 也没有关系

3.隔离性: 数据库允许多个事务对数进行读写和修改, 所以可能会导致交叉执行而导致一个事务中得到的数据不一致的结果, 隔离性就可以防止此类事件发生, 事务隔离包括了四种隔离级别, 分别是: 读未提交, 读已提交, 不可重复读, 串行化.

4.一致性: 在事务开始之前和结束之后, 要保证数据库的完整性, 写入和读取的数据必须符合所有的预设规则, 之前的三个特性都是为了保证一致性.

事务的实现原理

Mysql有很多日志文件, 比如二进制文件、错误日志、查询日志等, 而Innodb引擎提供了两种专门实现事务的日志, 一种是redolog(重做日志), 另一种是undolog(回滚日志), 其中, redolog用于保证事务的持久性, 而undolog用于保证事务事务的原子性和隔离性.

1.原子性的实现

实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的 sql 语句。InnoDB 实现回滚，靠的是 undo log：当事务对数据库进行修改时，InnoDB 会生成对应的 undo log；如果事务执行失败或调用了 rollback，导致事务需要回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。 undo log 属于逻辑日志，它记录的是 sql 执行相关的信息。当发生回滚时， InnoDB 会根据 undo log 的内容做与之前相反的工作：对于每个 insert，回滚时会执行 delete；对于每个delete，回滚时会执行 insert；对于每个 update，回滚时会执行一个相反的 update，把数据改回去。

2.持久性实现

redo log 叫做重做日志，是保证事务持久性的重要机制。当 mysql 服务器意外崩溃或者宕机后，保证已经提交的事务，确定持久化到磁盘中的一种措施。innodb 是以页为单位来管理存储空间的，任何的增删改差操作最终都会操作完整的一个页，会将整个页加载到 buffer pool 中，然后对需要修改的记录进行修改，修改完毕不会立即刷新到磁盘，而且仅仅修改了一条记录，刷新一个完整的数据页的话过于浪费了。但是如果不立即刷新的话，数据此时还在内存中，如果此时发生系统崩溃最终数据会丢失的，因此权衡利弊，引入了 redo log，也就是说，修改完后，不立即刷新而是记录一条日志，日志内容就是记录哪个页面，多少偏移量，什么数据发生了什么变更。这样即使系统崩溃，再恢复后，也可以根据 redo 日志进行数据恢复。另外，redo log 是循环写入固定的文件，是顺序写入磁盘的

事务的隔离级别

Mysql是一个客户端/服务器架构的软件, 那么就会存在多个客户端连接一个服务器, 所以就会出现服务器同时处理多个事务, 这样就可能就会导致不同的事务访问到的数据都是一致的, 理论上在一个事务执行的过程中, 其他事务是需要排队等待的, 当事务提交之后, 其他事务才可以访问该数据, 但是这样对性能影响又比较大, 所以提出了事务隔离级别原则.

查看隔离级别

SELECT @@global.transaction_isolation,@@transaction_isolation;

Mysql 数据提供四种不同级别的隔离级别,实际开发中可以根据不同的需要场景选择不同的隔离级别,除了串行级别 以外其他级别都会存在某种问题.

1.读未提交(read uncommitted) : 一个事务可以读到另一个事务未提交的数据, 这会带来脏读(第一个事务有可能回滚), 幻读, 不可重复读的问题.

2.读已提交(read committed): 一个事务读到另一个事务已提交的数据, 虽然解决了脏读的问题, 但是存在不可重复读和幻读的问题.

不可重复读就是一个事务中第一次查询和第二次查询的结果不一致.

3.可重复读(repeatable read MySQL 默认隔离级别): 同一个事务中多次读取相同的数据返回的结果是一样的, 解决了脏读和不可重复读, 但是还存在幻读的问题.

4.串行化(serializable): 事务串行执行, 避免了上述所有的问题, 安全性极高, 但是效率很低.

事务隔离级别实现原理(MVCC)

MVCC也称多版本并发控制, 配合undolog和版本链让事务的读-写和写-读功能可以并发执行, 从而提高系统的性能.

MVCC使得数据库不会对读操作进行加锁, 提高数据库的并发处理能力, 借助MVCC可以实现读已提交和可重复读隔离级别.

Innodb的MVCC是通过在每行记录的后面加两个隐藏的列来实现的, 一个保存了事务的id, 一个保存了回滚指针.

trx_id： 每次对某记录进行改动时，都会把对应的事务 id 赋值给 trx_id 隐藏列。

roll_pt： 每次对记录进行改动时，都会把旧的版本写入到 undo 日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

对该记录每次更新后，都会将旧值放到一条 undolog 中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pt 属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务 id，这个信息很重要。

ReadView 是什么

快照读, 也就是用来判断版本链中那个事务是当前事务可见的.

readview中包含的内容:

m_ids 。在生成ReadView时，当前系统中活跃的读写事务的事务id列表，即还未提交。
min_trx_id 。在生成ReadView时，当前系统中活跃的读写事务中最小的事务id；也就是m_ids中的最小值。
max_trx_id 。在生成ReadView时，系统应该分配给下一个事务的事务id值。
creator_trx_id 。生成该ReadView的事务的事务id。

如何通过readview来判断记录中哪个版本是当前事务可见的呢?

1.如果被访问版本的txr_id=当前readview中的creator_trx_id, 那么表示当前事务正在访问自己已经修改过的记录, 所以是可见的

2.如果txr_id<readview中的min_trx_id, 那么表示该版本的事务在当前事务生成readview之前就已经提交事务了, 所以是当前事务可见的.

3.如果txr_id>readview中的max_trx_id, 那么表示该版本的事务在当前事务生成readview之后才开启的,所以是当前事务不可见的.

4.如果txr_id在min_trx_id和max_trx_id之间的, 那么就需要判断txr_id在不在m_ids中, 那么就有两种情况:

(1)如果txr_id在m_ids中, 表示创建readview时, 该版本的事务还是活跃的, 所以是不可被访问的.

(2)如果txr_id不在m_ids中, 表示创建readview时, 该版本的事务已经被提交了, 所以是可被访问的.

Readview生成的时机

读已提交(READ COMMITTED)和可重复读(REPEATABLE READ)最大的区别就在于它们的生成readview的时机不同.

1.读已提交: 在一个事务中, 每次读取数据前都会生成一个readview.

2.可重复读: 在一个事务中, 只有第一次读取数据时会生成readview, 每一次读取都是从同一个readview中读取数据

MVCC总结

MVCC就是通过版本链和Readview或者说版本链和undolog, 来控制并发事务访问同一记录的行为. Mysql就是通过事务列表中的几种id和当前事务id进行做比较来判断当前事务id访问的版本是否可见, 版本可见的情况就包括:

当前版本的事务id是否小于,大于,等于事务列表中的几种id.

每一次读取数据前都会生成对应的readview和第一次的读取数据时生成readview分别对应着读已提交(READ COMMITTED)和可重复读(REPEATABLE READ).

Mysql锁

Mysql中的锁分为表锁, 行级锁, 间隙锁.

表锁: 表锁是Mysql中粒度最大的一种锁, 表示对当前操作的整张表加锁, 适用于大量批量的操作, 例如: 表的重建以及全表备份等, 通过 LOCK TABLE 和 UNLOCK TABLES 语句实现。

同时因为表锁需要所住整张表, 所以并发性能较差, 而且加锁本身是需要消耗资源的(获得锁, 检查锁, 释放锁等), 因此在锁定数据较多的情况下, 可以选择使用表锁从而节省大量资源, Mysql中不同的存储引擎使用的锁是不一样的, MyIsam支持的是表锁, InnoDB支持表锁和行锁.

行锁: 行锁是Mysql中粒度最小的一种锁, 只对当前操作的行加锁, 其他事务可以访问其他行的数据, 适用于并发高的场景, 通过 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 语句实现, 但加锁的开销也大, 也可能会出现死锁, 不过锁冲突的概率最低.

行级锁还分为共享锁和排它锁.

1.共享锁（Shared Lock）: 也叫读锁, 简称S锁, 在同一时间多个事务都可以持有共享锁, 持有共享锁的事务之间可以并发执行, 即读锁不会阻塞读锁, 但是如果有一个事务持有了共享锁, 其他事务便不可以获得该行的排它锁, 只能等待该共享锁释放.

2.排它锁（Exclusive Lock) : 也叫写锁, 在同一时间内只能有一个事务持有该锁，持有排他锁的事务既可以读取也可以修改该行数据，其他任何事务都不能再获得该行的共享锁和排他锁，直到该排他锁被释放。

间隙锁: 间隙锁锁的是一个区间, 为了解决幻读的问题, InnoDB引入了间隙锁, 同时也满足了串行化隔离级别的要求.

幻读: 幻读指的就是一个事务在同一范围内查询, 后一次查询到了前一次没有查询到的行.

举例来说，假如 user 表中只有 101 条记录，其userid 的值分别是 1,2,…,100,101，下面的 SQL： select * from user where userid > 100 for update;是一个范围条件的检索，InnoDB 不仅会对符合条件的 userid 值为 101 的记录加锁，也会对userid 大于 101（但是这些记录并不存在）的"间隙"加锁，防止其它事务在表的末尾增加数据

锁冲突

在多用户并发访问数据库时，如果多个用户同时请求对同一数据进行修改，就会产生锁冲突问题。锁冲突是指在一个事务中，如果要访问一个已经被加锁的资源，那么就需要等待这个锁被释放，从而导致事务等待，降低了数据库的性能。

锁冲突一般分为两种类型：共享锁和排他锁。共享锁（Shared Lock）又称读锁，是一种共享锁定机制，多个事务可以同时持有共享锁，且不会阻止其他事务获得共享锁，用于保证并发读取时的数据一致性。排他锁（Exclusive Lock）又称写锁，是一种互斥锁定机制，一旦一个事务获取了排他锁，其他事务就无法获得共享锁和排他锁，用于保证事务操作的原子性。

Sql优化

1. 查询时尽量不要用到select * 查询, 而是用具体字段.

1.可以节省资源, 较少网络和IO的开销, 因为我们需要从磁盘中读取数据, 我用的字段会增加网络的开销和IO的开销.

2.可能对数据的安全也有影响, 假如我们有一个类包含账户、密码等，使用select *可能导致用户的信息泄露。或者是后期添加了一些私密的信息。

3.不会使用到覆盖索引.

2.避免在where子句中使用or来连接条件.

因为使用or可能会导致引擎放弃使用索引, 从而进行全表扫描

select id from t where num=10 or num=20

正确使用方法如下:

select id from t where num=10 
union all 
select id from t where num=20

3.模糊查询也会导致全表扫描

select id from t where name like '%abc%'

4.尽量使用数值代替字符串类型

主键（id）：primary key 优先使用数值类型 int

性别（sex）：0 代表女，1 代表男；数据库没有布尔类型，mysql 推荐使用 tinyint

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；

而对于数字型而言只需要比较一次就够了；

字符会降低查询和连接的性能，并会增加存储开销；

5.使用varchar代替char

因为varchar是变长字段, 按数据内容实际长度来存储数据, 可以节省存储空间;

char按声明代销来存储, 不会补足空余空间.

对于查询来说, 在一个相对较小的字段内搜索, 查询效率更高.

6.对查询进行优化. 尽量避免全表扫描, 首先应考虑在where和order by涉及的列上建立索引

7.尽量避免索引失效

1.避免在where子句中对null值进行判断, 否则导致引擎放弃使用索引对全表进行扫描.

如：select id from t where num is null

可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，

然后这样查询：select id from t where num=0

2.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num

in(1,2,3),对于连续的数值，能用 between 就不要用 in ,select id from t where num between 1 and 3

3.应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行

8.inner join 、left join、right join，优先使用 inner join

三种连接如果结果相同，优先使用 inner join

inner join 内连接，只保留两张表中完全匹配的结果集；

left join 会返回左表所有的行，即使在右表中没有匹配的记录；

right join 会返回右表所有的行，即使在左表中没有匹配的记录；

9. 提高 group by 语句的效率

反例:先分组，再过滤

正例:先过滤，后分组

10. 清空表时优先使用 truncate

truncate table 比 delete 速度快，且使用的系统和事务日志资源少.

delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table 通过释放存储表数据所用的数据页来删除数据.

11. 表连接不宜太多，索引不宜太多，一般 5 个以内

联的表个数越多，编译的时间和开销也就越大

每次关联内存中都生成一个临时表

应该把连接表拆开成较小的几个执行，可读性更高

12. 避免在索引列上使用内置函数

使用索引列上内置函数，索引失效。

Sql执行计划(explain)

Explain : 使用explain可以模拟优化器执行sql查询, 从而知道MySQL是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。'

Explain的作用:

表的读取顺序, 数据读取操作的操作类型, 哪些索引可以使用, 哪些索引被实际使用, 表之间的引用, 每张表有多少行被优化器查询

在 select 语句之前增加 explain 关键字，执行查询会返回执行计划的信息，而不是执行 SQL。

EXPLAIN SELECT * FROM USER WHERE id = 1

expain 出来的信息有 12 列，分别是:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

概要描述

id:选择标识符

select_type:表示查询的类型。

table:输出结果集的表

partitions:匹配的分区

type:表示表的连接类型

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

ref:列与索引的比较

rows:扫描出的行数(估算的行数)

filtered:按表条件过滤的行百分比

Extra:执行情况的描述和说明

1.id

SELECT 识别符。这是 SELECT 的查询序列号

id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行

EXPLAIN SELECT * FROM employee e,dept d WHERE e.deptId = d.id

EXPLAIN SELECT * FROM employee e WHERE e.deptId = (SELECT id FROM dept d WHERE d.id = 1)

2. select_type

表示查询中每个 select 子句的类型

1.SIMPLE(简单 SELECT，不使用 UNION 或子查询等)

2.PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的

select 被标记为 PRIMARY)

3.SUBQUERY(子查询中的第一个 SELECT，结果不依赖于外部查询)

4.DERIVED(派生表的 SELECT, FROM 子句的子查询)

5.UNION(UNION 中的第二个或后面的 SELECT 语句) 6.UNION RESULT(UNION 的结果，union 语句中第二个 select 开始后面所有 select)

3.type

对表访问方式，表示 MySQL 在表中找到所需行的方式，又称“访问类型”。

常用的类型有：system>const>eq_ref>ref>range>index>ALL（从左到右，性能从好到差）

4.system: 表只有一行记录（等于系统表），平时不会出现，这个也可以忽略不计.

5.const: 表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。

6.eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描.

7.ref: 非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体.

8.range: 只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

9.index: Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树。这通常比 ALL 快，因为索引文件通常比数据文件小。也就是说虽然 all 和 Index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的）

10.All: Full Table Scan，将遍历全表以找到匹配的行一般来说，得保证查询至少达到 range 级别，最好能达到 ref.

11.possible_keys 显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用 key

实际使用的索引。如果为 NULL，则没有使用索引,或者索引失效.

12.ken_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好.

13.ref 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

EXPLAIN SELECT * FROM employee e,dept d,admin a WHERE e.deptId = d.id AND e.adminId=a.id

AND e.age=20

14.rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数.

15.Extra

额外的信息说明

Using filesort: 当 Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候,Mysql 无法利用索引完成排序的操作称为”文件排序”.

Using temporary: 使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

Using index

表示相应的 select 操作中使用了索引，避免访问了表的数据行，效率不错！

如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表明索引用来读取数据而非执行查找动作。

Using where

表示使用到了索引 , 但是也进行了 where 过滤