MySQL索引事务

最新推荐文章于 2024-05-21 09:46:31 发布

niceffking

最新推荐文章于 2024-05-21 09:46:31 发布

阅读量123

点赞数

分类专栏： MySQL 文章标签： mysql 数据结构 b树 Powered by 金山文档

本文链接：https://blog.csdn.net/niceffking/article/details/129360022

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

本节内容

索引

事务

索引

概念

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

作用

表示数据库中表,数据, 索引之间的关系

索引可用于快速定位,检索数据(省略了遍历的过程)

索引对搜索的性能有很大的提升

缺点

查找的速度是快了, 但是同时也付出了一定的代价

需要使用额外的存储空间的代价来保存这些索引

索引可能会拖慢新增, 删除, 修改的速度

整体来说, 索引的利还是大于弊的, 因为在一般的开发场景当中, 查询的频率实际要比增删改的频率要高很多.

语法

以下例子皆由下表为基本的图解(student表)

student表

查看索引

show index from 表名;

如果还没创建索引, 则会显示Empty set

创建索引

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

对student表的name列创建索引:

create index index_student_name on student(name);

也就是说把表中的内容, 根据name又创建了一份新的目录

但是, 创建索引操作可能会很危险!!! 如果表里面的数据很大, 那么简历这个索引的开销也会很大. 就相当于有一本很厚的书, 然后你在书写好之后给这本书再写一个目录, 这样做当然是不合理的, 好的做法是在创建表之前, 就把索引给设定好. 要是已经有很多数据了, 索引就应该尽量避免去动它

删除索引

drop index 索引名 on 表名;

和创建索引类型, 删除索引同样也存在危险

索引的数据结构

语法操作容易, 但是我们主要是去理解这些索引语法操作背后的数据结构

有哪些可以加快查询的数据结构呢?

例如:二叉搜索树, 哈希表等.

对于二叉搜索树来说, 如果元素多了此时树的高度就会增加, 数的高度增加了, 就相当于比较的次数增加了, 当数据量大的情况下, 许多数据一般是存放在io设备上的, 访问的速度则是访问io设备的速度, 相比于cpu直接读取内存的速度要慢了许多

对于哈希表而言, 虽然它查询比较快, 但是不能支持范围查询, 也不支持模糊匹配, 所以专门为SQL定制的数据结构来完成 , 也就是B+树

什么是B+树? 先了解B树

B树,B+树

B树也叫作B-树, 这个'-' 是一个连接符. B树其实是实现了一个N叉的搜索树, 也就是所有结点的最大度为N的树, 如下图:

相比于二叉树, 二叉树每个结点只能保存一个数据, 每个结点的度不能超过2 , 而B树上的结点保存的key多了, 以为这在同样个key的数量前提下, B树的高度会比二叉搜索树要低很多. 树的高度越高, 访问io设备的次数就越多, 性能就越低. B树高度降低, 访问次数也降低了.

B+树又是在B树的基础上又做出了一些改进. B+树其实也是一个N叉搜索树, 每个结点也可以存多个数据. 和B树不一样的是, B+树的一个结点里面有几个数据, 那么这个结点的度就为这个结点的度数. 例如:

以此类推

B+树的特点

一个节点可以存储N个key, N个key划分出了N个区间

每个节点中key的都会在其子节点中存在, 同时该key都是其子节点中key 的最大值

B+树的叶子结点是首尾相连的, 类似一个链表

整棵树的所有key的包含在叶子结点中的

由于叶子节点是完整的数据集合, 只在叶子节点这里存储数据表的每一行数据, 而非叶子节点只存储key本身

B+树的优势

当前一个结点保存更多的数据, 同时一个结点的度>2, 最终形成的树的高度相对较低, 查询的时候io访问次数减少了很多.

所有查询的数据最终都会落到叶子节点上, 查任何一个数据的io访问次数是一样的.

B+树所有的叶子节点, 构成一个链表, 此时能够很容易的进行范围查询

由于数据都在叶子节点上, 非叶子节点上值存储key, 所以非叶子节点占用的空间是比较小的, 这些非叶子节点就可以在内存中缓存或者缓存一部分, 又进一步减少了io访问的次数.

这就是MySQL组织数据的方式, 当你看见一张MySQL的一张表的时候, 他并不是像Excel表格那样存储数据在硬盘上的, 也有可能是按照这个树形结构, 具体是哪种结构, 取决于你的表里面有没有索引, 以及数据库使用了什么存储引擎

那如果一张表有多个索引, 那么这个B+树是怎么构建的呢? 加入有两个属性有索引,一个为id, 一个为name, 表的数据还是按照id为主键, 构建出B+树, 然后通过叶子结点来组织所有行的数据, 其次, 针对name这一列, 会构建出另外一行B+树, 但是这个B+树的叶子节点就不再存储这一行的完整数据, 而是存主键id. 此时如果你根据name来查询, 这个时候还是查询到的主键id, 之后再根据这个主键id, 再去通过主键id的B+树再查询一次. 这个过程称为回表, 都是MySQL自动完成的.

事务

什么是事务

假设有这种情况, 一个人向另外一个人转账, 具有如下两个操作语句:

update account set balance = balance - 500 where id = 1;

update account set balance = balance + 500 where id =2;

假设在执行这两步转账的过程中, 执行完1 之后, 数据库崩溃了, 或者主机宕机了, 这个时候转账就难免会出现问题, 产生戏剧性的一面: id = 1 的账户的钱扣了, 但是 id = 2 的账户钱却没有到账.

事务就是为了解决上述问题的, 事物的本质就是讲多个SQL语句打包成一个整体. 要么全部执行成功, 要么一个都不执行, 而不会出现执行一半这样的中间过程.

但是这种如果真的在执行过程中出现错误中断, 那么他不是真的没有执行, 而是执行到了一半出错了, 出错之后 ,把数据还原成未执行之前的状态, 让数据看起来没有执行任何操作.

如果将上述的1和2两个SQL语句作为一个事务, 当第一个SQL语句执行完后崩溃. 当下次数据库重启之后, 就会自动的把上次修改一半的数据给还原. 也就是说, 当id = 1 的账户扣了500之后, 会进行回轨操作, 将这个id = 1 的账户给+500,.

那为什么数据库知道是给哪个账户+ 500 呢?? 原来有一个专门记录这些事物操作的日志, 通过这个日志可以知道你对哪些数据进行了哪些操作, 以此来还原数据库.

但是也正是因为如此, 在使用事务的时候, 执行SQL语句的开销会更大,效率更低.

语法

将上面转账的两个SQL语句使用事务语法连接起来:

start transaction:
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;
commit;

start transcation为开启业务

commit为结束事务并提交

特性

原子性, 这是最核心的特性, 也就是我们上面所讲的, 把多个SQL语句捆绑在一起

一致性, 事务执行前后, 需要保持数据的目的性和正确性

例如: 转账前两个人的账户余额是: 1000 0, 转账之后不能变成500 50000, 这就违背了原来数据的正确性和一致性.

持久性, 事务修改的内容是写到硬盘上去的, 持久存在的, 数据库服务器重启也不会丢失

隔离性, 是为了解决并发执行事务,所引起的问题.

一些问题

并发:一个服务器, 要给多个客户端提供服务, 这些客户端提出的请求, 有可能是一个一个请求, 也可以是一次来了很多个请求, 服务器同时处理多个客户端的请求, 这就叫做并发.

并发引起的问题: 如果并发的这些事务是对同一个表进行修改数据,可能会带来一些列问题, 例如一个账户给多个账户进行转账, 此时就有可能把数据给搞乱了.

脏读问题:一个事物A正在对数据进行修改, 在提交修改之前, 另外一个事务B也对同一个数据进行了读取, 后面事务A又把这个数据给改了, 此时事务B的操作就称为"脏读", B读取到的数据也被称为"脏数据(无效数据)". 为了解决这个问题, MySQL加入了"写操作加锁"这样的机制, 也就是在给事务A写的过程中加锁, 那么事务B 或者别的事务就不能读取数据. 这个操作就降低了并发程度, 提高了隔离性(提高了数据的准确性)

不可重复读问题: 事务1已经提交了数据,此时事务2 开始读取数据, 在事务2读取的过程中, 另外一个新的事务3又提交了新的数据. 此时意味着事务2 在多次读取的过程中, 读取到的数据是不一样的, 但是这个事物2预期是前后多次读取的数据必须是相同的. 为了防止在读的过程中, 其他事务对数据进行修改, MySQL加入了"读操作加锁"这个操作, 进一步降低了并发事务的处理能力(降低效率), 提高了事务的隔离性

幻读:在读加锁和写加锁的前提下, 一个事物同时读取两个数据, 然后发现读取的数据值是一样的, 但是结果集却不一样, 就比读的一张表的数据值不变, 但是第一次是看到这张表, 第二次看到的却是除了这张表还有另外一张表, 这种情况就称为"幻读". 处理方法: 数据库放弃使用使用事务处理, 选择"串行化"来处, 理 ,也就是一个一个的处理, 这种处理方法并发程度是最低的, 但是效率较低, 隔离性也是最高的(准确性最高).

MySQL内置机制: 无论是读加锁,还是写加锁, 还是串行化操作, 都可以可以通过修改MySQL的配置文件, 来设置当前的MySQL工作在哪种状态下. 这种都是内部机制, 不是代码. 这块涉及到多线程的内容.

这几个机制如何选择, 看具体的场景, 例如转账的时候, 两个账户数据是一分都不能少, 哪怕宁愿效率低一点也没关系. 像抖音的点赞系统, 要求快, 但是并不一定要准确, 有时候用户看到的点赞数和真实的点赞数之间差个几个或者十几个都问题不大, 追求的是效率.