敲黑板，重点来了——MySQL索引与事务_mysql索引及事务

1. 索引

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

索引有什么作用呢？我们应该都使用过字典，比如我们想要查“啊”这个字，现在我们通过索引表看到这个字在第一页，那么我们就可以直接翻到第一页，不用一个字一个字的去遍历了。很明显它可以帮助我们提高检索的效率，快速定位。在我们的mysql中也是一样的效果。

1.1 使用场景

既然如此，那我们在什么时候该来使用索引呢？
索引的使用可以带来查询性能的提升，但并不是所有情况下都适合使用索引。通常情况下，你应该考虑在以下情况下使用索引：

1.频繁的查询：当某个列经常用于查询条件或者连接条件时，可以考虑在该列上创建索引，以加速查询操作。

2.大型表：对于包含大量数据的表，使用索引可以大大提高数据检索的速度，尤其是在需要检索少量数据行时。

3.经常需要排序或分组：如果查询需要根据某个列进行排序或者分组操作，可以在该列上创建索引，以提高排序和分组的效率。

4.唯一性约束：对于需要保证数据唯一性的列，比如主键列或者唯一约束列，通常会自动创建索引。

5.频繁的连接操作：当多个表经常需要连接查询时，可以在连接列上创建索引，以提高连接操作的效率。
1
2
3
4
5
6
7
8
9

然而，也有一些情况下不适合使用索引，比如：

1.小型表：对于小型表，索引的性能提升可能不明显，甚至会降低插入、更新和删除操作的性能。

2.经常更新的列：如果某个列经常被更新，特别是更新操作频繁而且索引列是非唯一索引的情况下，索引可能会导致额外的性能开销。

3.索引列的选择性低：如果索引列的选择性很低，即它的值在整个表中几乎是唯一的，那么索引可能不会提供太大的性能提升。
1
2
3
4
5

需要注意的是，索引也会占去额外的磁盘空间。

1.2 使用

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

• 查看索引

show index from 表名;
1

show index from student;

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

create index 索引名 on 表名(字段名);
1

索引创建好之后，不需要手动使用，直接查询的时候就会自动的来走索引。执行引擎会自动评估哪种方案的成本是最低的，速度最快的。可以使用explain关键字，现实查询过程中使用索引的情况。

• 删除索引

drop index 索引名 on 表名;
1

现在我们创建一个表来演示一下：

-- 创建 classtest 表
CREATE TABLE classtest (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    `desc` VARCHAR(255)
);

insert into classtest(name, `desc`) values 
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
1
2
3
4
5
6
7
8
9
10
11

建好的表如下：
查看索引
show index from classtest;
创建name字段为索引：create index idx_name on classtest(name);
创建索引最好是在表创建之初就把索引给建好，否则，如果是在表中已有很多的数据的情况下来创建索引是一个比较危险的操作。
查看此时的索引：

删除索引，删除刚才创建的name索引：drop index idx_name on classtest;
删除也是同样的，如果创建索引后表中已经添加了大量的数据，再去删除索引也是比较危险的。

1.3 索引的数据结构

索引保存的数据结构主要为B+树以及hash的方式。

• B+树

1. 多路搜索树：B+树是一种多路搜索树，每个节点可以包含多个子节点。相比于B树，B+树的内部节点只存储键值信息，而不存储数据，数据只存储在叶子节点上。这样可以使得B+树的内部节点更加紧凑，减少内存占用。

2. 所有叶子节点均在同一层级：B+树的所有叶子节点都位于树的最底层，并且通过指针连接成一个有序链表。这种特性使得范围查询和顺序遍历变得更加高效，因为可以通过叶子节点的有序链表快速定位到范围内的数据。

3. 内部节点的分裂与合并：当内部节点的子节点数量超过阈值时，会触发节点的分裂操作；当节点的子节点数量低于阈值时，会触发节点的合并操作。这样可以保持B+树的平衡性，并且确保树的高度尽可能低，以提高查找效率。

4. 适用于外部存储：由于B+树的叶子节点之间通过指针连接成有序链表，并且所有数据都存储在叶子节点上，而非叶子节点就不必存储数据，只用存储索引即可，这使得B+树特别适合于外部存储（例如磁盘存储）。在数据库系统中，B+树常被用作索引结构，可以实现高效的范围查询、顺序遍历和快速查找

为什么使用的是B+树而不是B树呢？

1 .由于B+树在内部节点上不好含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子几点上关联的数据也具有更好的缓存命中率；
2 .B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

对于有主键的数据库表，就会采取上面间的形式来构建。比我我们创建一个学生表，主键是id，那么非叶子节点中存放的就是学生的id，到了叶子节点这一层才会存放完整的数据。

那么我们如果添加上学生姓名为索引，此时是怎么样的呢？
此时就会有两个这样的结构（所以说创建索引是需要花费额外的空间的），姓名索引的B+非叶子节点存储的就是一些姓名了，而叶子节点存放的并不是完整的数据了，而是主键id。如果是使用非主键（姓名）的索引来查询的话，则先需要查一遍非主键索引的B+树。这时候得到的数据并不是完整的数据了，而是主键的id，然后再根据主键的id查询主键索引的B+树，这也就是“回表”。也就是所使用非主键的索引来查询的时候就需要额外的开销咯。

注：当前的B+树结构，只针对MySQL的InnoDB这个数据库引擎里面典型使用的数据结构，不同的数据库不同的引擎都会有差异。

2. 事务

事务是指作为单个逻辑工作单元执行的一系列数据库操作。一提到事务，想必大家都能够享到课堂上老师敲黑板说的ACID。务具有以下四个特性，通常缩写为 ACID：

1. 原子性（Atomicity）：事务是一个原子操作单元，要么全部执行，要么全部不执行。如果事务中的任何一步操作失败，整个事务都会被回滚，回到操作之前的状态，保持数据库的一致性。
2. 一致性（Consistency）：事务执行结束后，数据库的状态应该从一个一致性状态转换到另一个一致性状态。即使在事务执行过程中出现故障或者中断，数据库也应该保持一致性。
3. 隔离性（Isolation）：事务之间应该是相互隔离的，一个事务的执行不应该受到其他事务的干扰。每个事务都应该以一种独立的方式操作数据，以避免并发操作导致的问题，如脏读、不可重复读和幻读等。
4. 持久性（Durability）：一旦事务提交，其所做的修改就应该永久保存在数据库中，并且不会丢失。即使系统发生故障或者重新启动，之前提交的事务所做的修改也应该得以保留。

使用事务的主要目的是确保数据库操作的一致性和可靠性。通过将一系列数据库操作组合成一个事务，可以保证这些操作要么全部执行成功，要么全部回滚，从而保持数据库的一致性。事务还能够提供隔离性，确保并发操作的正确性，以及持久性，确保数据的持久保存。因此，使用事务是保证数据库操作正确、可靠和安全的重要手段。

举个例子：
最典型的就是转账操作了，现在用户A想要给用户B转账100元：

现在如果在B账户加100r在实行的时候，由于网络原因导致最后的执行失败了，但是A账户的扣款操作已经执行完毕。这样就出现了大问题，应该让他们要么全都成功，要么全都失败，将多个操作打包成一个整体（原子性）。这也是事务最核心的特性。这样我们就必须使用事务来控制，保证操作要么全部成功要么全部失败。

这里说的全部成功很好理解，就是我们的每一步操作斗成功执行并且更新到数据库中。奇怪的是全部失败是什么意思呢？一般不应该是有的成功有的失败吗？没错这里的全部失败，指的是将全部的操作自动恢复成执行前的样子（回滚，就像我们使用ctrl+z一样），看起来就像是全都没有执行。继续看下面的2.1即可理解。

2.1 数据库中事务的使用

（1）开启事务：start transaction;
（2）执行多条SQL语句；
（3）回滚或者提交：rollback/commit
rollback是全部失败，commit是全部成功。

start transaction;
 -- 用户A账户减少2000
update accout set money=money-2000 where name = '用户A';
 -- 用户B增加2000
update accout set money=money+2000 where name = '用户B';
commit;
1
2
3
4
5
6

使用rollback后回滚刚才的更新操作：