Day 03:
一、事务
事务(transaction):要么都成功,要么都失败。
核心:将一组 SQL 放在一个批次中去执行。
1. 原则
原则 ACID:原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)。
- 原子性:一个事务中的所有步骤要么都成功,要么都失败,不能只成功一个步骤。
- 一致性:包括最终一致性和过程一致性,一个事务操作前后的数据完整性保持一致。
- 持久性:事务结束后的数据不会因为外界原因(如服务器断电后重启)而导致数据丢失(一旦提交不可逆)。分为事务提交前和事务提交后。
情形 | 结果 |
---|---|
事务提交前 | 恢复到原状 |
事务提交后 | 持久化到数据库(成为文件) |
注意:事务一旦提交不可逆,被持久化到数据库中。
- 隔离性:针对多个用户同时操作时,排除其他事务对本次事务的影响(多个进程互不干扰)。隔离失败会出现以下情形。
名词 | 情形 |
---|---|
脏读 | 一个事务读取了另外一个事务未提交的数据 |
不可重复读 | 在一个事务内读取表中的某一行数据,多次读取的结果不同 |
幻读(虚读) | 一个事务内读取到了别的事务插入的数据,导致前后读取不一致(一般是行影响,多了一行) |
2. 测试实现
格式
-- 手动处理事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启一个事务,这是标记一个事务的开始,从这之后的 sql 都在同一个事务内
UPDATE xxx;
INSERT xxx; -- 事务内的操作
COMMIT; -- 操作执行成功后 --> 提交事务
ROLLBACK; -- 操作执行失败后 --> 回滚,回到原来的样子
SET autocommit = 1; -- 恢复自动提交,事务结束
-- 了解
SAVEPOINT 保存点名; -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
RELEASE SAVEPOINT 保存点名; -- 撤销保存点
注意:MySQL 是默认开启事务自动提交的,即:autocommit = 1,所以在手动处理事务时,要关闭事务自动提交。
图解
需求:用事务实现转账操作
-- 用事务实现转账操作
-- 创建数据库 shop
CREATE DATABASE IF NOT EXISTS `shop` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE `shop`;
-- 创建表 account
CREATE TABLE IF NOT EXISTS `account` (
`id` INT(5) NOT NULL auto_increment COMMENT '序号',
`name` VARCHAR(10) NOT NULL DEFAULT 'Sun3285' COMMENT '姓名',
`money` DECIMAL(7, 2) NOT NULL DEFAULT 20000.85 COMMENT '余额',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT charset=utf8mb4;
-- 添加数据
INSERT INTO `account` (`id`, `name`, `money`) VALUES
(1, 'Sun1234', 15000.12),
(2, 'Sun3285', 26000.70),
(3, 'Sun1478', 23000.93);
-- 模拟转账:事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启一个事务
UPDATE `account` SET `money`=`money`-2000.12 WHERE `name`='Sun1234';
UPDATE `account` SET `money`=`money`+2000.12 WHERE `name`='Sun3285'; -- Sun1234 给 Sun3285 转账 2000.12
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复自动提交
注意:
- DECIMAL(M,D) 中,M 是最大位数,D 是小数点右边的位数,D 不大于 M。如:DECIMAL(5,2) 可存储范围是从 -999.99 到 999.99,超出存储范围会报错,使用 DECIMAL 时,建议参数 M 和 D 手动指定,并按需分配。
- 事务一旦提交不可逆,被持久化到数据库中。
二、索引
索引(Index):是帮助 MySQL 高效获取数据的数据结构。
索引的本质是数据结构。
1. 分类
索引分为:主键索引(PRIMARY KEY)、唯一索引(UNIQUE KEY)、常规索引(KEY/INDEX)以及全文索引(FULLTEXT KEY)。
索引 | 说明 |
---|---|
主键索引 PRIMARY KEY | 唯一标识,不可重复,只能有一个列作为主键 |
唯一索引 UNIQUE KEY | 避免重复的列出现,可以重复,多个列都可以标识为唯一索引 |
常规索引 KEY/INDEX | 默认,用 KEY 或 INDEX 关键字来设置 |
全文索引 FULLTEXT KEY | 快速定位数据 |
注意:一个表中主键索引只能有一个,而唯一索引可以有多个。
2. 创建索引
创建索引可以有两种方法:
- 在创建表的时候给字段增加索引
- 创建表完毕后,增加索引
SHOW INDEX FROM `表名`; -- 显示所有的索引信息
ALTER TABLE `表名` ADD FULLTEXT/UNIQUE/PRIMARY INDEX/KEY `索引名` (`字段名`); -- 改变表,增加一个索引
3. 分析 sql 执行的状况
用 EXPLAIN
关键字可以分析 sql 执行的状况,分为非全文索引和全文索引。
- 非全文索引
EXPLAIN SELECT xxx;
- 全文索引
EXPLAIN SELECT * FROM `表名` WHERE MATCH(`全文索引字段`) against('查找的内容');
4. 测试索引
创建 app_user 表,并插入百万条数据,查询信息。
-- 创建数据库 use
CREATE DATABASE IF NOT EXISTS `use` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `use`;
-- 创建表 app_user
CREATE TABLE IF NOT EXISTS `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL auto_increment COMMENT '用户id',
`name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户邮箱',
`phone` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别(0:男; 1:女)',
`password` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '密码',
`age` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '年龄',
`creat_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT charset=utf8mb4 COMMENT='app用户表';
-- 百万数据插入
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$ -- 写函数之前必须要写,标志:$$
CREATE FUNCTION mock_data()
RETURNS INT -- 注意returns,否则报错。
DETERMINISTIC -- 8.0版本需要多这么一行
BEGIN
DECLARE num INT DEFAULT 1000000; -- num 作为截止数字,定义为百万,
DECLARE i INT DEFAULT 0;
WHILE i < num DO
INSERT INTO app_user (`name`, `email`, `phone`, `gender`, `password`, `age`) VALUES
(CONCAT('用户', i), '123456789@qq.com', CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)), FLOOR(RAND()*2),
UUID(), (FLOOR(RAND()*100))+1);
SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
-
未设置索引时,查询某一条数据,用
EXPLAIN
分析执行情况;
-
设置索引后,再次查询同一条数据,用
EXPLAIN
分析执行情况;
注意:索引在小数据量的时候,用处不大,但是在大数据量的时候,效果明显(查询时间减少)。
5. 索引原则
- 索引不是越多越好;
- 不要对经常变动的数据增加索引;
- 小数据量的表不需要加索引;
- 索引一般加在常用来查询的字段上。
三、数据库用户管理
- Navicat 可视化操作
注意:
- 用户名可以随便设置,一个用户对应一个密码,主机是指在哪个地方登录,为
localhost
或127.0.0.1
;- 新建连接(数据库管理系统与数据库之间的连接)时,连接名可以随便设置,每个用户都可以新建多个连接。
- SQL 命令操作
-- 创建用户:CREATE
CREATE USER '用户名'@'%' IDENTIFIED BY '密码'; -- 所有 IP 都可用账号
CREATE USER '用户名'@'localhost' IDENTIFIED BY '密码'; -- 本地可用账号
CREATE USER '用户名'@'指定 IP' IDENTIFIED BY '密码'; -- 指定 IP 可用账号
-- 修改用户密码:ALTER
ALTER USER '用户名'@'localhost' IDENTIFIED BY '新密码';
-- 修改用户名或主机:RENAME
RENAME USER '用户名'@'localhost' TO '新用户名'@'127.0.0.1'; -- 同时也可以修改主机
-- 用户授权:除了给用户授权(GRANT),其他都可以干:GRANT
GRANT ALL PRIVILEGES ON *.* TO '用户名'@'127.0.0.1';
GRANT ALL PRIVILEGES ON *.* TO '用户名'@'127.0.0.1' WITH GRANT OPTION;
-- 刷新权限:FLUSH
FLUSH PRIVILEGES;
-- 查询权限:SHOW
SHOW GRANTS FOR '用户名'@'127.0.0.1';
-- 撤销权限:REVOKE
REVOKE 某个权限 ON *.* FROM '用户名'@'127.0.0.1'; -- 撤销某个权限
REVOKE ALL PRIVILEGES ON *.* FROM '用户名'@'127.0.0.1'; -- 撤销全部权限
-- 删除用户:DROP
DROP USER '用户名'@'127.0.0.1';
注意:
- 表示方式:
用户名@主机
,如:Sun3285@localhost
;- 用户授权后,除了给用户授权(GRANT OPTION),其他都可以干,若仍想要给用户授权功能,则用
WITH GRANT OPTION
;- 用户授权时,
*.*
表示所有数据库.所有表
,指对所有的表生效。
四、备份
备份的目的:保证重要数据不丢失;数据转移(把数据库给别人)。
备份的内容:表结构和数据,不包括查询和报表。
三种方式:直接拷贝物理文件、在可视化工具中(Navicat)手动导出、使用命令行导出。
-
方式一:直接拷贝物理文件
data
。
-
方式二:在可视化工具中(Navicat)手动导出,选择要导出的库或表,右键选择
转储 SQL 文件
以及结构和数据
。
-
方式三:使用命令行(
Win + R
打开)导出。
-- 导出:mysqldump -h主机 -u用户名 -p 数据库 表名1 表名2 表xx > 导出路径(物理磁盘位置/文件名)
mysqldump -hlocalhost -uroot -p exercise > E:/sql_file/a.sql -- 导出整个数据库
mysqldump -hlocalhost -uroot -p exercise1 grade subject > E:/sql_file/b.sql -- 导出某个数据库的某几张表
-- 导入:source sql文件路径
mysql -uroot -p; -- 先登录 mysql
use exercise1; -- 选中要导入的数据库
source E:/sql_file/b.sql; -- 导入指定的 sql 文件
注意:
- 导出路径中不能有中文,否则乱码报错;
- 导出路径中的斜杠为正斜杠:
/
,并且需要写导出后的文件名;- 导出时,
-h
表示主机、-u
表示用户名、-p
表示密码、>
表示导出;- 在命令行中,导出的语句不能以分号
;
结尾,否则报错;- 在导入 sql 文件时,要先登录数据库,再用
source
将指定的 sql 文件导入;- 如果导入的是数据库,则不需要第二步,即不用选中要导入的数据库。
五、规范数据库设计
目的:节省内存空间、保证数据的完整性、方便开发系统。
软件开发中,关于数据库的设计步骤:
- 收集信息、分析需求(用户表、分类表、文章表等等);
- 概要设计(设计关系图 E-R 图);
- 标识实体、把需求落地到每一个字段;
- 标识实体之间的关系。
注意:前台的每一个位置,对应的都是数据库里面的一个字段。
1. 三大范式
- 第一范式(1NF)
内容:原子性:保证每一列不可再分
- 第二范式(2NF)
前提:满足第一范式
内容:每张表只描述一件事情
- 第三范式(3NF)
前提:满足第一范式和第二范式
内容:确保数据表中的每一列数据都和主键直接相关,而不能间接相关
注意:规范数据库的设计需要考虑规范性和性能的问题,两者寻求平衡。
阿里手册规范:关联查询不能超过三张表
- 考虑商业化的需求和目标(成本、用户体验等)时,数据库的性能更加重要;但考虑性能的时候,也需要适当考虑一下规范性;
- 考虑性能的做法:
- 故意给某些表增加一些冗余的字段,可以将多表查询变为单表查询;
- 故意增加一些计算列(如统计总数),可以从大数据量降低为小数据量的查询。
注意:
-
事务 ACID 理解参考链接:点此进入。
-
MySQL 索引的参考资料:CodingLabs - MySQL索引背后的数据结构及算法原理文章来源:https://www.toymoban.com/news/detail-425415.html
-
在测试索引部分,查询男生人数总数的 sql 语句为:文章来源地址https://www.toymoban.com/news/detail-425415.html
SELECT SUM(1) FROM `app_user` WHERE `gender`=0; -- 用 count(1)、count(*) 或 count(列名) 也可以
- InnoDB 的默认数据结构为:Btree。
- 数据库命名一般不用驼峰命名规则,因为数据库不区分大小写,而采用下划线。
到了这里,关于MySQL:事务、索引、用户管理、备份、数据库设计(三大范式)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!