MySql 的事务和索引

二廿七
136 阅读5分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

MySql 的事务和索引

MySql 事务

在这里插入图片描述

特性

  • 原子性：任何一个事务都可以想象成一个原子，表示其不可再分。只有事务中所有的数据库操作都执行成功，才算整个事务成功，事务中任何一个sql语句执行失败，已经执行成功的sql语句也必须撤销，数据库状态应该退回到执行事务前的状态。

  • 一致性：指数据库中数据在事务操作前和操作后都必须满足业务规则约束，如 转账的两个账户在该事务前后的总金额是相等的。

  • 隔离性：一个事务所做的修改在最终提交以前，对其他事务是不可见的。

  • 持久性：当事务提交后，所有的变化都是永久的，即使数据库崩溃需要恢复时，也可以保证恢复后的数据都不会丢失。

事务隔离级别

  • 读未提交：指一个事务还没提交时，它做的变更就能被别的事务看到。

  • 读提交：指一个事务提交之后，它做的变更才会被其他事务看到。

  • 可重复读：指一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

  • 串行化：指对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

脏读、不可重复读、幻读、可重复读

  • 脏读：脏读是一个事务读取到了其他事务还没有提交的数据。

  • 不可重复读：指在一个事务中，读到了其他事务针对就数据的修改记录（常见的操作就是 update 或者 delete 语句）。

  • 幻读：指在一个事务中，读取到了其他事务新增的数据，仿佛出现了幻影的现象（常见的操作是insert语句）。

  • 可重复读(加锁读)：是 MySql 默认的事务隔离级别，它消除了脏读、不可重复读、幻读的现象，保证了事务的一致性。

事务隔离级别与（脏读、不可重复读、幻读、可重复读） 的关系

脏读不可重复读幻读可重复读（加锁读）
读未提交（read uncommitted）可能可能可能不可能
读提交（read committed）不可能可能可能不可能
可重复读（repeatable read）不可能不可能可能不可能
串行化（serializable ）不可能不可能不可能可能

怎么写一个事务

  • 显式地开启一个事务
BEGIN
START TRANSACTION
  • 提交事务
-- COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
COMMIT
COMMIT WORK
  • 回滚事务
-- ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
ROLLBACK
ROLLBACK WORK
-- ROLLBACK TO identifier 把事务回滚到标记点；
ROLLBACK TO rollPoint
  • 事务保存点
-- SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
SAVEPOINT rollPoint

-- RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
RELEASE SAVEPOINT rollPoint

  • SET TRANSACTION 用来设置事务的隔离级别。

  • InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MySql 索引

在这里插入图片描述

  • 主键索引和唯一索引 比 普通索引 更快速地查询某条记录。

  • 单张表中索引不建议超过 5 个。

好处

  • 提高查询效率（降低IO使用率）

  • 降低CPU使用率

坏处

  • 降低 增删改 的效率。

  • 索引本身就是一个树形结构（B+树）的数据，很大，需要存放在内存或硬盘中（一般是硬盘）。

分类

  • 唯一索引：是指对某字段创建索引时，限制索引的字段值必须是唯一的，可以为 NULL。

  • 主键索引：是指对某字段创建索引时，限制索引的字段值必须是唯一的，同时不能为 NULL。

  • 字段索引（普通索引）：是指对某字段创建索引，用来标记字段，字段值不唯一，可以为 NULL。

  • 单值索引：是字段索引的一个子类，表示设置普通索引的字段是单个字段。

  • 联合索引（复合索引）：是字段索引的另一个子类，表示设置普通索引的字段是多个（大于等于两个）字段。

怎么创建一个索引

  • UNIQUE INDEX：唯一索引

  • UNIQUE KEY：主键索引

法一（使用 CREATE INDEX 创建）：

CREATE UNIQUE INDEX | KEY [索引名称] ON [表名]([字段名称]([限制的长度]))

法二（使用 ALTER 语句创建）：

ALTER TABLE [表名] ADD UNIQUE INDEX | KEY [索引名称] ([字段名称]([限制的长度])) [ASC|DESC]);

法三（建表时创建）：

UNIQUE INDEX | KEY [索引名称] ON [表名]([字段名称]([限制的长度]))
  • 像创建表字段一样，写在最后一行。如：
CREATE TABLE tablename(
    ...,
    UNIQUE INDEX | KEY [索引名称] ([字段名称]([限制的长度])) [ ASC | DESC ] ) 
);

创建联合索引

  • 将字段改为 多个即可。

  • 创建联合索引时，可不止创建一个。

给 A、B、C 三个字段建立联合索引时，是建立 A、A+B、A+B+C 三个索引的。

avatar
文章
阅读
粉丝