数据库基础知识

最新推荐文章于 2025-07-12 20:12:26 发布

原创最新推荐文章于 2025-07-12 20:12:26 发布 · 237 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #设计

设计数据库的奥义

*步骤

挑出事物，挑出你希望表描述的某样事物
列出关于那样事物的信息列表
使用信息列表，拆分成小块信息，便于组织表

*一些性质

原子性：已经被分割至最小块，已经不应该再被分割。
第一范式：每个数据行必须包含具有原子性的值。每个数据行必须有独一无二的识别行，人称主键（Primary Key）。
第二范式：符合第一范式，部分函数依赖
第三范式：符合第二范式，没有函数依赖

*一些规则

具有原子性数据的列中，不会有多个类型相同的值

如下表中的ingredient列，查找起来要爆炸的

food_name	ingredient
bread	flour, milk, egg, yeast, oil
salad	lettuce, tomato, cucumber

* 具有原子性数据的表中，不会有多个存储同类数据的列

如下表的student1，student2，student3

teacher	student1	student2	student3
Ms.Alice	Joe	Ron	Kelly
Mr.Howard	Sanjava	Tim	Julia

* 函数依赖性：T.x -> T.y。在关系表T中，y列函数依赖于x列（y = T(x)）

*规范表的优点

规范化表中没有重复的数据，可以减小数据库的大小
因为查找的数据较少，你的查询会更为快速

事务与ACID

*ACID

原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务而言，不可能只执行其中的一部分。
一致性：数据库总是从一个一致性的状态转移到另一个一致性的状态。比如银行转账，不会出现一个账户少了200元而一个账户没多200元。
隔离性：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。具体看（隔离级别）。
持久性：一旦事务提交后，则其所做的修改就会永久保存到数据中。

*事务

事务内的语句，要么全部执行成功，要么全部执行失败。以一个银行应用为例：

可以用START TRANSACTION 语句开始一个事务，然后要么使用COMMIT提交事务将修改的数据持久保留，要么使用ROLLBACK撤销所有的修改。

START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
COMMIT;

数据库事务隔离级别

READ UNCOMMITTED（未提交读）：事务的修改，即使没有提交，对其他事务都是可见的。事务可以读取未提交的数据，这也被称为脏读。例如：银行账户的事务即使还没有提交，但是也能看到一个账户少了200元。
READ COMMITTED（提交读）：只能看到已经提交的事务做出的修改。换句话说，一个事务从开始直到提交，所做的任何修改对其他事务是不可见的。有时也叫“不可重复读”，因为在一个事务中两次执行相同的查询，结果可能会不一样。（大多数数据库默认）
REPEATABLE READ（可重复读）：保证在同一事务中，多次读取相同记录的结果是一致的。但是不能解决幻读的问题，不过InnoDB通过多版本并发控制（MVCC）解决了幻读的问题。（MYSQL默认）
SERIALIZABLE（可串行化）：通过强制事务已串行的方式执行，避免了所有问题。会对操作的每一行数据加锁，导致的问题就是大量的超时与锁争用问题。

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读？
READ UNCOMMITED	有	有	有	不是
READ COMMITTED	无	有	有	不是
REPEATABLE READ	无	无	有	不是
SERIALIZABLE	无	无	无	是

多版本并发控制（MVCC）

在很多情况下避免了加锁操作，因此开销更低。

通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时间看到的数据可能是不一样的。也分为悲观并发控制与乐观并发控制。

*InnoDB的MVCC实现

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现。这两个列，一个保存了行的创建时间（系统版本号），一个保存行的过期时间。每开始一个新的事务，系统版本都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

*在REPEATABLE READ隔离级别下，具体MVCC具体操作：

SELECT：（1）InnoDB只查找版本早于当前事务版本的数据行（行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务本身插入或修改过的。（2）行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
DELETE：InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE：InnoDB为插入一行记录，保存当前系统版本号作为系统版本号，同时保存当前系统版本号到原来的行作为行删除标识。

死锁

概念：指两个或者多个事务在同一资源上相互占用，并请求对方占用的资源，从而导致恶性循环的现象。

**事务一**
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 AND date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 AND date = '2002-05-02';
COMMIT;

**事务二**
START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 AND date = '2002-05-02';
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 AND date = '2002-05-01';
COMMIT;

如果凑巧，两个事务都执行第一条UPDATE语句，更新了一行数据，同时也锁定了改行数据，接着每个事务都尝试去执行第二条UPDATE语句，却发现改行被对方锁定了，然后都等待对方释放锁，同时又持有对方的锁，则陷入死循环。

检测死锁的循环依赖或者当查询的时间达到锁等待超时的设定时会放弃锁请求。InnoDB存储引擎，有死锁检测的方法：将持有最少行级排它锁的事务进行回滚。

JDBC

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.SQLException;  

public class ConnectionDemo{  
    //定义MySQL驱动程序  
    public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";  
    //定义MySQL数据库的连接地址  
    public static final String DBURL = "jdbc:mysql://localhost:3306/xiaowei";  
    //MySQL数据库的连接用户名  
    public static final String DBUSER = "root";  
    //MySQL数据库的连接密码  
    public static final String DBPASS = "android";  
    public static void main(String[] args){  
        //数据库连接  
        Connection con = null;  
        try{  
            //加载驱动  
            Class.forName(DBDRIVER);  
        }catch(ClassNotFoundException e){  
            e.printStackTrace();  
        }  
        try{  
            con = DriverManager.getConnection(DBURL, DBUSER, DBPASS);  
        }catch(SQLException e){  
            e.printStackTrace();  
        }  
        System.out.println(con);  
        try{  
            //数据库关闭  
            con.close();  
        }catch(SQLException e){  
            e.printStackTrace();  
        }  
    }  
}

幻读与脏读

脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。
幻读：是指当某个事务在读取或修改范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取这个范围时，会产生幻行。例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。