Mysql学习(索引、事务)

最新推荐文章于 2023-12-11 09:21:07 发布

微笑看你哭

最新推荐文章于 2023-12-11 09:21:07 发布

阅读量194

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/hxyascx/article/details/88324793

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

InnoD 数据页结构

前边我们简单提了一下页的概念，它是InnoDB管理存储空间的基本单位，一个页的大小一般是16KB。

数据页代表的这块16KB大小的存储空间可以被划分为多个部分，不同部分有不同的功能，各个部分如图所示：

在这里插入图片描述
在页的7个组成部分中，我们自己存储的记录会按照我们指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：

在这里插入图片描述
每一条数据除了记录真实数据还要记录一些额外信息包括：变长字段长度列表（记录varchar字段长度）、
Null值列表（Null的列）、记录头信息。

正式数据还可能包含三个隐藏值：row_id（不是必须存在，如果没有指定主键那么会自动生成该值，并当做主键）、transaction_id（事务ID）、roll_pointer（回滚指针）

记录头信息：主要包含 delete_mask（是否被删除）、n_owned（当前记录拥有的记录数 重要稍后介绍）、heap_no（表示当前记录在记录堆的位置信息）、record_type（示当前记录的类型，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录）、next_record （表示下一条记录的相对位置）
在这里插入图片描述
但是不管我们向页中插入了多少自己的记录，设计InnoDB的大叔们都规定他们定义的两条伪记录分别为最小记录与最大记录。这两条记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定的部分组成的，如图所示
在这里插入图片描述
由于这两条记录不是我们自己定义的记录，所以它们并不存放在页的User Records部分，他们被单独放在一个称为Infimum + Supremum的部分，如图所示：

整个记录按照主键从小到大的顺序会形成了一个单链表。最大记录的next_record的值为0，这也就是说最大记录是没有下一条记录了，它是这个单链表中的最后一个节点。
在这里插入图片描述
删除记录

删掉第2条记录后：
在这里插入图片描述
从图中可以看出来，删除第2条记录前后主要发生了这些变化：

第2条记录并没有从存储空间中移除，而是把该条记录的delete_mask值设置为1。
第2条记录的next_record值变为了0，意味着该记录没有下一条记录了。
第1条记录的next_record指向了第3条记录。
还有一点你可能忽略了，就是最大记录的n_owned值从5变成了4，关于这一点的变化我们稍后会详细说明的。

所以，不论我们怎么对页中的记录做增删改操作，InnoDB始终会维护一条记录的单链表，链表中的各个节点是按照主键值由小到大的顺序连接起来的。删掉的记录不会被删除，而是改变状态，代表当前位置可以被重新使用。

Page Directory（页目录)

现在我们了解了记录在页中按照主键值由小到大顺序串联成一个单链表，那如果我们想根据主键值查找页中的某条记录该咋办呢？

InnoDB会为把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page Directory中，所以在一个页中根据主键查找记录是非常快的，分为两步：

通过二分法确定该记录所在的槽。
通过记录的next_record属性遍历该槽所在的组中的各个记录。

在这里插入图片描述
当数据不断增加时就会出现下面都效果：

File Header（文件头部）

上边唠叨的Page Header是专门针对数据页记录的各种状态信息，比方说页里头有多少个记录了呀，有多少个槽了呀。我们现在描述的File Header针对各种类型的页都通用，也就是说不同类型的页都会以File Header作为第一个组成部分，它描述了一些针对各种页都通用的一些信息，比方说这个页的编号是多少，它的上一个页、下一个页是谁。

索引

前边我们详细唠叨了InnoDB数据页的7个组成部分，知道了各个数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
[外链图片转存中…(img-qWbZjcNQ-1566356120267)] 在这里插入图片描述
没有索引的查找

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤：

定位到记录所在的页。
从所在的页内中查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的，如果一个表有一亿条记录，使用这种方式去查找记录那要等到猴年马月才能等到查找结果。所以祖国和人民都在期盼一种能高效完成搜索的方法，索引同志就要亮相登台了。

InnoDB中的索引方案

设计InnoDB的大叔们需要一种可以灵活管理所有目录项的方式。他们灵光乍现，忽然发现这些目录项其实长得跟我们的用户记录差不多，只不过目录项中的两个列是主键和页号而已，所以他们复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录。那InnoDB怎么区分一条记录是普通的用户记录还是目录项记录呢？别忘了记录头信息里的record_type属性，它的各个取值代表的意思如下：

0：普通的用户记录
1：目录项记录
2：最小记录
3：最大记录

哈哈，原来这个值为1的record_type是这个意思呀，我们把前边使用到的目录项放到数据页中的样子就是这样：
在这里插入图片描述
从图中可以看出来，我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调一遍目录项记录和普通的用户记录的不同点：

目录项记录的record_type值是1，而普通用户记录的record_type值是0。
目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。
还记得我们之前在唠叨记录头信息的时候说过一个叫min_rec_mask的属性么，只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0。

如果数据不断增加，为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据，所以现在各个页的示意图就是这样子：
在这里插入图片描述
如图，我们生成了一个存储更高级目录项的页33，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在[1, 320)之间，则到页30中查找更详细的目录项记录，如果主键值不小于320的话，就到页32中查找更详细的目录项记录。不过这张图好漂亮喔，随着表中记录的增加，这个目录的层级会继续增加，如果简化一下，那么我们可以用下边这个图来描述它：
在这里插入图片描述
不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到B+树这个数据结构中了，所以我们也称这些数据页为节点。从图中可以看出来，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。

索引

InnoDB存储引擎的索引是一棵B+树，完整的用户记录都存储在B+树第0层的叶子节点，其他层次的节点都属于内节点，内节点里存储的是目录项记录。InnoDB的索引分为两大种：

聚簇索引

以主键值的大小为页和记录的排序规则，在叶子节点处存储的记录包含了表中所有的列。

二级索引

以自定义的列的大小为页和记录的排序规则，在叶子节点处存储的记录内容是列 + 主键。

总结

们前边详细、详细又详细的唠叨了InnoDB存储引擎的B+树索引，我们必须熟悉下边这些结论：

每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。
InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。
我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录按照先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。
通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。

索引使用

上边只是我们在创建和使用B+树索引的过程中需要注意的一些点，后边我们还会陆续介绍更多的优化方法和注意事项，敬请期待。本集内容总结如下：

B+树索引在空间和时间上都有代价，所以没事儿别瞎建索引。

B+树索引适用于下边这些情况：

全值匹配
匹配左边的列
匹配范围值
精确匹配某一列并范围匹配另外一列
用于排序
用于分组

在使用索引时需要注意下边这些事项：

只为用于搜索、排序或分组的列创建索引
为列的基数大的列创建索引
索引列的类型尽量小
可以只对字符串值的前缀建立索引
只有索引列在比较表达式中单独出现才可以适用索引
为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
定位并删除表中的重复和冗余索引
尽量适用覆盖索引进行查询，避免回表带来的性能损耗。

事务的基本要素（ACID）

原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

一致性（Consistency）： 事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

隔离性（Isolation）： 同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

持久性（Durability）： 事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的并发问题

脏读： 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读： 事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

幻读： 系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

数据库隔离级别—读未提交，读已提交，重复读，序列化

Read uncommitted 读未提交

公司发工资了，领导把5000元打到singo的账号上，但是该事务并未提交，而singo正好去查看账户，发现工资已经到账，是5000元整，非常高兴。可是不幸的是，领导发现发给singo的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，将事务提交，最后singo实际的工资只有2000元，singo空欢喜一场。

出现上述情况，即我们所说的脏读，两个并发的事务，“事务A：领导给singo发工资”、“事务B：singo查询工资账户”，事务B读取了事务A尚未提交的数据。

当隔离级别设置为Read uncommitted时，就可能出现脏读，如何避免脏读，请看下一个隔离级别。

例子：

(1) 打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），查询表account的初始值：
在这里插入图片描述
(2) 在客户端A的事务提交之前，打开另一个客户端B，更新表account：

(3) 这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：

(4) 一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据：
在这里插入图片描述
(5) 在客户端A执行更新语句update account set balance = balance - 50 where id =1，lilei的balance没有变成350，居然是400，是不是很奇怪，数据的一致性没问啊，如果你这么想就太天真了，在应用程序中，我们会用400-50=350，并不知道其他会话回滚了，要想解决这个问题可以采用读已提交的隔离级别
在这里插入图片描述

Read committed 读提交

singo拿着工资卡去消费，系统读取到卡里确实有2000元，而此时她的老婆也正好在网上转账，把singo工资卡的2000元转到另一账户，并在singo之前提交了事务，当singo扣款时，系统检查到singo的工资卡已经没有钱，扣款失败，singo十分纳闷，明明卡里有钱，为何…

出现上述情况，即我们所说的不可重复读，两个并发的事务，“事务A：singo消费”、“事务B：singo的老婆网上转账”，事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

当隔离级别设置为Read committed时，避免了脏读，但是可能会造成不可重复读。

大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。如何解决不可重复读这一问题，请看下一个隔离级别。

例子：

(1) 打开一个客户端A，并设置当前事务模式为read committed（未提交读），查询表account的初始值：
在这里插入图片描述
(2) 在客户端A的事务提交之前，打开另一个客户端B，更新表account：

(3) 这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题：

(4) 客户端B的事务提交

(5) 客户端A执行与上一步相同的查询，结果与上一步不一致，即产生了不可重复读的问题，在应用程序中，假设我们处于客户端A的会话，查询到lilei的balance为450，但是其他事务将lilei的balance值改为400，我们并不知道，如果用450这个值去做其他操作，是有问题的，不过这个概率真的很小哦，要想避免这个问题，可以采用可重复读的隔离级别
在这里插入图片描述
Repeatable read 重复读

当隔离级别设置为Repeatable read时，可以避免不可重复读。当singo拿着工资卡去消费时，一旦系统开始读取工资卡信息（即事务开始），singo的老婆就不可能对该记录进行修改，也就是singo的老婆不能在此时转账。

虽然Repeatable read避免了不可重复读，但还有可能出现幻读。

singo的老婆工作在银行部门，她时常通过银行内部系统查看singo的信用卡消费记录。有一天，她正在查询到singo当月信用卡的总消费金额（select sum(amount) from transaction where month = 本月）为80元，而singo此时正好在外面胡吃海塞后在收银台买单，消费1000元，即新增了一条1000元的消费记录（insert transaction … ），并提交了事务，随后singo的老婆将singo当月信用卡消费的明细打印到A4纸上，却发现消费总额为1080元，singo的老婆很诧异，以为出现了幻觉，幻读就这样产生了。

注：MySQL的默认隔离级别就是Repeatable read。

例子：

(1) 打开一个客户端A，并设置当前事务模式为repeatable read，查询表account的初始值：
在这里插入图片描述
(2) 在客户端A的事务提交之前，打开另一个客户端B，更新表account并提交，客户端B的事务居然可以修改客户端A事务查询到的行，也就是mysql的可重复读不会锁住事务查询到的行，这一点出乎我的意料，sql标准中事务隔离级别为可重复读时，读写操作要锁行的，mysql居然没有锁，我了个去。在应用程序中要注意给行加锁，不然你会以步骤（1）中lilei的balance为400作为中间值去做其他操作
在这里插入图片描述
(3) 在客户端A执行步骤（1）的查询：

(4) 执行步骤（1），lilei的balance仍然是400与步骤（1）查询结果一致，没有出现不可重复读的问题；接着执行update balance = balance - 50 where id = 1，balance没有变成400-50=350，lilei的balance值用的是步骤（2）中的350来算的，所以是300，数据的一致性倒是没有被破坏，这个有点神奇，也许是mysql的特色吧

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei |  400 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)
 
mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei |  300 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

(5) 在客户端A开启事务，查询表account的初始值

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 300 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

(6) 在客户端B开启事务，新增一条数据，其中balance字段值为600，并提交

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into account values(4,'lily',600);
Query OK, 1 row affected (0.00 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

(7) 在客户端A计算balance之和，值为300+16000+2400=18700，没有把客户端B的值算进去，客户端A提交后再计算balance之和，居然变成了19300，这是因为把客户端B的600算进去了，站在客户的角度，客户是看不到客户端B的，它会觉得是天下掉馅饼了，多了600块，这就是幻读，站在开发者的角度，数据的一致性并没有破坏。但是在应用程序中，我们得代码可能会把18700提交给用户了，如果你一定要避免这情况小概率状况的发生，那么就要采取下面要介绍的事务隔离级别“串行化”

mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 18700 |
+--------------+
1 row in set (0.00 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 19300 |
+--------------+
1 row in set (0.00 sec)

Serializable 序列化

erializable是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

例子：

(1) 打开一个客户端A，并设置当前事务模式为serializable，查询表account的初始值：

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 10000 |
| 2 | hanmei | 10000 |
| 3 | lucy | 10000 |
| 4 | lily | 10000 |
+------+--------+---------+
rows in set (0.00 sec)

(2) 打开一个客户端B，并设置当前事务模式为serializable，插入一条记录报错，表被锁了插入失败，mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，往往一个事务霸占了一张表，其他成千上万个事务只有干瞪眼，得等他用完提交才可以使用，开发中很少会用到。

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into account values(5,'tom',0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

MYSQL MVCC

MVCC是一种多版本并发控制机制。

MVCC是为了解决什么问题?

大多数的MYSQL事务型存储引擎,如,InnoDB，Falcon以及PBXT都不使用一种简单的行锁机制.事实上,他们都和MVCC–多版本并发控制来一起使用。

大家都应该知道,锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销。

实现原理

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，用于事务回滚，不在本文范畴）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

查询操作

从上面的描述可以看到，在查询时要符合以下两个条件的记录才能被事务查询出来：