当前位置：首页 > article >正文

一文解决MySQL突击面试，关键知识点总结

article 2024/11/16 0:46:53

文章目录

MySQL重要知识点回顾
- 一、索引
- - 1. 为什么需要索引
  - 2. 索引的结构
  - 3. 避免索引失效
  - - 3.1 联合索引不满足最左匹配原则
    - 3.2 隐式转换
    - 3.3 like查询
    - 3.4 索引列存在运算或者使用函数
    - 3.5 优化器
  - 4. 执行计划
  - - 4.1 type
    - 4.2 key
    - 4.3 rows
    - 4.4 extra
  - 5. 建立索引
  - - 5.1 什么情况下应该建索引？
    - 5.2 在哪些列上建索引？
    - 5.3 建什么样的索引？
    - - 5.3.1 唯一索引、普通索引
      - 5.3.2 联合索引
      - 5.3.3 联合索引要注意什么？
      - 5.3.4 全文索引
    - 5.4 建多少索引？
    - 5.5 索引的长度
    - 5.6 关于区分度的问题
- 二、SQL语句的优化
- - 1. 执行计划分析
  - 2. join大表驱动还是小表驱动？
  - 3. limit语句优化
  - - 分页
    - 限制查询条数
  - 4. 索引合并
  - 5. select * from table 不建议使用
  - 6. distinct 和 group by
- 三、大表问题
- - 1. 大表会带来哪些问题
  - - 1.1 增删改查
    - 1.2 添加索引
    - 1.3 添加字段
    - 1.4 数据同步
  - 2. 怎么解决大表问题？
  - - 2.1 垂直拆分
    - 2.2 水平拆分
    - - 2.2.1 按范围
      - 2.2.2 按区域
      - 2.2.3 按时间
      - 2.2.4 hash取模
    - 2.3 分表后带来的问题
    - - 2.3.1 事务
      - 2.3.2 排序、分组、分页
  - 3. 数据归档
  - 4. 冷热分离
- 四、内存缓冲区
- - 1. buffer pool
  - - 1.1 buffer pool的空间管理方式
    - 1.2 buffer bool使用情况
  - 2. Change Buffer
- 五、ACID特性
- - 1. 什么是ACID
  - 2. 如何实现ACID
  - - A （Atomicity, 原子性）
    - C（Consistency，一致性）
    - I（Isolation，隔离性）
    - D（Durability，持久性）
- 六、脏读、幻读，不可重复读
- - 1. 概念解释
  - 2. 案例演示
  - - 2.1 脏读问题演示
    - 2.2 不可重复读问题演示
    - 2.3 幻读问题演示
- 七、日志
- - 1. binlog
  - - 1.1 binlog的写入频率
    - 1.2 binlog日志记录格式
  - 2. Redo Log
  - 3. Undo Log
- 八、主从复制
- - 主从复制的方式
  - 主从复制大致流程
- 九、常见笔试题
- - 1. 部门工资最高的员工
  - 2. 部门工资第N高的员工
  - 3. 行转列
  - 4. 列转行
  - 5.树结构查询
  - - 5.1 查询树的根节点
    - 5.2 查询树的叶子节点
    - 5.3 查询既不是叶子也不是根的节点
    - 5.4 输出完整的树
  - 6. 排名问题

MySQL重要知识点回顾

一、索引

1. 为什么需要索引

在我们日常生活中，常见的索引应用场景有：字典、货柜、楼层索引等等，这些都是按序归类，比如字典中：0-500页都是以“a”开头的字母，501-800页都是以“b”开头的字母，这样如果我们要查询的字母是“b”开头的，我们就可以直接从第501页开始查找，如果没有这种按序归类，那么你就必须找完800页。同样的，货柜上的物品按照某些分类规则来摆放也是一样的道理，都是为了能够快速定位到目标数据。

所以，使用索引的目的就是为了能够快速查找具有某个特定值的数据，对于MySQL来说，没有索引通常就需要遍历全表数据才能确定相关的数据行，而有了索引，又因为索引是有序的，所以就能快速定位到目标数据，就像查字典一样，从而避免全表扫描。

2. 索引的结构

从索引的使用场景可以理解，索引本身必须是要有序的，因此理论上只要是有序的数据结构，都可以用来建立索引，那MySQL为什么在那么多的数据结构类型中，选择B+Tree呢？

实际上，一个非常重要的原因就是因为MySQL的数据是存储在磁盘的，而我们知道每次与磁盘交互是相对较慢的，所以我们是希望能够尽量少的发生磁盘I/O事件，而B+Tree的每个节点可以分叉出N个分支，因此只要单节点容量够大，就能大大减少与磁盘交互的次数，从而提升整体响应。

举个简单的例子：

如果节点度数为3，也就是3叉树，那么保存：20,4,13,7,1,17,9,2,6,11这组数字的效果就如下：

如果我们把节点度数增加到5，则树的高度则减少了一层

假设我们现在要在这20个数中，找到19这个数

大致查询逻辑是：

从磁盘中加载第一个节点，数据为7和13,因为19大于13，因此继续找13指向的下一个节点
从磁盘中加载13指向的节点，数据为15和17，因为19大于17，因此继续找17指向的下一个节点
从磁盘中加载17指向的节点，数据为17、18、19、20，依次遍历（实际上可以采用更高效的查找法，比如二分）找到19

可以肯定，节点中存放的数据量多少，直接影响了产生磁盘I/O的次数，而在B+Tree中，每个节点（实际上对应的参数为：innodb_page_size）默认的大小是16KB，所以了为了能够存放更多的数据，才会有索引字段尽量不要太长的建议。

既然这样，是不是可以直接调整innodb_page_size的大小，比如innodb_page_size支持你调整到32KB或者64KB，其实之所以默认是16KB，主要是因为当前主流的磁盘设置，扇区大小就是16KB，保持页的大小与扇区大小一致，可以最大限度的减少将未更改的数据重写到磁盘，此外如果是经常用于频繁写入和读取的场景，较小的page_size也是可以减少单个节点被频繁重写到磁盘的情况。

3. 避免索引失效

索引失效指的是，虽然建立了索引，但无法使用的情况，我们可以准备一张表，插入一些数据，来演示几种索引失效的场景

准备一张表

CREATE TABLE `t_user_demo` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
`name` VARCHAR(20) DEFAULT NULL COMMENT '姓名',
`phone` char(11) DEFAULT NULL COMMENT '手机号',
`age` TINYINT DEFAULT NULL COMMENT '年龄',
`sex` CHAR(1) DEFAULT NULL COMMENT '性别',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_name_phone` (`name`, `phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入一些数据

3.1 联合索引不满足最左匹配原则

严格按照索引顺序执行，肯定走索引

select * from t_user_demo where name = 'zz' and phone = '13933333333';

这个语句虽然没有按照索引的建立顺序，但是依旧可以走索引，因为mysql会对这样的语句进行排序优化，或者交换两个条件的顺序并不影响结果。

select * from t_user_demo where phone = '13933333333' and name = 'zz';

这个语句就不会走索引了，因为不满足最左匹配原则。

select * from t_user_demo where phone = '13911111111'

交换一下顺序，只要符合最左匹配原则，单查联合索引中的列也是可以的。

select * from t_user_demo where name = 'zz'

同样最左查询条件索引中断了，也不能走索引了。

select * from t_user_demo where name like '%zz%' and phone = '13933333333';

建立一个phone，age，name的联合索引。

CREATE INDEX idx_phone_age_name ON t_user_demo (phone,age,name);

走索引没问题。

select * from t_user_demo where phone = '13944444444' and age = 15 and name = 'qq'

范围条件之后的列不走索引了。

select * from t_user_demo where phone = '13944444444' and age > 15 and name = 'qq';

3.2 隐式转换

当把phone的引号去掉，则只走一个name列的索引，这是因为发生了隐式转换，phone列是char类型需要转换成相应类型进行比较。（隐式转换的根本原因是因为索引列上发生了函数计算）

select * from t_user_demo where name = 'zz' and phone = 13933333333;

3.3 like查询

左右都有%的查询，是不会走索引的。

select * from t_user_demo where name like '%zz%'

左边没有%的情况下，会走范围类型的索引。

select * from t_user_demo where name like 'zz%'

3.4 索引列存在运算或者使用函数

在age列上建立索引

CREATE INDEX idx_age ON t_user_demo (age);

索引列存在计算，不走索引

select * from t_user_demo where age - 1 = 15;

一样不会走索引

select * from t_user_demo where ABS(age) = 15;

3.5 优化器

有些情况下，优化器会认为走索引还不如全表扫描来的快，那么就会直接使用全表扫描的方式，比如索引的区分度非常低，即便使用了索引，也几乎要扫描全索引的数据，那还不如直接全表扫描来的快。

当然，有些情况下也会遇到优化器误判定的情况，此时你可以使用force index(idx_name)的方式来强制使用某个索引。

4. 执行计划

执行计划可以帮助你快速分析SQL执行的情况，在你的SQL语句前面直接加上explain关键字即可，如：explain select * from table

执行输出的列如下图：

这里面比较容易分析出问题的列一般有：type、key、rows、extra

4.1 type

一般按照结果最好到最坏依次是：system > const > eq_ref > ref > range > index > all

system：表示改表为系统表，业务上一般不会出现。
const：一般在使用主键或者唯一键作为查询条件时出现，它表示最多只会出现一个匹配行。
eq_ref：当使用主键或者唯一键作为表关联条件时出现。
ref：非主键或者唯一键作为查询条件时，或者表关联条件时出现。
range：用作范围查询时，比如：<>，is， null， between， like， in等。
index：当利用到覆盖索引时出现，也就是仅通过扫描索引树就能得到结果。
all：all就是全表扫描了，是我们要尽量避免出现的类型。

4.2 key

key表示MySQL实际使用到的索引

4.3 rows

rows虽然是一个估算值，但它可以直接体现出查询的效率，rows的数值代表着本次查询要检索的行数，因此rows越大检索的时间就越长。

4.4 extra

虽然是额外信息，但其通常展示的数据都非常有意义，通过它就可以大致了解本次查询的方式。

常见的extra有下面几种：

Using filesort：一般情况下是由于查询语句中出现了排序的要求，且排序字段又没有建立索引，必须通过扫描全表来实现。
Using index：直接通过检索索引树就能够完成查询的情况。
Using where：简单的使用到了where的条件过滤
Using temporary：必须通过建立临时表才能满足查询的情况，场景的有，order by 和 group by
Using join buffer (Block Nested Loop)：在没有使用索引键做表关联查询时，为了减少磁盘I/O的交互，MySQL可以把某张表的数据先读取到内存中，然后在内存中和另外一张表进行结果匹配，从而实现查询的优化。
Using MRR：MRR简称Multi-Range Read，主要解决的就是当在非聚集索引中过滤出结果集之后，再回到聚集索引中查询数据行时，由于非聚集索引的排序结果与聚集索引的排序结果存在差异而导致回聚集索引查询时产生较多的磁盘随机I/O的问题。

5. 建立索引

建立索引，主要考虑的是什么情况下应该建索引，在哪些列上建索引，建什么样的索引，建多少索引等方面，下面我们可以简单分析一下。

5.1 什么情况下应该建索引？

实际上在innodb存储引擎下，每张表至少都有一个主键索引（没主键会用rowid代替），对业务上使用来说，建索引就是为了提升查询效率，因此在你可预见表数据规模的前提下，应该提前建立好索引，毕竟，如果你事先没有建索引，等你发现问题再去建索引时，通常都已经对业务造成一定影响了。

5.2 在哪些列上建索引？

首先，索引列最好要有比较高的区分度，因为只有足够高的区分度才能更好的体现出索引的优势。
其次，一般就是参与where、group by、order by、关联查询等情况时需要考虑建立索引。
最后，有一点比较重要，要尽量避免在频繁更新的列上建立索引，因为每一次更新都需要对索引的存储进行维护。

5.3 建什么样的索引？

按索引类型可以分为：主键索引、唯一索引、普通索引、联合索引、全文索引。

每张表只有一个主键索引，而根据主键索引查询是非常快的，因为它是唯一的且无需回表。

5.3.1 唯一索引、普通索引

理论上能建立唯一索引的优先考虑建立唯一索引，唯一索引不但检索效率高于普通索引，且还能在业务上防止重复数据的产生，至于说因为唯一索引在写入因为有唯一性的校验，因此无法利用Change Buffer的问题，对于绝大多数场景差异其实并不是很大。

关于Change Buffer的问题后面会单独提到。

5.3.2 联合索引

正如前面索引合并的问题产生一样，如果明确有多个索引列就是会一块查询时，那肯定是改为联合索引比较高效，就这好比原本你需要到多个索引文件中挨个检索的情况，现在只需要在一个索引文件中就能完成。

5.3.3 联合索引要注意什么？

遵守最左匹配原则
区分度高的尽量放在最左边
联合索引列数不宜过多，很明显，列数过多就像字段长度太长一样，从对磁盘和内存的使用来说，都不是好的选择。

5.3.4 全文索引

全文索引在MySQL中目前只能对文本类型的字段建立，在实际业务场景中几乎用不到，毕竟有像ES这种专门干这事的组件存在，为什么还要用MySQL呢？况且限制还很多，检索也不精确。

5.4 建多少索引？

显然，索引并不是越多越好，否则也不多想，直接给所有列都加上索引就好了，我记得在阿里开发手册中也有提及到，单表建立索引不要超过5个，之所以要限制索引的数量，主要是为了对新增、修改、删除等会对表数据进行修改的操作带来影响，我们知道每一个索引都会有一份独立的存储，索引越多也就意味着每添加一行数据，除了要记录本身的行数据之外，要写的索引文件也就越多，因此一定会对写入的性能造成一定的影响，尤其是那种写多读少的场景。
此外，索引越多也就意味着要存储的内容也就越多，就需要更多的磁盘空间来存储。

所以，我认为建多少索引主要还是需要结合实际的业务场景来定夺，你只要清楚的了解其中的利弊就可以了。

5.5 索引的长度

为什么有时候建立索引的时候还需要考虑索引的长度呢？说白了就是为了减少索引占用的空间，使得一次I/O能够读取更多的索引列到内存中，提高其查询效率。

比如，有些业务场景会要求在数据的前面或者后面拼接上某种固定的值：
1230001
2340001
8210001
8740001
像这样最后拼接上0001的情况，实际上就没有必要把所有数据都存储到索引文件中了，只需要记录前三位就可以了，因为后4位完全没有区分度。

值得一提的是，在阿里开发手册中，还专门提到了，如果是在varchar类型的字段上建立索引时，必须根据文本区分度来指定索引长度。

下面我准备了一些数据来看看文本区分度的效果：

假设order_no字段长度为32位，那么通过如下计算可以得出建立的索引长度为26位是比较合适的

SELECT 
count(DISTINCT LEFT(order_no, 20)) / count(*) AS '20', 
count(DISTINCT LEFT(order_no, 22)) / count(*) AS '22', 
count(DISTINCT LEFT(order_no, 24)) / count(*) AS '24', 
count(DISTINCT LEFT(order_no, 26)) / count(*) AS '26', 
count(DISTINCT LEFT(order_no, 28)) / count(*) AS '28', 
count(DISTINCT LEFT(order_no, 30)) / count(*) AS '30', 
count(DISTINCT LEFT(order_no, 32)) / count(*) AS '32' 
FROM test;

结果如下：

5.6 关于区分度的问题

首先区分度肯定是越高越好，当然越接近100%，也就意味着索引长度越接近于字段长度，这需要你来平衡。

那么分区度过低，到底有什么不好的呢？

很明显，我们是希望能够通过尽可能少的索引空间就能获得足够精确的查询结果，举个例子：

假设有这么两条数据，一个字段值为：10001，一个字段值为：10002，他们的前4位都是1000，所以如果我们索引只保留了前4位，那无论你是根据索引查10001还是查10002，都会查得两条满足1000的数据，此时只能拿着主键ID再利用回表的方式，在主键索引中过滤，如果索引是全字段记录，那就可以直接在二级索引（非聚集索引）精确检索，甚至有时候还可以享受到覆盖索引的红利。

二、SQL语句的优化

1. 执行计划分析

上面说过执行计划可以帮助我们快速分析SQL语句的情况，一般情况下，你首先可以看看是否使用到了索引，用到的索引类型，大概需要扫描的行数，以及extra列中的一些信息。

2. join大表驱动还是小表驱动？

简单来说，在关联字段可以走索引的情况下，谁作为驱动表谁就会全表扫描，例如如下的SQL语句：

select * from t2 left join t1 on t2.a = t1.a;

其中t1是小表，t2是大表，执行流程大致如下：

先从t2表取a字段。
用a字段与t1表进行匹配，类似于：select * from t1 where t1.a = ?
记录结果集。
然后重复1~3过程，直到t2全部扫描结束。

如果用：M（表示t2表的行数）、 N（表示t1表的行数）
时间复杂度大致如下：M * logN（索引扫描）

所以，很明显M的大小更能影响整体的复杂度，因此才会有建议用小表作为驱动表。

原则上，关联字段必须要建立索引，如果没有，MySQL虽然也会采用Block Nested Loop的方式进行优化，但实际上效果并不明显。

3. limit语句优化

分页

当使用limit分页时，比如像这样查询：select * from table LIMIT M,N

M越大查询的越慢，一般可以利用覆盖索引的特性来进件优化

select * from page_test a, ( select id from page_test LIMIT 100000,5) b where a.id = b.id;

限制查询条数

如果你只需要查询一条数据，那么就没有必要让MySQL扫描全表，建议加上limit 1

4. 索引合并

索引合并是指，对于单表通过多个索引过滤出来的数据，进行合并处理（一般包含：并集、交集、差集等合并处理逻辑）

比如像如下几种：

交集合并

select * from table where 主键ID < ? and 普通索引 = ?

并集合并

select * from table where 主键ID = ? or 普通索引 = ?

交集+并集

select * from table where 普通索引 = ? and 主键ID < ? or 普通索引 = ?

并集+排序

select * from table where 主键ID < ? or 普通索引 < ?

一般有索引合并必然会对CPU有一定的消耗，理论上应该尽量避免索引合并的情况发生，考虑通过建立联合索引来解决。

5. select * from table 不建议使用

select * from table很明显查的数据越多就越慢，这不仅仅与行数相关，列数也是会有一定影响的，并且如果可以利用覆盖索引的也利用不上了。

6. distinct 和 group by

大多数情况下，下面两个SQL语句查询是等效的。

SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > ?;

SELECT c1, c2, c3 FROM t1 WHERE c1 > ? GROUP BY c1, c2, c3;

三、大表问题

究竟多大的数据量算大表，这个数量本身就不太好定义，更重要的是你应该清楚表越大越会对什么样的事情造成影响，这才是真正需要关心的。

为了方便有个好理解的概念，我们先认为单表数据量到达千万级别，就算是大表。

1. 大表会带来哪些问题

1.1 增删改查

首先，毫无疑问，随着数据量的不断增长，对增删改查肯定是最直接的影响，但我想说的是，如果你能命中区分度较高的索引时，实际上还是非常快的，这个只针对无法通过索引过滤或者索引区分度不够高的情况。

1.2 添加索引

给一个千万级别的表添加索引时，是需要考虑至少存在秒级别的锁表情况，对业务是否会造成影响。
或者考虑采取一些替代方案，比如无锁变更（整个过程会变得更长）之类的，网上都有介绍，这里就不多说了。

1.3 添加字段

和添加索引一样，对大表进行DDL操作，都有可能导致锁表，这点需要额外的注意，替代方案和上面的一样。

1.4 数据同步

表越大，对于之后如果要做表数据备份、迁移，或者数据更新、初始化来说，都将是非常漫长的过程。

2. 怎么解决大表问题？

2.1 垂直拆分

垂直拆分就是针对表的列做拆分，通常是为了检索数据时能尽量减少单行数据的大小以及让数据页一次能够加载更多的数据，而我们通常所说的大表指的是单表的数据行数，因此垂直拆分并不是针对解决大表问题的。

2.2 水平拆分

水平拆分是解决大表问题的常见方案，简单来说就是把单表拆分成多表，被拆分后的每张表数据不重复，所有表数据加起来就是全集，比如我们可以将一张有1W条数据的表，拆分为5张表，每张表记录2000条数据。

水平拆分规则

2.2.1 按范围

表1：0~2000

表2：2001~4000

表3：4001~6000

表4：6001~8000

表5：8001~10000

非常简单的拆分规则，弊端也很明显，范围一旦确认后就很难再调整，无论是调整范围大小，还是调整范围内的数据。

2.2.2 按区域

按区域划分实际上和范围划分思想类似，都是圈定一个范围，然后按这个范围来拆分，按区域拆分有个很大的问题就是热点区域，比如：上海和新疆，很明显他们的数据量并不是一个数量级的，所以一般对于上海肯定还会再细分，按区域一般更多是业务上的选择。

2.2.3 按时间

和按范围、按时间也都差不多，一般适用数据冷热分离的情况。

2.2.4 hash取模

hash取模是表拆分规则常用的方式，一般会肯定比较有特征的业务数据，比如user_id, order_id等字段，假设我们要将一张表拆分为16（0~15）张表，那我们计算数据入哪张表的公式就是：hash(user_id) % 16

hash取模最大的好处就是能够将数据拆分的足够均匀。

上面无论哪种拆分方式，我们都要尽量事先能规划好分表的数量，因为一旦按照分表的数量开始拆分之后，后面想再调整就比较麻烦了。

2.3 分表后带来的问题

2.3.1 事务

显然，从原来的操作一张表，到分表后的多张表，肯定会额外带来事务的问题。

2.3.2 排序、分组、分页

无论是排序、分组还是分页肯定都是在全量数据集中完成的，所以分表后对于需要扫描全表才能得到的结果也需要额外处理。

3. 数据归档

数据归档一般是指将部分历史数据从原表中挪出去，比如放到一张专门的历史归档表中，之所以能这样处理的前提就是因为历史数据对于业务来说，实际使用场景已经并不多了，如果你的业务场景对待几年前的数据和刚产生的数据没有任何区别，那数据归档也没有任何意义了。

4. 冷热分离

冷热分离实际上更强调的是对热数据检索的支持，本质上被分离出来的冷数据，也是数据归档的思想，将更多的资源倾向分配给热数据以支持快速的检索。

四、内存缓冲区

1. buffer pool

buffer pool是一种常见的加速查询的优化方式，在一些专用的服务器上，差不多80%的内存都会用来构建buffer pool，可以大大减少访问磁盘的次数，提升查询效率。

1.1 buffer pool的空间管理方式

可以大致认为buffer pool采用的就是LRU的算法来进行空间管理，头部为最近访问过的数据，尾部为访问较少的数据。

举个简单的例子：假设现在只能存在3条数据。

第一次访问1，加入头部。

1 -> null

第二次访问2，加入头部。

2 -> 1 -> null

第三次访问3，加入头部。

3 -> 2 -> 1 -> null

第四次访问4，加入头部，1被淘汰出去。

4 -> 3 -> 2 -> null

这个应该不难理解，但考虑MySQL的实际业务场景，如果此时来了一次全表访问，那么4,3,2可能全部都会被淘汰出去，也就是说因此一次较大量的数据访问，将一些热点数据给淘汰了，这显然是不合理的。

所以，针对这个问题，MySQL也在原有的LRU算法上做了一些改进，按照3:5比例把buffer pool分为两块，其中3/8称为old区，5/8称为new区，new区的尾部指向old区的头部，new区的头部为整个链表的头部，old区的尾部为整个链表的尾部。

改变过的LRU算法，假设我们可以存储8页数据，大致淘汰过程如下：

此时，访问数据页2，直接按照LRU方式处理

但如果要访问一个不存在的数据页时，则只会被放在old区，比如访问数据页9，变成像下图这样

如果要访问数据页存在于old区，则每次要做如下判断：

若被访问的数据页在old区超过了1秒（可通过innodb_old_blocks_time指定），则把他挪到整个链表的头部（new区）。
若被访问的数据页在old区未超过1秒，则保持不变。

这样，我们看看如果再来一次全表扫描，会是什么样的：

old区的数据页可能全部会被置换掉，同时因为同一数据页连续访问的时间间隔肯定不会超过1秒（因为全表扫描肯定是顺序的，扫描耗时肯定用不到1秒），所以这些数据页不会被挪到new区。
最终可以认为最多只有old区会被“污染”，但很快又会被淘汰出去。

经过改造后的LRU算法，可以确保new区不被“污染”，从而也就保证了查询的效率。

1.2 buffer bool使用情况

执行命令：show engine innodb status

2. Change Buffer

官方介绍对于change buffer的介绍

The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

简单来说，就是当发生INSERT, UPDATE, or DELETE这样的DML操作时，如果被操作的二级索引数据页并不在buffer pool中，为了提升操作效率，MySQL会直接将这些DML语句直接写在change buffer中，这样就能减少一次从磁盘加载数据到内存的过程，之后如果再访问到此数据页时，再将此数据页加载到buffer pool中，并根据change buffer中的记录进行更新即可。

当然，可以看出，如果每次刚写完数据就要查询，那change buffer就没什么好处，不过，大多数情况下并没有此类需求，所以先写入change buffer，等待一定时间后再批量写入磁盘就好了。

五、ACID特性

1. 什么是ACID

A （Atomicity, 原子性）
- 一个事务中的所有操作要不全部成功，要不全部失败，不能出现部分成功，部分失败的情况。
C（Consistency，一致性）
- 数据库设计上这个含义比较模糊，简单可以理解为财务的对账一样，两边数据的加加减减必须要能保持一致。
I（Isolation，隔离性）
- 主要是针对在并发场景下，对于共享资源的访问要有一定的隔离性，在MySQL中隔离性也是分等级的，根据不同的业务需求选择不同的隔离性，主要依靠锁+MVCC来实现，隔离性越强，数据库的吞吐就越差。
D（Durability，持久性）
- 事务一旦提交，数据将会保存到数据库中，此时如果数据库发生错误，也不会造成数据丢失。

2. 如何实现ACID

A （Atomicity, 原子性）

MySQL主要是利用undo日志来实现原子性。

undo log又称撤销日志，就是记录相反操作的日志，比如执行delete时，记录的则是对应的insert，当执行insert时，记录则是对应的delete，当执行update时，记录则是相反的update记录，在执行所有操作之前undo log会先落盘。

C（Consistency，一致性）

对于一致性的问题，它是一个比较模糊的概念，原子性、持久性、隔离性都是为了保证一致性的实现，或者说一致性更侧重于业务层面由开发人员的控制。

I（Isolation，隔离性）

隔离性的实现主要是依靠MVCC+锁来实现。

MVCC（多版本并发控制）是实现并发访问的一种方式，在MySQL的innodb引擎读已提交和可重复读两种隔离级别下，事务在select时实际上读取的是版本链中的数据。

说简单点，就是每次访问数据前会先生成一份类似快照的信息，通过控制快照记录的时机来实现不同级别下的隔离性。

D（Durability，持久性）

持久性主要是利用redo log、binlog日志来保证的。

关于redo log和binlog会在下面的章节中单独介绍。

六、脏读、幻读，不可重复读

1. 概念解释

脏读

一个事务会读到另一个未提交的事务数据。

不可重复读

一个事务内多次读取到的数据不一致，A事务第一次读到值是1, B事务把1修改成2，并且提交了，A事务第二次读到值也变成了2，针对update。

幻读

一个事务内多次读取到的数据不一致，第一次读到1条，第二次读到2条。针对insert，delete，数据行数发生了变化。

2. 案例演示

要解释脏读、幻读，不可重复读，直接用案例最合适。

准备一张表tran_test ，id为主键，使用innodb存储引擎。

CREATE TABLE tran_test ( id INT PRIMARY KEY, NAME VARCHAR ( 10 ) ) ENGINE = INNODB;

插入3条数据。

insert into tran_test values(1,'zhangsan');
insert into tran_test values(2,'wangwu');
insert into tran_test values(3,'lisi');

打开的会话窗口都需要关闭自动提交，默认为1：表示开启自动提交，设置为0：表示关闭自动提交。

select @@autocommit;

set autocommit = 0;

2.1 脏读问题演示

A：set session transaction isolation level read uncommitted;
A：start transaction;
A：insert into tran_test values(4,'zhaoliu');
A：select * from tran_test; --可以查询到最新插入的数据
B：set session transaction isolation level read uncommitted;
B：select * from tran_test; -- 也可以查询A最新插入的数据，尽管A的此时的事务还未提交

2.2 不可重复读问题演示

当把隔离级别调整到不可重复读之后，再按照脏度问题演示的流程来执行，可以确认新建的会话窗口是读不到其他会话中未提交的事务的。

但是会产生不可重复读问题。

A：set session transaction isolation level read committed;
A：start transaction;
A：select * from tran_test where id = 1; --结果为zhangsan
B：start transaction;
B：update tran_test set name = 'zs' where id = 1; --把id为1的name修改为zs
A：select * from tran_test where id = 1; -- B未提交所以A此时查询结果还是zhangsan
B：commit;
A：select * from tran_test where id = 1; -- B事务提交后，A再查询此时结果已经变成了zs

A在同一个事务中，但两次查询结果不一致。

2.3 幻读问题演示

读者可自行测试，当隔离级别调整到可重复读时，前面两个操作流程都不会再出现脏度和不可重复读的问题。

不过，幻读的问题还是存在的。

A：set session transaction isolation level read committed;
A：start transaction;
A：select * from tran_test where id = 4; --结果为空
B：start transaction;
B：insert into tran_test values(4,'zhaoliu'); -- B插入一条id=4的数据
A：select * from tran_test where id = 4; --结果依旧为空
B：commit; -- B提交事务
A：select * from tran_test where id = 4; --结果依旧为空
A：insert into tran_test values(4,'zhaoliu'); -- 插入失败，报主键冲突

insert into tran_test values(4,'zhaoliu')
> 1062 - Duplicate entry '4' for key 'PRIMARY'
> 时间: 0s

七、日志

1. binlog

binlog又称二进制日志，是MySQL中非常重要的日志，与存储引擎无关，是MySQL服务级别的日志，它记录了所有的DML和DDL语句（查询类除外），binlog的主要可以用来做数据同步和数据恢复，binlog也是MySQL实现主从复制的主要方式

1.1 binlog的写入频率

binlog的写入频率是由sync_binlog参数控制，参数值有0、1、N，默认为1，每个参数的含义如下：

0：不让MySQL主动将binlog写入磁盘，而是让操作系统的文件写入系统来决定什么时候刷入到磁盘，在此状态下，可以达到最佳性能，但也可能带来丢失部分数据写入的日志。
1：这是最安全的设置，在事务提交的同时，也将日志同步写入磁盘，当然也会对性能产生一定的影响。
N：当N大于1时，则表示每N次事务，写一次磁盘，相当于在0~1之间再找一个平衡点，但是依旧存在数据丢失的可能。

1.2 binlog日志记录格式

STATEMENT

基于语句的日志记录，顾名思义，就是按照原SQL语句来记录。

优点

减少写入日志文件的体量，尤其是影响的行数特别多时，相比ROW格式可以大大减少存储的信息，这同时也意味着，当再进行数据同步或者数据恢复时速度会更快。
由于日志记录的是完整SQL语句，因此更有利于SQL审查。

缺点

对于一些带有函数的语句，可能并不能正确记录，比如像：UUID()、SYSDATE()、RAND()等。
因为是完整语句的记录，因此原SQL语句执行所需要的消耗，相当于还需要再来一遍。

ROW

基于数据行的日志记录，根据写入事件对数据行的影响，记录影响后的行信息。

优点

基于行模式的记录，是最安全的形式，所有的更改都可以复制。
对比STATEMENT的缺点，当在副本上再次执行insert、update或者delete语句时，所需要的行锁将会更少。

缺点

正如前面所说，基于行数据的记录，会大大增加日志的存储体量，同时在数据同步和数据恢复时也需要更多的时间。
行模式并不会记录原SQL语句，因此也没办法从源头执行。

MIXED

混合模式的日志记录，默认采用STATEMENT方式记录，当遇到某些特殊条件时（比如写入语句中有用到类似SYSDATE()的函数时），则转为ROW模式。

MIXED模式就是STATEMENT和ROW模式的结合。

2. Redo Log

redo log又叫重做日志，是InnoDB存储引擎中才有的，主要是在服务崩溃时用来恢复数据的记录，redo log记录了对数据库中每个页的修改，redo log分为两部分：一部分是在内存中的缓冲日志（redo log buffer），一部分是在磁盘上的文件日志（redo log file），内存是会丢失的，而磁盘是永久的。

与undo log一样，redo log也是先于数据之前先写入磁盘的。

如何提升落盘效率？

常见的方式就是先写内存，然后在从内存刷到磁盘，为了提高吞吐也可以选择在内存中停留一会再刷到磁盘上，但是这样如果在停留的那段时期内，宕机或者掉电那数据可就丢了，没错，因此MySQL也给了几种方案让你选择，就看你如何取舍了。

大致落盘过程：

写数据 —> 写redo log —> 写log buffer —> 写log file

innodb_flush_log_at_trx_commit参数决定了写入时机，0、1、2时分别表示下图写入方案，默认为1，也只有1的情况下才能保证持久性。

3. Undo Log

假设数据库有一条记录：age=1，现在要将age修改为2，流程如下：

1、事务开启。
2、记录undo日志，update table set age = 1 where id = 1。
3、执行update，修改 update table set age = 2 where id = 1。
4、事务提交。
5、数据写入磁盘。

如果在写入磁盘前，发生异常，则可以通过undo log进行回滚。

八、主从复制

主从复制的方式

MySQL默认采用异步复制方式，从节点可以复制主节点中的所有数据或者特定的数据库、数据表，主节点数据变化时，可以实时的同步到从节点中。

MySQL主从复制是基于binlog文件的方式，当master服务器有变化时以“事件”的形式写入binlog文件，binlog中的信息会根据记录的数据库变化以不同的日志格式存储，slave服务器读取binlog，并在自己的数据库中执行binlog中的记录。

MySQL的主从复制功能主要使用3个线程来实现，1个在master服务器上，2个在slave服务器上

主从复制大致流程

Binary log dump thread（二进制日志转储线程）

当从节点连接时，主节点创建一个线程将binlog文件内容发送到从节点，这个线程在主节点的show processlist输出中标识为binlog转储线程。
Replication I/O thread（IO线程）

当在从节点上发出START SLAVE语句时，从节点会创建一个I/O线程，该线程连接到主节点，并要求它发送其binlog日志中记录的更新。
I/O线程读取主节点的binlog转储线程发送的更新，并将它们复制到本地文件中，这些文件构成了从节点的relay log（中继日志）。
在SHOW SLAVE STATUS的输出中，该线程的状态显示为Slave_IO_running。
Replication SQL thread （SQL线程）

从节点创建一个SQL线程来读取I/O线程写入的中继日志，并执行其中包含的事务。

九、常见笔试题

1. 部门工资最高的员工

Employee表

id	name	salary	departmentId
1	zhangsan	8000	1
2	lisi	5000	1
3	wangwu	6000	2
4	zhaoliu	7000	2
5	qianqi	8000	1

Department表

id	name
1	研发部
2	销售部

期望输出结果

departmentName	employeeName	salary
研发部	zhangsan	8000
销售部	zhaoliu	7000
研发部	qianqi	8000

方法一：

SELECT
	c. NAME AS Department,
	a. NAME AS Employee,
	a.salary AS Salary
FROM
	Employee a,
	(
		SELECT
			departmentId,
			max(salary) AS sal
		FROM
			Employee
		GROUP BY
			departmentId
	) b,
	Department c
WHERE
	a.departmentId = b.departmentId
AND a.salary = b.sal
AND a.departmentId = c.id

方法二：

SELECT
	c. NAME AS Department,
	a. NAME AS Employee,
	a.salary AS Salary
FROM
	Employee a,
	Department c
WHERE
	(a.departmentId, a.salary) IN (
		SELECT
			departmentId,
			max(salary)
		FROM
			Employee
		GROUP BY
			departmentId
	)
AND a.departmentId = c.id

2. 部门工资第N高的员工

解题方法

SELECT
	*
FROM
	Employee a,
	(
		SELECT
			salary
		FROM
			Employee
		GROUP BY
			salary
		ORDER BY
			salary DESC
		LIMIT N,
		1
	) b
WHERE
	a.salary = b.salary

3. 行转列

表结构

mysql> desc column_to_row;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | varchar(255) | YES  |     | NULL    |       |
| subject1 | varchar(255) | YES  |     | NULL    |       |
| subject2 | varchar(255) | YES  |     | NULL    |       |
| subject3 | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

表数据


mysql> select * from column_to_row;
+------+----------+----------+----------+
| name | subject1 | subject2 | subject3 |
+------+----------+----------+----------+
| 小明 | 80       | 90       | 100      |
| 小红 | 100      | 90       | 80       |
+------+----------+----------+----------+

解题方法

mysql> select name, 'subject1' as 'subject', subject1 as score from column_to_row
    -> union all
    -> select name, 'subject2' as 'subject', subject2 as score from column_to_row
    -> union all
    -> select name, 'subject3' as 'subject', subject3 as score from column_to_row
    -> ;
+------+----------+-------+
| name | subject  | score |
+------+----------+-------+
| 小明 | subject1 | 80    |
| 小红 | subject1 | 100   |
| 小明 | subject2 | 90    |
| 小红 | subject2 | 90    |
| 小明 | subject3 | 100   |
| 小红 | subject3 | 80    |
+------+----------+-------+

4. 列转行

表结构

mysql> desc row_to_column;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(255) | YES  |     | NULL    |       |
| score   | varchar(255) | YES  |     | NULL    |       |
| subject | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

表数据

mysql> select * from row_to_column;
+------+-------+---------+
| name | score | subject |
+------+-------+---------+
| 小明 | 80    | 语文    |
| 小明 | 90    | 数学    |
| 小明 | 85    | 英语    |
| 小红 | 70    | 语文    |
| 小红 | 95    | 数学    |
| 小红 | 85    | 英语    |
+------+-------+---------+

解题方法

mysql> select name, max(if(subject = '语文', score, null)) as '语文' ,max(if(subject = '数学', score, null)) as '数学',max(if(subject = '英语', score, null)) as '英语' from row_to_column group by name;  
+------+------+------+------+
| name | 语文 | 数学 | 英语 |
+------+------+------+------+
| 小明 | 80   | 90   | 85   |
| 小红 | 70   | 95   | 85   |
+------+------+------+------+

5.树结构查询

表结构

mysql> desc t_org;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| p_id  | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

表数据

mysql> insert into t_org (id, p_id, name) values (1,null,'一级机构'),(2,1,'二级机构1-1'),(3,1,'二级机构1-2'),(4,2,'三级机构2-1'),(5,3,'三级机构3-1');
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t_org;
+----+------+-------------+
| id | p_id | name        |
+----+------+-------------+
|  1 | NULL | 一级机构    |
|  2 |    1 | 二级机构1-1 |
|  3 |    1 | 二级机构1-2 |
|  4 |    2 | 三级机构2-1 |
|  5 |    3 | 三级机构3-1 |
+----+------+-------------+
5 rows in set (0.06 sec)

5.1 查询树的根节点

mysql> select * from t_org where p_id is null;
+----+------+----------+
| id | p_id | name     |
+----+------+----------+
|  1 | NULL | 一级机构 |
+----+------+----------+
1 row in set (0.03 sec)

5.2 查询树的叶子节点

mysql> select * from t_org where id not in (select distinct p_id from t_org where p_id is not null) and p_id is not null;
+----+------+-------------+
| id | p_id | name        |
+----+------+-------------+
|  4 |    2 | 三级机构2-1 |
|  5 |    3 | 三级机构3-1 |
+----+------+-------------+
2 rows in set (0.05 sec)

5.3 查询既不是叶子也不是根的节点

mysql> select * from t_org where id in (select distinct p_id from t_org where p_id is not null) and p_id is not null;
+----+------+-------------+
| id | p_id | name        |
+----+------+-------------+
|  2 |    1 | 二级机构1-1 |
|  3 |    1 | 二级机构1-2 |
+----+------+-------------+
2 rows in set (0.05 sec)

5.4 输出完整的树

方法一：

mysql> select id, name, 'root' as type from t_org where p_id is null union all select id, name, 'inner' as type from t_org where id in (select distinct p_id from t_org where p_id is not null) and p_id is not null union all select id, name, 'leaf' as type from t_org where id not in (select distinct p_id from t_org where p_id is not null) and p_id is not null;
+----+-------------+-------+
| id | name        | type  |
+----+-------------+-------+
|  1 | 一级机构    | root  |
|  2 | 二级机构1-1 | inner |
|  3 | 二级机构1-2 | inner |
|  4 | 三级机构2-1 | leaf  |
|  5 | 三级机构3-1 | leaf  |
+----+-------------+-------+
5 rows in set (0.07 sec)

方法二：

mysql> select id, name, case
    -> when id in (select id from t_org where p_id is null) then 'root'
    -> when id in (select p_id from t_org where p_id is not null) then 'inner'
    -> else 'leaf' end as 'type' from t_org;
+----+-------------+-------+
| id | name        | type  |
+----+-------------+-------+
|  1 | 一级机构    | root  |
|  2 | 二级机构1-1 | inner |
|  3 | 二级机构1-2 | inner |
|  4 | 三级机构2-1 | leaf  |
|  5 | 三级机构3-1 | leaf  |
+----+-------------+-------+
5 rows in set (0.04 sec)

6. 排名问题

按分数从高到低排名，

表结构

mysql> desc t_rank;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
| score | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

表数据

mysql> insert into t_rank (name,score) values ('xiaoming',100),('xiaozhang',98),('xiaoli',98),('xiaowang',99),('xiaohong',97); 
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t_rank;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | xiaoming  |   100 |
|  2 | xiaozhang |    98 |
|  3 | xiaoli    |    98 |
|  4 | xiaowang  |    99 |
|  5 | xiaohong  |    97 |
+----+-----------+-------+
5 rows in set (0.04 sec)

期望结果

+-----------+------+-------+
| name      | rank | score |
+-----------+------+-------+
| xiaoming  |    1 |   100 |
| xiaowang  |    2 |    99 |
| xiaoli    |    3 |    98 |
| xiaozhang |    3 |    98 |
| xiaohong  |    4 |    97 |
+-----------+------+-------+

解题方法：

第一步，排序

mysql> select name, score from t_rank order by score desc;
+-----------+-------+
| name      | score |
+-----------+-------+
| xiaoming  |   100 |
| xiaowang  |    99 |
| xiaozhang |    98 |
| xiaoli    |    98 |
| xiaohong  |    97 |
+-----------+-------+
5 rows in set (0.04 sec)

利用count就可以统计出排名

假设最高分为100分，则可以利用count加去重得出每个分数的排名。

比如统计分数大于等于100的有多少，结果为1，则表示排第1

mysql> select count(distinct score) from t_rank where score >= 100;
+-----------------------+
| count(distinct score) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.04 sec)

统计分数大于等于99的有多少，结果为2，则表示排第2

mysql> select count(distinct score) from t_rank where score >= 99;
+-----------------------+
| count(distinct score) |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.05 sec)

以此类推。。。

组合前2步，即可得到期望的结果

mysql> select score, (select count(distinct score) from t_rank a where a.score >= b.score) from t_rank b order by b.score desc;
+-------+---------------------------------------------------------------------+
| score | (select count(distinct score) from t_rank a where a.score>=b.score) |
+-------+---------------------------------------------------------------------+
|   100 |                                                                   1 |
|    99 |                                                                   2 |
|    98 |                                                                   3 |
|    98 |                                                                   3 |
|    97 |                                                                   4 |
+-------+---------------------------------------------------------------------+
5 rows in set (0.04 sec)