MySQL数据库的存储引擎InnoDB和MyIASM

以下都是以InnoDB  ，Mysql默认事务隔离级别为REPEATABLE-READ 

事务

4个特性（ACID）： 原子性(Atomicity),一致性(Consistency),隔离性(Isolation),持久性(Durability)。

查看并开启自动提交事务

show variables like 'autocommit';

set session autocommit = on;

事务隔离级别

查看当前事务级别

show global variables like 'tx_isolation';

或者

1.查询mysql全局事务隔离级别

select @@global.tx_isolation;

2.查询当前会话事务隔离级别

 select @@tx_isolation;

事务并发所带来的问题

脏读：相同的select查询，读取到其他事务未提交的数据造成。

不可重读：读取到其他事务的（update/delete）已提交的数据。

幻读：读取到其他事务（insert）已提交的数据。

事务并发的三大问题其实都是数据库读一致性的问题；必须由数据库提供一定的事务隔离机制来解决。

事务隔离级别

事务隔离级别

MySQL数据库Innodb引擎 采用两种方式解决事务隔离

事务隔离机制

InnoDB事务隔离的实现

RR（可重复读）和RC（已提交读）

InnoDB事务隔离的实现

RR（可重复读）和RC（已提交读）问题？

RR的快照建立时间是第一次查询的时候，所以提交的事务和其后的事务的数据是不能看到的。

RC的快照建立时间是当前查询，所以能够看到其他事务已提交的数据。

事务是有递增版本号

锁机制

查看锁

show engine innodb status;

锁的作用是什么？解决并发情况下资源的竞争。

默认update/delete/insert 加排它锁。

锁的粒度

表锁，行锁

表锁，行锁

锁类型

1）共享锁（S锁）

2）排它锁（X锁）

3）意向共享（IS锁）

4）意向排它锁（IX锁）

共享锁 排它锁 意向共享、意向排它锁

行锁算法

1）记录锁（Record Lock）

2）间隙锁（Gap Lock）  ：阻塞插入

3）临键锁（Next-key Lock）

行锁算法

索引

索引是什么？是一种有序的结构。

B+树 数据存储结构

聚集索引（索引的逻辑顺序，数据行的物理顺序），辅助索引

索引

为什么数据存储引擎InnoDB，能在事务隔离级别为REPEATABLE-READ解决幻读？

因为临键锁的作用

---

一张表没有建索引，为什么锁表？

Row Id（隐藏索引），作为聚集索引

（依次会找 这张表的 聚集索引 primary key ，辅助索引 unique key  NOT NULL，隐藏索引 Row Id 来锁住）

---

为什么使用name列（建了unique key）加锁，会阻塞id 加锁？

锁住的是索引

MVCC操作

MVCC

---

table2 数据导入 table1避免全表被锁

insert into table1 select * from table2 

把table2中的数据插入table1中，可以避免使用网络I/O，直接使用SQL依靠数据库I/O完成

INSERT INTO table1 SELECT * FROM table2 FORCE INDEX (idx_pay_suc_time) WHERE pay_success_time <= '2020-05-28 00:00:00';

force index()指令可以指定本次查询使用哪个索引。

使用 insert into table1 select * from table2 语句时，一定要确保 table2 后面的 where，order by 或者其他条件，都需要有对应的索引，来避免出现 table2 全部记录被锁定的情况。

---

远程访问MySQL

报错：Host xxx-PC is not allowed to connect to this MySQL server

在本地登入mysql服务器，查看mysql用户host的权限，%表示允许所有机器访问。

use mysql;

SELECT user, host from mysql.user; 

若user为root      host为127.0.0.1/localhost，那么这个用户就只能本机访问，则需要将host改为%。

最后记得刷新一下权限。

update user set host='%' where user='root';

flush privileges;

---

错误问题1：连接新的MySQL5.7.x 需要带时区

com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

URL

jdbc:mysql://localhost:3306/db

加上下面这段

?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8

---

错误问题2：中文插入错误，说明字符集的问题

[2020-06-07 12:04:48] [HY000][1366] Incorrect string value: '\xE8\x8B\xA5\xE4\xBE\x9D...' for column 'dept_name' at row 1

1）修改表的编码方式：

ALTER TABLE 'test' DEFAULT CHARACTER SET utf8; 

该命令用于将表test的编码方式改为utf8；

2)创建表的时候加上，指定字符集utf8支持中文
DEFAULT CHARSET=utf8