MySQL Day５.１

最新推荐文章于 2024-11-15 21:53:57 发布

Schdust

最新推荐文章于 2024-11-15 21:53:57 发布

阅读量388

点赞数

分类专栏： MySQL学习文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/m0_52991090/article/details/121191918

版权

MySQL学习专栏收录该内容

5 篇文章 0 订阅

订阅专栏

查看源图像

6、事务

6.1、什么是事务

要么都成功，要么都失败

事务原则：ACID原则：原子性、一致性、隔离性、永久性

参考博客：mysql的事务四个特性以及事务的四个隔离级别 - java界的小python - 博客园

1、原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

要么都成功，要么都失败。

2、一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。

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

3、隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。

不会被其他事务所干扰。

4、持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成。否则的话就会造成我们虽然看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。这是不允许的。

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

隔离所导致的一些问题

脏读（Dirty Reads）
脏读（Dirty Read）：A事务读取B事务尚未提交的数据并在此基础上操作，而B事务执行回滚，那么A读取到的数据就是脏数据。

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

不可重复读（Non-repeatable Reads）
一个事务对同一行数据重复读取两次，但是却得到了不同的结果。事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。

在一个事务内读取表中的某一行数据，多次读取结果不同。

幻象读
指两次执行同一条 select 语句会出现不同的结果，第二次读会增加一数据行，并没有说这两次执行是在同一个事务中。一般情况下，幻象读应该正是我们所需要的。但有时候却不是，如果打开的游标，在对游标进行操作时，并不希望新增的记录加到游标命中的数据集中来。隔离级别为游标稳定性的，可以阻止幻象读。例如：目前工资为1000的员工有10人。那么事务1中读取所有工资为1000的员工，得到了10条记录；这时事务2向员工表插入了一条员工记录，工资也为1000；那么事务1再次读取所有工资为1000的员工共读取到了11条记录。

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

执行事务

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


-- 手动处理事务
SET autocommit = 0  -- 关闭自动提交

-- 事务开启
start transaction  -- 标记一个事物的开始，从这个之后的sql都在同一个事务中

insert  xxx
insert  xxx

-- 提交: 持久化（成功！）
commit
-- 回滚：回到原来的样子（失败！）
rollback

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

-- 以下为了解内容
savepoint 保存点名  -- 设置一个事务保存点
rollback  to 保存点名  -- 回滚到保存点
release savepoint 保存点名  -- 撤销保存点

模拟场景

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account`(
	`id` INT(4) 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('张三','2000.00'),('李四','10000.00')

-- 模拟转账：事务
SET autocommit = 0 -- 关闭自动提交
START TRANSACTION  -- 开启一个事务

UPDATE `account` SET `money`=`money`-500 WHERE `name`='张三'  -- 张三减500
UPDATE `account` SET `money`=`money`+500 WHERE `name`='李四'  -- 李四加500
 
COMMIT   -- 成功————>提交  事务一旦提交，就被持久化了
ROLLBACK -- 失败————>回滚

SET autocommit = 1 -- 回复自动提交

7、索引

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

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

7.1、索引的分类

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

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

基础语法

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

-- 显示所有索引信息
SHOW INDEX FROM `account`  -- 显示`account`表中所有索引信息

-- 增加一个全文索引 （索引名） 列名
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、测试索引

create database `school` -- 创建一个数据库

CREATE TABLE `app_user` (-- 创建一个表
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
  `email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
  `phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
  `gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0:男；1：女）',
  `password` VARCHAR(100) NOT NULL COMMENT '密码',
  `age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'



-- 插入100万条数据
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$-- 写函数之前必写，分隔符
CREATE FUNCTION mock_data()
RETURNS INT deterministic
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), '24736743@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;
SELECT mock_data();


select * from app_user where `name`='用户9999'; -- 2.486 sec

-- id_表名_字段名
-- create index 索引名 on 表(字段)

create index id_app_user_name on app_user(`name`);

SELECT * FROM app_user WHERE `name`='用户9999'; --  0.003 sec

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

加快查询速度