学MySQL，这篇万字总结，真的够用了

脚本之家

你与百万开发者在一起

作者：学习 Java 的小姐姐

出处：转载自微信公众号学习 Java 的小姐姐（ID：huangtest01）

“

这篇文章将从查询缓存，索引，优化器，explain，redo 日志，undo 日志，事务隔离级别，锁等方面来讲解 MySQL。

开局一张图

这张图是重点！！！咱要先对 MySQL 有一个宏观的了解，知道他的执行流程。

一条 SQL 语句过来的流程是什么样的？

①当客户端连接到 MySQL 服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过 SSL 证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。

②在正式查询之前，服务器会检查查询缓存，如果能找到对应的查询，则不必进行查询解析，优化，执行等过程，直接返回缓存中的结果集。

③MySQL 的解析器会根据查询语句，构造出一个解析树，主要用于根据语法规则来验证语句是否正确，比如 SQL 的关键字是否正确，关键字的顺序是否正确。而预处理器主要是进一步校验，比如表名，字段名是否正确等。

④查询优化器将解析树转化为查询计划，一般情况下，一条查询可以有很多种执行方式，最终返回相同的结果，优化器就是根据成本找到这其中最优的执行计划。

⑤执行计划调用查询执行引擎，而查询引擎通过一系列 API 接口查询到数据。

⑥得到数据之后，在返回给客户端的同时，会将数据存在查询缓存中。

查询缓存

我们先通过 show variables like '%query_cache%' 来看一下默认的数据库配置，此为本地数据库的配置。

概念

①have_query_cache：当前的 MySQL 版本是否支持“查询缓存”功能。

②query_cache_limit：MySQL 能够缓存的最大查询结果，查询结果大于该值时不会被缓存。默认值是 1048576(1MB)。

③query_cache_min_res_unit：查询缓存分配的最小块（字节）。默认值是 4096（4KB）。

当查询进行时，MySQL 把查询结果保存在 query cache，但是如果保存的结果比较大，超过了 query_cache_min_res_unit 的值，这时候 MySQL 将一边检索结果，一边进行保存结果。

他保存结果也是按默认大小先分配一块空间，如果不够，又要申请新的空间给他。

如果查询结果比较小，默认的 query_cache_min_res_unit 可能造成大量的内存碎片，如果查询结果比较大，默认的 query_cache_min_res_unit 又不够，导致一直分配块空间。

所以可以根据实际需求，调节 query_cache_min_res_unit 的大小。

注：如果上面说的内容有点弯弯绕，那举个现实生活中的例子，比如咱现在要给运动员送水，默认的是 500ml 的瓶子，如果过来的是少年运动员，可能 500ml 太大了，他们喝不完，造成了浪费。

那我们就可以选择 300ml 的瓶子，如果过来的是成年运动员，可能 500ml 不够，那他们一瓶喝完了，又开一瓶，直接不渴为止。那么那样开瓶子也要时间，我们就可以选择 1000ml 的瓶子。

④query_cache_size：为缓存查询结果分配的总内存。

⑤query_cache_type：默认为 on，可以缓存除了以 select sql_no_cache 开头的所有查询结果。

⑥query_cache_wlock_invalidate：如果该表被锁住，是否返回缓存中的数据，默认是关闭的。

原理

MySQL 的查询缓存实质上是缓存 SQL 的 Hash 值和该 SQL 的查询结果，如果运行相同的 SQL，服务器直接从缓存中去掉结果，而不再去解析，优化，寻找最低成本的执行计划等一系列操作，大大提升了查询速度。

但是万事有利也有弊：

第一个弊端就是如果表的数据有一条发生变化，那么缓存好的结果将全部不再有效。这对于频繁更新的表，查询缓存是不适合的。

比如一张表里面只有两个字段，分别是 id 和 name，数据有一条为 1，张三。

我使用 select * from 表名 where name=“张三”来进行查询，MySQL 发现查询缓存中没有此数据，会进行一系列的解析，优化等操作进行数据的查询。

查询结束之后将该 SQL 的 Hash 和查询结果缓存起来，并将查询结果返回给客户端。

但是这个时候我又新增了一条数据 2，张三。如果我还用相同的 SQL 来执行，他会根据该 SQL 的 Hash 值去查询缓存中，那么结果就错了。

所以 MySQL 对于数据有变化的表来说，会直接清空关于该表的所有缓存。这样其实效率是很差的。

第二个弊端就是缓存机制是通过对 SQL 的 Hash，得出的值为 Key，查询结果为 Value 来存放的，那么就意味着 SQL 必须完完全全一模一样，否则就命不中缓存。

我们都知道 Hash 值的规则，就算很小的查询，哈希出来的结果差距是很多的，所以 select * from 表名 where name=“张三”和SELECT * FROM 表名 WHERE NAME=“张三”和select * from 表名 where name = “张三”，三个SQL 哈希出来的值是不一样的。

大小写和空格影响了他们，所以并不能命中缓存，但其实他们搜索结果是完全一样的。

生产如何设置 MySQL Query Cache

先来看线上参数：

我们发现将 query_cache_type 设置为 OFF，其实网上资料和各大云厂商提供的云服务器都是将这个功能关闭的，从上面的原理来看，在一般情况下，他的弊端大于优点。

索引

例子：创建一个名为 user 的表，其包括 id，name，age，sex 等字段信息。此外，id 为主键聚簇索引，idx_name 为非聚簇索引。

CREATE TABLE `user` (
  `id` varchar(10) NOT NULL DEFAULT '',
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我们将其设置 10 条数据，便于下面的索引的理解：

INSERT INTO `user` VALUES ('1', 'andy', '20', '女');
INSERT INTO `user` VALUES ('10', 'baby', '12', '女');
INSERT INTO `user` VALUES ('2', 'kat', '12', '女');
INSERT INTO `user` VALUES ('3', 'lili', '20', '男');
INSERT INTO `user` VALUES ('4', 'lucy', '22', '女');
INSERT INTO `user` VALUES ('5', 'bill', '20', '男');
INSERT INTO `user` VALUES ('6', 'zoe', '20', '男');
INSERT INTO `user` VALUES ('7', 'hay', '20', '女');
INSERT INTO `user` VALUES ('8', 'tony', '20', '男');
INSERT INTO `user` VALUES ('9', 'rose', '21', '男');

聚簇索引（主键索引）

先来一张图镇楼，接下来就是看图说话：

他包含两个特点：

• 使用记录主键值的大小来进行记录和页的排序。页内的记录是按照主键的大小顺序排成一个单项链表。各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。

• 叶子节点存储的是完整的用户记录。

注：聚簇索引不需要我们显示的创建，他是由 InnoDB 存储引擎自动为我们创建的。如果没有主键，其也会默认创建一个。

非聚簇索引（二级索引）

上面的聚簇索引只能在搜索条件是主键时才能发挥作用，因为聚簇索引可以根据主键进行排序的。

如果搜索条件是 name，在刚才的聚簇索引上，我们可能遍历，挨个找到符合条件的记录，但是，这样真的是太蠢了，MySQL 不会这样做的。

如果我们想让搜索条件是 name 的时候，也能使用索引，那可以多创建一个基于 name 的二叉树，如下图：

他与聚簇索引的不同：

• 叶子节点内部使用 name 字段排序，叶子节点之间也是使用 name 字段排序。

• 叶子节点不再是完整的数据记录，而是 name 和主键值。

为什么不再是完整信息？MySQL 只让聚簇索引的叶子节点存放完整的记录信息，因为如果有好几个非聚簇索引，他们的叶子节点也存放完整的记录绩效，那就不浪费空间啦。

如果我搜索条件是基于 name，需要查询所有字段的信息，那查询过程是啥？

• 根据查询条件，采用 name 的非聚簇索引，先定位到该非聚簇索引某些记录行。

• 根据记录行找到相应的 id，再根据 id 到聚簇索引中找到相关记录。这个过程叫做回表。

联合索引

图就不画了，简单来说，如果 name 和 age 组成一个联合索引，那么先按 name 排序，如果 name 一样，就按 age 排序。

一些原则

①最左前缀原则。一个联合索引（a,b,c）,如果有一个查询条件有 a，有 b，那么他则走索引，如果有一个查询条件没有 a，那么他则不走索引。

②使用唯一索引。具有多个重复值的列，其索引效果最差。例如，存放姓名的列具有不同值，很容易区分每行。

而用来记录性别的列，只含有“男”，“女”，不管搜索哪个值，都会得出大约一半的行，这样的索引对性能的提升不够高。

③不要过度索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。

在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

④索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = ’2014-05-29’就不能使用到索引。

原因很简单，B+ 树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。

所以语句应该写成：

create_time = unix_timestamp(’2014-05-29’);

⑤一定要设置一个主键。前面聚簇索引说到如果不指定主键，InnoDB 会自动为其指定主键，这个我们是看不见的。

反正都要生成一个主键的，还不如我们设置，以后在某些搜索条件时还能用到主键的聚簇索引。

⑥主键推荐用自增 id，而不是 uuid。上面的聚簇索引说到每页数据都是排序的，并且页之间也是排序的，如果是 uuid，那么其肯定是随机的，其可能从中间插入，导致页的分裂，产生很多表碎片。

如果是自增的，那么其有从小到大自增的，有顺序，那么在插入的时候就添加到当前索引的后续位置。当一页写满，就会自动开辟一个新的页。

注：如果自增 id 用完了，那将字段类型改为 bigint，就算每秒 1 万条数据，跑 100 年，也没达到 bigint 的最大值。

万年面试题（为什么索引用 B+ 树）

①B+ 树的磁盘读写代价更低：B+ 树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对 B 树更小。

如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对 IO 读写次数就降低了。

②由于 B+ 树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可。

但是 B 树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以 B+ 树更加适合在区间查询的情况，所以通常 B+ 树用于数据库索引。

优化器

在开篇的图里面，我们知道了 SQL 语句从客户端经由网络协议到查询缓存，如果没有命中缓存，再经过解析工作，得到准确的 SQL，现在就来到了我们这模块说的优化器。

首先，我们知道每一条 SQL 都有不同的执行方法，要不通过索引，要不通过全表扫描的方式。

那么问题就来了，MySQL 是如何选择时间最短，占用内存最小的执行方法呢？

什么是成本？

• I/O 成本。数据存储在硬盘上，我们想要进行某个操作需要将其加载到内存中，这个过程的时间被称为 I/O 成本。默认是 1。

• CPU 成本。在内存对结果集进行排序的时间被称为 CPU 成本。默认是 0.2。

单表查询的成本

先来建一个用户表 dev_user，里面包括主键 id，用户名 username，密码 password，外键 user_info_id，状态 status，外键 main_station_id，是否外网访问 visit，这七个字段。

索引有两个，一个是主键的聚簇索引，另一个是显式添加的以 username 为字段的唯一索引 uname_unique。

如果搜索条件是 select * from dev_user where username='XXX'，那么 MySQL 是如何选择相关索引呢？

①使用所有可能用到的索引

我们可以看到搜索条件 username，所以可能走 uname_unique 索引。也可以做聚簇索引，也就是全表扫描。

②计算全表扫描代价

我们通过 show table status like ‘dev_user’命令知道 rows 和 data_length 字段，如下图：

rows：表示表中的记录条数，但是这个数据不准确，是个估计值。

data_length：表示表占用的存储空间字节数。data_length=聚簇索引的页面数量 X 每个页面的大小。

反推出页面数量=1589248÷16÷1024=97：

• I/O 成本：97X1=97

• CPU 成本：6141X0.2=1228

• 总成本：97+1228=1325

③计算使用不同索引执行查询的代价

因为要查询出满足条件的所有字段信息，所以要考虑回表成本：

• I/O 成本=1+1X1=2（范围区间的数量+预计二级记录索引条数）

• CPU 成本=1X0.2+1X0.2=0.4（读取二级索引的成本+回表聚簇索引的成本）

• 总成本= I/O 成本+CPU 成本=2.4

④对比各种执行方案的代价，找出成本最低的那个

上面两个数字一对比，成本是采用 uname_unique 索引成本最低。

多表查询的成本

对于两表连接查询来说，他的查询成本由下面两个部分构成：

• 单次查询驱动表的成本

• 多次查询被驱动表的成本（具体查询多次取决于对驱动表查询的结果集有多少个记录）

index dive

如果前面的搜索条件不是等值，而是区间，如 select * from dev_user where username>'admin' and username<'test' 这个时候我们是无法看出需要回表的数量。

步骤 1：先根据 username>'admin' 这个条件找到第一条记录，称为区间最左记录。

步骤 2：再根据 username<'test' 这个条件找到最后一条记录，称为区间最右记录。

步骤 3：如果区间最左记录和区间最右记录相差不是很远，可以准确统计出需要回表的数量。

如果相差很远，就先计算 10 页有多少条记录，再乘以页面数量，最终模糊统计出来。

Explain

产品来索命：

• 产品：为什么这个页面出来这么慢？
  

• 开发：因为你查的数据多呗，他就是这么慢

• 产品：我不管，我要这个页面快点，你这样，客户怎么用啊

• 开发：......你行你来

哈哈哈哈，不瞎 BB 啦，如果有些 SQL 贼慢，我们需要知道他有没有走索引，走了哪个索引，这个时候我就需要通过 explain 关键字来深入了解 MySQL 内部是如何执行的。

id：一般来说一个 select 一个唯一 id，如果是子查询，就有两个 select，id 是不一样的，但是凡事有例外，有些子查询的，他们 id 是一样的。

这是为什么呢？那是因为 MySQL 在进行优化的时候已经将子查询改成了连接查询，而连接查询的 id 是一样的。

select_type：

• simple：不包括 union 和子查询的查询都算 simple 类型。

• primary：包括 union，union all，其中最左边的查询即为 primary。

• union：包括 union，union all，除了最左边的查询，其他的查询类型都为 union。

table：显示这一行是关于哪张表的。

type 访问方法：

• ref：普通二级索引与常量进行等值匹配

• ref_or_null：普通二级索引与常量进行等值匹配，该索引可能是 null

• const：主键或唯一二级索引列与常量进行等值匹配

• range：范围区间的查询

• all：全表扫描

possible_keys：对某表进行单表查询时可能用到的索引。

key：经过查询优化器计算不同索引的成本，最终选择成本最低的索引。

rows：

• 如果使用全表扫描，那么 rows 就代表需要扫描的行数

• 如果使用索引，那么 rows 就代表预计扫描的行数

filtered：

• 如果全表扫描，那么 filtered 就代表满足搜索条件的记录的满分比

• 如果是索引，那么 filtered 就代表除去索引对应的搜索，其他搜索条件的百分比

redo 日志（物理日志）

InnoDB 存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作都是将页的数据加载到内存中，然后进行操作，再将数据刷回到硬盘上。

那么问题就来了，如果我要给张三转账 100 块钱，事务已经提交了，这个时候 InnoDB 把数据加载到内存中，这个时候还没来得及刷入硬盘，突然停电了，数据库崩了。

重启之后，发现我的钱没有转成功，这不是尴尬了吗？

解决方法很明显，我们在硬盘加载到内存之后，进行一系列操作，一顿操作猛如虎，还未刷新到硬盘之前，先记录下，在 XXX 位置我的记录中金额减 100，在 XXX 位置张三的记录中金额加 100。

然后再进行增删改查操作，最后刷入硬盘。如果未刷入硬盘，在重启之后，先加载之前的记录，那么数据就回来了。

这个记录就叫做重做日志，即 redo 日志。他的目的是想让已经提交的事务对数据的修改是永久的，就算他重启，数据也能恢复出来。

log buffer（日志缓冲区）

为了解决磁盘速度过慢的问题，redo 日志不能直接写入磁盘，咱先整一大片连续的内存空间给他放数据。

这一大片内存就叫做日志缓冲区，即 log buffer。到了合适的时候，再刷入硬盘。至于什么时候是合适的，这个下一章节说。

我们可以通过 show VARIABLES like 'innodb_log_buffer_size' 命令来查看当前的日志缓存大小。

下图为线上的大小：

redo 日志刷盘时机

由于 redo 日志一直都是增长的，且内存空间有限，数据也不能一直待在缓存中，我们需要将其刷新至硬盘上。 

那什么时候刷新到硬盘呢？

• log buffer 空间不足。上面有指定缓冲区的内存大小，MySQL 认为日志量已经占了总容量的一半左右，就需要将这些日志刷新到磁盘上。 

• 事务提交时。我们使用 redo 日志的目的就是将他未刷新到磁盘的记录保存起来，防止丢失，如果数据提交了，我们是可以不把数据提交到磁盘的，但为了保证持久性，必须把修改这些页面的 redo 日志刷新到磁盘。 

• 后台线程不同的刷新后台有一个线程，大概每秒都会将 log buffer 里面的 redo 日志刷新到硬盘上。

• checkpoint 下下小节讲。

redo 日志文件组

我们可以通过 show variables like 'datadir' 命令找到相关目录，底下有两个文件，分别是 ib_logfile0 和 ib_logfile1，如下图所示：

我们将缓冲区 log buffer 里面的 redo 日志刷新到这个两个文件里面，他们写入的方式是循环写入的，先写 ib_logfile0，再写 ib_logfile1，等 ib_logfile1 写满了，再写 ib_logfile0。 

那这样就会存在一个问题，如果 ib_logfile1 写满了，再写 ib_logfile0，之前 ib_logfile0 的内容不就被覆盖而丢失了吗？这就是 checkpoint 的工作啦。

checkpoint

redo 日志是为了系统崩溃后恢复脏页用的，如果这个脏页可以被刷新到磁盘上，那么他就可以功成身退，被覆盖也就没事啦。

冲突补习：从系统运行开始，就不断的修改页面，会不断的生成 redo 日志。

redo 日志是不断递增的，MySQL 为其取了一个名字日志序列号 Log Sequence Number，简称 lsn。他的初始化的值为 8704，用来记录当前一共生成了多少 redo 日志。

redo 日志是先写入 log buffer，之后才会被刷新到磁盘的 redo 日志文件。MySQL 为其取了一个名字 flush_to_disk_lsn。

用来说明缓存区中有多少的脏页数据被刷新到磁盘上啦。他的初始值和 lsn 一样，后面的差距就有了。

做一次 checkpoint 分为两步：

• 计算当前系统可以被覆盖的 redo 日志对应的 lsn 最大值是多少。redo 日志可以被覆盖，意味着他对应的脏页被刷新到磁盘上。

  只要我们计算出当前系统中最早被修改的 oldest_modification，只要系统中 lsn 小于该节点的 oldest_modification 值，磁盘的 redo 日志都是可以被覆盖的。

•  将 lsn 过程中的一些数据统计。

undo 日志

undo log 有两个作用：提供回滚和多个行版本控制（MVCC）。

undo log 和 redo log 记录物理日志不一样，它是逻辑日志。

可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。

举个例子：

insert into a(id) values(1);(redo)

这条记录是需要回滚的。回滚的语句是：

delete from a where id = 1;(undo)

试想想看：如果没有做 insert into a(id) values(1);(redo)，那么 delete from a where id = 1;(undo) 这句话就没有意义了。

现在看下正确的恢复：

• 先 insert into a(id) values(1);(redo)

• 然后 delete from a where id = 1;(undo)

• 系统就回到了原先的状态，没有这条记录了
  

存储方式：是存在段之中。

事务

事务中有一个隔离性特征，理论上在某个事务对某个数据进行访问时，其他事务应该排序，当该事务提交之后，其他事务才能继续访问这个数据。

但是这样子对性能影响太大，我们既想保持事务的隔离性，又想让服务器在出来多个事务时性能尽量高些，所以只能舍弃一部分隔离性而去性能。

事务并发执行的问题：

①脏写：这个太严重了，任何隔离级别都不允许发生）。

• sessionA：修改了一条数据，回滚掉

• sessionB：修改了同一条数据，提交掉

对于 sessionB 来说，明明数据更新了也提交了事务，不能说自己啥都没干。

②脏读：一个事务读到另一个未提交事务修改的数据。

• session A：查询，得到某条数据

• session B：修改某条数据，但是最后回滚掉啦

• session A：在 sessionB 修改某条数据之后，在回滚之前，读取了该条记录

对于 session A 来说，读到了 session 回滚之前的脏数据。

③不可重复读：前后多次读取，同一个数据内容不一样。

• session A：查询某条记录

• session B : 修改该条记录，并提交事务

• session A : 再次查询该条记录，发现前后查询不一致
  

④幻读：前后多次读取，数据总量不一致。

• session A：查询表内所有记录

• session B：新增一条记录，并查询表内所有记录

• session A：再次查询该条记录，发现前后查询不一致
  

数据库都有的四种隔离级别，MySQL 事务默认的隔离级别是可重复读，而且 MySQL 可以解决了幻读的问题：

• 未提交读：脏读，不可重复读，幻读都有可能发生

• 已提交读：不可重复读，幻读可能发生

• 可重复读：幻读可能发生

• 可串行化：都不可能发生

但凡事没有百分百，emmmm，其实 MySQL 并没有百分之百解决幻读的问题。

举个例子：

• session A：查询某条不存在的记录。

• session B：新增该条不存在的记录，并提交事务。

• session A：再次查询该条不存在的记录，是查询不出来的，但是如果我尝试修改该条记录，并提交，其实他是可以修改成功的。

MVCC

版本链：对于该记录的每次更新，都会将值放在一条 undo 日志中，算是该记录的一个旧版本，随着更新次数的增多，所有版本都会被 roll_pointer 属性连接成一个链表，即为版本链。

readview：

• 未提交读：因为可以读到未提交事务修改的记录，所以可以直接读取记录的最新版本就行

• 已提交读：每次读取之前都生成一个 readview

• 可重复读：只有在第一次读取的时候才生成 readview

• 可串行化：InnoDB 涉及了加锁的方式来访问记录

参考文献：

• 【原创】面试官：讲讲 MySQL 表设计要注意啥

• 【原创】杂谈自增主键用完了怎么办

• MySQL 是怎样运行的：从根儿上理解 MySQL

• 详细分析 MySQL 事务日志(redo log 和 undo log)

- END -

更多精彩

在公众号后台对话框输入以下关键词

查看更多优质内容！

女朋友 | 大数据 | 运维 | 书单 | 算法

大数据 | JavaScript | Python | 黑客

AI | 人工智能 | 5G | 区块链

机器学习 | 数学 | 送书