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

【MySQL】索引事务

news 来源：原创 2025/8/25 3:20:37

索引

1.索引概念

数据库使用select查询需要将表中所有的数据都遍历一遍筛选出满足where条件的，如果表中数据非常多，遍历一遍就需要O(n)复杂度是非常高的，为此就引入了索引的概念。

索引是查询的优化手段，避免对表进行遍历，但是它会降低插入、删除、修改的速度并且占用空间。

2.索引的操作

1.创建索引

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

一个索引是针对一个列来指定的，只有针对这一列进行条件查询的时候，查询才能更快
如果某一字段（列）被约束【主键、unique、外键】，那么就不需要再创建索引了，系统会主动创建被约束的那列的索引
创建索引时，需要对数据将进行整理，如果在该表中存在巨大的数据，那么创建索引会导致数据服务器卡住，数据可能就会产生丢失。如果想要修改索引，最好就是在另一个电脑上创建同样的表，把表上的索引创建好，再把之前的数据导入另一个电脑的mysql服务器上。（用新的数据库替换旧的数据库）

2.查看索引

show index from 表名;

约束条件产生的索引：如果是主键，那么索引名就是prinmary；如果是unique唯一或者外键，那么该字段的索引名与字段名相同；

3.删除索引

drop index 索引名 on 表名;

自己create创建的索引是可以随时删除的，但是系统创建的索引是不能删除的。

3.索引的内部逻辑

索引也是通过数据结果实现的。一个二叉树搜索树查询的复杂度是O(N)，；哈希表查询是的复杂度是O(1)；但是这些都不适合数据库作为索引，哈希表是进行精准匹配的，不能进行范围查询，更不能模糊匹配所以不适合。

如果二叉搜索树中引入平衡机制即AVL树、红黑树，这个是可以精准匹配、范围查询、模糊匹配的，但是它的查询复杂度还是有点高O(logN)；

索引内部是一个改进的树形结构B+树（又称N叉搜索树）【它与二叉搜索树相比：树的高度减少】。

B+树的是一个N叉搜索树，一个节点存在n个key，就可以划分n个区间
每个节点上n个key，最后一个就相当于前子树的最大值
父结点的每个key会以最大值的身份在子节点中存在（key可能会重复出现）
B+树使用链表把叶子节点串起来

B+树的优点

N叉搜索树，树的高度有限，降低IO次数
非常擅长范围查询
所有查询都落在叶子节点，查询和查询之间时间开销是稳定的
由于叶子结点是全集，行数据只存储在叶子结点上，非叶子节点只存储一个用来排序的key

事务

1.事务的概念

在 MySQL 里，事务是由一组 SQL 语句构成的一个逻辑工作单元，这组语句要么全部成功执行，要么全部不执行。比如，在一个银行转账事务里，从账户 A 向账户 B 转账 100 元，这包含了从账户 A 扣除 100 元与向账户 B 增加 100 元两个操作。只有当这两个操作都成功执行，事务才算成功；若其中任何一个操作失败，整个事务就会回滚，账户 A 和账户 B 的余额都不会发生改变。

2.事务的操作

1.开启事务：start transaction

2.事务结束：commit

3.主动触发回滚：rollback

单独执行每个sql，都自成一个体系，此时sql之间就没有原子性；rollback一般要搭配一些条件判断逻辑使用的，sql也能支持条件、循环、变量、函数，更多是搭配其他语言。

回滚是怎么做到的？

通过日志的方式记录事务中关键操作（打印出来的内容存在文件里，即使主机掉电也不会影响，一旦重新启动主机，mysql也重新启动，就会发现回滚日志中一些需要进行回滚的操作，于是就可以完成回滚了）

3.事务的特性

事务能够保证数据库操作的原子性、一致性、隔离性和持久性，也就是通常所说的 ACID 特性。以下是对这些特性的详细说明：

原子性：原子性指的是事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败回滚。
一致性：一致性意味着事务执行前后，数据库要从一个一致性状态转变到另一个一致性状态。一致性和原子性是密切相关的，例如在上述转账事务中，无论事务执行成功与否，账户 A 和账户 B 的总金额都应该保持不变。
隔离性：隔离性是指多个事务并发执行时，一个事务的执行不能被其他事务干扰。各个事务之间是相互隔离的，就像在单独执行一样。“数据正确”和“效率”之间的权衡。
持久性：持久性表示事务一旦提交，它对数据库中数据的改变就是永久性的，即便数据库发生故障也不会丢失。通常，数据库通过将事务日志写入磁盘来保证持久性，当数据库崩溃后，可以利用事务日志来恢复已经提交的事务。

并发执行：mysql是一个客户端服务器结构的程序，一个服务器可以给多个客户端提供服务，多个客户端都会让数据库执行事务。

客户端1提交事务1，执行一半，客户端2提交事务2，数据库就需要同时处理这两个事务。如果希望数据库服务器执行效率高，就希望提高并发程度，但是提高并发程度后可能会存在一些问题，导致数据出现“错误”。

“脏读问题”：事务A正在写数据的过程中，事务B读取同一个数据，但是事务A修改了数据，事务B还是之前的数据，此时事务B读到的数据就是一个无效的、过时的数据，就称为“脏数据”；

解决方法：（“写枷锁” ）让事务A先写，确定写完后提交代码到码云后，事务B再写，此时A再写过程中B什么不能写。导致并发执行的效率降低，但是数据靠谱。

“不可重复读”：事务A在内部多次读取同一个数据出现不同的情况（也就是事务A在读的过程中，事务B在修改数据并提交了事务）；

解决方法：（“读枷锁“）事务A在读的过程中，事务B不能修改，也就是事务A读的时候，事务B对数据什么都不干。导致并发执行有降低，但是隔离性（两个事物的影响程度）提交，准确性提高。

“幻读”：事务A执行过程中，两次读取数据虽然没有改变，但是查询结果的集合改变了；

解决方法：引入串行化方式，保持绝对串行执行事务，此时完全没有并发执行。也就是并发程度最低，隔离性最高，效率最低，数据最准确。

隔离级别

脏读：

1.read uncommitted（读未提交）并发执行最高、速度最快、隔离性最低、准确性最低

2.read committed（读已提交）引入了“读枷锁”，只能读写完后提交，并发执行降低、速度降低、准确性提交

不可重复读：

repeatable read（可重复读）引入了“写枷锁”和“读枷锁”，写的时候不能读，读的时候不能写，并发执行进一步降低，速度也降低，隔离性提高，准确性提高

幻读：

serializable（串行化）一个一个执行事务，并发执行最低，速度最低，隔离性最高，准确性最高

索引