Skip to content

Latest commit

 

History

History
1072 lines (677 loc) · 53.1 KB

sql-interview.md

File metadata and controls

1072 lines (677 loc) · 53.1 KB
Raw

关系型数据库面试题

一、索引和约束

什么是索引

索引是对数据库表中一或多个列的值进行排序的结构，是帮助数据库高效查询数据的数据结构。

索引的优缺点

✔ 索引的优点：

  • 索引大大减少了服务器需要扫描的数据量，从而加快检索速度。
  • 支持行级锁的数据库，如 InnoDB 会在访问行的时候加锁。使用索引可以减少访问的行数，从而减少锁的竞争，提高并发。
  • 索引可以帮助服务器避免排序和临时表。
  • 索引可以将随机 I/O 变为顺序 I/O。
  • 唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

❌ 索引的缺点：

  • 创建和维护索引要耗费时间，这会随着数据量的增加而增加。
  • 索引需要占用额外的物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立组合索引那么需要的空间就会更大。
  • 写操作（INSERT/UPDATE/DELETE）时很可能需要更新索引，导致数据库的写操作性能降低。

何时使用索引

索引能够轻易将查询性能提升几个数量级。

✔ 什么情况适用索引：

  • 表经常进行 SELECT 操作；
  • 表的数据量比较大；
  • 列名经常出现在 WHERE 或连接（JOIN）条件中

❌ 什么情况不适用索引：

  • 频繁写操作（ INSERT/UPDATE/DELETE ）- 需要更新索引空间；
  • 非常小的表 - 对于非常小的表，大部分情况下简单的全表扫描更高效。
  • 列名不经常出现在 WHERE 或连接（JOIN）条件中 - 索引就会经常不命中，没有意义，还增加空间开销。
  • 对于特大型表，建立和使用索引的代价将随之增长。可以考虑使用分区技术或 Nosql。

索引的类型

主流的关系型数据库一般都支持以下索引类型：

从逻辑类型上划分（即一般创建表时设置的索引类型）：

  • 唯一索引（UNIQUE）：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  • 主键索引（PRIMARY）：一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。
  • 普通索引（INDEX）：最基本的索引，没有任何限制。
  • 组合索引：多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

从物理存储上划分：

  • 聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个。
  • 非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序，也可以视为二级索引。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于 249 个。

索引的数据结构

主流数据库的索引一般使用的数据结构为：B 树、B+ 树。

B 树

一棵 M 阶的 B-Tree 满足以下条件：

  • 每个结点至多有 M 个孩子；
  • 除根结点和叶结点外，其它每个结点至少有 M/2 个孩子；
  • 根结点至少有两个孩子（除非该树仅包含一个结点）；
  • 所有叶结点在同一层，叶结点不包含任何关键字信息；
  • 有 K 个关键字的非叶结点恰好包含 K+1 个孩子；

对于任意结点，其内部的关键字 Key 是升序排列的。每个节点中都包含了 data。

对于每个结点，主要包含一个关键字数组 Key[]，一个指针数组（指向儿子）Son[]。

在 B-Tree 内，查找的流程是：

  1. 使用顺序查找（数组长度较短时）或折半查找方法查找 Key[] 数组，若找到关键字 K，则返回该结点的地址及 K 在 Key[] 中的位置；
  2. 否则，可确定 K 在某个 Key[i] 和 Key[i+1] 之间，则从 Son[i] 所指的子结点继续查找，直到在某结点中查找成功；
  3. 或直至找到叶结点且叶结点中的查找仍不成功时，查找过程失败。

B+ 树

B+Tree 是 B-Tree 的变种：

  • 每个节点的指针上限为 2d 而不是 2d+1（d 为节点的出度）。
  • 非叶子节点不存储 data，只存储 key；叶子节点不存储指针。

由于并不是所有节点都具有相同的域，因此 B+Tree 中叶节点和内节点一般大小不同。这点与 B-Tree 不同，虽然 B-Tree 中不同节点存放的 key 和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中 B-Tree 往往对每个节点申请同等大小的空间。

带有顺序访问指针的 B+Tree

一般在数据库系统或文件系统中使用的 B+Tree 结构都在经典 B+Tree 的基础上进行了优化，增加了顺序访问指针。

在 B+Tree 的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的 B+Tree。

这个优化的目的是为了提高区间访问的性能，例如上图中如果要查询 key 为从 18 到 49 的所有数据记录，当找到 18 后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

B 树 vs. B+ 树

  • B+ 树更适合外部存储(一般指磁盘存储)，由于内节点(非叶子节点)不存储 data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用 B+ 树单次磁盘 IO 的信息量相比较 B 树更大，IO 效率更高。
  • Mysql 是关系型数据库，经常会按照区间来访问某个索引列，B+ 树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以 B+ 树对索引列上的区间范围查询很友好。而 B 树每个节点的 key 和 data 在一起，无法进行区间查找。

Hash

Hash 索引只有精确匹配索引所有列的查询才有效。

对于每一行数据，对所有的索引列计算一个 hashcode。哈希索引将所有的 hashcode 存储在索引中，同时在 Hash 表中保存指向每个数据行的指针。

哈希结构索引的优点：

  • 因为索引数据结构紧凑，所以查询速度非常快。

哈希结构索引的缺点：

  • 哈希索引数据不是按照索引值顺序存储的，所以无法用于排序。
  • 哈希索引不支持部分索引匹配查找。如，在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A，无法使用该索引。
  • 哈希索引只支持等值比较查询，不支持任何范围查询，如 WHERE price > 100。
  • 哈希索引有可能出现哈希冲突，出现哈希冲突时，必须遍历链表中所有的行指针，逐行比较，直到找到符合条件的行。

索引策略

索引基本原则

  • 索引不是越多越好，不要为所有列都创建索引。
  • 要尽量避免冗余和重复索引；
  • 要考虑删除未使用的索引；
  • 尽量的扩展索引，不要新建索引；
  • 频繁作为 WHERE 过滤条件的列应该考虑添加索引

独立的列

如果查询中的列不是独立的列，则数据库不会使用索引。

“独立的列” 是指索引列不能是表达式的一部分，也不能是函数的参数。

❌ 错误示例：

SELECT actor_id FROM actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(current_date) - TO_DAYS(date_col) <= 10;

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。

解决方法是：可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。

索引的选择性是指：不重复的索引值和数据表记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。

对于 BLOB/TEXT/VARCHAR 这种文本类型的列，必须使用前缀索引，因为数据库往往不允许索引这些列的完整长度。

要选择足够长的前缀以保证较高的选择性，同时又不能太长（节约空间）。

❌ 低效示例：

SELECT COUNT(*) AS cnt, city FROM sakila.city_demo
GROUP BY city ORDER BY cnt DESC LIMIT 10;

✔ 高效示例：

SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref FROM sakila.city_demo
GROUP BY city ORDER BY cnt DESC LIMIT 10;

多列索引

不要为每个列都创建独立索引。

将选择性高的列或基数大的列优先排在多列索引最前列。但有时，也需要考虑 WHERE 子句中的排序、分组和范围条件等因素，这些因素也会对查询性能造成较大影响。

举例来说，有一张 user 表，其中含 name, sex, age 三个列，如果将这三者组合为多列索引，应该用什么样的顺序呢？从选择性高的角度来看：name > age > sex。

聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储方式。具体细节依赖于实现方式。如 InnoDB 的聚簇索引实际是在同一个结构中保存了 B 树的索引和数据行。

聚簇表示数据行和相邻的键值紧凑地存储在一起，因为数据紧凑，所以访问快。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

若没有定义主键，InnoDB 会隐式定义一个主键来作为聚簇索引。

覆盖索引

索引包含所有需要查询的字段的值。

具有以下优点：

  • 因为索引条目通常远小于数据行的大小，所以若只读取索引，能大大减少数据访问量。
  • 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
  • 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

使用索引扫描来做排序

Mysql 有两种方式可以生成排序结果：通过排序操作；或者按索引顺序扫描。

索引最好既满足排序，又用于查找行。这样，就可以使用索引来对结果排序。

最左前缀匹配原则

MySQL 会一直向右匹配直到遇到范围查询 (>,<,BETWEEN,LIKE) 就停止匹配。

  • 索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。
  • 如果是联合索引，那么 key 也由多个列组成，同时，索引只能用于查找 key 是否存在（相等），遇到范围查询(>、<、between、like 左匹配)等就不能进一步匹配了，后续退化为线性查找。
  • 因此，列的排列顺序决定了可命中索引的列数。

例子：

  • 如有索引(a, b, c, d)，查询条件 a = 1 and b = 2 and c > 3 and d = 4，则会在每个节点依次命中 a、b、c，无法命中 d。(很简单：索引命中只能是相等的情况，不能是范围匹配)

= 和 in 可以乱序

不需要考虑=、in 等的顺序，Mysql 会自动优化这些条件的顺序，以匹配尽可能多的索引列。

例子：如有索引(a, b, c, d)，查询条件 c > 3 and b = 2 and a = 1 and d < 4 与 a = 1 and c > 3 and b = 2 and d < 4 等顺序都是可以的，MySQL 会自动优化为 a = 1 and b = 2 and c > 3 and d < 4，依次命中 a、b、c。

约束

数据库约束（CONSTRAINT）有哪些：

  • NOT NULL - 用于控制字段的内容一定不能为空（NULL）。
  • UNIQUE - 字段内容不能重复，一个表允许有多个 Unique 约束。
  • PRIMARY KEY - 数据表中对储存数据对象予以唯一和完整标识的数据列或属性的组合，它在一个表中只允许有一个。主键的取值不能为空值（Null）。
  • FOREIGN KEY - 在一个表中存在的另一个表的主键称此表的外键。用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
  • CHECK - 用于控制字段的值范围。

二、并发控制

乐观锁和悲观锁

  • 数据库的乐观锁和悲观锁是什么？
  • 数据库的乐观锁和悲观锁如何实现？

确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。

  • 悲观锁 - 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
    • 在查询完数据的时候就把事务锁起来，直到提交事务（COMMIT）
    • 实现方式：使用数据库中的锁机制
  • 乐观锁 - 假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
    • 在修改数据的时候把事务锁起来，通过 version 的方式来进行锁定
    • 实现方式：使用 version 版本或者时间戳

行级锁和表级锁

  • 什么是行级锁和表级锁？
  • 什么时候用行级锁？什么时候用表级锁？

从数据库的锁粒度来看，MySQL 中提供了两种封锁粒度：行级锁和表级锁。

  • 表级锁（table lock） - 锁定整张表。用户对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他用户才能获得读锁，读锁之间不会相互阻塞。
  • 行级锁（row lock） - 仅对指定的行记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。

二者需要权衡：

  • 锁定的数据量越少，锁竞争的发生频率就越小，系统的并发程度就越高。
  • 锁粒度越小，系统开销就越大。

在 InnoDB 中，行锁是通过给索引上的索引项加锁来实现的。如果没有索引，InnoDB 将会通过隐藏的聚簇索引来对记录加锁。

读写锁

  • 什么是读写锁？
  • 独享锁（Exclusive），简写为 X 锁，又称写锁。使用方式：SELECT ... FOR UPDATE;
  • 共享锁（Shared），简写为 S 锁，又称读锁。使用方式：SELECT ... LOCK IN SHARE MODE;

写锁和读锁的关系，简言之：独享锁存在，其他事务就不能做任何操作。

InnoDB 下的行锁、间隙锁、next-key 锁统统属于独享锁。

意向锁

  • 什么是意向锁？
  • 意向锁有什么用？

意向锁的作用是：当存在表级锁和行级锁的情况下，必须先申请意向锁（表级锁，但不是真的加锁），再获取行级锁。使用意向锁（Intention Locks）可以更容易地支持多粒度封锁。

意向锁是 InnoDB 自动加的，不需要用户干预。

MVCC

什么是 MVCC？

MVCC 有什么用？解决了什么问题？

MVCC 的原理是什么？

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

MVCC 的思想是：

  • 保存数据在某个时间点的快照。写操作（DELETE、INSERT、UPDATE）更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和 CopyOnWrite 类似。
  • 脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读。

Next-key 锁

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

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

另外，根据针对 SQL 语句检索条件的不同，加锁又有以下三种情形需要我们掌握。

  • Record Lock - 行锁对索引项加锁，若没有索引则使用表锁。
  • Gap Lock - 对索引项之间的间隙加锁。锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;
  • Next-key lock -它是 Record Lock 和 Gap Lock 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间。

索引分为主键索引和非主键索引两种，如果一条 SQL 语句操作了主键索引，MySQL 就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL 会先锁定该非主键索引，再锁定相关的主键索引。在 UPDATE、DELETE 操作时，MySQL 不仅锁定 WHERE 条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的 next-key lock。

当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。发生死锁后，InnoDB 一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

三、事务

事务简单来说：一个 Session 中所进行所有的操作，要么同时成功，要么同时失败。具体来说，事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

img

ACID

ACID — 数据库事务正确执行的四个基本要素：

  • 原子性（Atomicity）
  • 一致性（Consistency）
  • 隔离性（Isolation）
  • 持久性（Durability）

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易。

img

并发一致性问题

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。

  • 丢失修改

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

img

  • 脏读

T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

img

  • 不可重复读

T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

img

  • 幻读

T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

img

并发一致性解决方案：

产生并发不一致性问题主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。

并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题。

事务隔离

数据库隔离级别：

  • 未提交读（READ UNCOMMITTED） - 事务中的修改，即使没有提交，对其它事务也是可见的。
  • 提交读（READ COMMITTED） - 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
  • 重复读（REPEATABLE READ） - 保证在同一个事务中多次读取同样数据的结果是一样的。
  • 串行化（SERIALIXABLE） - 强制事务串行执行。

数据库隔离级别解决的问题：

隔离级别 脏读 不可重复读 幻读
未提交读 ❌ ❌ ❌
提交读 ✔️ ❌ ❌
可重复读 ✔️ ✔️ ❌
可串行化 ✔️ ✔️ ✔️

分布式事务

在单一数据节点中，事务仅限于对单一数据库资源的访问控制，称之为 本地事务。几乎所有的成熟的关系型数据库都提供了对本地事务的原生支持。

分布式事务 是指事务的参与者、支持事务的服务器、资源服务器以及事务管理器分别位于不同的分布式系统的不同节点之上。

两阶段提交

两阶段提交（XA）对业务侵入很小。 它最大的优势就是对使用方透明，用户可以像使用本地事务一样使用基于 XA 协议的分布式事务。 XA 协议能够严格保障事务 ACID 特性。

严格保障事务 ACID 特性是一把双刃剑。 事务执行在过程中需要将所需资源全部锁定，它更加适用于执行时间确定的短事务。 对于长事务来说，整个事务进行期间对数据的独占，将导致对热点数据依赖的业务系统并发性能衰退明显。 因此，在高并发的性能至上场景中，基于 XA 协议的分布式事务并不是最佳选择。

柔性事务

如果将实现了ACID 的事务要素的事务称为刚性事务的话，那么基于BASE事务要素的事务则称为柔性事务。 BASE是基本可用、柔性状态和最终一致性这三个要素的缩写。

  • 基本可用（Basically Available）保证分布式事务参与方不一定同时在线。
  • 柔性状态（Soft state）则允许系统状态更新有一定的延时，这个延时对客户来说不一定能够察觉。
  • 而最终一致性（Eventually consistent）通常是通过消息传递的方式保证系统的最终一致性。

在ACID事务中对隔离性的要求很高，在事务执行过程中，必须将所有的资源锁定。 柔性事务的理念则是通过业务逻辑将互斥锁操作从资源层面上移至业务层面。通过放宽对强一致性要求，来换取系统吞吐量的提升。

基于ACID的强一致性事务和基于BASE的最终一致性事务都不是银弹，只有在最适合的场景中才能发挥它们的最大长处。 可通过下表详细对比它们之间的区别，以帮助开发者进行技术选型。

事务方案对比

本地事务 两（三）阶段事务 柔性事务
业务改造 无 无 实现相关接口
一致性 不支持 支持 最终一致
隔离性 不支持 支持 业务方保证
并发性能 无影响 严重衰退 略微衰退
适合场景 业务方处理不一致 短事务 & 低并发 长事务 & 高并发

四、分库分表

什么是分库分表

什么是分库分表？什么是垂直拆分？什么是水平拆分？什么是 Sharding？

分库分表是为了解决什么问题？

分库分表有什么优点？

分库分表有什么策略？

分库分表的基本思想就是：把原本完整的数据切分成多个部分，放到不同的数据库或表上。

分库分表一定是为了支撑 高并发、数据量大两个问题的。

垂直切分

垂直切分，是 把一个有很多字段的表给拆分成多个表，或者是多个库上去。一般来说，会 将较少的、访问频率较高的字段放到一个表里去，然后 将较多的、访问频率较低的字段放到另外一个表里去。因为数据库是有缓存的，访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多一些。

image-20200114211639899

一般来说，满足下面的条件就可以考虑扩容了：

  • Mysql 单库超过 5000 万条记录，Oracle 单库超过 1 亿条记录，DB 压力就很大。
  • 单库超过每秒 2000 个并发时，而一个健康的单库最好保持在每秒 1000 个并发左右，不要太大。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

水平拆分

水平拆分 又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。当 单表数据量太大 时，会极大影响 SQL 执行的性能 。分表是将原来一张表的数据分布到数据库集群的不同节点上，从而缓解单点的压力。

image-20200114211203589

一般来说，单表有 200 万条数据 的时候，性能就会相对差一些了，需要考虑分表了。但是，这也要视具体情况而定，可能是 100 万条，也可能是 500 万条，SQL 越复杂，就最好让单表行数越少。

分库分表的优点

# 分库分表前 分库分表后
并发支撑情况 单机部署，扛不住高并发 从单机到多机，能承受的并发增加了多倍
磁盘使用情况 单机磁盘容量几乎撑满 拆分为多个库，数据库服务器磁盘使用率大大降低
SQL 执行性能 单表数据量太大，SQL 越跑越慢 单表数据量减少，SQL 执行效率明显提升

分库分表策略

  • 哈希取模：hash(key) % N 或 id % N
    • 优点：可以平均分配每个库的数据量和请求压力（负载均衡）。
    • 缺点：扩容麻烦，需要数据迁移。
  • 范围：可以按照 ID 或时间划分范围。
    • 优点：扩容简单。
    • 缺点：这种策略容易产生热点问题。
  • 映射表：使用单独的一个数据库来存储映射关系。
    • 缺点：存储映射关系的数据库也可能成为性能瓶颈，且一旦宕机，分库分表的数据库就无法工作。所以不建议使用这种策略。
    • 优点：扩容简单，可以解决分布式 ID 问题。

分库分表中间件

❓ 常见问题：

  • 你用过哪些分库分表中间件，简单介绍一下？

  • 不同的分库分表中间件各自有什么特性，有什么优缺点？

  • 分库分表中间件技术如何选型？

常见的分库分表中间件

  • Cobar - 阿里 b2b 团队开发和开源的，属于 proxy 层方案，就是介于应用服务器和数据库服务器之间。应用程序通过 JDBC 驱动访问 cobar 集群，cobar 根据 SQL 和分库规则对 SQL 做分解，然后分发到 MySQL 集群不同的数据库实例上执行。早些年还可以用，但是最近几年都没更新了，基本没啥人用，差不多算是被抛弃的状态吧。而且不支持读写分离、存储过程、跨库 join 和分页等操作。
  • TDDL - 淘宝团队开发的，属于 client 层方案。支持基本的 crud 语法和读写分离，但不支持 join、多表查询等语法。目前使用的也不多，因为还依赖淘宝的 diamond 配置管理系统。
  • Atlas - 360 开源的，属于 proxy 层方案，以前是有一些公司在用的，但是确实有一个很大的问题就是社区最新的维护都在 5 年前了。所以，现在用的公司基本也很少了。
  • sharding-jdbc - 当当开源的，属于 client 层方案。确实之前用的还比较多一些，因为 SQL 语法支持也比较多，没有太多限制，而且目前推出到了 2.0 版本，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）。而且确实之前使用的公司会比较多一些（这个在官网有登记使用的公司，可以看到从 2017 年一直到现在，是有不少公司在用的），目前社区也还一直在开发和维护，还算是比较活跃，个人认为算是一个现在也可以选择的方案。
  • Mycat - 基于 cobar 改造的，属于 proxy 层方案，支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，也有一些公司开始在用了。但是确实相比于 sharding jdbc 来说，年轻一些，经历的锤炼少一些。

分库分表中间件技术选型

建议使用的是 sharding-jdbc 和 mycat。

  • sharding-jdbc 这种 client 层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是如果遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合 sharding-jdbc 的依赖。其本质上通过配置多数据源，然后根据设定的分库分表策略，计算路由，将请求发送到计算得到的节点上。

  • Mycat 这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。

通常来说，这两个方案其实都可以选用，但是我个人建议中小型公司选用 sharding-jdbc，client 层方案轻便，而且维护成本低，不需要额外增派人手，而且中小型公司系统复杂度会低一些，项目也没那么多；但是中大型公司最好还是选用 mycat 这类 proxy 层方案，因为可能大公司系统和项目非常多，团队很大，人员充足，那么最好是专门弄个人来研究和维护 mycat，然后大量项目直接透明使用即可。

分库分表的问题

  • 分库分表的常见问题有哪些？

  • 你是如何解决分库分表的问题的？

下文一一讲解常见分库分表的问题及解决方案。

分布式事务

方案一：使用数据库事务

  • 优点：交由数据库管理，简单有效
  • 缺点：性能代价高，特别是 shard 越来越多时

方案二：由应用程序和数据库共同控制

  • 原理：将一个跨多个数据库的分布式事务分拆成多个仅处于单个数据库上面的小事务，并通过应用程序来总控各个小事务。
  • 优点：性能上有优势
  • 缺点：需要应用程序在事务控制上做灵活设计。如果使用了 spring 的事务管理，改动起来会面临一定的困难。

跨节点 Join

只要是进行切分，跨节点 Join 的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的 id，根据这些 id 发起第二次请求得到关联数据。

跨节点的 count,order by,group by 以及聚合函数

这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。

解决方案：与解决跨节点 join 问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和 join 不同的是每个节点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

业务角度上的解决方案：

  • 如果是在前台应用提供分页，则限定用户只能看前面 n 页，这个限制在业务上也是合理的，一般看后面的分页意义不大（如果一定要看，可以要求用户缩小范围重新查询）。
  • 如果是后台批处理任务要求分批获取数据，则可以加大 page size，比如每次获取 5000 条记录，有效减少分页数（当然离线访问一般走备库，避免冲击主库）。
  • 分库设计时，一般还有配套大数据平台汇总所有分库的记录，有些分页查询可以考虑走大数据平台。

分布式 ID

一旦数据库被切分到多个物理节点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的 ID 无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得 ID，以便进行 SQL 路由。

一些常见的主键生成策略：

  • 使用全局唯一 ID：GUID。
  • 为每个分片指定一个 ID 范围。
  • 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)。

数据迁移，容量规划，扩容等问题

来自淘宝综合业务平台团队，它利用对 2 的倍数取余具有向前兼容的特性（如对 4 取余得 1 的数对 2 取余也是 1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了 Sharding 扩容的难度。

五、集群

这个专题需要根据熟悉哪个数据库而定，但是主流、成熟的数据库都会实现一些基本功能，只是实现方式、策略上有所差异。由于本人较为熟悉 Mysql，所以下面主要介绍 Mysql 系统架构问题。

复制机制

Mysql 支持两种复制：基于行的复制和基于语句的复制。

这两种方式都是在主库上记录二进制日志（binlog），然后在从库上以异步方式更新主库上的日志记录。这意味着：复制过程存在时延，这段时间内，主从数据可能不一致（即最终一致性）。

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

  • binlog 线程 ：负责将主服务器上的数据更改写入二进制文件（binlog）中。
  • I/O 线程 ：负责从主服务器上读取二进制日志文件，并写入从服务器的日志中。
  • SQL 线程 ：负责读取日志并执行 SQL 语句以更新数据。

img

读写分离

主服务器用来处理写操作以及实时性要求比较高的读操作，而从服务器用来处理读操作。

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

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

  • 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
  • 从服务器可以配置 MyISAM 引擎，提升查询性能以及节约系统开销；
  • 增加冗余，提高可用性。

img

六、数据库优化

数据库优化的路线一般为：SQL 优化、结构优化、配置优化、硬件优化。前两个方向一般是普通开发的考量点，而后两个方向一般是 DBA 的考量点。

SQL 优化

SQL 优化是数据库优化的最常见、最初级手段。

在执行 SQL 语句，语句中字段的顺序、查询策略等都可能会影响到 SQL 的执行性能。

执行计划

如何检验修改后的 SQL 确实有优化效果？这就需要用到执行计划（EXPLAIN）。

使用执行计划 EXPLAIN 用来分析 SELECT 查询效率，开发人员可以通过分析 EXPLAIN 结果来优化查询语句。

比较重要的字段有：

  • select_type - 查询类型，有简单查询、联合查询、子查询等
  • key - 使用的索引
  • rows - 扫描的行数

更多内容请参考：MySQL 性能优化神器 Explain 使用分析

访问数据优化

减少请求的数据量：

  • 只返回必要的列 - 不要查询不需要的列，尽量避免使用 SELECT * 语句。
  • 只返回必要的行 - 使用 WHERE 语句进行查询过滤，有时候也需要使用 LIMIT 语句来限制返回的数据。
  • 缓存重复查询的数据 - 使用缓存可以避免在数据库中进行查询，特别要查询的数据经常被重复查询，缓存可以带来的查询性能提升将会是非常明显的。

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

  • 最有效的方式是使用索引来覆盖查询（即 WHERE 后的过滤查询字段最好是索引字段）。

重构查询方式

切分查询

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

DELEFT FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0
分解关联查询

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

  • 缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
  • 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
  • 减少锁竞争；
  • 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可扩展。
  • 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

SQL 语句细节

选择最有效率的表名顺序

数据库按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表将被最先处理。

在 FROM 子句中包含多个表的情况下：

  • 如果多个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推。也就是说：选择记录条数最少的表放在最后。

如果有 3 个以上的表连接查询：

  • 如果多个表是有关系的话，将引用最多的表，放在最后，然后依次类推。也就是说：被其他表所引用的表放在最后。

例如：查询员工的编号，姓名，工资，工资等级，部门名

emp 表被引用得最多，记录数也是最多，因此放在 form 字句的最后面

select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade,dept,emp
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)
WHERE 子句中的连接顺序

数据库按照从右到左的顺序解析 WHERE 子句。

因此，表之间的连接必须写在其他 WHERE 条件的左边，那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的之右。

emp.sal 可以过滤多条记录，写在 WHERE 字句的最右边

select emp.empno,emp.ename,emp.sal,dept.dname
from dept,emp
where (emp.deptno = dept.deptno) and (emp.sal > 1500)
SELECT 子句中避免使用 * 号

我们当时学习的时候，“*” 号是可以获取表中全部的字段数据的。

  • 但是它要通过查询数据字典完成的，这意味着将耗费更多的时间
  • 使用*号写出来的 SQL 语句也不够直观。
用 TRUNCATE 替代 DELETE

如果需要清空所有表记录，使用 TRUNCATE 比 DELETE 执行效率高：

DELETE 是一条一条记录的删除，而 Truncate 是将整个表删除，仅保留表结构

使用内部函数提高 SQL 效率

例如使用 mysql 的 concat() 函数会比使用 || 拼接速度快，因为 concat() 函数已经被 mysql 优化过了。

使用表或列的别名

如果表或列的名称太长了，使用一些简短的别名也能稍微提高一些 SQL 的性能。毕竟要扫描的字符长度就变少了。

SQL 关键字大写

我们在编写 SQL 的时候，官方推荐的是使用大写来写关键字，因为 Oracle 服务器总是先将小写字母转成大写后，才执行

用 >= 替代 >

❌ 低效方式：

-- 首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
SELECT * FROM EMP WHERE DEPTNO > 3

✔ 高效方式：

-- 直接跳到第一个DEPT等于4的记录
SELECT * FROM EMP WHERE DEPTNO >= 4
用 IN 替代 OR

❌ 低效方式：

select * from emp where sal = 1500 or sal = 3000 or sal = 800;

✔ 高效方式：

select * from emp where sal in (1500,3000,800);
总是使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列被 WHERE 子句引用时，优化器才会选择使用该索引。 当只引用索引的第二个列时，不引用索引的第一个列时，优化器使用了全表扫描而忽略了索引

create index emp_sal_job_idex
on emp(sal,job);
----------------------------------
select *
from emp
where job != 'SALES';
SQL 关键字尽量大写

SQL 关键字尽量大写，如：Oracle 默认会将 SQL 语句中的关键字转为大写后在执行。

结构优化

数据库结构优化可以从以下方向着手：

  • 数据类型优化
  • 范式和反范式优化
  • 索引优化 - 细节请看索引和约束章节
  • 分库分表 - 细节请看分库分表章节

数据类型优化原则

  • 更小的通常更好
  • 简单就好，如整型比字符型操作代价低
  • 尽量避免 NULL

范式和反范式

范式和反范式各有利弊，需要根据实际情况权衡。

范式化的目标是尽力减少冗余列，节省空间。

  • 范式化的优点是：

    • 减少冗余列，要写的数据就少，写操作的性能提高；
    • 检索列数据时，DISTINCT 或 GROUP BY 操作减少。

  • 范式化的缺点是：增加关联查询。

反范式化的目标是适当增加冗余列，以避免关联查询。

反范式化的缺点是：

  • 冗余列增多，空间变大，写操作性能下降；
  • 检索列数据时，DISTINCT 或 GROUP BY 操作变多；

配置优化

配置优化主要是针对 Mysql 服务器，例如：max_connections、max_heap_table_size、open_files_limit、max_allowed_packet 等等。

在不同环境，不同场景下，应该酌情使用合理的配置。这种优化比较考验 Mysql 运维经验，一般是 DBA 的考量，普通开发接触的较少。

Mysql 配置说明请参考：Mysql 服务器配置说明

硬件优化

数据库扩容、使用高配设备等等。核心就是一个字：钱。

七、数据库理论

函数依赖

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。

如果 {A1，A2，... ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。

对于 A->B，如果能找到 A 的真子集 A'，使得 A'-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖；

对于 A->B，B->C，则 A->C 是一个传递依赖。

异常

以下的学生课程关系的函数依赖为 Sno, Cname -> Sname, Sdept, Mname, Grade，键码为 {Sno, Cname}。也就是说，确定学生和课程之后，就能确定其它信息。

Sno Sname Sdept Mname Cname Grade
1 学生-1 学院-1 院长-1 课程-1 90
2 学生-2 学院-2 院长-2 课程-2 80
2 学生-2 学院-2 院长-2 课程-1 100
3 学生-3 学院-2 院长-2 课程-2 95

不符合范式的关系，会产生很多异常，主要有以下四种异常：

  • 冗余数据：例如 学生-2 出现了两次。
  • 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
  • 删除异常：删除一个信息，那么也会丢失其它信息。例如如果删除了 课程-1，需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
  • 插入异常，例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

范式

范式理论是为了解决以上提到四种异常。

高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

第一范式 (1NF)

属性不可分。

第二范式 (2NF)

  • 每个非主属性完全函数依赖于键码。

  • 可以通过分解来满足。

分解前

Sno Sname Sdept Mname Cname Grade
1 学生-1 学院-1 院长-1 课程-1 90
2 学生-2 学院-2 院长-2 课程-2 80
2 学生-2 学院-2 院长-2 课程-1 100
3 学生-3 学院-2 院长-2 课程-2 95

以上学生课程关系中，{Sno, Cname} 为键码，有如下函数依赖：

  • Sno -> Sname, Sdept
  • Sdept -> Mname
  • Sno, Cname-> Grade

Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。

Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。

分解后

关系-1

Sno Sname Sdept Mname
1 学生-1 学院-1 院长-1
2 学生-2 学院-2 院长-2
3 学生-3 学院-2 院长-2

有以下函数依赖：

  • Sno -> Sname, Sdept, Mname
  • Sdept -> Mname

关系-2

Sno Cname Grade
1 课程-1 90
2 课程-2 80
2 课程-1 100
3 课程-2 95

有以下函数依赖：

  • Sno, Cname -> Grade

第三范式 (3NF)

  • 非主属性不传递依赖于键码。

上面的 关系-1 中存在以下传递依赖：Sno -> Sdept -> Mname，可以进行以下分解：

关系-11

Sno Sname Sdept
1 学生-1 学院-1
2 学生-2 学院-2
3 学生-3 学院-2

关系-12

Sdept Mname
学院-1 院长-1
学院-2 院长-2

八、Mysql 存储引擎

Mysql 有多种存储引擎，不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在 Mysql 服务层统一处理的。

简单列举几个存储引擎：

  • InnoDB - Mysql 的默认事务型存储引擎，并提供了行级锁和外键的约束。性能不错且支持自动故障恢复。
  • MyISAM - Mysql 5.1 版本前的默认存储引擎。特性丰富但不支持事务，也不支持行级锁和外键，也没有故障恢复功能。
  • CSV - 可以将 CSV 文件作为 Mysql 的表来处理，但这种表不支持索引。
  • MEMORY 。所有的数据都在内存中，数据的处理速度快，但是安全性不高。

InnoDB vs. MyISAM

InnoDB 和 MyISAM 是目前使用的最多的两种 Mysql 存储引擎。

  • 数据结构比较：
    • InnoDB 和 MyISAM 的索引数据结构都是 B+ 树。
    • MyIASM 的 B+ 树中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
    • InnoDB 的 B+ 树中存储的内容是实际的数据，这种索引有被称为聚集索引。
  • 事务支持比较：
    • InnoDB 支持事务，并提供了行级锁和外键的约束。
    • MyIASM 不支持事务，也不支持行级锁和外键。
  • 故障恢复比较：
    • InnoDB 支持故障恢复。
    • MyISAM 不支持故障恢复。

九、数据库比较

常见数据库比较

  • Oracle - 久负盛名的商业数据库。功能强大、稳定。最大的缺点就是费钱。
  • Mysql - 曾经是互联网公司的最爱，但自动 Mysql 被 Oracle 公司收购后，好日子可能一去不复返。很多公司或开源项目已经逐渐寻找其他的开源产品来替代 Mysql。
  • MariaDB - 开源关系型数据库。 MySQL 的真正开源的发行版本，由 Mysql 部分核心人员创建。可作为 Mysql 的替代产品。
  • PostgreSQL - 开源关系型数据库。和 MySQL 的工作方式非常相似，社区支持做得很好。可作为 Mysql 的替代产品。
  • SQLite - 开源的轻量级数据库，移动端常常使用。
  • H2 - 内存数据库，一般用作开发、测试环境数据库。
  • SQL Server - 微软 Windows 生态系统的数据库。我想，Java 程序员应该没人用吧。

Oracle vs. Mysql

目前为止，Java 领域用的最多的关系型数据库，应该还是 Oracle 和 Mysql，所以这里做一下比较。

数据库对象差异

在 Mysql 中，一个用户可以创建多个库。

而在 Oracle 中，Oracle 服务器是由两部分组成

  • 数据库实例【理解为对象，看不见的】
  • 数据库【理解为类，看得见的】

一个数据库实例可拥有多个用户，一个用户默认拥有一个表空间。

表空间是存储我们数据库表的地方，表空间内可以有多个文件。

SQL 差异

（1）主键递增

Mysql 可以设置 AUTO_INCREMENT 约束来指定主键为自增序列。

Oracle 需要通过 CREATE SEQUENCE 创建序列。

（2）分页查询

Mysql 分页基于 SELECT ... FROM ... LIMIT ... 完成，较简单。

select * from help_category order by parent_category_id limit 10,5;

Oracle 分页基于 SELECT ... FROM (SELECT ROWNUM ...) WHERE ... 完成，较复杂。

select * from
(select rownum rr,a.* from (select * from emp order by sal) a )
where rr>5 and rr<=10;

事务差异

  • auto commit
    • Mysql 事务是 autocommit 模式，即自动提交事务；
    • Oracle 事务需要手动 COMMIT。
  • 事务隔离级别
    • Mysql 默认的事务隔离级别是可重复读（REPEATABLE READ）
    • Oracle 支持读已提交（READ COMMITTED）和串行化（SERIALIZABLE） 两种事务隔离级别，默认事务隔离级别是读已提交（READ COMMITTED）

数据类型比较

不同数据库中，对数据类型的支持是不一样的。

即使存在同一种数据类型，也可能存在名称不同、或大小不同等问题。

因此，对于数据类型的支持详情必须参考各数据库的官方文档。

下面列举一些常见数据类型对比：

数据类型 Oracle MySQL PostgreSQL
boolean Byte N/A Boolean
integer Number Int Integer Int Integer
float Number Float Numeric
currency N/A N/A Money
string (fixed) Char Char Char
string (variable) Varchar Varchar2 Varchar Varchar
binary object Long Raw Blob Text Binary Varbinary

数据类型对比表摘自 SQL 通用数据类型、SQL 用于各种数据库的数据类型

参考资料