Hermethus
2021/04/01
2
来自 头疼
约 7251 字
原创
最近更新： 2021/11/03 23:51

数据库基础 - MySQL

系列目录

计算机通信网络学习笔记

操作系统学习笔记

数据库基础 - MySQL

分布式系统学习笔记

一天学会 MySQL 数据库

在数据库操作，尤其是增删改当中，可能会出现一系列必须同时完成的语句，如果这些语句不能同时完成，会导致数据不一致问题。例如转账问题。

在这种情况下，应当有一种机制，保证这些数据库操作要么全部完成，否则就完全回滚。这就是MySQL的事务机制。

事务

事务是一个不可分割的最小工作单元，保证了一个业务的完整性。

事务的四大特征

A（Atomicity） - 原子性：事务是最小单位，不可分割。

C（Consistency） - 一致性：同一事务中的sql语句，必须同时成功或同时失败。

I（Isolation） - 隔离性：不同的事务是相互隔离的，不能互相影响。

D（Durability） - 持久性：事务一旦提交，就持久化到硬盘中，不可回滚。

MySQL的事务

MySQL的事务默认是关闭的。MySQL会自动提交任何一条sql语句。

要关闭自动提交/开启手动提交，要通过修改@@autocommit变量达成

set autocommit=0;
select @@autocommit;//查看autocommit变量值

关闭autocommit之后，每一条语句都必须commit才能写入数据库。

相反，开启autocommit之后，必须手动开启事务。

begin;--或者 start transaction;
--这里写要执行的sql语句
commit;--提交事务

--如果提交事务不成功，MySQL提供回滚操作
--commit成功之后，回滚就没用了
roolback;
--自动提交开启时，回滚也不起作用

事务的隔离性

事务的隔离级别

select @@global.transaction_isolation;
select @@transaction_isolation; --查看事务的隔离级别
set transaction isolation level read uncommited;--修改事务的隔离级别

隔离级别越高，异步操作带来的问题越少，同时效率也越低（因为需要额外的操作去保证一致性）。

  1. read uncommited 读未提交

    • 不同的事务在操作的过程中可以看见其他事务未提交的结果。

    • 脏读：一个事务读到了另一个事务未提交的数据。如果对这些脏数据进行进一步操作，则会造成未提交的数据依赖关系。

    • 可以用rollback造成欺骗，这是实际开发不允许的。

  2. read commited 读已提交

    • 只能看见其他事务已提交的结果，允许事务的异步提交，在访问同一张表时，会造成数据显示异常。

    • 不可重复读：假设一个事务操作过程中，另一个事务对表完成了修改，而造成本事务视角下数据前后不一致。

  3. repeatable read 可重复读(不可读已提交)

    • MySQL的默认隔离级别。一个进行中的事务不能读到别的事务任何的修改，保证前后读数据的一致性，但还是允许事务的异步提交，在访问同一条数据时，会造成数据显示异常。

    • 通过 MVCC 实现。

    • 幻读：一个事务进行期间，另一个事务进行了增删操作，而不影响本事务的视角；当本事务想要对同一条目进行操作时，会出现错误。

    • 例如：事务A进行期间，事务B删除了某条数据，此时事务A视角下这条数据依旧存在，但如果想修改这条数据就会出错。

  4. serializable 串行化(不可提交)

    • 事务A在操作一张表时，其他事务的操作会挂起，并在事务A提交之后再提交（或者等待超时失败）。

    • 没有数据异常问题，但是性能低。

MVCC 多版本并发控制

一般而言，数据库的读-读操作不存在冲突，不需要加锁；写-写操作大概率冲突，必须加锁，否则会造成更新丢失。而读-写冲突则是介于二者之间。

一般而言，出于性能考虑，我们倾向于不使用锁解决读-写冲突，于是提出了 MVCC 多版本并发控制。

MVCC 很好的实现了事务的隔离性，达到了读已提交和可重复读的级别。

实现方式

Mysql-MVCC多版本并发控制底层原理及实现机制

数据库的数据表中，除了我们自己定义的字段，还有三个隐藏字段：

  • DB_TRX_ID 最近修改事务ID

    • 6byte，记录创建这条记录/最后一次修改该记录的事务ID

  • DB_ROLL_PTR 回滚指针

    • 7byte，指向这条记录的上一个版本（存储于rollback segment里）

当一个事务A对一条记录进行了修改（但未提交），发生了以下事件：

  1. rollback segment 添加了一条该记录的旧版本，以链表形式存储。

  2. 修改当前记录以及最近修改事务ID，回滚指针指向刚刚生成的旧版本。

此时发生了写冲突，事务B也对该记录进行了修改，则有：

  1. rollback segment 链表头再添加了一条旧版本。

  2. 修改当前记录以及最近修改事务ID，回滚指针指向新生成的旧版本。

此时我们就拥有了一系列的旧版本。

此时一个事务要进行查询，会获得一个 read-view（快照），包含当前未提交（活跃）的所有事务ID。其查找目标是：查找一个最近提交的版本。于是它开始在旧版本中从新到旧查找：

  • 最近修改事务ID < 未提交事务的最小ID，则该版本已经提交，可读。

  • 最近修改事务ID > 未提交事务的最大ID，则代表最近修改事务ID所在的记录在 read-view 生成后才出现的，不可读。

  • 判断最近修改事务ID是否在活跃事务之中，如果在，则说明该版本尚未提交，不可读。否则可读。

对于读已提交隔离级别的实现方式，每次查询都会生成一个新的 read-view。

对于可重复读级别，每次查询都会使用第一次生成的 read-view。

锁

锁的粒度

数据库为不同等级的资源设置的不同粒度的锁，共有三级：

行级锁

粒度最小，发生冲突的概率最小，并发度最高。InnoDB 默认支持行级锁，但是只在 where 查询主键时生效，其他时候则是表锁。

但是开销较大，加锁较慢。锁是逐步获得的，所以可能出现死锁。在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。

会导致幻读等现象。（见#事务的隔离性）

表级锁

粒度最大，发生冲突的概率也大，并发度低。

MyISAM 只支持表锁。

开销小，加锁快，不会出现死锁。

页级锁

介于行级锁和表级锁之间，可能出现死锁。

悲观锁

就是一般的互斥锁，给资源上锁之后，其他进程对该资源的访问会被阻塞。

共享锁（读锁）：只对写操作互斥，其他进程依旧可以进行读操作。

排它锁（写锁）：排斥其他所有访问。

一般而言，当写操作较多时，建议使用悲观锁，可以较好地保证安全性。

乐观锁

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，如果数据被更新了，则回滚自己的操作。

可以使用版本号机制和CAS算法实现。

乐观锁适用于读多写少的场景，可以减少加锁带来的效率损失。但不适合写多的场景，因为可能会频繁遇到冲突和回滚。回滚的消耗要大于加锁。

版本号机制

数据库中每个表有一个version字段。每次对表进行更新时，先读取版本号，更新结束后，再检查版本号，如果一致，则更新数据和版本号，否则回滚。

  1. A在更新表时，先读取version字段，假设version=1。

  2. A操作的同时，假设B进行了更新并修改版本号version=2。

  3. A操作结束后，检查版本号，发现被修改了，于是放弃更新，回滚。

  4. 如果是A先提交的修改，则B的更新回滚。

CAS算法

涉及到三个数：

  • 需要读写的内存值V

  • 进行比较的值A

  • 想要写入的新值B

在提交前，检查V和A是否相等，相等则以原子方式将用B更新V的值，否则不进行操作。

缺点：

  • 失败时，CAS算法会用自旋（即循环访问）方式不断尝试直到成功，比较消耗CPU。

  • 只能保证一个值的锁。

  • ABA问题：因为检查的是值相等，如果值曾经被修改又改回来，CAS算法是检测不出来的。

索引

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据。

优点

  • 索引是排好序的快速查找数据结构。作用是提高查询效率，降低I/O成本。

  • 索引会影响sql语句中where的查找条件和order by的排序，可以降低数据排序的成本，降低CPU消耗。

缺点

  • 大表的索引文件往往很大，要存在磁盘当中。

  • 使用索引之后，会大大提高查询速度，但是会降低更新表（增删改）的效率，因为要连同索引一块修改。

  • 索引需要不断优化，如业务环境变化。

索引的分类

  • 单值索引：一个索引只包含单个列。一个表可以有多个单值索引。
  • 复合/联合索引：一个索引包含多个列。最佳左前缀法则！
  • 唯一索引：索引列的值必须唯一，但允许有空值。

使用索引的情况

  • 主键自动创建索引
  • 频繁作为查询条件的字段
  • 被统计count或者分组group by的字段

不创建索引的情况

  • （百万级别以下的）小表没必要建索引
  • 频繁更新的字段不应创建索引
  • 不会作为查询条件的字段
  • 有大量重复数据的字段

基本语法

CREATE [UNIQUE] INDEX 索引名 ON 表名(列名1, 列名2,...);
ALTER TABLE 表名 ADD INDEX 索引名(列名);
--如果列是text或blob格式需要：列名(长度)
DROP INDEX [索引名] ON 表名;
SHOW INDEX FROM 表名;

索引的实现

有B-Tree索引、Hash索引（精准查询高效，不支持范围查询）、全文索引、R-Tree索引。

只介绍常见数据库引擎使用的B-Tree索引。

B-Tree 与 B+Tree

B-Tree是一种树，可以对数据进行顺序组织，往往不是二叉树。在每个节点中会存储许多的数据值，从左至右递增。每个节点的数据量为d，称为B-Tree的度。

一个节点如果有n个值，那么由这n个值可以分出n个指针指向下一层节点。

每个节点分别包含索引键和指向对应数据记录物理地址的指针。保证能在log(n)的复杂度下获得相应数据。

B+Tree

B+Tree是对B-Tree的一种改进，优化了区间索引的性能。也是 InnoDB 使用的索引结构。

  • B+Tree的非叶子节点不保存数据，只存储冗余索引。

  • 一个磁盘块可以放更多的索引，减少I/O次数。一般一个节点是16K大小。

  • 所有数据保存在叶子结点中，且叶子结点会带有顺序访问指针，构成一个双向链表。这样，进行区间访问就无须再从根节点重新进行I/O。

树的根节点常驻内存，其他节点存储在磁盘块中，所以使用索引时磁盘I/O次数最多为h-1。

  • 一般情况下，每个节点包含的数据项很多，保证树的高度很低，一般不超过4，可以减少磁盘I/O次数。

复合索引的最佳左前缀法则

使用复合索引查询时，使用的查询字段应当从索引最左边的字段开始依次使用，且中间不应跳过字段。

原因：B+Tree的联合索引是按照字段从左到右依次排序的，只有在前一个字段相同的情况下，下一个字段才是有序的；跳过了前一个字段，后面的字段是无序的。

索引失效与优化

覆盖索引

select的字段越精确越好，在索引字段的范围内可以提高效率，尽量不用通配符*。

这样SQL只需要通过索引就能返回数据。最差的情况也是扫描索引，而不需要去进行磁盘数据I/O。

最佳左前缀法则

使用复合索引查询时，如果不从最左边开始，则索引失效，全表扫描。

如果中间有跳过的情况，则索引从跳过的字段开始失效。

优化：where的条件尽量只使用索引字段，且要按顺序从左到右使用

在索引列上做计算、函数、类型转换等操作

优化：少在索引列上使用计算、函数等操作

范围操作后面的索引失效

  • 包括大于、小于操作等
  • 不等于<>, !=
  • 使用or连接
  • is not null（is null可以索引）

上面的情况大多属于一种广义的范围操作。

优化：应尽量使用精确匹配 =, in, is null。

通配符开头的like

select * from xxx where xx like '%abc'

优化：模糊查询尽量只在右边模糊，或者使用覆盖索引。否则会全表扫描。

字符串不加单引号

varchar类型不能丢掉单引号。

  • '2000'是varchar类型，但是用数值的2000也可以搜到（SQL进行了自动的类型转换），但是会导致索引失效。属于类型转换操作导致的失效。

Mysql的常见数据库引擎

mysql的两种引擎

主要介绍两种：InnoDB 和 MyISAM，主要是因为 MyISAM 是 MySQL 5.1 之前版本的默认引擎，InnoDB 是 MySQL 现在的默认引擎。

InnoDB

  1. InnoDB 相对于 MyISAM 最重要的变化，是支持具有提交、回滚和崩溃恢复能力的事务机制。

  2. 支持行级锁和表级锁。默认行级锁，降低冲突发生概率，提高写并发。这让 InnoDB 在多核心场景下的吞吐量大大提高。

  3. InnoDB 的数据本身就是按B+树组织的主键索引。叶子节点包含了完整的数据记录，称为“聚簇索引”。

    • 如果没有给一个表设置主键，InnoDB 会生成一个隐式的主键 row_id。

    • InnoDB 推荐使用自增的整型数作为主键。整型适合做比较，而且比字符串占空间小；自增会保证新节点按顺序加入，减小了B+树进行平衡的额外开销。

  4. 支持外键约束，即约束不同表中数据的一致性。

  5. 会在内存中建立高速缓存和索引。

MyISAM

  1. 不支持事务。这让 MyISAM 可以提供较高效率的查询，适合数据仓库等 select 查询频繁的应用。

  2. 使用表级锁，使得数据的插入和修改效率较低。当读写请求都很多的时候会造成写进程饿死。

  3. 索引文件与数据文件分离，称为“非聚簇索引”。这让 MyISAM 可以提供多样化的索引。

  4. 支持针对 char、varchar 和 text 中的全文索引。

  5. 数据可被压缩，存储空间较小。

数据库八股文

评论区