面试整理——数据库

发表于 更新于 分类于
本文字数： 17k 阅读时长 ≈ 16 分钟
整理面试中常问的数据库相关问题，持续更新中。

数据库

一. 综合

1.1 范式

【数据库三范式是哪三个？】

  1. 确保每列的原子性，属性不可分
  2. 非主键列完全函数依赖于主键 （要求每个表只描述一件事情）
  3. 满足第二范式，并且表中的列不存在对非主键列的传递依赖

1.2 事务

【什么是数据库事务？】

指满足ACID特性的一组数据库操作序列。

【数据库事务特点（ACID）？】

事务有哪些特性？怎么单独理解XX性？

ACID包括：

  • 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
  • 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
  • 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
  • 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。系统发生奔溃可以用重做日志（Redo Log）进行恢复，从而实现持久性。

【数据库事务的并发一致性问题？】

并发一致性问题包括哪几种？

在并发环境下，事务的隔离性很难保证，所以有几种并发一致性问题：

  1. 修改丢失：即一个事务的更新背另一个事务的更新替换。
  2. 读脏数据：指当前事务读到另外事务未提交的数据。
  3. 不可重复读：指一个事务多次读取同一数据集合，在此期间另外事务也访问了此集合并做出修改，导致前个事务两次读取数据不一致。
  4. 幻影读：本质也是不可重复读，指一个事务读取某个范围的数据，另一事务在此范围插入新数据，导致前个事务再次读取结果不一致。区别在要避免幻读需要锁整张表，而避免不可重复读只需锁住行即可。

如何处理并发一致性问题？

通过锁机制来解决此问题，MySql提供了对应的封锁机制来实现，通过三级封锁协议来处理并发一致性问题。

两种封锁粒度：选择锁粒度要在锁的开销和并发程度之间做出权衡。

  • 行级锁
  • 表级锁

封锁类型：

  • 读写锁：同Java一样，写锁持有时其他事务不能获取任何锁，读锁持有时其他事务可以获取读锁，但不能获取写锁。
    • 互斥锁：简称X锁，又叫写锁。
    • 共享锁：简称S锁，又叫读锁。
  • 意向锁：更容易的支持多粒度的封锁，在同时存在行级锁和表级锁的情况下，事务想要对表加锁，要首先分别检查是否有其他事务加表锁或行锁，这个过程需要对每一行都进行一次检测，这相当耗时。意向锁在 X/S 锁之上引入了 IX/IS，二者都是表锁，规定事务获取某行的X或S锁前要先获取IX或IS锁。这样当事务想对表加写锁时只需检测是否有其他事务对表加了 X/IX/S/IS 锁，只需做一次检测。（任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁）

封锁协议：

  • 三级封锁协议：
    • 一级：事务 T 要修改数据 A 时必须加写锁，直到 T 结束才释放锁。可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。
    • 二级：在一级的基础上，要求读取数据 A 时必须加读锁，读取完马上释放读锁。可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。
    • 三级：在二级的基础上，要求读取数据 A 时必须加读锁，直到事务结束了才能释放读锁。可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。
  • 两段封锁协议：加锁和解锁分为两个阶段进行。

【数据库事务隔离级别？】

数据库有哪几种事务隔离级别？

  1. 读未提交（READ UNCOMMITTED）：事务中的修改，即使没有提交，对其它事务也是可见的。
  2. 读已提交（READ COMMITTED）： 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
  3. 可重复读（REPEATABLE READ）：保证在同一个事务中多次读取同一数据的结果是一样的。
  4. 可串行化（SERIALIZABLE）：强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。

【你们生产用的是哪种事务隔离级别？为什么？】

MySql默认是可重复读，但很多项目都采用读已提交。

为什么默认是可重复读？

MySql的历史版本中，主从复制基于binlog（记录数据库修改的文件），当时binlog只有 statement 这种模式（记录修改SQL语句），后来还有row（记录每行数据变更）和 mixed（前两种模式混合）总共三种模式。

statement模式在读已提交下进行主从复制有BUG，在master上执行的顺序为先删后插，而binlog的记录顺序为先插后删，所以slave同步binlog后导致执行顺序和master不一致。

解决方法：

  1. 调整隔离级别为可重复读。
  2. 更新模式为row（5.1版本后引入）。

为什么选择读已提交？

首先排除读未提交和可串行化，前者会出现脏读，后者每次读操作都要加锁。

选读已提交的原因：

  1. 可重复读下，存在间隙锁（封锁索引记录中的间隔）导致死锁概率增加：如 SELECT * FROM testWHEREid BETWEEN 5 AND 7，表中有id（1，5，7，10），所以存在5个间隙，上述sql会锁住(5,7]和(7,10]这两个区间，导致区间无法插入数据。而读已提交不存在间隙锁。
  2. 可重复读下，条件未命中索引会锁表，而读已提交，只会锁行。
  3. 读已提交，因为半一致性读的特性会增加update操作的并发性。一个事务执行完update操作，但尚未提交，另一事务获取锁时发现已占用，InnoDB开启半一致性读返回最新的已提交版本，MySql会重新发起一次读操作，此时可以读到此行的最新版本并加锁，而可重复读这时事务只能等待前一事务提交。
  4. 不可重复读问题可以接受，已经提交了数据，即使不一样通常也不会造成问题。

1.3 数据库连接

【数据库连接池？】

  1. 单次数据库连接比较耗时（有过多网络IO步骤），当数据库操作频繁时会成为性能瓶颈，需要数据库连接池来避免重复创建连接。

  2. 两个最重要的参数：最小连接数和最大连接数

    步骤：

    • 连接池创建：根据配置参数初始化连接池，初始化最小数量的连接
    • 连接池管理：
      1. 当前有空闲连接，直接复用。
      2. 当前没有空闲连接，且还未达到最大连接数，则创建新的连接。
      3. 没有空闲，且已大于等于最大，则按最大等待时间等待，超时仍未有空闲资源则返回异常。
    • 连接池关闭：先关闭连接，再释放资源。

二. SQL

2.1 连接

【SQL有哪些表连接方式？】

什么是连接？连接即用 JOIN 连接多个表，条件语句用 ON 代替 Where，用来代替子查询。

几种连接包括：

  • 内连接：INNER JOIN，又叫等值连接，等价于 from A,B where A.key = B.key
  • 外连接：OUTER JOIN，保留了没有关联的行。
    • 左外连接：LEFT OUTER JOIN，保留左边未关联的行
    • 右外连接：RIGHT OUTER JOIN，保留右边未关联的行

2.2 组合查询

【组合查询？】

使用 UNION 来组合两个查询，每个查询必须包含相同的列、表达式和聚集函数，会去除重复行（使用UNION ALL保留），只能保留一个ORDER BY 于语句末尾。

2.3 视图

视图是什么？

  • 视图是一种虚拟表，不存储数据，访问视图时从实体表获取数据；
  • 有两种实现算法：
    • 临时表算法
    • 合并算法
  • 使用场景：
    • 重构Schema时使用视图，修改表结构时不会影响应用运行；
    • 使用视图实现基于列的权限控制，不需要真正的在系统创建权限，没有额外开销。

2.4 存储过程

【存储过程？】

存储过程可以看成是对一系列 SQL 操作的批处理。

优点：

  • 代码封装，保证了一定的安全性；
  • 这是一种代码重用，方便统一业务规则，保证某些行为总是一致，所以也带来了一定的安全性；
  • 由于是预先编译，因此具有很高的性能。
  • 服务器内部执行，离数据最近，可以节省带宽和网络延迟；
  • 服务器端可以缓存存储过程的执行计划，降低了需要反复调用的过程的消耗；

缺点：

  • 编写存储代码难度较高；
  • 很难实现复杂逻辑；
  • 原本只需部署应用代码和库表结构变更，还需要额外部署内部存储代码；
  • 部署在服务器会带来安全隐患，只要破解数据库就将功能和数据全部获取；
  • 存储过程给数据库服务器带来额外的压力，数据库服务器的扩展性要远差于应用服务器。
  • 无法控制存储程序的资源消耗，可能一个错误就把服务器拖死。
  • 存储代码的实现有很多限制，调试也很困难，难以定位问题。

2.5 聚合函数

【聚合函数？】

在一个行的集合（一组行）上进行操作，对每个组给一个结果。

如：

  • AVG()
  • SUM()
  • MAX()
  • MIN()
  • COUNT()

三. MySql

3.1 索引

【索引的适用场景？】

首先索引是一块单独的物理结构，类似于书的目录，所以会有维护开销和空间开销。

大部分场景索引对于提高数据检索的优势大大超过其不足，特别是：

  • 数据量大的表要创建索引
  • 经常做表连接的字段要创建索引
  • 经常出现在Where子句的字段要创建索引
  • 如性别这种选择性低的字段不建议创建索引
  • 大的文本字段不建议创建索引
  • 复合索引要多考虑，建议用单字段索引代替
  • 频繁进行数据操作的表不建议创建索引，维护索引的成本太高
  • 无用的索引会对执行计划造成负面影响

不能使用索引的情况？

  • 全表扫描：select * （标明具体返回字段代替）
  • 否定判断：where xx != 或 <> … 以及 where xx is (not) null （用 union all 来代替）
  • 或判断：where xx or …（用 union all 来代替）
  • 存在判断：where xx in/not in …（between，exists或join替换in，用not exists替代not in）
  • 以通配符开始的LIKE：LIKE '%abc%' （考虑使用全文检索）
  • ……

【数据库的索引有哪几种？】

数据库索引类型：

  • 唯一索引：不允许任何两行具有相同索引值的索引。
  • 非唯一索引：允许任何两行具有相同索引值的索引。
  • 主键索引：为表定义主键将自动创建主键索引，是唯一索引的特定类型，不允许null。
  • 聚集索引：表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。

【MySql的索引有哪几种？】

  1. B+Tree 索引
  2. 哈希索引
  3. 全文索引
  4. 空间数据索引

【聚集索引与非聚集索引？】

什么是聚集索引？

  1. 聚集索引就是以主键创建的索引
  2. 每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放，实际的数据页只能按照一颗 B+ 树进行排序
  3. 表记录的排列顺序和与索引的排列顺序一致
  4. 聚集索引存储记录是物理上连续存在
  5. 聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多
  6. 聚簇索引适合排序，非聚簇索引不适合用在排序的场合，因为聚簇索引叶节点本身就是索引和数据按相同顺序放置在一起，索引序即是数据序，数据序即是索引序，所以很快。非聚簇索引叶节点是保留了一个指向数据的指针，索引本身当然是排序的，但是数据并未排序，数据查询的时候需要消耗额外更多的I/O，所以较慢
  7. 更新聚集索引列的代价很高，因为会强制innodb将每个被更新的行移动到新的位置

什么是非聚集索引？

  1. 除了主键以外的索引
  2. 聚集索引的叶节点就是数据节点，而非聚簇索引的叶节点仍然是索引节点，并保留一个链接指向对应数据块
  3. 聚簇索引适合排序，非聚簇索引不适合用在排序的场合
  4. 聚集索引存储记录是物理上连续存在，非聚集索引是逻辑上的连续。

聚簇和非聚簇又可细分哪一些？

TODO

使用聚集索引为什么查询速度会变快？

使用聚簇索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻

建立聚集索引有什么需要注意的地方吗？

在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置，索引此时会重排，会造成很大的资源浪费

非聚集索引最多可以有多少个？

每个表你最多可以建立249个非聚簇索引。非聚簇索引需要大量的硬盘空间和内存

【组合索引？】

（写了一个例子，选择会走索引的sql）。like索引的例子？(就是以like %李%和like 李%为例子讲)有没有其它例子也是这样跳过索引的？

【组合索引和几个单个的索引有什么区别？】

TODO

【MySql常用的索引类型有哪些？】

MySql常用的索引类型有哪些？

TODO

索引的优缺点，以及索引选择？

TODO

数据库索引优缺点？

  1. 需要查询，排序，分组和联合操作的字段适合建立索引
  2. 索引多，数据更新表越慢，尽量使用字段值不重复比例大的字段作为索引，联合索引比多个独立索引效率高
  3. 对数据进行频繁查询进建立索引，如果要频繁更改数据不建议使用索引
  4. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

【为什么要用B+树来做索引？】

为什么要用B+树来做索引？

TODO

B+树索引的原理？

TODO

索引的底层实现是B+树，为何不采用红黑树，B树？

  1. B+Tree非叶子节点只存储键值信息，降低B+Tree的高度，所有叶子节点之间都有一个链指针，数据记录都存放在叶子节点中
  2. 红黑树这种结构，h明显要深的多，效率明显比B-Tree差很多
  3. B+树也存在劣势，由于键会重复出现，因此会占用更多的空间。但是与带来的性能优势相比，空间劣势往往可以接受，因此B+树的在数据库中的使用比B树更加广泛

B+树与B树的区别？索引为什么选择B+树？说下索引结构，为什么使用B+树？

TODO

为什么mongodb的索引用了B树，而mysql用B+树？

TODO

BTree 与 Hash 索引有什么区别？

  1. BTree索引可能需要多次运用折半查找来找到对应的数据块
  2. HASH索引是通过HASH函数，计算出HASH值，在表中找出对应的数据
  3. 大量不同数据等值精确查询，HASH索引效率通常比B+TREE高
  4. HASH索引不支持模糊查询、范围查询和联合索引中的最左匹配规则，而这些Btree索引都支持

【什么是索引覆盖？】

TODO

【索引失效条件？】

  1. 条件是or，如果还想让or条件生效，给or每个字段加个索引
  2. like开头%
  3. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不会使用索引
  4. where中索引列使用了函数或有运算

【Innodb中索引的实现？】

TODO

【知道mysql的索引算法么？】

TODO

3.2 事务

【AUTOCOMMIT？】

MySQL 默认采用自动提交模式。也就是说，如果不显式使用START TRANSACTION语句来开始一个事务，那么每个查询操作都会被当做一个事务并自动提交。

【MySql中数据库事务是如何实现的？】

通过预写日志方式实现的，redo和undo机制是数据库实现事务的基础，包括：Buffer Pool、LogBuffer、Redo Log和Undo Log。

一个Update语句：

  1. InnoDB收到SQL后，先根据条件找到数据所在页，并将页缓存在Buffer Pool中。
  2. 执行Update语句，修改Buffer Pool即内存中的数据。
  3. 针对Update语句生成一个Redo Log对象，并存入LogBuffer中。
  4. 针对Update语句生成一个Undo Log对象，用于事务回滚。
  5. 如果事务提交，则把Redo Log对象持久化，后续有其它机制将Buffer Pool修改的数据页持久化到磁盘。
  6. 如果事务回滚，则利用Undo Log对象进行回滚。

两种日志：

  • redo日志用来在断电/数据库崩溃等状况发生时重演一次刷数据的过程，把redo日志里的数据刷到数据库里，保证了事务的持久性（Durability）
  • undo日志是在事务执行失败的时候撤销对数据库的操作，保证了事务的原子性

【七种事务传播行为？】

  1. Propagation.REQUIRED{默认} 如果当前存在事务，则加入该事务，如果当前不存在事务，则创建一个新的事务。
  2. Propagation.SUPPORTS 如果当前存在事务，则加入该事务；如果当前不存在事务，则以非事务的方式继续运行。
  3. Propagation.MANDATORY 如果当前存在事务，则加入该事务；如果当前不存在事务，则抛出异常。
  4. Propagation.REQUIRES_NEW 重新创建一个新的事务，如果当前存在事务，延缓当前的事务。
  5. Propagation.NOT_SUPPORTED 以非事务的方式运行，如果当前存在事务，暂停当前的事务。
  6. Propagation.NEVER 以非事务的方式运行，如果当前存在事务，则抛出异常。
  7. Propagation.NESTED 如果没有，就新建一个事务；如果有，就在当前事务中嵌套其他事务。

【@Transaction？】

底层实现是AOP，动态代理

  1. 实现是通过Spring代理来实现的。生成当前类的代理类，调用代理类的invoke（）方法，在invoke（）方法中调用 TransactionInterceptor拦截器的invoke（）方法；
  2. 非public方式其事务是失效的；
  3. 自调用也会失效，因为动态代理机制导致
  4. 多个方法外层加入try…catch，解决办法是可以在catch里 throw new RuntimeException（）来处理

3.3 多版本并发控制

【什么是MVCC？】

MVCC机制了解不？

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

引入封锁机制处理并发一致性问题，又因为数据库场景中读远远多于写，所以引入读写锁，MVCC利用多版本思想，对写操作进行了又一层优化，即写操作更新最新的版本快照，而读操作读旧版本快照，二者无互斥关系，这类似于写时复制CopyOnWrite。

脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交的快照。

MVCC机制有什么问题？

不能完整的解决所有隔离级别：不能解决幻读，无法实现可串行化的隔离级别。

【Undo日志？】

MVCC的多版本指多个版本的快照，而快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。

例如在 MySQL 创建一个表 t，包含主键 id 和一个字段 x。我们先插入一个数据行，然后对该数据行执行两次更新操作。

1
2
3
INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;

因为没有使用 START TRANSACTION 将上面的操作当成一个事务来执行，根据 MySQL 的 AUTOCOMMIT 机制，每个操作都会被当成一个事务来执行，所以上面的操作总共涉及到三个事务。快照中除了记录事务版本号 TRX_ID 和操作之外，还记录了一个 bit 的 DEL 字段，用于标记是否被删除。

INSERT、UPDATE、DELETE 操作会创建一个日志，并将事务版本号 TRX_ID 写入。DELETE 可以看成是一个特殊的 UPDATE，还会额外将 DEL 字段设置为 1。

【MVCC 和 ReadView？MVCC的实现原理？】

隐藏列：InnoDB的数据行结构：{DATA_TRX_ID, DATA_ROLL_PTR, PK,other columns}，前两个是隐藏列：

  • DATA_TRX_ID：表示最近修改该行数据的事务ID
  • DATA_ROLL_PTR：表示指向该行回滚段的指针，该行上所有旧的版本，在undo中都通过链表的形式组织，而该值，正式指向undo中该行的历史记录链表

事务链表：事务在开始到提交的过程会保存在一个叫trx_sys的事务链表中，提交后从链表移除。

ReadView是实现MVCC的关键数据结构，其包含三个主要成员ReadView{low_trx_id, up_trx_id, trx_ids}分别指向事务链表的三个部分：

  • low_trx_id：表示该SQL启动时，当前事务链表中最大的事务id编号，也就是最近创建的除自身以外最大事务编号；
  • up_trx_id：表示该SQL启动时，当前事务链表中最小的事务id编号，也就是当前系统中创建最早但还未提交的事务；
  • trx_ids：表示所有事务链表中事务的id集合。

在并发情况下，事务链表中保存着还未提交的事务，这些事务的改变哪些对当前事务是可见的由ReadView来判定：

  • 所有数据行DATA_TRX_ID小于up_trx_id都是已提交完的事务，都对当前事务可见。
  • 所有数据行DATA_TRX_ID大于low_trx_id说明修改此行数据的事务在当前事务之后，所以对当前事务不可见。
  • 位于中间的事务是否可见由事务隔离级别决定。读已提交都是可见的，可重复读则都不可见。

ReadView于每个SQL绑定，而非事务。在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。

【MVCC 的SELECT操作会加锁吗？】

  • MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。
  • MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。

3.4 锁

【MySQL 的锁机制？】

【MySQL有哪些锁？】

【MySQL行锁是否会有死锁的情况？】

【InnoDB的锁实现？什么是Next-Key Locks？】

三种锁实现：

  1. Record Locks：记录锁，也叫行锁。锁定一个记录上的索引，而不是记录本身。

  2. Gap Locks：间隙锁。锁定索引之间的间隙，但是不包含索引本身。

  3. Next-Key Locks：临键锁。是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间，例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

    1
    2
    3
    4
    5
    (-∞, 10]
    (10, 11]
    (11, 13]
    (13, 20]
    (20, +∞)

Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。

MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。

3.5 引擎

【MySQL常用的存储引擎？特点与区别?还有应用场景】

  1. Myiasm：
    • 设计简单，数据以紧密格式存储。
    • 提供了大量的特性，包括压缩表、空间数据索引等。
    • 使用的是非聚集索引。
    • 不支持数据库事务。
    • 不支持行级锁，只能锁表。
  2. InnoDB：默认的存储引擎。
    • 支持事务，实现四种隔离级别：默认可重复读，在此级别通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。
    • 底层为B+树实现，适合处理多重并发更新操作，普通select都是快照读，快照读不加锁。
    • 使用的是聚集索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
    • 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
    • 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

比较：

  • 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
  • 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
  • 外键：InnoDB 支持外键。
  • 备份：InnoDB 支持在线热备份。
  • 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
  • 其它特性：MyISAM 支持压缩表和空间数据索引。

【InnoDB 的一些特性？】

【InnoDB 表对主键生成策略是什么样的？】

优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id隐藏列作为主键。

【AUTO_INCREMENT原理（考察并发情况）？】

3.6 优化

【常见的数据库优化手段？】

  1. 使用用 Explain 进行分析
  2. 优化数据访问：
    • 减少请求的数据量，如只返回所需列和行，利用缓存。
    • 使用索引覆盖查询（包含所有满足查询需要的数据的索引）
  3. 重构查询方式：
    • 切分大型查询，避免阻塞小查询
    • 分解复杂连接查询，改为单表查询，在应用程序中再关联，优点：
      • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
      • 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
      • 减少锁竞争；
      • 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
      • 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效

【mysql索引的执行计划有了解过吗？有用过explain吗？重要的执行参数有哪些？】

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

比较重要的字段有：

  • select_type : 查询类型，有简单查询、联合查询、子查询等
  • type : 对表的访问方式，有全表扫描、全文索引检索、全索引扫描等等
  • key : 使用的索引
  • rows : 扫描的行数

【MySQL有索引但未命中的情况？】

  1. 没有符合最左前缀原则。
  2. 字段进行了隐式类型转换。
  3. 走索引没有全表扫描效率高。

【数据库的大表查询优化了解吗？】

【有考虑过大数据量优化的情况吗？】

【mysql慢语句调优做过吗？】

【MySQL 对于千万级的大表要怎么优化？】

MySQL 对于千万级的大表要怎么优化

【NoSQL？】

NoSQL主要用于与关系型数据库互补：

  • KV存储：Redis等，有快的多的读写性能。
  • 列式存储数据库：Hbase等，用于离线数据统计的场景。
  • 文档型数据库：MongoDB等，Schema Free 模式自由，字段任意扩展。

数据库大多使用的机械磁盘，机械磁盘的访问方式有两种：

  • 随机 IO：需要花费时间做昂贵的磁盘寻道，读写效率要比顺序 IO 小两到三个数量级，要尽量减少随机 IO
  • 顺序 IO

比如 MySQL 的 InnoDB ，更新 binlog、redolog、undolog 都是在做顺序 IO，而更新 datafile 和索引文件则是在做随机 IO。为了减少随机 IO 的发生，关系数据库做了很多的优化，比如写入时先写入内存，然后批量刷新到磁盘上，但是随机 IO 还是会发生。

索引在 InnoDB 引擎中是以 B+ 树方式来组织的，而 MySQL 主键是聚簇索引（一种索引类型，数据与索引数据放在一起），既然数据和索引数据放在一起，那么在数据插入或者更新的时候，我们需要找到要插入的位置，再把数据写到特定的位置上，这就产生了随机的 IO。而且一旦发生了页分裂，就不可避免会做数据的移动，也会极大地损耗写入性能。

NoSQL 数据库是怎么解决这个问题的呢？

LSM树

互补场景举例：

  1. 电商平台提供快速根据商品名称找到对应商品：数据库很难使用索引模糊匹配，使用Elasticsearch的倒排索引
  2. 商品的评论系统：数据增长极快，数据库很难扩展，使用MongoDB。

MongoDB的三个扩展性：

  • 其一是 Replica，也叫做副本集，你可以理解为主从分离，也就是通过将数据拷贝成多份来保证当主挂掉后数据不会丢失。同时呢，Replica 还可以分担读请求。Replica 中有主节点来承担写请求，并且把对数据变动记录到 oplog 里（类似于 binlog）；从节点接收到 oplog 后就会修改自身的数据以保持和主节点的一致。一旦主节点挂掉，MongoDB 会从从节点中选取一个节点成为主节点，可以继续提供写数据服务。
  • 其二是 Shard，也叫做分片，你可以理解为分库分表，即将数据按照某种规则拆分成多份，存储在不同的机器上。MongoDB 的 Sharding 特性一般需要三个角色来支持，一个是 Shard Server，它是实际存储数据的节点，是一个独立的 Mongod 进程；二是 Config Server，也是一组 Mongod 进程，主要存储一些元信息，比如说哪些分片存储了哪些数据等；最后是 Route Server，它不实际存储数据，仅仅作为路由使用，它从 Config Server 中获取元信息后，将请求路由到正确的 Shard Server 中。
  • 其三是负载均衡，就是当 MongoDB 发现 Shard 之间数据分布不均匀，会启动 Balancer 进程对数据做重新的分配，最终让不同 Shard Server 的数据可以尽量的均衡。当我们的 Shard Server 存储空间不足需要扩容时，数据会自动被移动到新的 Shard Server 上，减少了数据迁移和验证的成本。

3.7 读写分离和主从复制

【读写分离？】

背景：大部分软件系统的场景都是读多写少，将读写流量分开，方便对读操作进行性能扩展。

实现：数据库拷贝为多分，其中主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

难点：

  1. 主从复制：主从库之间数据的拷贝。
  2. 屏蔽主从库分离带来的访问数据库方式的变化，降低开发难度。

读写分离能提高性能的原因在于：

  • 主从服务器负责各自的读和写，写请求的锁不影响读请求，极大程度缓解了锁的争用；
  • 从服务器可以扩展，一主多从来应对读流量较大的场景。
  • 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
  • 增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

img

【数据库主从复制原理？】

MySQL依赖binlog实现，binlog以二进制形式的日志文件保存MySQL的数据变化，主从复制就是把binlog从主库异步传输到从库。

  1. 主库的更新事件（update、insert、delete）被写到binlog。
  2. 主库创建一个binlog dump thread线程，把binlog的内容发送到从库。
  3. 从库创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log。
  4. 从库还会创建一个SQL线程，从relay log里面读取内容写入到从库，实现主从一致。

使用dump线程异步传输，避免影响主库正常使用，从库先写入relay log，避免回库过于耗时导致主从库的数据延迟过大。

从库数量增加，连接的IO线程也增多，主库也需要创建相同数量的dump线程处理复制请求，实际使用中通常1个主库最多挂3~5个从库。

【复制方式分类？】

  1. 异步复制（默认） 主库写入binlog日志后即可成功返回客户端，无须等待binlog日志传递给从库的过程，但是一旦主库宕机，就有可能出现丢失数据的情况。
  2. 半同步复制（ 5.5版本之后）： （安装半同步复制插件）确保从库接收完成主库传递过来的binlog内容已经写入到自己的relay log（传送log）后才会通知主库上面的等待线程。如果等待超时，则关闭半同步复制，并自动转换为异步复制模式，直到至少有一台从库通知主库已经接收到binlog信息为止。

【主从复制的三个线程？】

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

  • binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
  • I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
  • SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

img

【如何保证数据库主从一致性？】

【主从库的数据延迟怎么应对？】

排查问题容易忽略，从数据库获取不到数据，有时会纠结于是否代码逻辑有问题，有错误的删除操作等，但过段时间又可以查到。核心思想是尽量不去从库查询数据。

  1. 数据冗余：比如一次写操作，尽量减少再多加几次读操作的行为，适当冗余数据。足够简单但也会让单次操作数据变大。
  2. 缓存：同步数据库时，将部分数据存入缓存，后续操作直接读取缓存数据。适合数据新增的场景，数据更新时因为多线程操作可能会造成数据不一致。
  3. 查询主库：但要确认查询的量级在主库的承受范围内。

【主从的模式有哪些？实现同步的方式？】

3.8 分库分表

【数据规模不断增长，到单表千万甚至亿级别后如何优化？】

问题：

  1. 这种情况即使使用了索引，索引占用的空间也随着数据量的增长而增大，数据库就无法缓存全量的索引信息，那么就需要从磁盘上读取索引数据，影响到查询的性能。
  2. 数据量的增加也占据了磁盘的空间，数据库在备份和恢复的时间变长， 如何让数据库系统支持如此大的数据量？
  3. 不同模块的数据，比如用户数据和用户关系数据，全都存储在一个主库中，一旦主库发生故障，所有的模块儿都会受到影响， 如何做到不同模块的故障隔离呢？
  4. 数据库对于写入性能要弱于数据查询的能力，那么随着系统写入请求量的增长， 数据库系统如何来处理更高的并发写入请求呢？

以下是一些优化策略：

  1. 使用缓存： 使用缓存技术，如Redis或Memcached，将频繁查询的数据缓存起来，减少对数据库的访问次数。特别是对于热点数据，缓存可以显著提高访问速度。
  2. 数据分片： 将数据按照一定的规则分散到不同的数据库节点上，通过分片技术实现数据的水平拆分，减轻单个数据库的负载压力，提高并行查询能力。
    • 分区和分表： 将数据按照一定的规则分散到不同的表或者不同的数据库实例中，可以减少单表的数据量，提高查询效率。时间范围、地理位置等都可以作为分区的依据。
    • 垂直拆分与水平拆分： 根据业务需求，对数据库进行垂直拆分（按列拆分）或水平拆分（按行拆分），将数据存储到不同的表或不同的数据库中，从而提高查询效率。
  3. 优化查询语句和索引： 重新审视查询语句，确保它们充分利用了索引，避免全表扫描。可以考虑创建覆盖索引、组合索引等来优化查询性能。
  4. 定期数据清理和优化： 定期清理无用数据、优化数据库表结构、重新组织索引等操作，保持数据库的良好性能。

【分库分表？】

主从复制是数据全量的拷贝到多个节点，分库分表则每个节点只保存部分数据。既解决了单点数据存储瓶颈，又提升了查询性能。但数据被拆分后，单库单表查询也变成了请求多个数据节点的查询。

  • 实现类别：水平切分和垂直切分
  • 数据分片策略/路由策略
  • 分布式事务与一致性

【水平切分和垂直切分？】

  • 垂直切分：关注业务相关性
    • 数据库层面将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。无法解决单个业务模块数据膨胀的问题。
    • 表层面将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。
  • 水平切分：又称为 Sharding，将单一数据表按规则拆分到不同库和表中，关注数据的特征。当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

【水平切分的策略？存在的问题？】

切分策略有：

  • 哈希取模：hash(key) % N；适合实体表，比如用户表，根据UserId来拆分。
  • 范围/区分：可以是 ID 范围也可以是时间范围；但存在明显的热点，某些时间数据多，并且要提前建表。
  • 映射表：使用单独的一个数据库来存储映射关系。

存在的问题：

  1. 数据访问方式改变：查询前要先确认数据在哪些分库分表中，再进行查询。
  2. 事务问题：使用分布式事务来解决，比如 XA 接口。
  3. 多表连接：可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接（应用层实现）。聚合类如count操作可以比如单独维护一张计数表或者缓存。
  4. ID唯一性：注意尽量不要使用业务字段，即使用也要斟酌是否能保证唯一且不变
    • 使用全局唯一 ID（UUID，Universally Unique Identifier，通用唯一标识码）：
      • 使用场景：一般用于生成 Request ID 来标记单次请求。
      • 缺点：
        1. 随机字符串，不具有业务意义。
        2. 32个16进制组成，比较耗费空间。
        3. 作为分区键其不是有序的，有排序需求时需要额外的字段辅助。
        4. 并且有序ID可以提升写入性能（B+树，无序需要先查询索引位置，并且磁盘顺序写无需寻道）。
    • 为每个分片指定一个 ID 范围
    • 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)：
      • 将64bit的二进制数字分成若干部分，每个部分代表特定的含义，如时间戳、机器ID、序列号，比如0（恒定） + 41位时间 + 6位IDC信息（支持 64 个 IDC） + 6位业务信息（支持 64 个业务） + 10位自增信息（每毫秒支持 1024 个号）。
      • 实现方式：
        1. 嵌入到代码中，即分布在业务服务器中。优点是不需要网络调用，但需要更多的机器ID来支持更多业务服务器，同时需要引入 ZooKeeper 等分布式一致性组件来保证每次机器重启时都能获得唯一的机器 ID。
        2. 作为独立的服务部署，即发号器服务。需要网络调用，但内网环境下可以减少机器ID的位数。因为发号器部署实例数有限，那么就可以把机器 ID 写在发号器的配置文件里，这样即可以保证机器 ID 唯一性，也无需引入第三方组件了。 微博和美图都是使用独立服务的方式来部署发号器的，性能上单实例单 CPU 可以达到两万每秒。
        3. 依赖系统的时间戳，当系统时间不准时，发号器暂时拒绝发号。如果请求发号QPS不高，会造成ID的末位永远是1，分配不均匀。可以让时间戳不记录毫秒而是秒，生成的序列号的起始可以随机一下。

【如何保证分库分表后ID的全局唯一性】

待整理

【你们数据库的高可用架构是怎么样的？】

【如何保障mysql和redis之间的数据一致性？】

如何保障mysql和redis之间的数据一致性

四. 分布式事务

【XA方案？】

有一个事务管理器的概念，负责协调多个数据库（资源管理器）的事务 不适合高并发场景，严重依赖数据库层面，同步阻塞问题；协调者故障则所有参与者会阻塞

【TCC方案？】

严重依赖代码补偿和回滚，一般银行用，和钱相关的支付、交易等相关的场景，我们会用TCC Try，对各个服务的资源做检测，对资源进行锁定或者预留 Confirm，在各个服务中执行实际的操作 Cancel，如果任何一个服务的业务方法执行出错，那么这里就需要进行补偿，即执行已操作成功的业务逻辑的回滚操作

【可靠消息最终一致性方案？】

  1. 本地消息服务 本地消息表其实是国外的 ebay 搞出来的这么一套思想。 主动方是认证服务，有个消息异常处理系统，mq，还有消息消费端应用系统，还有采集服务；

在我认证返回数据中如果有发票是已经认证的，在处理认证数据的操作与发送消息在同一个本地事务中，业务执行完，消息数据也同时存在一条待确认的数据；

发送消息给mq，mq发送消息给消息消费端服务，同时存一份消息数据，然后发送给采集服务，进行抵账表更新操作；

采集服务逻辑处理完以后反馈给消息消费端服务，其服务删除消息数据，同时通知认证服务，把消息记录改为已确认成功费状态；

对于异常流程，消息异常处理系统会查询认证服务中过期未确认的消息发送给mq，相当于重试

  1. 独立消息最终一致性方案： A 主动方应用系统，B消息服务子系统，C消息状态确认子系统，C2消息管理子系统 D 消息恢复子系统，mq ，消息消费端E ，被动系统F
1
2
3
4
5
6
7
8
9
 流程：
A预发送消息给B，然后执行A业务逻辑，B存储预发送消息，A执行完业务逻辑发送业务操作结果给B，B更新预发送消息为确认并发送消息状态同时发送消息给mq，然后被E监听然后发送给F消费掉
C：对预发送消息异常的处理，去查询待确认状态超时的消息，去A中查询进行数据处理，如果A中业务处理成功了，那么C需改消息状态为确认并发送状态，然后发送消息给mq；如果A中业务处理失败了..那么C直接把消息删除即可.
C2 ： 查询消息的页面，对消息的可视化，以及批量处理死亡消息；
D： B给mq放入数据如果失败，，通过D去重试，多次重试失败，消息设置为死亡 
E：确保F执行完成，发送消息给B删除消息
优化建议： 
 （1）数据库：如果用redis，持久化要配置成appendfsync always，确保每次新添加消息都能持久化进磁盘
 （2）在被动方应用业务幂等性判断比较麻烦或者比较耗性能情况下，增加消息日志记录表.用于判断之前有无发送过；

【最大努力通知性（定期校对）？】

  1. 业务主动方完成业务处理之后，设置时间阶梯型通知规则向业务活动的被动方发送消息，允许消息丢失.
  2. 被动方根据定时策略，向主动方查询，恢复丢失的业务消息
  3. 被动方的处理结果不影响主动方的处理结果
  4. 需增加业务查询，通知服务，校对系统服务的建设成本
  5. 适用于对业务最终一致性的时间敏感度低，跨企业的业务通知活动
  6. 比如银行通知，商户通知，交易业务平台间商户通知，多次通知，查询校对等

【Seata（阿里） ？】

应用层基于SQL解析实现了自动补偿，从而最大程度的降低业务侵入性； 将分布式事务中TC（事务协调者）独立部署，负责事务的注册、回滚； 通过全局锁实现了写隔离与读隔离。

MySQL 八股文主要看《高性能 MySQL》就可以了，InnoDB 深入的话再看一下《MySQL技术内幕》，底层数据结构的话可以看一下《算法》中二叉搜索树、AVL、二三树、红黑树、B 树的相关章节。

MySQL 八股文 应用篇 1

  • 使用什么存储引擎比较多？有什么特点？
  • 用不用索引？怎么用？
  • 事务特性，详细讲一下？
  • ACID 中 I 有几种级别？
  • 说一说使用的存储引擎使用哪种隔离级别？
  • 如何检测慢查询？
  • EXPLAIN 怎么用？有什么关键字？
  • 如何优化慢查询？

MySQL 八股文 原理篇 1

  • InnoDB 底层使用什么数据结构？
  • InnoDB 和 MyISAM 底层使用的数据结构有什么不同？
  • B 树和 B+ 树有什么区别？
  • 为什么不用 AVL、红黑树、跳表？
  • InnoDB 怎么建索引的？
  • 为什么索引要满足最左匹配？
  • 为什么 MySQL 在建表的时候要使用更紧凑的数据结构？

MySQL 八股文 应用篇 2

  • InnoDB 中有哪些锁？分别讲一下？
  • 行锁和表锁分别有什么特点？
  • MySQL 是如何实现乐观锁和悲观锁的？
  • 不使用锁如何实现并发？
  • 锁锁住的是什么？

MySQL 八股文 应用篇 3

  • 两个事务并发读的过程是什么？
  • 两个事务并发写的过程是什么？
  • 两个事务一个读，一个写会发生什么？
  • InnoDB 中的 MVCC 是什么？解决了什么问题？

MySQL 八股文 应用篇 3

  • MySQL 是如何保证 crash-safe 的？
  • Redo Log 的流程是什么？
  • （上一个引申）讲一讲分布式事务？2PC 是什么？
  • 有 Redo Log 一定能保证 crash-safe 吗？什么情况下数据会丢失？

MySQL 八股文 应用篇 4

  • Binlog 是做什么的？
  • Binlog 在什么阶段写入？
  • Binlog 在 crash-safe 中充当什么角色？
  • 主从同步的流程是什么？
  • Binlog、Redo Log、Undo Log、Relay Log 之间有什么区别？

-————————————–

参考：

🔗 高并发系统设计 40 问

相关文章