极客时间-MySQL实战45讲

- 日理万妓 2023-06-13 09:25 17阅读 0赞

# 1.基础架构：一条SQL查询语句是如何执行的？ #

## 1.1 SQL语句执行过程 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70]  
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。InnoDB、MyISAM、Memory 等多个存储引擎。InnoDB 在5.5.5后成为默认存储引擎

## 1.2 SERVER 层 ##

### 连接器 ###

负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的：

mysql -h$ip -P$port -u$user -p

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接  
里面的权限判断逻辑，都将依赖于此时读到的权限。对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置

processlist 参数查看连接用户  
wait\_timeout 参数设置空闲连接自动断开

在使用中要尽量减少建立连接的动作，多使用长连接

长连接导致内存占用的解决方案：

1.  定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2.  如果用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执  
    行 mysql\_reset\_connection 来重新初始化连接资源。这个过程不需要重连和重新做权  
    限验证，但是会将连接恢复到刚刚创建完时的状态。

## 查询缓存 ##

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。  
参数 query\_cache\_type 设置成 DEMAN,对默认SQL语句都不使用查询缓存，对于要使用查询缓存的语句，使用 SQL\_CACHE 显示指定，例如

SELECT SQL_CACHE * FROM T WHERE ID=10;

MYSQL 8.0 后不支持查询缓存

## 分析器 ##

1.词法分析：从"SELECT" 识别是查询语句，把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”  
2.语法分析：根据与语法规则判断SQL语句是否满足MySQL语法  
一般语法错误会提示第一个出错位置，要关注的是紧接“use near”的内容

## 优化器 ##

优化器在表里有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

## 执行器 ##

1.在执行之前，判断时候有执行的权限  
2.命中查询缓存，在返回结果时做权限验证  
3.查询会在优化器之前调用precheck验证权限

举例：

SELECT * FROM T WHERE ID=10

执行器流程：  
1.调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如  
果是则将这行存在结果集中；  
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。  
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。  
对有索引的表，第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

在数据库慢查询日志中看到 rows\_examined 字段

## 小结 ##

如果表 T 中没有字段 k，而你执行了这个语句 select \* from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in  
‘where clause’” 这个错误是在我们上面提到的哪个阶段报出来的呢？

# 2.日志系统：一条SQL更新语句是如何执行的？ #

## 2.1 更新语句的执行流程 ##

UPDATE T  SET  c=c+1 WHERE ID=2;

1.先连接器连接  
2.分析器通过词法和语法分析是一条更新语句  
3.优化器决定要是使用ID的索引

## 2.2 日志模块 ##

### 2.2.1日志模块：redo log ###

类比《孔乙己》的掌柜记账 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。  
redo log 是InnoDB 引擎特有的，从开头写到末尾又回开头循环写，有crash-safe能力  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 1]  
write pos 是当前记录的位置，一边写一边后移，check point 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件

### 2.2.2 重要日志模块 binlog ###

binlog是Server层的归档日志，没有crash-safe能力  
两种日志的不同：  
1.redo log是InnoDB引擎特有的；binlog是Server层实现的，所有引擎都可以使用  
2.redo log是物理日志，记录的是 “在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如 “给ID=2这一行的c字段加1”  
3.redo log是 循环写的，空间固定会用完；binlog是 可以追加写入的。“追加写”是指binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

## 2.3 Update语句内部流程 ##

1.执行器先找引擎去ID=2这行，ID是主键，引擎用树搜索找到这行，如果内存存在数据，直接返回给执行器，否则需要先从磁盘读入内存，再返回  
2.执行器拿到引擎给的行数据，将该值加上1，得到新的一行数据，再调用引擎接口写入这行新数据  
3.引擎将这行新数据更新到内存，同时将更新操作记录到redo log，redo log处于prepare状态，然后告知执行器执行完了，随时可以提交事务。  
4.执行器生成这个操作的binlog，并把binlog写入磁盘  
5.执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 2]

redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致

## 2.4 小结 ##

redo log用于保证crash-safe能力。inoodb\_flush\_log\_at\_trx\_commit 参数设置成1，表示每次事务的redo log都直接持久到磁盘。可以保证Mysql异常重启后数据不丢失  
sync\_binlog 参数设置成1的时候，表示每次事务的binlog都持久化到磁盘，可以保证MySQL异常重启后binlog不丢失

## 2.5问题 ##

在什么场景下，一天一备会比一周一备更有优势呢？或者说，它影响了这个数据库系统的哪个指标?  
好处是“最长恢复时间”更短。  
在一天一备的模式里，最坏情况下需要应用一天的 binlog。比如，你每天 0 点做一次全量备份，而要恢复出一个到昨天晚上 23 点的备份。一周一备最坏情况就要应用一周的 binlog 了  
因为更频繁全量备份需要消耗更多存储空间，所以这个 RTO 是成本换来的，就需要你根据业务重要性来评估了

# 3.事务隔离：为什么你改了我还看不见？ #

## 3.1 隔离性与隔离级别 ##

隔离的越严实，效率就会越低  
SQL标准的事务隔离级别：  
1.读未提交（read uncommitted）：一个事务还没提交，它的变更就能被别的事务看到  
2.读提交（read committed）:一个事务提交了之后，它的变更才会被其他事务看到  
3.可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的，在可重复读隔离级别下，未提交的变更对其他事务也是不可见的  
4.串行化（serializable）：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

举例：

create table T(c int) engine=InooDB;
    insert into T(c) values(1);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 3]  
在不同隔离级别下V1、V2、V3的值

串行化：事务B执行“将1改成2”的时候，会被锁住，直到事务A提交后，事务B才可以继续执行

实现上，数据库会创建视图，访问的时候一视图的逻辑结果为准  
可重复读：在事务启动时创建，整个事务存在期间都用这个视图  
读提交：在每个SQL语句开始执行时创建  
读未提交：直接返回记录上的最新值  
串行化：直接用加锁的方式避免并行访问

配置方式：  
将启动参数 transaction-isolation 的值设置成 READ-COMMITTED

## 3.2 事务隔离的实现 ##

在MySQL中，实际上每条记录在更新状态的时候都会记录一条回滚操作，记录上的最新值，通过回滚操作，都可以得到前一个状态的值  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 4]  
即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的

尽量不要使用长事务，长事务意味着系统会存在很老的事务视图，由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间

## 3.3 事务的启动方式 ##

事务启动方式  
1.显示启动事务语句，begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback  
2.set autocommit=0，这个命令会将线程的自动提交关掉，意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。可能会导致意外的长事务

可以在 information\_schema 库的 innodb\_trx 这个表中查询长事务

## 3.4问题 ##

你现在知道了系统里面应该避免长事务，如果你是业务开发负责人同时也是数据库负责人，你会有什么方案来避免出现或者处理这种情况呢？  
首先，从应用开发端来看：

1.  确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL 的 general\_log 开起来，然后随便跑一个业务逻辑，通过 general\_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
2.  确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
3.  业务连接数据库的时候，根据业务本身的预估，通过 SET MAX\_EXECUTION\_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

其次，从数据库端来看：

1.  监控 information\_schema.Innodb\_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
2.  Percona 的 pt-kill 这个工具不错，推荐使用；
3.  在业务功能测试阶段要求输出所有的 general\_log，分析日志行为提前发现问题；
4.  如果使用的是 MySQL 5.6 或者更新版本，把 innodb\_undo\_tablespaces 设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便

# 4.深入浅出索引（上） #

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样

## 4.1 索引的常见模型 ##

1.哈希表  
（1）一种键-值（key-value）存储数据的结构，哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置  
（2）多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表，用哈希索引做区间查询速度很慢  
（3）哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些NoSQL 引擎  
2. 有序数组  
（1）在等值查询和范围查询场景中的性能就都非常优秀  
（2）在需要更新数据的时候，往中间插入一个记录就必须得挪动后面所有的记录，成本太高  
（3）有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据  
3.二叉树  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 5]  
特点：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你要查 ID\_card\_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF ->User2 这个路径得到。这个时间复杂度是 O(log(N))

4.N叉树  
每个节点有多个儿子，儿子之间的大小保证从左到右递增，以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了

## 4.2 InnoDB 的索引模型 ##

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+树中的

举例：  
主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。这个表的建表语句是  
create table T(  
id int primary key,  
k int not null,  
name varchar(16),  
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 6]  
索引类型分为主键索引和非主键索引。  
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。  
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

## 4.3 基于主键索引和普通索引的查询有什么区别？ ##

如果语句是 select \* from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵B+ 树；  
如果语句是 select \* from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO\_INCREMENT  
自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂

`显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小`

有些业务的场景需求是这样的：

1.  只有一个索引；
2.  该索引必须是唯一索引  
    由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树

## 4.4问题 ##

对于上面例子中的 InnoDB 表 T，如果你要重建索引 k，你的两个 SQL 语句可以这么写  
alter table T drop index k;  
alter table T add index(k);  
如果你要重建主键索引，也可以这么写：  
alter table T drop primary key;  
alter table T add primary key(id);  
对于上面这两个重建索引的作法，说出你的理解。如果有不合适的，为什么，更好的方法是什么？

# 5.深入浅出索引（下） #

select \* from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

create table T (
    ID int primary key,
    k int NOT NULL DEFAULT 0,
    s varchar(16) NOT NULL DEFAULT '',
    index k(k))
    engine=InnoDB;

执行流程

1.  在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2.  再到 ID 索引树查到 ID=300 对应的 R3；
3.  在 k 索引树取下一个值 k=5，取得 ID=500；
4.  再回到 ID 索引树查到 ID=500 对应的 R4；
5.  在 k 索引树取下一个值 k=6，不满足条件，循环结束。

回到主键索引树搜索的过程，我们称为回表

## 5.1 覆盖索引 ##

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。  
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用  
的性能优化手段。

举例  
假设这个市民表的定义是这样的：  
备注：关于如何查看扫描行数的问题，我将会在第 16 文章《如何正确地显  
示随机消息？》中，和你详细讨论。

CREATE TABLE `tuser` (
    `id` int(11) NOT NULL,
    `id_card` varchar(32) DEFAULT NULL,
    `name` varchar(32) DEFAULT NULL,
    `age` int(11) DEFAULT NULL,
    `ismale` tinyint(1) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `id_card` (`id_card`),
    KEY `name_age` (`name`,`age`)
    ) ENGINE=InnoDB

有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间

## 5.2 最左前缀原则 ##

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

在建立联合索引的时候，如何安排索引内的字段顺序。  
第一原则是，如果通过调以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的，考虑的原则就是空间了。比如上面这个市民表的情况，name 字段是比age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引

## 5.3 索引下推 ##

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数  
无索引下推  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 7]  
有索引下推  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 8]

## 小结 ##

在满足语句需求的情况下， 尽量少地访问资源是数据库设计的重要原则之一，在使用数据库的时候，尤其是在设计表结构时，也要以减少资源消耗作为目标

## 问题 ##

DBA 小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的

CREATE TABLE `geek` (
    `a` int(11) NOT NULL,
    `b` int(11) NOT NULL,
    `c` int(11) NOT NULL,
    `d` int(11) NOT NULL,
    PRIMARY KEY (`a`,`b`),
    KEY `c` (`c`),
    KEY `ca` (`c`,`a`),
    KEY `cb` (`c`,`b`)
    ) ENGINE=InnoDB;

由于历史原因，这个表需要 a、b 做联合主键，这个小吕理解了。但是，学过本章内容的小吕又纳闷了，既然主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？  
select \* from geek where c=N order by a limit 1;  
select \* from geek where c=N order by b limit 1;  
回答  
表记录  
–a–|–b–|–c–|–d–  
1 2 3 d  
1 3 2 d  
1 4 3 d  
2 1 3 d  
2 2 2 d  
2 3 4 d  
主键 a，b 的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序，  
c 无序。  
索引 ca 的组织是先按 c 排序，再按 a 排序，同时记录主键  
–c–|–a–|–主键部分b-- （注意，这里不是 ab，而是只有 b）  
2 1 3  
2 2 2  
3 1 2  
3 1 4  
3 2 1  
4 2 3  
这个跟索引 c 的数据是一模一样的。  
索引 cb 的组织是先按 c 排序，在按 b 排序，同时记录主键  
–c–|–b–|–主键部分a-- （同上）  
2 2 2  
2 3 1  
3 1 2  
3 2 1  
3 4 1  
4 3 2  
所以，结论是 ca 可以去掉，cb 需要保留。

# 6.全局锁和表锁 ：给表加个字段怎么有这么多阻碍？ #

## 6.1 全局锁 ##

对整个数据库加锁，命令：Flush tables with read lock（FTWRL）。整个库处于只读状态，数据更新语句、数据定义语句（建表、修改表结构）和更新事务类的提交语句会阻塞  
`全局锁的典型使用场景是做全库逻辑备份`  
不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的  
官方导出数据工具mysqldump，使用参数 -single-transaction，会启动一个事务，来确保拿到一致性视图，适用于InnoDB引擎，MyISAM只能使用FTWRL命令

不使用set global readonly=true方式设置为只读的原因  
1.readonly 会被用来当做从库判断依据  
2.FTWRL 发生异常，MySQL会释放锁，readonly 客户端异常，数据库会一直保持该状态

## 6.2 表级索 ##

### 6.2.1 表锁 ###

表锁的语法是 lock tables … read/write。释放锁的语法 unlock tables  
线程执行 lock tables t1 read,t2 write;这个语句之后，则其他线程写t1，读写t2会阻塞，该线程也只能执行读t1，读写t2的操作，也不能访问其他表

### 6.2.1 MDL锁 ###

MDL不需要显式使用，访问表时自动加上  
当对一个表做增删改查时候，加MDL读锁，对表结构变更的时候，加MDL写锁  
读锁之间不互斥，多个线程可以同时对一张表增删改查  
读写锁之间、写锁之间互斥，来保证变更表结构操作的安全性  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 9]  
session c 被blocked后，后面读锁的申请也会被阻塞

### 6.2.3 给小表加字段 ###

1.要解决长事务，事务不提交，就会一直占着MDL锁，做DDL时有长事务，要考虑暂停DDL，或者kill长事务  
2.可以在MraiaDB中使用,设置等待时间

ALTER TABLE tbl_name NOWAIT add column ...
    ALTER TABLE tbl_name WAIT N add column ...

### 6.3 问题 ###

备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？  
假设这个 DDL 是针对表 t1 的， 这里我把备份过程中几个关键的语句列出来

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT；
    /* other tables */
    Q3:SAVEPOINT sp;
    /* 时刻 1 */
    Q4:show create table `t1`;
    /* 时刻 2 */
    Q5:SELECT * FROM `t1`;
    /* 时刻 3 */
    Q6:ROLLBACK TO SAVEPOINT sp;
    /* 时刻 4 */
    /* other tables */

在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1);  
启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个  
一致性视图（Q2)；  
设置一个保存点，这个很重要（Q3）；  
show create 是为了拿到表结构 (Q4)，然后正式导数据 （Q5），回滚到 SAVEPOINT  
sp，在这里的作用是释放 t1 的 MDL 锁 （Q6）。  
DDL 从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到  
达后，如果开始执行，则很快能够执行完成。  
参考答案:

1.  如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
2.  如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
3.  如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
4.  从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构

# 7.行锁功过：怎么减少行锁对性能的影响？ #

## 7.1 两阶段锁 ##

1.  在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议
2.  如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

## 7.2 死锁和死锁检测 ##

### 7.2.1死锁 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 10]  
事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。  
两种策略：

1.  直接进入等待，直到超时。超时时间可以通过参数 innodb\_lock\_wait\_timeout 来设置
2.  另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb\_deadlock\_detect 设置为 on，表示开启这个逻辑

### 7.2.2 死锁检测 ###

一般采取主动检测死锁，死锁检测要耗费大量的 CPU 资源。

1.如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。  
2.控制并发度，对于相同行的更新，在进入引擎之前排队  
3.将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。  
`减少死锁的主要方向，就是控制访问相同资源的并发事务量。`

## 7.3 问题 ##

如果你要删除一个表里面的前 10000 行数据，有以下三种  
方法可以做到：  
第一种，直接执行 delete from T limit 10000;  
第二种，在一个连接中循环执行 20 次 delete from T limit 500;  
第三种，在 20 个连接中同时执行 delete from T limit 500

怎么删除表的前 10000 行。比较多的留言都选择了第二种方式，即：在一个连接中循环执行 20 次 delete from T limit 500。确实是这样的，第二种方式是相对较好的。  
第一种方式（即：直接执行 delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。  
第三种方式（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。

# 8.事务到底是隔离的还是不隔离的？ #

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力

## 8.1 更新逻辑 ##

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）

## 8.2 小结 ##

InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx\_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx\_id 和一致性视图确定数据版本的可见性。  
对于可重复读，查询只承认在事务启动前就已经提交完成的数据；  
对于读提交，查询只承认在语句启动前就已经提交完成的数据；  
而当前读，总是读取已经提交完成的最新版本。

## 8.3 问题 ##

用下面的表结构和初始化语句作为试验环境，事务隔离级别是可重复读。现在，我要把所有“字段 c 和 id 值相等的行”的 c 值清零，但是却发现了一个“诡异”的、改不掉的情况。请你构造出这种情况，并说明其原理

mysql> CREATE TABLE `t` (
    `id` int(11) NOT NULL,
    `c` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;
    insert into t(id, c) values(1,1),(2,2),(3,3),(4,4);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 11]

# 9.普通索引和唯一索引，应该怎么选择？ #

假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了  
不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这  
样的 SQL 语句：

SELECT name from CUser WHERE id_card='xxxxxxxxxxxxx'

由于身份证字段比较大，不要当做主键，可以选择普通索引或者唯一索引

## 9.1 查询过程 ##

执行查询语句 `select id from T where k=5`  
普通索引：查找到满足条件的第一个记录（5,500）后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录  
唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索  
两者的差异时间较小

## 9.2 更新过程 ##

当需要更新一个数据页时，如果数据页在内存中就直接更新，如果数据页没在内存中，InnoDB会将这些更新操作缓存在chang buff 中，就不需要从磁盘中读入这个数据页，在下次查询访问到该数据页是，再执行相关操作  
`change buffer 只限于用在普通索引的场景下，而不适用于唯一索引`  
对于写多读少的业务，change buffer使用效果最好，例如：账单类、日志类系统  
假如对于写完之后马上要查询的系统，不适合使用 change buffer

## 9.3 索引选择和实践 ##

普通索引和唯一索引，查询性能上几乎无差别，主要是对更新性能的影响  
如果更新后面马上伴随着查询，应该关闭 change buffer，而在其他情况下 change buffer 能提升性能

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 12]  
更新过程

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 13]  
查询过程

`redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。`

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190405160716135.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190406004635798.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2019040609475819.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190406101753852.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190406104324835.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20190406111358219.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/2019040611272112.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20190406133756809.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20190406133831891.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20190406143854849.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/2019040615250229.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20190406173751582.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20190406200502159.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/20190406200526847.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODk5MDQzMQ==,size_16,color_FFFFFF,t_70