1：MySQL

1-1：为什么要使用数据库

为了更高效且条例分明的存储数据，能够更迅速和方便地管理数据。

  1. 数据库可以结构化存储大量的数据信息，方便用户进行有效的检索和访问。
  2. 数据库可以有效地保证数据信息的一致性，完整性，降低数据冗余
  3. 数据库可以满足应用的共享和安全方面的要求，把数据放在数据库中在很多情况下也是出于安全的考虑。
  4. 数据库技术能够方便智能化地分析，产生新的有用信息。

1-2: 什么是 SQL？

  • SQL 指结构化查询语言，全称是 Structured Query Language。
  • SQL 让您可以访问和处理数据库，包括数据插入、查询、更新和删除。
  • SQL 在 1986 年成为 ANSI（American National Standards Institute 美国国家标准化组织）的一项标准，在 1987 年成为国际标准化组织（ISO）标准。

1-3：什么是 MySQL?

MySQL 是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

2：关系型数据库与非关系型数据库

2-1：非关系型数据库和关系型数据库定义

  • 关系数据库（英语：Relational database），是创建在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
  • NoSQL（最初表示 Non-SQL，后来有人转解为 Not only SQL），是对不同于传统的关系数据库的数据库管理系统的统称。
    允许部分资料使用 SQL 系统存储，而其他资料允许使用 NOSQL 系统存储。其数据存储可以不需要固定的表格模式以及元数据（metadata），也经常会避免使用 SQL 的 JOIN 操作，一般有水平可扩展性的特征。

2-2：关系型数据库优缺点

优点：

  • 通过事务处理保持数据的一致性
  • 数据更新的开销很小
  • 可以进行 Join 等复杂查询
  • 20 多年的技术历程，技术成熟

缺点：

  • 数据读写必须经过 sql 解析，大量数据、高并发下读写性能不足
  • 为保证数据一致性，需要加锁，影响并发操作
  • 无法适应非结构化的存储
  • 大量数据集中到一台服务区处理，使服务器不堪重负
  • “阻抗失谐”，即数据库中存储的对象与实际的对象实体有一定的差别
  • 扩展困难
  • 数据库庞大，价格昂贵

2-3：非关系型数据库优缺点

优点：

  • 处理高并发、大批量数据的能力强
  • 支持分布式集群，负载均衡，性能高
  • 解决“阻抗失谐”问题
  • 内存级数据库，查询速度快
  • 存储格式多，支持 key-value 形式、文档形式、图片形式
  • 没有多表连接查询机制的限制，扩展性高

缺点：

  • 技术起步晚，维护工具以及技术资料有限
  • 不支持 sql 工业标准
  • 没有 join 等复杂的连接操作
  • 事务处理能力弱
  • 没有完整性约束，对于复杂业务场景支持较差

2-4：非关系型数据库和关系型数据库区别

关系型数据库 非关系型数据库
数据相互关联 数据独立
一般数据量 海量数据
行存储 列储存
保证数据完整性和安全性 不保证数据完整性和安全性存储

3：三大范式

3-1：数据库三大范式是什么

  1. 数据库表的每一列都是不可分割的基本数据项
  2. 每一行都要有唯一标识存在，这个唯一属性列被称为主关键字或主键、主码。实体的属性完全依赖于主关键字。
  3. 属性不依赖于其他非主属性。

4：数据库的数据类型

4-1：MySQL 的数据类型

  • TINYINT：1 个字节，整形
  • SMALLINT：2 个字节，整形
  • MEDIUMINT：3 个字节，整形
  • INT：4 个字节：整形
  • BITINT：8 个字节，整形
  • FLOAT：4 个字节，单精度浮点型
  • DOUBLE：8 个字节，双精度浮点型
  • DECIMAL：定点型，解决浮点数的精度丢失问题
  • CHAR：固定长度字符串
  • VARCHAR：可变长度字符串
  • DATA：3 个字节，yyyy-MM-dd，日期
  • TIME：3 个字节，HH:mm:ss，时间
  • YEAR：1 个字节，yyyy，年
  • DATETIME：8 个字节，yyyy-MM-dd HH:mm:ss 日期+时间
  • TIMESTAMP：4 个字节，yyyy-MM-dd HH:mm:ss 日期+时间，可作时间戳

4-2：varchar 与 char 的区别

char 是一种固定长度的类型，varchar 则是一种可变长度的类型，它们的区别是：

  • char(M)类型的数据列里，每个值都占用 M 个字节，如果某个长度小于 M，MySQL 就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）
  • 在 varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为 L+1 字节）．

4-2-1：varchar(50)中 50 的涵义

varchar(50)中 50 的涵义最多存放 50 个字符，varchar(50)和(200)存储 hello 所占空间一样，但后者在排序时会消耗更多内存，
因为 order by col 采用 fixed_length 计算 col 长度

4-3：int(20)中 20 的涵义

显示长度，不足用 0 补齐

4-4：FLOAT 和 DOUBLE 的区别是什么？

单精度和双精度的区别

4-5：MySQL INT 和 CHAR 隐式类型转换需要注意什么？

  1. 当查询字段是 INT 类型，如果查询条件为 CHAR，将查询条件转换为 INT，如果是字符串前导都是数字，将截取前导数字用来比较，如果没有前导数字，则转换为 0。
  2. 当查询字段是 CHAR/VARCHAR 类型，如果查询条件为 INT，将查询字段为换为 INT 再进行比较，可能会造成全表扫描。

5：SQL 生命周期

  • 连接器：建立连接
  • 查询缓存：缓冲中存在则返回，否则继续执行（8.0 后删除）
  • 分析器：词法分析，判断是否合法
  • 优化器：索引优化
  • 执行器：有索引则按照索引树跳转，否则全表扫描。

6：MySQL 预编译

6-1：预编译的好处

即时 sql 每次都会进行分析和优化，而预编译 sql 将语句参数化，实现一次编译，多次执行。省去了解析优化等过程。此外预编译能防止 sql 注入。

7：SQL 注入

7-1：SQL 注入简介

Sql 注入攻击是通过将恶意的 Sql 查询或添加语句插入到应用的输入参数中，再在后台 Sql 服务器上解析执行进行的攻击，它目前黑客对数据库进行攻击的最常用手段之一。

7-2：SQL 注入解决方案

  • 严格限制 Web 应用的数据库的操作权限，给连接数据库的用户提供满足需要的最低权限，最大限度的减少注入攻击对数据库的危害
  • 校验参数的数据格式是否合法（可以使用正则或特殊字符的判断）
  • 对进入数据库的特殊字符进行转义处理，或编码转换
  • 预编译 SQL（Java 中使用 PreparedStatement），参数化查询方式，避免 SQL 拼接
  • 发布前，利用工具进行 SQL 注入检测
  • 报错信息不要包含 SQL 信息输出到 Web 页面

8：sql 语句

8-1：SQL 的分类

  • 数据操作语言(DML)：对数据库中数据的操作
  • 数据定义语言(DDL)：定义 SQL 模式，基本表，视图和索引的创建和撤销
  • 数据控制语言(DCL)：用于数据库搜全，角色控制等管理工作
  • 事务控制语言(TCL)：用于数据库的事务管理

8-2：多表查询

  • 合并查询结果（union，union all）
  • 连接查询（交叉连接（cross join）、内连接（inner join）、左连接（left join）、右连接（right join)、全连接）
  • CASE 表达式。

8-2-1：笛卡尔积问题

连接查询，如果 on 条件是非唯一字段，会出现笛卡尔积(局部笛卡尔积)；如果 on 条件是表的唯一字段，则不会出现笛卡尔积。

8-2-2：笛卡尔积的解决方案

  • 使用左连接，右连接，内连接
  • 使用唯一条件作为 on 筛选条件

8-3：常见函数

  • ABS(X):返回 X 的绝对值
  • MOD(N,M)或%:返回 N 被 M 除的余数
  • FLOOR(X):返回不大于 X 的最大整数值
  • CEILING(X):返回不小于 X 的最小整数值
  • ROUND(X) :返回参数 X 的四舍五入的一个整数
  • ….

8-3-1：单行函数

8-3-2：聚合函数

函数 备注
AVG([distinct] expr) 求平均值
COUNT([distinct] expr) 统计行的数量
MAX([distinct] expr) 求最大值
MIN([distinct] expr) 求最小值
SUM([distinct] expr) 求累加和

8-4：关键字/语句

8-4-1：truncate、 delete 区别

  • DELETE 语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
  • TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
  • drop 语句将表所占用的空间全释放掉。

8-4-2：mysql 的 having 用法

having 字句可以让我们筛选成组后的各种数据，where 字句在聚合前先筛选记录，也就是说作用在 group by 和 having 字句前。而 having 子句在聚合后对组记录进行筛选。

where 和 having 不可以同时使用。

8-4-3：mysql 中 in 和 exists 区别

  • IN 适合于外表大而内表小的情况
  • EXISTS 适合于外表小而内表大的情况

in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询。 in 其实与等于相似，比如 in(1,2) 就是 = 1 or = 2 的一种简单写法，所以一般在元素少的时候使用 in，如果多的话就用 exists。

8-4-4：WHERE 子句和 HAVING 子句的执行速度

WHERE 处理速度比 HAVING 处理速度高

8-4-5：groupby 和 having 的区别

groupby 是对数据分组，而 having 是对分组的结果进行筛选，两者相辅相成。

8-4-6：UNION 与 UNION ALL 的区别？

union 会去重，union all 保留所有数据

8-4-7：count(*)、count(1)、count(column)的区别

  1. count(*)查询所有行
  2. count(1)查询所有行
  3. count(column)统计列字段，会忽略 null 的情况

执行效率上，count(*)=count(1)>count(primary key)>count(column)

8-4-8：MySQL，左连接中 on 和 where 的区别

on 是两个表之间的筛选条件，最先执行，where 是对单表进行筛选。

8-5：增删改查（CURD）+ 日志

8-5-1：一条 sql 执行过程

  • MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。
  • 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
  • SQL 等执行过程分为两类，一类对于查询等过程如下：权限校验—>查询缓存（8.0 以前）—>分析器（词法分析，语法分析）—>优化器（确定执行方案）—>权限校验—>执行器—>引擎
  • 对于更新等语句执行流程如下：分析器—->权限校验—->执行器—>引擎—redo log prepare—>binlog—>redo log commit

8-5-2：一条 sql 更新/删除/增加语句时怎么执行的

  • 先查询数据，如果有缓存，也是会用到缓存。
  • 修改数据，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
  • 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
  • 更新完成。

8-5-3：日志

日志文件中记录着 MySQL 数据库运行期间发生的变化；也就是说用来记录 MySQL 数据库的客户端连接状况、SQL 语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。

8-5-4：什么是 binlog

binlog 是 MySQl server 层维护的一种二进制日志，这个文件记录了 MySQL 所有的 DML 操作。通过 binlog 日志我们可以做数据恢复，增量备份，主主复制和主从复制等等

8-5-5：binlog 一般用来做什么

  • 复制：MySQL Replication 在 Master 端开启 binlog，Master 把它的二进制日志传递给 slaves 并回放来达到 master-slave 数据一致的目的
  • 数据恢复：通过 mysqlbinlog 工具恢复数据
  • 增量备份

8-5-6：MySQL 的 binlog 有几种录入格式

  • Statement：每一条会修改数据的 sql 都会记录在 binlog 中
  • MiXED：不记录 sql 语句上下文相关信息，仅保存哪条记录被修改
  • ROW：是以上两种 level 的混合使用，一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog,MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式，也就是在 Statement 和 Row 之间选择一种.新版本的 MySQL 中 row level 模式也被做了优化，并不是所有的修改都会以 row level 来记录，像遇到表结构变更的时候就会以 statement 模式来记录。至于 update 或者 delete 等修改数据的语句，还是会记录所有行的变更。

8-5-7：redo log

当我们想要修改 DB 上某一行数据的时候，InnoDB 是把数据从磁盘读取到内存的缓冲池上进行修改。这个时候数据在内存中被修改，与磁盘中相比就存在了差异，我们称这种有差异的数据为脏页。
InnoDB 对脏页的处理不是每次生成脏页就将脏页刷新回磁盘，这样会产生海量的 IO 操作，严重影响 InnoDB 的处理性能。既然脏页与磁盘中的数据存在差异，那么如果在这期间 DB 出现故障就会造成数据的丢失。为了解决这个问题，redo log 就应运而生了。

redo log 就是存储了数据被修改后的值。当我们提交一个事务时，InnoDB 会先去把要修改的数据写入日志，然后再去修改缓冲池里面的真正数据页。

8-5-8：binlog 和 redo log 比较

  • redo log 是属于 innoDB 层面，binlog 属于 MySQL Server 层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
  • redo log 是物理日志，记录该数据页更新的内容；binlog 是逻辑日志，记录的是这个更新语句的原始逻辑
  • redo log 是循环写，日志空间大小固定；binlog 是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
  • binlog 可以作为恢复数据使用，主从复制搭建，redo log 作为异常宕机或者介质故障后的数据恢复使用。

8-5-9：我写其中的某一个 log，失败了，那会怎么办？

因为 redo log 和 bin log 的两阶段提交，所以不会影响数据。

8-5-10：两阶段提交意义

  1. 如果先写 redo log，后写 binlog，假如 binlog 失败，恢复后主库根据 redo log 重做，但是 binlog 不存在，复制到从库到出现主从不一致
  2. 如果先写 binlog，后写 redolog，假如 redo log 失败，恢复后从库根据主库的 binlog 回放数据，但是主库因为 redo log 不存在回滚事务。也会出现主从不一致。

两阶段先在 redo log 提交变为 prepare 状态，修改后提交 binlog，再把 redo log 改为 commit 状态。保证了数据的一致性。

8-5-11：MySQL 如何保证 redo log 和 binlog 的数据是一致的

通过两阶段提交。

  1. 如果数据库在记录此事务的 binlog 之前和过程中发生 crash。数据库在恢复后认为此事务并没有成功提交，则会回滚此事务的操作。与此同时，因为在 binlog 中也没有此事务的记录，所以从库也不会有此事务的数据修改。
  2. 如果数据库在记录此事务的 binlog 之后发生 crash。此时，即使是 redo log 中还没有记录此事务的 commit 标签，数据库在恢复后也会认为此事务提交成功（因为在上述两阶段过程中，binlog 写入成功就认为事务成功提交了）。它会扫描最后一个 binlog 文件，并提取其中的事务 ID（xid），InnoDB 会将那些状态为 Prepare 的事务（redo log 没有记录 commit 标签）的 xid 和 Binlog 中提取的 xid 做比较，如果在 Binlog 中存在，则提交该事务，否则回滚该事务。这也就是说，binlog 中记录的事务，在恢复时都会被认为是已提交事务，会在 redo log 中重新写入 commit 标志，并完成此事务的重做（主库中有此事务的数据修改）。与此同时，因为在 binlog 中已经有了此事务的记录，所有从库也会有此事务的数据修改。

8-5-12：如果整个数据库的数据都被删除了，那我可以用 redo log 的记录来恢复吗？

不能，因为功能的不同，redo log 存储的是物理数据的变更，如果我们内存的数据已经刷到了磁盘了，那 redo log 的数据就无效了。所以 redo log 不会存储着历史所有数据的变更，文件的内容会被覆盖的。

8-5-13：MySQL 查询字段区不区分大小写？

MySQL 查询是不区分大小写的。

8-5-14：如何解决需要区分英文大小写的场景

  1. Mysql 默认的字符检索策略：utf8_general_ci，表示不区分大小写；utf8_general_cs 表示区分大小写，utf8_bin 表示二进制比较，同样也区分大小写
  2. 直接修改 sql 语句，在要查询的字段前面加上 binary 关键字即可。

8-6：常见约束

8-6-1：字段为什么要求定义为 not null?

  1. 索引性能不好 Mysql 难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要 mysql 内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致 MYisam 中固定大小的索引变成可变大小的索引。如此看来，不指定 not null 并没有性能上的优势。
  2. 查询会出现一些不可预料的结果，因为 null 列的存在，会出现很多出人意料的结果，从而浪费开发时间去排查 Bug。

8-6-2：超键、候选键、主键、外键分别是什么？

  • 超键(super key):在关系中能唯一标识元组的属性集称为关系模式的超键。
  • 候选键(candidate key):不含有多余属性的超键称为候选键。
  • 主键(primary key):用户选作元组标识的一个候选键程序主键。
  • 外键(foreign key)如果关系模式 R1 中的某属性集不是 R1 的主键，而是另一个关系 R2 的主键则该属性集是关系模式 R1 的外键。

8-6-3：为什么用自增列作为主键

  • 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。
  • 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页的中间某个位置，此时 MySQL 不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。

8-6-4：主键使用自增 ID 还是 UUID?

自增 id

8-6-5：使用自增 id 的缺点

  1. 别人一旦爬取你的数据库,就可以根据数据库的自增 id 获取到你的业务增长信息，很容易分析出你的经营情况
  2. 对于高并发的负载，innodb 在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争
  3. Auto_Increment 锁机制会造成自增锁的抢夺,有一定的性能损失

8-6-6：数据库主键自增怎么获取主键值

1
2
3
4
5
<insert id=”insertUser”  useGeneratedKeys=”true” keyProperty=”userId” >
insert into user(
user_name, user_password, create_time)
values(#{userName}, #{userPassword} , #{createTime, jdbcType= TIMESTAMP})
</insert>

8-6-7：为什么要尽量设定一个主键?

设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全

9：sql 实战

9-1：去重重复数据

1
select distinct id from table

9-2：MySQL 如何高效率随机获取 N 条数据？

计算 id 最大值和最小值，从中取随机数。

1
2
3
SELECT * FROM mm_account
WHERE id >= ((SELECT MAX(id) FROM mm_account)-(SELECT MIN(id) FROM mm_account)) * RAND() + (SELECT MIN(id) FROM mm_account)
limit 5;

10：事务

10-1：什么是事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

10-2：数据库事务特性

  1. A (Atomicity) 原子性
    原子性很容易理解，也就是说事务里的所有操作要么全部做完，要么都不做，事务成功的条件是事务里的所有操作都成功，只要有一个操作失败，整个事务就失败，需要回滚。
    比如银行转账，从 A 账户转 100 元至 B 账户，分为两个步骤：1）从 A 账户取 100 元；2）存入 100 元至 B 账户。这两步要么一起完成，要么一起不完成，如果只完成第一步，第二步失败，钱会莫名其妙少了 100 元。
  2. C (Consistency) 一致性
    一致性也比较容易理解，也就是说数据库要一直处于一致的状态，事务的运行不会改变数据库原本的一致性约束。
    例如现有完整性约束 a+b=10，如果一个事务改变了 a，那么必须得改变 b，使得事务结束后依然满足 a+b=10，否则事务失败。
  3. I (Isolation) 隔离性
    所谓的隔离性是指并发的事务之间不会互相影响，如果一个事务要访问的数据正在被另外一个事务修改，只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响。
    比如现有有个交易是从 A 账户转 100 元至 B 账户，在这个交易还未完成的情况下，如果此时 B 查询自己的账户，是看不到新增加的 100 元的。
  4. D (Durability) 持久性
    持久性是指一旦事务提交后，它所做的修改将会永久的保存在数据库上，即使出现宕机也不会丢失。

10-3：ACID 靠什么保证的(底层原理)

  1. 原子性通过undo log日志实现。回滚日志会记录相应的日志信息，例如 delete 数据时，记录一条 insert 语句，回滚时插入。
  2. 通过原子性，持久性，隔离性保证了一致性。
  3. 通过redo log保证持久性，重做日志会记录处理后的数据。
  4. 通过锁保证了隔离性

10-3-1：什么是 undo log

undo log 主要有两个作用：回滚和多版本控制(MVCC)

在数据修改的时候，不仅记录了 redo log，还记录 undo log，如果因为某些原因导致事务失败或回滚了，可以用 undo log 进行回滚

10-4：数据库崩溃时事务的恢复机制

undo log 通过回滚保证事务的原子性，redo log 通过恢复保证事务的持久性

10-5：在并发环境下,事务会发生哪些问题?

  1. 脏读(dirty read)：如果第二个事务查询到第一个事务还未提交的更新数据，形成脏读。
  2. 幻读(phantom read)：一个事务执行两次查询，第二次查询比第一次多出或少一些数据，造成两次结果不一致。只是另一个事务在这两次查询中间插入或者删除了数据造成的。
  3. 不可重复读(unrepeated read)：一个事务两次读取同一行数据，结果得到不同状态结果，如中间正好另一个事务更新了该数据，两次结果相异，不可信任。

10-5-1：不可重复读和幻读的区别

不可重复读是对同一条数据而言，而幻读是对多条数据的读取结果。

10-5-2：如何解决幻读

使用序列化隔离等级，这个等级会开启表锁。

10-6：如何解决事务并发问题

通过加锁和 MVCC 机制

10-7：四大隔离级别

  1. 读未提交：read uncommitted
    SELECT 语句以非锁定方式被执行，所以有可能读到脏数据，隔离级别最低。（读不锁）
  2. 读已提交：read committed
    只能读取到已经提交的数据。即解决了脏读，但未解决不可重复读。（读锁，等写完）
  3. 可重复读：repeatable read
    在同一个事务内的查询都是事务开始时刻一致的，InnoDB 的默认级别。在 SQL 标准中，该隔离级别消除了不可重复读，但是还存在幻读。(写锁，等读完)
  4. 串行化：serializable
    完全的串行化读，所有 SELECT 语句都被隐式的转换成 SELECT … LOCK IN SHARE MODE，即读取使用表级共享锁，读写相互都会阻塞。隔离级别最高。(以上 3 个均为行锁/记录锁，当前为表锁)
脏读 不可重复读 幻读
Read uncommitted √ √ √
Read committed × √ √
Repeatable read × × √
Serializable × × ×

10-7-1：隔离级别的原理

在数据库增删改查四种操作中，insert、delete 和 update 都是会加排它锁(Exclusive Locks)的，而 select 只有显式声明才会加锁:

  • select: 即最常用的查询，是不加任何锁的
  • select … lock in share mode: 会加共享锁(Shared Locks)
  • select … for update: 会加排它锁
  • 读未提交中写虽然会加排他锁，但是读不加锁所以排他锁不会影响读取数据
  • 读已提交中写加排他锁，读的时候不加写锁而是获取 MVCC 机制，获取最新快照。因为快照是每次 select 生成，如果多次 select 间有事务提交更改数据，就出现了不可重复读问题。
  • 可重复读中写加排他锁，不同的是 MVCC 版本的生成时机：一次事务只在第一次 select 时生成快照。后续读取都是基于一个版本，实现了可重复读。
  • 串行化会在读时加共享锁，写时加排他锁。

10-7-2：MySQL 中 RC（读已提交）和 RR（可重复读）隔离级别的区别

在于 MVCC 版本的加载时机，读已提交是每次 select 都会读取快照，可重复读是事务第一次读取生成快照，后续基于这个版本查询。

10-8：如何手动处理事务

1
2
3
start transaction
....
commit

语句内的一系列语句都处于一个事务周期内，否则每一个 sql 语句都是一个独立事务。

11：引擎

11-1：MySQL 存储引擎 MyISAM 与 InnoDB 区别

MyIASM InnoDB
事务支持 不支持 支持
外键约束 不支持 支持
索引结构 非聚集索引 聚集索引
保存行数 保存 不保存
全文索引 支持 不支持
粒度 表锁 行锁
主键 不必需 必需
存储文件 frm frm,MYD,MYI
存储空间 可压缩 不可压缩

11-2：InnoDB 引擎的 4 大特性

  1. 写缓冲：对于不在缓冲池的非唯一普通索引页，不会立即将磁盘页刷入缓冲池中，而是记录缓冲变更，等未来数据读取时，在将数据合并到缓冲池。写缓冲的目的是降低写操作的磁盘 IO，提升数据库性能。
  2. 二次写：避免脏页刷盘时崩溃导致数据丢失的问题，将脏页先拷贝到内存中的 doublewrite buffer 中，再从两次写缓冲区分两次写入磁盘共享表空间。最后从两次写缓冲区写入实际表空间。保证了系统崩溃时脏页数据不会丢失。
  3. 自适应 hash：会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，此索引成为热数据，建立 hash 索引以提升查询速度，此建立是自动建立哈希索引，故称为自适应哈希索引。
  4. 预读：磁盘读写，并不是按需读取，而是按页读取，一次至少读一页数据。如果未来要读取的数据就在页中，就能够省去后续的磁盘 IO，提高效率。将预读的页放入缓冲池中，从而提高读取效率

12：MySQl 锁

12-1：MyISAM 和 InnoDB 存储引擎使用的锁

MyISAM 使用表锁，而 InnoDB 使用行锁+表锁。

12-2：InnoDB 存储引擎的锁的算法

  • Record Lock：单个行记录上的范围
  • Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
  • Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身

12-3：MySQL 锁的种类

  1. 共享/排它锁(Shared and Exclusive Locks)
  2. 意向锁(Intention Locks)
  3. 记录锁(Record Locks)
  4. 间隙锁(Gap Locks)
  5. 临键锁(Next-key Locks)
  6. 插入意向锁(Insert Intention Locks)
  7. 自增锁(Auto-inc Locks)

12-3-1：共享/排它锁(Shared and Exclusive Locks)

即读锁和写锁，读锁时其他线程也可以读，但是不能写，写锁其他线程不能读也不能写。

12-3-2：意向锁(Intention Locks)

意向锁是一种不与行锁冲突的表级锁。

  • 意向共享锁（IS）：事务有意向对表中某些行加共享锁
  • 意向排他锁（IX）：事务有意向对表中某些行加排他锁
  • InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存。
  • 意向锁之间互不排斥，但除了 IS（意向共享锁） 与 S（共享锁） 兼容外，意向锁会与 共享锁 / 排他锁 互斥
  • IX，IS 是表级锁，不会和行级的 X，S 锁发生冲突。只会和表级的 X，S 发生冲突。
  • 意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

12-3-4：记录锁(Record Locks)

为某行记录加锁，它封锁该行的索引记录，行锁，排他锁。存在于包括主键索引在内的唯一索引中，锁定单条索引记录。

12-3-5：间隙锁(Gap Locks)

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于 Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的一条数据。

12-3-6：临键锁(Next-key Locks)

Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

12-3-7：自增锁(Auto-inc Locks)

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入 AUTO_INCREMENT 类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

12-4：数据库死锁的预防与解除

  1. 尽量避免并发地执行涉及到修改数据的语句
  2. 要求每个事务一次就将所有要使用的数据全部加锁，否则就不予执行
  3. 预先规定一个封锁顺序，所有的事务都必须按这个顺序对数据执行封锁。如不同的过程在事务内部对对象的更新执行顺序应尽量保持一致。
  4. 每个事务的执行时间不可太长，在业务允许的情况下可以考虑将事务分割成为几个小事务来执行。【比如说把复杂的多表查询分散成多次单表查询】
  5. 数据存储空间离散法。数据存储空间离散法是指采取各种手段，将逻辑上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过将大表按行或列分解为若干小表，或者按不同的用户群分解两种方法实现。这种方法类似分散“数据热点”，但是确实，如果数据不是太经常被访问，那么死锁就不会太经常发生。
  6. 类似 1，比如有一个修改上百条记录的 update 语句，我们可以修改成每 10 条一个 update 语句，或者干脆就每条记录一个 update 语句。
  7. 将经常更新的数据库和查询数据库分开

12-5：多版本并发控制 MVCC

多版本并发控制（MVCC） 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

简单来说，多版本并发控制 的思想就是保存数据的历史版本，通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。

12-6：哪些读操作是快照读？哪些操作又是当前读呢？

读未提交和序列化的情况下 select 是当前读，读已提交和可重复读的情况下 select 是快照读。
而 update，insert，remove 都是当前读。

12-7：为什么将 插入/更新/删除 操作，都归为当前读

因为对数据的更新必须基于数据的最新情况。

13：索引

13-1：主键与索引的区别

  1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。
  2. 一个表中可以有多个唯一性索引，但只能有一个主键。
  3. 主键列不允许空值，而唯一性索引列允许空值。
  4. 索引可以提高查询的速度。

13-2：索引的分类

  • 普通索引：无限制条件，最基本的索引
  • 唯一索引：索引列的值必须唯一，但允许有控制，如果是联合索引，列值的组合必须唯一
  • 主键索引：特殊的唯一索引，一个表只能有一个主键，不能为 null，建表时会自动创建
  • 联合索引：多个字段上创建的索引，遵循最左前缀匹配
  • 全文索引：查找文本关键字

13-2-1：联合索引失效的条件

  • 不符合最左前缀匹配
  • 不在索引列上做任何操作（计算、函数、（自动 or 手动）类型转换），会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引范围条件右边的列（< ,> between and）
  • mysql 在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
  • is not null 也无法使用索引
  • like 以通配符开头（’%abc…’）mysql 索引失效会变成全表扫描的操作。
  • mysql 中，如果条件中有 or，即使其中有条件带索引也不会使用(这也是为什么尽量少用 or 的原因)。要想使用 or，又想让索引生效，只能将 or 条件中的每个列都加上索引

13-2-2：单列索引和联合索引区别

多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上(or)！
但多个单列索引底层会建立多个 B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！
同时存在联合索引和单列索引时，会根据优化器的判断选择最优索引执行。

13-3：主键索引和普通索引的工作原理

主键索引也叫做聚簇索引，其余都称呼为非主键索引也叫二级索引。聚簇索引中的叶子节点存储了整行数据，而二级索引（非聚簇索引）存储了索引和主键 id，查找是先在普通索引树上找到主键 id，再通过 id 查询主键索引树。

在流程中从非主键索引树搜索回到主键索引树搜索的过程称为：回表。

13-4：覆盖索引

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

13-5：mysql 索引的结构

b+树 – 多路平衡查找树

13-5-1：B+树比 B 树的优势

  1. 单一节点存储更多的元素，使得查询的 IO 次数更少；
  2. 所有查询都要查找到叶子节点，查询性能稳定；
  3. 所有叶子节点形成有序链表，便于范围查询。

13-5-2：B+树与红黑树比较

在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘 IO 读写过于频繁，进而导致效率低下的情况。为什么会出现这样的情况，我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘 IO 代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘 IO 频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B 树可以有多个子女，从几十到上千，可以降低树的高度。

13-5-3：B+树与 hash 索引比较

Hash 优势：

  1. 等值查询。哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）

哈希索引不适用的场景：

  1. 不支持范围查询
  2. 不支持索引完成排序
  3. 不支持联合索引的最左前缀匹配规则

13-6：聚簇索引与非聚簇索引概念

  • 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
  • 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam 通过 key_buffer 把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在 key buffer 命中时，速度慢的原因

13-6-1：聚簇索引的优缺点

聚簇索引的优点：

  1. 当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好
  2. 当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次 I/
  3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点：

  1. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键。
  2. 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于 InnoDB 表，我们一般定义主键为不可更新。
  3. 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。
  4. 采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的 I/O 代价。

13-7：为什么要用索引（优点）

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

13-8：索引这么多优点，为什么不对表中的每一个列创建一个索引呢？（缺点）

优化器只会选择一个最佳索引使用。

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

13-9：创建索引原则（使用场景）

  1. 最左前缀匹配原则
  2. =和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式
  3. 尽量选择区分度高的列作为索引,区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10 条记录
  4. 索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 create_time = unix_timestamp(’2014-05-29’);
  5. 尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
  6. Where 子句中经常使用的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接字段应该创建索引。
  7. like 模糊查询中，右模糊查询（321%）会使用索引，而%321 和%321%会放弃索引而使用全局扫描

13-10：最左前缀原则内部原理

因为索引是有序的，所以会在第一列排序的基础上对第二列排序，第二列排序的基础上对第三列排序，导致了最左前缀原则

13-11：创建索引的三种方式

1
create index indexName on tableName(fieldName);
1
2
3
4
5
6
create table t_301(
            tid int,
            tname varchar(20),
            gender varchar(1),
            index [indexName] (fieldName)
         );
1
alter table tableName add unique index indexName (fieldName);

13-12：索引叶子节点为什么不直接保存的记录地址而要存主键键值

14：优化方案

14-1：explain（优化）

explain 用于获取查询执行计划（即 MySQL 是如何执行一个查询的）

14-2：explain 主要包含的信息

Column JSON Name Meaning
id select_id The SELECT identifier - 优先级
select_type None The SELECT type - 查询类型
table table_name The table for the output row - 涉及的表或衍生表
partitions partitions The matching partitions - 判断查询是否高效的依据
type access_type The join type - 连接可能
possible_keys possible_keys The possible indexes to choose - 能够使用的索引
key key The index actually chosen - 实际使用的索引
key_len key_length The length of the chosen key - 索引的字节数
ref ref The columns compared to the index - 哪个字段/常数和索引一起使用
rows rows Estimate of rows to be examined - 统计信息
filtered filtered Percentage of rows filtered by table condition - 过滤的百分比
Extra None Additional information - 额外信息

14-2-1：id

sql 语句执行优先级，id 越大越先执行，同一等级从上到下执行

14-2-2：select_type

  1. simple：简单查询（不包含子查询和 union 查询）
  2. primary：复杂查询的最外围查询
  3. subquery：包含在 select 中的子查询（不在 from 字句中）
  4. derived：包含在 from 子句中的子查询，结果会存放在临时表中
  5. union：union 中的第二个和随后的 select

14-2-3：type

  1. null：不用访问表或者索引，直接就能得到结果（例如索引列中取得最大值）
  2. system：查询的表只有一行的情况，system 是 const 类型的特例
  3. const：针对主键或唯一索引的等值查询扫描，最多只返回一行数据。const 查询速度非常快, 因为它仅仅读取一次即可。
  4. eq_ref：此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是=，查询效率较高。
  5. ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的最左前缀，可能会找到多个符合条件的行。
  6. range：扫描部分索引(范围扫描)，对索引的扫描开始于某一点，返回匹配值域的行，常见于 between、<、>、in 等查询
  7. index：表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
  8. all：全表扫描

14-3：为什么要优化

  • 查询性能低
  • 执行时间过长
  • 等待时间过长
  • SQL 写的太差(尤其体现在多表查询上)
  • 索引失效
  • 服务器参数(缓存,线程数)设置不合理
  • 项目需求不合理
  • …

14-3-1：数据库的优化（如果 mysql 数据过多，如何进行处理）

  1. 根据三大范式设计数据库
  2. 优化 sql 和索引
  3. 缓存
  4. 主从复制/主主复制
  5. 分区表
  6. 垂直拆分
  7. 水平拆分

14-3-2：创建时优化

  1. 尽可能使用 not null
  2. 使用紧凑的数据类型
  3. 为每张表设置 id
  4. 选择合适的引擎
  5. 根据三大范式设计

14-3-3：索引优化

  1. 优化索引字段
  2. 组合索引字段顺序
  3. 索引合并

14-3-4：查询优化

  1. 避免 select *
  2. 只需要一行数据时使用 limit 1
  3. join 时使用相同类型的列并建立索引
  4. 不要 order by rand()
  5. 使用预编译
  6. 拆分大的 delete 和 insert 语句

14-3-5：一条 SQL 语句执行得很慢的原因有哪些

  • 硬件问题
  • 没有索引或者索引失效
  • 数据过多
  • 服务器各个参数限制

14-3-6：分页查询优化

分页查询中随着 n 逐渐变大，就需要扫描更多的数据来定位，耗费大量的 IO 成本和时间成本

  1. limit offset,size（含子查询）
  2. id < max and limit size
  3. 覆盖索引 + join
  4. id < max and limit size（不含子查询）
  5. min<=id<=max

14-4：为什么数据库会选错了索引

在一些不断删除历史数据和新增数据的场景下，MySQL 会出现选错索引的情况

MySQL 在采样统计的时候，会默认选择 N 个数据页，然后统计这个数据页上的不同值的个数，然后取平均值，再乘以这个索引的总数据页数量，就得到了索引的基数，这个基数是个估计值，不准确。

频繁进行插入和删除的表中，统计信息可能不准确，使得优化器选错索引。

解决方案：

  1. 可以使用 analyze table t 的方法来对表的索引信息做重新统计，提高索引统计信息的准确性，该过程没有修改表的数据，只是对表加了 MDL 读锁。
  2. optimize table t，该方法会重建表并且重新统计表的索引信息。

如果还是不能解决问题，那么可以考虑采用下面 3 种方法：

  1. 使用 force index 的方法来强制 SQL 使用某个索引来执行。
  2. 考虑修改语句，引导 MySQL 使用我们期望的索引。
  3. 删除指定索引，重建其他更合适的索引来进行替代

15：主从复制

15-1：什么是主从复制

主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL 主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

15-2：主从复制的作用（好处，或者说为什么要做主从）

  • 高可用
  • 负载均衡
  • 读写分离

15-3：主从复制的原理（重中之重，面试必问）

  1. 主库使用 binlog 记录数据变动
  2. 从库复制 binlog 并读取到 relay log 中
  3. 从库根据 relay log 逐一执行

binlog 有三种方式记录：

  1. 基于 sql 语句的记录（statement）：记录执行的 sql 语句
  2. 基于行的复制（row）：记录行修改的数据
  3. 混合模式复制（mixed）：一般情况使用 statement 模式，对于 statement 无法复制的情况使用 row 复制

15-4：主从复制的几种方式

  1. 异步模式：主节点不会主动推送数据到从节点，库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理。
  2. 主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到 relay log 中才返回成功信息给客户端（只能保证主库的 Binlog 至少传输到了一个从节点上），否则需要等待直到超时时间然后切换成异步模式再提交。
  3. 指当主库执行完一个事务，然后所有的从库都复制了该事务并成功执行完才返回成功信息给客户端。因为需要等待所有从库执行完该事务才能返回成功信息，所以全同步复制的性能必然会收到严重的影响。

GTID：以前的主从复制通过偏移量读取 binlog，而 GITD 模式会生成全局事务 id，从库复制后比较事务是否执行。

16：数据库池与 JDBC

16-1：什么是数据库连接池？

数据库连接池（Connection pooling）是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对池中的连接进行申请，使用，释放。

16-2：数据库连接池种类

  • DBCP （Database Connection Pool）
  • C3P0
  • Proxool
  • BoneCP
  • Druid

16-3：传统的连接机制与数据库连接池的运行机制区别

传统连接:

  1. 装载数据库驱动程序
  2. 通过 JDBC 建立数据库连接
  3. 访问数据库，执行 SQL 语句
  4. 断开数据库连接。

使用了数据库连接池的机制：

  1. 程序初始化时创建连接池
  2. 使用时向连接池申请可用连接
  3. 使用完毕，将连接返还给连接池
  4. 程序退出时，断开所有连接，并释放资源

16-4：说说数据库连接池工作原理

  1. 连接池的建立:一般在系统初始化时，连接池会根据系统配置建立，并在池中创建了几个连接对象，以便使用时能从连接池中获取。连接池中的连接不能随意创建和关闭，这样避免了连接随意建立和关闭造成的系统开销。Java 中提供了很多容器类可以方便的构建连接池，例如 Vector、Stack 等。

  2. 连接池的管理:连接池管理策略是连接池机制的核心，连接池内连接的分配和释放对系统的性能有很大的影响。其管理策略是：

    • 当客户请求数据库连接时，首先查看连接池中是否有空闲连接，如果存在空闲连接，则将连接分配给客户使用；如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，如果没达到就重新创建一个连接给请求的客户；如果达到就按设定的最大等待时间进行等待，如果超出最大等待时间，则抛出异常给客户。
    • 当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值，如果超过就从连接池中删除该连接，否则保留为其他客户服务。
      该策略保证了数据库连接的有效复用，避免频繁的建立、释放连接所带来的系统资源开销。

  3. 连接池的关闭。当应用程序退出时，关闭连接池中所有的连接，释放连接池相关的资源，该过程正好与创建相反。

17：分库分表

17-1：为什么要分库分表？

目的就是为了缓解数据库的压力，最大限度提高数据操作的效率。

17-2：数据分表

  • 垂直分表：因为字段太多导致数据太多，可以切分字段到不同的表中
  • 水平分表：针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH 取模等），切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有 IO 瓶颈。

17-3：数据分库

  • 垂直分库：将一个系统中的不同业务拆分，放到不同的库，不同的服务器中
  • 水平分库：将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破 IO、连接数、硬件资源等的瓶颈。

17-4：分库分表带来的问题

  1. 分布式事务
  2. 多库结果集合并
  3. 跨库 join