Mysql知识点整理

1、Mysql的体系结构是什么样子的（查询语句怎么进行执行的）

mysql的架构：单进程多线程的架构模式 CLient -----> Server架构

Mysql的链接方式有没有性能优化的点
show global variable like ‘wait_timeout’ 非交互式超时时间，JDBC程序
show global variable like ‘interactive_timeout’ 交互式超时间，数据库连接工具

查询缓存(Query Cache)

MySQL 内部自带了一个缓存模块。默认是关闭的。主要是因为 MySQL 自带的缓存的应用场景有限，第一个是它要求 SQL 语句必须一模一样。第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效。

在 MySQL 5.8 中，查询缓存已经被移除了。

语法解析和预处理(Parser & Preprocessor)

下一步我们要做什么呢？

假如随便执行一个字符串 fkdljasklf ，服务器报了一个 1064 的错：

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘fkdljasklf’ at line 1

服务器是怎么知道我输入的内容是错误的？

或者，当我输入了一个语法完全正确的 SQL，但是表名不存在，它是怎么发现的？

这个就是 MySQL 的 Parser 解析器和 Preprocessor 预处理模块。

这一步主要做的事情是对 SQL 语句进行词法和语法分析和语义的解析。

词法解析

词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。

比如一个简单的 SQL 语句：

select name from user where id = 1;

它会打碎成 8 个符号，记录每个符号是什么类型，从哪里开始到哪里结束。

语法解析

第二步就是语法分析，语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合，然后根据 MySQL

定义的语法规则，根据 SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树。

预处理器（Preprocessor）

语义解析

如果表名错误，会在预处理器处理时报错。

它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。

查询优化（Query Optimizer）与查询执行计划

什么优化器？

问题：一条 SQL 语句是不是只有一种执行方式？或者说数据库最终执行的 SQL 是不是就是我们发送 的 SQL？

这个答案是否定的。一条 SQL 语句是可以有很多种执行方式的。但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？

这个就是 MySQL 的查询优化器的模块（Optimizer）。

查询优化器的目的就是根据解析树生成不同的执行计划，然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，那种执行计划开销最小，就用哪种。

使用如下命令查看查询的开销：
    show status like 'Last_query_cost'; 
    --代表需要随机读取几个 4K 的数据页才能完成查找。 
1
2
3

如果我们想知道优化器是怎么工作的，它生成了几种执行计划，每种执行计划的 cost 是多少，应该怎么做？

优化器是怎么得到执行计划的？

https://dev.mysql.com/doc/internals/en/optimizer-tracing.html

首先我们要启用优化器的追踪（默认是关闭的）：

SHOW VARIABLES LIKE 'optimizer_trace'; 

set optimizer_trace="enabled=on"; 
1
2
3

注意开启这开关是会消耗性能的，因为它要把优化分析的结果写到表里面，所以不要轻易开启，或者查看完之后关闭它（改成 off）。

接着我们执行一个 SQL 语句，优化器会生成执行计划：

select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid; 
1

这个时候优化器分析的过程已经记录到系统表里面了，我们可以查询：

select * from information_schema.optimizer_trace\G 
1

expanded_query 是优化后的 SQL 语句。

considered_execution_plans 里面列出了所有的执行计划。 
1

记得关掉它：

        set optimizer_trace="enabled=off"; 

•       SHOW VARIABLES LIKE 'optimizer_trace'; 
1
2
3

优化器可以做什么？

MySQL 的优化器能处理哪些优化类型呢？

比如：

1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。 

2、select * from user where a=1 and b=2 and c=3，如果 c=3 的结果有 100 条，b=2 的结果有 200 条，		a=1 的结果有 300 条，你觉得会先执行哪个过滤？ 

3、如果条件里面存在一些恒等或者恒不等的等式，是不是可以移除。 

4、查询数据，是不是能直接从索引里面取到值。 

5、count()、min()、max()，比如是不是能从索引里面直接取到值。 

6、其他。
1
2
3
4
5
6
7
8
9
10
11

优化器得到的结果

优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。

当然，这个执行计划是不是一定是最优的执行计划呢？不一定，因为 MySQL 也有可能覆盖不到所有的执行计划。

MySQL 提供了一个执行计划的工具。我们在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的信息。

EXPLAIN select name from user where id=1; 
1

MySQL 体系结构总结（知其然，知其所以然）

架构分层

总体上，我们可以把 MySQL 分成三层。

模块详解

1.Connector：用来支持各种语言和 SQL 的交互，比如 PHP，Python，Java 的 JDBC

2.Management Serveices & Utilities：系统管理和控制工具，包括备份恢复、MySQL 复制、集群等等

3.Connection Pool：连接池，管理需要缓冲的资源，包括用户密码权限线程等等

4.SQL Interface：用来接收用户的 SQL 命令，返回用户需要的查询结果

5.Parser：用来解析 SQL 语句

6.Optimizer：查询优化器

7.Cache and Buffer：查询缓存，除了行记录的缓存之外，还有表缓存，Key 缓存，权限缓存等等。

8.Pluggable Storage Engines：插件式存储引擎，它提供 API 给服务层使用，跟具体的文件打交道。

2、Bin log

1.bin Log: 数据恢复 主从复制

MySQL Server 层也有一个日志文件，叫做 binlog，它可以被所有的存储引擎使用。

bin log 以事件的形式记录了所有的 DDL 和 DML 语句（因为它记录的是操作而不是数据值，属于逻辑日志），可以用来做主从复制和数据恢复。
1

、

数据恢复：区别于Redo Log的崩溃恢复，数据恢复是基于业务数据的，比如删库跑路，而崩溃恢复是断电重启的

3、什么是预读？

磁盘读写，并不是按需读取，而是按页读取，一次至少读一页数据（一般是4K）但是Mysql的数据页是16K，如果未来要读取的数据就在页中，就能够省去后续的磁盘IO，提高效率。

4、什么是Buffer Pool？

内存缓冲区，缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。

Buffer Pool的内存淘汰策略

冷热分区的LRU策略

LRU链表会被拆分成为两部分，一部分为热数据，一部分为冷数据。冷数据占比 3/8，热数据5/8。

数据页第一次加载进来，放在LRU链表的什么地方？

放在冷数据区域的头部

冷数据区域的缓存页什么时候放入热数据区域？

MySQL设定了一个规则，在 innodb_old_blocks_time 参数中，默认值为1000，也就是1000毫秒。

意味着，只有把数据页加载进缓存里，在经过1s之后再次对此缓存页进行访问才会将缓存页放到LRU链表热数据区域的头部。

为什么是1秒？

因为通过预读机制和全表扫描加载进来的数据页通常是1秒内就加载了很多然后对他们访问一下，这些都是1秒内完成，他们会存放在冷数据区域等待刷盘清空，基本上不太会有机会放入到热数据区域，除非在1秒后还有人访问，说明后续可能还会有人访问，才会放入热数据区域的头部。

5、Redo Log跟Buffer Pool的关系

崩溃恢复 基本保障 系统自动做的

InnoDB 引入了一个日志文件，叫做 redo log（重做日志），我们把所有对内存数据的修改操作写入日志文件，如果服务器出问题了，我们就从这个日志文件里面读取数据，恢复数据——用它来实现事务的持久性。

redo log 有什么特点？

1.记录修改后的值，属于物理日志

2.redo log 的大小是固定的，前面的内容会被覆盖，所以不能用于数据回滚/数据恢复。

3.redo log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有。

6、Mysql的数据恢复怎么做

数据恢复：删库跑路了 我能够去将你的数据进行恢复

备份每天2点 备份今天的所有数据库的数据 防止丢失 bin Log 二进制的日志文件 DDL DML

解析Bin Log 专门的工具

9点 删库跑路 drop table 首先 应该做得就是 把数据备份恢复出来 2点之前的

2点 - 9点之间的数据 bin Log 重新执行一遍 10分钟

7、一条更新语句的执行流程

跟 redo log 不一样，它的文件内容是可以追加的，没有固定大小限制。

有了这两个日志之后，我们来看一下一条更新语句是怎么执行的：

例如一条语句：update teacher set name=‘jim’ where name =‘666’

1、先查询到这条数据，如果有缓存，也会用到缓存。

2、把 name 改成jim，然后调用引擎的 API 接口，写入这一行数据到内存，同时记录 redo log。这时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，可以随时提交。

3、执行器收到通知后记录 binlog，然后调用存储引擎接口，设置 redo log 为 commit 状态。

4、更新完成。

问题：为什么redo Log要用两阶段提交（XA）呢？

举例：

如果我们执行的是把 name 改成jim，如果写完 redo log，还没有写 bin log 的时候，MySQL 重启了。

因为 redo log 可以恢复数据，所以写入磁盘的是jim。但是 bin log 里面没有记录这个逻辑日志，所以这时候用 binlog 去恢复数据或者同步到从库，就会出现数据不一致的情况。

所以在写两个日志的情况下，binlog 就充当了一个事务的协调者。通知 InnoDB 来执行 prepare 或commit 或者 rollback。

简单地来说，这里有两个写日志的操作，类似于分布式事务，不用两阶段提交，就不能保证都成功或者都失败。

8、查看存储引擎

查看数据库表的存储引擎：
1

show table status from `training`;
1

在 MySQL 里面，我们创建的每一张表都可以指定它的存储引擎，它不是一个数据库只能使用一个存储引擎。而且，创建表之后还可以修改存储引擎。
1

数据库存放数据的路径：
show variables like 'datadir'; 
1
2

每个数据库有一个自己文件夹，以 trainning 数据库为例。

任何一个存储引擎都有一个 frm 文件，这个是表结构定义文件。

我们在数据库中建了三张表，使用了不同的存储引擎。

不同的存储引擎存放数据的方式不一样，产生的文件也不一样。
1
2
3
4
5
6
7

常见存储引擎

在 MySQL 5.5 版本之前，默认的存储引擎是 MyISAM，它是 MySQL 自带的5.5.5 版本之后默认的存储引擎改成了InnoDB，它是第三方公司为MySQL开发的。为什么要改呢？最主要的原因还是InnoDB支持事务，支持行级别的锁，对于业务一致性要求高的场景来说更适合。
1

数据库支持的存储引擎

我们可以用这个命令查看数据库对存储引擎的支持情况：

SHOW ENGINES ; 

其中有存储引擎的描述和对事务、XA 协议和 Savepoints 的支持

官网对于存储引擎的介绍：

https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
1
2
3
4
5
6
7
8
9

MyISAM（.frm .MYI .MYD）

应用范围比较小。表级锁定限制了读/写的性能，因此在 Web 和数据仓库配置中，它通常用于只读或以读为主的工作。

特点：

• 支持表级别的锁（插入和更新会锁表）。不支持事务。
• 拥有较高的插入（insert）和查询（select）速度。
• 存储了表的行数（count 速度更快）。
• 适合：只读之类的数据分析的项目。

InnoDB（.frm .ibd）

mysql 5.7 中的默认存储引擎。InnoDB 是一个事务安全（与 ACID 兼容）的 MySQL 存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁（不升级为更粗粒度的锁）和 Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB 将用户数据存储在聚集索引中，以减少基于 主键的常见查询的 I/O。为了保持数据完整性，InnoDB 还支持外键引用完整性约束。

特点：

• 支持事务，支持外键，因此数据的完整性、一致性更高。
• 支持行级别的锁和表级别的锁。
• 支持读写并发，写不阻塞读。
• 特殊的索引存放方式，可以减少 IO，提升查询效率。
• 适合：经常更新的表，存在并发读写或者有事务处理的业务系统。

Memory（.frm）

将所有数据存储在 RAM 中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少；InnoDB 及其缓冲池内存区域提供了一种通用、持久的方法来 将大部分或所有数据保存在内存中，而 ndbcluster 为大型分布式数据集提供了快速的键值查找。

特点：

把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合 做临时表。默认使用哈希索引。 将表中的数据存储到内存中。

9、为什么Mysql要使用B+树做为索引而不用B树

1. B+树能显著减少IO次数，提高效率
2. B+树的查询效率更加稳定，因为数据放在叶子节点
3. B+树能提高范围查询的效率，因为叶子节点指向下一个叶子节点
4. B+树采取顺序读

10、磁盘的顺序读以及随机读有什么区别？

盘片+磁头 +主轴 + 控制电路集成板

磁盘是如何完成单次IO的

寻道时间+旋转延迟+数据传送
数据的顺序读跟随机读有什么区别

顺序IO 跟并发IO 一块磁盘一次只能处理一个IO指令 磁盘组 一次 能够并发IO

11、什么是Hash索引

12、索引使用原则（索引怎么使用才合理）

我们容易有一个误区，就是在经常使用的查询条件上都建立索引，索引越多越好，那到底是不是这样呢？ 
1

列的离散（sàn）度

第一个叫做列的离散度，我们先来看一下列的离散度的公式：

不同值得数量：总行数 越接近1 那么离散度越高，越接近0，离散度越低

count(distinct(column_name)) : count(*)，列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。
1

联合索引最左匹配

前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立联合索引，举例：查询成绩的时候必须同时输入身份证和考号。

联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的（name 在左边，phone 在右边）。

从这张图可以看出来，name 是有序的，phone 是无序的。当 name 相等的时候，phone 才是有序的。

这个时候我们使用 where name= ‘jim’ and phone = '136xx '去查询数据的时候，B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name相同的时候再比较 phone。但是如果查询条件没有 name，就不知道第一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以用不到索引。

如何创建联合索引

有一天我们的 DBA 找到我，说我们的项目里面有两个查询很慢，按照我们的想法，一个查询创建一个索引，所以我们针对这两条 SQL 创建了两个索引，这种做法觉得正确吗？

CREATE INDEX idx_name on user_innodb(name); 
CREATE INDEX idx_name_phone on user_innodb(name,phone);
1
2

当我们创建一个联合索引的时候，按照最左匹配原则，用左边的字段 name 去查询的时候，也能用到索引，所以第一个索引完全没必要。

相当于建立了两个联合索引(name),(name,phone)。

如果我们创建三个字段的索引 index(a,b,c)，相当于创建三个索引：

index(a) 

index(a,b) 

index(a,b,c) 

用 where b=? 和 where b=? and c=? 是不能使用到索引的。

这里就是 MySQL 里面联合索引的最左匹配原则。 
1
2
3
4
5
6
7
8
9
10
11

覆盖索引与回表

什么叫回表： 不需要回表 叫覆盖索引

聚集索引 ：id

二级索引 ：name

非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没

有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

Extra 里面值为“Using index”代表使用了覆盖索引。

13、索引的创建与使用

因为索引对于改善查询性能的作用是巨大的，所以我们的目标是尽量使用索引。

在什么字段上索引？

1、在用于 where 判断 order 排序和 join 的（on）字段上创建索引

2、索引的个数不要过多。

——浪费空间，更新变慢。

3、区分度低的字段，例如性别，不要建索引。

——离散度太低，导致扫描行数过多。

4、频繁更新的值，不要作为主键或者索引。

——页分裂

5、随机无序的值，不建议作为主键索引，例如身份证、UUID。

——无序，分裂

6、创建复合索引，而不是修改单列索引

什么时候索引失效？ 索引值不确定的情况

1、索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式

2、字符串不加引号，出现隐式转换

3、like 条件中前面带%

4、负向查询 NOT LIKE 不能

## MyiSAM与Innodb

myi index

myd data

我们表内的数据是按照聚集索引的顺序排列的

14、什么是数据库的事务？

内存 ----- buffer pool ----- 磁盘

重启 Redo Log 物理日志

下单 订单表 资金表 物流 库存表 insert update

A 10000 -11000 互不干扰的

B 10000 +500 银行会计科目不平衡 原子性 Undo Log 事务发生前的状态

事务的定义

维基百科的定义：事务是数据库管理系统（DBMS）执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

这里面有两个关键点，第一个，它是数据库最小的工作单元，是不可以再分的。第二个一系列的 D，它可能包含了一个或者ML 语句，包括 insert delete update

哪些存储引擎支持事务

InnoDB 支持事务，这个也是它成为默认的存储引擎的一个重要原因：

https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

15、事务的四大特性

原子性（Atomicity）

也就是我们刚才说的不可再分，也就意味着我们对数据库的一系列的操作，要么都是成功，要么都是失败，不可能出现部分成功或者部分失败的情况，以刚才提到的转账的场景为例，一个账户的余额减少，对应一个账户的增加，这两个一定是同时成功或者同时失败的。全部成功比较简单，问题是如果前面一个操作已经成功了，后面的操作失败了，怎么让它全部失败呢？这个时候我们必须要回滚。

原子性，在 InnoDB 里面是通过 undo log 来实现的，它记录了数据修改之前的值（逻辑日志），一旦发生异常，就可以用 undo log 来实现回滚操作。

一致性（consistent）

指的是数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。比如主键必须是唯一的，字段长度符合要求。

除了数据库自身的完整性约束，还有一个是用户自定义的完整性。

举例：

1.比如说转账的这个场景，A 账户余额减少 1000，B 账户余额只增加了 500，这个时候因为两个操作都成功了，按照我们对原子性的定义，它是满足原子性的， 但是它没有满足一致性，因为它导致了会计科目的不平衡。

2.还有一种情况，A 账户余额为 0，如果这个时候转账成功了，A 账户的余额会变成-1000，虽然它满足了原子性的，但是我们知道，借记卡的余额是不能够小于 0 的，所以也违反了一致性。用户自定义的完整性通常要在代码中控制。

隔离性（isolation）

有了事务的定义以后，在数据库里面会有很多的事务同时去操作我们的同一张表或者同一行数据，必然会产生一些并发或者干扰的操作，对隔离性就是这些很多个的事务，对表或者 行的并发操作，应该是透明的，互相不干扰的。通过这种方式，我们最终也是保证业务数据的一致性。

持久性（Durable）

我们对数据库的任意的操作，增删改，只要事务提交成功，那么结果就是永久性的，不可能因为我们重启了数据库的服务器，它又恢复到原来的状态了。

持久性怎么实现呢？数据库崩溃恢复（crash-safe）是通过什么实现的？持久性是通过 redo log 来实现的，我们操作数据的时候，会先写到内存的 buffer pool 里面，同时记录 redo log，如果在刷盘之前出现异常，在重启后就可以读取 redo log的内容，写入到磁盘，保证数据的持久性。

总结：原子性，隔离性，持久性，最后都是为了实现一致性

16、那么数据库什么时候会出现事务呢？

举例：

当我执行这样一条更新语句的时候，它有事务吗？

update user_innodb set name = '涛哥' where id=1;

实际上，它自动开启了一个事务，并且提交了，所以最终写入了磁盘。这个是开启事务的第一种方式，自动开启和自动提交。

InnoDB 里面有一个 autocommit 的参数（分成两个级别， session 级别和 global级别）。它的默认值是 ON

show variables like 'autocommit';
1

autocommit 这个参数是什么意思呢？是否自动提交。如果它的值是 true/on 的话，我们在操作数据的时候，会自动开启一个事务，和自动提交事务。

否则的话，如果我们把 autocommit 设置成 false/off，那么数据库的事务就需要我们手动地去开启和手动地去结束。

手动开启事务也有几种方式，一种是用 begin；一种是用 start transaction。

那么怎么结束一个事务呢？我们结束也有两种方式，第一种就是提交一个事务，commit；还有一种就是 rollback，回滚的时候，事务也会结束。

还有一种情况，客户端的连接断开的时候，事务也会结束

17、事务并发会带来什么问题？

当很多事务并发地去操作数据库的表或者行的时候，如果没有我们刚才讲的事务的Isolation 隔离性的时候，会带来哪些问题呢？

脏读

大家看一下，我们有两个事务，一个是 Transaction A，一个是 Transaction B，在第一个事务里面，它首先通过一个 where id=1 的条件查询一条数据，返回 name=Ada，age=16 的这条数据。然后第二个事务呢，它同样地是去操作 id=1 的这行数据，它通过一个 update 的语句，把这行 id=1 的数据的 age 改成了 18，但是大家注意，它没有提交。

这个时候，在第一个事务里面，它再次去执行相同的查询操作，发现数据发生了变化，获取到的数据 age 变成了 18。那么，这种在一个事务里面，由于其他的时候修改了数据并且没有提交，而导致了前后两次读取数据不一致的情况，这种事务并发的问题，我们把它定义成脏读。

不可重复读

同样是两个事务，第一个事务通过 id=1 查询到了一条数据。然后在第二个事务里面执行了一个 update 操作，这里大家注意一下，执行了 update 以后它通过一个 commit提交了修改。然后第一个事务读取到了其他事务已提交的数据导致前后两次读取数据不一致的情况，就像这里，age 到底是等于 16 还是 18，那么这种事务并发带来的问题，我们把它叫做不可重复读。
1

幻读

在第一个事务里面我们执行了一个范围查询，这个时候满足条件的数据只有一条。在第二个事务里面，它插入了一行数据，并且提交了。重点：插入了一行数据。在第一个事务里面再去查询的时候，它发现多了一行数据。
1

一个事务前后两次读取数据数据不一致，是由于其他事务插入数据造成的，这种情况我们把它叫做幻读。
1

总结：

不可重复读是修改或者删除，幻读是插入。

无论是脏读，还是不可重复读，还是幻读，它们都是数据库的读一致性的问题，都是在一个事务里面前后两次读取出现了不一致的情况。

18、SQL92标准

读一致性的问题，必须要由数据库提供一定的事务隔离机制来解决。就像我们去饭店吃饭，基本的设施和卫生保证都是饭店提供的。那么我们使用数据库，隔离性的问题也必须由数据库帮助我们来解决。
1

我们来看一下 SQL92 标准的官网。(个人吐槽一下，这个官网是真的丑)
1

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

这里面有一张表格（搜索_iso），里面定义了四个隔离级别，右边的 P1 P2 P3 就是代表事务并发的 3 个问题，脏读，不可重复读，幻读。Possible 代表在这个隔离级别下，这个问题有可能发生，换句话说，没有解决这个问题。Not Possible 就是解决了这个问题。
1

Read Uncommitted（未提交读）

一个事务可以读取到其他事务未提交的数据，会出现脏读，所以叫做 RU，它没有解决任何的问题。

Read Committed（已提交读）

一个事务只能读取到其他事务已提交的数据，不能读取到其他事务未提交的数据，它解决了脏读的问题，但是会出现不可重复读的问题。

Repeatable Read（可重复读）

它解决了不可重复读的问题，也就是在同一个事务里面多次读取同样的数据结果是一样的，但是在这个级别下，没有定义解决幻读的问题。

Serializable（串行化）

在这个隔离级别里面，所有的事务都是串行执行的，也就是对数据的操作需要排队，已经不存在事务的并发操作了，所以它解决了所有的问题。

总结：这个是 SQL92 的标准，但是不同的数据库厂商或者存储引擎的实现有一定的差异。

19、Mysql的innoDB存储引擎对于隔离级别的支持

在 MySQL InnoDB 里面，不需要使用串行化的隔离级别去解决所有问题。那我们来看一下 MySQL InnoDB 里面对数据库事务隔离级别的支持程度是什么样的。

InnoDB 支持的四个隔离级别和 SQL92 定义的基本一致，隔离级别越高，事务的并发度就越低。唯一的区别就在于，InnoDB 在 RR 的级别就解决了幻读的问题。这个也是InnoDB 默认使用 RR 作为事务隔离级别的原因，既保证了数据的一致性，又支持较高的并发度。

脏读

当一个事务读取到了另外一个事务修改但未提交的数据，被称为脏读。

1、在事务A执⾏过程中，事务A对数据资源进⾏了修改，事务B读取了事务A修改后的数据。
2、由于某些原因，事务A并没有完成提交，发⽣了RollBack操作，则事务B读取的数据就是脏数据。
这种读取到另⼀个事务未提交的数据的现象就是脏读(Dirty Read)。

不可重复读

当事务内相同的记录被检索两次，且两次得到的结果不同时，此现象称为不可重复读。

事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的
数据不⼀致。

幻读

在事务执行过程中，另一个事务将新记录添加到正在读取的事务中时，会发生幻读。

事务B前后两次读取同⼀个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后⼀
次读取到前⼀次查询没有看到的⾏。
幻读和不可重复读有些类似，但是幻读重点强调了读取到了之前读取没有获取到的记录。

20、如何解决数据的读一致性问题（Mysql中解决数据读一致性的方案）

两大方案:

LBCC

第一种，既然要保证前后两次读取数据一致，那么读取数据的时候，锁定我要操作的数据，不允许其他的事务修改就行了。这种方案叫做基于锁的并发控制 Lock Based Concurrency Control（LBCC）。

如果仅仅是基于锁来实现事务隔离，一个事务读取的时候不允许其他时候修改，那就意味着不支持并发的读写操作，而我们的大多数应用都是读多写少的，这样会极大地影响操作数据的效率。

MVCC

https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html

另一种解决方案，如果要让一个事务前后两次读取的数据保持一致，那么我们可以在修改数据的时候给它建立一个备份或者叫快照，后面再来读取这个快照就行了。这种方案我们叫做多版本的并发控制 Multi Version Concurrency Control（MVCC）

MVCC 的核心思想是： 我可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

通过以上演示我们能看到，通过版本号的控制，无论其他事务是插入、修改、删除，第一个事务查询到的数据都没有变化。

在 InnoDB 中，MVCC 是通过 Undo log 实现的。

Oracle、Postgres 等等其他数据库都有 MVCC 的实现。

需要注意，在 InnoDB 中，MVCC 和锁是协同使用的来实现隔离性的，这两种方案并不是互斥的。

READ COMMITTED

脏读问题的解决

READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。
我们还是以表teacher 为例，假设现在表teacher 中只有一条由事务id为60的事务插入的一条记录，接下来看一下READ COMMITTED和REPEATABLE READ所谓的生成ReadView的时机不同到底不同在哪里。
READ COMMITTED —— 每次读取数据前都生成一个ReadView
比方说现在系统里有两个事务id分别为80、120的事务在执行：Transaction 80

UPDATE teacher  SET name = '马' WHERE number = 1;
UPDATE teacher  SET name = '连' WHERE number = 1;
...
1
2
3

此刻，表teacher 中number为1的记录得到的版本链表如下所示：

假设现在有一个使用READ COMMITTED隔离级别的事务开始执行：

使用READ COMMITTED隔离级别的事务

BEGIN;
SELECE1：Transaction 80、120未提交

SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'
1
2
3
4
5
6

第1次select的时间点 如下图：

这个SELECE1的执行过程如下：
在执行SELECT语句时会先生成一个ReadView：

ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。

然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’连’，该版本的trx_id值为80，在m_ids列表内，所以不符合可见性要求（trx_id属性值在ReadView的min_trx_id和max_trx_id之间说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问），根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’马’，该版本的trx_id值也为80，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
下一个版本的列name的内容是’李瑾’，该版本的trx_id值为60，小于ReadView中的min_trx_id值，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’李瑾’的记录。

所以有了这种机制，就不会发生脏读问题！因为会去判断活跃版本，必须是不在活跃版本的才能用，不可能读到没有 commit的记录。

不可重复读问题

然后，我们把事务id为80的事务提交一下，然后再到事务id为120的事务中更新一下表teacher 中number为1的记录：

Transaction120

BEGIN;

更新了一些别的表的记录

UPDATE teacher  SET name = '严' WHERE number = 1;
UPDATE teacher  SET name = '晁' WHERE number = 1;

1
2
3
4
5
6
7
8
9

此刻，表teacher 中number为1的记录的版本链就长这样：

然后再到刚才使用READ COMMITTED隔离级别的事务中继续查找这个number为1的记录，如下：

使用READ COMMITTED隔离级别的事务

BEGIN;

SELECE1：Transaction 80、120均未提交

SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'

SELECE2：Transaction 80提交，Transaction 120未提交

SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'连'

1
2
3
4
5
6
7
8
9
10

第2次select的时间点 如下图：

这个SELECE2的执行过程如下：

SELECT * FROM teacher WHERE number = 1;

在执行SELECT语句时会又会单独生成一个ReadView，该ReadView信息如下：

m_ids列表的内容就是[120]（事务id为80的那个事务已经提交了，所以再次生成快照时就没有它了），min_trx_id为120，max_trx_id为121，creator_trx_id为0。
然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’晁’，该版本的trx_id值为120，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’严’，该版本的trx_id值为120，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
下一个版本的列name的内容是’连’，该版本的trx_id值为80，小于ReadView中的min_trx_id值120，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’连’的记录。

以此类推，如果之后事务id为120的记录也提交了，再次在使用READ COMMITTED隔离级别的事务中查询表teacher 中number值为1的记录时，得到的结果就是’晁’了，具体流程我们就不分析了。

但会出现不可重复读问题，在 READ COMMITTED事务隔离级，每次查询都会生成新的READ VIEW，就会导致不可重复读的问题

REPEATABLE READ

REPEATABLE READ解决不可重复读问题

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。我们还是用例子看一下是什么效果。

比方说现在系统里有两个事务id分别为80、120的事务在执行：Transaction 80

UPDATE teacher  SET name = '马' WHERE number = 1;
UPDATE teacher  SET name = '连' WHERE number = 1;
...
1
2
3

此刻，表teacher 中number为1的记录得到的版本链表如下所示：

假设现在有一个使用REPEATABLE READ隔离级别的事务开始执行：

使用READ COMMITTED隔离级别的事务

BEGIN;
SELECE1：Transaction 80、120未提交

SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'
1
2
3
4
5
6

这个SELECE1的执行过程如下：
在执行SELECT语句时会先生成一个ReadView：

ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。

然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’连’，该版本的trx_id值为80，在m_ids列表内，所以不符合可见性要求（trx_id属性值在ReadView的min_trx_id和max_trx_id之间说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问），根据roll_pointer跳到下一个版本。
下一个版本的列name的内容是’马’，该版本的trx_id值也为80，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
下一个版本的列name的内容是’李瑾’，该版本的trx_id值为60，小于ReadView中的min_trx_id值，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’李瑾’的记录。
之后，我们把事务id为80的事务提交一下，然后再到事务id为120的事务中更新一下表teacher 中number为1的记录：

Transaction120

BEGIN;

更新了一些别的表的记录

UPDATE teacher  SET name = '严' WHERE number = 1;
UPDATE teacher  SET name = '晁' WHERE number = 1;

1
2
3
4
5
6
7
8
9

此刻，表teacher 中number为1的记录的版本链就长这样：

然后再到刚才使用REPEATABLE READ隔离级别的事务中继续查找这个number为1的记录，如下：

使用READ COMMITTED隔离级别的事务

BEGIN;

SELECE1：Transaction 80、120均未提交

SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'

SELECE2：Transaction 80提交，Transaction 120未提交

SELECT * FROM teacher WHERE number = 1; # 得到的列name的值为'李瑾'

1
2
3
4
5
6
7
8
9
10

这个SELECE2的执行过程如下：

因为当前事务的隔离级别为REPEATABLE READ，而之前在执行SELECE1时已经生成过ReadView了，所以此时直接复用之前的ReadView，之前的ReadView的m_ids列表的内容就是[80, 120]，min_trx_id为80，max_trx_id为121，creator_trx_id为0。

根据前面的分析，返回的值还是’李瑾’。

也就是说两次SELECT查询得到的结果是重复的，记录的列name值都是’李瑾’，这就是可重复读的含义。

总结一下就是：

ReadView中的比较规则(前两条)

1、如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

2、如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。

MVCC下的幻读解决和幻读现象

前面我们已经知道了，REPEATABLE READ隔离级别下MVCC可以解决不可重复读问题，那么幻读呢？MVCC是怎么解决的？幻读是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录，而这个记录来自另一个事务添加的新记录。
我们可以想想，在REPEATABLE READ隔离级别下的事务T1先根据某个搜索条件读取到多条记录，然后事务T2插入一条符合相应搜索条件的记录并提交，然后事务T1再根据相同搜索条件执行查询。结果会是什么？按照ReadView中的比较规则(后两条)：
3、如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
4、如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间(min_trx_id < trx_id < max_trx_id)，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

不管事务T2比事务T1是否先开启，事务T1都是看不到T2的提交的。请自行按照上面介绍的版本链、ReadView以及判断可见性的规则来分析一下。
但是，在REPEATABLE READ隔离级别下InnoDB中的MVCC 可以很大程度地避免幻读现象，而不是完全禁止幻读。怎么回事呢？我们来看下面的情况：

我们首先在事务T1中：

select * from teacher where number = 30;
1

很明显，这个时候是找不到number = 30的记录的。
我们在事务T2中，执行：

insert into teacher values(30,'豹','数据湖');
1

通过执行insert into teacher values(30,‘豹’,‘数据湖’);，我们往表中插入了一条number = 30的记录。
此时回到事务T1，执行：

update teacher set domain='RocketMQ' where number=30;
select * from teacher where number = 30;
1
2

嗯，怎么回事？事务T1很明显出现了幻读现象。
在REPEATABLE READ隔离级别下，T1第一次执行普通的SELECT 语句时生成了一个ReadView（但是版本链没有），之后T2向teacher 表中新插入一条记录并提交，然后T1也进行了一个update语句。
ReadView并不能阻止T1执行UPDATE 或者DELETE 语句来改动这个新插入的记录，但是这样一来，这条新记录的trx_id隐藏列的值就变成了T1的事务id。

之后T1再使用普通的SELECT 语句去查询这条记录时就可以看到这条记录了，也就可以把这条记录返回给客户端。因为这个特殊现象的存在，我们也可以认为MVCC 并不能完全禁止幻读（就是第一次读如果是空的情况，且在自己事务中进行了该条数据的修改）。

21、MySQL InnoDB 锁的基本类型

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

锁的基本模式——共享锁

第一个行级别的锁就是我们在官网看到的 Shared Locks （共享锁），我们获取了一行数据的读锁以后，可以用来读取数据，所以它也叫做读锁。而且多个事务可以共享一把读锁。那怎么给一行数据加上读锁呢？

我们可以用 select lock in share mode;的方式手工加上一把读锁。

释放锁有两种方式，只要事务结束，锁就会自动执行事务，包括提交事务和结束事务。

锁的基本模式——排它锁

第二个行级别的锁叫做 Exclusive Locks（排它锁），它是用来操作数据的，所以又叫做写锁。只要一个事务获取了一行数据的排它锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。

排它锁的加锁方式有两种，第一种是自动加排他锁，可能是同学们没有注意到的：

我们在操作数据的时候，包括增删改，都会默认加上一个排它锁。

还有一种是手工加锁，我们用一个 FOR UPDATE 给一行数据加上一个排它锁，这个无论是在我们的代码里面还是操作数据的工具里面，都比较常用。

释放锁的方式跟前面是一样的。

锁的基本模式——意向锁

意向锁是由数据库自己维护的。

也就是说，当我们给一行数据加上共享锁之前，会自动在这张表上面加一个意向共享锁。

当我们给一行数据加上排他锁之前，会自动在这张表上面加一个意向排他锁。

反过来说：

如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁。

行锁：没有索引的表

mysql数据库 innoDB存储引擎 锁的是索引

锁的算法

t2 这张表 id 有一个主键索引。我们插入了 4 行数据，主键 id 分别是 1、4、7、10。

我们这里的划分标准是主键 id。

这些数据库里面存在的主键值，我们把它叫做 Record，记录，那么这里我们就有 4 个 Record。

根据主键，这些存在的 Record 隔开的数据不存在的区间，我们把它叫做 Gap，间隙，它是一个左开右开的区间。

假设我们有 N 个 Record，那么所有的数据会被划分成多少个 Gap 区间？答案是 N+1，就像我们把一条绳子砍 N 刀，它最后肯定是变成 N+1 段。

最后一个，间隙（Gap）连同它左边的记录（Record），我们把它叫做临键的区间，它是一个左开右闭的区间。

如果主键索引不是整型，是字符怎么办呢？字符可以排序吗？ 基于 ASCII 码

记录锁

第一种情况，当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一

条记录的时候，这个时候使用的就是记录锁。

比如 where id = 1 4 7 10 。

间隙锁

第二种情况，当我们查询的记录不存在，无论是用等值查询还是范围查询的时候，它使用的都是间隙锁。

临键锁

第三种情况，当我们使用了范围查询，不仅仅命中了 Record 记录，还包含了 Gap 间隙，在这种情况下我们使用的就是临键锁，它是 MySQL 里面默认的行锁算法，相当于记录锁加上间隙锁。

比如我们使用>5 <9 ， 它包含了不存在的区间，也包含了一个 Record 7。

锁住最后一个 key 的下一个左开右闭的区间。

select * from t2 where id >5 and id <=7 for update; 锁住(4,7]和(7,10]

select * from t2 where id >8 and id <=10 for update; 锁住 (7,10]，(10,+∞)**

总结：为什么要锁住下一个左开右闭的区间？——就是为了解决幻读的问题。

22、索引下推是什么？

索引下推是索引下推是 MySQL 5.6 及以上版本上推出的，用于对查询进行优化。

索引下推是把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断，这样能有效减少回表。

举例说明：

首先使用联合索引（name，age），现在有这样一个查询语句：

select *  from t_user where name like 'L%' and age = 17;
1

这条语句从最左匹配原则上来说是不符合的，原因在于只有name用的索引，但是age并没有用到。

不用索引下推的执行过程：

第一步：利用索引找出name带'L'的数据行：LiLei、Lili、Lisa、Lucy 这四条索引数据
第二步：再根据这四条索引数据中的 id 值，逐一进行回表扫描，从聚簇索引中找到相应的行数据，将找到的行数据返回给 server 层。
第三步：在server层判断age = 17,进行筛选，最终只留下 Lucy 用户的数据信息。
1
2
3

使用索引下推的执行过程：

第一步：利用索引找出name带'L'的数据行：LiLei、Lili、Lisa、Lucy 这四条索引数据
第二步：根据 age = 17 这个条件，对四条索引数据进行判断筛选，最终只留下 Lucy 用户的数据信息。
（注意：这一步不是直接进行回表操作，而是根据 age = 17 这个条件，对四条索引数据进行判断筛选）
第三步：将符合条件的索引对应的 id 进行回表扫描，最终将找到的行数据返回给 server 层。
1
2
3
4

比较二者的第二步我们发现，索引下推的方式极大的减少了回表次数。

索引下推需要注意的情况：

下推的前提是索引中有 age 列信息，如果是其它条件，如 gender = 0，这个即使下推下来也没用

开启索引下推：

索引下推是 MySQL 5.6 及以上版本上推出的，用于对查询进行优化。默认情况下，索引下推处于启用状态。我们可以使用如下命令来开启或关闭。

set optimizer_switch='index_condition_pushdown=off';  -- 关闭索引下推
set optimizer_switch='index_condition_pushdown=on';  -- 开启索引下推
1
2

23、如何进行慢SQL查询

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

打开慢日志开关

因为开启慢查询日志是有代价的（跟 bin log、optimizer-trace 一样），所以它默认是关闭的：

show variables like 'slow_query%';
1

除了这个开关，还有一个参数，控制执行超过多长时间的 SQL 才记录到慢日志，默认是 10 秒。

除了这个开关，还有一个参数，控制执行超过多长时间的 SQL 才记录到慢日志，默认是 10 秒。

show variables like '%long_query%';
1

可以直接动态修改参数（重启后失效）。
1

set @@global.slow_query_log=1; -- 1 开启，0 关闭，重启后失效 
set @@global.long_query_time=3; -- mysql 默认的慢查询时间是 10 秒，另开一个窗口后才会查到最新值 

show variables like '%long_query%'; 
show variables like '%slow_query%';
1
2
3
4
5

或者修改配置文件 my.cnf。
1

以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。
1

slow_query_log = ON 
long_query_time=2 
slow_query_log_file =/var/lib/mysql/localhost-slow.log
1
2
3

模拟慢查询：
1

select sleep(10);
1

查询 user_innodb 表的 500 万数据（检查是不是没有索引）。
1

SELECT * FROM `user_innodb` where phone = '136';
1

慢日志分析

1、日志内容

show global status like 'slow_queries'; -- 查看有多少慢查询 
show variables like '%slow_query%'; -- 获取慢日志目录
1
2

cat /var/lib/mysql/ localhost-slow.log
1

有了慢查询日志，怎么去分析统计呢？比如 SQL 语句的出现的慢查询次数最多，平均每次执行了多久？人工肉眼分析显然不可能。
1

2、mysqldumpslow

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

MySQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目录下。

mysqldumpslow --help
1

例如：查询用时最多的 10 条慢 SQL：

mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost-slow.log
1

Count 代表这个 SQL 执行了多少次；

Time 代表执行的时间，括号里面是累计时间；

Lock 表示锁定的时间，括号是累计；

Rows 表示返回的记录数，括号是累计。

除了慢查询日志之外，还有一个 SHOW PROFILE 工具可以使用

24、如何查看执行计划

你们的导向图：https://www.processon.com/view/link/643c031f6dcb245472ab26c9

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表（没有任何索引）。

我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表（没有任何索引）。

CREATE TABLE `course` ( `cid` INT ( 3 ) DEFAULT NULL, `cname` VARCHAR ( 20 ) DEFAULT NULL, `tid` INT ( 3 ) DEFAULT NULL ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

CREATE TABLE `teacher` ( `tid` INT ( 3 ) DEFAULT NULL, `tname` VARCHAR ( 20 ) DEFAULT NULL, `tcid` INT ( 3 ) DEFAULT NULL ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

CREATE TABLE `teacher_contact` ( `tcid` INT ( 3 ) DEFAULT NULL, `phone` VARCHAR ( 200 ) DEFAULT NULL ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

INSERT INTO `course` VALUES ( '1', 'mysql', '1' );

INSERT INTO `course` VALUES ( '2', 'jvm', '1' );

INSERT INTO `course` VALUES ( '3', 'juc', '2' );

INSERT INTO `course` VALUES ( '4', 'spring', '3' );

INSERT INTO `teacher` VALUES ( '1', 'bobo', '1' );

INSERT INTO `teacher` VALUES ( '2', '老严', '2' );

INSERT INTO `teacher` VALUES ( '3', 'dahai', '3' );

INSERT INTO `teacher_contact` VALUES ( '1', '13688888888' );

INSERT INTO `teacher_contact` VALUES ( '2', '18166669999' );

INSERT INTO `teacher_contact` VALUES ( '3', '17722225555' );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

explain 的结果有很多的字段，我们详细地分析一下。
1

先确认一下环境：
1

select version(); 
show variables like '%engine%';
1
2

1、id

id 是查询序列编号。
1

id 值不同

id 值不同的时候，先查询 id 值大的（先大后小）。
1

-- 查询 mysql 课程的老师手机号
EXPLAIN SELECT
	tc.phone 
FROM
	teacher_contact tc 
WHERE
	tcid = ( SELECT tcid FROM teacher t WHERE t.tid = ( SELECT c.tid FROM course c WHERE c.cname = 'mysql' ) );
1
2
3
4
5
6
7

查询顺序：course c——teacher t——teacher_contact tc。
1

先查课程表，再查老师表，最后查老师联系方式表。子查询只能以这种方式进行，只有拿到内层的结果之后才能进行外层的查询。
1

id 值相同（从上往下）

-- 查询课程 ID 为 2，或者联系表 ID 为 3 的老师 
EXPLAIN SELECT
	t.tname,
	c.cname,
	tc.phone 
FROM
	teacher t,
	course c,
	teacher_contact tc 
WHERE
	t.tid = c.tid 
	AND t.tcid = tc.tcid 
	AND ( c.cid = 2 OR tc.tcid = 3 );
1
2
3
4
5
6
7
8
9
10
11
12
13

id 值相同时，表的查询顺序是
1

从上往下顺序执行。例如这次查询的 id 都是 1，查询的顺序是 teacher t（3 条）——course c（4 条）——teacher_contact tc（3 条）。

既有相同也有不同

如果 ID 有相同也有不同，就是 ID 不同的先大后小，ID 相同的从上往下。
1

2、select type 查询类型

这里并没有列举全部（其它：DEPENDENT UNION、DEPENDENT SUBQUERY、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION）。
1

下面列举了一些常见的查询类型：
1

SIMPLE

简单查询，不包含子查询，不包含关联查询 union。
1

EXPLAIN SELECT * FROM teacher;
1

再看一个包含子查询的案例：

-- 查询 mysql 课程的老师手机号 
EXPLAIN SELECT
	tc.phone 
FROM
	teacher_contact tc 
WHERE
	tcid = ( SELECT tcid FROM teacher t WHERE t.tid = ( SELECT c.tid FROM course c WHERE c.cname = 'mysql' ) );
1
2
3
4
5
6
7

PRIMARY

子查询 SQL 语句中的主查询，也就是最外面的那层查询。
1

SUBQUERY

子查询中所有的内层查询都是 SUBQUERY 类型的。
1

DERIVED

衍生查询，表示在得到最终查询结果之前会用到临时表。例如：
1

-- 查询 ID 为 1 或 2 的老师教授的课程
EXPLAIN SELECT
	cr.cname 
FROM
	( SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2 ) cr;
1
2
3
4
5

对于关联查询，先执行右边的 table（UNION），再执行左边的 table，类型是DERIVED
1

UNION

用到了 UNION 查询。同上例。
1

UNION RESULT

主要是显示哪些表之间存在 UNION 查询。<union2,3>代表 id=2 和 id=3 的查询存在 UNION。同上例。
1

3、type连接类型

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

所有的连接类型中，上面的最好，越往下越差。
1

在常用的链接类型中：system > const > eq_ref > ref > range > index > all
1

这 里 并 没 有 列 举 全 部 （ 其 他 ： fulltext 、 ref_or_null 、 index_merger 、unique_subquery、index_subquery）。
1

以上访问类型除了 all，都能用到索引。

const

主键索引或者唯一索引，只能查到一条数据的 SQL。
1

DROP TABLE
IF
	EXISTS single_data;
CREATE TABLE single_data ( id INT ( 3 ) PRIMARY KEY, content VARCHAR ( 20 ) );
INSERT INTO single_data
VALUES
	( 1, 'a' );
EXPLAIN SELECT
	* 
FROM
	single_data a 
WHERE
	id = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13

system

system 是 const 的一种特例，只有一行满足条件。例如：只有一条数据的系统表。
1

EXPLAIN SELECT * FROM mysql.proxies_priv;
1

eq_ref

通常出现在多表的 join 查询，表示对于前表的每一个结果,，都只能匹配到后表的一行结果。一般是唯一性索引的查询（UNIQUE 或 PRIMARY KEY）。
1

eq_ref 是除 const 之外最好的访问类型。
1

先删除 teacher 表中多余的数据，teacher_contact 有 3 条数据，teacher 表有 3条数据。
1

DELETE 
FROM
	teacher 
WHERE
	tid IN ( 4, 5, 6 );
COMMIT;
-- 备份
INSERT INTO `teacher`
VALUES
	( 4, '老严', 4 );
INSERT INTO `teacher`
VALUES
	( 5, 'bobo', 5 );
INSERT INTO `teacher`
VALUES
	( 6, 'seven', 6 );
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

为 teacher_contact 表的 tcid（第一个字段）创建主键索引。
1

-- ALTER TABLE teacher_contact DROP PRIMARY KEY; 
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);
1
2

为 teacher 表的 tcid（第三个字段）创建普通索引。
1

-- ALTER TABLE teacher DROP INDEX idx_tcid;
ALTER TABLE teacher ADD INDEX idx_tcid (tcid);
1
2

执行以下 SQL 语句：
1

select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;
1

此时的执行计划（teacher_contact 表是 eq_ref）：
1

小结：

以上三种 system，const，eq_ref，都是可遇而不可求的，基本上很难优化到这个状态。

ref

查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。
1

例如：使用 tcid 上的普通索引查询：
1

explain SELECT * FROM teacher where tcid = 3;
1

range

索引范围扫描。
1

如果 where 后面是 between and 或 <或 > 或 >= 或 <=或 in 这些，type 类型就为 range。
1

不走索引一定是全表扫描（ALL），所以先加上普通索引。
1

-- ALTER TABLE teacher DROP INDEX idx_tid; 
ALTER TABLE teacher ADD INDEX idx_tid (tid);
1
2

执行范围查询（字段上有普通索引）：
1

EXPLAIN SELECT * FROM teacher t WHERE t.tid <3; 
-- 或
EXPLAIN SELECT * FROM teacher t WHERE tid BETWEEN 1 AND 2;
1
2
3

IN 查询也是 range（字段有主键索引）
1

EXPLAIN SELECT * FROM teacher_contact t WHERE tcid in (1,2,3);
1

index

Full Index Scan，查询全部索引中的数据（比不走索引要快）。
1

EXPLAIN SELECT tid FROM teacher;
1

all

Full Table Scan，如果没有索引或者没有用到索引，type 就是 ALL。代表全表扫描。
1

小结：

一般来说，需要保证查询至少达到 range 级别，最好能达到 ref。
1

ALL（全表扫描）和 index（查询全部索引）都是需要优化的。
1

4、possible_key、key

可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。
1

possible_key 可以有一个或者多个，可能用到索引不代表一定用到索引。
1

反过来，possible_key 为空，key 可能有值吗？
1

表上创建联合索引：
1

ALTER TABLE user_innodb DROP INDEX comidx_name_phone; 
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
1
2

执行计划（改成 select name 也能用到索引）：
1

explain select phone from user_innodb where phone='126';
1

结论：是有可能的（这里是覆盖索引的情况）。
1

如果通过分析发现没有用到索引，就要检查 SQL 或者创建索引。
1

5、key_len

索引的长度（使用的字节数）。跟索引字段的类型、长度有关。
1

表上有联合索引：KEY
1

comidx_name_phone (name,phone)

explain select * from user_innodb where name ='jim';
1

6、rows

MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。
1

7、filtered

这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。
1

8、ref

使用哪个列或者常数和索引一起从表中筛选数据。
1

9、Extra

执行计划给出的额外的信息说明。
1

using index

用到了覆盖索引，不需要回表。
1

EXPLAIN SELECT tid FROM teacher ;
1

using where

使用了 where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在 server 层进行过滤（跟是否使用索引没有关系）。
1

EXPLAIN select * from user_innodb where phone ='13866667777';
1

using filesort

不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）。需要优化。（复合索引的前提）
1

ALTER TABLE user_innodb DROP INDEX comidx_name_phone; 
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
1
2

EXPLAIN select * from user_innodb where name ='jim' order by id;
1

（order by id 引起）
1

using temporary

用到了临时表。例如（以下不是全部的情况）：
1

1、distinct 非索引列
1

EXPLAIN select DISTINCT(tid) from teacher t;
1

2、group by 非索引列
1

EXPLAIN select tname from teacher group by tname;
1

3、使用 join 的时候，group 任意列
1

EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;
1

需要优化，例如创建复合索引。
1

总结一下：

模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。

分析出问题之后，就是对 SQL 语句的具体优化。

25、分库分表

垂直分库，减少并发压力。水平分表，解决存储瓶颈。

垂直分库的做法，把一个数据库按照业务拆分成不同的数据库：

水平分库分表的做法，把单张表的数据按照一定的规则分布到多个数据库。

以上是架构层面的优化，可以用缓存，主从，分库分表

26、我们为什么需要分库分表

在分库分表之前，就需要考虑为什么需要拆分。我们做一件事，肯定是有充分理由的。所以得想好分库分表的理由是什么。我们现在就从两个维度去思考它，为什么要分库？为什么要分表？

1.1 为什么要分库
如果业务量剧增，数据库可能会出现性能瓶颈，这时候我们就需要考虑拆分数据库。从这两方面来看：

磁盘存储
业务量剧增，MySQL单机磁盘容量会撑爆，拆成多个数据库，磁盘使用率大大降低。

并发连接支撑
我们知道数据库连接数是有限的。在高并发的场景下，大量请求访问数据库，MySQL单机是扛不住的！高并发场景下，会出现too many connections报错。

当前非常火的微服务架构出现，就是为了应对高并发。它把订单、用户、商品等不同模块，拆分成多个应用，并且把单个数据库也拆分成多个不同功能模块的数据库（订单库、用户库、商品库），以分担读写压力。

1.2 为什么要分表
假如你的单表数据量非常大，存储和查询的性能就会遇到瓶颈了，如果你做了很多优化之后还是无法提升效率的时候，就需要考虑做分表了。一般千万级别数据量，就需要分表。

这是因为即使SQL命中了索引，如果表的数据量超过一千万的话，查询也是会明显变慢的。这是因为索引一般是B+树结构，数据千万级别的话，B+树的高度会增高，查询就变慢啦。MySQL的B+树的高度怎么计算的呢？跟大家复习一下：

InnoDB存储引擎最小储存单元是页，一页大小就是16k。B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据，B+树结构图如下：

假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。

如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16. 非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是 8+6=14 字节，16k/14B =16*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，大概可以存放两千万左右的记录。B+树高度一般为1-3层，如果B+到了4层，查询的时候会多查磁盘的次数，SQL就会变慢。

因此单表数据量太大，SQL查询会变慢，所以就需要考虑分表啦。

27、什么时候考虑分库分表？

对于MySQL，InnoDB存储引擎的话，单表最多可以存储10亿级数据。但是的话，如果真的存储这么多，性能就会非常差。一般数据量千万级别，B+树索引高度就会到3层以上了，查询的时候会多查磁盘的次数，SQL就会变慢。

阿里巴巴的《Java开发手册》提出：

单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。

那我们是不是等到数据量到达五百万，才开始分库分表呢？

不是这样的，我们应该提前规划分库分表，如果估算3年后，你的表都不会到达这个五百万，则不需要分库分表。

MySQL服务器如果配置更好，是不是可以超过这个500万这个量级，才考虑分库分表？

虽然配置更好，可能数据量大之后，性能还是不错，但是如果持续发展的话，还是要考虑分库分表

一般什么类型业务表需要才分库分表？

通用是一些流水表、用户表等才考虑分库分表，如果是一些配置类的表，则完全不用考虑，因为不太可能到达这个量级。

28、如何选择分表键

分表键，即用来分库/分表的字段，换种说法就是，你以哪个维度来分库分表的。比如你按用户ID分表、按时间分表、按地区分表，这些用户ID、时间、地区就是分表键。

一般数据库表拆分的原则，需要先找到业务的主题。比如你的数据库表是一张企业客户信息表，就可以考虑用了客户号做为分表键。

为什么考虑用客户号做分表键呢？ Saas

这是因为表是基于客户信息的，所以，需要将同一个客户信息的数据，落到一个表中，避免触发全表路由。

非分表键如何查询

分库分表后，有时候无法避免一些业务场景，需要通过非分表键来查询。

假设一张用户表，根据userId做分表键，来分库分表。但是用户登录时，需要根据用户手机号来登陆。这时候，就需要通过手机号查询用户信息。而手机号是非分表键。

非分表键查询，一般有这几种方案：

遍历：最粗暴的方法，就是遍历所有的表，找出符合条件的手机号记录（不建议）
将用户信息冗余同步到ES，同步发送到ES，然后通过ES来查询（推荐）
其实还有基因法：比如非分表键可以解析出分表键出来，比如常见的，订单号生成时，可以包含客户号进去，通过订单号查询，就可以解析出客户号。但是这个场景除外，手机号似乎不适合冗余userId。

分表策略如何选择

5.1 range范围

range，即范围策略划分表。比如我们可以将表的主键 order_id，按照从 0~300万的划分为一个表，300万~600万划分到另外一个表。

有时候我们也可以按时间范围来划分，如不同年月的订单放到不同的表，它也是一种range的划分策略。

优点： Range范围分表，有利于扩容。
缺点： 可能会有热点问题。因为订单id是一直在增大的，也就是说最近一段时间都是汇聚在一张表里面的。比如最近一个月的订单都在300万~600万之间，平时用户一般都查最近一个月的订单比较多，请求都打到order_1表啦。

5.2 hash取模

hash取模策略：

指定的路由key（一般是user_id、order_id、customer_no作为key）对分表总数进行取模，把数据分散到各个表中。

比如原始订单表信息，我们把它分成4张分表：

比如id=1，对4取模，就会得到1，就把它放到t_order_1;
id=3，对4取模，就会得到3，就把它放到t_order_3;
一般，我们会取哈希值，再做取余：

Math.abs(orderId.hashCode()) % table_number
复制代码
优点：hash取模的方式，不会存在明显的热点问题。
缺点：如果未来某个时候，表数据量又到瓶颈了，需要扩容，就比较麻烦。所以一般建议提前规划好，一次性分够。（可以考虑一致性哈希）

5.3 一致性Hash

如果用hash方式分表，前期规划不好，需要扩容二次分表，表的数量需要增加，所以hash值需要重新计算，这时候需要迁移数据了。

比如我们开始分了10张表，之后业务扩展需要，增加到20张表。那问题就来了，之前根据orderId取模10后的数据分散在了各个表中，现在需要重新对所有数据重新取模20来分配数据

为了解决这个扩容迁移问题，可以使用一致性hash思想来解决。

一致性哈希：在移除或者添加一个服务器时，能够尽可能小地改变已存在的服务请求与处理请求服务器之间的映射关系。一致性哈希解决了简单哈希算法在分布式哈希表存在的动态伸缩等问题

29、跨节点Join关联问题

在单库未拆分表之前，我们如果要使用join关联多张表操作的话，简直so easy啦。但是分库分表之后，两张表可能都不在同一个数据库中了，那么如何跨库join操作呢？

跨库Join的几种解决思路：

**字段冗余：**把需要关联的字段放入主表中，避免关联操作；比如订单表保存了卖家ID（sellerId），你把卖家名sellerName也保存到订单表，这就不用去关联卖家表了。这是一种空间换时间的思想。
**全局表：**比如系统中所有模块都可能会依赖到的一些基础表（即全局表），在每个数据库中均保存一份。
**数据抽象同步：**比如A库中的a表和B库中的b表有关联，可以定时将指定的表做同步，将数据汇合聚集，生成新的表。一般可以借助ETL工具。
**应用层代码组装：**分开多次查询，调用不同模块服务，获取到数据后，代码层进行字段计算拼装。

30、order by,group by等聚合函数问题

跨节点的 count,order by,group by以及聚合函数等问题，都是一类的问题，它们一般都需要基于全部数据集合进行计算。可以分别在各个节点上得到结果后，再在应用程序端进行合并。

31、分库分表后的分页问题

方案1（全局视野法）：

在各个数据库节点查到对应结果后，在代码端汇聚再分页。这样优点是业务无损，精准返回所需数据；缺点则是会返回过多数据，增大网络传输，也会造成空查，

比如分库分表前，你是根据创建时间排序，然后获取第2页数据。如果你是分了两个库，那你就可以每个库都根据时间排序，然后都返回2页数据，然后把两个数据库查询回来的数据汇总，再根据创建时间进行内存排序，最后再取第2页的数据。

方案2（业务折衷法-禁止跳页查询）：

这种方案需要业务妥协一下，只有上一页和下一页，不允许跳页查询了。

这种方案，查询第一页时，是跟全局视野法一样的。但是下一页时，需要把当前最大的创建时间传过来，然后每个节点，都查询大于创建时间的一页数据，接着汇总，内存排序返回。

32、分库分表选择哪种中间件

目前流行的分库分表中间件比较多：

• Sharding-JDBC 当当开源，好用，建议
• cobar 阿里巴巴产品，不支持读写分离
• Mycat 建议 比较重，但是好用
• Atlas 360开源产品，不支持分布式分表，所有表同库
• TDDL（淘宝） 阿里巴巴产品，非代理式，不支持读写分离
• vitess 谷歌产品，还可以，但是用的少，支持高并发 ZK管理 PRC方式进行处理数据，重

33、如何评估分库数量

对于MySQL来说的话，一般单库超过5千万记录，DB的压力就非常大了。所以分库数量多少，需要看单库处理记录能力有关。
如果分库数量少，达不到分散存储和减轻DB性能压力的目的；如果分库的数量多，对于跨多个库的访问，应用程序需要访问多个库。
一般是建议分4~10个库，一般建议10个库以下，不然不好管理

34、分表要停服嘛？不停服怎么做？

不用停服。不停服的时候，应该怎么做呢，主要分五个步骤：

编写代理层，加个开关（控制访问新的DAO还是老的DAO，或者是都访问），灰度期间，还是访问老的DAO。
发版全量后，开启双写，既在旧表新增和修改，也在新表新增和修改。日志或者临时表记下新表ID起始值，旧表中小于这个值的数据就是存量数据，这批数据就是要迁移的。
通过脚本把旧表的存量数据写入新表。
停读旧表改读新表，此时新表已经承载了所有读写业务，但是这时候不要立刻停写旧表，需要保持双写一段时间。
当读写新表一段时间之后，如果没有业务问题，就可以停写旧表啦