• 周五. 9月 30th, 2022

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

测试面试题集-MySQL数据库灵魂拷问(11)

admin

11月 28, 2021

1、什么是事务?

事务是数据库操作的最小工作单元,是作为单个逻辑工作单元执行的一系列操作,这些操作作为一个整体一起向系统提交,要么都执行、要么都不执行,是一组不可再分割的操作集合。

通俗理解就是做一件事情的过程,事务封装了一条dml、或者多条dml语句。这个过程有两种结果:要么全部成功、要么全部失败。

 

2、什么是事务的ACID特性?

A=Atomicity ,原子性:事务是数据库最小逻辑单位。事务中包含的各项操作在一次执行过程中,只允许出现两种状态之一,要么全部执行成功 ,要么全部执行失败。任何一项操作都会导致整个事务的失败,同时其它已经被执行的操作都将被撤销并回滚,只有所有的操作全部成功,整个事务才算是成功完成。

C=Consistency ,一致性:系统总是从一个一致性的状态转移到另一个一致性的状态。例如从 A 账户转账到 B 账户,不能因为 A 账户扣了钱,而 B 账户没有加钱,无论 A 和 B 怎么转账,系统中总额是固定的。如果数据库系统运行中发生故障,有些事务尚未完成就被迫中断,这些未完成事务对数据库所做的修改有一部分已写入物理数据库,这时数据库就处于一种不正确的状态,或者说是不一致的状态。

I=Isolation ,隔离性: 通常来说一个事务在完全提交之前,对其他事务是不可见的。也就是说,不同的事务并发操作相同的数据时,每个事务都有各自完整的数据空间。一个事务内部的操作及使用的数据对其它并发事务是隔离的,并发执行的各个事务是不能互相干扰的。

D=Durability ,持久性:事务一旦提交,将永久存在,接下来的其它操作或故障不应该对其执行结果有任何影响。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动,那么一定能够将其恢复到事务成功结束后的状态。

3、事务的隔离级别与对应的问题?

MySQL 的隔离等级对加锁有影响,所以在分析具体加锁场景时,首先要确定当前的隔离等级,分为以下几个等级:

读未提交(Read Uncommitted ,简称 RU):可以读到未提交的读,基本上不会使用该隔离等级,所以暂时忽略。

读已提交(Read Committed ,简称 RC):存在幻读问题,对当前读获取的数据加记录锁。

可重复读(Repeatable Read ,简称 RR):不存在幻读问题,对当前读获取的数据加记录锁,同时对涉及的范围加间隙锁,防止新的数据插入,导致幻读。

序列化(Serializable):从 MVCC 并发控制退化到基于锁的并发控制,不存在快照读,都是当前读,并发效率急剧下降,不建议使用。

隔离级别与对应问题矩阵如下所示:

隔离级别 脏读 不可重复读 幻读
读未提交
不可重复读
可重复读 是(MySQL否)
串行化

注,常见数据库的默认级别:

  • MySQL 数据库的默认隔离级别是 Repeatable read (可重复读)级别。

  • Oracle数据库中,只支持 Seralizable(顺序读) 和 Read committed(读已提交)级别,默认的是 Read committed 级别。

  • SQL Server 数据库中,默认的是 Read committed(读已提交) 级别。

4、MySQL是如何解决幻读的?

事务的隔离级别有4种:读未提交、读已提交、可重复读、串行化,关于在MySQL中InnoDB引擎是如何解决幻读,一张图甚过千言万语:

图片

综上,高并发数据库系统中,为保证事务与事务之间隔离性和数据一致性,MySQL InnoDB引擎默认是RR的隔离级别,在MySQL 中通过MVCC快照读和next-key(当前读)两种模式解决幻读问题。

5、MySQL常见死锁场景有哪些?

  • 2个事务交叉:同一个事务中出现delete,insert操作;

  • 3个insert事务,一个回滚:三个事务的 insert 语句都是insert ignore into t1(a, b)values(“1”, “1”);

  • 个事务,间隙锁造成死锁:同一个事务中多个update操作导致锁升级(行锁升级为表锁),并发操作时会导致死锁;

 

解决方式:事务拆分,同一个事物中不要出现锁升级,如果业务需求确实导致有表锁的出现,直接使用悲观锁。

6、drop、delete与truncate的区别?

drop:drop是DDL,会隐式提交,所以,不能回滚,不会触发触发器;drop语句删除表结构及所有数据,并将表所占用的空间全部释放,底层系统文件会变小;drop语句将删除表的结构所依赖的约束,触发器,索引,依赖于该表的存储过程/函数将保留,但是变为invalid状态。

truncate:truncate是DDL,会隐式提交,所以,不能回滚,不会触发触发器;truncate会删除表空间,底层系统文件会变小。并且将重新设置高水线和所有的索引,缺省情况下将空间释放到minextents个extent,除非使用reuse storage。不会记录日志,所以执行速度很快,但不能通过rollback撤消操作,如果一不小心把一个表truncate掉,也是可以恢复的,只是不能通过rollback来恢复;对于外键(foreignkey )约束引用的表,不能使用 truncate table,而应使用不带 where 子句的 delete 语句;truncatetable不能用于参与了索引视图的表。

delete:delete是DML,执行delete操作时,每次从表中删除一行,并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚(rollback)和重做操作,但要注意表空间要足够大,需要手动提交(commit)操作才能生效,可以通过rollback撤消操作;delete可根据条件删除表中满足条件的数据,如果不指定where子句,那么删除表中所有记录,只删表数据,删除操作后,底层系统文件不会变小;delete语句不影响表所占用的extent,高水线(high watermark)保持原位置不变。

总结:

  • 在速度上,一般来说,drop> truncate > delete。

  • 在使用drop和truncate时一定要注意,虽然可以恢复,但为了减少麻烦,还是要慎重。

  • 如果想删除部分数据用delete,注意带上where子句,回滚段要足够大;如果想删除表,用drop; 如果想保留表而将所有数据删除,如果和事务无关,用truncate即可;如果和事务有关,或者想触发trigger,还是用delete; 如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。


7、谈谈对索引的理解?

  • 索引大大减小了服务器需要扫描的数据量;

  • 索引可以帮助服务器避免排序和临时表;

  • 索引可以将随机IO变成顺序IO


缺点:创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加;索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大;当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

8、哪些情况可能无法使用上索引?

  • 类型转换:当存在索引列的数据类型隐形转换,则用不上索引,比如列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引;

  • 索引列加函数:加了函数无法使用上索引;

  • 字符校对规则不对;

9、一个查询语句只查询到一条记录,但是总在扫描数据库,试分析原因?

  • 没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷);

  • 没有创建计算列导致查询不优化;

  • 查询出的数据量过大(可以采用多次查询或其他方法降低数据量);

  • 查询语句需要优化;

10、若客户反馈系统慢,如何查找问题?

第一步:查询应用服务器,数据库服务器 CPU使用率,CPU负载,带宽,内存;

第二步:一般是 CPU 过高,且是mysql进程,则进入数据库,首先查询活跃线程数,查询正在执行的sql,顺便也去慢查询日志文件;
第三步:找到问题sql,分析sql,通过explain分析具体问题(一般都是数据库有大量计算操作,大量数据查询返回没有做分页处理);
第四步:检查是否为网络问题。

11、MySQL是如何实现 ACID 特性的?

A=原子性:undo log来保证原子性,异常或执行失败后进行回滚;

C=一致性:事务的最终目的,即需要数据库层面保证,又需要应用层面进行保证;

I=隔离性:事务间的读写靠MySQL的锁机制来保证隔离,事务间的写操作靠MVCC机制(快照读、当前读)来保证隔离性;

D=持久性:redo log和binlog来保证持久性,确保当MySQL宕机或停电后,可以通过redo log最终将数据保存至磁盘中;

12、MySQL如何做大表DDL?

MySQL 5.7 已经支持原生在线DDL语句,但是涉及到一些参数配置,并可能不知道配置多少合适,所以一般大表还是使用percona-tools。

13、MySQL主从复制的原理和好处?

主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式,这样从节点不用一直访问主服务器来更新自己的数据,数据的更新可以在远程连接上进行,从节点可以复制主数据库中的所有数据库或者特定的数据库,或者特定的表。

MySQL主从复制的好处

  • 在业务复杂的系统中,假如有一句sql语句需要锁表,导致暂时不能使用读的服务,那么就很影响运行中的业务,使用主从复制,让主库负责写,从库负责读,这样,即使主库出现了锁表的情景,通过读从库也可以保证业务的正常运作。

  • 做数据的热备。

  • 有利于架构的扩展。业务量越来越大,I/O访问频率过高,单机无法满足,此时做多库的存储,降低磁盘I/O访问的频率,提高单个机器的I/O性能。

MySQL主从复制的原理

从库IO线程请求主库的binlog,主库binlog dump线程写binlog,推送给从库,从库IO 线程接受binlog并把写入本地的relay log, 从库SQL线程回访relay log中的内容。 

图片

14、MySQL内部的线程,各自负责什么工作?

  • master主线程:innodb1.2 版本 负责插入缓冲;

  • purge线程:负责undo页回收;

  • page clean线程:负责脏页刷新;

  • redo log线程:将日志缓存的内容刷新到redo log文件中;

  • change buffer线程:将插入缓冲中的内容刷新到磁盘;

  • purge线程:删除无用的undo页;

  • error monitor线程:负责数据库报错的监控线程;

  • lock monitor线程:负责锁的监控线程;

 

15、Explain执行计划中要关注哪些要素?

  • type:查询表联接类型,从这里可以看到本次查询大概的效率;

  • key:最终选择的索引,如果没有索引的话,本次查询效率通常很差;

  • key_len:实际用上的索引长度(很多情况下,索引不一定会全部使用上,通过长度判断);

  • rows:预计需要扫描的记录数,预计需要扫描的记录数越小越好;

  • extra:额外附加信息,主要确认是否出现 Using filesort、Using temporary 类似情况;

16、MySQL主从复制延迟原因与表现?

主从复制延迟的原因

一个服务器开放N个链接给客户端来连接,这样有会有大并发的更新操作, 但是从服务器的里面读取binlog 的线程仅有一个,当某个SQL在从服务器上执行的时间稍长或者由于某个SQL要进行锁表就会导致,主服务器的SQL大量积压,未被同步到从服务器里。这就导致了主从不一致, 即主从延迟。MySQL提供了从服务器状态命令,可以通过 show slave status 进行查看,比如可以查看Seconds_Behind_Master参数的值来判断,是否有发生主从延时。

主从复制延迟的表现

  • 网络延迟:Read_Master_Log_Pos 变化非常慢,Seconds_Behind_Master逐步加大;

  • 大表DDL:Read_Master_Log_Pos不变,status 显示正在alter 表,Seconds_Behind_Master逐步加大;

  • 大事务:Read_Master_Log_Pos;

17、MySQL备份原理?

  • 首先记录开始的LSN(全备的话就是从0开始,增备的话从指定路径,或者从表中获取)并启动一个xtrabackup_log后台检测的 fork进程,实时检测mysql redo的变化,一旦发现redo有新的日志写入,立刻将日志写入到日志文件xtrabackup_log中;

  •  复制innodb的数据文件和系统表空间文件idbdata1到对应的以默认时间戳为备份目录的地方(流式备份就没有这个目录咯);

  •  复制结束后,执行flush table with read lock操作(5.7以及之前) 8.0使用备份锁,所以percona工具xtrabackup8.0只能备份mysql8.0;

  •  复制.frm .myd .myi文件,并在这一时刻获得binary log 的位置;

  •  将表进行解锁unlock tables (8.0 使用 UNLOCK INSTANCE);

  •  停止xtrabackup_log进程;

18、MySQL锁到底锁住了什么?

MySQL中的锁机制是为了解决共享资源并发访问的问题,从不同程度控制资源的读写,以保证数据库的完整性和一致性,MySQL同时锁住了主键与辅助索引。

19、谈谈MySQL锁超时,以及如何避免死锁?

MySQL锁超时排查:

  • 查出的线程杀死:

    kill SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;
  • 设置锁的超时时间:Innodb 行锁的等待时间(单位秒)。可在会话级别设置,RDS 实例该参数的默认值为 50(秒)。生产环境不推荐使用过大的 innodb_lock_wait_timeout参数值,该参数支持在会话级别修改,方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间,如下:

    set innodb_lock_wait_timeout=1000; 

    设置当前会话 Innodb 行锁等待超时时间,单位秒。 

MySQL避免死锁:

  • 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。

  • 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;

  • 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率;

20、 MySQL锁的优化策略?

  • 读写分离;

  • 分段加锁;

  • 减少锁持有的时间;

  • 多个线程尽量以相同的顺序去获取资源;

21、MySQL有哪些日志,请简要介绍

  • 重做日志(redo log):确保事务的持久性。redo日志记录事务执行后的状态,用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点,尚有脏页未写入磁盘,在重启mysql服务的时候,根据redo log进行重做,从而达到事务的持久性这一特性。

  • 回滚日志(undo log):保证数据的原子性,保存了事务发生之前的数据的一个版本,可以用于回滚,同时可以提供多版本并发控制下的读(MVCC),也即非锁定读。

  • 二进制日志(binlog):用于复制,在主从复制中,从库利用主库上的binlog进行重播,实现主从同步。

  • 错误日志(errorlog):记录mysql服务的启停时正确和错误的信息,还记录启动、停止、运行过程中的错误信息。在默认情况下,系统记录错误日志的功能是关闭的,错误信息被输出到标准错误输出。

  • 慢查询日志(slow query log):记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。报错select、update、delete以及insert语句,慢日志只会记录执行成功的语句。

  • 一般查询日志(general log):记录了服务器接收到的每一个查询或是命令,无论这些查询或是命令是否正确甚至是否包含语法错误,general log 都会将其记录下来 ,记录的格式为 {Time ,Id ,Command,Argument }。也正因为mysql服务器需要不断地记录日志,开启General log会产生不小的系统开销。因此,Mysql默认是把General log关闭的。

  • 中继日志(relay log):主从复制时使用的日志。

22、重做日志和二进制日志的区别?

  • 涉及存储引擎不一样:binlog记录的是所有存储引擎的操作记录 redo log只记录innodb存储引擎的日志;

  • 记录内容不一样:binlog记录的是关于一个事务的具体操作内容。为逻辑日志 而redo log记录的是每个页更改的物理情况;

  • 写的时间不一样:binlog文件仅在事务提交前进行提交,即只写磁盘一次 而在事务进行过程中,却不断有重做日志条目被写入到重做日志文件中;

23、重做日志和二进制日志如何保证一致性?

MySQL通过两阶段提交(内部XA的两阶段提交)很好地解决了这一问题:

  • 第一阶段:协调者向所有参与者发送事务内容,询问是否可以执行事务提交操作,并开始等待各参与者响应。事务操作,资源管理器此时会将undo日志和redo日志计入事务日志中。如果参与者成功执行了事务操作,那么就反馈给协调者Yes响应,表示事务可以执行;如果参与者没有成功执行事务,那么就反馈给协调者No响应,表示事务不可以执行。

  • 第二阶段:提交或者中断事务。

24、MySQL同步,5.6、5.7 半同步的区别?

  • 5.6在半同步的时候,采用的是After Commit策略。即在主库上commit了之后,等待从库返回确认。

  • 5.7在半同步的时候,采用的是AFTER_SYNC,先等待从库返回确认,然后主库在提交。

25、MySQL常见存储引擎及各自特点?

  • InnoDB 支持事务、行级锁、支持外键约束,主要面向OLTP的应用,使用next-key locking 的策略来避免幻读现象的产生。

  • MyISAM 不支持事务、表锁设计、支持全文索引、读写互相阻塞、不支持外键约束;主要面向OLAP应用场景;缓存池只缓存索引文件,不缓存数据文件。

  • Memory 将所有数据保存在RAM中,在需要快速查找引用和其他类似数据的环境下,可提供极快的访问。如果数据库重启或者奔溃,数据都将丢失。

  • TokuDB 支持事务、高压缩、告诉读写、基于稀疏树索引设计;支持大多数在线修改索引、添加字段。

  • Inforbright/infinidb 列式存储、高压缩、单列查询快。 

发表回复

您的电子邮箱地址不会被公开。