mysql（六）

事务

6.1 什么是事务

要么都成功，要么都失败

---

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

---

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

事务管理 ACID

原子性（Atomicity） 针对同一个事务 要么都完成，要么都不完成

一致性（Consistency）最终一致性，针对一个事务操作前与操作后的状态一致

隔离性（Isolation） 针对多个用户同时操作，主要是排除其他事务对本次事务的影响（事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离）

持久性（Durability）表示事务结束后的数据不随着外界原因导致数据丢失

举例子：

隔离产生的问题

1. 脏读

2. 不可重复读

3. 幻读

模拟测试事务转账

执行事务

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

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


-- 提交：持久化（成功！）
COMMIT
-- 回滚：回到的原来的样子（失败！）
ROLLBACK
-- 事务结束
SET autocommit=1 -- 开启自动提交

SAVEPOINT 保存点名 -- 设置一个事务的保存点（了解即可）
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点

模拟场景

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

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


-- 提交：持久化（成功！）
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(10) 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('hjy',100000),('B',1000)

-- 模拟转账
SET autocommit =0;  -- 关闭自动提交
START TRANSACTION -- 开启事务（一组事务）

UPDATE account SET money=money -500 WHERE `name`='hjy' -- hjy 减500
UPDATE account SET money=money+500 WHERE `name`='B' -- B加500
COMMIT; -- 提交事务，事务一旦提交 就持久化了
ROLLBACK; -- 回滚
SET autocommit =1 -- 恢复默认值

7 索引

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

7.1，索引的分类

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

通过创建唯一索引，可以保证某一列的值具有唯一性

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

基础语法

-- 索引的使用
-- 1，在创建表的时候给字段增加索引
-- 2，创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

--  增加一个全文索引（索引名）列名
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`);

-- explain 分析sql执行的状况

EXPLAIN SELECT * FROM student

EXPLAIN SELECT * FROM student WHERE MATCH(`studentname`) AGAINST ('张%')

7.2 测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' ,
`gender` TINYINT(4) UNSIGNED DEFAULT'0',
`password` VARCHAR(100) NOT NULL DEFAULT'密码',
`age` TINYINT(4) DEFAULT'0',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入100万数据
-- 写函数之前必须要写，标志(delimiter)
DELIMITER $$ 

CREATE FUNCTION `shop`.mock_data()
RETURNS INT   -- 这里是returns 要加s
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`)
		VALUES(CONCAT('用户',i),'19224305@qq.com',
		'123456789',FLOOR(RAND()*2),FLOOR(RAND()*100));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data() 
-- 执行此函数 生成一百万条数据

SELECT * FROM `app_user` WHERE `name`='用户19999';
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户19999';

-- id_表名_字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON `app_user`(`name`);

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

7.3 索引原则

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

索引的数据结果

Btree：InnoDB的默认数据结构

Hash 类型索引

name);

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

## 7.3 索引原则

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

> 索引的数据结果
>
> [外链图片转存中...(img-7cSRKqw2-1628688108300)]

Btree：InnoDB的默认数据结构

Hash 类型索引