MySQL索引与事务详解

目录

1. 索引

1.1 什么是索引？⭐⭐

1.2 作用

1.3 使用场景

1.4 使用⭐⭐⭐

2. 事务

2.1 什么是事务？⭐⭐⭐

2.2 使用

---

1. 索引

1.1 什么是索引？⭐⭐

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引， 并指定索引的类型，各类索引有各自的数据结构实现（B+树实现）。

1.2 作用

• 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
• 索引所起的作用类似书籍目录，可用于快速定位、检索数据。
• 索引对于提高数据库的性能有很大的帮助。

1.3 使用场景

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

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。

反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

1. 对于插入、删除数据频率高的表，不适用索引
2. 对于某列修改频率高的，该列不适用索引
3. 通过某列或某几列的条件查询频率高的，可以对这些列创建索引

1.4 使用⭐⭐⭐

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

• 查看索引

show index from 表名 ;

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

show index from student ;

• 创建索引

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

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

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

create index idx_classes_name on classes(name) ;

• 删除索引

drop index 索引名 on 表名 ;

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

drop index idx_classes_name on classes ;

2. 事务

2.1 什么是事务？⭐⭐⭐

事务：指逻辑上的一组操作。就是把多种操作打包成一起，使其具有原子性，要么全成功，要么全失败（利用回滚机制完成）

事务的四个核心特征：

• 原子性：把一组操作打包在一起，要么全都做完，要么一个都不做。
• 一致性：执行事务之前，和执行事务完之后，当前表里面的数据都是合理的状态。
• 持久性：事务操作的数据都是直接操作硬盘/磁盘，硬盘的数据都是持久化存储。
• 隔离性：多个事务，并发执行时产生的问题。

2.2 使用

（1）开启事务：start transaction ;

（2）执行多条SQL语句

（3）回滚或提交：rollback/commit ;

说明：rollback即是全部失败，commit即是全部成功。

案例：

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);

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