mysql事物学习宝典

qq_35422696

已于 2023-02-23 20:56:05 修改

阅读量114

点赞数

分类专栏： mysql 文章标签： mysql

于 2021-02-19 09:54:57 首次发布

本文链接：https://blog.csdn.net/qq_35422696/article/details/112916797

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

最近发现了一个比较好的学习网站和工具下载网站，相当便宜，感兴趣的同学请移步：

昂炎数据（www.ayshuju.com）

1、sql语句

正则匹配：

select * from person where name regexp "ok$"

2、事务：

mysql中的事务

事务的特性：acid

A：原子性，一个事务要么全部成功，要么全部失败

C：一致性，事务开始之前和事务结束之后，数据库的完整性没有被破坏，这意味着输入是符合数据结构的规则的

I：隔离性，允许多个事务对数据进行读写和修改；可以防止多个事务并发执行，导致数据不一致。

级别：读未提交、读已提交、可重复读、串行化

D：持久性，事务结束后，对数据的修改是永久的

开启事务：begin 或者 start transaction，并不能立即启动事务，而是在执行第一个sql时才启动。如果想立即启动，请使用：start transaction with consistent snapshot

提交事务：commit

回滚：rollback

创建保存点：savepoint name

删除保存点：release savepoint name

回滚到某个保存点：rollback to name

设置事务的隔离级别：set transaction （read uncommited,read commited,repeatable read,serializable)

问题“：

脏读：事务A读取到了缓存中事务B未提交的数据

不可重复读：事务A读取data，事务B修改data，事务A再次读取data，发现不一样了

幻读：事务A读取data，事务B新增data，事务A再次读取，发现数据不一样了（读到了新增的）

如何解决上面的三个问题呢，那就使用事务的隔离性解决

1、读未提交

事务A可以读到事务B未提交的数据

如何实现的呢

这个其实就是直接读记录

2、读提交

事务A不能读到事务B未提交的数据，只能读到B已提交的数据

实现：

在事务开始后，sql执行之前，创建一个视图。这样只读视图内的数据

3、可重复读

事务A一开始读到的是1，到事务结束读到的都是1

实现：

在事务开始前就创建一个视图，这样在整个事务期间，就只能读到1个数值

视图这里有一个概念，叫回滚视图，就是假如加视图时，视图是1，但是再去拿值的时候，表上的视图已经更新到3了，但是为了保证一致性，就需要把表的视图回滚到1，取完值之后，再回滚到3

4、串行化

这个直接就用锁了，读的时候加读锁，写的时候加写锁，事务冲突了，就等待上一个事务结束；

这样的话，事务A读到的数据，事务B就不能修改了

例如：

id：1——》2——3——》4

id在从1改到4的过程中，假如一个事务开始读的是1，想再次读到1，也就是隔离级别在可重复读的情况下，这时只有回滚版本才行，把视图回滚到是1时候的视图。这个时候就会存在一个回滚日志。

回滚日志记录了视图的回滚切换的过程。

这里还有一个长事务：

一个事务执行过长，会导致回滚日志也相当长，会有各种各样的问题

https://www.cnblogs.com/rainydayfmb/p/9355259.html

多版本并行控制（MVVC）

mvcc是比较麻烦的，这里用自己的理解讲解一下：

针对的是可重复读隔离级别：

这里有两个名词需要理解下：事务号，视图数组

事务号容易理解，即使事务的id

规则：在本事务前的已经提交的事务，那是可见的。

未提交的呢，看情况：如果在视图数组中，可见；

不在视图数组中，不可见

那么怎么才会在视图数组中，怎么样又不会呢？

这里有一个隐藏的知识点，事务C没有明显的写事务开始和事务结束，那么就是默认的，执行到sql时，加事务，sql执行结束，事务提交。

上图这三个事务，假如：事务a 的事务号是2，事务b的是3，事务c的是4，三个事务开始之前就存在的为1

那么a的视图数组即使[1,2],b的视图数组是[1,2,3],c的视图数组是[1,2,3,4]

a读到的为1，这个应该都能理解。那么b读到的是几呢？你可能觉得是2，其实是3.为什么呢，这里又得引入一个“当前读”的概念。

更新数据是先读后写，而且是基于现在数据的基础上的，事务B更新前，读了一下数据，读到了事务C更新后的数据，然后更新，这时数据是（1，3），而且打上的事务号是3，然后后面再读的时候，就读到了3.

除了更新默认有当前读以外，select也可以有当前读，比如 select * from table where id = 1 lock in share mode或者select * from table where id = 1 for update

索引

索引的类型：

1、普通索引

create Index index_name on table_name(column1(length))

char和varchar类型可以加length

alter TABLE table_name add index index_name on (column1)

drop index index_name on table_name

2、唯一索引

特点：索引列的值必须唯一，但允许有空值

创建：

unique index

3、组合索引

create index index_name on table_name (column1,column2)

假如在name，age，department上面建立组合索引，那么相当于建立了

name&&age&&department，name&&age，name这三个索引。

组合索引遵循最左组合原则。这意味着下面这些是用不了组合索引的：

select name,age,department from person where age = 20 and department = 3

select name,age,department from person where department = 3;

4、全文索引

fulltext

唯一索引和普通索引：

这里先介绍一下，innodb引擎读写的单位不是单条数据，而是数据页，和内存的的内存页概念差不多。

读数据时，如果该数据不在内存，就在磁盘上找到存在该数据的数据页，然后读入内存。

更新呢？如果数据在内存，那直接在内存改，改完存redolog就好了。但是如果不在内存呢？这时，数据库引擎会直接把该更新语句写入change buffer，当需要查询该数据时，再把数据从change buffer写入磁盘，写入的过程叫做merge。

唯一索引不使用change buffer，只有普通索引才使用

change buffer和redo log

redolog保存在磁盘中，在buffer pool中有重写日志的缓存。 changebuffer 是属于buffer pool的。

redo log是减少写的磁盘次数（把随机磁盘转为顺序写），change buffer是减少读的磁盘次数。

字符串索引

使用字符串索引两种方式：

1、alter table table_name add index index_name(email);

2、alter table table_name add index index_name(email(6));

一个是把整个字符串当作索引，另一个是前缀索引，把字符串的前6为作为索引

第一种优点：找数据时只用扫一次

缺点：占据的空间比较大

第二种优点：占据空间小

缺点：有可能要扫多次

例如寻找email为 qianduoduo@123.com的，前6位就是qiandu

扫描步骤：这时找到了5个索引为qiandu的数据，然后逐一从id找到该数据做比对

回表

例如：select * from k = 3

k有一个普通索引，主键索引是id

会先查k=3这条记录，然后再查k=3的id，然后再根据这个id去查这条记录

我们猜测一下为什么会出现回表的现象，其实这里的快速查找一直都是id，因为B+树是根据这个主键索引建造的，k的索引只不过是又多了一个类似索引表的东西，存储的内容是（k，id）这样的东西，那么先找到k，再根据k找到id，再根据id找到这条记录

这里又出来一个概念，覆盖索引。意思就是，当我们根据k找id时，那是不是就不需要回表了，找一次k就能拿到id了。这样看，似乎是理所当然的，但是如果把覆盖索引放到复合索引上呢

假如，我们创建一个复合索引：（name，age）索引表关于这个索引会怎么存？答案是：（id，name，age）。那这个时候，有个需求是需要根据name，找到age，如果我们单纯建立一个name索引，那么搜索步骤是这样的，先根据name找到id，再根据id找到这条记录，然后取出age，查询了两次。如果建立一个复合索引，如上面所说，那就只需要一次搜索了。

再根据上面的案例，说一下最左前缀原则：

name和age的复合索引，相当于建立了两个索引：name索引和（name，age）索引，所以上面的需求是根据name找age，建立的复合索引就必须是（name，age），而不能是（age，name），否则索引是没用的。

导致索引失效：

尽量不使用not in和<>

不要在列上进行计算：

select * from user where YEAR(birthday) < '2007'

应该改成：

select * from user where birthday < '2007-01-01'

mysql深入理解：

首先需要知道一个sql执行时经过了什么步骤；

1、连接

这里的连接是连接服务器和客户端，连接器负责这个板块，包括用户的权限检查等，用户名密码的检查。

2、server层

缓存

这是管理系统做的工作了，一个sql过来，会先查询缓存系统，查询过的sql语句和查询结果会以key-value的方式存储起来。

当这个表有任何更新改动时，都会通知缓存系统把关于这个表的所有缓存都清空。

分析器

例如：select * from table where id = 1

如果缓存查不到该sql，就要进入sql执行阶段。sql执行首先会交给分析器去分析语义，这里的语义包括词语分析，例如select ，要识别成sql语言，代表是一个查询语句；表名，需要识别成表的名称；把ID 识别成列ID

优化器

优化器主要做的事是，选择更高效的路径。例如，user这个表有三个索引，但是使用那个索引呢，这就需要优化器去抉择。或者做表连接的时候，各表的连接顺序是什么等

优化器的工作：

优化器的主要工作是索引的选择，直接上例子

表中有字段：id，a，b

在a，b上创建普通索引，插入10万条数据

select * from table where a between 1000 and 5000

这条sql，大家都知道，数据库会使用a索引（可以使用explain select * from table where a between 1000 and 5000查看这条语句的执行情况)

接下来呢，执行下图的步骤：

idata（）代表插入10万条数据。

这时再次执行上面那条sql，设置慢查询日志阈值为0（set long_query_time = 0)，将sql执行情况记入日志看具体执行

发现sql执行选择了b索引。

优化器选择索引的因素：扫描行数、是否使用临时表、是否排序等

扫描行数如何判断？innodb采用采样统计方式，选择N页数据页，计算页面不同值的平均数；当数据修改超过1/M时，重新计算

上面的语句，选择错误的索引是因为扫描行数预估错误了，那么为什么会预估错误呢？

原来是事务A先开启事务，事务B再删除所有数据，那肯定是不行的，得等A事务提交之后才行

如何修复这种索引预估错误？

答案：使用anlyze table t 可以解决

redolog和binlog

redo日志和binlog日志

这两个日志是关于更新语句很重要的东西

redo log是引擎的物理日志，都存了什么：存的是这条记录做了什么，比如更新了id这个字段，更新为3

bin log是server层的逻辑，都存了什么：statement存的是sql，row存的是更新前的值和更新后的值

redo log类似一个循环队列，大小是固定的，超过队列大小后，会删除一部分空间。

这两个日志的作用是什么：

为了不更新数据库，因为redo log在内存中，更新内存比直接IO来的方便的多。假如你要对一个字段频繁更新，一会儿等于1，一会等于2的，如果没有redo log，那就需要频繁的IO，更新之后从内存写到磁盘。redo log相当于一个记账功能，等我先不更新磁盘，我记下来，等一天结束了，没人了，我在自己慢慢往磁盘更新。

更新的步骤：

例如：

update user set id = 3

1、首先执行器从内存中或者硬盘上取出这条记录

2、然后，把记录读入内存

3、更新数据

4、将数据写入内存

5、写入redo log，redo log处于prepar状态

6、更新bin log

7、提交事务 redo log更新为commit状态