day04——事务和存储引擎

事务

事务处理可以用来维护数据库的完整性，多条的 SQL 语句要么全部执行，要么全部不执行。

事务举例：
	张三给李四转账500元钱，需要让三账户的余额-500，李四的账户余额+500，转账是一个连续的过程，要么同时成功，要么同时失败。不能出现张三转出了500块，而李四没收到的情况，这将是很严重的问题。

准备数据

-- 1. 删除数据库,如果存在,则删除
DROP DATABASE IF  EXISTS db7;

-- 2. 创建数据库
CREATE DATABASE IF NOT EXISTS db7 CHARSET utf8;

-- 3. 使用数据库
USE db7;

-- 4. 创建账户表信息
CREATE TABLE IF NOT EXISTS account(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 账户的id
	NAME VARCHAR(20),  -- 账户名称
	money DOUBLE       -- 账户余额
);

-- 5. 添加数据
INSERT INTO account VALUES (NULL,'张三',2000),(NULL,'李四',2000);

-- 6. 查询数据表
SELECT * FROM account;

-- 7. 修改账户信息,都修改成为 2000 元
UPDATE account SET money = 2000;

事务的基本使用

-- 1. 开启事务
START TRANSACTION; 

-- 2. 修改数据表记录,张三的账户,金额减少 100 元
UPDATE account SET money = money - 100 WHERE NAME = '张三';

-- 3. 修改数据表记录,李四的账户,金额增加 100 元
UPDATE account SET money = money + 100 WHERE NAME = '李四';

-- 5. 提交事物
COMMIT;   

-- 6. 查询账户信息
select * from account;

事务的提交方式

1. 事务提交方式分类:
	A. 自动提交 (MySQL 默认的提交方式)
	B. 手动提交 (Oracle 采用的提交方式)

2. 相关SQL语法
	-- 查看事务的提交方式, 说明: 1表示自动提交, 0表示手动提交
	SELECT @@AUTOCOMMIT;
	-- 修改事务的提交方式, 标准语法
	SELECT @@AUTOCOMMIT = 数字;

事务的隔离级别

多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

• 存在的问题

1. 脏读：一个事务，读取到另一个事务中没有提交的数据
2. 不可重复读：在同一个事务中，两次读取到的数据不一样。
3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

序号	隔离级别	名称	脏读	不可重复读	幻读	数据库默认隔离级别
1	READ UNCOMMITTED	读未提交	YES	YES	YES
2	READ COMMITTED	读已提交	NO	YES	YES	Oracle 数据库默认级别
3	REPEATABLE READ	可重复读	NO	NO	YES	MySQL 数据库默认级别
4	SERIALIZABLE	串行化	NO	NO	NO

• 设置隔离级别

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查询隔离级别：
    select @@tx_isolation;
数据库设置隔离级别：
    set global transaction isolation level  级别字符串;

存储引擎

MySQL结构

数据库存储引擎是数据库最核心的部分，不同的存储引擎提供不同的存储机制、索引技巧等。也就是说不同的存储引擎底层对数据的组织方式不一样，从而导致性能和特点也不一样。

MySQL 当中常用的存储引擎：

1. InnoDB : 支持事务, MySQL5.5版本之后, 默认的存储引擎
2. MyISAM : 不支持事务
3. Memory : 速度快,最不安全。小量快速访问的数据

相关SQL操作

查询存储引擎

-- 1. 查询数据库支持的存储引擎。
SHOW  ENGINES;

-- 2. 查询某个数据库当中, 所有数据表的存储引擎
SHOW  TABLE  STATUS  FROM  数据库名称;

-- 3. 查询某个数据库当中,某个数据表的存储引擎
SHOW  TABLE  STATUS  FROM  数据库名称  WHERE  NAME = '数据表的名称';  

创建数据表，指定存储引擎

-- 1. 语法格式
CREATE  TABLE  表名称  (列名  数据类型, 列名 数据类型)  ENGINE = 引擎的名称;

-- 2. 案例代码, 创建数据表的时候,指定存储引擎 MYISAM
CREATE  TABLE  engine_test (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(10))   ENGINE = MYISAM ;

修改数据表，指定存储引擎

-- 1. 语法格式
ALTER  TABLE  表名称  ENGINE = 引擎名称;

-- 2. 案例代码, 修改表的存储引擎是 InnoDB
ALTER  TABLE  engine_test ENGINE = InnoDB;

存储引擎练习

SQL语句准备

-- 1. 创建数据库 db10 如果不存在则创建
CREATE DATABASE IF NOT EXISTS db10;

USE db10;

-- 2. 创建数据表的时候,指定存储引擎 MYISAM
CREATE  TABLE  engine_test (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10))   ENGINE = MYISAM ;

-- 3. 查询存储引擎
SHOW  TABLE  STATUS  FROM  db10;

-- 4. 修改数据表当中的存储引擎
ALTER  TABLE  engine_test  ENGINE = InnoDB;

-- 5. 查询存储引擎
SHOW  TABLE  STATUS  FROM  db10;

效果图

索引

MySQL 的索引是一种数据结构, 主要是用来高效获取数据。

有索引和无索引的情况比较

索引的分类

1. 按照功能分类
	A. 普通索引: 最基本的索引,他没有任何的限制
	B. 唯一索引: 索引列的值必须唯一, 但是允许有空值的情况, 如果是组合索引, 则列值组合必须唯一
	C. 主键索引: 一种特殊的唯一索引, 不允许有空值, 一般在建表时, 同时创建主键索引
	D. 组合索引: 就是将单列索引进行组合
	E. 外键索引: 只有 InnoDB 引擎支持外键索引, 用来保证数据的一致性、完整性和实现级联操作
	F. 全文索引: 快速匹配全部文档的方式。InnoDB 引擎 5.6 版本后, 才能支持全文索引, MEMORY 引擎不支持

2. 按照结构分类
	A. B+Tree 索引: MySQL 使用最频繁的一个索引数据结构, 是InnoDB和MyISAM 存储引擎默认的索引类型
	B. Hash 索引:   MySQL 中的 Memory 存储引擎默认支持的索引类型

创建索引

-- 1. 创建索引的语法:
CREATE  [UNIQUE][FULLTEXT]  INDEX  索引名称  [USING 索引类型]  ON  表名称(列名称....);

-- 2. 案例代码:
-- 为 student 表当中的 name 列创建一个普通的索引。
CREATE  INDEX  idx_name  ON  student(name);
-- 为 student 表当中的 age 列创建一个唯一索引。
CREATE  UNIQUE  INDEX  idx_age  ON  student(age);

查看索引

-- 1. 查看索引的语法:
SHOW  INDEX   FROM  表名称;

-- 2. 案例代码: 查询学生表当中,拥有的索引
SHOW  INDEX  FROM  student;

添加索引

-- 1. 普通索引
ALTER  TABLE  表名称  ADD  INDEX  索引名称 (列名);
-- 2. 组合索引
ALTER  TABLE  表名称  ADD  INDEX  索引名称 (列名1,列名2,列名3....);
-- 3. 主键索引
ALTER  TABLE  表名称  ADD  PRIMARY  KEY  (主键列名称);
-- 4. 外键索引
ALTER  TABLE  表名称  ADD  CONSTRAINT  外键名称  FOREIGN  KEY  (本表外键列名)  REFERENCES  主表名称(主键列名);
-- 5. 唯一索引
ALTER  TABLE  表名称  ADD  UNIQUE  索引名称(列名);
-- 6. 全文索引
ALTER  TABLE  表名称  ADD  FULLTEXT  索引名称(列名);

删除索引

-- 1. 基础语法
DROP  INDEX  索引名称  ON  表名称;

练习：为学生表当中的 score 列添加唯一约束，查看唯一约束，删除唯一约束

-- 1. 为student表中score列添加唯一约束
ALTER  TABLE  student ADD  UNIQUE  idx_score(score);

-- 2. 查询student表的索引
SHOW  INDEX  FROM  student;

-- 3. 删除 idx_score 索引
DROP  INDEX  idx_score  ON  student;

索引的原理

1. 磁盘存储
2. BTree     读写效率低,了解即可
3. B+Tree

磁盘存储特点

1. 将多条数据,进行分组管理,每一组的大小设定为 16KB。
2. 先查找数据所在的组, 再查找组当中的数据。

B+Tree的特点

1. 查询效率高. 普通节点上,不会保存 数据值,只需要找到 普通节点的 索引。 根据左边大,右边小的原则, 找到叶子节点就行。
2. 如果是 BTree 普通节点上, 会保存 数据值, 每次找到普通节点的同时, 还需要去获取节点当中的数据值。
3. 获取普通节点的数据值, 需要花费时间,降低效率。

创建索引的原则

1. 对查询频次较高，并且数据量比较大的表，建立索引。
2. 使用唯一索引，区分度越高，使用索引的效率越高。
3. 索引字段的选择，最佳候选列应当从 WHERE 子句的条件当中提取
4. 如果 WHERE 子句当中的条件组合比较多，那么应当挑选最常用的、过滤效果最好的列组合
5. 使用短索引，索引创建之后，也是使用硬盘来存储的，因此提升索引访问的 IO 效率，也可以提升总体的访问效率。
6. 索引虽然可以有效的提升查询数据的效率，但是并不是多多益善。

拓展练习

添加百万条数据，其中一列，添加索引，另外一列，不添加索引。

不添加索引

-- 使用存储过程,插入 100万条数据到数据库当中

-- 创建数据库,并且使用数据库,创建表
CREATE DATABASE IF NOT EXISTS db8;
USE db8;
CREATE TABLE IF NOT EXISTS hero (
	id INT,
	NAME VARCHAR(20)
);

-- 1. 删除存储过程
DROP PROCEDURE IF EXISTS pro_million;

-- 2. 创建存储过程
DELIMITER $
CREATE PROCEDURE pro_million()
BEGIN
	DECLARE num INT DEFAULT 1;
	WHILE num<=1000000 DO
	INSERT INTO hero VALUES (num,CONCAT('英雄姓名',num));
	SET num = num + 1;
	END WHILE;
END $
DELIMITER ;


-- 3. 调用存储过程
CALL pro_million();

-- 4. 查询结果
SELECT COUNT(*) FROM hero;

添加索引，对比两者

-- 1. 删除索引
DROP INDEX  IF EXISTS  idx_name ON  hero;

-- 2. 添加索引,给 hero 表
ALTER TABLE hero ADD UNIQUE idx_name(NAME);

-- 3. 查看索引
SHOW INDEX FROM hero;

-- 4. 查询结果
SELECT * FROM hero WHERE id = 633331;   -- 花费时间约 0.393秒
SELECT * FROM hero WHERE NAME = '英雄姓名632333'; -- 花费时间约 0.001秒

数据库锁

数据库为了保证数据的一致性, 当出现各种共享资源, 在被并发访问的时候, 保证有序设计的一种规则。

锁的分类

1. 操作方式分类
	A. 共享锁: 也叫作 读锁。 操作同一份数据,可以查询DQL,但是不能增删改DML。
	B. 排他锁: 也叫作 写锁。 当前的操作,没有完成的情况下,会阻断其他 增删改查 操作。

2. 粒度大小分类
	A. 表级锁: 
		a. 规则: 操作数据的时候,锁住整张表
		b. 特点: 开销小,加锁快,冲突高  
		c. 代表: MyISAM 存储引擎
	B. 行级锁: 
		a. 规则: 操作数据的时候,锁住当前操作行
		b. 特点: 开销大,加锁慢,冲突低  
		c. 代表: InnoDB 存储引擎

3. 使用方式分类
	A. 悲观锁: 每次查询,都会判定数据变化, 很悲观, 所以查询时候会加锁
	B. 乐观锁: 每次查询,都会判断数据不会发生变化, 很乐观, 需要手动加锁

不同的存储引擎，支持不同的锁 （常见的几种存储引擎当中，只有 InnoDB支持 行级锁）

存储引擎	表级锁	行级锁
InnoDB	支持	支持
MyISAM	支持	不支持
MEMORY	支持	不支持
BDB	支持	不支持

InnoDB锁演示

准备数据

-- 删除数据库,如果存在则删除
DROP DATABASE IF EXISTS db8;

-- 创建 db8 数据库
CREATE DATABASE IF NOT EXISTS db8;

-- 使用 db8 数据库
USE db8;

-- 创建student表
CREATE TABLE IF NOT EXISTS student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	age INT,
	score INT
);

-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,99),(NULL,'李四',24,95),(NULL,'王五',25,98),(NULL,'赵六',26,97);

-- 查询数据
SELECT * FROM student;

InnoDB 共享锁演示

语法格式

-- 共享锁说明: 数据可以被多个事务查询（DQL）, 但是不能修改（DML）
-- 创建共享锁的语法:
SELECT  语句  LOCK IN SHARE MODE;

共享锁，演示效果：窗口1

-- 使用数据库
USE db8;

-- 开启事务
START TRANSACTION;

-- 查询id为1数据，并加入共享锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询分数为99的数据，并加入共享锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;

-- 提交事务
COMMIT;

共享锁，演示效果：窗口2

-- 使用数据库
USE db8;

-- 开启事务
START TRANSACTION;

-- 查询id为1数据,(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1数据,也加入共享锁(共享锁和共享锁是兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 修改id为1数据，姓名改成张三三(修改失败。会出现锁的情况。只有窗口1提交事务后才能修改成功)
UPDATE student SET NAME='张三三' WHERE id=1;

-- 修改id为2数据，姓名改成李四四(修改成功，InnoDB引擎默认加的是行锁)
UPDATE student SET NAME='李四四' WHERE id=2;

-- 修改id为3数据，姓名改成王五五(修改失败，InnoDB引擎如果不采用带索引的列加锁，加的就是表锁)
UPDATE student SET NAME='王五五' WHERE id=3;

-- 提交事务
COMMIT;

InnoDB 排他锁演示

语法格式

-- 排他锁说明: 加锁的数据, 不可以被其他事务加锁查询，可以被普通查询，不支持 增删改。
-- 创建排他锁的语法:
SELECT 语句 FOR UPDATE;

排他锁，演示效果：窗口1

-- 使用数据库
USE db8;

-- 开启事务
START TRANSACTION;

-- 查询id为1数据，并加入排他锁
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 提交事务
COMMIT;

排他锁，演示效果：窗口2

-- 使用数据库
USE db8;

-- 开启事务
START TRANSACTION;

-- 查询id为1数据(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1数据,并加入共享锁(排他锁和共享锁是不兼容的)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询id为1数据，并加入排他锁(排他锁和排他锁是不兼容的)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 修改id为1数据，将姓名改成张三(修改失败，会出现锁的情况。只有窗口1提交事务后才能修改成功)
UPDATE student SET NAME='张三' WHERE id=1;

-- 提交事务
COMMIT;

MyISAM 锁 演示

MyISAM 数据准备

-- 删除数据库
DROP DATABASE IF EXISTS db9;

-- 创建数据库
CREATE DATABASE IF NOT EXISTS db9;

-- 使用数据库
USE db9;

-- 创建product表
CREATE TABLE IF NOT EXISTS product(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	price INT
)ENGINE = MYISAM;  -- 指定存储引擎为MyISAM

-- 添加数据
INSERT INTO product VALUES (NULL,'华为手机',4999),(NULL,'小米手机',2999),(NULL,'苹果',8999),(NULL,'中兴',1999);

MyISAM 读锁演示

语法格式

-- 读锁说明: 所有的连接，只能读取数据（DQL），不能修改数据（DML）
-- 基础语法:
LOCK  TABLE  表名称  READ;		  -- 加锁操作
UNLOCK  TABLES;  				-- 解锁操作（将当前会话所有的表进行解锁）	

读锁，演示效果：窗口1

-- 使用数据库
USE db9;

-- 为product表添加读锁
LOCK TABLE product READ;

-- 查询id为1数据
SELECT * FROM product WHERE id=1;

-- 修改id为1数据，将金额修改4999
UPDATE product SET price = 4999 WHERE id=1;

-- 解锁
UNLOCK TABLES;

读锁，演示效果：窗口2

-- 使用数据库
USE db9;

-- 查询id为1数据
SELECT * FROM product WHERE id=1;

-- 修改id为1数据，将金额改成5999(修改失败，只有窗口1解锁后才能修改成功)
UPDATE product SET price=5999 WHERE id=1;

MyISAM 写锁演示

语法格式

-- 写锁说明: 其他连接不能 查询 和 修改 数据
-- 基础语法:
LOCK  TABLE  表名称  WRITE;   	-- 加锁
UNLOCK  TABLES;				  -- 解锁（将当前会话所有的表,进行解锁）

写锁，演示效果：窗口1

-- 使用数据库
USE db9;

-- 为product表添加写锁
LOCK TABLE product WRITE;

-- 查询
SELECT * FROM product;

-- 修改
UPDATE product SET price=1999 WHERE id=2;

-- 解锁
UNLOCK TABLES;

写锁，演示效果：窗口2

-- 使用数据库
USE db9;

-- 查询(查询失败，只有窗口1解锁后才能查询成功)
SELECT * FROM product;

-- 修改(修改失败，只有窗口1解锁后才能修改成功)
UPDATE product SET price=2999 WHERE id=2;

悲观锁和乐观锁

基础说明

1. 悲观锁: 站在悲观的角度去看待问题, 它对于数据被外界修改的操作, 持有保守的态度, 认为数据随时都会修改。
2. 乐观锁: 站在乐观的角度去看待问题, 它对于数据被外界修改的操作, 持有乐观的态度, 认为不会有人去修改它, 所以不用去加锁。

案例代码

-- 创建city表
CREATE TABLE city(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 城市id
	NAME VARCHAR(20),                   -- 城市名称
	VERSION INT                         -- 版本号
);

-- 添加数据
INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'广州',1),(NULL,'深圳',1);


-- 将北京修改为北京市
-- 1.将北京的版本号读取出来
SELECT VERSION FROM city WHERE NAME='北京';   -- 1
-- 2.修改北京为北京市，版本号+1.并对比版本号是否相同
UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;