SQL数据库面试总结

• 数据库范式：对数据库规范化设计，减少数据冗余，增加数据一致性
  • 第一范式：列不可分、无重复列，eg:【联系人】（姓名，性别，电话），电话还可以细分，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF；
  • 第二范式：有主键，保证完全依赖。eg:订单明细表【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName），Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID，不符合2NF；
  • 第三范式：无传递依赖(非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况)，eg:订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID），CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。
  • BCNF范式和第四范式
• 数据库事务：事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。
  • (1). 事务的特征
    • 原子性(Atomicity)：事务所包含的一系列数据库操作要么全部成功执行，要么全部回滚；
    • 一致性(Consistency)：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态；
    • 隔离性(Isolation)：并发执行的事务之间不能相互影响；
    • 持久性(Durability)：事务一旦提交，对数据库中数据的改变是永久性的。
  • (2). 事务并发带来的问题
    • 脏读：一个事务读取了另一个事务未提交的数据；
    • 不可重复读：不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；
    • 幻读：幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样。
  • (3). 隔离级别
    • 隔离级别决定了一个session中的事务可能对另一个session中的事务的影响。ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持，分别是：
      • READ UNCOMMITTED：最低级别的隔离，通常又称为dirty read，它允许一个事务读取另一个事务还没commit的数据，这样可能会提高性能，但是会导致脏读问题；
      • READ COMMITTED：在一个事务中只允许对其它事务已经commit的记录可见，该隔离级别不能避免不可重复读问题；
      • REPEATABLE READ：在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务commit或rollback。但是，其他事务的insert/delete操作对该事务是可见的，也就是说，该隔离级别并不能避免幻读问题。在一个事务中重复select的结果一样，除非本事务中update数据库。
      • SERIALIZABLE：最高级别的隔离，只允许事务串行执行。
      • MySQL默认的隔离级别是REPEATABLE READ。
  • (4).mysql的事务支持：MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：
    • MyISAM：不支持事务，用于只读程序提高性能；
    • InnoDB：支持ACID事务、行级锁、并发；
    • Berkeley DB：支持事务
  • (5).对分布式事务的支持（XA事务）：在开发中，为了降低单点压力，通常会根据业务情况进行分表分库，将表分布在不同的库中（库可能分布在不同的机器上）。在这种场景下，事务的提交会变得相对复杂，因为多个节点（库）的存在，可能存在部分节点提交失败的情况，即事务的ACID特性需要在各个不同的数据库实例中保证。比如更新db1库的A表时，必须同步更新db2库的B表，两个更新形成一个事务，要么都成功，要么都失败。
    • 资源管理器（resource manager）：用来管理系统资源，是通向事务资源的途径。数据库就是一种资源管理器。资源管理还应该具有管理事务提交或回滚的能力。
    • 事务管理器（transaction manager）：事务管理器是分布式事务的核心管理者。事务管理器与每个资源管理器（resourcemanager）进行通信，协调并完成事务的处理。事务的各个分支由唯一命名进行标识。
    • mysql在执行分布式事务（外部XA）的时候，mysql服务器相当于xa资源管理器，与mysql链接的客户端相当于事务管理器。
    • 分布式事务原理：分段式提交
      • 分布式事务通过2PC协议将提交分成两个阶段：
        • prepare：即所有的参与者准备执行事务并锁住需要的资源。参与者ready时，向transaction manager报告已准备就绪。
        • commit/rollback。当transaction manager确认所有参与者都ready后，向所有参与者发送commit命令。 
      • 外部XA用于跨多MySQL实例的分布式事务，需要应用层作为协调者，通俗的说就是比如我们在PHP中写代码，那么PHP书写的逻辑就是协调者。应用层负责决定提交还是回滚，崩溃时的悬挂事务。MySQL数据库外部XA可以用在分布式数据库代理层，实现对MySQL数据库的分布式事务支持，例如开源的代理工具：网易的DDB，淘宝的TDDL等等。
      • 内部XA事务用于同一实例下跨多引擎事务，由Binlog作为协调者，比如在一个存储引擎提交时，需要将提交信息写入二进制日志，这就是一个分布式内部XA事务，只不过二进制日志的参与者是MySQL本身。Binlog作为内部XA的协调者，在binlog中出现的内部xid，在crash recover时，由binlog负责提交。(这是因为，binlog不进行prepare，只进行commit，因此在binlog中出现的内部xid，一定能够保证其在底层各存储引擎中已经完成prepare)
• 数据库索引：索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B_TREE及其变种。索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。
  • 索引的底层实现（B-Tree vs B+Tree）
    • 磁盘读取
      • 磁盘读取数据I/O：寻找磁道+旋转选择
      • 预读+局部性原理：
        • 页存储模型：磁盘I/O读取非常慢，根据数据预读原理，当磁盘一个数据被查找使用，计算机将顺序读取一定长度的数据放入内存（页的整倍数，一般为16K，innoDB其中一页同样为16k）——局部性原理：当一个数据被用到时，其附近的数据通常也马上会被用到；
        • 页的构成：
          • 最小虚记录和最大虚记录：页存储数据范围
          • 记录堆，索引数据存储区域
          • slot区：页面有效指针，存储记录相对页面首地址的偏移（快速定位到数据在那个Slot中进行快速检索）
          • 页尾：页面校验信息
        • InnoDB存储结构：
          • 段：数据段、索引段、回滚段
          • 区：连续页组成，1MB，一个区64页，InnoDB一次会申请4-5个区
          • 页：InnoDB最小的存储单位，16K
          • 行：面向行的引擎
    • 索引：建立索引就是要进行排序（ 叶子节点：数据；非叶子几点：主键id+指针———8b+6b=14b）
      • B+数高度=2，1条数据为1kb，一页16kb，1个叶子节点可以存放16条数据。一个非叶子节点16kb/14b=1170；1170*16=18724条数据 ；
      • B+高度=3，1170*1170*16=21907740条。大于2千万条数据就需要分表，最多进行2次磁盘Io
      • innoDB的最小储存单位页 4kb（16384）
      • 页的Fil Header（指向下一页的指针和指向前一页的指针）
      • 存储一个表中的一行

        

      • Page Directory（页目录）：主键要小，自增
        • 红色部分为主键（也可以理解为页目录）主键索引

          

  • B+树是对B树的一种变形树，它与B树的差异在于：
    • B+树的非叶子结点只包含导航信息，具有索引作用，不包含实际的值，跟记录有关的信息均存放在叶结点中
    • 所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历，可以按照关键码排序的次序遍历全部记录。
    • B+ 树的优点在于：
      • 由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
      • B+树的叶子结点都是相链的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
      • 但是B树也有优点，其优点在于，由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。
  • B+-tree比B 树更适合实际应用中操作系统的文件索引和数据库索引
    • B+tree的磁盘读写代价更低：B+tree的内部结点并没有指向关键字具体信息的指针(红色部分)，因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了；
    • B+tree的查询效率更加稳定：由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；
    • 数据库索引采用B+树而不是B树的主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。
    • 文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上。而所谓索引，则为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，因此B+树相比B树更为合适。数据库系统巧妙利用了局部性原理与磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入，而红黑树这种结构，高度明显要深的多，并且由于逻辑上很近的节点(父子)物理上可能很远，无法利用局部性。最重要的是，B+树还有一个最大的好处：方便扫库。B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持，这是数据库选用B+树的最主要原因。
  • MyISAM索引实现（非聚集）
    • 索引文件和数据文件分离：.frm结构文件 .MYD数据文件 .MTI索引文件
    • B+tree主键索引和非主键页节点存储数据为指针，
  • InnoDB索引实现（聚集）
    • 索引文件和数据文件结合：.frm结构文件 .ibd数据文件+索引文件
    • B+tree主键索引下会存储此行的所有信息，非主键索引页节点存储数据为主键索页结点
  • 联合索引：最左原则
    • 红色仍为主键，但其叶子节点只包含联合排序+主键。如需其他行信息需要利用主键索引进行查找）

      

  • 索引选择原则：（单列索引和复合索引）
    • 表的某个字段值得离散度越高，该字段越适合选作索引的关键字（主键索引）
    • 占用存储空间少的字段更适合选作索引的关键字。例如，与字符串相比，整数字段占用的存储空间较少，因此，较为适合选作索引关键字。
    •  存储空间固定的字段更适合选作索引的关键字。与text类型的字段相比，char类型的字段较为适合选作索引关键字。
    • Where子句中经常使用的字段应该创建索引，不会出现在where子句中的字段不应该创建索引。
    • 两个表的连接字段应该创建索引。
    • 更新频繁的字段不适合创建索引。
    •  复合索引还有一个优点，它通过被称为“最左前缀”，避免出现重复索引
  • 索引利弊
    • 索引的好处
      • a、提高数据检索的效率，降低检索过程中必须要读取得数据量，降低数据库IO成本。
      • b、降低数据库的排序成本。因为索引就是对字段数据进行排序后存储的，如果待排序的字段与索引键字段一致，就在取出数据后不用再次排序了，因为通过索引取得的数据已满足排序要求。另外，分组操作是先排序后分组，所以索引同样可以省略分组的排序操作，降低内存与CPU资源的消耗。
    • 索引的弊端
      • a、索引会增加 增、删、改操作所带来的IO量与调整索引的计算量。
      • b、索引要占用空间，随着数据量的不断增大，索引还会带来存储空间的消耗。
  • 索引失效情况
    • 1.索引无法存储null值：当查询is null时，不能利用索引，只能进行全表扫描
    • 2.模糊查询不能利用索引(like '%XX'或者like '%XX%')---联合索引的最有原则
    • 3.如果条件中有or，即使其中有条件带索引也不会使用，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
    • 4.对于联合索引，不是使用的第一部分，则不会使用索引
    • 5.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引（如varchar不加单引号的话可能会自动转换为int型）；
    • 6.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
• MySQL存储引擎中的MyISAM和InnoDB区别详解
  • InnoDB是MySQL的数据库引擎之一，MyISAM相比，InnoDB的最大特色就是支持ACID兼容的事务功能，MyISAM与InnoDB作为MySQL的两大存储引擎的差异主要包括：
    • 存储结构：每个MyISAM在磁盘上存储成三个文件：第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义，数据文件的扩展名为.MYD (MYData)，索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
    • 存储空间：MyISAM可被压缩，占据的存储空间较小，支持静态表、动态表、压缩表三种不同的存储格式。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
    • 可移植性、备份及恢复：MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便，同时在备份和恢复时也可单独针对某个表进行操作。InnoDB免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
    • 事务支持：MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。InnoDB提供事务、外键等高级数据库功能，具有事务提交、回滚和崩溃修复能力。
    • AUTO_INCREMENT：在MyISAM中，可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，它可以根据前面几列进行排序后递增。InnoDB中必须包含只有该字段的索引，并且引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
    • 表主键：MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
    • 表的具体行数：MyISAM保存表的总行数，select count() from table;会直接取出出该值；而InnoDB没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
    • CURD操作：在MyISAM中，如果执行大量的SELECT，MyISAM是更好的选择。对于InnoDB，如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
    • 外键：MyISAM不支持外键，而InnoDB支持外键。
    • MyISAM索引实现（非聚集）：索引文件和数据文件分离：.frm结构文件 .MYD数据文件 .MTI索引文件
    • InnoDB索引实现（聚集）：索引文件和数据文件结合：.frm结构文件 .ibd数据文件+索引文件
• MySQL锁
  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
    • 举个例子，一个用户表user，有主键id和用户生日birthday。
    • 当你使用update … where id=?这样的语句时，数据库明确知道会影响哪一行，它就会使用行锁；
    • 当你使用update … where birthday=?这样的的语句时，因为事先不知道会影响哪些行就可能会使用表锁。
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
  • 共享锁（Share Lock）：S锁，也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。
    • 1. 多个事务可封锁同一个共享页；
    • 2. 任何事务都不能修改该页；
    • 3. 通常是该页被读取完毕，S锁立即被释放。
  • 排他锁（Exclusive Lock）：X锁，也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。（某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面打开了。）
    • 1. 仅允许一个事务封锁此页；
    • 2. 其他任何事务必须等到X锁被释放才能对该页进行访问；
    • 3. X锁一直到事务结束才能被释放。
• MySQL中的悲观锁与乐观锁的实现:
  • 悲观锁与乐观锁的应用场景
    • 一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。
  • 悲观锁
    • 悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。
    • 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。比如行锁、表锁、读锁、写锁等，都是在操作之前先上锁。
    • Java synchronized 就属于悲观锁的一种实现，每次线程要修改数据时都先获得锁，保证同一时刻只有一个线程能操作数据，其他线程则会被block。
  • 乐观锁
    • 乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。
    • 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
    • 乐观锁一般来说有以下2种方式：
      • 使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
      • 使用时间戳（timestamp）。乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。