insert select 为什么有这么多锁？

本节我们会介绍一些特殊的 insert 语句产生的锁:

1. insert … select 是很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁
2. 如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。
3. insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

1. insert … select 语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

在可重复读隔离级别下，binlog_format=statement 时, insert into t2(c,d) select c,d from t; 需要对表 t 的所有行和间隙加锁。原因还是日志和数据的一致性。

B+树索引

1. InnoDB 的索引模型

实现索引的方式有很多方式，N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

幻读

1. 幻读

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。对于幻读需要在注意:

1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。而当前读的规则，就是要能读到所有已经提交的记录的最新值。因此，幻读只在“当前读”下才会出现。
2. 修改结果，被之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”

1.1 幻读有什么问题？

没有行锁到底会导致什么问题，我们来看下面这个示例:

事务的隔离性和回滚日志

1.事务的隔离性

事务的隔离级别包括:

1. 读未提交: read uncommitted，一个事务还没提交时，它做的变更就能被别的事务看到
2. 读提交: read committed，一个事务提交之后，它做的变更才会被其他事务看到
3. 可重复读: repeatable read，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
4. 串行化: 对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行(锁是在事务提交之后才释放的)。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

全局锁 - 表锁 - 行锁

1. 全局锁

全局锁:

• 作用: 对整个数据库实例加锁
• 加锁: Flush tables with read lock
• 解锁: unlock tables，客户端断开时会自动释放锁
• 场景: 全库逻辑备份，即把整库每个表都 select 出来存成文本
• 加锁范围: 数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句都会被阻塞

做全库备份时，对于 Innodb，通过可重复度隔离级别我们就可以获取数据库的一致视图，但是对 于MyISAM 这些不支持事务的存储引擎，只能使用 Flush tables with read lock 让整个库处于只读状态。

我的数据库占用空间太大，我把一个最大的表删掉了一半的数据，怎么表文件的大小还是没变？

1.innodb_file_per_table

一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。