【MySQL】索引和事务

🌴索引

🚩概念

🚩索引的作用

🚩索引的使用场景

🚩索引的使用

🏀查看索引

🏀创建索引

🏀删除索引

🎄索引的底层数据结构

🚩引入B树(B-树)

🚩B+树

🚩详细总结

🌳事务

🚩概念

🚩事务的特性(面试题)

🏀mysql事务的隔离性具体体现

🚩事务的使用

🌴索引

🚩概念

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

🚩索引的作用

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

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

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

引入索引，能够提高查询速度，但是也会引入两个问题：

1. 索引本身要占据存储空间

2. 索引能够提高查询速度，可能会拖慢增删改的速度(后续对数据进行增删改，都要同步更新索引)

但是在实际开发中，大多数都是读多写少，索引仍然是有很多的用武之地

🚩索引的使用场景

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

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

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

索引需要占用额外的存储空间。
对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能。
过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引。

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

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

🚩索引的使用

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

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

🏀查看索引

语法：

show index from 表名;

🏀创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引，例如经常根据学生名字进行查询，就可以针对名字这一列创建索引（创建索引都是根据具体的列来创建的）。

语法：

create index 索引名 on 表名(列名);

注意：索引的创建也是一个危险操作！！！

如果针对空的表，或者表里的数据不多，此时创建索引都无所谓；如果表本身就很大(数据量很大)，此时创建索引，就会引起大量的CPU/硬盘IO的消耗，也是有可能会把数据库搞挂了(数据库无法响应其他操作)

只能在建表初期就把索引加上，未来数据多了就不怕了(这就看你的设计功力了)。

假如有以下场景，当前在上产环境上有一个表，没有加索引，又要频繁的查询，如何优化？？？

解决办法：

🏀删除索引

drop index 索引名 on 表名;

这也是危险操作！

🎄索引的底层数据结构

经典面试题：谈谈索引内部的结构是怎样的

索引，一定是引入了一些额外的数据结构，来增加查询的速度；默认的情况下，进行条件查询的时候，都是遍历表，一条一条数据带入条件，这种操作一旦数据量过大，效率就很低。引入索引，就是要通过其他的数据结构，加快查询的速度，减少遍历表的可能性。

前面所聊过的数据结构中，哪些数据结构能够加快查询速度？

树 => 二叉树 => 二叉搜索树(查询元素，如果普通的二叉搜索树，最坏情况成为链表，时间复杂度为O(N)；如果是一个比较平衡的树，O(logN))。

就引入了AVL树，是一个平衡的二叉搜索树，此时查询就能够做到O(logN)。

那么为啥TreeMap，TreeSet不使用AVL树，而使用红黑树？

红黑树本质上是一个没那么严格的平衡二叉搜索树，AVL树则是一个非常严格的平衡二叉搜索树(要求任意节点，左右子树高度差不能超过1)，当你要求非常严格的时候，随便进行一些增删改操作，都可能破坏要求从而触发旋转(每次旋转都有开销)，红黑树触发旋转的概率要远远低于AVL树，虽然没有AVL树那么平衡，但是查询时候的速度也没差多少。

还有一个哈希表，也能够提高查询的效率，哈希表中，查询元素的时间复杂度为多少？

平时所说的时间复杂度都是只最坏情况，但是谈到哈希表呢，往往不是谈最坏，就认为是O(1)了；但是呢当发生哈希冲突时(两个key被印证到同一下标上)，就可以通过链表/红黑树的方式来解决哈希冲突，一旦出现链表/红黑树很明显就不是严格的O(1)了，即使不是O(1)，也绝对不是O(N)。我们所认为的N，指的是问题的规模，在哈希表中认为所有的数据的数量加到一起是N，链表的长度当然不是N(这里不是一根链表，而是很多很多根，虽然理论上存在所有数据在同一个链表上的极端情况，但是这个情况认为在工程上不会出现，除非你故意构造出一个特殊的哈希函数)。最多可以设链表最大长度为M，复杂度为O(M)。

另外在使用哈希表的时候，可以在合适的时机，对哈希表进行扩容，并且选择合适的哈希函数，就可以确保每个链表上的元素都不是很多，近似认为是O(1)了。例如hash上的每个链表长度是100，在查询元素的时候，虽然要遍历，实际上这个操作和O(1)差不了多少。归根结底，哈希表的查询操作视为O(1)。

上述呢，我们说到，红黑树和哈希表都可以用来查询的数据结构，但是这两个数据结构都不适合给数据库使用。

哈希表的原因：

因为哈希表只能查询key相等的情况，不能使用< >这样的范围查询；

红黑树的原因：

红黑树里面的元素是有序的，就可以进行范围查询，但是红黑树中要想找到中序遍历的下一个后继元素，这样的操作也未必就高效，就可能需要往父节点上一系列的回溯，才能找到后继(例如左树的最后一个节点的下一个节点为root节点，就需要回溯)。红黑树是一个二叉搜索树，当元素非常多的时候，就会使树的高度非常高，树的高度越高，查询效率就越低，高度每增加一行，比较的次数就增加1，数据库的数据/索引是在硬盘上的，上述每次比较，都需要进行一次硬盘IO操作，开销很大！因此红黑树不太适合于大规模在硬盘上管理数据的场景。

🚩引入B树(B-树)

因此就引入了B树，本质上是一个N叉搜索树。红黑树之所以高度比较高，就是因为是二叉搜索树，也就是每个节点上只能有一个key，一个key下面分出两个叉来，这样以来表示的数据就比较少。

N叉搜索树，就可以在每个节点上存储多个元素，进一步的延申出多个子树，表示同样的数据，需要的节点树就少了，对应的高度就大大降低了。

B树结构：

其实数据库索引的数据结构的最终形态，不是B树，而是B+树，相当于B树的升级版！

🚩B+树

B+树同样也是N叉搜索树。

结构：在B树种，两个key，可划分出三个区间；B+树中两个key，划分出两个区间。左子树的区间为<=8；右子树的区间为（8, 15 ]

按照上述规则来排列数据，此时叶子节点这一层就包含了整个数据集合的全集；另外还会以类似链表这样的链式结构将叶子节点串起来。这就构造出了一个典型的B+树。

B+树相比于B树的优势：

1.非常方便的进行范围查询

此时就可以通过上述链式结构非常方便的遍历整个表中的所有数据，同时也非常方便的进行范围查询。比如查询 id >= 5 and id <= 9，就可以先按照5这个值在B+树中进行查询，查询到之后，直接沿着5出发，遍历这个链式结构，一直到9为止，这一段即为id >= 5 and id <= 9的范围。

2.当前每一次查询，都是要落到叶子节点完成的，查询操作稳定

即查询任何数据，经历的硬盘IO操作次数是一样的，这个时候，查询操作消耗的时间是稳定的。在上述B树中，有的元素，直接在非叶子节点就查找到了，这样效率不是更高吗？？？IO次数更少嘛？？注意，稳定是一个非常重要且难得的优点，B树这样的结构就会导致有些key查询的速度很快，有些key很慢(IO次数有很大影响)，这就是不稳点。

3.数据存储在叶子节点，非叶子节点可在内存中缓存

由于叶子节点是数据的全集，对应的非叶子节点中都是重复出现的数据，此时就可以把表的每一行数据，最终都关联到叶子节点这一层，非叶子节点中只保存一个单纯的key值即可(id)。这样组织带来的好处，非叶子节点占用的空间就比较小(非叶子节点只存id)，此时非叶子节点就可以缓存到内存中！！！(当然这份数据必然要在硬盘上保存一份，为了提高查询速度，就可以把这部分结构放到内存中)，这样查询的速度大大提高了(非叶子节点中的比较不在受到硬盘IO的制约了)，B树若也想做到这样的结构来保存数据，意味着非叶子节点也要包含数据行，此时叶子节点占据空间很大，就无法在内存中缓存了。

小结：针对哪个列创建索引，就是针对哪个列构建B+树；针对主键创建索引构建的B+树，叶子节点是带有数据行的，在针对其他列(非主键)创建索引，B+树其叶子节点则是主键(id)；针对非主键列进行索引查询，查到的结果是一个主键(id)，还需要去主键索引中再做一次查询(称为"回表")。

🚩详细总结

B+树存在的前提，是使用了 innodb 这个存储引擎，mysql支持多种存储引擎，不同的存储引擎使用的索引的数据结构是不同的，innodb是最常用的，也是面试考的。

B+树的结构是一直存在的，如果你定义了主键，自然就是按照主键来建立B+树；如果你没有定义主键，会有一个自带的隐藏的列，来建立B+树。

给定一个表：student(id int primary key, name varchar(20), classId int);

那么MySQL就会根据主键(id)来构建B+树。

针对name这一列，手动创建索引：create index idx_student_name on student(name);

这个操作就会构造出另外一个B+树，是和上述主键创建的B+树是独立存在的

🌳事务

例如有以下场景：

有一个库存表和订单表，用户进行下单操作时，会触发库存表的删除操作 && 订单表的新增操作。

所以经常会涉及到通过多个SQL配合，完成某个操作，这个时候就需要使用到事务，来确保上述的操作，是可靠的，是完整的。

🚩概念

MySQL 事务主要用于处理操作量大，复杂度高的数据。也就是说事务是把多个操作，打包成一个整体("原子性")，能够保证这个整体要么都执行成功，要么一个都不执行。就可以有效避免，一部分执行一部分不执行，所引起的"中间状态"产生的问题。

上述的一个都不执行，并不是真的没有执行。事务中的若干个sql必然是一条一条执行的，但是事务能够保证，当执行到某一条的时候如果出现问题了，数据库就能够自动的把前面sql造成的影响，给恢复回去，看起来就像一条sql都没有执行一样。这个操作也称为"回滚"。

也就是说数据库事务的原子性，核心就是通过"回滚"机制来保证的。

上述关于事务的内容就涉及到一个面试题：谈谈事务是啥~~

🚩事务的特性(面试题)

原子性

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

一致性

执行事务之前，和执行事务完毕之后数据是一致的。也就是说若触发了回滚，那么回滚回去的数据得是对的；如果事务顺利执行完成，没有触发，数据也是要符合要求的。一致性也是对"数据正确"的承诺。

持久性

此处的持久性，说程序重启/主机重启，数据仍然还存在(数据存在硬盘上)。(不仅仅是针对数据库)

执行事务对数据库产生的修改，就会在硬盘上持久保存，重启之后仍然存在。

隔离性

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

数据库并发(多个客户端同时给服务器发起事务)执行事务时，产生的情况。

每个客户端啥时候把事务提交过来，并不知道，有可能多个客户端正好把事务赶到一块了，就需要数据库服务器都能给出处理。更糟糕的是，如果多个事务都尝试去操作同一个表，情况就会更复杂

如果服务器要是一起同时处理，又可能会出现问题~~

1. 脏读问题

数据库中，如果有事务A和事务B，事务A针对某个表做出了一些修改，在事务A提交(事务执行完毕；commit)之前，事务B就对这里的数据进行了读取。最终就可能会出现A后续读取的数据和B读取的数据是不同的。

解决办法：针对"写操作"进行加锁，也就是A在写的时候，B不能读，等到A写完了，B才能读。

本来事务A在在进行修改时，事务B也能读(同时执行)，现在进行加锁之后，执行A的时候，B要进行等待，这就降低了"并发能力"，也就会降低数据库服务器的处理效率，提高了"隔离性"，也提高了数据的准确行。

2. 不可重复读问题

存在三个事务ABC，现在事务A针对数据进行修改提交，事务B进行读取数据(事务B可能要读取多个sql)，事务C又针对数据进行修改(因为上述只针对"写操作"加锁，没有针对"读操作加锁")，就会使事务B里面的不同读操作，读出来的结果不一样。