事务和索引

事务

什么是事务

要么成功要么失败

• SQL执行A给B转账
• SQL执行B收到A转账
• 两条语句要么都成功，要么都失败

事务原则

事务原则：ACID原则，原子性、一致性、隔离性、持久性、（脏读、幻读……）

原子性（atomicity）

• 要么成功，要么失败

一致性（consistency）

• 事务前后得数据完整性要保持一致

持久性（durability）

• 事务一但提交则不可逆，比持久化到数据库中

隔离性（isolation）

• 事务得隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务数据所干预，事务之间要相互隔离
• 隔离所导致的一些问题
  • 脏读：指一个事务提取了另一个事务未提交的数据
  • 虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

执行事务

-- ============事务=============
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启（默认的）

-- 手动处理事务
-- 先关闭自动提交
SET autocommit = 0
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql 都在同一个事务内
INSERT xx
INSERT xx  -- 要么同时成功，要么同时失败

-- 提交：成功了就持久化到数据库
COMMIT
-- 回滚：失败了就回到初始样子
ROLLBACK
-- 事务结束
 SET autocommit = 1  -- 开启自动提交
 SAVEPOINT -- 设置一个事务的保存点(存档)
 ROLLBACK  TO SAVEPOINT -- 回滚到保存点(读档)
 RELEASE SAVEPOINT -- 撤销保存点（删档）

模拟场景

-- ========模拟转账==========
-- 创建一个shop数据库
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci
-- 使用数据库
USE `shop`
-- 创建一张account表
CREATE TABLE `account`(	
`id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '账户id',	
`name` VARCHAR(10) NOT NULL COMMENT '账户名字',	
`money` DECIMAL(10,2) NOT NULL COMMENT '账户余额',	
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO `account`(`name`,`money`)
VALUES('A','2000.00'),('B','10000.00')
-- 模拟转账:事务
-- 关闭事务自动提交
SET autocommit = 0;
-- 开启一个事务
START TRANSACTION
UPDATE `account` SET `money`=`money`-500 WHERE `name`='A' -- A-500
UPDATE `account` SET `money`=`money`+500 WHERE `name`='B' -- B+500
COMMIT; -- 提交事务，成功持久化到数据库
ROLLBACK; -- 回滚，回到原始的样子
-- 开启默认的自动提交
SET autocommit=1;

索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL搞笑的获取数据的数据结构提取句子主干，就可以得到索引的本质：索引是数据结构

索引的分类

在一个表中，逐渐索引只能有一个，唯一索引可以有多个

• 主键索引（PRIMARY KEY）
  • 唯一标识，主键不可重复，只能由一个列作为主键
• 唯一索引（UNIQUEKEY）
  • 避免重复列出现，唯一索引可以重复，多个列都可以标识唯一索引
• 常规索引（KEY/INDEX)
  • 默认的index/key关键字来设置
• 全文索引（FullText）
  • 在特定的数据库引擎下才有
  • 快速定位数据

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM 表名
-- 添加索引
CREATE INDEX[需要添加的索引类型] 索引名 ON 表名(字段)
-- 增加一个全文索引
ALTER TABLE 表名 ADD INDEX[需要添加的索引类型] 索引名(列名)
-- 删除索引
DROP INDEX 索引名 ON 表名
-- 删除主键索引
ALTER TABLE 表名 DROP primary key
-- explain 分信息sql执行的状况

测试索引

索引在小数据量的时候，用处不大，但在大数据量的时候区别十分明显

• 索引不是越多越好
• 不要对进程变动数据添加索引
• 小数据量表不需要添加索引
• 索引一般假在常用来查询的字段上