当前位置：首页 > news >正文

谈谈MySQL中的索引和事务

news 来源：原创 2025/7/26 8:52:43

1. 索引

1.1 索引介绍

1.2 缺陷

1.3 使用

1.3.1 查看索引

1.3.2 创建索引

1.3.3 删除索引

2. 索引底层的数据结构

2.1 B+树

3. 事务

3.1 为什么使用事务

3.2 事务的使用

3.3 事务的基本特性

1. 索引

1.1 索引介绍

索引相当于一本书的目录(index), 在一个表里有很多数据, 在查询表的时候, 最基本的方式就是遍历表,一条一条地进行筛选. 因此,就可以给这个表,来建立索引来提高查找的速度, 而索引是以"列"为维度进行建立的.

表中有很多列, 查询的时候, 查的方式也有区别. 有的时候, 是按照id来查询. 有的时候, 是按照name来查. 还有的时候, 是按照邮箱来查.

比如，按照id这一列,建立索引. 就会在数据库上额外搞了一个空间, 用来维护一些和id相关的信息. 这里就会通过一些特定的数据结构, 维护表示id相关的索引的情况.

后续再按照id来查询, 就不必直接遍历了. 而是从索引中进行查询, 根据索引就能够初步的锁定出数据所在的位置.

如果按照id来建立索引了，接下来按照name来查询, 能够提高效率嘛? 答: 不可以的!!

因为索引中只包含了id相关的信息, 要想让name加快查找, 就需要给name 也建立一个索引.

1.2 缺陷

索引，是用来提高查询效率的., 但是也有其缺陷.
缺陷:
1. 会消耗额外的空间
2. 有可能会拖慢,增删改的速度.
比如新增, 不光要往表里写数据, 同时还好修改索引数据对应的索引.
再比如删除, 修改的, 如果删除修改的条件, 正好是和索引匹配还能快点.
但是如果是涉及到索引列的删除/修改, 这个时候也需要同时维护索引.

对于数据库来说, 数据都是存储在硬盘上的. 同样索引数据也是在硬盘上的.
所以说, 任何的交换都是有代价的. 不过上述这样的交换, 是赚的. 因为, 在实际的应用开发中, 是以查询操作为主，增删改相对来说是少很多的. 这样的情况下, 使用索引就非常合适了.

1.3 使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

1.3.1 查看索引

show index from 表名;

当表中存在主键的时候, 内部就会自动的给这个列来创建索引, 主键不允许重复, 因此进行插入或者修改时, 就需要先查询(这个查询就挺费时间的), 看看插入或修改后的结果是否已经存在.

使用unique也会自动的生成索引

设置外键约束,自动生成的索引

外键这里也涉及到自动查询
1. 给学生表中插入一条记录,就需要查询classld是否在class表的classld中存在~~这个查询,就用到了class表的classld (主键自动生成的索引)
2. 给班级表中删除一条记录,就需要查询classld是否在student表中存在
如果存在,这样的删除就会失败(子表对于父表的反向约束)
这个查询,就用到了student表的classld 这一列对应的索引(外键约束自动生成的)

1.3.2 创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名(字段名);

所以应该提前考虑好，设计表的时候,哪些列要有索引, 要明确规划出来.

1.3.3 删除索引

drop index 索引名 on 表名;

删除索引，只能针对手动创建的索引. 自动生成的索引，是不能被删除的.

但是删除索引这个操作也可能会非常的危险.

2. 索引底层的数据结构

mysql的索引的数据结构到底是啥样的数据结构? 并非是定式!! 取决于mysql使用哪个存储引擎.

2.1 B+树

B＋树, 为了数据库量身定做的数据结构.
mysql的索引的数据结构为什么不能是顺序表, 链表, 栈, 队列, 红黑树, 哈希表呢?

1. 顺序表

在顺序表中间插入或删除元素需要移动大量数据，效率较低。
当容量不足时，需要重新分配更大的存储空间，并拷贝所有数据。
在大规模数据中进行随机查询效率低，尤其是当数据需要排序时。

2. 链表

链表不支持随机访问，必须从头开始遍历，查询时间复杂度是 O(n)。
每个节点需要额外的指针存储，内存开销较高。
链表不能快速进行范围查询。

3. 栈和队列

栈和队列只适合用于简单的顺序操作，不支持高效的随机查询、范围查询或复杂操作。
栈和队列不支持快速的插入、删除或排序操作。

4. 红黑树

元素有序，可以处理范围查询. 但最大的问题, 在于红黑树的高度, 会在元素个数比较多的时候，变的比较高.比较高就意味着查询操作时, 比较次数有时就会变得多, 索引这样的结构是存储在硬盘上的, 每一次比较就意味着硬盘IO操作

5. 哈希表

不适合数据库的查询场景, 因为哈希表,只能做这种精确查询,没法做模糊查询和范围查询.

要了解B+树，需要先了解下B树, B树有的时候会写作B-树, '-' 是连接符的意思, 不是数学中的减符号.B树的核心思路, 和"二叉搜索树"差不多.B树本质上是一个N叉搜索树.

一个节点上,可以保存多个key. N个key 就能延伸出N＋1个分叉来. N个key就划分出了N＋1个区间.

B树查询元素的流程:
拿着要查询的元素, 从根节点出发, 判定要查找的元素是否在根节点上存在.
如果不存在,看这个元素是落到哪个区间里, 就沿着这个区间的路线往下一个节点上找. 最终找到叶子节点, 还不存在, 就是真的不存在了.
此时每个节点上，就可以保存多个元素了. 当总的元素个数固定的时候,相比于二叉搜索树, 涉及到的节点的总数就大大降低了, 树的高度也大大降低了, B树的高度是远远小于二叉搜索树的. 于是, 进行查询的时候,硬盘IO的次数也就随之减少了.

对于数据库来说, 每个节点, 都需要把数据从硬盘上读出来才能进行比较.
一个节点上有多个key，和一个节点上有一个key, 硬盘io 的开销是差不多的.

这就与倒垃圾类似. 关键的时间开销, 是从家里到楼下垃圾桶这里的距离, 一次拿一个垃圾袋, 去倒一趟和一次拿四个垃圾袋, 去倒一趟, 时间开销是一样的.
除非是你要拿100个垃圾袋, 一次拿不完, 需要多次, 才会有效率的明显降低.