金三银四到了，给大家整理一些数据库必知必会的面试题。基础相关
1、关系型和非关系型数据库的区别？
关系型数据库的优点
非关系型数据库（NOSQL）的优点
2、详细说一下一条 MySQL 语句执行的步骤
索引相关
3、MySQL 使用索引的原因？
根本原因
4、索引的三种常见底层数据结构以及优缺点
三种常见的索引底层数据结构：分别是哈希表、有序数组和搜索树。
-
扩展（以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。）
5、索引的常见类型以及它是如何发挥作用的？
6、MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么？
7、InnoDB 为什么设计 B+ 树索引？
8、什么是覆盖索引和索引下推？
覆盖索引：
索引下推：
9、哪些操作会导致索引失效？
-
对索引使用左或者左右模糊匹配，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。原因在于查询的结果可能是多个，不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。
10、字符串加索引
日志相关
11、MySQL 的 change buffer 是什么？
-
当需要更新一个数据页时，如果数据页在内存中就直接更新；而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中。
-
这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
-
适用场景：
– 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
– 反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。
12、MySQL 是如何判断一行扫描数的？
13、MySQL 的 redo log 和 binlog 区别？
14、为什么需要 redo log？
-
其实是为了配合 MySQL 的 WAL 机制。因为 MySQL 进行更新操作，为了能够快速响应，所以采用了异步写回磁盘的技术，写入内存后就返回。但是这样，会存在 crash后 内存数据丢失的隐患，而 redo log 具备 crash safe 的能力。
15、为什么 redo log 具有 crash-safe 的能力，是 binlog 无法替代的？
第一点：redo log 可确保 innoDB 判断哪些数据已经刷盘，哪些数据还没有
-
redo log 和 binlog 有一个很大的区别就是，一个是循环写，一个是追加写。也就是说 redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志。
-
当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经刷盘，哪些数据还没有。
-
但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，因为是循环写！数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。
第二点：如果 redo log 写入失败，说明此次操作失败，事务也不可能提交
16、当数据库 crash 后，如何恢复未刷盘的数据到内存中？
根据 redo log 和 binlog 的两阶段提交，未持久化的数据分为几种情况：
-
change buffer 写入，redo log 虽然做了 fsync 但未 commit，binlog 未 fsync 到磁盘，这部分数据丢失。
-
change buffer 写入，redo log fsync 未 commit，binlog 已经 fsync 到磁盘，先从 binlog 恢复 redo log，再从 redo log 恢复 change buffer。
-
change buffer 写入，redo log 和 binlog 都已经 fsync，直接从 redo log 里恢复。
17、redo log 写入方式？
redo log包括两部分内容，分别是内存中的日志缓冲(redo log buffer)和磁盘上的日志文件(redo log file)。
MySQL 每执行一条 DML 语句，会先把记录写入 redo log buffer（用户空间） ，再保存到内核空间的缓冲区 OS-buffer 中，后续某个时间点再一次性将多个操作记录写到 redo log file（刷盘） 。这种先写日志，再写磁盘的技术，就是WAL。
可以发现，redo log buffer写入到redo log file，是经过OS buffer中转的。其实可以通过参数innodb_flush_log_at_trx_commit进行配置，参数值含义如下：
-
0：称为延迟写，事务提交时不会将redo log buffer中日志写入到OS buffer，而是每秒写入OS buffer并调用写入到redo log file中。
-
1：称为实时写，实时刷”，事务每次提交都会将redo log buffer中的日志写入OS buffer并保存到redo log file中。
18、redo log 的执行流程?
我们来看下Redo log的执行流程，假设执行的 SQL 如下：
update T set a =1 where id =666
-
MySQL 客户端将请求语句 update T set a =1 where id =666，发往 MySQL Server 层。
-
MySQL Server 层接收到 SQL 请求后，对其进行分析、优化、执行等处理工作，将生成的 SQL 执行计划发到 InnoDB 存储引擎层执行。
19、binlog 的概念是什么，起到什么作用， 可以保证 crash-safe 吗?
20、什么是两阶段提交？
MySQL 将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，这就是”两阶段提交”。
而两阶段提交就是让这两个状态保持逻辑上的一致。redolog 用于恢复主机故障时的未更新的物理数据，binlog 用于备份操作。两者本身就是两个独立的个体，要想保持一致，就必须使用分布式事务的解决方案来处理。
-
如果不用两阶段提交的话，可能会出现这样情况
-
先写 bin log，crash 后，由于 redo log 没写入，事务无效，所以后续 bin log 备份恢复时，数据不一致。
-
两阶段提交就是为了保证 redo log 和 binlog 数据的安全一致性。只有在这两个日志文件逻辑上高度一致了才能放心的使用。
在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。
21、MySQL 怎么知道 binlog 是完整的?
22、什么是 WAL 技术，有什么优点？
WAL，中文全称是 Write-Ahead Logging，它的关键点就是日志先写内存，再写磁盘。MySQL 执行更新操作后，在真正把数据写入到磁盘前，先记录日志。
好处是不用每一次操作都实时把数据写盘，就算 crash 后也可以通过redo log 恢复，所以能够实现快速响应 SQL 语句。
23、binlog 日志的三种格式
每一条会修改数据的 SQL 都会记录在 binlog 中
Row格式
-
优点：binlog 中可以不记录执行的 SQL 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或 function、或trigger的调用和触发无法被正确复制的问题。
-
缺点:可能会产生大量的日志内容。
实际上就是 Statement 与 Row 的结合。一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式。
24、redo log日志格式
redo log buffer (内存中)是由首尾相连的四个文件组成的，它们分别是：ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4。
-
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
-
如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
-
有了 redo log，当数据库发生宕机重启后，可通过 redo log将未落盘的数据（check point之后的数据）恢复，保证已经提交的事务记录不会丢失，这种能力称为crash-safe。
25、原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，原因是什么？如何解决？
-
确实是索引使用不当，没有走索引。
解决：
-
考虑修改语句，引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。
-
如果确定是索引根本没必要，可以考虑删除索引。
26、InnoDB 数据页结构
数据相关
27、MySQL 是如何保证数据不丢失的？
-
在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。
28、误删数据怎么办？
DBA 的最核心的工作就是保证数据的完整性，先要做好预防，预防的话大概是通过这几个点：
-
搭建延迟备库
-
做好备份。备份的话又分为两个点 (1)如果数据量比较大，用物理备份 xtrabackup。定期对数据库进行全量备份，也可以做增量备份。(2)如果数据量较少，用 mysqldump 或者 mysqldumper。再利用 binlog 来恢复或者搭建主从的方式来恢复数据。定期备份binlog 文件也是很有必要的
-
如果发生了数据删除的操作，又可以从以下几个点来恢复:
-
DML 误操作语句造成数据不完整或者丢失。可以通过 flashback，美团的 myflash，也是一个不错的工具，本质都差不多
-
都是先解析 binlog event，然后在进行反转。把 delete 反转为insert，insert 反转为 delete，update前后 image 对调。
-
所以必须设置binlog_format=row 和 binlog_row_image=full，切记恢复数据的时候，应该先恢复到临时的实例，然后在恢复回主库上。
-
DDL语句误操作(truncate和drop)，由于DDL语句不管 binlog_format 是 row 还是 statement ，在 binlog 里都只记录语句，不记录 image 所以恢复起来相对要麻烦得多。
29、drop、truncate 和 delete 的区别
-
DELETE 语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
-
TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
-
如果和事务有关，或者想触发 trigger，还是用 delete；如果是整理表内部的碎片，可以用 truncate 跟上 reuse stroage，再重新导入/插入数据。
30、在 MySQL 中有两个 kill 命令
31、如何理解 MySQL 的边读边发
-
内存的数据页都是在 Buffer_Pool中操作的。
-
InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法，使用链表实现，实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。
32、MySQL 的大表查询为什么不会爆内存？
-
由于 MySQL 是边读变发，因此对于数据量很大的查询结果来说，不会再 server 端保存完整的结果集，所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。
-
InnoDB 引擎内部，由于有淘汰策略，InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法，使用链表实现，实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。对冷数据的全扫描，影响也能做到可控制。
33、MySQL 临时表的用法和特性
34、MySQL 存储引擎介绍（InnoDB、MyISAM、MEMORY）
-
InnoDB 是事务型数据库的首选引擎，支持事务安全表 (ACID)，支持行锁定和外键。MySQL5.5.5 之后，InnoDB 作为默认存储引擎
-
MyISAM 基于 ISAM 的存储引擎，并对其进行扩展。它是在 Web、数据存储和其他应用环境下最常用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。在 MySQL5.5.5 之前的版本中，MyISAM 是默认存储引擎
35、都说 InnoDB 好，那还要不要使用 MEMORY 引擎？
36、如果数据库误操作, 如何执行数据恢复?
数据库在某个时候误操作，就可以找到距离误操作最近的时间节点的bin log，重放到临时数据库里，然后选择误删的数据节点，恢复到线上数据库。
主从备份相关
37、MySQL 是如何保证主备同步？
主备关系的建立：
-
一开始创建主备关系的时候，是由备库指定的，比如基于位点的主备关系，备库说“我要从binlog文件A的位置P”开始同步，主库就从这个指定的位置开始往后发。
-
而主备关系搭建之后，是主库决定要发给数据给备库的，所以主库有新的日志也会发给备库。
一个事务完整的同步过程：
-
在备库B上通过changemaster命令设置主库A的IP端口用户名密码以及从哪个位置开始请求binlog包括文件名和日志偏移量
-
在备库B上执行start–slave命令备库会启动两个线程：io_thread和sql_thread分别负责建立连接和读取中转日志进行解析执行
38、什么是主备延迟
主库和备库在执行同一个事务的时候出现时间差的问题，主要原因有：
39、为什么要有多线程复制策略？
40、MySQL 的并行策略有哪些？
-
按表分发策略：如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证两个 worker 不会更新同一行。缺点：如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。
-
按行分发策略：如果两个事务没有更新相同的行，它们在备库上可以并行。如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求 binlog 格式必须是 row。缺点：相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。
41、MySQL的一主一备和一主多从有什么区别？
在一主一备的双 M 架构里，主备切换只需要把客户端流量切到备库；而在一主多从架构里，主备切换除了要把客户端流量切到备库外，还需要把从库接到新主库上。
42、主库出问题如何解决?
-
基于位点的主备切换：存在找同步位点这个问题
-
MySQL 5.6 版本引入了 GTID，彻底解决了这个困难。那么，GTID 到底是什么意思，又是如何解决找同步位点这个问题呢？
-
GTID：全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识；它由两部分组成，格式是：GTID=server_uuid:gno
-
在基于 GTID 的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。因此，如果实例 B 需要的日志已经不存在，A’就拒绝把日志发给 B。
43、MySQL 读写分离涉及到过期读问题的几种解决方案?
44、MySQL的并发链接和并发查询有什么区别？
-
在执行show processlist的结果里，看到了几千个连接，指的是并发连接。而”当前正在执行”的语句，才是并发查询。
-
所以需要设置参数：innodb_thread_concurrency 用来限制线程数，当线程数达到该参数，InnoDB就会认为线程数用完了，会阻止其他语句进入引擎执行。
性能相关
45、短时间提高 MySQL 性能的方法
-
第一种方法：先处理掉那些占着连接但是不工作的线程。或者再考虑断开事务内空闲太久的连接。kill connection + id
-
第二种方法：减少连接过程的消耗：慢查询性能问题在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：索引没有设计好；SQL 语句没写好；MySQL 选错了索引（force index）。
46、为什么 MySQL 自增主键 ID 不连续？
-
事务回滚
-
深层次原因是：MySQL 不判断自增主键是否存在，从而减少加锁的时间范围和粒度，这样能保持更高的性能，确保自增主键不能回退，所以才有自增主键不连续。
-
自增主键怎么做到唯一性？自增值加1来通过自增锁控制并发。
47、InnoDB 为什么要用自增 ID 作为主键？
48、如何最快的复制一张表？
-
另一种方法是直接将结果导出成.csv 文件。MySQL 提供语法，用来将查询结果导出到服务端本地目录：select * from db1.t where a>900 into outfile ‘/server_tmp/t.csv‘;得到.csv 导出文件后，你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中：load data infile ‘/server_tmp/t.csv‘ into table db2.t;
-
物理拷贝：在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。
49、grant 和 flush privileges语句
-
grant语句会同时修改数据表和内存，判断权限的时候使用的内存数据，因此，规范使用是不需要加上 flush privileges 语句。
-
flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。
50、要不要使用分区表？
51、join 用法
-
如果需要 left join 的语义，就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断，必须都写在 on 里面
-
标准的 group by 语句，是需要在 select 部分加一个聚合函数，比如select a,count(*) from t group by a order by null;
52、MySQL 有哪些自增ID？各自场景是什么？
-
Xid 是由 server 层维护的。InnoDB 内部使用 Xid，就是为了能够在 InnoDB 事务和 server 之间做关联。但是，InnoDB 自己的 trx_id，是另外维护的。
-
thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。使用了insert_unique算法
53、Xid 在 MySQL 内部是怎么生成的呢？
MySQL 内部维护了一个全局变量 global_query_id，每次执行语句（包括select语句）的时候将它赋值给 Query_id，然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。
而 global_query_id 是一个纯内存变量，重启之后就清零了。所以你就知道了，在同一个数据库实例中，不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件，这就保证了，同一个 binlog 文件里，Xid 一定是惟一的。
锁相关
54、说一下 MySQL 的锁
-
MySQL 在 server 层 和 存储引擎层 都运用了大量的锁
-
MySQL server 层需要讲两种锁，第一种是MDL(metadata lock) 元数据锁，第二种则 Table Lock 表锁。
-
MDL 又名元数据锁，那么什么是元数据呢，任何描述数据库的内容就是元数据，比如我们的表结构、库结构等都是元数据。那为什么需要 MDL 呢？
-
InnoDB 有五种表级锁：IS（意向读锁）；IX（意向写锁）；S（读）；X（写）；AUTO-INC
-
自增主键的保障就是有 AUTO-INC 锁，是语句级别的：为表的某个列添加 AUTO_INCREMENT 属性，之后在插⼊记录时，可以不指定该列的值，系统会⾃动为它赋上单调递增的值。
-
InnoDB 4 种行级锁
-
GapLock：间隙锁解决幻读；前一次查询不存在的东西在下一次查询出现了，其实就是事务A中的两次查询之间事务B执行插入操作被事务A感知了
-
行锁和表锁的抉择
-
全表扫描用行级锁
-
55、什么是幻读？
值在同一个事务中，存在前后两次查询同一个范围的数据，第二次看到了第一次没有查询到的数据。
幻读出现的场景：
幻读带来的问题：
-
对行锁语义的破坏
-
破坏了数据一致性
解决：
其它为什么系列
56、为什么 MySQL 会抖一下？
57、为什么删除了表，表文件的大小还是没变？
58、count(*)实现方式以及各种 count 对比
-
对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
-
对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
-
对于 count(字段) 来说：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。
-
但是 count * 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
-
所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(※)，所以建议尽量使用 count(*)。
59、orderby 排序内部原理
-
如果数据量很大，内存中无法存下这么多，就会使用磁盘临时文件来辅助排序，称为外部排序；
-
外部排序，MySQL会分为好几份单独的临时文件来存放排序后的数据，一般是磁盘文件中进行归并，然后将这些文件合并成一个大文件；
60、如何高效的使用 MySQL 显式随机消息
-
得到id集后算出Y1、Y2、Y3对应的三个id 最后 select * from t where id in (id1, id2, id3) 这样扫描的行数应该是C+Ymax+3
mysql> select count(*) into @C from t; set @Y1 = floor(@C * rand()); set @Y2 = floor(@C * rand()); set @Y3 = floor(@C * rand()); Ymax = max(Y1,Y2,Y3) Ymin = min(Y1,Y2,Y3) select id from t limit Ymin，(Ymax - Ymin)
持续更新中。
参考：
-
https://www.nowcoder.com/discuss/744934?type=1&order=0&pos=25&page=1&ncTraceId=&channel=-1&source_id=discuss_tag_nctrack
今天的唠嗑就到这里了。
我是小贺，我们下期再见。
·················END·················
你好，我是 herongwei，一个精神小伙&鹅厂程序猿，热爱编程，热爱生活，热爱分享，在平凡的人生中追求一点不平凡，欢迎关注，一起加油，点击下方名片，了解更多。
原文地址:https://blog.csdn.net/u013050857/article/details/123244085
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.7code.cn/show_31291.html
如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！
