【MySQL】 MySQL索引事务

本节目标

• 索引
• 事务

🛫索引

🎍索引的概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现

🌳索引的作用

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高MySQL 的检索速度。

• MySQL 索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。
• 打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。
• 拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

🎄索引的使用场景

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引虽然能够提高查询性能，但也需要注意以下几点：

• 索引需要占用额外的存储空间。

• 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能。

• 过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。

反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

🍀索引的使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约（FOREIGN KEY）时，会自动创建对应列的索引

接下里我们实现一些索引的基本操作和使用

📌查看索引

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。

可以通过添加 \G 来格式化输出信息。

SHOW INDEX 语句：

SHOW INDEX FROM table_name\G

• SHOW INDEX: 用于显示索引信息的关键字。

• FROM table_name: 指定要查看索引信息的表的名称。

• \G: 格式化输出信息。

执行上述命令后，将会显示指定表中所有索引的详细信息

包括索引名称（Key_name）、索引列(Column_name）、是否是唯一索引（Non_unique）、排序方式（Collation）、索引的基数（Cardinality）等。

这里博主查询一个博主已经建立好的一个student表的索引

案例查询：查看学生表已有的索引，查询结果如下

📌创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

使用 CREATE INDEX 语句可以创建普通索引。

普通索引是最常见的索引类型，用于加速对表中数据的查询。

CREATE INDEX 的语法：

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• CREATE INDEX: 用于创建普通索引的关键字。

• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。

• table_name: 指定要在哪个表上创建索引。

• (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。

• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

以下实例假设我们有一个名为 students 的表，包含 id、name 和 age 列，我们将在 name 列上创建一个普通索引。

CREATE INDEX idx_name ON students (name);

建立索引如下：

🌲删除索引

drop index 索引名 on 表名;

以下实例是我们对上述建立索引的一个删除，删除student表中name字段的索引

drop index inx_name on student;

删除后，查询结果如下：

🌴索引保存的数据结构

索引保存的数据结构主要为B+树

再介绍B+树之前我们先来看一下，B树，因为B+树是在B树的基础上进行优化的

🎈B树

B树与二叉树（Binary Tree）不是一个概念，你可以将其翻译成Balance Tree，或者是Bayer Tree。

B树是一种自平衡的树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作，都在对数时间内完成。

B树与AVL树不同，可以拥有2个以上的子节点，并且每个节点可以有多个键值，这些属性减少了定位记录时所经历的中间过程，加快了存取速度。B树更适用于读写相对较大的数据块存储系统，如磁盘。这种数据结构常被应用在数据库和文件系统的实现上。

对于一个M阶B树具有以下特性：

• 每个节点最多有 M 个子节点；每个内部节点最少有 ⌈M/2⌉ 个子节点（⌈x⌉为向上取整符号；如果根节点不是叶子节点，那么它至少有两个子节点。

• 具有 N 个子节点的非叶子节点拥有 N-1 个键。

• 所有叶子节点必须处于同一层上。

🎈B+树

是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

那我们具体怎么存储数据库中的这些数据呢？

比如我们有以下这样一张student表，主键为id

MyISAM（MySQL的数据库引擎）中是这样存储的

在InnoDB中的实现

🎈问题解决

问：为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？

hash：虽然可以快速定位，但是没有顺序，IO复杂度高。
二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
红黑树：树的高度随着数据量增加而增加，IO代价高。

问：为什么官方建议使用自增长主键作为索引。

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

问：B树与B+树的异同点

相同点

• 一个节点可以存储多个元素
• 叶子节点是排序的
• 每个节点中的元素， 也都按照从小到大的顺序排列， 即： 左小右大。
• 所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。
• 根节点元素个数： 1<= k <= m-1 (m表示阶数， 即： 一个节点最多有多少子节点)非根节点元素个数： m/2 <= k <= m-

不同点

• B+树叶子节点是有指针的， MySQLInnoDB中采用的是双向指针，上层非叶子节点也有双向指针
• B+树非叶子节点的元素是与叶子节点有冗余重复的情况

🛬事务

⚾事务的概念

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

• 事务用来管理 insert,update,delete 语句

⚽事务需要满足的四大条件

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

🧭事务控制语句与简单使用

控制语句（部分）

• BEGIN 或 START TRANSACTION 显式地开启一个事务；

• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

使用如下：

1. 开启事务：start transaction;
2. 执行多条SQL语句
3. 回滚或提交：rollback/commit;

• rollback即是全部失败，commit即是全部成功

🥎事务的并发控制

数据库是一个共享资源，可以供多个用户使用。允许多个用户同时使用一个数据库的数据库系统称为多用户数据库系统。例如飞机订票数据库系统、银行数据库系统等都是多用户数据库系统。在这样的系统中，在同一时刻并发执行的事务数可达数百上千个。

而在面对这么多并发执行的事务存在以下几个问题

• 丢失修改

两个事务T1和T2读入同一数据并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失

• 不可重复读

不可重复读是指事务T1读取数据后，事务T2执行更新操作后，使T1无法再现前一次读取结果

• 读“脏”数据

读“脏”数据是指事务T1修改某以数据并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被撤销，这时被T1修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致，则T2读到的数据就为“脏”数据，即不正确的数据。

🏀封锁

封锁是实现并发控制的一个重要技术。所谓的封锁就是事务T在对某个数据对象，例如表、记录等操作之前，先前系统发出请求，对其加锁。

加锁后事务T就对该数据对象有了一定的控制，在事务T释放它之前，其他事务不能更新此数据对象。

确切的控制由封锁的类型决定。基本的封锁的类型由两种：排他锁（exclusive locks，简称X锁）和共享锁（share locks，简称S锁）

• 排他锁又称为写锁。若事务T对数据对象A加上X锁，则只允许工读取和修改Ar其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁为止。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A。

• 共享锁称为读锁。若事务 T对数据对象A加上S锁，则事务工可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁为止。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

🎡封锁协议

在运用X锁和S锁这种基本封锁对数据对象加锁时，还需要约定一些规则。例如何时申请X锁或S锁、封锁时间、何时释放等，这些规则称为封锁协议。对封锁方式制定不通的规则，就形成了各种不同的封锁协议。

• 一级封锁协议

一级封锁协议是指，事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。事务结束包括正常结束（COMMIT）和正常结束（ROLLBACK）。

一级封锁协议可防止丢失修改，并保证事务T是可恢复的，可解决丢失修改问题

• 二级封锁协议

二级封锁协议是指，在一级封锁协议基础上增加事务在读取数据R之前必须先对其加S锁，读完后即可释放S锁。

二级封锁协议除防止了丢失修改，还可进一步防止读“脏”数据

• 三级封锁协议

三级封锁协议是指，在一级封锁协议的基础上增加事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。三级封锁协议除了防止丢失修改和读“脏”数据外、还进一步防止了不可重复读

⭕总结

关于《【MySQL】 MySQL索引事务》就讲解到这儿，感谢大家的支持，欢迎各位留言交流以及批评指正，如果文章对您有帮助或者觉得作者写的还不错可以点一下关注，点赞，收藏支持一下！