专门做家教的网站,网站开发大学,wordpress 菜单 链接,wordpress好玩插件#x1f970;#x1f970;#x1f970;来都来了#xff0c;不妨点个关注叭#xff01; #x1f449;博客主页#xff1a;欢迎各位大佬!#x1f448; 本期内容讲解 MySQL 中的索引和事务#xff0c;在学习的过程中#xff0c;我们需要经常问自己为什么 文章目录 1. 索… 来都来了不妨点个关注叭 博客主页欢迎各位大佬! 本期内容讲解 MySQL 中的索引和事务在学习的过程中我们需要经常问自己为什么 文章目录 1. 索引1.1 索引的概念1.2 索引的目的1.2.1 为什么使用索引会加快查询速度 1.3 索引的使用1.4 索引的分类1.5 索引的优缺点1.6 MySQL 的默认存储引擎 InnoDB 背后的数据结构 —— B 树1.6.1 B树1.6.2 B树 2. 事务2.1 事务的概念2.2 事务的使用2.3 事务的四大特性 —— ACID2.3.1 原子性2.3.2 一致性2.3.3 持久性2.3.4 隔离性1 脏读2不可重复读3幻读 1. 索引
1.1 索引的概念
【概念】索引是一种特殊的文件包含着对数据表里所有记录的引用指针可以对表中的一列或多列创建索引并指定索引的类型各类索引有各自的数据结构实现 ~
1.2 索引的目的
【目的】就比如说本期文章带有目录笔者每篇文章都带有目录~ 通过目录我们就对这期内容有一个大致的了解并且通过目录我们可以快速的定位我们想要的内容而索引的作用和目录的作用类似都是加快查询速度~
1.2.1 为什么使用索引会加快查询速度
首先我们知道MySQL 数据库文件存储在磁盘上的磁盘 I/O 是数据库操作比较耗时的一部分
无索引数据库会进行全表扫描它必须读取表中的每一行数据来查找匹配的行时间效率为 O(n)当表的数据量非常大时就会导致大量的磁盘 I/O 操作非常耗时间有索引可以直接跳到索引指示的数据位置不用扫描整张表大大减少了磁盘 I/O 操作的次数减少时间如 MySQL 的默认存储引擎 InnoDB 默认使用 B 树来作为索引的数据结构 而 B 树的查询效率非常高时间复杂度是O(logN)本期内容后面将会具体介绍 B 树
我们知道索引是一种特殊的文件MySQL 索引文件主要存储索引数据库文件存储的数据不仅包括全部数据还包括索引等信息索引文件与数据库文件相比体积小很多通过查询索引再映射到数据库记录查询效率就会高很多~
【举例】 还是结合目录来看通过目录可以快速定位到哪一个位置比如查字典可以通过目录快速找到需要的内容在哪一页没有目录的话我需要从字典的第一页每一页一页的翻找到想要的内容想想是不是很耗时呢索引的作用也是如此加快查询的作用
1.3 索引的使用
【情况一】对于创建主键约束唯一约束外键约束时会自动创建对应列的索引
首先创建一个 student 表以 id 为主键此时创建了主键约束会自动创建对应列的索引 create table student (id int primary key,username varchar(50));查看索引 show index from 表名;从下图中可以看到主键 id自动创建了索引 如果 id 没有加主键约束是不会自动创建索引的~ 如下图 【情况二】创建普通索引对于非主键、非唯一约束、非外键的字段可以创建普通索引
创建索引
create index 索引名 on 表名(列名);删除索引
drop index 索引名 on 表名;可以动手多操作哦才会记忆深刻呀~更加利于理解哦
1.4 索引的分类 1.5 索引的优缺点
【优点】
加快查询速度通过索引可以快速定位到满足查询条件的数据行减少数据的扫描范围从而提高查询效率。例如在一个包含大量用户信息的表中如果经常根据用户姓名进行查询为姓名列创建索引后查询速度大大提升保证数据唯一性可以通过创建唯一索引来确保表中某列或某些列组合的数据具有唯一性防止出现重复数据比如在用户表中为身份证号码列创建唯一索引就可以保证每个用户的身份证号码是唯一的支持数据排序索引可以按照指定的列进行排序当查询需要对结果进行排序时使用索引可以避免数据库在查询时进行额外的排序操作提高查询性能。比如在订单表中为订单时间列创建索引当按照订单时间查询并排序订单时数据库可以直接使用索引来获取有序的数据。
【缺点】
占用存储空间索引本身需要占用一定的磁盘空间来存储索引结构随着数据量的增加和索引数量的增多占用的空间也会相应增大比如一个大型数据表创建多个索引后可能会使数据库文件的大小增加很多增加维护成本在数据插入、更新和删除时索引也需要进行相应的更新操作这会增加数据库的维护成本和时间开销。例如当向表中插入一条新记录时如果该表有多个索引那么每个索引都需要进行更新以保证索引的准确性和一致性降低写入性能由于写入数据时需要同时更新索引所以会降低写入操作的性能比如在批量插入数据时没有索引的表插入速度会比有索引的表快很多
1.6 MySQL 的默认存储引擎 InnoDB 背后的数据结构 —— B 树
在本期内容开头就介绍了数据库索引的作用是为了加快查询速度的~ 那么我们会思考其索引背后的数据结构是什么呢 并且能够让它加快查询速度
我们可以回想一下之前学过的数据结构其中二叉搜索树和哈希表就是比较适合查询的但是很遗憾的是它们两个都不适合于数据库索引的底层数据结构
二叉搜索树 二叉搜索树查找的时间复杂度是树的高度我们知道数据库一般都是将数据存储在硬盘上数据量很大的时候如果使用二叉搜索树则树的高度也会很大导致查询效率会很慢哈希表哈希表它是由数组和链表组成的查找的时间复杂度为 O(1)尽管时间复杂度低但是它的 key 不是有序的并且对于数据库中的大于、小于的范围查找或者是 LIKE 类似的模糊查询哈希表都是不能够做到的比如我们要查询学生的序号在 202110120510 到 202110120534 之间的学生信息哈希表是做不到的~
噔噔噔!!! 这里就要介绍一种特殊的数据结构B树 —— 专门为了数据库索引量身定做的数据结构
1.6.1 B树
介绍 B 树前先介绍一下 B 树也叫做 B- 树注意!!! 这里的 ‘-’ 不是减号只是连接符哦 很多小伙伴看到有 B 树可能会想当然的以为也有 B- 树
B树可以认为是一颗N叉搜索树结构如下 我们可以通过图看到当节点的子树多了节点上保存的 key 就多了在相同个数 key 的情况下B 树的高度就比二叉搜索树的高度要低很多对 IO 操作的次数就越少这样查找性能就会比较高
1.6.2 B树
我们先来了解一下 B 树的特点~
B 树的特点
一个节点可以存储 N 个 keyN 个 key 可以划分出 N 个子区间不是 N1 个每一个 key 都会在子区间出现且为子区间的最大值B 树的叶子节点是首尾相连的类似于一个链表B 树的非叶子节点只用于索引并不保存数据只有叶子节点存储着索引和数据
到底是一个什么样的数据结构呢 我们一起来看看 既然有 B 树那么为什么还要有 B 树呢
我们一起来分析一下 B 树的优缺点
B树优点每个节点都存储了索引和对应的数据在查找离根节点近的节点时查找效率是很高的不用每次都查找到叶子节点与B树相比因为 B 树必须得查找到叶子节点B树缺点 不利于范围查找即不利于区间查找比如要找 5 - 14 的索引值那么 B 树就需要多次从根节点逐个查找而 B 树的叶子节点是链表连接起来的且是从小到大依次有序的在 B 树中只用找到 5 索引 和 14 索引的叶子节点从 5 沿着链表遍历到 14 即可
再来分析一下 B 树的优点
查询任意一个节点最终都会落到叶子节点每次 IO 访问次数是一样的B 树的所有叶子节点构成了一个完整的链表适合进行范围查找只有叶子节点是存储着完整的数据非叶子节点只记录索引这样这些非叶子节点占用的内存就十分的小又进一步减小 IO 操作次数
【补充知识】
1回表
比如在有一些表中有多个索引如上述创建的 student 表里我们在 username 这一列创建了一个索引此时表的数据还是会根据主键id 构建出 B 树通过叶子节点将数据组织起来其次会根据 username 这个列创建一个B树但这个 B 树的叶子节点只存储主键id是什么此时如果你是通过 username 这个索引来查找的会通过叶子节点拿到的 id 索引再去由 id 创建的B树里再查一次即查两次B树上述过程在数据库中就叫回表
上述过程是 Mysql 自动完成的用户是完全感知不到的这是因为主键索引也是聚簇索引叶子节点存储整个数据和索引而其他索引就是非聚簇索引叶子节点只存储主键和索引因此使用非聚簇索引进行查询的时候需要再拿着这个查询到的主键再在聚簇索引中进行一次查询即回表
2聚簇索引和非聚簇索引
在 MySQL 的 默认 InnoDB 引擎中每个索引都会对应一颗 B 树而聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同聚簇索引叶子节点存储的是行数据因此通过聚簇索引可以直接找到真正的行数据而非聚簇索引叶子节点存储的是主键信息所以使用非聚簇索引还需要回表查询因此我们可以得出聚簇索引和非聚簇索引的区别主要有以下几个
存储内容聚簇索引叶子节点存储的是行数据而非聚簇索引叶子节点存储的是聚簇索引通常是主键 ID效率聚簇索引查询效率更高而非聚簇索引需要进行回表查询因此性能不如聚簇索引数量上聚簇索引一般为主键索引而主键一个表中只能有一个因此聚簇索引一个表中也只能有一个而非聚簇索引则没有数量上的限制
2. 事务
2.1 事务的概念
我们先来想一下这个场景七夕节到了小丁准备给他的女朋友小万转账 1314那么 sql 语句应该如下
update account set money money - 1314 where name ‘小丁’;
update account set money money 1314 where name ‘小万’;假设在执行完第一条语句后数据库突然崩溃了或者主机宕机了此时就出现了问题小丁的钱扣了但是小万没有收到转账小万就会很生气觉得没转就没转而不能这样骗她小丁就百口莫辩了明明自己转了呀现实生活中有很多这样的支付场景那么如何解决呢
事务则是解决上述问题的事务指的是逻辑上一组操作组成这组操作的各个单元要么全部成功要么全部失败在不同环境中都可以有事务对应在数据库中就是数据库事务通俗的来讲就是把 sql 语句打包在一起要么全部都执行成功要么全部都不执行不会出现执行到一半的情况注意这里的全部都不执行并不是真的没执行而是执行到一半出现问题后选择恢复到原来的样子将数据灰度到未执行的状态这个恢复操作就叫做 “回滚”rollback
2.2 事务的使用
开启事务start transaction;中间放要执行的 sql 语句回滚或提交: rollback/commit; 说明 rollback表示“全部失败”commit表示“全部成功”
具体代码如下
start transaction; //开启事务
update account set money money - 1314 where name 小丁;
update account set money money 1314 where name 小万;
commit; //提交事务2.3 事务的四大特性 —— ACID
2.3.1 原子性
事务的初心就是原子性事务是一个不可分割的单位数据库事务里的 sql 语句要么全部执行要么全部不执行不会出现执行一半的情况如果执行一半出现问题就会进行 “回滚” 操作强调事务是一个整体
2.3.2 一致性
事务执行前后数据都是一个合法的状态就像上面的转账情况在转账前和转账后数据都应该是一个合法的状态是一致的强调数据的合法状态
2.3.3 持久性
事务修改的内容是写入硬盘的持久存在重启也不会消失强调事务修改内容都是写入硬盘的具有持久性
2.3.4 隔离性
多个事务并发执行的时候每个事务应该感受不到其他事务的存在各个事务是隔离的强调多个事务并发执行各事务是隔离的相互不影响
但是不同的事务隔离级别会导致不同的并发问题如脏读不可重复读幻读下面将具体介绍这三个问题
1 脏读
【概念】脏读就是一个事务读取到了另一个未提交事务修改的数据
【举例】事务A 对数据库中的小万余额进行读取余额为 100接着再执行更新操作将余额更新为 500此时并没有提交事务而另一边事务B 对数据库中的小万余额进行读取那么事务B就读取到了事务A更新的金额余额为 500但是此时事务A并没有提交事务随时可能发生回滚操作如果上述情况事务发生回滚那么余额为 100而事务B读取到了事务A的过期数据500这个就是脏读
【解决方式】给事务A修改数据这个操作进行写加锁当事务A修改数据的时候其它事务不能对该数据进行读取这样就意味着此时写操作和读操作不能并发执行了降低了并发程度即降低了小效率但是提高了隔离性即提高了数据的准确性
2不可重复读
【概念】不可重复读就是一个事务多次重复读取一个数据如果出现了前后两次读取的数据不一致的情况
【举例】一个事务A对小万的余额进行读取此时有一个事务B对小万的余额进行更新并提交事务这样事务A对小万的余额再进行读取的时候就会发现前后两次读取的数据不一样这就是不可重复读
【解决方式】给事务A对数据进行读取的时候进行读加锁当事务A对数据进行读取操作的时候其它事务不能对该数据进行修改此时又降低了并发程度提高了隔离性
3幻读
【概念】幻读就是在一个事务内多次查询符合条件的记录数量出现了前后查询数量不一致的情况
【举例】事务A在数据库中查询余额大于500的用户此时有十条记录但是事务B此时插入了一条余额大于500的用户并提交事务这个时候事务A再进行查询余额大于500的用户就查询到十一条记录这个就是幻读
【解决方式】数据库使用 “串行化” 的操作来解决幻读的问题就是彻底放弃事务并发一个接一个的串行执行事务此时并发程度最低效率最低隔离性最高数据准确性最高
数据库提供的四个隔离级别 read uncommitted读未提交 没有任何锁限制并发程度最高隔离性最低会发生上述三种问题 read committed读已提交 给写加锁并发程度降低隔离性增加但会产生“不可重复读”和“幻读”问题 repeatable read可重复读MySQL的默认隔离级别 给读写都加锁并发程度进一步降低隔离性进一步增加但可能会产生“幻读”问题 serializable串行化 彻底实行串行化并发程度最低隔离性最高
本期内容回顾
✨✨✨本期内容到此结束啦~
