0


MySQL的索引和事务

1.索引(index)

1.1 索引的理解

索引存在的主要意义,就是为了提高查询的效率

索引就相当于目录

大多数情况下,查询的频率是高于增删改的,所以索引的存在是很有必要的

索引付出的代价:

  1. 消耗更多的空间

  2. 虽然提高了查找效率,但是降低了增 删 改的效率

(比如插入新记录,需要既能够插入硬盘的数据,又要调整索引)

1.2 查看索引

show index from 表名;

1.3 创建索引

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

创建索引操作,也是一个低效操作,如果表中数据少不影响,

如果表中数据多,创建索引操作,就可能会非常耗时,并且带来大量的硬盘IO

1.4 删除索引

**drop index 索引名 on 表名; **

**删除操作和创建操作都是比较低效的操作 **

SQL可以支持一些 定义变量,定义函数,条件,循环,库函数等 但是这些很少用到

SQL 一般都是单纯的去 增删查改


1.5 索引的数据结构 (B+树)

索引能够提高查询效率,具体是怎么提高的

索引背后的数据结构是怎么样的

数据结构中,查询效率高的有 哈希表 和 二叉搜索树,但是这两种不适合用于数据库索引

因为

(1)哈希表虽然增删查改都快 O(1)

但是只能查询 值 相等的情况,但是如果是< > between and 这种比较大小的范围查询就不适合

(2)二叉搜索树,查询速度 O(N)最坏情况

**AVL 树 / 红黑树 **(比较平衡的二叉搜索树)O(logN)

如果数据库数据特别多时,树的高度就会比较高,高度高了就查询时比较的次数就会多

如果每次要读硬盘,这样就不太行了

所以上面两种都不适合,就有了这样一个专门用于数据库的数据结构 B+ 树

下面先了解一下B树

** B树,是一个N叉搜索树**

这样分成N个叉的作用,就是表示相同元素的数据集合时,比二叉树的高度小很多,IO次数就降低了很多

B+树


2.事务

2.1 理解事务

事务指逻辑上的一组操作,组成这组操作的各个单元,要不全部都执行完,要不就一个都不执行

不是说真的没执行,而是执行一半如果出现问题,能够恢复如初

事务就能保证,当执行过程中出现问题时,自动的把前面的sql执行的效果进行还原,恢复如初 这个操作叫回滚(rollback)

在事务执行过程中,MYSQL会记录每一步都执行了啥,一旦出现问题就可以根据记录回滚

在不同的环境中,都可以有事务。对应在数据库中,就是数据库事务。

2.2 使用

(1)开启事务:start transaction;

**(2)执行多条SQL语句 **

(3)回滚或提交:rollback/commit; 全部失败/全部成功

2.3 事务的特性

原子性:事务的存在的意义,能够把多个SQL打包成一个整体,要么全部执行完,要么一个都不执行(如果执行过程中出错,则自动回滚)

一致性:事务执行前后,数据处在“一致”的状态(数据能对的上,合情合理)

持久性:事务进行的改动,都是写到硬盘里的,不会随着程序重启/主机重启,而丢失

隔离性:(最复杂)多个事务,并发执行时,事务之间能够保持“隔离”,不互相干扰

(隔离性存在的意义,就是让并发执行事务的过程中,尽量不出问题(问题在可控范围内))

在并发执行事务下会出现的问题

(1)脏读(读取到脏数据,解决方法:对写操作加锁)

脏读:一个事务A在修改数据,提交之前,另一个事务B读取了数据,此时A极有可能在提交的时候提交的是不同数据(后续又修改了)

此时事务B读到的就是“无效的数据”,就称为** 脏读,读取脏数据**

解决脏读问题的思路

在提交之前,不能读,提交之后才能读(写完才能读)

相当于对 写操作 加锁

在写加锁之前,我的写操作,和别的事务的读操作,就是完全并发的

并发性是最高的,隔离性最低的

当写加锁之后,我的写操作,别的事务就不能读了

并发性降低了(效率降低),隔离性提高了(准确性提高了)

(2)不可重复读 (读的过程被修改,解决方法:使用读加锁)

如果对写操作加锁后,当我的写操作执行完后,别的事务开始读取代码

此时如果我再次对代码进行修改,再提交,此时别的事务就发现代码改变了

不可重复读:在一个事务A中,多次读取同一个数据,发现不一样****(读的过程被修改)

不可重复读:需要使用 读加锁 来解决

引入读加锁后,并发程度又进一步降低了(效率降低了)

隔离性又提高了(数据准确性也提高了)

**(3)幻读(结构集发生改变,解决方法:串行化) **

对写操作加锁,并且对读加锁,此时如果读文件A时,去改文件B(不影响另一个事务读)

虽然这样操作,对另一个事务读取的数据不影响,但是,两次如果读完后,发现结构集变了

(另一个事务读取时第一次看到只有一个.java文件,下次读的时候突然发现变为两个.java文件了)

这种情况称为“幻读”

为了解决幻读问题,方法就是串行化

也就是另一个事务在读时,我不能进行代码操作

2.4 MySQL的隔离级别

** MySQL提供了“隔离级别”选项,四个选项**

**1.read uncommitted **

允许读未提交的数据,并发程度最高,隔离性最低。可能存在脏读/不可重复读/幻读问题

**2.read committed **

只能读提交之后的数据,相当于 写加锁,并发程度降低,隔离性提高

解决了脏读,可能存在 不可重复读/幻读问题

3.repeatable read(默认)

相当于读和写都加锁了,并发程度再降低,隔离性再提高,

解决了脏读/不可重复读,可能存在幻读问题

4.serializable

严格执行串行话,并发程度最低,隔离性最高

解决了 脏读/不可重复读/幻读问题,但效率最低

在MySQL的配置文件 my.ini 中进行设置

根据不同的需求场景,就可以分别设置不同的档位了


3. 本篇需理解这几点

1. 如何理解索引

从这三个方面思考

(1)索引是干啥的(解决了什么问题)

索引相当于目录。提高查询效率

(2)索引付出的代价

索引会占用额外的磁盘空间;

虽然提高了查找效率,但是降低了增 删 改的效率

(3)索引背后的数据结构

B+树(可以思考一下B树和B+树的区别)

**2. 如何理解事务 **

从这三个方便思考

(1)事务是干啥的(从原子性切入)

事务包含一个或多个业务操作,这些操作要么都执行,要么都不执行。事务常被用来确保数据的一致性。(可联系原子性展开说明,并解释“回滚”)

(2)事务的其他特征还要啥

原子性;持久性;一致性;隔离性;(可展开思考一下)

(3)重点隔离性,在并发执行事务下会有哪些问题,以如何解决

脏读(读取到脏数据,解决方法:对写操作加锁)

不可重复读 (读的过程被修改,解决方法:使用读加锁)

幻读(结构集发生改变,解决方法:串行化) (可详细展开思考)

3. MySQL 隔离级别都有什么

**1.read uncommitted **

**2.read committed **

3.repeatable read(默认)

4.serializable

从上到下,并发程度逐渐降低,隔离性增高(可从脏读/不可重复读/幻读进行分析)


标签: 数据库 mysql

本文转载自: https://blog.csdn.net/m0_58761900/article/details/126191109
版权归原作者 快到锅里来呀 所有, 如有侵权,请联系我们删除。

“MySQL的索引和事务”的评论:

还没有评论