【总结】数据库基础

该文章为知识总结的文章，如果是初学者，建议先从专栏学习：数据库专栏

一、能简单介绍一下什么是Mysql吗？

• MySQL 是一种关系型数据库
• 在Java企业级开发中非常常用，因为 MySQL 是开源免费的
• 阿里巴巴数据库系统也大量用到了 MySQL，因此它的稳定性是有保障的
• MySQL的默认端口号是3306

二、SQL 的分类

• DQL–数据查询语言：代表关键字:select

• DML–数据操纵语言：代表关键字:insert,delete,update

• DDL–数据定义语言：代表关键字:create ,drop,alter,

• TCL–事务控制语言：代表关键字:commit ,rollback;

• DCL–数据控制语言：代表关键字:grant,revoke.

三、数据库三大范式

1. 第一范式

数据库表中不能出现重复记录，每个字段是原子性的不能再分

学生编号	学生姓名	联系方式
1001	张三	zs@gmail.com,1359999999
1002	李四	ls@gmail.com,13699999999
1001	王五	ww@163.net,13488888888

• 学生编号重复，因为没有主键
• 联系方式既有邮箱和电话

因此需要修改成如下形式：

学生编号(pk)	学生姓名	email	联系电话
1001	张三	zs@gmail.com	1359999999
1002	李四	ls@gmail.com	13699999999
1003	王五	ww@163.net	13488888888

• 每个行必须唯一，且存在主键
• 列不能再分

2. 第二范式

要求所有非主键字段完全依赖主键，不能产生部分依赖

学生编号(PK)	教师编号(PK)	学生姓名	教师姓名
1001	001	张三	王老师
1002	002	李四	赵老师
1003	001	王五	王老师
1001	002	张三	赵老师
1001	002	爱迪生	赵老师

• 如果只是拿学生编号或者教师编号做主键，都不能确定唯一一行数据，所以采用联合主键
• 学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部门依赖了主键
  的一个字段教师编号，这就是第二范式部分依赖。

解决就是分表：

• 学生信息表

学生编号（PK）	学生姓名
1001	张三
1002	李四
1003	王五

• 教师信息表

教师编号（PK）	教师姓名
001	王老师
002	赵老师

• 教师和学生的关系表

学生编号(PK)	教师编号(PK)
1001	001
1002	002
1003	001
1001	002

3. 第三范式

非主键字段不能传递依赖于主键字段。

学生编号（PK）	学生姓名	班级编号	班级名称
1001	张三	01	一年一班
1002	李四	02	一年二班
1003	王五	03	一年三班

• 班级名称字段没有直接依赖于主键，班级名称字段依赖于班级编号，班级编号依赖于学生编号，那么这就是传递依赖

继续分表

• 学生信息表

学生编号（PK）	学生姓名	班级编号
1001	张三	01
1002	李四	02
1003	王五	03

• 班级信息表

班级编号	班级名称
01	一年一班
02	一年二班
03	一年三班

4. 总结

• 第一范式： 有主键，具有原子性，字段不可分割
• 第二范式：完全依赖， 没有部分依赖
• 第三范式： 没有传递依赖

数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求

四、事务的简介

• 当执行 DML 语句是其实就是开启一个事务
• 关于事务的回滚需要注意：只能回滚 insert、 delete 和 update 语句，不能回滚 select（回滚 select 没有任何意义），对于 create、 drop、 alter 这些无法回滚.事务只对 DML 有效果。
• 注意： rollback，或者 commit 后事务就结束了。

五、事务的特性

1. 原子性

• 整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。

undo log 名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的 sql 语句，他需要记录你要回滚的相应日志信息。

undo log 记录了这些回滚需要的信息，当事务执行失败或调用了 rollback，导致事务需要回滚，便可以利用 undolog 中的信息将数据回滚到修改之前的样子。

2. 一致性

• 关注数据的可见性，中间状态的数据对外部不可见，只有最初状态和最终状态的数据对外可见

数据库无法保证，需要从应用层角度考虑，用业务来实现。从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据

3. 隔离性

并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的

事务的隔离性是利用的是锁和 MVCC 机制。

至于 MVCC,即多版本并发控制(Multi Version Concurrency Control),一个行记录数据有多个版本对快照数据，这些快照数据在 undo log 中。

如果一个事务读取的行正在做 DELELE 或者 UPDATE 操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。

4. 持久性

在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。

事务的持久性是利用 Innodb 的redo log

六、并发事务出现的问题

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

七、事务隔离级别

1. 四个隔离级别

• 读未提交：允许一个事务可以看到其他事务未提交的修改，可能会导致脏读、幻读或不可重复读。
• 读已提交：允许一个事务只能看到其他事务已经提交的修改，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• 可重复读：确保如果在一个事务中执行两次相同的 SELECT 语句，都能得到相同的结果，不管其他事务是否提交这些修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• 串行化：将一个事务与其他事务完全地隔离，相当于加锁，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

InnoDB 存储引擎REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server) 是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。

八、数据库锁和并发策略

1. 常见的锁

• MyIsam 实现了表锁。表锁可以针对数据库表加锁，在锁的灵活性上不如行锁。表锁分为两种锁：读锁与写锁。
• InnoDB 存储引擎实现了行锁与表锁（意向锁）。行锁可以以行为单位对数据集进行锁定。行锁也分为两种锁：
  共享锁与排他锁。 InnoDB 对于 Update、 Delete、 insert 语句会自动给涉及的数据集隐式的加上排他锁。对于 select语句 InnoDB 不会加任何锁
  • 共享锁：允许一个事务读取一行，阻止其他事务获得相同数据集的排他锁。但允许其他事务获取共享锁。
    显示加锁 select * from table where … lock in share mode
  • 排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享与排他锁。但是可以对获取了排他锁的数据集进行单纯的查询访问。
    显示加锁 sql select * from table where … for update
• innoDB 的行锁的实现方式是基于索引项的。这意味着即使你尝试获取不同行的排他锁，若使用了相同的索引键，也可能会造成锁冲突。

表级锁和行级锁对比：

• 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

• 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

• 事务更新大表中的大部分数据直接使用表级锁效率更高；事务比较复杂，使用行级索很可能引起死锁导致回滚。

2. 页级锁

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

3. 乐观锁

乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据

实现：

时间戳就是在数据库表中单独加一列时间戳，比如“TimeStamp”，每次读出来的时候，把该字段也读出来，当写回去的时候，把该字段加 1，提交之前 ，跟数据库的该字段比较一次，如果比数 据库的值大的话，就允许保存，否则不允许保存，这种处理方法虽然不使用数据库系统提供的锁 机制，但是这种方法可以大大提高数据库处理的并发量

# 每一个记录都有version，会有冗余，可以一个表一个version
Select data,version from biao where id = #{id};
Update biao set data=#{data},version=vsersion+1 where id=#{id} and version = #{version};

# 一个表一个修改
begin;
Select version from version_biao where table_name = #{table_name};
Update biao set data=#{data} where id=#{id};
Update version_biao set version=vsersion+1 from  where version = #{version};
commit;

4. 悲观锁

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

实现：

sql 语句后边加上 for update

select id,nam from biao for update ;

5. InnoDB锁算法

• 间隙锁：锁定一个一个区间记录中的间隙，不包括记录本身

• 行锁：单个行记录上的锁

• 临键锁：锁定一个一个区间记录中的间隙以及记录本身

九、给定业务场景说明设计数据库的思路

1. 分析场景选择存储引擎，比如业务是读多还是写多？需不需要支持事务？
2. 数据库设计的原则，遵循三大范式，但是可以做一些适当的反三大范式
3. 字段的设计，选择类型

十、说一下什么是池化设计

• 我们常见的如java线程池、jdbc连接池、redis连接池等就是这类设计的代表实现。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。
• 池化设计会配置一些参数，比如初始线程数，最大线程数等等，还会有一个阻塞队列，当一些请求过多时，会采用一些拒绝的策略
• 在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。 连接池还减少了用户必须等待建立与数据库的连接的时间。

十一、一条SQL语句执行慢的原因？

1. 分类讨论

• 大多数情况是正常的，只是偶尔会出现很慢的情况。

• 在数据量不变的情况下，这条SQL语句一直以来都执行的很慢。

2. 针对偶尔很慢的情况

一条 SQL 大多数情况正常，偶尔才能出现很慢的情况，针对这种情况，我觉得这条SQL语句的书写本身是没什么问题的，而是其他原因导致的，那会是什么原因呢？

a. 数据库在刷新脏页（flush）

当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

刷脏页有两种场景会影响性能

• **redolog写满了：**redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。
• **内存不够用了：**如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。

b. 拿不到锁我能怎么办

这个就比较容易想到了，我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了，这个时候，我也没办法啊。

3. 针对一直都这么慢的情况

先通过EXPLAN去分析SQL语句是否用到索引，主要关注type，possible_key，keys这几个字段

a. 没用到索引

• 如果该字段没有加索引，那么自然没有用到索引
• 字段有索引，但却没有用索引，那说明出现了索引失效，比如说like 查询以%开头，或者不满足索引的最左原则，或者使用了Or关键字，或者索引字段的值有NULL值，或者使用了函数

b. 用到了索引，但是系统误判了

• 非主键索引存储的是主键索引的值，如果SQL语句走的非主键索引，那么还需要到主键索引去寻找对应的data
• 当一个SQL语句范围比较大，比如where x>0 and x < 20000或者没有where条件，那么可能命中的数据非常多，甚至命中全表，而全表扫描顶多就是O(n)，此时再走索引，还会有两次索引寻找的时间消耗，即使索引查找非常快
• 此时分析器就要判断是否走索引，采用的方式是抽样检测，来预测SQL语句范围中出现相同值的概率，比如在0-20000范围中，数字3可能出现了10次，也可能只出现1次，显然如果重复数字越少，走索引的性能越好