MySQL知识系统学习（基础知识篇）------保姆级教学

最新推荐文章于 2023-12-07 22:31:20 发布

我是飞酱

最新推荐文章于 2023-12-07 22:31:20 发布

阅读量282

点赞数

文章标签： mysql 索引

本文链接：https://blog.csdn.net/fxhhh123/article/details/108101059

版权

前言

前面我们先介绍一下MySQL本身的一些锁，事务，引擎相关知识内容，后面再对SQL语言进行一个介绍。（内容参考《高性能MySQL》）

并发控制

读写锁

1.共享锁：也叫读锁。读锁是共享的，是不阻塞的。因为像读取数据这种操作不管执行多少次，多少并发，都不会产生数据一致性问题，所以不需要阻塞。
2.排它锁：也叫写锁。写锁是私有的，需要阻塞，一个写锁会阻塞其他的读锁和写锁。因为存在写操作，如果我们读取在写完之前就读取数据，那么可能读取到的数据只能旧值或者是一个中间值，那么势必会存在隐患。

锁粒度

因为就像上述知识中的排它锁，它会导致写锁和读锁阻塞，这样就会使效率十分的低下，并且频繁的加锁解锁和对锁资源的管理都是一笔不晓得开销，所以MySQL选择了一种可以提高资源并发性的一种方式，就是将锁的粒度进行一个可控设置。
1. 表锁
表锁是MySQL的最基本的锁策略，并且是开销最小的策略。表锁的方式是当对表有写操作时候就会锁住整张表的读写操作，但是其他表的读写操作不会受到影响。（MyISAM默认）
2. 行锁
行锁可以最大程度的支持并发处理（同样的锁粒度的减小会带来更大得开销）。行锁的方式是当对数据的某行或者某些行进行写操作时候，会阻塞对这些行的读写操作，但是其他行的读写操作不会受到影响（InnoDB默认）。
3. 间隙锁
间隙锁相当于是对可能存在的行进行一个行锁定，例如我们查询grade > 90的学生，此时此时有91个学生，分数1 2 … 91，那么此时MySQL不仅会锁住grade = 91的行，并且会对其后面的行也进行锁定，假如这时候想要插入一个grade = 92的学生，那么该事务就需要等待（因为此时grade = 92的锁已经被上一个事务所占有）。

注意：
1.不光是针对这种范围查询会上间隙锁，针对于等式查询也会上，例如我们查询一个grade = 100的学生，表中没有该数据，事务也会锁住grade = 100的行。
2.因为范围查询会使间隙锁会锁住多行，所以一般会造成严重的锁等待，所以在写SQL语句时需要尽量减少范围查询的使用。

事务

事务通俗点说就是一组SQL语言的原子操作，或者说是一个执行单元。即该组SQL语言只能全部执行，如果有一条操作崩溃，那么其他的操作全部作废。
事务必须具有四条特性（ACID）
1. 原子性（Atomicity）
即一个事务要么执行，要么不执行，不允许存在执行一半。
2. 一致性（Consistency）
数据总是从一个状态转换到另一个状态。即如果像事务执行一半崩溃则事务失败，那么数据库就不会保存这个事务的状态。
3. 隔离性（Isolation）
一个事务提交前的数据不允许其他事务读取。这个不一定非要遵守，因为这个根据设置的隔离级别不一样可能会打破（例如未提交读）。
4. 持久性（durability）
一旦事务提交成功，那么就代表数据已经保存在了数据库中，就算数据库崩溃，也能够恢复。（写入硬盘中）

事务的隔离级别

1. 未提交读（READ UNCOMMITED）
字如其名，就是一个事务可以读取到其他事务未提交的数据，这样十分容易出现 “脏读”（后面解释）的情况，一般情况下不推荐这个隔离级别。
2. 提交读（READ COMMITED）
该隔离级别是：只能够读取事务提交后的数据，这样确实解决了脏读的问题，但是又出现了一个新问题 “不可重复读”（后面解释）。
3. 可重复读（REPEATABLE READ）
该隔离级别是：会对事务中的操作的表或者行加悲观锁（锁是事务开始时加锁，事务提交或者失败时释放），这样就能够解决“不可重复读问题”，但是这样又会出现一个新问题 “幻读”（后面解释）。
4. 可串行化（SERIALIZABLE）
该隔离级别是：相当于给事务进行了一个排序操作，让事务挨个执行，这样能够解决上述的所有问题，但是问题就是效率十分低下，如果不是非常需要保证数据强一致性的情况下不推荐使用。

隔离级别出现的问题

1. 脏读
一个事务读取到了其他未提交事务的数据。
2. 不可重复读
不能够多次执行同一SQL语句。
例：一个事务中先后执行了两次相同的SQL语句，第一次执行时候没问题，在第一次到第二次执行的间隔期间，有一个事务对该SQL语句的某些数据进行了修改，这样就会导致两次相同的SQL语句得到的结果是不同的。
3. 幻读
执行相同SQL语句可能会返回比期望值更多的数据。
例：如果我们执行SQL语句select count(*) from student where grade = 90;
一开始我们返回的是9个人，但是中间又有一个事务插入了一个grade = 90的学生，然后后面又执行了一次该SQL语句，那么此时返回的就是10个人。

MVCC（多版本并发控制）

MVCC是MySQL提出的一种针对于 “幻读” 的解决方案，使用该方案能够解决 “幻读” 问题。
不同存储引擎使用的MVCC是存在差异的，这里我们参考InnoDB的MVCC实现方式。

InnoDB是通过给表中的数据添加两行隐藏行，分别用于保存该行的创建时间和删除时间。（这里所说的时间并不是真正的时间，而是类似于一种版本号，该版本号和事务的版本号保持一致，事务的版本号是顺序切唯一的）

下面是针对于隔离级别：REPEATABLE READ 下的MVCC具体操作。
SELECT
有两个对比操作：
1.只查询创建版本号小于等于当前事务版本号的数据。这样能保证自己读取的数据是在该事务之前就被提交了或者是事务中操作的。
2.只查询删除版本号大于当前事务版本号的数据。这样能保证该行数据是在后序的事务中才被删除的，当前事务查询是没有问题的。
DELETE
删除掉数据后将自己的版本号写入数据的删除时间中。
INSERT
插入一行数据后，需要将自己的版本号写入数据的创建时间中。
UPDATE
（首先需要保证操作的数据行的删除行未定义，或者大于当前事务，不然修改操作不存在）该操作比较特殊，他是先插入一条新数据后，将自己的事务版本号写入新行的创建时间，然后把原数据的删除时间写入自己事务版本号。

日志管理

我们需要知道对于一个数据库来说，数据不重要，日志才是最关键的，有了日志就算系统宕机了，我们也可以通过日志进行一个恢复，所以也有个说法叫Write Ahead Log（预写日志），即先将操作写入日志，再将数据从内存写入磁盘。

使用日志的另一个重要原因：我们将数据修改后如果直接写入磁盘，这时候是一个随机IO，效率很低，而我们将数据写入日志（顺序IO），则可以提高事务的提交速度。

1.redo log（重做日志,InnoDB）

作用：保证事务的持久性。就算服务器发生了宕机，事务的脏页还未写入硬盘，我们也可以通过redo log对脏页进行写入操作。
生命周期：当事务开始时就产生redo log信息（写入内存中的innodb的log buffer中，默认size = 8M），并且随着事务的执行过程来同步写入，当将redo log写入磁盘后则事务提交，之后则redo log释放。

三种模式的log_buffer写入磁盘情况（可自定义）
1.master thread每一秒将redo log buffer写入redo log file->磁盘。
2.每个事务提交时会将buffer写入file->磁盘。
3.事务提交时将buffer写入file，但是不保证写入磁盘。

2.undo log（回滚日志,InnoDB）

作用：保存了事务上一个状态（事务开始前）的数据信息，可以用作事务的回滚。
生命周期：在事务开始前产生undo log，当事务执行结束以后，将undo log放入待清理链表，由线程判断该log所包含的数据（事务前）是否被其他的事务所需求，

3.bin log（二进制日志,Server）

主要是用于记录整个数据库相关数据修改信息，包括多种的存储引擎，并且记录的格式是逻辑日志，即SQL语句。

一般用于主从复制的同步情况，由slave向master发送sync请求，将master的bin log发给slave用于从机的同步

死锁

死锁是指多个事务在同一资源上相互占用，并且请求对方已占用的资源，使得多个事务进入了无休止的等待。
为了解决这种问题，数据库系统实现了多种死锁的检验和死锁超时机制。InnoDB的实现方法是：当出现死锁时，将持有最少数量行锁的事务回滚。

存储引擎

InnoDB

InnoDB是MySQL默认的事务型引擎，也是使用最广泛，最重要的引擎。它被设计出来用于解决大量的短期事务，短期事务的大部分情况是正常提交，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型事务的需求中也很流行。

InnoDB采用MVCC来实现高并发，并且实现了四个隔离级别，默认的隔离级别是：REPEATABLE READ（可重复读），并且通过 “间隙锁” 策略防止幻读行的出现。间隙锁使得InnoDB不仅仅锁定查询涉及到的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

InnoDB表是基于聚簇索引建立的（具体在索引部分讲解）。InnoDB的索引结构和MySQL的其他存储引擎差距较大，聚簇索引对主键查询有很高的性能，不过它的二级索引（非主键索引）中必须包含主键列，所以说如果主键列很大的话会导致索引也很大。因此，如果表上索引较多的话，主键应当尽可能的小。

。。。。等等等

MyISAM

在MySQL5.1版本之前，MySQL默认存储引擎是MyISAM。MyISAM提供了大量特性。例如：全文索引，压缩，空间函数等。但是MyISAM并不支持事务和行锁，而且数据库崩溃后无法安全恢复。但是对于只读数据，或者表比较小，可以忍受恢复操作也可以使用MyISAM。

MyISAM会将表的信息存储在两个文件中：数据文件（.MYD）和索引文件（.MYI，即非聚簇索引）。

MyISAM针对于并发使用的是表锁，当读取表的时候会对表加共享锁，当有写操作时候会对表加排它锁，但是当对表有读操作时候时可以执行插入操作（这里叫做并发插入）。

等等等。。。。

InnoDB和MyISAM对比
1.InnoDB支持事务，MyISAM不支持。
2.InnoDB使用行锁，MyISAM使用表锁。
3.InnoDB使用聚簇索引，MyISAM使用非聚簇索引。
4.InnoDB不支持全文索引，MyISAM支持全文索引。
5.InnoDB支持自动崩溃恢复，MyISAM需要手动恢复。
6.执行count(*)时，InnoDB需要进行全表统计，MyISAM可直接读取已缓存的行数。
7.MyISAM表可被压缩，存储空间小。InnoDB使用较多内存和存储，它需要在内存中建立专属缓冲池用于高速缓冲数据和索引。

范式与反范式

范式

1.第一范式
确保每列的原子性，即每列不能在进行拆分。（关系数据库的最基本要求）
2.第二范式
保证每一列都与主键相关，而不是主键（指联合主键）中的一个部分。
3.第三范式
保证每列与主键直接相关，而不是传递相关。
例：某表（学号，学院，院长）主键：学号
学号 -> 学院学院 -> 院长
这样院长就不是和学号直接相关，所以不满足3NF。

范式的优缺点

优点：
1.范式的更新操作通常比反范式快。
2.当数据进行较好的范式化后，冗余就会很少，所以只需要修改更少的数据。
3.范式化的表通常更小，可以更好的放在内存中，所以操作更快。
缺点：
缺点在于表之间通常需要关联。例如本来如果使用非范式的表可能所有数据在一张表中，我们只需要索引即可查询，但是使用了范式以后最少需要查询两个表，这样导致资源消耗增加，也可能索引失效。