Mysql 相关概念

1、一条更新语句在 MySQL 中是怎样处理的？

更新内存和redo log，MySQL 将寻找机会将 redo log 刷新到磁盘上。

2、什么是 MySQL 的脏页？

当内存数据页和磁盘数据页不一致的时候，我们称这个数据页为脏页，当内存数据页刷新到磁盘后，这个也叫做干净页。

2、MySQL什么时候会刷 redo log 的脏页到磁盘上呢？

四种情况：

1. redo log 满了，先暂停所有更新操作，redo log 将checkpiont向前推进一段，然后刷新脏页。

   2、内存不足，当申请新的数据页的时候，剩余内存已经不够分配新的数据页了，这时就要淘汰一部分数据页，如果淘汰的是脏页，还要刷盘。

   3、MySQL自行判定空闲时间。

   4、MySQL关闭之前，刷新全部脏页。

3、MySQL5.6 以后的 online DDL原理？

在线重新构建一张新表，在这个过程中，所有写的记录都会存在一个临时的 row log 中，新表构建完成后，将 row log 写进新表。

4、Innodb 引擎 count 内部原理？

先说结论，count(字段)<count(主键 id)<count(1)≈count(*)

count(字段)：最慢，查出所有行的这个字段，判断是否是 not null，如果是 not null，那么直接累加，如果字段允许为 null，还要再遍历一次取出所有部位 null的字段，然后累加。

count(主键 id)：取出所有主键 id 返回给 server 层，然后累加。

count(1)：取出一行，就记一个1，然后累加。

count(*)：MySQL单独优化过，性能最优。

简单来说就是，你 count 后面的括号里面输入什么，innodb 引擎就给你返回什么，而所有种类中，只有 count(*) MySQL 的优化器优化过，其他都没有单独处理。

5、什么是覆盖索引？

覆盖索引就是指索引本身的数据，就可以满足查询，不需要回到主键索引上面取数据了。

6、MySQL 排序原理？

1）初始化排序的内存，确认要查询的字段。

2）根据 SQL 匹配的索引获取主键id，根据主键id去主键索引中寻找这一整条数据，得到需要的字段的值。

3）重复第二步，获取所有需要的数据在缓存中。

4）将所有列的数据在内存中执行快速排序。

5）取出 sql 中标明的数据条数。

当所需要排序的字段所占空间大于 MySQL 为每一条线程设置的阈值 sort_buffer_size 时，会采用 filesort，将基于临时文件使用归并排序对所有数据进行排序。

7、当 MySQL 单行数据过大，可能会导致 filesort，排序会导致使用临时文件进行归并排序，如果提高效率？

当单行数据总长度大于 max_length_for_sort_data 时，会将排序字段和主键放在内存中，而不是查询所需要的字段，但是这样虽然节省了内存，在排序后，还需要使用一次主键索引查询 sql 需要的数据，节省了内存。

8、如何给有排序需求的 sql 创建索引？

如果字段不是特别多的话，可以给 where 条件中的 条件和 order by 中的字段加上联合索引，这样可以走覆盖索引，减少回表次数。

9、Innodb 引擎，如果表中没有设置主键，或者是删除掉主键，是不是就无法走主键索引了？

不是，如果没有主键的话，Innodb 会自动生成一个6字节的 rowid。

10、MySQL 的隐式转换问题

当表中字段的类型是字符型，而 sql 中该字段在 where 中是数字时，会触发隐式转换，MySQL 会将表中的字段转换为数字再与其进行匹配，这些操作是不走索引的，全表扫描。

11、普通索引和唯一索引应该如何选择？

对唯一约束有要求的话，只能选唯一索引了，如果对唯一约束没有要求的话，最好还是选择普通索引，唯一约束是有成本的，对于 where xxx=“yyy” 这样的查询来说，普通索引查询到数据后，还会向后检索，而唯一索引的字段查询后会直接返回的，性能差异微乎其微。更新的情况下，普通索引字段更新效率是高于唯一索引的，普通索引更新时会更新在 change buffer 中，是一种内存中的更新缓存，这些缓存会定时和数据页合并，这个缓存也是会持久化的，，而唯一索引字段的更新，是需要去磁盘中查询的，因为需要知道更新后的字段会不会违反唯一约束，更新效率显然不如普通索引，MySQL 整体的内存命中率也会降低。

12、读写分离时，主从数据不一致的问题如何解决？

1）对于刚写入就立刻需要读的场景，可以直接走主库。

2）可以走从库，但是读之前可以sleep一下，主从数据延迟通常情况下都是1s内。

3）开启半同步复制，主库在事务提交时，给从库同步 bin log，从库返回ack，主库才给客户端返回事务完成。

13、undo 日志文件：记录数据被修改前的样子
准备更新一条语句的时候，该条语句已经被加载到 Buffer pool 中了，实际上这里还有这样的操作，就是在将该条语句加载到 Buffer Pool 中的时候同时会往 undo 日志文件中插入一条日志，也就是将 id=1 的这条记录的原来的值记录下来。

14 MySQL 查询优化器
查询优化器内部具体怎么实现的我们不需要是关心，我需要知道的是 MySQL 会帮我去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划，比如你创建了多个索引，MySQL 会依据成本最小原则来选择使用对应的索引，这里的成本主要包括两个方面, IO 成本和 CPU 成本

IO 成本: 即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关

CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。

MySQL 优化器 会计算 「IO 成本 + CPU」 成本最小的那个索引来执行

15 bin log 日志文件：记录整个操作过程
redo log是 InnoDB 存储引擎特有的日志文件，而bin log属于是 MySQL 级别的日志。redo log记录的东西是偏向于物理性质的，如：“对什么数据，做了什么修改”。bin log是偏向于逻辑性质的，类似于：“对 students 表中的 id 为 1 的记录做了更新操作” 两者的主要特点总结如下:

性质	redo Log	bin_log
文件大小	redo log 的大小是固定的（配置中也可以设置，一般默认的就足够了）	bin log 可通过配置参数max_bin log_size设置每个bin log文件的大小（但是一般不建议修改）
实现方式	redo log是InnoDB引擎层实现的（也就是说是 Innodb 存储引起过独有的）	bin log是 MySQL 层实现的，所有引擎都可以使用 bin log日志
记录方式	redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。	bin log 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上
使用场景	redo log适用于崩溃恢复(crash-safe)（这一点其实非常类似与 Redis 的持久化特征）	bin log 适用于主从复制和数据恢复

16 bin log文件是如何刷入磁盘的?

bin log 的刷盘是有相关的策略的，策略可以通过sync_bin log来修改，默认为 0，表示先写入 os cache，也就是说在提交事务的时候，数据不会直接到磁盘中，这样如果宕机bin log数据仍然会丢失。所以建议将sync_bin log设置为 1 表示直接将数据写入到磁盘文件中。

17 redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

1. 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
2. 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务

18 MVCC如何防止READ COMMITTED 已提交读和REPEATABLE READ：可重复读。
READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。
19 普通索引和唯一索引，应该怎么选择？
查询过程
假设，执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

更新过程
先介绍一下change buffer

是可以持久化的数据，在内存中有拷贝，也会被写入到磁盘上。

当需要更新一个数据页时，如果数据页在内存中就直接更新，

而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数
innodb_change_buffer_max_size 来动态设置。

什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。判断唯一的时候就需要将数据页读入内存，所以没有必要使用change buffer。

实际上也只有普通索引可以使用。

如果要在这张表中插入一个新记录的话，InnoDB 的处理流程是怎样的？

第一种情况是，这个记录要更新的目标页在内存中。

普通索引和唯一索引对更新语句性能影响的差别，只是一个判断冲突，只会耗费微小的CPU 时间。

第二种情况是，这个记录要更新的目标页不在内存中。

这时，InnoDB 的处理流程如下：

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer 的使用场景
写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。

这种业务模型常见的就是账单类、日志类的系统。

更新模式是写入之后马上会做查询，

那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 purge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。

20 change buffer 和 redo log
redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），

而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。