mysql复制技术 免费刷5000播放量

客服   dy抖音ks快手 自助商城点击进入

9、那事务ACID特性靠什么保证的呢？

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

C一致性一般由代码层面来保证

I隔离性由MVCC来保证

D持久性由内存+redo log来保证mysql复制技术，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

10、redo log，undo log，binlog的区别是什么？

(1)重做日志（redo log）作用

确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

(2)回滚日志（undo log）作用

确保事务的原子性。保存了事务发生之前的数据的一个版本，可以用于回滚mysql复制技术，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

(3)二进制日志（binlog）作用

用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。

用于数据库的基于时间点的还原。

11、 那你知道什么是覆盖索引和回表吗？

覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询。

而要确定一个查询是否是覆盖索引，我们只需要explain sql语句看Extra的结果是否是“Using index”就能够触发索引覆盖。

12、聚集索引与非聚集索引的区别

可以按以下四个维度回答：

（1）一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。

（2）如果表定义了PK，则PK就是聚集索引；如果表没有定义PK，则第一个not NULL unique列是聚集索引；否则，InnoDB会创建一个隐藏的row-id作为聚集索引

（3）我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点不存放具体的整行数据（叶子结点不直接指向数据页），而是存储的这一行的主键的值。

（4）非聚集索引需要回表查询，先定位主键值，再定位行记录，因为要扫描两遍索引树，它的性能较扫一遍索引树更低。

13、为什么要用 B+ 树，为什么不用普通二叉树？

可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数，为什么不是普通二叉树，为什么不是平衡二叉树，为什么不是B树，而偏偏是 B+ 树呢？

（1）为什么不是普通二叉树？

如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

（2）为什么不是平衡二叉树呢？

我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

（3）为什么不是 B 树而是 B+ 树呢？

B+ 树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

14、锁的类型有哪些呢？说说数据库的乐观锁和悲观锁是什么以及它们的区别？MVCC 熟悉吗，知道它的底层原理？

（1）锁的类型有哪些呢？

mysql锁分为共享锁和排他锁，也叫做读锁和写锁。

读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。

写锁是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和行锁两种。

表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表。

行锁又可以分为乐观锁和悲观锁，悲观锁可以通过for update实现，乐观锁则通过版本号实现。

（2) 说说数据库的乐观锁和悲观锁是什么以及它们的区别？

悲观锁：

悲观锁她专一且缺乏安全感了，她的心只属于当前事务，每时每刻都担心着它心爱的数据可能被别的事务修改，所以一个事务拥有（获得）悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待锁被释放才可以执行。

乐观锁：

乐观锁的“乐观情绪”体现在，它认为数据的变动不会太频繁。因此，它允许多个事务同时对数据进行变动。

实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

(3) MVCC 熟悉吗，知道它的底层原理？

MVCC (Multiversion Concurrency Control)，即多版本并发控制技术。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

15 、 那你说说什么是幻读，什么是MVCC？

要说幻读，首先要了解MVCC，MVCC叫做多版本并发控制，实际上就是保存了数据在某个时间节点的快照。

我们每行数实际上隐藏了两列，创建时间版本号，过期(删除)时间版本号，每开始一个新的事务，版本号都会自动递增。

还是拿上面的user表举例子，假设我们插入两条数据，他们实际上应该长这样。

这时候假设小明去执行查询，此时current_version=3

select * from user where id<=3;

同时，小红在这时候开启事务去修改id=1的记录，current_version=4

update user set name='张三三' where id=1;

执行成功后的结果是这样的

如果这时候还有小黑在删除id=2的数据，current_version=5，执行后结果是这样的。

由于MVCC的原理是查找创建版本小于或等于当前事务版本，删除版本为空或者大于当前事务版本，小明的真实的查询应该是这样

select * from user where id<=3 and create_version<=3 and (delete_version>3 or delete_version is null);

所以小明最后查询到的id=1的名字还是'张三'，并且id=2的记录也能查询到。这样做是为了保证事务读取的数据是在事务开始前就已经存在的，要么是事务自己插入或者修改的。

明白MVCC原理，我们来说什么是幻读就简单多了。举一个常见的场景，用户注册时，我们先查询用户名是否存在，不存在就插入，假定用户名是唯一索引。

小明开启事务current_version=6查询名字为'王五'的记录，发现不存在。

小红开启事务current_version=7插入一条数据，结果是这样：

小明执行插入名字'王五'的记录，发现唯一索引冲突，无法插入，这就是幻读。

16、那你知道什么是间隙锁吗？

间隙锁是可重复读级别下才会有的锁，结合MVCC和间隙锁可以解决幻读的问题。我们还是以user举例，假设现在user表有几条记录

当我们执行：

begin;select * from user where age=20 for update;begin;insert into user(age) values(10); #成功insert into user(age) values(11); #失败insert into user(age) values(20); #失败insert into user(age) values(21); #失败insert into user(age) values(30); #失败

只有10可以插入成功，那么因为表的间隙mysql自动帮我们生成了区间(左开右闭)

(negative infinity，10],(10,20],(20,30],(30,positive infinity)

由于20存在记录，所以(10,20]，(20,30]区间都被锁定了无法插入、删除。

如果查询21呢？就会根据21定位到(20,30)的区间(都是开区间)。

需要注意的是唯一索引是不会有间隙索引的。

17、说说mysql主从同步怎么做的吧

首先先了解mysql主从同步的原理

master提交完事务后，写入binlog

slave连接到master，获取binlog

master创建dump线程，推送binglog到slave

slave启动一个IO线程读取同步过来的master的binlog，记录到relay log中继日志中

slave再开启一个sql线程读取relay log事件并在slave执行，完成同步

slave记录自己的binglog

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制

主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

18、日常工作中你是怎么优化SQL的？

可以从这几个维度回答这个问题：

(1) 优化表结构

（1）尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

（2）尽可能的使用 varchar 代替 char

变长字段存储空间小，可以节省存储空间。

（3）当索引列大量重复数据时，可以把索引删除掉

比如有一列是性别，几乎只有男、女、未知，这样的索引是无效的。

(2) 优化查询

(3) 索引优化

19、关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

我们平时写Sql时，都要养成用explain分析的习惯。慢查询的统计，运维会定期统计给我们

优化慢查询思路：

20、如果让你做分库与分表的设计，简单说说你会怎么做？

分库分表方案:

常用的分库分表中间件：

分库分表可能遇到的问题

21、你们数据量级多大？分库分表怎么做的？

首先分库分表分为垂直和水平两个方式，一般来说我们拆分的顺序是先垂直后水平。

垂直分库

基于现在微服务拆分来说，都是已经做到了垂直分库了

垂直分表

如果表字段比较多，将不常用的、数据较大的等等做拆分

水平分表

首先根据业务场景来决定使用什么字段作为分表字段(sharding_key)，比如我们现在日订单1000万，我们大部分的场景来源于C端，我们可以用user_id作为sharding_key，数据查询支持到最近3个月的订单，超过3个月的做归档处理，那么3个月的数据量就是9亿，可以分1024张表，那么每张表的数据大概就在100万左右。

比如用户id为100，那我们都经过hash(100)，然后对1024取模，就可以落到对应的表上了。

22、那分表后的ID怎么保证唯一性的呢？

因为我们主键默认都是自增的，那么分表之后的主键在不同表就肯定会有冲突了。有几个办法考虑：

设定步长，比如1-1024张表我们设定1024的基础步长，这样主键落到不同的表就不会冲突了。

分布式ID，自己实现一套分布式ID生成算法或者使用开源的比如雪花算法这种

分表后不使用主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表订单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样。

23、MySQL数据库cpu飙升的话，要怎么处理呢？

排查过程：

（1）使用top 命令观察，确定是mysqld导致还是其他原因。（2）如果是mysqld导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。（3）找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：

（1）kill 掉这些线程(同时观察 cpu 使用率是否下降)， （2）进行相应的调整(比如说加索引、改 sql、改内存参数) （3）重新跑这些 SQL。

其他情况：

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

24、MySQL 遇到过死锁问题吗，你是如何解决的？（重点去了解一下，好跟面试官造火箭）

遇到过。我排查死锁的一般步骤是酱紫的：

（1）查看死锁日志 show engine innodb status;

（2）找出死锁Sql

（3）分析sql加锁情况

（4）模拟死锁案发

（5）分析死锁日志

（6）分析死锁结果

参考链接：

,