Mysql表的索引，事务（必学）-优快云博客

类型	特点	示例
主键索引	唯一 + 非空，表只能有一个	`PRIMARY KEY (id)`
唯一索引	值必须唯一，允许空值	`UNIQUE KEY (email)`
普通索引	基本索引，可重复加快对数据的访问速度	`INDEX (name)`
组合索引	多列联合索引	`INDEX (city, age)`
全文索引	文本内容搜索（文章关键词）	`FULLTEXT (content)`

在此我们只学习索引的常用类型。

4. 索引的使用

数据准备

--创建一个学生表
create table student{
    
    id int,
    name varchar(20),
    hobby varchar(30)
    
};

1.主键索引

特点：主键是一种唯一性索引,每个表只能有一个主键,用于标识数据表中的某一条记录，

并且主键值不能包含NULL。

语法格式：字段名类型 PRIMARY KEY（建表时直接创立）

ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )（建表后修改表结构时再创立）

为学生表添加主键索引

alter table student add primary key (id);

2.唯一索引

特点：索引列的所有值都只能出现一次, 必须唯一. 唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

语法格式：

1.创建表时直接添加唯一索引

        unique [索引名称] 列名

2.使用create语句在已有的表上创建索引

                create unique index 索引名 on 表名(列名(长度))

3.修改表结构添加索引

                ALTER TABLE 表名 ADD UNIQUE ( 列名 )

为hobby字段添加唯一索引

create unique index ind_hobby on student(hobby);

插入数据进行唯一性测试

insert into student values(1,'xiaoming','爱你');
insert into student values(2,'小张','爱你');

此时数据会报错，第二个hobby不能与第一个重复.

3.普通索引

特点：普通索引是数据库中最基本的一种索引类型。它没有任何限制，可以在表的任意列上创建。普通索引的主要作用是加快数据的检索速度，提高查询效率。

语法格式：

1.建表时创建索引

          index 索引名（列名（长度））;

2.使用create index在已有表上创建索引

                create index 索引名 on 表名(列名[长度])

3.修改表结构时添加索引

                ALTER TABLE 表名 ADD INDEX 索引名 (列名)

给name字段添加索引

alter table student add index ind_name(name);

4.查看并删除索引

查看索引：show index from 表名;

删除索引：

                  1.直接删除

                                drop index 索引名 on 表名;

                  2.修改表结构删除

                                ALTER TABLE 表名 DROP INDEX 索引名;

查看表中所有索引，并删除name索引

--查看表中索引
show index from student;

--删除索引name
 drop index ind_name on student;

5. 索引的使用原则总结

-在WHERE、JOIN、ORDER BY、GROUP BY中频繁出现的列上创建索引。

- 选择区分度高的列（如身份证号比性别更适合建索引）。

- 避免过度索引（索引会占用空间，降低写操作速度）。

-使用组合索引时，遵循“最左前缀原则”。

6. 索引的优缺点

添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。

索引的优点

1. 大大的提高查询速度

2. 可以显著的减少查询中分组和排序的时间。

索引的缺点

1. 创建索引和维护索引需要时间，而且数据量越大时间越长

2. 当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

索引是需要再磁盘占用物理空间的，所以在使用完索引后，可以删除掉不在使用的索引。

二.事务

1.什么是事务

事务是一个整体，由一条或者多条sql语句组成，这些sql语句要么执行成功，要么执行失败，只要有一条sql出现异常，整个操作就会回滚，整个业务执行失败。

回滚：即在事务运行的过程中发生某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）

2.为什么使用事务

比如: 银行的转账业务,小张向小明转账500元 , 至少要操作两次数据库, 小张-500, 小明+ 500,小张的账户余额扣掉500，小明的账户余额没有增加500，导致账户转账错误，这个时候怎么办？事务的出现就避免了这类问题的发生。当操作出现异常的时候在事务的机制作用下就会出现回滚，所有的操作都会撤销，上面小张的转账操作就会失效，500会退还到小张的转账中，直到问题被解决，才能转账成功。

3.Mysql事务的操作

事务提交操作分为手动提交操作和自动提交操作

此处我们学习重点学习手动提交事务

Mysql默认为自动提交事务，设置为手动提交事务

登录mysql,查看autocommit状态

语法格式：

准备数据

create table accout(
id int primary key auto_increment,
name varchar(20) comment '账户名称',
balance decimal(11,2) comment '金额'
);
insert into accout(name, balance) values
('小张', 5000),
('小明', 1000);

小明向小张借款5000元

手动提交

正确操作：

--开启事务
start transaction;

update accout set balance=balance-2000 where name = '小张';
update accout set balance=balance+2000 where name = '小明';

--提交事务
commit;

异常操作：

--开启事务
start transaction;

update accout set balance=balance-2000 where name = '小张';
update accout set balance=money+2000 where name = '小明';

--提交事务
commit;

此时就会抛出语法异常操作。

自动提交：

把上面的autocommit状态该为on就变成自动的呢

自动是不需要开启事务和提交事务操作的，每当执行一个sql语句在sql中就算一个事务。

4.事务的四大特性

特性	含义	示例说明
原子性	每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败。	银行转账：A 转 100 给 B，必须同时完成扣款和收款操作，否则全部撤销
一致性	事务在执行前数据库的状态与执行后数据库的状态保持一致	转账前 A+B 总金额 2000，转账后 A+B 总金额仍是 2000（无论转账是否成功）
隔离性	事务与事务之间不应该相互影响，执行时保持隔离的状态	用户 A 转账时，用户 B 查余额不受影响；多个事务并发执行如同顺序执行
持久性	一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的	转账成功提交后，即使服务器断电重启，转账记录和余额变更仍然有效

三.数据库中的常见问题和解决方法

数据准备

create table accout(
id int primary key auto_increment,
name varchar(20) comment '账户名称',
balance decimal(11,2) comment '金额'
);
insert into accout(name, balance) values
('小张', 5000),
('小明', 1000);

1.脏读问题

描述：一个事务读取了另一个事务未提交的数据。

举例：事务A修改了数据但未提交，事务B读取了这个未提交的数据。如果事务A回滚，则事务B读取到的数据就是无效的（脏数据）。

问题描述：事务A读取了事务B未提交的数据，若事务B回滚，则事务A读到的是无效的"脏数据"。

场景模拟：

-- 事务B（修改数据但未提交）
START TRANSACTION;
UPDATE accout SET balance = 500 WHERE id = 1;  -- 余额从1000→500（未提交）

-- 事务A（读取未提交数据）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- 设置读未提交隔离级别
START TRANSACTION;
SELECT balance FROM accout WHERE id = 1;  -- 读到500（脏数据！）

-- 事务B回滚
ROLLBACK;  -- 余额恢复为1000

-- 事务A误认为余额是500（实际应为1000）

解决方法：
使用 READ COMMITTED 隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2.不可重复读问题

问题描述：同一事务内两次读取相同数据，结果不一致（因其他事务修改并提交了数据）。

场景模拟：

-- 事务A（第一次读取）
START TRANSACTION;
SELECT balance FROM accout WHERE id = 1;  -- 返回1000

          -- 事务B修改并提交
          UPDATE users SET balance = 800 WHERE id = 1;
          COMMIT;

-- 事务A（第二次读取）
SELECT balance FROM accout WHERE id = 1;  -- 返回800（结果改变！）
COMMIT;

解决方法：
使用 REPEATABLE READ 隔离级别（MySQL默认）：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

原理：MySQL通过MVCC（多版本并发控制）提供事务开始时的一致性快照。

3.幻读问题

问题描述：同一事务内两次查询相同条件，返回的行数不同（因其他事务新增/删除了数据）。

场景模拟：

-- 事务A（第一次查询）
START TRANSACTION;
SELECT COUNT(*) FROM accout WHERE age > 30;  -- 返回10人

          -- 事务B插入新数据并提交
          INSERT INTO accout(name, age) VALUES ('老王', 35);
          COMMIT;

-- 事务A（第二次查询）
SELECT COUNT(*) FROM accout WHERE age > 30;  -- 返回11人（多出1行！）
COMMIT;

解决方法：
方案1：使用 SERIALIZABLE 隔离级别（强制串行执行）
方案2：在 REPEATABLE READ 下使用间隙锁（Gap Locks）：

-- 显式加锁
SELECT * FROM accout WHERE age > 30 FOR UPDATE;

隔离级别解决方案总表

隔离级别	脏读	不可重复读	幻读	实现机制
READ UNCOMMITTED	❌	❌	❌	无锁读取
READ COMMITTED	✅	❌	❌	快照读（每次新快照）
REPEATABLE READ	✅	✅	△¹	MVCC+间隙锁（默认）
SERIALIZABLE	✅	✅	✅	完全串行执行