Java数据库问题整合

一、数据库简介

1.1 简介

• 数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种
  用户或应用共享的数据集合。
• 数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软
  件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。
  用户通过数据库管理系统访问数据库中的数据。

---

1.2 常见的数据库管理系统

• Oracle：Oracle数据库被认为是业界目前比较成功的关系型数据库管理系统。Oracle数据库可以运行在UNIX、Windows等主流操作系统平台，完全支持所有的工业标准，并获得最高级别的ISO标准安全性认证。
• MySQL：MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS(Relational Database Management System，关系数据库管理系统) 应用软件。
• DB2：DB2是IBM公司的产品，DB2数据库系统采用多进程多线索体系结构，其功能足以满足大中公司
  的需要，并可灵活地服务于中小型电子商务解决方案。
• Microsoft SQL Server：SQL Server 是Microsoft 公司推出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点。

---

1.3 三大范式

• 第一范式：无重复的列。当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
• 第二范式：属性完全依赖于主键 [ 消除部分子函数依赖 ]。如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。 为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。
• 第三范式：属性不依赖于其它非主属性 [ 消除传递依赖 ]。设R是一个满足第一范式条件的关系模式，X 是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF. 满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其
  它表中已包含的非主关键字信息。

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是
否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非
主键列。

---

二、SQL语言

2.1 SQL语句分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。

2.2 数据库优化

• 1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
• 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫
  描，如：

select id from t where num is null

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

• 3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。
• 4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致
  引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10 union all select id from t where Name = 'admin'

• 5.in 和 not in 也要慎用，否则会导致全表扫描，如：
  select id from t where num in(1,2,3)
  对于连续的数值，能用 between 就不要用 in 了：
  select id from t where num between 1 and 3
  很多时候用 exists 代替 in 是一个好的选择

---

三、数据完整性

3.1 数据库的完整性

用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性
确保数据的完整性 = 在创建表时给表中添加约束
完整性的分类：

• 实体完整性(行完整性):
• 域完整性(列完整性):
• 引用完整性(关联表完整性):

主键约束：primary key （实体完整性）
唯一约束：unique [key] （实体完整性）
非空约束：not null （域完整性）
默认约束：default （域完整性）
自动增长：auto_increment （实体完整性）
外键约束: foreign key （引用完整性）
建议这些约束应该在创建表的时候设置
多个约束条件之间使用空格间隔

---

四、事务

（转载链接）

学习数据库的时候常常会接触到事务, ACID等概念，那么到底什么是数据库的事务，数据库事务又具有哪些特点，和ACID有怎样的关系，事务的隔离级别又是做什么的呢？。

4.1 事务及其四大特性

事务（Transaction）：访问并可能更新数据库中各种数据项的一个程序执行单元（unit），它通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起。当在数据库中更改数据成功时，在事务中更改的数据便会提交，不再改变。否则，事务就取消或者回滚，更改无效。

举个例子来说，张三给李四转了1000元钱，那么在数据库操作时，就要先把张三的账户减去1000元，再把李四的账户加上1000元，两部分操作放在一起，才是一个完整的转账过程，也可称之为事务。

（1）原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，这和前面两篇博客介绍事务的功能是一样的概念，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

就像你买东西要么交钱收货一起都执行，要么要是发不出货，就退钱。

（2） 一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

打个比方，你买东西这个事情，是不影响其他人的。

（3）隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

（4）持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

打个比方，你买东西的时候需要记录在账本上，即使老板忘记了那也有据可查。

---

4.2 InnoDB引擎的事务实现

InnoDB是mysql的一个存储引擎，大部分人对mysql都比较熟悉，这里简单介绍一下数据库事务实现的一些基本原理，在本地事务中，服务和资源在事务的包裹下可以看做是一体的。

而事务的ACID是通过InnoDB日志和锁来保证。事务的隔离性是通过数据库锁的机制实现的，持久性通过redo log（重做日志）来实现，原子性和一致性通过Undo log（回撤日志）来实现。Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了roll back语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。 和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将RedoLog持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是RedoLog已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

---

4.3 事务的隔离性及隔离级别

以上介绍完事务的四大特性(简称ACID)，现在重点来说明下事务的隔离性，当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，在介绍数据库提供的各种隔离级别之前，我们先看看如果不考虑事务的隔离性，会发生的几种问题：


3、幻读：一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。如果事务中都使用快照读，那么就不会产生幻读现象，但是快照读和当前读混用就会产生幻读。关于快照读与当前读可参见《InnoDB对MVCC的实现》。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就与脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本上可分为以下两种：

• 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
• 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

---

4.4 四种隔离级别

为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。

4个事务隔离级别：

• Read uncommitted (读未提交)：最低级别，以上问题均无法解决。
• Read committed (读已提交)：读已提交，可避免脏读情况发生。
• Repeatable Read（可重复读）：确保事务可以多次从一个字段中读取相同的值，在此事务持续期间，禁止其他事务对此字段的更新，可以避免脏读和不可重复读，仍会出现幻读问题。
• Serializable （串行化）：最严格的事务隔离级别，要求所有事务被串行执行，不能并发执行，可避免脏读、不可重复读、幻读情况的发生。

这四种隔离级别，分别有可能产生问题总结如下：

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。避免不可重复读需要锁行（某一行在select操作时，不允许update与delete）就行，避免幻读则需要锁表。如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，幻读不能通过行锁来避免，需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然隔离级别越高，越能保证数据的完整性和统一性，但是执行效率就越低，对并发性能的影响也越大。像Serializable这样的级别，就是以锁表的方式（类似于Java多线程中的锁）使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。

各类流行的数据库都实现了一些SQL标准中的事务隔离级别，但是他们的实现也是极其不一样的。Oracle仅仅实现了RC 和 SERIALIZABLE隔离级别。默认采用RC隔离级别，解决了脏读。但是允许不可重复读和幻读。其SERIALIZABLE则解决了脏读、不可重复读、幻读。MySQL支持全部4个隔离级别，但在具体实现时，有一些特点，比如在一些隔离级别下是采用MVCC一致性读，但某些情况下又不是。MySQL默认采用RR隔离级别，SQL标准是要求RR解决不可重复读的问题，但是因为MySQL通过nex-key lock在RR隔离级别下解决了幻读的问题。那么MySQL的SERIALIZABLE是怎么回事呢？MySQL的SERIALIZABLE采用了经典的实现方式，对读和写都加锁。

查看MySQL数据库当前事务的隔离级别：

select @@tx_isolation;

 
 

 
 
1

在MySQL数据库中设置事务的隔离级别：

set  [glogal | session]  transaction isolation level 隔离级别名称;
set tx_isolation=’隔离级别名称;’

 
 

 
 
1
2

互联网项目中MySQL用什么事务隔离级别

Mysql默认的事务隔离级别是可重复读(Repeatable Read)，那互联网项目中Mysql也是用默认隔离级别，不做修改么？ OK，不是的，我们在项目中一般用读已提交(Read Commited)这个隔离级别！ 居然是读已提交。

我们先来思考一个问题，在Oracle、SqlServer中都是选择读已提交(Read Commited)作为默认的隔离级别，为什么Mysql不选择读已提交(Read Commited)作为默认隔离级别，而选择可重复读(Repeatable Read)作为默认的隔离级别呢？

我们先明白一点！项目中是不用读未提交(Read UnCommitted)和串行化(Serializable)两个隔离级别，原因有二： 采用读未提交(Read UnCommitted)，一个事务读到另一个事务未提交读数据，这个不用多说吧，从逻辑上都说不过去！采用串行化(Serializable)，每个次读操作都会加锁，快照读失效，一般是使用Mysql自带的分布式事务功能时才使用该隔离级别！也就是说，我们该纠结都只有一个问题，究竟隔离级别是用读已经提交呢还是可重复读？ 接下来对这两种级别进行对比，讲讲我们为什么选读已提交(Read Commited)作为事务隔离级别！

---

4.5 锁

MySQL间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP LOCK）。举例来说，假如user表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

select * from  user where user_id > 100 for update;

 
 

 
 
1

这是一个范围条件的检索且要求加上排他锁，InnoDB不仅会对符合条件的user_id值为101的记录加锁，也会对user_id大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读（为了防止幻读去锁表则影响太大，会影响效率），以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了user_id大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要（发生幻读时的binlog，如果直接拿到备库去执行会发生了主备数据不一致的严重问题）。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件；当然，对一条不存在的记录加锁，也会有间隙锁的问题。

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁。

要禁止间隙锁的话，可以把隔离级别降为读已提交，或者开启参数innodb_locks_unsafe_for_binlog。

RC与RR在锁方面的区别

1、RR要用到间隙锁，而RC则没有间隙锁。因为MySQL的RR需要间隙锁来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR；在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多；
2、 RC 隔离级别，通过 where 条件过滤之后，不符合条件的记录上的行锁，会被释放掉，但是RR隔离级别，即使不符合where条件的记录，也不会释放行锁和间隙锁，所以从锁方面来看，RC的并发应该要好于RR；
3、RC隔离级别时，事务中的每一条select语句会读取到他自己执行时已经提交了的记录，也就是每一条select都有自己的一致性读ReadView; 而RR隔离级别时，事务中的一致性读的ReadView是以第一条select语句的运行时，作为本事务的一致性读snapshot的建立时间点的，只能读取该时间点之前已经提交的数据。

4.6 InnoDB底层

深入了解MySQL存储引擎-------InnoDB