事务与索引

最新推荐文章于 2024-11-06 11:22:39 发布

酝酿～思念

最新推荐文章于 2024-11-06 11:22:39 发布

阅读量88

点赞数

分类专栏： MySQL 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/qq_44561426/article/details/121587587

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

事务

要么都成功，要么都失败

事务原则：ACID原则原子性，一致性，隔离性，持久性 (脏读，幻读……)

1、基本概念

原子性(Atomicity)

要么都成功，要么都失败

一致性(Consistency)

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

隔离性(Isolation)

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

持久性(Durability)

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

隔离所导致的一些问题：

脏读：

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

不可重复读：

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

虚读(幻读)

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

2、执行事务

/*
mysql是默认开启事务自动提交的
set autocommit = 0	关闭
set autocommit = 1	开启
*/
-- 手动处理事务
-- 首先要关闭自动提交
SET autocommit = 0

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

INSERT xx
INSERT xx

-- 提交：持久化（成功！）
COMMIT

-- 回滚：回到原来的样子（失败！）
ROLLBACK

-- 事务结束
-- 最后要开启自动提交
SET autocommit = 1

-- 了解
-- 保存点名，设置一个事务的保存点，就和存档一样
SAVEPOINT 保存点名
-- 回滚到保存点，就和读档一样
ROLLBACK TO SAVEPOINT 保存点名
-- 删除保存点
RELEASE SAVEPOINT 保存点名

3、模拟场景：

-- 转账
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.00),
('B',10000.00)

-- 模拟转账：事务
-- 首先关闭自动提交
SET autocommit = 0;
-- 开启一个事务
START TRANSACTION
-- A-500
UPDATE `account` SET money = money - 500
WHERE `name` = 'A'
-- B+500
UPDATE `account` SET money = money + 500
WHERE `name` = 'B'

-- 提交事务
COMMIT;
-- 回滚
ROLLBACK;

-- 恢复默认值
SET autocommit = 1;

这些事务的模拟场景看起来没啥用，还需要自己去一条一条的执行，看似很麻烦，其实到时候在业务当中是直接写在java语句之中自动执行的，如：写在try……catch之中，没有异常就执行COMMIT，有异常就执行ROLLBACK，差不多就是这种的操作

索引

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

1、基本概念

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

2、基础语法

-- 索引
-- 索引的使用
-- 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('张');

3、测试索引


/*
报错：
	This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
*/
-- 报了这个错误，才百度加了这行代码
SET GLOBAL log_bin_trust_function_creators=1;
-- 插入100万条数据
-- 写函数之前必须要写，是一个标志
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`,`email`,`phone`,`gender`,`password`,`age`)
	   VALUES(CONCAT('用户',i),'2412451@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
	   SET i = i+1;
	END WHILE;
	RETURN i;
END;

-- 消耗时间：1 min 21 sec
SELECT mock_data();

-- 消耗时间：1.276 sec
SELECT * FROM app_user
WHERE `name` = '用户9999';

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

-- 消耗时间：0.004 sec
SELECT * FROM app_user
WHERE `name` = '用户9999';