数据库 面试题

                                                       数据库 面试题

• 数据库ACID特性

  • 原子性：

    • 事务被视为不可分割的最小单位，事务的所有操作要么全部提交成功，要么全部失败回滚。回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些操作即可

  • 一致性：

    • 数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的

  • 隔离性：

    • 一个事务所做的修改在最终提交以前，对其它事务是不可见的

  • 持久性：

    • 一旦事务提交，则其所做的修改将会永远保存到数据库中，即使系统发生崩溃，事务执行的结果也不能丢失

• 四大隔离级别以及不可重复读和幻读的出现原因

  • 隔离级别：

    • Read uncommitted（读未提交）：最低级别，任何情况都会发生

    • Read Committed（读已提交）：可避免脏读的发生

    • Repeatable（可重复读）：可避免脏读、不可重复读的发生。MySql默认级别

    • Serializable（串行化）：避免脏读、不可重复读、幻读的发生

  • 脏读：

    • A事务读取B事务尚未提交的更改数据，并在这个数据基础上操作。如果B事务回滚，那么A事务读取到的数据根本就是不合法的，称为脏读（在oracle中，由于有version控制，不会出现脏读）

  • 不可重复读：

    • A事务读取了B事务已经提交修改了的数据。比如A事务第一次读取数据，然后B事务更改该数据并提交，A事务再次读取该数据，两次读取的数据不一样

  • 幻读：

    • A事务读取了B事务已经提交的新增数据，注意与不可重复的区别是这里是新增数据，这两种情况对策是不一样的。对于不可重复度，只需要采取行级锁防止该记录数据被更改或修改，然而对于幻读必须加表级锁，防止在这个表中新增一条数据

• B/B+树原理，与其他查找树的比较

  • 数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度，而“胖矮”就是B树的特征之一，它的每个节点最多包含m个孩子，每个节点最多有m-1个数据，m称为b数的阶，m的大小取决于磁盘页的大小

  • B数：

    • 关键字（包括数据）集合分布在整颗树中

    • 任何一个关键字出现且只出现在一个结点中

    • 搜索有可能在非叶子结点结束

    • 其搜索性能等价于在关键字全集内做一次二分查找

    • 

  • B+树

    • 关键字不保存数据，只用来索引，所有数据都保存在叶子节点（B数是每个关键字都保存数据）

    • 所有的叶子节点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子节点本身以关键字大小自小至大顺序连接

    • 所有的非叶子结点可以看成是索引部分，结点中仅含其中子树中的最大（或最小）关键字，通常在B+树上有两个头指针，一个指向根节点，一个指向关键字最小的叶子结点

    • 同一个数字会在不同节点中重复出现，根节点的最大元素就是B+树的最大元素

    • 

• • B+树相比于B树的查询优势

    • B+树空间利用率高，可减少IO次数

      • 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往索引文件的形式存储在磁盘上，这样的话，索引查找过程中就要产生磁盘IO消耗，而因为B+树的内部节点只是作为索引使用，而不像B树那样每个节点都需要存储磁盘指针。也就是说：B+树中每个非叶子节点没有指向某个关键字具体信息的指针，所以每一个节点可以存放更多的关键字数量，即一次性读入内存所需要查找的关键字也就越多，减少了IO操作

      • 假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字信息具体指针2bytes。一课9阶B树（一个结点最多8个关键字）的内部结点需要2个盘块。而B+树内部结点只需要1个盘块。当需要把内部结点读入内存中的时候，B树就比B+树多一次盘块查找时间

    • 增删文件（节点）时，效率更高

      • 因为B+树的叶子结点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率，基于范围查询更好

    • B+树的查询效率更加稳定

      • 因为B+树的每次查询过程中，都需要遍历从根节点到叶子结点的某条路径，所有关键字的查询路径长度相同，导致每一次查询的效率相当

  • 与红黑树的比较

    • 红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用B+树作为索引结构，主要有以下两个原因

      • 更少的查找次数

        • 平衡树查找操作的时间复杂度和树高h相关，其中d为每个节点的出度。红黑树的出度为2，而B+树的出度一般都非常大，所以红黑树的数高h很明显比B+数大非常多，查找的次数也就更多

      • 利用磁盘预读特性

        • 为了减少磁盘IO操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道。并且只需要很短的旋转时间，速度会非常快。操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次IO就能完全载入一个节点。并且可以利用预读特性，相邻节点也能够被预先载入

• B+树索引和Hash索引的比较

  • 1、如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然这个前提是键值都是唯一的，如果键值不唯一就需要先找到该键所在位置，然后再根据链表往后扫描，直到直到相应的数据

  • 2、如果是范围查询检索，原先是有序的键值，经过哈希算法以后有可能变成不连续了，就没办法再利用索引完成范围查询检索了；同理哈希也没办法利用索引完成排序

  • 3、哈希索引不支持多列联合索引的最左匹配规则

  • 4、B+树索引的关键字检索效率比较平均，不像B数那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题

• MySql索引的优缺点

  • MySql有哪些索引

    • 索引分为单列索引（主键索引（不允许有空值）、唯一索引、普通索引）和组合索引

  • 为什么要有索引（索引的优点）

    • 创建索引可以大大提高系统的性能

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

    • 可以大大加快数据的检索速度

    • 可以加速表和表之间的连接，特别是在实现数据的参考完整性发面特别有意义

    • 在使用分组和排序子句进行数据检索时，同样可以显著检索查询中分组和排序的时间

    • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

  • 创建索引的缺点

    • 创建索引和维护索引要消耗时间，这种时间随着数据量的增加而增加

    • 索引需要占用物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，然后要建立聚簇索引，那么需要的空间就会更大

    • 当对表中的数据进行增加和删除、修改时，索引也要动态的维护，这样就降低了数据的维护速度

  • 索引的选择（哪些适合做索引）

    • 在经常需要搜索的列上，可以加快搜索的速度

    • 在作为主键的列上，强调该列的唯一性和组织表中数据的排列结构

    • 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度

    • 在经常需要根据范围进行搜索的列上创建索引，因为索引以及排序，其指定的范围是连续的

    • 在经常需要排序的列上创建索引，因为索引已经排序，这样的查询可以利用索引的排序，加快排序查询时间

    • 在经常使用在Where子句中的列上面创建索引，加快条件的判断速度

  • 哪些列不适合作为索引列

    • 查询中很少使用或者参考的列

    • 很少数据值的列

    • 定义为text，image和bit数据类型的列，因为往往这些列数据量要么相当大，要么取值很少

    • 当修改性能远远大于检索性能时，不应该创建索引

• MySql优化

  • 索引优化

    • 建立聚集索引

      • 首先聚合索引是提升查询速度最有效的手段，基于聚合索引的性质我们可以了解到数据库的物理存储顺序是按照集合索引顺序排列的，而通过聚合索引的B+树，我们可以迅速的查找任何一行的全部信息

      • 常查询数据建立索引或组合索引

      • 最左前缀原则

        • 建立组合索引优化查询语句时，一定要考虑到最左前缀原则（在组合索引中优先匹配最左的索引），否则你的索引建立的可以说毫无意义

      • 较长的数据列建立前缀索引

      • 不要建立无意义的索引

        • 对于查询次数很少的语句中的字段的索引，备注描述和大字段的索引等

  • 查询优化

    • 使用Explain进行分析

      • Explain用来分析SELECT语句，开发人员可以通过分析Explain结果来优化查询语句。比较重要的字段有：

        • select_type：查询类型，有简单查询、联合查询、子查询等

        • key：使用的索引

        • rows：扫描的行数

    • 优化数据访问

      • 减少请求的数据量

        • 只返回必要的列：最好不要使用SELECT * 语句

        • 只返回必要的行：使用LIMIT语句来限制返回的数据

        • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的

      • 减少服务器端扫描的行数

        • 最有效的方式就是使用索引来覆盖查询

    • 重构查询方式

      • 切分大查询 

        • 一个大查询如果一次执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小并重要的查询

      • 分解大连接查询

        • 将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

          • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询即使其中一个表发生变化，对其它表的查询缓存依然可以使用。分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询

• InnoDB和MyISAM比较

  • 1、MyISAM是非事务安全的，而InnoDB是事务安全的

  • 2、MyISAM锁的粒度是表级的，而InnoDB支持行级锁

  • 3、MyISAM支持全文类型搜索，而InnoDB不支持全文搜索

  • 4、MyISAM相对简单，效率上要优于InnoDB。小型应用可以考虑使用MyISAM

  • 5、MyISAM表保存成文本形式，跨平台使用更加方便

  • 6、MyISAM管理非事务，提供高速存储和检索以及全文搜索功能，如果在应用中执行大量select查询操作可选择

  • 7、InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作可选择

  • 8、InnoDB支持外键（从A表一个列（外键）去检索B表的主键）

  • 9、MyISAM一般是非聚集索引，InnoDB是聚集索引

• 聚集索引和非聚集索引（从物理角度分析索引）

  • InnoDB中索引的组织形式是B+树，非叶子节点存key，叶子结点存key+data，叶子结点之间用指针联通

  • 聚集索引：data存放是数据页

  • 非聚集索引：data中存放的是主键的值，得到主键后还需要在聚集索引上再查询一次，在效率方面最好使用聚集索引，并给表设定唯一主键。在数据索引的存储有序的情况下，可以大大提高效率

• 主从复制、作用、实现

  • 什么是主从复制？

    • 主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库

  • 主从复制概述

    • 简单来说就是数据集群，包括主数据库和从数据库，用户更新数据只去主数据库更新，查询数据只去从数据库查询，即更操作仅在主数据上，之后从数据库一起更新，查询操作仅在从数据库上。这样保证了读写分离，不会出现在高并发的情况下造成的读写冲突

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

  • 主从复制作用

    • 做数据库的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失

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

    • 读写分离，使数据库能支撑更大的并发，在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度

  • 主从复制的原理

    • 1、数据库有个bin-log二进制文件，记录了所有sql语句

    • 2、我们的目标就是把主数据库的bin-log文件的sql语句复制过来

    • 3、让其在从数据库的relay-log重做日志文件在执行一次这些sql语句即可

    • 4、下面的主从配置就是围绕这个原理配置

    • 5、具体需要三个线程来操作：

      • 1.binlog输出线程：每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。从库里，当复制开始的时候，从库就会创建两个线程进行处理

      • 从库IO线程：当START SLAVE语句在从库开始执行之后，从库创建一个IO线程，该线程连接到主库发送binlog里面更新记录到从库上。从库IO线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件

      • 从库的SQL线程：从库创建一个SQl线程，这个线程读取从库IO线程写到relay log的更新事件并执行

    • 可以知道，对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的IO线程和SQL线程

      • 主从复制如图：

      • 

      • 

      • 步骤一：主库db的更新事件（update、insert、delete）被写到binlog

      • 步骤二：从库发起连接，连接到主库

      • 步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库

      • 步骤四：从库启动后，创建一个IO线程，读取主库传过来的binlog内容并写入到relay log

      • 步骤五：还会创建一个SQL线程，从ralay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

  • 主从复制的常见问题及解决方案

    • 问题：

      • 主库宕机后，数据可能丢失

      • 从库只有一个sql Thread，主库写压力大，复制很可能延时

    • 解决方法：

      • 半同步复制-解决数据丢失的问题

      • 并行复制-解决从库复制延迟的问题

    • 半同步复制

      • 对于异步复制，主库将事务binlog事件写入binlog文件中，此时主库只会通知一下Dump线程发送这些新的Binlog，然后主库就会继续处理提交操作，而此时不会保证这些Binlog传到任何一个从库节点上。

      • 对于全同步复制，当主库提交事务之后，所有的从库节点必须收到，APPLY并且提交这些事务，然后主库线程才能继续做后续操作。这里面有一个很明显的缺点就是，主库完成一个事务的时间被拉长、性能降低

      • 对于半同步复制，是介于全同步复制和异步复制之间的一种，主库只需要等待至少一个从库节点收到并且Flush Binlog到Relay log文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全执行并且提交的反馈，这样就节省了很多时间

    • 并行复制

      • 原理：从库多线程apply binlog