2021面试准备-数据库

3.数据库

3.1 SQL 之连接查询(必会)

1**.左连接**（左外连接）以左表为基准进行查询,左表数据会全部显示出 来, 右表 如果和左表匹配 的数据则显示相应字段的数据,如果不匹配,则 显示为NULL;

2**.右连接**（右外连接）以右表为基准进行查询,右表数据会全部显示出 来, 右表 如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则 显示为NULL;

3.全连接就是先以左表进行左外连接，然后以右表进行右外连接。

4.内连接： 显示表之间有连接匹配的所有行。

3.2 SQL 之聚合函数(必会)

聚合函数是对一组值进行计算并返回单一的值的函数，它经常与 select 语句中的 group by 子句一同使用。

1. avg()：返回的是指定组中的平均值，空值被忽略。
2. count()：返回的是指定组中的项目个数。
3. max()：返回指定数据中的最大值。
4. min()：返回指定数据中的最小值。
5. sum()：返回指定数据的和，只能用于数字列，空值忽略。
6. group by()：对数据进行分组，对执行完 group by 之后的组进行聚 合函数的运算，计算每一组的值。最后用 having 去掉不符合条件的 组，having 子句中的每一个元素必须出现在 select 列表中（只针对于 mysql）

3.3 SQL 之 SQL 注入(必会)

举例：

select admin from user where username='admin' or 'a'='a' and passwd=''or 'a'='a' 

防止 SQL 注入，使用预编译语句是预防 SQL 注入的最佳方式，如

select admin from user where username=？And password=? 

使用预编译的 SQL 语句语义不会发生改变，在 SQL 语句中，变量用问 号？ 表示。像上面例子中，username 变量传递的'admin' or 'a'='a' 参 数，也只会当 作 username 字符串来解释查询，从根本上杜绝了 SQL 注 入攻击的发生。 注意：使用 mybaits 时 mapper 中#方式能够很大程度防止 SQL 注入， $方式 无法防止 SQL 注入.

3.4 SQL Select 语句完整的执行顺序: (必会)

查询中用到的关键词主要包含六个，并且他们的顺序依次为 select--from--where--group by--having--order by 其中 select 和 from 是必须的，

其他关键词是可选的， 这六个关键 词的执行顺序如下：

from: 需要从哪个数据表检索数据

where: 过滤表中数据的条件

group by: 如何将上面过滤出的数据分组算结果

order by : 按照什么样的顺序来查看返回的数据

3.5 存储引擎（高薪常问）

3.5.1 概念

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使 用数据引擎进行创建.查询. 更新和删除数据。不同的存储引擎提供不同的存储机 制. 索引技巧.锁定水平等功能，使用不同 的存储引擎，还可以 获得特定的 功能。 现在许多不同的数据库管理系统都支持多种不同的数据引擎。 存储引擎主要有： 1. MyIsam , 2. InnoDB, 3. Memory, 4. Archive, 5.Federated 。

3.5.2 InnoDB

InnoDB 底层存储结构为 B+树，B 树的每个节点对应 innodb 的一个 page， page 大小是固定的， 一般设为 16k。其中非叶子节点只有键值，叶 子节点包含完成数据。

适 用 场 景 ：

1）经常更新的表，适合处理多重并发的更新请求。

2）支持事务。

3）可以从灾难中恢复（通过 bin-log 日志等）。

4）外键约束。只有他支持外键。

5）支持自动增加列属性 auto_increment。

3.5.3 TokuDB

TokuDB 底层存储结构为 Fractal Tree,Fractal Tree 的结构与 B+树有 些类似, 在 Fractal Tree 中，每一个 child 指针除了需要指向一个 child 节点 外， 还会带有一个 Message Buffer ，这个 Message Buffer 是一个 FIFO 的 队列， 用来缓存更新操作。 例如，一次插入操作只需要落在某节点的 Message Buffer 就可以马上 返回了，并不需要搜索到叶 子节点。这些缓存的更新会在查询时或后台异步合 并应用到对应的节点中。 TokuDB 在线添加索引，不影响读写操作, 非常快的写入性能， Fractal-tree 在事务实现上有优势。 他主要适用于访问频率不高的数据 或历史数据归档。

3.5.4 MyIASM

MyIASM 是 MySQL 默认的引擎，但是它没有提供对数据库事务的支 持， 也不支持行级锁和外键， 因此当 INSERT(插入)或 UPDATE(更新)数据 时即写操作需要锁定整个表，效率便会低一些。 ISAM 执行读取操作的速度很快，而且不占用大量的内存和存储资源。在 设计之初就预想数据组织 成有固定长度的记录，按顺序存储的。---ISAM 是 一种静态索引结构。 缺点是它不 支持事务处理。

3.5.5 Memory

Memory（也叫 HEAP）堆内存：使用存在内存中的内容来创建表。每 个 MEMORY 表只实际对应 一个磁盘文件。MEMORY 类型的表访问非常得 快， 因为它的数据是放在内存中的，并且默认使用 HASH 索引。但是一旦服 务关闭， 表中的数据就会丢失掉。 Memory 同时支持散列索引和 B 树索 引，B 树索引可以使用部分查询和通配查询，也可以使用和>=等操作符方便 数据挖掘，散列索 引相等的比较快但是对于范围的比较慢很多。

3.6 索引（高薪常问）

索引（Index）是帮助 MySQL 高效获取数据的数据结构。常见的查询 算法, 顺序查找,二分查找,二 叉排序树查找,哈希散列法,分块查找,平衡多路搜索 树 B 树（B-tree）

索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。 在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在 数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数 据库。

MySQL 数据库几个基本的索引类型：普通索引.唯一索引.主键索引.全 文索引.组合索引

3.6.1 索引的优点

创建唯一索引，保证数据库表中每一行数据的唯一性大大加快数据的检 索速度，这也是创建索引的最主要的原因 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意 义。在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和 排 序的时间。 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性 能。

3.6.2 索引的缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定 的物理空间，如果要建立聚簇索引，那么需要的空间就会更大 当对表中的数据进行增加.删除和修改的时候，索引也要动态的维护，降 低了数据的维护速度

3.6.3 常见索引原则

选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引 来确定某条记录。 为经常需要排序.分组和联合操作的字段建立索引. 为常作为查询条件的 字段建立索引。 限制索引的数目：越多的索引，会使更新表变得很浪费时间。 尽量使用数据量少的索引：如果索引的值很长，那么查询的速度会受到 影响。尽量使用前缀来索引：如果索引字段的值很长，最好使用值的前缀来索 引。删除不再使用或者很少使用的索引 最左前缀匹配原则，非常重要的原则。 尽量选择区分度高的列作为索引：区分度的公式是表示字段不重复的比 例索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。 尽量的扩展索引，不要新建索引。

3.7索引原理（了解）

我们使用索引，就是为了提高查询的效率，如同查书一样，先找到章， 再找到章中对于的小节，再找到具体的页码，再到我们需要的内容。 事实上索引的本质就是不断缩小获取数据的筛选范围，找出我们想要的 结果。同时把随机的事件变成顺序的事件，也就是说有了这种索引机制，我们 就可以总是用同一种查找方式来锁定数据。 数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有 范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库 应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把 数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一 段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只 要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录 呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是 lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型 是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是 保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存 来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简 单的搜索树难以满足复杂的应用场景。

3.7.1 磁盘IO与预读

磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道 时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所 需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转 速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120 次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写 入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访 问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来 还挺不错的，但要知道一台500 -MIPS（Million Instructions Per Second）的 机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一 次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据， 每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家 参考：

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次 IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区 内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与 其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页 (page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构 设计非常有帮助。

3.7.2 B+树

上面说了磁盘io是很费时间的。当我们想要查询一个数据的时候，应该控 制把磁盘IO控制在一个很小的数量级。而B+数应运而生（B+树是通过二叉查找 树，再由平衡二叉树，B树演化而来）。

)如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重 点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项 （深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针 P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3 表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、 28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存 储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

3.7.3 B+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内 存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘 块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘 块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在 26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第 三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况 是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么 总共需要百万次的IO，显然成本非常非常高。

3.7.4 B+树性质

1.索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+ 数的高度 h，假设当前数据表的数据为 N，每个磁盘块的数据项的数量是 m， 则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁 盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定 的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为 什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字 节少一半。这也是为什么 b+树要求把真实的数据放到叶子节点而不是内层节 点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据 项等于1时将会退化成线性表。

2.索引的最左匹配特性：当 b+树的数据项是复合的数据结构，比如 (name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当 (张三,20,F)这样的数据来检索的时候，b+树会优先比较 name 来确定下一步的 所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但 当(20,F)这样的没有 name 的数据来的时候，b+树就不知道下一步该查哪个节 点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字 等于张三的数据都找到，然后再匹配性别是 F 的数据了， 这个是非常重要的性 质，即索引的最左匹配特性。

3.7.5 聚焦索引和辅助索引

在数据库中，B+树的高度一般都在2~4层，这也就是说查找某一个键 值的行记录时最多只需要2到4次IO，这倒不错。 数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索 引（secondary index）， 聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。 聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息 1) 聚集索引 InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而 聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子 结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。 聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据 结构一样，每个数据页都通过一个双向链表来进行链接。 如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空 列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。 如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字 节，而且是隐藏的，使其作为聚簇索引。 由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有 一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索 引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺 序，聚集索引能够特别快地访问针对范围值得查询。 聚集索引的好处 它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户 所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+ 树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录 范围查询（range query），即如果要查找主键某一范围内的数据，通 过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可 2) 辅助索引 表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称 为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全 部数据。 叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书 签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相 对应的行数据。 由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索 引的书签就是相应行数据的聚集索引键。 辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以 有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时， InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得相应的主键索引的 主键，然后再通过主键索引来找到一个完整的行记录。 举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这 个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还 需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此 一共需要6次逻辑IO访问才能得到最终的一个数据页

3.8 数据库三范式(必会)

范式是具有最小冗余的表结构。3 范式具体如下：

3.8.1 第一范式(1st NF －First Normal Fromate)

第一范式的目标是确保每列的原子性:如果每列都是不可再分的最小数 据单元（也称为最小的原子 单元），则满足第一范式（1NF） 第一范式（1NF）要求数据库表的每一列都是不可分割的基本数据项， 同一列中不能有多个值。 若某一列有多个值，可以将该列单独拆分成一个实体，新实体和原实体 间是一对多的关系。
在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求， 不满足第一范式（1NF）的数据库就不是关系数据库。 第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解 的原子值，就说明该数据库表满足了第一范式。 第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系 统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那 么就非要将“地址”这个属性重新拆分为省份.城市.详细地址等多个部分进行存 储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数 据库的第一范式

3.8.2 第二范式(2nd NF－Second Normal Fromate)

首先满足第一范式，并且表中非主键列不存在对主键的部分依赖。 第 二范式要求每个表只描述一 件事情。 满足第二范式（2NF）必须先满足第一范式（1NF）。 第二范式要求实体中没一行的所有非主属性都必须完全依赖于主键； 即：非主属性必须完全依赖于主键。 完全依赖：主键可能由多个属性构成，完全依赖要求不允许存在非主属 性依赖于主键中的某一部分属性。 若存在哪个非主属性依赖于主键中的一部分属性，那么要将发生部分依 赖的这一组属性单独新建一个实体，并且在旧实体中用外键与新实体关联，并 且新实体与旧实体间是一对多的关系。 第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表 中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键 而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多 种数据保 存在同一张数据库表中。

3.8.3 第三范式(3rd NF－ Third Normal Fromate)

第三范式定义是，满足第二范式，并且表中的列不存在对非主键列的传 递依赖。除了主键订单编号外，顾客姓名依赖于非主键顾客编号。 满足第三范式必须先满足第二范式。 第三范式要求：实体中的属性不能是其他实体中的非主属性。因为这样 会出现冗余。即：属性不依赖于其他非主属性。 如果一个实体中出现其他实体的非主属性，可以将这两个实体用外键关联， 而不是将另一张表的非主属性直接写在当前表中。 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间 接相关。

3.9 数据库事务(必会)

3.9.1 事务(TRANSACTION)

是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整 体一起向系统提交，要么都执行.要么都不执行。事务是一个不可分割的工作逻 辑单元 事务必须具备以下四个属性，简称 ACID 属性： A 原子性（Atomicity）：事务是一个完整的操作。事务的各步操作 是不可分的（原子的）；要 么都执行，要么都不执行。 B 一致性（Consistency）：当事务完成时，数据必须处于一致状态。 C 隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离 的，这表明事务必须是独 立的，它不应以任何方式依赖于或影响其他事务。

D 永久性（Durability）：事务完成后，它对数据库的修改被永久保持，事务 日志能够保持事务 的永久性。

3.9.2 事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务； COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。 COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的； ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。 回滚会结束用户的事务，并撤销正在进行的所有未提交的修改； SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存 点，一个事务中可以有多个 SAVEPOINT； RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常； ROLLBACK TO identifier 把事务回滚到标记点； SET TRANSACTION 用来设置事务的隔离级别。 InnoDB 存储引 擎 提 供 事 务 的 隔 离 级 别 有 READ UNCOMMITTED.READ COMMITTED.REPEATABLE READ 和 SERIALIZABLE 。

3.9.3 MySQL 事务处理主要有两种方法：

a) 用 BEGIN, ROLLBACK, COMMIT 来实现 i. BEGIN 开始一个事务 ii. ROLLBACK 事务回滚 iii. COMMIT 事务确认 b) 直接用 SET 来改变 MySQL 的自动提交模式: i. SET AUTOCOMMIT=0 禁止自动提交 ii. SET AUTOCOMMIT=1 开启自动提交

3.9.4 事务的四种隔离级别

1）Read uncommitted 读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。 2）Read committed 读已提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数 3）Repeatable read 可重复读，就是在开始读取数据（事务开启）时，不再允许修改操作 4）Serializable 序列化 Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可 以避免脏读.不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。 在 MySQL 数据库中，支持上面四种隔离级别，默认的为 Repeatable read (可重复读)；而在 Oracle 数据库中，只支持 Serializable (串行化)级别和 Read committed (读已提交)这两种级别，其中默认的为 Read committed 级别。

3.10 存储过程（高薪常问）

一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次 编译后再次调用不需要再次 编译，用户通过指定存储过程的名字并给出参 数（如果该存储过程带有参数）来执行它。存储过 程是数据库中的一个重要 对象。 存储过程优化思路： 1. 尽量利用一些 SQL 语句来替代一些小循环，例如聚合函数，求平均 函数等。

2. 中间结果存放于临时表，加索引。
3. 少使用游标。SQL 是个集合语言，对于集合运算具有较高性能。而 cursors是 过程运算。比 如对一个 100 万行的数据进行查询。游标需要读表100 万 次，而不 使用游标则只需要少量几次读取。
4. 事务越短越好。SQLserver 支持并发操作。如果事务过多过长，或者 隔离级 别过高，都会造 成并发操作的阻塞，死锁。导致查询极慢，cpu 占用率极地。
5. 使用 try-catch 处理错误异常。
6. 查找语句尽量不要放在循环内。

3.11 数据库并发策略（高薪常问）

并发控制一般采用三种方法，分别是乐观锁和悲观锁以及时间戳。

3.11.1 乐观锁

乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据；悲观锁 就刚好相反，觉得自 己读数据库的时候，别人可能刚好在写自己刚读的数 据， 其实就是持一种比较保守的态度；时间 戳就是不加锁，通过时间戳来 控制并发出现的问题。

3.11.2 悲观锁

悲观锁就是在读取数据的时候，为了不让别人修改自己读取的数据，就会先 对自己读取的数据加 锁，只有自己把数据读完了，才允许别人修改那部分数据， 或 者反过来说，就是自己修改某条数 据的时候，不允许别人读取该数据，只 有等自己的整个事务提交了，才释放自己加上的锁，才允 许其他用户访问 那部分数据。

3.11.3 两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另 一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生 的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情 况，一般会经常产生冲突，这就会导致上层应用会不断的进行 retry，这样 反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

3.11.4 乐观锁常见的两种实现式版本号机制

一般是在数据表中加上一个数据版本号 version 字段，表示数据被修改的次 数，当数据被修改时，version 值会加一。当线程 A 要更新数据值时，在读取数 据的同时也会读取 version 值，在提交更新时，若刚才读取到的 version 值为当前 数据库中的 version 值相等时才更新，否则重试更新操作，直到更新成功。CAS 算 法即 compare and swap（比较与交换），是一种有名的无锁算法。 无锁编程，即不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程 被阻塞的情况下实现变量的同步，所以也叫非阻塞同步（Non-blocking Synchronization） 。CAS 算法涉及到三个操作数 需要读写的内存值 V 进行比较的值 A 拟写入的新值 B 当且仅当 V 的值等于 A 时，CAS 通过原子方式用新值 B 来更新 V 的值， 否则不会执行任何操作（比较和替换是一个原子操作）。一般情况下 是一个自旋操作，即不断的重试。

3.11.5 乐观锁的缺点

ABA 问 题 如果一个变量 V 初次读取的时候是 A 值，并且在准备赋值的时候检查 到它仍然是 A 值，那我们就能说明它的值没有被其他线程修改过了吗？很明 显是不能的，因为在这段时间它的值可能被改为其他值，然后又改回 A，那 CAS 操作就会误认为它从来没有被修改过。这个问题被称为 CAS 操作的 "ABA"问题。 JDK 1.5 以后的 AtomicStampedReference 类就提供了此种能力， 其中的 compareAndSet 方法就是首先检查当前引用是否等于预期引用， 并且当前标志是否等于预期标志，如果全部相等，则以原子方式将该引用和该标 志的值设置为给定的更新值。 循环时间长开销大 自旋 CAS（也就是不成功就一直循环执行直到成功）如果长时间不成 功， 会给 CPU 带来非常大的执行开销。 如果 JVM 能支持处理器提供的 pause 指令那么效率会有一定的提升，pause 指令有两个作用，第一它可以延 迟流水线执行指令（de-pipeline）,使 CPU 不会消耗过多的执行资源，延迟的 时间取决于具体实现的版本，在一些处理器上延迟时间是零。第二它可以避免在退 出循环的时候因内存顺序冲突（memory order violation）而引起 CPU 流水线被 清空（CPU pipeline flush），从而提高 CPU 的执行效率。 只能保证一个共享变量的原子操作 CAS 只对单个共享变量有效，当操作涉及跨多个共享变量时 CAS 无 效。但是从 JDK 1.5 开始，提供了 AtomicReference 类来保证引用对象之间的原子性，你可以把多个变量放在一个对象里来进行 CAS 操作.所以我们 可以使用锁或者利用 AtomicReference 类把多个共享变量合并成一个共享 变量来操作 CAS 与 synchronized 的使用情景 简单的来说 CAS 适用于写比较少的情况下（多读场景，冲突一般较少） ， synchronized 适用于写比较多的情况下（多写场景，冲突一般较多） 对于资源竞争较少（线程冲突较轻）的情况，使用 synchronized 同步锁 进行线程阻塞和唤醒切换以及用户态内核态间的切换操作额外浪费消耗cpu 资 源； 而 CAS 基于硬件实现，不需要进入内核，不需要切换线程，操作自旋几率较少， 因此可以获得更高的性能。 对于资源竞争严重（线程冲突严重）的情况，CAS 自旋的概率会比较 大， 从而浪费更多的 CPU 资源，效率低于 synchronized。 补充： Java 并发编程这个领域中 synchronized 关键字一直都是元老级的 角色，很久之前很多人都会称它为 “重量级锁” 。但是，在 JavaSE 1.6 之后 进行了主要包括为了减少获得锁和释放锁带来的性能消耗而引入的 偏向锁 和 轻量级锁 以及其它各种优化之后变得在某些情况下并不是那么重了。 synchronized 的底层实现主要依靠 Lock-Free 的队列，基本思路是 自旋后阻 塞， 竞争切换后继续竞争锁，稍微牺牲了公平性，但获得了高吞吐量。在线程 冲突较 少的情况下，可以获得和 CAS 类似的性能；而线程冲突严重的情况 下，性能远高于 CAS。

3.11.6 时间戳

时间戳就是在数据库表中单独加一列时间戳，比如“TimeStamp”，每次 读出来的时候，把该字 段也读出来，当写回去的时候，把该字段加 1，提交之前 ， 跟数据库的该字段比较一次，如果比数 据库的值大的话，就允许保存，否 则不允许保存，这种处理方法虽然不使用数据库系统提供的锁 机制，但是 这种方法可以大大提高数据库处理的并发量， 以上悲观锁所说的加“锁”，其实分为几种锁，分别是：排它锁（写锁）和共 享锁（读锁） 。

3.12 触发器（高薪常问）

触发器是一段能自动执行的程序，是一种特殊的存储过程，触发器和普通的存 储过程的区别是： 触发器是当对某一个表进行操作时触发。诸如： update.insert.delete 这些操作的时候，系统 会自动调用执行该表上对应的触 发器。SQL Server 2005 中触发器可以分为两类：DML 触发器和 DDL 触 发器，其中 DDL 触发器它们会影响多种数据定义语言语句而激发，这些语 句有create. alter.drop 语句。

3.13 数据库锁（高薪常问）

3.13.1 行级锁

行级锁是一种排他锁，防止其他事务修改此行；在使用以下语句时，Oracle 会 自动应用行级锁： INSERT.UPDATE.DELETE.SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT]; SELECT … FOR UPDATE 语句允许用户一次锁定多条记录进行更新 使用 COMMIT 或 ROLLBACK 语句释放锁。

3.13.2 表级锁

表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使 用的 MYISAM 与 INNODB 都支持表级锁 定。表级锁定分为表共享读锁（共享锁）与表独占写锁 （排他锁） 。

3.13.3 页级锁

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁 速度快，但冲突多，行级 冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB 支持页级锁

3.14 基于 Redis 分布式锁（高薪常问）

1. 获取锁的时候，使用 setnx（ETNX key val：当且仅当 key 不存 在时， set 一个 key 为 val 的字符串，返回 1；若 key 存在，则什么都 不做，返回 0） 加锁，锁的 value 值为一个随机生成的 UUID，在释放锁的 时候进行判断。并使用 expire 命令为锁添 加一个超时时间，超过该时间则 自动释放锁。
2. 获取锁的时候调用 setnx，如果返回 0，则该锁正在被别人使用，返 回 1 则成功获取 锁。 还设置一个获取的超时时间，若超过这个时间则放弃获 取锁。
3. 释放锁的时候，通过 UUID 判断是不是该锁，若是该锁，则执行 delete 进行锁释放。

3.15 分区分表（高薪常问）

分库分表有垂直切分和水平切分两种。 ▪ 垂直切分：将表按照功能模块.关系密切程度划分出来，部署到不同的 库上。例如，我们会 建立定义数据库 workDB.商品数据库 payDB.用户数 据库userDB.日志数据库 logDB 等，分别用于存储项目数据定义表.商品定 义表.用户数据表.日志数据表等。 ▪ 水平切分：当一个表中的数据量过大时，我们可以把该表的数据按照某种 规则，例如 userID 散列，进行划分，然后存储到多个结构相同的表，和不同 的库上。例如，我们的 userDB 中的用户数据表中，每一个表的数据量都 很大， 就可以把 userDB 切分为结构相同的多个 userDB： part0DB.part1DB 等，再将 userDB 上 的 用 户 数 据 表 userTable， 切 分 为 很 多 userTable： userTable0.userTable1 等，然后将这些表按 照一定的规则存储到多个 userDB 上。

3.16 应该使用哪一种方式来实施数据库分库分表，这要看 数据库中数据量的瓶颈所在，并综合项目的业务类型进行 考虑。（了解）

如果数据库是因为表太多而造成海量数据，并且项目的各项业务逻辑划分清晰. 低耦合，那么规则简单明了.容易实施的垂直切分必是首选。 而如果数据库中的表并不多，但单表的数据量很大.或数据热度很高，这种 情况 之下就应该选择水平切分，水平切分比垂直切分要复杂一些，它将原本逻 辑上属 于一体的数据进行了物理分割，除了在分割时要对分割的粒度做好评估， 考虑数 据平均和负载平均，后期也将对项目人员及应用程序产生额外的数据管 理负担。 在现实项目中，往往是这两种情况兼而有之，这就需要做出权衡，甚 至既需要垂 直切分，又需要水平切分。我们的游戏项目便综合使用了垂直与水 平切分，我们 首先对数据库进行垂直切分，然后，再针对一部分表，通常是用 户数据表，进行 水平切分。 单库多表 ： 随着用户数量的增加，user 表的数据量会越来越大，当数据量达到一定 程 度的时候对 user 表的查询会渐渐的变慢，从而影响整个 DB 的性能。如果 使用 MySQL, 还有一个更严重的问题是，当需要添加一列的时候，MySQL 会锁表， 期间所有的读写操作只能等待。 可以将 user 进行水平的切分，产生两个表结构完全一样的 user_0000,user_0001 等表，user_0000 + user_0001 + …的数据刚好是一份 完整的数据。 多库多表 ： 垂 直切分，又需要水平切分。我们的游戏项目便综合使用了垂直与水 平切分，我们 首先对数据库进行垂直切分，然后，再针对一部分表，通常是用 户数据表，进行 水平切分。 单库多表 ： 随着用户数量的增加，user 表的数据量会越来越大，当数据量达到一定 程 度的时候对 user 表的查询会渐渐的变慢，从而影响整个 DB 的性能。如果 使用 MySQL, 还有一个更严重的问题是，当需要添加一列的时候，MySQL 会锁表， 期间所有的读写操作只能等待。 可以将 user 进行水平的切分，产生两个表结构完全一样的 user_0000,user_0001 等表，user_0000 + user_0001 + …的数据刚好是一份 完整的数据。 多库多表 ： 随着数据量增加也许单台 DB 的存储空间不够，随着查询量的增加单台数 据 库服务器已经没办法支撑。这个时候可以再对数据库进行水平区分。 分库分表规则举例： 通过分库分表规则查找到对应的表和库的过程。如分 库分表的规则是 user_id 除以 4 的方式，当用户新注册了一个账号，账号 id 的 123,我们可以通过 id 除以 4 的方式确定此账号应该保存到 User_0003 表中。当用户 123 登录的时 候，我们通过 123 除以 4 后 确定记录在User_0003 中 。