数据库优化

1. 事务

在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一 条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。

简而言之：事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体 一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。

事务是指一组操作的集合，保证这些操作要么全部成功，要么全部失败，从而确保数据一致性。

1.1 事务特性 (ACID)

1. 原子性：事务的所有操作要么全部执行，要么全部回滚。
2. 一致性：事务执行前后，数据必须处于一致状态。
3. 隔离性：一个事务的操作不会受到其他事务的干扰。
4. 持久性：事务一旦提交，结果永久保存。

1.2 事务操作

MySQL 默认是自动提交事务，可以手动控制事务的提交和回滚：

（1）自动提交事务：即执行一条sql语句提交一次事务。（默认MySQL的事务是自动提交）

（2）手动提交事务：先开启，再提交

START TRANSACTION;  -- 开启事务
DELETE FROM tb_dept WHERE id = 1;  -- 删除部门
DELETE FROM tb_emp WHERE dept_id = 1;  -- 删除该部门的员工
COMMIT;  -- 提交事务
ROLLBACK;  -- 回滚事务

2. 索引

索引是一种数据结构，用于提高查询速度。MySQL 常用的索引结构为 B+Tree。

2.1 索引的优缺点

• 优点：
  1. 提高查询效率，减少 IO 操作。
  2. 对数据进行排序，提高排序性能。
• 缺点：
  1. 占用存储空间。
  2. 插入、更新、删除操作的效率会降低。

2.2 创建索引

可以在创建表时定义索引，也可以在表创建后添加索引：

CREATE INDEX idx_emp_name ON tb_emp(name);  -- 为员工表的 name 字段创建索引

create [ unique ] index 索引名 on 表名 (字段名,... ) ;

在创建表时，如果添加了主键和唯一约束，就会默认创建：主键索引、唯一约束

2.3 查看索引

SHOW INDEX FROM tb_emp;

show index from 表名;

2.4 删除索引

DROP INDEX idx_emp_name ON tb_emp;

drop index 索引名 on 表名;

注意事项：

（1）主键字段，在建表时，会自动创建主键索引

（2）添加唯一约束时，数据库实际上会添加唯一索引

通过合理使用索引和事务，可以显著提高数据库的查询性能，并确保数据操作的完整性与一致性。