泰州专业网站制作公司,营销导向的企业网站优化,网站解决方案模板,SUPERW上海网站建设工作室文章目录 #x1f6eb;索引#x1f38d;索引的概念#x1f333;索引的作用#x1f384;索引的使用场景#x1f340;索引的使用#x1f4cc;查看索引#x1f4cc;创建索引#x1f332;删除索引 #x1f334;索引保存的数据结构#x1f388;B树#x1f388;B树#x… 文章目录 索引索引的概念索引的作用索引的使用场景索引的使用查看索引创建索引删除索引 索引保存的数据结构B树B树问题解决 事务⚾事务的概念⚽事务需要满足的四大条件事务控制语句与简单使用事务的并发控制封锁封锁协议 ⭕总结 本节目标 索引事务 索引
索引的概念
索引是一种特殊的文件包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引并指定索引的类型各类索引有各自的数据结构实现
索引的作用
MySQL 索引是一种数据结构用于加快数据库查询的速度和性能。
MySQL 索引的建立对于 MySQL 的高效运行是很重要的索引可以大大提高MySQL 的检索速度。 MySQL 索引类似于书籍的索引通过存储指向数据行的指针可以快速定位和访问表中的特定数据。打个比方如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话那么没有设计和使用索引的 MySQL 就是一个人力三轮车。拿汉语字典的目录页索引打比方我们可以按拼音、笔画、偏旁部首等排序的目录索引快速查找到需要的字。 索引的使用场景
创建索引时你需要确保该索引是应用在 SQL 查询语句的条件(一般作为WHERE 子句的条件)。
实际上索引也是一张表该表保存了主键与索引字段并指向实体表的记录。
索引虽然能够提高查询性能但也需要注意以下几点 索引需要占用额外的存储空间。 对表进行插入、更新和删除操作时索引需要维护可能会影响性能。 过多或不合理的索引可能会导致性能下降因此需要谨慎选择和规划索引。
满足以上条件时考虑对表中的这些字段创建索引以提高查询效率。
反之如果非条件查询列或经常做插入、修改操作或磁盘空间不足时不考虑创建索引。
索引的使用
创建主键约束PRIMARY KEY、唯一约束UNIQUE、外键约FOREIGN KEY时会自动创建对应列的索引
接下里我们实现一些索引的基本操作和使用
查看索引
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。
可以通过添加 \G 来格式化输出信息。
SHOW INDEX 语句
SHOW INDEX FROM table_name\GSHOW INDEX: 用于显示索引信息的关键字。 FROM table_name: 指定要查看索引信息的表的名称。 \G: 格式化输出信息。
执行上述命令后将会显示指定表中所有索引的详细信息
包括索引名称Key_name、索引列(Column_name、是否是唯一索引Non_unique、排序方式Collation、索引的基数Cardinality等。
这里博主查询一个博主已经建立好的一个student表的索引
案例查询查看学生表已有的索引查询结果如下
创建索引
对于非主键、非唯一约束、非外键的字段可以创建普通索引
使用 CREATE INDEX 语句可以创建普通索引。
普通索引是最常见的索引类型用于加速对表中数据的查询。
CREATE INDEX 的语法
CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);CREATE INDEX: 用于创建普通索引的关键字。 index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。 table_name: 指定要在哪个表上创建索引。 (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。 ASC和DESC可选: 用于指定索引的排序顺序。默认情况下索引以升序ASC排序。
以下实例假设我们有一个名为 students 的表包含 id、name 和 age 列我们将在 name 列上创建一个普通索引。
CREATE INDEX idx_name ON students (name);建立索引如下
删除索引
drop index 索引名 on 表名;以下实例是我们对上述建立索引的一个删除删除student表中name字段的索引
drop index inx_name on student;删除后查询结果如下
索引保存的数据结构
索引保存的数据结构主要为B树
再介绍B树之前我们先来看一下B树因为B树是在B树的基础上进行优化的
B树
B树与二叉树Binary Tree不是一个概念你可以将其翻译成Balance Tree或者是Bayer Tree。
B树是一种自平衡的树能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作都在对数时间内完成。
B树与AVL树不同可以拥有2个以上的子节点并且每个节点可以有多个键值这些属性减少了定位记录时所经历的中间过程加快了存取速度。B树更适用于读写相对较大的数据块存储系统如磁盘。这种数据结构常被应用在数据库和文件系统的实现上。 对于一个M阶B树具有以下特性 每个节点最多有 M 个子节点每个内部节点最少有 ⌈M/2⌉ 个子节点⌈x⌉为向上取整符号如果根节点不是叶子节点那么它至少有两个子节点。 具有 N 个子节点的非叶子节点拥有 N-1 个键。 所有叶子节点必须处于同一层上。
B树
是B-Tree的改进版本同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上并且增加了顺序访问指针每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说进行范围查找时只需要查找两个节点进行遍历即可。而B-Tree需要获取所有节点相比之下BTree效率更高。 那我们具体怎么存储数据库中的这些数据呢
比如我们有以下这样一张student表主键为id MyISAMMySQL的数据库引擎中是这样存储的 在InnoDB中的实现
问题解决
问为什么索引结构默认使用B-Tree而不是hash二叉树红黑树 hash虽然可以快速定位但是没有顺序IO复杂度高。 二叉树树的高度不均匀不能自平衡查找效率跟数据有关树的高度并且IO代价高。 红黑树树的高度随着数据量增加而增加IO代价高。 问为什么官方建议使用自增长主键作为索引。 结合BTree的特点自增主键是连续的在插入过程中尽量减少页分裂即使要进行页分裂也只会分裂很少一部分。并且能减少数据的移动每次插入都是插入到最后。总之就是减少分裂和移动的频率。 问B树与B树的异同点
相同点 一个节点可以存储多个元素叶子节点是排序的每个节点中的元素 也都按照从小到大的顺序排列 即 左小右大。所有叶子节点都位于同一层或者说根节点到每个叶子节点的长度都相同。根节点元素个数 1 k m-1 (m表示阶数 即 一个节点最多有多少子节点)非根节点元素个数 m/2 k m- 不同点 B树叶子节点是有指针的 MySQLInnoDB中采用的是双向指针上层非叶子节点也有双向指针B树非叶子节点的元素是与叶子节点有冗余重复的情况 事务
⚾事务的概念
MySQL 事务主要用于处理操作量大复杂度高的数据。比如说在人员管理系统中你删除一个人员你既需要删除人员的基本资料也要删除和该人员相关的信息如信箱文章等等这样这些数据库操作语句就构成一个事务 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 事务处理可以用来维护数据库的完整性保证成批的 SQL 语句要么全部执行要么全部不执行。 事务用来管理 insert,update,delete 语句
⚽事务需要满足的四大条件 原子性一个事务transaction中的所有操作要么全部完成要么全部不完成不会结束在中间某个环节。事务在执行过程中发生错误会被回滚Rollback到事务开始前的状态就像这个事务从来没有执行过一样。 一致性在事务开始之前和事务结束以后数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。 隔离性数据库允许多个并发事务同时对其数据进行读写和修改的能力隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别包括读未提交Read uncommitted、读提交read committed、可重复读repeatable read和串行化Serializable。 持久性事务处理结束后对数据的修改就是永久的即便系统故障也不会丢失
事务控制语句与简单使用
控制语句部分 BEGIN 或 START TRANSACTION 显式地开启一个事务 COMMIT 也可以使用 COMMIT WORK不过二者是等价的。COMMIT 会提交事务并使已对数据库进行的所有修改成为永久性的 ROLLBACK 也可以使用 ROLLBACK WORK不过二者是等价的。回滚会结束用户的事务并撤销正在进行的所有未提交的修改
使用如下
开启事务start transaction;执行多条SQL语句回滚或提交rollback/commit;
rollback即是全部失败commit即是全部成功
事务的并发控制
数据库是一个共享资源可以供多个用户使用。允许多个用户同时使用一个数据库的数据库系统称为多用户数据库系统。例如飞机订票数据库系统、银行数据库系统等都是多用户数据库系统。在这样的系统中在同一时刻并发执行的事务数可达数百上千个。
而在面对这么多并发执行的事务存在以下几个问题
丢失修改
两个事务T1和T2读入同一数据并修改T2提交的结果破坏了T1提交的结果导致T1的修改被丢失
不可重复读
不可重复读是指事务T1读取数据后事务T2执行更新操作后使T1无法再现前一次读取结果
读“脏”数据
读“脏”数据是指事务T1修改某以数据并将其写回磁盘事务T2读取同一数据后T1由于某种原因被撤销这时被T1修改过的数据恢复原值T2读到的数据就与数据库中的数据不一致则T2读到的数据就为“脏”数据即不正确的数据。 封锁
封锁是实现并发控制的一个重要技术。所谓的封锁就是事务T在对某个数据对象例如表、记录等操作之前先前系统发出请求对其加锁。
加锁后事务T就对该数据对象有了一定的控制在事务T释放它之前其他事务不能更新此数据对象。
确切的控制由封锁的类型决定。基本的封锁的类型由两种排他锁exclusive locks简称X锁和共享锁share locks简称S锁 排他锁又称为写锁。若事务T对数据对象A加上X锁则只允许工读取和修改Ar其他任何事务都不能再对A加任何类型的锁直到T释放A上的锁为止。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A。 共享锁称为读锁。若事务 T对数据对象A加上S锁则事务工可以读A但不能修改A其他事务只能再对A加S锁而不能加X锁直到T释放A上的S锁为止。这就保证了其他事务可以读A但在T释放A上的S锁之前不能对A做任何修改。
封锁协议
在运用X锁和S锁这种基本封锁对数据对象加锁时还需要约定一些规则。例如何时申请X锁或S锁、封锁时间、何时释放等这些规则称为封锁协议。对封锁方式制定不通的规则就形成了各种不同的封锁协议。
一级封锁协议
一级封锁协议是指事务T在修改数据R之前必须先对其加X锁直到事务结束才释放。事务结束包括正常结束COMMIT和正常结束ROLLBACK。
一级封锁协议可防止丢失修改并保证事务T是可恢复的可解决丢失修改问题
二级封锁协议
二级封锁协议是指在一级封锁协议基础上增加事务在读取数据R之前必须先对其加S锁读完后即可释放S锁。
二级封锁协议除防止了丢失修改还可进一步防止读“脏”数据
三级封锁协议
三级封锁协议是指在一级封锁协议的基础上增加事务T在读取数据R之前必须先对其加S锁直到事务结束才释放。三级封锁协议除了防止丢失修改和读“脏”数据外、还进一步防止了不可重复读
⭕总结
关于《【MySQL】 MySQL索引事务》就讲解到这儿感谢大家的支持欢迎各位留言交流以及批评指正如果文章对您有帮助或者觉得作者写的还不错可以点一下关注点赞收藏支持一下
