0


【MySQL】MySQL索引与事务的透析——(超详解)

前言

🌟🌟本期讲解关于MySQL索引事务,希望能帮到屏幕前的你。

🌈上期博客在这里:【MySQL】MySQL表的增删改查(进阶篇)——之查询操作(超级详解)-CSDN博客

🌈感兴趣的小伙伴看一看小编主页:GGBondlctrl-CSDN博客


📚️1.引言

   Hello!!!家人们,小编上期期讲解了关于增删查改的进阶之查询进阶操作,关于数据库表的增删查改有了就更深的认识,小编这期将深入讲解另一部分内容,即关于数据库的索引事务大家准备好了吗~~~🥳🥳🥳;

且听小编进行讲解,包你学会!!!

📚️2.索引

2.1索引存在意义

在数据库使用select查询数据的时候有以下几个步骤:

1.先遍历表;

2.在把当前的行带入条件看条件是否成立;

3.若成立则保留,反之就不保留,跳过;

那么在一个表的数据非常大的时候,遍历的成本就非常的高了,时间复杂度至少为O(n)

总结:****所以索引是针对查询操作引入的操作,避免针对表的遍历,运用索引可以加快查询的操作;

2.2索引相关操作

1.查看索引

SQL执行语句:show index from 表名;

代码实例如下:

mysql> create table student(id int,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> show index from student;
Empty set (0.00 sec)

可以发现在没有建立索引的情况下,表是没有索引的,那么这就涉及另一个问题了;

存在unique,primary key,foreign key 的时候,索引会自动生成;

代码实例如下:

mysql> create table student(id int primary key,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

此时可以发现,存在索引了,列名字为id;

注意:****如上述,索引会针对指定的列,在查询的过程中只有针对这一列进行查询,查询才会被索引进行优化;

当然还存在外键的时候,代码如下:

mysql> create table student(id int primary key,name varchar(10),classid int,foreign key(classid) references class(classid));
Query OK, 0 rows affected (0.01 sec)

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | classid  |            1 | classid     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

这里的外键的建立,小编在上期讲解过,不明白的小伙伴就去看看吧~~~

注意:此时索引指定的列名就为两个,上面那个是主键建立的索引,下面是外键建立的索引;

2.创建索引

SQL执行语句:create index 索引名 on 表名(列名);

代码实例如下:

mysql> create index id_index on stu(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from stu;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| stu   |          1 | id_index |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

注意:创建索引是一个危险的操作,在创建索引时,数据库会根据现有的数据,进行大规模的整理,如果数据过多,会导致服务器卡死;

3.删除索引

SQL执行语句:drop index 索引名 on 表名;

代码实例如下:

mysql> drop index id_index on stu;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from stu;
Empty set (0.00 sec)

通过上述的stu表建立的索引进行删除后,可以发现在再次查找索引的时候就为空了;

注意:****这里删除的是自己手动创建的索引,如果为(主键,外键,unique创建的索引)那么此时就不能够进行删除;

2.3索引原理理解

索引的内部原理其实是一种数据结构:即B+数(N叉搜索树)

那么我们之前学过的关于查询比较高效的就有哈希表,二叉搜索树;

📍哈希表是内部一种顺序表和链表结合的结构,在查找精确的数值时,他是可以实现的,但是在数据库的模糊匹配,范围数据查询,那么哈希表就不适用了;

📍二叉搜索树来说,当数据量过大时,那么此时树的高度就为log(N),那么此时还是完全平衡的状态,如果为单分支,那么树的高度就很离谱了;

那么此时就有一个B+树来解决这个问题;那么接下来,就先讲解B树的结构;

1.B树

B树就是和二叉搜索树基本原理一致,但是一个节点上不止一个数字,当多个数字在时,就会分为几个范围,小编就通过画图解释一下吧;

如图所示:

如上图所示,此时树的高度就大大减少了,那么就缩短了时间复杂度;

注意:

一个节点存储N个key那么就会生成N+1个区域,每个区域会生成对应的子树;

结点是存储在硬盘区域的,一次读硬盘,就取出来几个key,再进行比较;

一个节点中是可以存储N个key的,但是这不是无限制的,当一个节点的key过多时,就要触发节点的分裂;当然当节点的太少时,就会触发节点的合并;

2.B+树

对于B+树是B树的优化版本,那么还是和上述一样,小编来画图来解释吧;

如图所示:B+树就是省去了大于最大值的区间范围,并且每个区间的子树最大值必须为区间的最大值,即父节点对应区间最大值;

那么就有以下的特点:

📍 一个节点上存储一个N个key,对应就有N个区间,子树;

📍每个节点的最后一个节点,就相当于当前节点的最大值;

📍父节点的每个key都将以最大值的身份在子结点的对应区间出现;

📍B+树会使用链表这样的结构将叶子结点串联起来;

所以注意:

B+树减少了树的高度,降低了时间复杂度;

所有查询都要在叶子结点进行,使查询之间的时间开销是稳定的;

由于叶子结点存储的是行数据,而非叶子结点存储的是排序的key,非叶子结点是占有的空间少,那么在查询时,将非叶子结点加载到内存中运行,再次降低了IO访问次数;

2.4索引的缺点

1.占用空间,生成索引,需要一系列数据结构,和一系列是数据元素;

2.降低插入修改的执行效率;

📚️3.事务

3.1事务存在意义

为啥要有事务呢,那么就有一下案列:

在之前,父母就要通过银行卡来进行转钱,如果当父母转了钱后,突然服务器崩了,那么此时钱就不会到你的手里;

运用MySQL进行实现:

mysql> create table accout(
    ->  id int primary key auto_increment,
    ->  name varchar(20) comment '账户名称',
    ->  money decimal(11,2) comment '金额'
    -> );

mysql> insert into accout(name, money) values
    -> ('阿里巴巴', 5000),
    -> ('四十大盗', 1000);

mysql> -- 阿里巴巴账户减少2000
mysql> update accout set money=money-2000 where name='阿里巴巴';

mysql> update accout set money=money+2000 where name='四十大盗';

那么假如在转出2000后,突然崩了,那么此时就会造成一些不必要的线下问题,那么此时事务就是为了解决这些问题;

注意:事务可以把多个SQL进行打包成一个整体,要么整个执行正确,要么一个都不执行(这里不是不执行,而是方便理解)即原子性~~~

3.2事务的使用

(1)开启事务:start transaction

(2)执行多条SQL语句

(3)回滚或提交:rollback/commit;
说明:rollback即是全部失败,commit即是全部成功。

代码实例:

mysql> start transaction;

mysql> update accout set money=money-1000 where name='阿里巴巴';

mysql> update accout set money=money+1000 where name='四十大盗';

mysql> commit;

那么此时SQL语句部分,可以改变;

注意:回滚是通过日志的操作,记录操作中的关键事务,这样的记录就是回滚的依据;

3.3事务的理解

事务的特点:

📍原子性:通过回滚的方式,保证一系列操作正确;

📍一致性:事务在执行之前和执行之后的数据都不能太离谱(这里要通过约束条件,检查机制)

📍持久性:事务做出的操作是持久的,保存在硬盘上

📍隔离性:数据库并发执行多个事务时的问题,通常来说一个服务器同时给多个用户提供服务,所以并发执行;

那么在并发过程中会存在哪些问题??

1.脏读 read committed

所谓脏读就是在事务A执行的过程中,事务B进行了读取同一个数据,那么之后事务A修改了数据,那么事务B所读的数据就是无效的数据;

解决脏读:

添加写枷锁,即在事务A执行过程中,B事务不能读取,只能等待事务A执行完成后才能读取;

那么此时:并发性下降,效率下降,数据正确性上升,隔离性提高~~~

2.不可重复读 repeatable read

所谓不可重复读就是在事务A执行完成后,在事务B读取的过程中,事务A又再次修改了数据,那么就造成事务B读取的数据两次结果不一样;(一个事务内部,两次读)

解决不可重复读

添加读加锁,即在脏读的前提下,在事务B进行读取的时候,事务A不能够进行修改;

那么此时:并发性下降,效率下降,数据正确性上升,隔离性提高~~~(相比脏读效果更强)

3.幻读 serializable

所谓幻读就是在前面两种解决前提下,在事务A执行完成后,事务B读取的过程中,事务A又重新执行另一个事务,那么此时事务B在两次读取的过程中,数据没有改变,但是结果集改变了~~~

解决幻读:

引入串行化的解决方式,完全规避了并发执行;

那么此时:完全没有并发了,数据准确率最高,效率最低~~~

所以得根据不同的事务场景来决定不同的隔离级别:《一级:完全并发》《二级:脏读引入写加锁》《三级:不可重复读引入写和读加锁》《四级:幻读完全串行化》,一般默认三级

📚️4.总结

💬💬本期小编讲解了关于MySQL中索引与事务,主要理解了索引的内部原理,和底层数据结构,以及事务的相关使用和并发执行的问题,也附上了对应代码供小伙伴们参考~~~

🌅🌅🌅~~~~最后希望与诸君共勉,共同进步!!!


💪💪💪以上就是本期内容了, 感兴趣的话,就关注小编吧。

** 😊😊 期待你的关注~~~**


本文转载自: https://blog.csdn.net/GGBond778/article/details/142281916
版权归原作者 GGBondlctrl 所有, 如有侵权,请联系我们删除。

“【MySQL】MySQL索引与事务的透析——(超详解)”的评论:

还没有评论