索引和事务

一、索引

1.1 概念

索引是一种特殊的文件，包含对数据表所有记录的引用指针。在MySQL中，索引是基于一个或多个列的数据结构，是一种数据库对象，它们存储有序的键值对，其中键是列的值，而值是指向表中数据行的物理位置。可以对表中的一列或多列创建索引，并指定索引类型，各类索引有各自的数据结构实现。

1.2 作用

• 数据库中的表、数据、索引之间的关系类似于书架上的图书、书籍内容、书籍目录的关系。
• 索引可用于快速定位，检索数据，加快查询的速度，避免对表进行遍历，是对查询操作的优化手段
  
  索引能提高查询的速度，但也会有一定的代价
  1. 占用更多的空间，生成索引，需要一系列的数据结构和额外的数据，来存储到硬盘空间中
  2. 可能会降低插入、更新和删除操作的速度

1.3 使用场景

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

1. 数据量较大，经常对列进行条件查询
2. 该表的插入和对这些列的修改操作频率较低
3. 索引会占用额外的磁盘空间
   满足上述条件，考虑对表中这些字段创建索引，来提高查询效率，但如果不是条件查询列，或者经常使用插入、修改操作，或者磁盘空间不足时，不考虑创建索引

1.4 使用

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

• 查看索引

show index from 表名;

-- 查看学生表已有的索引
show index from student;
1
2

• 创建索引
  要创建索引的列是非主键、非唯一约束、非外键的字段

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

-- 创建课程表中name 字段的索引
create index idx_course_name on classes(name);
1
2

• 删除索引
  自动创建的索引不能删除

drop index 索引名 on 表名;

-- 删除课程表中name字段的索引
drop index idx_course_name on course;
1
2

拓展：

索引保存的数据结构主要为B+树（N叉搜索树），及hash的方式。

在查询的时候，直接从根节点出发，判断当前要查的数据在节点上的那个区间中，决定下一步向哪走，进行添加和删除元素可能涉及节点的拆分和合并

二、事务

2.1 为什么要使用事务

-- 创建一个银行账户的表
create table accout(
	id int primary key auto_increment,
	name varchar(20) comment '账户名称',
	money decimal(11,2) comment '金额'
);
-- 插入数据
insert into accout(name, money) values('张三',10000),('李四',10000);
1
2
3
4
5
6
7
8

现在要实现张三向李四转账1000元

-- 张三账户减少1000
update accout set money=money-1000 where name = '张三';
-- 李四账户增加1000
update accout set money=money+1000 where name = '李四';
1
2
3
4

如果在执行第一个update 语句时，程序或者数据库崩溃了，或者是机器断电了，张三帐户会减少1000，但李四账户没有增加1000金额，此时就需要使用事务来控制，保证两条update语句要么都执行成功，要么都执行失败。

2.2 事务的概念

事务是指逻辑上的一组操作，组成这个操作的单元，要么全部成功，要么全部失。在不同环境中，都可以有事务，而对应在数据库中的，就是数据库事务。
以2.1 例子来说，事务，就是将这要实现的业务操作进行打包成一个整体，这个整体中的操作要么都成功，要么都失败（需要返回事务开始前的状态，即回滚），保证数据的一致性和完整性

2.3 事务的使用

1. 开启事务：start transaction;
2. 执行多条SQL语句
3. 回滚或提交：rollback / commit

开启事务后，单独执行的每个 sql 都是独立的，这些sql 之间没有原子性‘，提交事务是使用 commit 将事务的操作持久化保存到数据库中。
当出现错误或异常情况时，可以使用 rollback 语句撤销事务中的所有更改。也就是说当执行 rollback 时，MySQL会撤销所有未提交的事务并回滚到事务开始之前的状态，回滚只适用于尚未提交的事务，一旦事务被提交，就无法再进行回滚操作。

拓展：

三、内容重点总结

• 索引：
  索引是基于一个或多个列的数据结构，是一种数据库对象，它们存储有序的键值对，其中键是列的值，而值是指向表中数据行的物理位置。
  1. 插入和删除数据频繁的表，不适用索引
  2. 对某列修改频率高的，该列不适合用索引
  3. 某列或某些列条件查询频率高的，可以对这些列创建索引

查看索引 show index on 表名

创建索引 create index 索引名 on 表名(列名)

删除索引 drop index 索引名 on 表名

• 事务：
  事务是指逻辑上的一组操作，组成这个操作的单元，要么全部成功，要么全部失败

开启事务 start transaction

执行多条SQL语句

提交或回滚事务 commit / rollback