ZHANGKB

【SQL】笔记

2022/11/11 16:07:02

主键

选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。

索引

可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

检测数据库连接是否有效

SELECT 语句可以进行运算：SELECT 100+200;。

上述查询会直接计算出表达式的结果。虽然 SELECT 可以用作计算，但它并不是 SQL 的强项。但是，不带 FROM 子句的 SELECT 语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条 SELECT 1;来测试数据库连接。

并发

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

隔离级别

SQL 标准定义了 4 种隔离级别，分别对应可能出现的数据不一致的情况：

Isolation Level脏读（Dirty Read）不可重复读（Non Repeatable Read）幻读（Phantom Read）
Read Uncommittedopen in new windowYesYesYes
Read Committedopen in new window-YesYes
Repeatable Read--Yes
Serializable---

在 Serializable 隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

虽然 Serializable 隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用 Serializable 隔离级别。

设置隔离级别：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

脏读（Dirty Read）

B事务 读到 A事务 更新后但未提交的数据，如果 A事务 回滚，那么 B事务 读到的数据就是脏数据，这就是脏读（Dirty Read）。

不可重复读（Non Repeatable Read）

不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据（提交），那么，在第一个事务中，两次读取的数据就可能不一致。

幻读（Phantom Read）

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

  1. B 事务查询 表1 中 id=1 的数据，假设此时 表1 中没有此数据，则返回空。
  2. A 事务在 表1 中插入 id=1 的数据。
  3. B 事务再次查询 表1 中 id=1 的数据，依旧查不到。
  4. B 事务更新 表1 中 id=1 的数据，可以更新成功。
  5. 此时 B 事务查询表1 中 id=1 的数据，就能查出来了，查出来的数据时第 4 步更新的数据。

可见，幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。