1、关系型和非关系型数据库的区别？

关系型数据库的优点

• 容易理解，因为它采用了关系模型来组织数据。

• 可以保持数据的一致性。

• 数据更新的开销比较小。

• 支持复杂查询（带 where 子句的查询）

非关系型数据库（NOSQL）的优点

• 无需经过 SQL 层的解析，读写效率高。

• 基于键值对，读写性能很高，易于扩展

• 可以支持多种类型数据的存储，如图片，文档等等。

• 扩展（可分为内存性数据库以及文档型数据库，比如 Redis，MongoDB，HBase 等，适合场景：数据量大高可用的日志系统/地理位置存储系统）。

2、详细说一下一条 MySQL 语句执行的步骤

Server 层按顺序执行 SQL 的步骤为：

• 客户端请求 -> 连接器（验证用户身份，给予权限）

• 查询缓存（存在缓存则直接返回，不存在则执行后续操作）

• 分析器（对 SQL 进行词法分析和语法分析操作）

• 优化器（主要对执行的 SQL 优化选择最优的执行方案方法）

• 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）-> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

索引相关

3、MySQL 使用索引的原因？

根本原因

• 索引的出现，就是为了提高数据查询的效率，就像书的目录一样。

• 对于数据库的表而言，索引其实就是它的“目录”。

扩展

• 创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

• 帮助引擎层避免排序和临时表。

• 将随机 IO 变为顺序 IO，加速表和表之间的连接。

4、索引的三种常见底层数据结构以及优缺点

三种常见的索引底层数据结构：分别是哈希表、有序数组和搜索树。

• 哈希表这种适用于等值查询的场景，比如 memcached 以及其它一些 NoSQL 引擎，不适合范围查询。

• 有序数组索引只适用于静态存储引擎，等值和范围查询性能好，但更新数据成本高。

• N 叉树由于读写上的性能优点以及适配磁盘访问模式以及广泛应用在数据库引擎中。

• 扩展（以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。）

5、索引的常见类型以及它是如何发挥作用的？

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

• 主键索引的叶子节点存的整行数据，在InnoDB里也被称为聚簇索引。

• 非主键索引叶子节点存的主键的值，在InnoDB里也被称为二级索引。

6、MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么？

• InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身，其数据文件本身就是索引文件。

• MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址，叶节点的 data 域存放的是数据记录的地址，索引文件和数据文件是分离的。

7、InnoDB 为什么设计 B+ 树索引？

两个考虑因素：

• InnoDB 需要执行的场景和功能需要在特定查询上拥有较强的性能。

• CPU 将磁盘上的数据加载到内存中需要花费大量时间。

为什么选择 B+ 树：

• 哈希索引虽然能提供O（1）复杂度查询，但对范围查询和排序却无法很好的支持，最终会导致全表扫描。

• B 树能够在非叶子节点存储数据，但会导致在查询连续数据可能带来更多的随机 IO。

• 而 B+ 树的所有叶节点可以通过指针来相互连接，减少顺序遍历带来的随机 IO。

• 普通索引还是唯一索引？

由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发建议你优先考虑非唯一索引。

8、什么是覆盖索引和索引下推？

覆盖索引：

• 在某个查询里面，索引 k 已经“覆盖了”我们的查询需求，称为覆盖索引。

• 覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

索引下推：

• MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

9、哪些操作会导致索引失效？

• 对索引使用左或者左右模糊匹配，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。原因在于查询的结果可能是多个，不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。

• 对索引进行函数/对索引进行表达式计算，因为索引保持的是索引字段的原始值，而不是经过函数计算的值，自然就没办法走索引。

• 对索引进行隐式转换相当于使用了新函数。

• WHERE 子句中的 OR语句，只要有条件列不是索引列，就会进行全表扫描。

10、字符串加索引

• 直接创建完整索引，这样可能会比较占用空间。

• 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。

• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。

• 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

日志相关

11、MySQL 的 change buffer 是什么？

• 当需要更新一个数据页时，如果数据页在内存中就直接更新；而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中。

• 这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

• 注意唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

• 适用场景：

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。

12、MySQL 是如何判断一行扫描数的？

• MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条。

• 而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度。

13、MySQL 的 redo log 和 binlog 区别？

14、为什么需要 redo log？

• redo log 主要用于 MySQL 异常重启后的一种数据恢复手段，确保了数据的一致性。

• 其实是为了配合 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 写入失败，说明此次操作失败，事务也不可能提交

• redo log 每次更新操作完成后，就一定会写入日志，如果写入失败，说明此次操作失败，事务也不可能提交。

• redo log 内部结构是基于页的，记录了这个页的字段值变化，只要crash后读取redo log进行重放，就可以恢复数据。

• 这就是为什么 redo log 具有 crash-safe 的能力，而 binlog 不具备。

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中。

• 2：称为实时写，延迟刷。每次事务提交写入到OS buffer，然后是每秒将日志写入到redo log file。

18、redo log 的执行流程?

我们来看下Redo log的执行流程，假设执行的 SQL 如下：

update T set a =1 where id =666

1. MySQL 客户端将请求语句 update T set a =1 where id =666，发往 MySQL Server 层。

2. MySQL Server 层接收到 SQL 请求后，对其进行分析、优化、执行等处理工作，将生成的 SQL 执行计划发到 InnoDB 存储引擎层执行。

3. InnoDB 存储引擎层将a修改为1的这个操作记录到内存中。

4. 记录到内存以后会修改 redo log 的记录，会在添加一行记录，其内容是需要在哪个数据页上做什么修改。

5. 此后，将事务的状态设置为 prepare ，说明已经准备好提交事务了。

6. 等到 MySQL Server 层处理完事务以后，会将事务的状态设置为 commit，也就是提交该事务。

7. 在收到事务提交的请求以后，redo log 会把刚才写入内存中的操作记录写入到磁盘中，从而完成整个日志的记录过程。

19、binlog 的概念是什么，起到什么作用， 可以保证 crash-safe 吗?

• binlog 是归档日志，属于 MySQL Server 层的日志。可以实现主从复制和数据恢复两个作用。

• 当需要恢复数据时，可以取出某个时间范围内的 binlog 进行重放恢复。

• 但是 binlog 不可以做 crash safe，因为 crash 之前，binlog 可能没有写入完全 MySQL 就挂了。所以需要配合 redo log 才可以进行 crash safe。

20、什么是两阶段提交？

MySQL 将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，这就是"两阶段提交"。

而两阶段提交就是让这两个状态保持逻辑上的一致。redolog 用于恢复主机故障时的未更新的物理数据，binlog 用于备份操作。两者本身就是两个独立的个体，要想保持一致，就必须使用分布式事务的解决方案来处理。

为什么需要两阶段提交呢?

• 如果不用两阶段提交的话，可能会出现这样情况

• 先写 redo log，crash 后 bin log 备份恢复时少了一次更新，与当前数据不一致。

• 先写 bin log，crash 后，由于 redo log 没写入，事务无效，所以后续 bin log 备份恢复时，数据不一致。

• 两阶段提交就是为了保证 redo log 和 binlog 数据的安全一致性。只有在这两个日志文件逻辑上高度一致了才能放心的使用。

在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。

21、MySQL 怎么知道 binlog 是完整的?

一个事务的 binlog 是有完整格式的：

• statement 格式的 binlog，最后会有 COMMIT；

• row 格式的 binlog，最后会有一个 XID event。

22、什么是 WAL 技术，有什么优点？

WAL，中文全称是 Write-Ahead Logging，它的关键点就是日志先写内存，再写磁盘。MySQL 执行更新操作后，在真正把数据写入到磁盘前，先记录日志。

好处是不用每一次操作都实时把数据写盘，就算 crash 后也可以通过redo log 恢复，所以能够实现快速响应 SQL 语句。

23、binlog 日志的三种格式

binlog 日志有三种格式

• Statement：基于SQL语句的复制((statement-based replication,SBR))

• Row：基于行的复制。(row-based replication,RBR)

• Mixed：混合模式复制。(mixed-based replication,MBR)

Statement格式

每一条会修改数据的 SQL 都会记录在 binlog 中

• 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

• 缺点：由于记录的只是执行语句，为了这些语句能在备库上正确运行，还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库得到和在主库端执行时候相同的结果。

Row格式

不记录 SQL 语句上下文相关信息，仅保存哪条记录被修改。

• 优点：binlog 中可以不记录执行的 SQL 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或 function、或trigger的调用和触发无法被正确复制的问题。

• 缺点:可能会产生大量的日志内容。

Mixed格式

实际上就是 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。

• write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。

• checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

• write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。

• 如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

• 有了 redo log，当数据库发生宕机重启后，可通过 redo log将未落盘的数据（check point之后的数据）恢复，保证已经提交的事务记录不会丢失，这种能力称为crash-safe。

25、原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，原因是什么？如何解决？

原因：从大到小可分为四种情况

• MySQL 数据库本身被堵住了，比如：系统或网络资源不够。

• SQL 语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的 SQL 语句。

• 确实是索引使用不当，没有走索引。

• 表中数据的特点导致的，走了索引，但回表次数庞大。

解决：

• 考虑采用 force index 强行选择一个索引

• 考虑修改语句，引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

• 第三种方法是，在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

• 如果确定是索引根本没必要，可以考虑删除索引。

作者：herongweiV

原文链接：https://blog.csdn.net/u013050857/article/details/123244085