MySQL秘密武器：索引与事务

ok，今天来分享下MySQL中的索引与事务。

当我们进行查询的时候，数据咻的一下就查询出来，速度很快噢。

那么这里呢，就有了索引的功劳了。

所以，先来介绍下索引是什么？

索引

索引是数据库中的一种数据结构。但我们可以理解为像是一本书中的目录，而我们更早接触到类似的，比如我们学习的数组的下标。

由前面的引入，显然，索引的引入是为了加快查询速度的。

一枚硬币既有反面也有正面。显然这个索引也是带来一些缺点（代价）

1.引入索引会消耗一些存储空间

2.索引的引入，确实能提高查询速度，但不总是能提高增删改的速度。

体现在：加快、拖慢、没什么变化

加快：比如删除操作：delete from student where id=5；这里就涉及了查询。

拖慢：比如进行增删改的时候，需要同步更新维护索引。

所以，这么看来索引可谓是有利也有弊。所以大家要辩证的去使用它。

那么讲到这，索引如何去使用呢？

索引使用

1.查看索引

语法：show index from 表名

这里举个例子

这里有两张表：cla、student。

所以我查询两张表的索引：

这里cla查询出来是空的，student是存在一个索引的

为什么呢？

回到展示表结构那个图片。

可以发现，cla中那key列中，没有任何信息。

所以查询出来的时候就没有索引信息。

所以就此得出了，primary key是会自带索引的。

同时也值得注意的是，这个primary key索引是不用我们手动添加的，指定主键后就会自动添加。

那么就只有这一个？

当然不是

还有unique 和 foreign key

unique

foreign key

还有一些注意的点，你对那个列进行索引创建，那么查询的时候以这个列为条件，查询就快。

同时一张表中可以存在多个索引，但是主键不能存在多个噢。

2.创建索引。

语法：create index 索引名 on 表名（列名）

还是举个例子

创建一个班级表，为id列创建索引，索引名为index_id

这个操作具有一定危险性，

当前表的数据量好说，一旦涉及到千万级别的表数据，那么这个操作就会触发大量的硬盘IO，可能导致机器垮掉。

因为，索引创建中，有一步就是要进行全表扫描，搜集所有列的值并构建索引机构，这样是产生磁盘的大量读写操作。

所以，创建索引的过程，一般也是事先商量好的，或者规划好的。

3.删除索引

语法：drop index 索引名 on 表名

举个例子

刚刚为class创建了个index_id索引，现在删除掉。

但值得注意的是，像是primary key 和 foreign key这些创建之后，数据库自动生成的不能删除。

那么讲到这里，索引一些操作讲完了。

接下来就要讲讲另外一个了，

前面说到索引是一种数据库中的数据结构。

那么这种数据结构是什么呢？

索引背后的数据结构

是我们接触数据结构时的那些？

比如顺序表、链表、栈、队列？

还是二叉树搜索树、哈希表？

顺序表、链表、栈、队列这些是可以pass掉了，毕竟有些能做其一，不能做其二，要么就是插入耗资源，要么就是查找耗资源。

那么二叉搜索树和哈希表呢？

其实也不然，二叉搜索树，重点在二叉上，当我们插入很多数据时，树的高度很高了，再去插入，对硬盘也是一个不小的挑战。

哈希表呢？

查询速度是很快，但不能进行模糊查询，进行的是精确性的查找。

那么到底是什么适合呢？

答案揭晓：

B+树

当然介绍这个之前，先来浅浅介绍下B树

B树是一棵n叉搜索树，每个节点都能分出多个子树。

这么一说，发现，这样就是可以降低树的高度咯。

那么举一个实例图咯

由图可以得知，一层中保存了n个key节点，可以分出n+1个子树来。

虽然，这里插入还是需要多次比较，才能走下一个。

但是比二叉搜索树优势很大，起码高度降低不少，且一层一层节点中的数据全部一次硬盘IO就行，不在需要多次IO了。

那么B+树呢？

B+树是B树的进一步优化的数据结构来的，同时它也是一个n叉搜索树。

还是给个图片看看

这是一个B+树的简单示意图。

在第一层节点中16，就是整个树的最大值。

对比B树，发现是少一个区间的。

即N个key，就是有N个区间。

同时还发现了，父节点的中的最大值会下放到子区间中，作为最大值，比如第一层中的8，作为父节点，下放到左边的区间是作为最大值的。

最后，在叶子节点中，所有的值会呈现数据的全集，就像是1-16全部的集合

通过链表的形式存储下来。

同时值得注意的是，这些非叶子节点是不存储数据行，叶子节点才存储，所以也得出了，我们查询数据都是落到叶子节点进行的。

所以当我们进行范围查询的时候，这个就特别有用。

ok，可以来简单总结下B+树的优势有什么

优势：

1.N叉搜索树，高度较低，磁盘IO较少。

2.叶子节点是表示数据范围的全集，并用链表进行连接，非常便于进行查询

3.B+树所有的查询，最终落到叶子节点下，任何的一次查询，我们的磁盘IO次数和比较次数是不会相差太大，，所以查询开销较为稳定。

4.同时B+树中，由于非叶子节点不存储“数据行”，存储索引列的key值，叶子节点才存储“数据行”，所以占用的空间少。

ok，到这里，分享完第一个秘密武器了。

接下来到事务了。

事务

咋一听这词有点陌生呢。

这是我们不常接触的话，就有点陌生咯。

事务是什么呢？

事务是一组作为单个逻辑单元的执行的SQL语句。

简单来说，就是解决一些特定场景的问题的。

举个例子。

我们平时用的较多的转账。

比如有两个这样的账户：

1：1000

2：500

那么现在1对2进行转账500元

一般的操作流程就是先对1账户进行减去500元

再对2账户进行增加500元；

不出意外的，一切都顺利成章，那么账户1就是有500，账户2就有1000.

但意外总是无处不在的，万一执行第一个操作之后，突然出现程序崩溃、主机崩溃啥的，没能执行第二个语句，那么数据库内容就会出错。那客户不得更银行急眼。

所以为了解决这个问题，引入了事务。

把这些语句打包成一个整体，这个整体执行中，要么全部执行完，要不都不执行。

但值得注意的是，都不执行这里不是说一个sql语句都不执行。

而是执行到一半的时候，发现错误的时候，数据库会自动进行“还原操作”，把之前执行语句给“撤销”，达到什么都没有做的效果。

这个“还原操作”在数据库中就成为回滚（rollback）。

那么数据库是如何知道，怎么回滚？怎么把之前的操作复原的呢？

这里呢归功于数据的库中强大的日志体系

这一系列的日志体系，会把我们开启事务的时候，执行的每一个sql语句，以及对数据进行哪些修改，都会记录下来。

这样当我们主机断电，程序错误且需要回滚的时候，就可以根据记录的信息，进行还原操作了。

但是我们要注意的的是，回滚只在事务中进行，还有，事务中的sql语句也是不能够太多的。

诶，上诉这里就涉及到了事务的一个特性——原子性。

那么讲到了事务的特性，那么事务是特性有什么呢？

特性

1.原子性

刚刚上面讲诉过了

2.一致性

事务执行前执行后，数据库中的数据都是“合法的状态”，不会出现非法临时结果性的状态

3.持久性 事务执行完毕后，就会修改硬盘上的数据，而且这些修改是持久生效的。

4.隔离性

这个呢，解释起来，就是说，描述多个事务并发执行的时候，相互之间产生的影响是怎样的。

诶，并发执行又是怎么样呢？

比如说，MySQL中提供的多个客户端，且每个客户端提交了多个事务给到服务端，与此同时，服务端收到多个事务需要同时执行的时候，这个时候就是并发执行。

都在执行的话，或多或少会产生一些问题的。

比如，当同时执行这些事务的时候，恰好针对一个表，且进行了一些增删改查。

那么我们可能会引入这样的问题：
1.脏读

2.不可重复度

3.幻读

那我们来一一解释下

脏读：

比如现在有两个事务A和B并发执行

其中事务A对某个表进行数据修改。与此同时，B对这个表进行读取，B读完后，A呢就把数据进行修改完成了

这样导致了B读的数据，不是A最终改为的数据，而是临时性的“脏数据”，也可以称之为“过期了”。

通俗点，当我进行对我的白月光写一篇“小作文”的时候，我的朋友偷看到了，然后并没有等我写完，就去告诉了我的白月光，可是我写到一半，发现不对，重写了再次发给白月光，那么白月光收到的信息和我朋友传达的信息就不一致了。

解决办法也挺简单的，就是约束一下，我写的时候，我的朋友不能读，等我写完提交给我的白月光了，你再来读。

不可重复读：

这里呢涉及到了三个事务了，

比如A事务执行修改操作，然后把数据提交了，然后B事务进行读取A提交的数据的时候，突然来个C事务进行把刚刚A提交的事务进行修改了，然后当B再次读取时候，就会发现，第一次读取和第二次读取，出现数据不一致。当然，这里更多指的是针对一个事务来讲，比如B事务

通俗点解释

还是那我给白月光写“小作文”的例子，这次呢，我写完了，我本人没做修改，同时我的朋友也是完完全全读取到了，然后我的朋友屁颠屁颠的去我的白月光那边再想看一次，这时候，我发现写的不好，我教我的“文豪"朋友来帮我把之前的”小作文“修改一下，然后再次发给我的”白月光“。此时当我那个朋友去到白月光那边进行读的时候，发现第一次读和第二次读，发现不一致了。

如何解决呢？

还是约定一下，当我那个朋友在我白月光那边进行读取的时候，就不能修改正在读的文章了

幻读：

比如，有一个事务A在读取数据的时候，读的过程中，另外一个事务B进行增删一些数据，此时，在A的角度上看，当进行结果集查询的时候，第二次查询出现了第一次不存在的记录。

那么如何去解决呢？

这里我们直接一些，当事务A读的时候，B事务不能进行任何操作，有点线性化那样。这样的操作称之为串行化，就是一个个排队执行完事务。

那以上三个问题和隔离性有什么关系呢？

诶，这里的MySQL隔离提供了四个级别，通过一些配置文件来设置当前服务器的隔离级别是哪个级别，从而影响到三个问题出现的情况。

那么有哪四个呢？

1.read uncommitted   读未提交

这样的情况下，一个事务是可以读取另一个事务未提交的数据，那么及其容易出现脏读、不可重复读、幻读的问题。

但此时事务并发执行的速率是最高的，执行速度最快！

2.read committed 读已提交。

这样的情况下，一个事务只能读取已经提交的数据，可能会产生不可重复读或者幻读问题。但脏读解决了。

此时，并发程度降低，速度也降低，但隔离性提高了，也就是说，事务间的相互影响较小，数据更准确些了。

3.repeatable read 可重复读。

这样的情况下，就是进行了一些写和读操作的加锁，当这个事务正在执行的时候，不能对此内容进行读和写，解决了不可重复读和脏读问题，可能会出现幻读问题。同时速度降低，并发程度进一步降低，但事务间的隔离性进一步提高！

4.串行化

这个时候，事务是在服务器上一个个的执行，就像排队一样，这时候脏读、幻读、不可重复读也解决了。此时，速度最低，并发程度也是最低的,但是隔离性是最好的，数据最准确！

当然，这里提到的一些速度和准确性是相对业务场景而言，什么时候需要快，什么时候需要准确，

都是对业务需求分析后而定夺的。

那么，这个事务和索引，就浅浅的分享到这啦~

原文地址：https://blog.csdn.net/m0_75169015/article/details/143476687

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！