MySQL学习Day06（狂神说Java）

6.事务

6.1 什么是事务

要么都成功，要么都失败

---

1. SQL执行 A给B转钱 A 1000-> 200 B 200
2. SQL执行 B收到A的钱 A 800-> B 400

---

将一组SQL放在一个批次中执行

事务原则：原子性，一致性，持久性，隔离性

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后的数据完整性要保证一致

持久性（Durability）

事务一旦提交则不可逆，被持久化到数据库中

隔离性（Isolation）

事务的隔离性是多个用户在并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离

隔离所导致的一些问题

脏读：

指一个事务读取了另外一个事务未提交的数据

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同（这个不一定是错误，只是某些场合不对）

虚读（幻读）：

是指一个事务内读取到了别的事务插入的数据，导致前后读取不一致

执行事务

 -- ==============事务========================
 
-- mysql 是默认开启事务自动提交的
SET autocommit =0 /*关闭*/ 
SET autocommit =1 /*开启事务*/ 

-- 手动处理事务
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

 INSERT XX
 INSERT XX
 -- 提交：持久化（成功！)
 COMMIT
 -- 回滚 回到原来的样子（失败！）
 ROLLBACK
 -- 事务结束
 SET autocommit=1  -- 开启自动提交
 
 SAVEPOINT 保存点名-- 设置一个事务的保存点
 
 ROLLBACK TO SAVEPOINT 保存点名-- 回滚到保存点
 RELEASE SAVEPOINT 保存点名 -- 撤销保存点

模拟场景

 --  转账
 CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
 USE shop
 CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30)  NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
 INSERT INTO account(`name`,`money`)
 VALUES('a',2000),('b',10000)
 
 -- 模拟转账：事务
 SET autocommit=1;-- 关闭自动提交
 START TRANSACTION-- 开启一个事务（一组事务—）
 UPDATE account SET money=money-500 WHERE `name`='a'  -- a减500
  UPDATE account SET money=money+500 WHERE `name`='b'  -- a加500
  COMMIT;-- 提价事务，就被持久化了
  ROLLBACK;-- 回滚
  SET autocommit=1; -- 恢复默认值

7.索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高校获取数据的数据结构

提起句子主干，就可以得到索引的本质：索引是数据结构

7.1 索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

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

  -- 索引的使用
  -- 1.在创建表的时候给字段增加索引
  -- 2.创建完毕后，增加索引
  -- 显示所有的索引信息
  SHOW INDEX FROM student
  
  -- 增加一个全文索引
  ALTER TABLE school.student ADD FULLTEXT  INDEX `studentname`(`studentname`);
  
  -- explain分析sql执行的情况
  EXPLAIN SELECT * FROM student;-- 非全文索引
  EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘')：

7.2 测试索引

DELIMITER $$
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data() 

7.3 索引原则

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

索引的数据结构

Hash类型的索引

BTree：InnoDB的默认数据结构