数据库精选 60 道面试题.docx

《数据库精选 60 道面试题.docx》由会员分享，可在线阅读，更多相关《数据库精选 60 道面试题.docx（36页珍藏版）》请在第一文库网上搜索。

1、数据库精选60道面试题基础相关1、关系型和非关系型数据库的区别？关系型数据库的优点容易理解，因为它采用了关系模型来组织数据。可以保持数据的一致性。数据更新的开销比较小。支持复杂查询（带where子句的查询）非关系型数据库（NOSQL）的优点无需经过SQL层的解析，读写效率高。基于键值对，读写性能很高，易于扩展可以支持多种类型数据的存储，如图片，文档等等。扩展（可分为内存性数据库以及文档型数据库，比如Redis, MongoDB, HBase等，适合场景：数据量大高可用的日志系统/地理位置存储系统）。2、详细说一下一条MySQL语句执行的步骤Server层按顺序执行SQL的步骤为：客户端请求-

2、连接器（验证用户身份，给予权限）查询缓存（存在缓存则直接返回，不存在则执行后续操作）分析器（对SQL进行词法分析和语法分析操作）优化器（主要对执行的SQL优化选择最优的执行方案方法）执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）-去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）索引相关3、MySQL使用索引的原因？根本原因索引的出现，就是为了提高数据查询的效率，就像书的FI录一样。对于数据库的表而言，索引其实就是它的“目录”。扩展创建唯一性索引，可以保证数据库表中每一行数据的唯一性。帮助引擎层避免排序和临时表将随机10变为顺序10,加速表和表之间的连接。4、索引

3、的三种常见底层数据结构以及优缺点三种常见的索引底层数据结构：分别是哈希表、有序数组和搜索树。哈希表这种适用于等值查询的场景，比如memcached以及其它一些NoSQL引擎，不适合范围查询。有序数组索引只适用于静态存储引擎，等值和范围查询性能好，但更新数据成本高。N叉树由于读写上的性能优点以及适配磁盘访问模式以及广泛应用在数据库引擎中。扩展（以InnoDB的一个整数字段索引为例，这个N差不多是1200。棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。其实，树的第二层也有很大

4、概率在内存中，那么访问磁盘的平均次数就更少了。）5、索引的常见类型以及它是如何发挥作用的？根据叶子节点的内容，索引类型分为主键索引和非主键索引。主键索引的叶子节点存的整行数据，在TnnoDB里也被称为聚簇索引。非主键索引叶子节点存的主键的值，在InnoDB里也被称为二级索引。6、MylSAM和InnoDB实现B树索引方式的区别是什么？InnoDB存储引擎：B+树索引的叶子节点保存数据本身，其数据文件本身就是索引文件。MylSAM存储引擎：B+树索引的叶子节点保存数据的物理地址，叶节点的data域存放的是数据记录的地址，索引文件和数据文件是分离的。7、InnoDB为什么设计B+树索引？两个考虑因

5、素：InnoDB需要执行的场景和功能需要在特定查询上拥有较强的性能。CPU将磁盘上的数据加载到内存中需要花费大量时间。为什么选择B+树：哈希索引虽然能提供0（1）复杂度查询，但对范围查询和排序却无法很好的支持，最终会导致全表扫描。B树能够在非叶子节点存储数据，但会导致在查询连续数据可能带来更多的随机I0o而B+树的所有叶节点可以通过指针来相互连接，减少顺序遍历带来的随机10。普通索引还是唯一索引？由于唯一索引用不上change buffer的优化机制，因此如果业务可以接受，从性能角度出发建议你优先考虑非唯一索引。8、什么是覆盖索引和索引下推？覆盖索引：在某个查询里面，索引k已经“覆盖了”我们的

6、查询需求，称为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。索引下推：MySQL 5. 6引入的索引卜推优化(index condition pushdown), 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。9、哪些操作会导致索引失效？对索引使用左或者左右模糊匹配，也就是like %xx或者like %xx%这两种方式都会造成索引失效。原因在于查询的结果可能是多个，不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。对索引进行函数/对索引进行表达式计算，因为索引保持的是索引字段的原

7、始值，而不是经过函数计算的值，自然就没办法走索引。对索引进行隐式转换相当于使用了新函数。WHERE子句中的OR语句，只要有条件列不是索引列，就会进行全表扫描。10、字符串加索引直接创建完整索引，这样可能会比较占用空间。创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。日志相关11、MySQL 的 change buffer 是什么？当需要更新一个数据页时，如果数据页在内存中就直接更新；而如果这个数据页还没有在内存中

8、的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中。这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。注意唯一索引的更新就不能使用change buffer,实际上也只有普通索引可以使用。适用场景：-对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。-反过来，假设一个业务的更新模式是写入之后马上会做查询，那么

9、即使满足了条件，将更新先记录在change buffer,但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问10的次数不会减少，反而增加了 change buffer的维护代价。12、MySQL是如何判断一行扫描数的？MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条。而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度。13、MySQL 的 redo log 和 binlog 区别？14、为什么需要redo log?redo log主要用于MySQL异常重启后的一种数据恢复手段，确保了数据的一致性。其实是为了配合MySQL的WAL机制。

10、因为MySQL进行更新操作，为了能够快速响应，所以采用了异步写回磁盘的技术，写入内存后就返回。但是这样，会存在crash后 内存数据丢失的隐患，而redo log具备crash safe的能力。15、为什么redo log具有crash-safe的能力，是binlog无法替代的？第一点：redo log可确保innoDB判断哪些数据己经刷盘，哪些数据还没有redo log和binlog有一个很大的区别就是，一个是循环写，一个是追加写。也就是说redo log只会记录未刷盘的日志，已经刷入磁盘的数据都会从redo log这个有限大小的日志文件里删除。binlog是追加日志，保存的是全量的日志。当

11、数据库crash后，想要恢复未刷盘但已经写入redo log和binlog的数据到内存时，binlog是无法恢复的。虽然binlog拥有全量的日志，但没有一个标志让innoDB判断哪些数据已经刷盘，哪些数据还没有。但redo log不一样，只要刷入磁盘的数据，都会从redo log中抹掉，因为是循环写！数据库重启后，直接把redo log中的数据都恢复至内存就可以了。第二点：如果redo log写入失败，说明此次操作失败，事务也不可能提交redo log每次更新操作完成后，就一定会写入日志，如果写入失败，说明此次操作失败，事务也不可能提交。redo log内部结构是基于页的，记录了这个页的字段

12、值变化，只要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 至lj磁盘，先从 binlog 恢复 redo l

13、og,再从 redo log 恢复 change bufferochange 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 (刷盘)。这种先写日志，再写磁盘的

14、技术，就是WAL。可以发现，redo log buffer写入到redo log file,是经过OS buffer中转的。其实可以通过参数innodb_flush_log_at_trx_connnit进行配置,参数值含义如下:0：称为延迟写，事务提交时不会将redo 1 og buffer中日志写入到OS buffer,而是每秒写入OS buffer并调用写入到redo log file中。1：称为实时写，实时刷”，事务每次提交都会将redo log buffer中的日志写入OS buffer并保存到redo log file中。2：称为实时写，延迟刷。每次事务提交写入到OS buffer,

15、然后是每秒将日志写入到redo log file。18、redo log的执行流程？我们来看下Redolog的执行流程，假设执行的SQL如下:update Ta =1 where id =666MySQL 客户端将请求语句 update T set a =1 where id =666,发往 MySQLServer 层。2. MySQL Server层接收到SQL请求后，对其进行分析、优化、执行等处理工作，将生成的SQL执行计划发到InnoDB存储引擎层执行。3. InnoDB存储引擎层将a修改为1的这个操作记录到内存中。4. 记录到内存以后会修改redo log的记录，会在添加一行记录，其内容是需要在