Mysql索引事务

核動力牛馬

已于 2024-09-25 17:48:04 修改

阅读量975

点赞数 10

分类专栏： Mysql 文章标签： mysql 数据库

于 2024-08-14 17:40:11 首次发布

本文链接：https://blog.csdn.net/m0_63272315/article/details/141193936

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1. 索引

1.1 概念

在MySQL中，索引是一种数据结构（通常是B树或者其变种），它可以帮助数据库系统更快地检索数据。没有索引时，数据库必须从头到尾扫描整个表来查找所需的数据行，这种操作称为全表扫描。当表中的数据量很大时，这种操作会非常耗时。通过使用索引，可以显著提高数据检索的速度。

1.2 作用

1、索引所起的作用类似书籍目录，可用于快速定位、检索数据。

2、索引对于提高数据库的性能有很大的帮助。

1.3 使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

1、数据量较大，且经常对这些列进行条件查询。

2、该数据库表的插入操作，及对这些列的修改操作频率较低。

3、索引会占用额外的磁盘空间。

1.4 使用

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

1、查看索引

show index from 表名;

案例：查看学生表已有的索引

show index from student;

2、创建索引

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

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

案例：创建班级表中，name字段的索引

create index idx_classes_name on classes(name);

3、删除索引

drop index 索引名 on 表名;

案例：删除班级表中name字段的索引

drop index idx_classes_name on classes;

2. 事务

2.1 使用事务的好处

准备测试表：

drop table if exists accout;
create table accout(
 id int primary key auto_increment,
 name varchar(20) comment '账户名称',
 money decimal(11,2) comment '金额'
);
insert into accout(name, money) values
('阿里巴巴', 5000),
('四十大盗', 1000);

select * from  accout;

比如说，四十大盗把从阿里巴巴的账户上偷盗了2000元

-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';

假如在执行上述第一条语句时，出现网络错误，阿里巴巴的账户-2000，但四十大盗的账户并未增加。那么就要使用事务来控制，保证两条SQL语句全部执行成功或者失败。

2.2 事务的概念

在MySQL中，事务是一系列的操作作为一个整体被提交或回滚。这意味着事务中的所有操作要么全部成功完成，要么全部失败并撤销。事务提供了一种机制来保证数据的一致性和完整性，尤其是在需要执行多个相关操作的情况下。

事务具有ACID特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

ACID特性解释：

原子性（Atomicity）:
- 事务是一个不可分割的工作单元。事务中的所有操作要么全部完成，要么一个也不做。
- 如果事务的一部分失败，则整个事务都将被回滚，以保持数据的一致性。
一致性（Consistency）:
- 事务完成后，数据必须处于一致状态。
- 事务应该将数据库从一个一致的状态转换到另一个一致的状态。
隔离性（Isolation）:
- 多个并发事务之间不会相互影响。
- 每个事务都与其他事务隔离，就好像它是系统中唯一的事务一样。
持久性（Durability）:
- 一旦事务完成（提交），它对数据库所做的更改就是永久性的。
- 即使系统出现故障，提交的更改也不会丢失。

2.3 使用

开始事务:

START TRANSACTION;

提交事务:

COMMIT;

提交事务意味着确认所有事务中的更改，并使其对其他用户可见。

回滚事务:

ROLLBACK;

回滚事务撤销了事务中所做的所有更改，将数据库恢复到事务开始之前的状态。

事务示例：

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT; -- 如果一切顺利，提交事务
-- 或者
ROLLBACK; -- 如果出现问题，回滚事务

隔离级别：

MySQL支持四种不同的事务隔离级别，它们按照从低到高的顺序分别是：

READ UNCOMMITTED (未提交读) - 可能出现脏读、不可重复读和幻读。
READ COMMITTED (已提交读) - 避免脏读，但仍可能出现不可重复读和幻读。
REPEATABLE READ (可重复读) - MySQL默认的隔离级别，避免脏读和不可重复读，但可能出现幻读。
SERIALIZABLE (序列化) - 最高的隔离级别，完全避免脏读、不可重复读和幻读，但可能导致更多的锁定。

脏读、不可重复读、幻读

脏读（Dirty Read）

定义:
- 脏读是指一个事务读取到了另一个事务尚未提交的数据。
- 如果随后第一个事务回滚了这些更改，那么第二个事务就会看到无效或不正确的数据。
例子：
假设有一个账户表 accounts，其中用户A的余额为1000元。
事务T1开始，并将用户A的余额更新为2000元（但尚未提交）。
同时，事务T2开始并读取到用户A的余额为2000元。
如果此时事务T1因某种原因回滚，用户A的余额恢复为1000元。
事务T2就相当于读到了不正确的（脏）数据。

不可重复读（Non-Repeatable Read）

定义:
- 不可重复读是指在一个事务内多次读取同一数据时，由于其他事务的并发操作，导致读取的结果不一致。
- 即使在同一个事务中，两次读取同一数据也可能得到不同的结果。
例子：
继续使用上面的账户表 accounts 的例子。
事务T1开始，并读取到用户A的余额为1000元。
在事务T1未提交之前，事务T2将用户A的余额更新为2000元，并提交了这个更改。
当事务T1再次读取用户A的余额时，它会看到新的值2000元，而不是原来的1000元。

幻读（Phantom Read）

定义:
- 幻读是指在一个事务内多次执行相同的查询时，由于其他事务的并发操作，导致查询结果集的行数发生变化。
- 例如，第一次查询返回了N行结果，但在同一个事务中再次执行相同的查询时，返回了N+M行结果，其中M是其他事务插入的新行数。
例子：
假设有一个人口统计表 population，记录着不同年龄的人数。
事务T1开始，并执行了一个查询来获取所有30岁以下的人数，得到了50个人。
在事务T1未提交之前，事务T2向表中添加了几条关于30岁以下的新记录，并提交了这些更改。
当事务T1再次执行相同的查询时，它可能得到比第一次更多的结果，比如说现在有55个人。