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 Logbin_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 消耗。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值