【MySQL】MySQL索引与事务的透析——（超详解）

GGBondlctrl

于 2024-09-15 17:30:14 发布

阅读量674

点赞数 31

分类专栏： MySQL 文章标签： mysql 数据库 MySQL索引 MySQL事务

本文链接：https://blog.csdn.net/GGBond778/article/details/142281916

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

前言

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

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

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

2.不可重复读 repeatable read

3.幻读 serializable

📚️4.总结

📚️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事务的理解

事务的特点：

📍原子性：通过回滚的方式，保证一系列操作正确；

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

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

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

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