Navicat的安装使用与MySQL事务特性

吉吉吉吉吉吉吉吉吉吉

已于 2022-09-26 19:07:15 修改

阅读量962

点赞数

文章标签： mysql 数据库

于 2022-09-26 18:58:45 首次发布

本文链接：https://blog.csdn.net/liji133122/article/details/127056889

版权

②read committed（oracle默认隔离级别）

③repeatable read（mysql默认的隔离级别）

引言

我们的MySQL数据库已经在linux系统中成功安装运行了，但如果要在windows系统上管理数据库，那么我们还需要学习navicat这个管理数据库的工具。

一、navicat-15的下载安装

navicat premium-15官方下载Navicat | 下载 Navicat Premium 14 天免费 Windows、macOS 和 Linux 的试用版

选择合适的版本进行下载

详细的下载、安装可以参考以下文章

Navicat Premium15安装与激活(完整激活版)

二、navicat连接数据库

1、登录

在安装好navicat后，我们就可以开始连接我们的MySQL数据库了，打开navicat，点击连接，选择mysql进行登录

2、连接数据库失败

这里可能会出现登录失败的情况，如果你是学习阶段，开的虚拟机和navicat跑在一台机器上，排除数据库没有开启成功、主机防火墙未设置策略或未关闭（错误码1130）等问题，可能存在的问题是windows主机没有开启telnet功能，需要做如下设置

进入windows功能开启telnet并重启完成配置。

做完这一步还需要进入数据库修改Host，root用户默认只能从localhost进行登录，因此想要用navicat登录root的话需要进入mysql.user表进行设置，“%”是一个通配符，Host列设置为%，表示所有的IP都有连接权限。

#修改mysql.user表中用户root的host字段为%
mysql> update mysql.user set host= '%' where user='root';
 
#让配置生效
mysql> flush privileges;
 
#当然，给root用户设置任何host都可以登录明显是不安全的，这里只是试试
mysql> select Host,User from mysql.user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
3 rows in set (0.00 sec)

错误码2058，密码的加密方法变更，需要在linux系统中登录mysql执行如下语句

mysql> alter user 'root'@'%' identifined with mysql_native_password by '你的密码'

三、navicat能做的基础操作

1、数据库的基本操作

我们成功连接上了数据库，这里使用的mysql版本是mysql-5.7。可以看到我们所有的库，比起linux里还要繁琐的输入

mysql>show databases；

这种可视化界面可谓是一览无余

我们直接双击进入test库，双击“表”进入，连show tables也不用输了，每一个表都看的清清楚楚。

通过右键点击，我们可以创建、编辑、删除......数据库更改数据库和表的属性

2、针对表的操作

点击操作添加、插入、删除表中的字段，直接点击名、类型、长度等输入字段的属性，ctrl+s或点击“保存”来进行保存

双击左键进入指定的表可以对表的记录进行操作，拿上图的这个表做示范，输入相应的数据，name=“zhangsan” gender=“male” ，直接按方向键下，id=1自动填入，

测试一下我们设置的“not null”，我们设置了id、name不能为空，第二行只输入一个id=2，直接ctrl+s保存，提示报错了

未进行保存，字段前方会有一个星号提示，然而不知是我电脑屏幕太小了还是怎么，不仔细看还真看不到它。

当我想要给gender设置默认值为unknow(未知)时，按下保存却报错了，没想到这里居然要写的和SQL语句一样，加上引号括起来才行

在这个界面创建外键，我们先清空表内容（最好创建表的时候设置，否则会报错），所关联的主表的主键和foreign key需要在类型、长度上保持一致，同时外键不可以设置自增、无符号、填充零。

3、SQL语句管理数据库

在navicat中，也可以点击新建查询，在命令行中输入SQL语句的方式管理数据库中的数据，并可以使用Tab键对命令进行补全

4、用户管理

点击用户进入用户的管理界面，在这里可以直观的看到当前数据库（非单个库）的所有用户，也可以对他们进行更改密码，新建、删除，权限设置，以及可登录的主机设置（文章开头的设置）。

5、视图的创建

点击新建视图

点击进入视图创建工具

双击要创建进入的表格，选择WHERE添加相同条件，这里是两个表中的“id”。

接着选择要添加进入的数据

点击构建并运行

视图创建成功

6、表的导出与导入

点击导出，当前全部记录

指定导出格式

导出后的表格样式

7、数据库的备份操作

点击备份--新建备份

进行属性修改

进行备份---备份完成

四、MySQL数据库的事务

1、事务特性

日志文件（redo log 和 undo log）

锁技术

MVCC

原子性：Atomicity当前事务的操作要么同时成功，要么同时失败。由undo log来保证

一致性：Consistency使用事务的最终目的，事务完成前、后，数据必须处于一致状态，由业务代码正确逻辑保证

隔离性：Isolation在事务并发执行时，他们内部的操作不能相互干扰

持久性：Durability一旦提交了事务，它对数据库的改变就应该是永久的。由redo log日志来保证

InnoDB四种隔离级别：read uncommit（有脏读问题）、read commit（有不可重复读问题）、repeatable read（有幻读问题）、serializable（解决前三种问题，但效率过低）

redo 日志

前面还提到了持久性是靠redo log来保证的，那么redo log是什么，为什么可以保证mysql里的数据不丢失的呢

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。

redo是innodb引擎特有的，记录了事务里对数据的修改，由于只记录了修改，大大减小的IO频率。一个事务会包含许多SQL，而一条SQL修改语句，会产生很多组redo，而这一组（学名：Mini-Transaction）才是写入的最小单元。mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。那么问题来了，如果还没来的及同步的时候宕机或断电了怎么办？这样会导致丢部分已提交事务的修改信息！所以就引入了redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后再读取redo log恢复最新数据。

2、隔离级别

①read uncommitted

两个事务同时对同一个数据进行只读不提交操作，一个进行修改，一个来进行读取数据。

操作的表：

新建两个查询，模拟两个事务同时对同一个数据进行只读不提交操作，一个进行修改，一个来进行读取数据。

查询1：

set tx_isolation='read-UNCOMMITTED';
begin;
UPDATE class set account=account+100 where name='ljp';
ROLLBACK;

查询2：

SET tx_ISOLATION='read-UNCOMMITTED';
begin;
select * from class;
commit;

我们先选中除了回滚的语句进行操作，然后去查看

name为ljp的account成功增加了100，只读不提交的方式的事务可以读取到其他同方式的事务的数据，但这会前者的更改没有进行事务提交，他还可以继续操作，比如进行回滚操作

执行回滚操作，再进行查看，到手的100没了，这谁受得了，要是用这种方式来进行一些商业操作，很明显，数据失去了它的可靠性、安全性。

②read committed（oracle默认隔离级别）

读且提交可以解决上面的读不提交带来的脏读问题，在同样的表上，使用相同的方法，即前者执行给name为ljp的account+100但不提交事务，我们进行再次查询，发现只要前者的事务没有进行提交，表中的数据不会进行更改，也就是说我们查询的结果是前者提交事务前，或后的结果（可以确定的数据内容）

set tx_isolation='read-COMMITTED';
begin;
UPDATE class set account=account+100 where name='ljp';
#只执行上面的语句，不进行事务提交
 
COMMIT;

表中的内容没有变动

只有当前者提交了事务，才可以查询到变动

set tx_isolation='read-COMMITTED';
begin;
UPDATE class set account=account+100 where name='ljp';
COMMIT;

但是紧接着，问题又出现了，当我们开启另一个事务，再次对数据进行修改，并再次进行查看时

set tx_isolation='read-COMMITTED';
begin;
UPDATE class set account=account+100 where name='ljp';
COMMIT;
 
#执行以下部分
begin;
UPDATE class SET account=account-50 where name='ljp';
COMMIT;

数据发生变动了，也就是说，上一个已提交事务发生的变动已经不能通过这种方法查到了（不可重复读取）

③repeatable read（mysql默认的隔离级别）

可重复读取

我们继续使用上面这个截图的表，试一试repeatable read来解决不可重复读取

我们执行以下操作

set tx_isolation='REPEATABLE-READ';
begin;
UPDATE class set account=account+100 where name='ljp';
COMMIT;
 
 
#先更改然后查询
 
 
 
SET tx_ISOLATION='REPEATABLE-READ';
begin;
select * from class;

紧接着再开启一个事务

set tx_isolation='REPEATABLE-READ';
begin;
UPDATE class set account=account+100 where name='ljp';
COMMIT;
 
begin;
UPDATE class SET account=account+2000 where name='ds';
COMMIT;
 
 
 
#增加一个查询
SET tx_ISOLATION='REPEATABLE-READ';
begin;
select * from class where name='ljp';
select * from class where name='ds';#只执行该语句

表内此时真正的数据

当然，此级别也有一定的问题存在，虽然可以解决数据存在但被改动（版本不同无法回溯查看）的问题，但是不能监测数据还是否存在，也称作幻读；例如，事务1先对某一符合条件的字段进行查询，随后决定进行一定的操作，但这时，事务2在事务1未提交的过程中进行了操作，删除了事务1查找的字段内容，此时表中已经不存在相应的数据了，但对于事务1来说，仍能读取的到。

④serializable

串行执行操作，只要有一个事务在操作，其他事务就需要等待在操作的事务提交之后。如果一个事务开启后，没有进行提交，就会一直阻塞。读与写不会并行执行，直接没有并发的问题，但因此效率低下基本不会使用。

serializable相当于给事务加上了锁

读锁：select。。。 lock in share mode；

共享的，多个事务可以同时读取，但不允许其他事务进行修改

写锁：select。。。 for update；

排他的，阻塞其他的写锁和读锁（update/delete/insert都会加写锁）

也就是我们先进行一个查询（不要提交）

SET tx_ISOLATION='SERIALIZABLE';
begin;
select * from class;

再去开启第二个事务对表做更改

SET tx_ISOLATION='SERIALIZABLE';   
 
begin;
UPDATE class SET account=account+2000 where name='ds';
COMMIT;

只有查询的事务提交后，后面的修改事务才会被执行，否则一直阻塞。

五、MVCC

MVCC（Multi-Version Concurrency Control）即多版本并发控制。是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

在 MySQL InnoDB 中主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

1、什么是当前读和快照读？

①当前读（悲观锁的具体实现）

像上文中提到的（select。。。lock in mode）共享锁和（select。。。for update/insert/delete）排他锁这些操作都属于当前读，他们读取的都是记录的最新版本，并且读取时还需要保证其他事务不能修改其正在读取的当前记录。

②快照读（MVCC非阻塞的具体实现）

像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC ,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

2、MVCC的原理

从上面navicat事务的隔离级别可以发现，数据库有三种并发的场景：（读while读）（读while写）（写while写），当然，（读while读）没有什么问题，但后两者存在着冲突

①隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的 DB_TRX_ID；DB_ROLL_PTR等字段

DB_TRX_ID
6 byte，最近修改事务 ID：记录最后一次修改该记录的事务 ID

DB_ROLL_PTR
7 byte，回滚指针，指向这条记录的上一个版本的位置（存储于 rollback segment 里）

DB_ROW_ID
6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引

还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了

如上图，DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID 是当前操作该记录的事务 ID ,而 DB_ROLL_PTR 是一个回滚指针，用于配合 undo日志，指向上一个旧版本

②undo日志

insert undo log（事务在 insert 新记录时产生）只在rollback时需要，在事务提交后可以被立即丢弃

update undo log（事务在进行 update 或 delete 时产生）提交后直到没有事务涉及后，由purge线程统一清除

*purge：更新或者删除操作都只是设置一下老记录的 deleted_bit ，并不真正将过时的记录删除。为了节省磁盘空间，InnoDB 有专门的 purge 线程来清理 deleted_bit 为 true 的记录。为了不影响 MVCC 的正常工作，purge 线程自己也维护了一个read view（这个 read view 相当于系统中最老活跃事务的 read view ）;如果某个记录的 deleted_bit 为 true ，并且 DB_TRX_ID 相对于 purge 线程的 read view 可见，那么这条记录是可以被安全清除的。

不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log 的链首就是最新的旧记录，链尾就是最早的旧记录

两者中，insert undo log 实际上就是rollback segment中的旧纪录链，上文中navicat上我们进行的read uncommitted级别的事务在对数据进行修改时，数据库先为该行加锁（排它锁），把修改之前的该行数据拷贝到undo log中作为旧纪录，此时被修改的记录的DB_TRX_ID就会变为做此修改的事务的ID，我们默认从1开始，之后递增，而DB_ROLL_PTR则指向undo log中的副本数据（SQL语句：delete。。。），事务提交后，释放锁。

而update undo log的实现原理是，当进行数据修改的操作时，会有一个叫做COW（copy on write）的机制，/*吐槽：奶牛机制。。。*/还是字面意思，复制写，它的存在是为了解决一个读写并发的问题，为了让读写可以互不干扰，例如在进行update操作时，会先把要操作的行复制出一份，在复制出的行上进行数据修改，复制出的这行数据的事务id会发生更改，而回滚指针则指向了update undo log（被复制的数据），也就是进行update操作的原数据，解决了读写并发的问题同时带来了数据读取的延后性问题

③Read View

三个主要属性：

trx_list （随便取的名字）开启read view时当前活跃的（未提交）事务id集合

up_limit_id 开启read view时最小的事务id

low_limit_id 开启read view后，应该分配给下一个事务的id值，也即当前最大事务id+1

事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID (当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大，反之越小)；主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，通过一定条件判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据

read view遵循一个可见性算法来进行判断，将要被修改的数据的最新记录中的 DB_TRX_ID（当前事务 ID ）取出来，与系统当前其他活跃事务的 ID 去对比（由 Read View 维护），如果事务id 跟 Read View 的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出 Undo Log 中的 DB_TRX_ID 再比较，即遍历链表的 DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的 DB_TRX_ID , 那么这个 DB_TRX_ID 所在的旧记录就是当前事务可见的最新老版本。