【MySQL】索引和事务重点知识汇总

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 【MySQL】索引和事务重点知识汇总

1.索引:

  1. 索引存在的意义就是为了提高查询到效率.
  2. 索引的作用就类似与一本书的目录,通过目录就可以快速找到想要的内容.如果没有目录就只能一页一页的翻(遍历).
  3. 使用索引付出的代价(有得就有失): a)消耗了更多的空间,b)虽然提高了查找效率,但是降低了增删改的效率(因为插入修改记录,不仅需要修改硬盘的数据还要调整索引).
  4. 虽然索引有一些代价但是仍然认为还是值得使用索引的,因为大多数情况下查询的频率是高于增删改的.

1.1 索引的使用:

  1. 对于生产环境上比较大的表,一般都是建表之初就把索引都规划好,这样就会避免很多的低效操作.
  2. 查看索引, show index from 表名;
  3. 创建索引, create index 索引名 on 表名(列名);
  4. 创建索引是一个低效的操作,如果表里的数据少,那么创建索引开销就不大;如果表里的数据很多,创建索引操作就会非常的耗时并且带来大量的硬盘IO,甚至会卡死数据库.
  5. 创建索引的时候也会创建出一些相关的数据结构.
  6. 删除索引, drop index 索引名 on 表名;
  7. 删除操作和刚才的创建操作类似都是比较低效的操作.

1.2 索引背后的核心数据结构:

哪些数据结构可以提高查找的效率:

1.哈希表, 增删查改都是O(1)

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

2.二叉树 / 二叉搜索树, 查询速度最差是O(N)

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

如果数据库数据特别多,上面的树就会比较的高 O(logN)

程序猿为了数据库索引量身定做了一个专门的数据结构 B+ 树.

1.2.1 先认识 B 树(N叉搜索树):

B树是一个N叉搜索树.每个节点上可能会包含N-1个值(也可能更少), N-1个值就把区间划分成了N份.这样分成N个叉的意义就是表示同样的数据集合的时候,比二叉树的高度要小很多,IO次数就降低了不少!

1.2.2 再认识 B+ 树(N叉搜索树):

B 树 B+ 树
B树每个节点N-1个值,就分出了N个区间 B+树N个值分成N个区间
B树中的值不会重复出现 B+树是可能重复出现的 (父元素的值会在子元素中以最大值/最小值的姿态出现)
叶子节点这里,B+树会把所有的叶子节点以链表的形式首尾相连,这个时候非常便于范围查找
正因为叶子节点是全集数据,只需要把每一行(每一条记录的完整的所有列关联到叶子节点上即可);非叶子节点只需要保存索引列(只存个id);
非叶子节点占用空间非常小(相比于完整的数据集合),就可以在内存中缓存.因此这个时候查询就又进一步的减少硬盘IO.

2.事务:

  1. 事务就是用来保证原子性的.
  2. 原子性: 原子是不可分割的最小单位,使用原子来表示不能分割的基本单位.
  3. 数据库里面也有一些操作希望可以按照原子的方式来执行,这种情况下就可以使用"事务"来实现
  4. 类似于转账操作就需要按照原子的方式来完成,要么执行全都执行完,要么都不执行(这里说的不执行不是真的没执行,而是执行一半如果出现问题可以自动的恢复如初)
  5. 事务就能保证,当执行过程中出现问题的时候,自动的把前面的SQL执行的效果进行还原,恢复如初,这个操作叫做回滚(rollback);
  6. 事务执行的过程中, MySQL会记录每一步都执行了啥,一旦出现问题就可以根据记录来回滚.
  7. 既然可以回档, 为什么没有撤回呢? 为了实现事务, 其实需要付出很大的代价! 如果想要实现撤回的话, 意味着每一步都要付出这些代价. 撤回操作不是实现不了, 而是代价太大了, 不划算!
  8. 事务最核心的就是原子性, 事务的开启/提交/回滚,一般都是通过代码来控制的.
  9. 四个特性:
4个特性 解释
原子性 这就是事务存在的意义!, 能够把多个SQL打包成一个整体,要么全都执行完,要么一个都不执行(如果执行过程中出错,则自动回滚)
一致性 事务执行前后,数据处在一致的状态, (数据能够对的上)
持久性 事务进行的改动都是写到硬盘上的,不会随着程序重启/主机重启而丢失
隔离性 多个事务并发执行的时候,事务之间能够保持"隔离",不会相互干扰

2.1 隔离性:

  1. 并发执行, 简单的理解就是同时做很多件事情.并发执行事务可能存在问题,就需要隔离性.
  2. 隔离性存在的意义就是让并发执行事务的过程中,尽量不出问题(问题在可控范围之内)

2.1.1 脏读问题:

  1. 想象一个场景, 室友问我要作业,我把修改之前的作业发给他, 他用了之后,我把作业给改了.
  2. 上述就是一个脏读问题, 脏读数据就是一个临时的数据, 不代表最终的结果.
  3. 脏读: 一个事务A在修改数据,提交之前,另外一个事务B读取了数据,此时A极有可能在提交的时候把数据给改了.此时事务B读到的就是"无效的数据"就称为脏读, 读到了脏数据.
  4. 如何解决脏读问题: 结合上述场景,我就和室友约定好, 等我作业写好了再来找我要.在我写好之前,你们不要问我要! 这个操作就相当于是对 写操作加锁!
  5. 写加锁之前, 我的写操作和室友的读操作,就是完全并发的,此时并发是最高的,隔离性是最低的!
  6. 写加锁之后,我写作业的时候,室友就不能问我要,并发性降低了, 但是隔离性提高了!
  7. 但是这又引入了新的问题, 不可重复读!

2.1.2 不可重复读问题:

  1. 概念: 在一个事务A中,多次读取同一个数据发现不一样!!! (读的过程中数据被人修改了)
  2. 想象一个场景, 由于约定过写加锁, 室友在看我作业的时候,我又有了新的想法就把作业又给改了, 这个时候我再次发给室友, 他们就发现作业变了! , 这个过程就是不可重复读的问题.
  3. 不可重复读需要使用读加锁来解决, 我和室友约定我写作业的时候,你们不要问我要; 同时室友看我作业的时候, 我也不要去改.
  4. 随着引入读加锁,并发程度又进一步的降低了(效率降低),隔离性又提高了(数据准确性也提高了).

2.1.3 幻读问题:

  1. 想象一个场景, 刚刚和室友约定了写加锁和读加锁, 我还是闲不住, 室友读取文件A的时候, 我去修改文件B/新增删除文件…,只要不影响到大家正在读的那个数据就好了呀!(我是这么想的)
  2. 这样做虽然同学们直接读取的数据没有影响, 但是同学们会发现,俩次读虽然关系的数据一样但是结果集变了.(第一次大家只能看到一个.java文化,现在看到了俩个.java文件)
  3. 上面这种情况称之为幻读问题, 可以看成是不可重复读的特殊情况.
  4. 为了解决幻读问题, 我和室友约定好,他们读数据的时候,我就得关上电脑就要去摸鱼,作业一点都不能碰!
  5. 此时并发程度最低了(串行执行的了)效率是最低的, 隔离性是最高的,数据的准确性最高!

2.1.4 总结:

  1. 上述的脏读问题,不可重复读问题,幻读问题. 都是在并发执行事务中, 可能带来的影响.产生这些影响不一定是bug.
  2. 如果需求对于数据精度要求不高,上述问题就不是bug,因此就可以让并发程度高一点,隔离性低一点,提高效率!
  3. 如果需求对于精度要求很高,上述问题就是可能是bug,因此就需要rag并发程度低一点,隔离性高一点,保证数据的可靠性!
  4. 类似于转账,必须要精度很高,效率低一点都没事.
  5. 类似于抖音点赞/投币数,精度要求就不高.

2.1.5 隔离级别:

MySQL提供了隔离级别这个选项,给了四个档位, 让我们根据实际需求来选择不同的档位. 在MySQL的配置文件中 my.ini 进行配置,根据不同的需求场景,就可以分别设置不同的档位了.

选项 说明
read uncommitted 允许读未提交的数据,并发程度最高,隔离性最低,可能存在脏读/不可重复读/幻读问题
read committed 只能读取提交之后的数据, 相当于是写加锁,并发程度降低,隔离性提高,解决了脏读问题
repeatable read (默认) 相当于写加锁和读加锁了, 并发程度再次降低,隔离性再提高,解决了脏读/不可重复读问题
serializable 严格执行串行化, 并发程度最低,隔离性最高,解决了脏读/不可重复读/幻读问题,效率最低
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
1月前
|
SQL 关系型数据库 MySQL
MySQL锁机制:并发控制与事务隔离
本文深入解析了MySQL的锁机制与事务隔离级别,涵盖锁类型、兼容性、死锁处理及性能优化策略,助你掌握高并发场景下的数据库并发控制核心技巧。
|
2月前
|
存储 监控 Oracle
MySQL事务
MySQL事务具有ACID特性,包括原子性、一致性、隔离性和持久性。其默认隔离级别为可重复读,通过MVCC和间隙锁解决幻读问题,确保事务间数据的一致性和并发性。
MySQL事务
|
3月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
3月前
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
114 4
|
5月前
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
20天前
|
关系型数据库 MySQL 数据库
MySql事务以及事务的四大特性
事务是数据库操作的基本单元,具有ACID四大特性:原子性、一致性、隔离性、持久性。它确保数据的正确性与完整性。并发事务可能引发脏读、不可重复读、幻读等问题,数据库通过不同隔离级别(如读未提交、读已提交、可重复读、串行化)加以解决。MySQL默认使用可重复读级别。高隔离级别虽能更好处理并发问题,但会降低性能。
|
7月前
|
关系型数据库 MySQL 数据库
Mysql的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
|
3月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
3月前
|
安全 关系型数据库 MySQL
mysql事务隔离级别
事务隔离级别用于解决脏读、不可重复读和幻读问题。不同级别在安全与性能间权衡,如SERIALIZABLE最安全但性能差,READ_UNCOMMITTED性能高但易导致数据不一致。了解各级别特性有助于合理选择以平衡并发性与数据一致性需求。
151 1
|
4月前
|
存储 关系型数据库 MySQL
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
127 9

推荐镜像

更多