Java（MySQL03）
参考视频：28. 事务ACID原则、脏读、不可重复读、幻读（狂神）
参考文档：https://gitee.com/zhayuyao/java-notes/blob/master/md%E6%96%87%E6%A1%A3/%E7%8B%82%E7%A5%9E%E8%AF%B4java/03.MySql/MySQL%E5%9F%BA%E7%A1%80.md#22%E5%88%86%E9%A1%B5%E5%92%8C%E6%8E%92%E5%BA%8F
6. 事务
6.1 什么是事务
- 要么都成功，要么都失败
- SQL执行 A给B转账 A1000 -> 200 B200
- SQL执行 B收到A钱 A800 -> B400
-
将一组SQL放在一个批次中去执行
-
事务原则（ACID原则）：
- 原子性（atomicity，或称不可分割性）
- 一致性（consistency）
- 隔离性（isolation，又称独立性）
- 持久性（durability）
-
脏读、幻读…
-
参考博客：https://blog.csdn.net/dengjili/article/details/82468576
- ACID：
- 原子性（Atomicity）
事务中的操作要么都成功，要么都失败。 - 一致性（Consistency）
事务前后数据的完整性必须保持一致。 - 隔离性（Isolation）
多用户并发访问数据库时，数据库开启的各个事务之间相互隔离，不能被干扰。 - 持久性（Durability）
事务一旦提交则不可逆，被持久化到数据库中。
- 隔离所导致的一些问题：
- 脏读：
指一个事务读取了另一个事务未提交的数据。 - 不可重复读：
指在一个事务内读取表中某一行数据，多次读取的结果不同。（不一定是错误，可能场合不对。） - 虚读（幻读）
指在一个事务内读取了别的事务插入的数据，导致前后读取不一致。
- 执行事务：
--
-- mysql是默认开启事务自动提交的
SET autocomnit = 0; /*关闭*/
SET autocommit = 1; /*开启（默认的）*/
-- 手动处理事务
SET autocomnit = 0; -- 关闭自动提交
-- 事务开启
START TRANSACTION; -- 标记一个事务的开始，从这个之后的 sql 都在同一个事务内
INSERT xx;
INSERT xx;
-- 提交：持久化(成功！)
COMMIT;
-- 回滚：回到的原来的样子(失败！)
ROLLBACK;
-- 事务结束
SET autocommit = 1; -- 开启自动提交
-- 了解
SAVEPOINT 保存点名; -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
RELEASE SAVEPOINT 保存点名; -- 撤销保存点
- 模拟场景：
--
-- 转账
CREATE DATABASE shop CHARSET utf8 COLLATE utf8_general_ci;
-- 使用shop数据库
USE shop;
-- 建表
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`money` DECIMAL(9,2) NOT NULL, -- 一共9位，小数点后2位
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; -- 开启事务 （一组事务）
UPDATE account SET `money`=`money`-500 WHERE `name`='A'; -- A减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 UNIQUE KEY `UK_IDENTITY_CARD` (`identity_card`);
ALTER TABLE `student` ADD KEY `K_STUDENT_NAME`(`student_name`);
ALTER TABLE `student` ADD FULLTEXT INDEX `FI_PHONE` (`phone`);
-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`phone`) AGAINST('138'); -- 全文索引
7.2 测试索引
--
-- 测试索引
CREATE TABLE app_user (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
`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 COMMENT '创建时间',
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';
-- 插入100万数据b (函数)
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),'123345@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 31 sec
SELECT mock_data();
SELECT * FROM app_user;
-- 函数中间的插入脚本
INSERT INTO app_user(`name`,
`email`,
`phone`,
`gender`,
`password`,
`age`)
VALUES(CONCAT('用户X'),
'123345@qq.com',
CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*100));
-- 测试过程
-- 加索引前
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.628 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
-- 创建索引
-- id_表名_字段名 索引名
-- CREATE INDEX 索引名 ON 表名(`字段名`);
CREATE INDEX id_app_user_name ON app_user(`name`); -- 11.556 sec
-- 加索引后
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.046 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.001 sec
- 加索引前
- 加索引后
- 索引在小数据量的时候，用处不大，但是在大数据量的时候，区别十分明显。
7.3 索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在经常用来查询的字段上
索引的数据结构：
- Hash类型的索引
- Btree ：innoDB的默认数据结构
阅读：
- CodingLabs-MySQL索引背后的数据结构及算法原理：
- http://blog.codinglabs.org/articles/theory-of-mysql-index.html
8. 权限管理和备份
8.1 用户管理
- SQLyog可视化管理
- sql命令操作
- 用户表：mysql.user
- 本质：读这张表进行增删改查
-- 创建用户
CREATE USER zyy IDENTIFIED BY '123456';
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456');
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR zyy = PASSWORD('123456');
-- 重命名 RENAME 原名子 zyy TO 新名字;
RENAME USER zyy TO newzyy;
-- 用户授权 ALL PRIVILEGES 全部的权限，库，表
-- ALL PRIVILEGES 除了给别人授权不行，其他都能干
GRANT ALL PRIVILEGES ON *.* TO newzyy;
-- 查询权限
SHOW GRANTS FOR newzyy; -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost; -- 查看root用户的权限
-- 撤销权限 REVOKE哪些权限，在哪个库，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM newzyy;
-- 删除用户
DROP USER newzyy;
8.2 数据库备份
- 为什么要备份？
- 保证重要的数据不丢失
- 数据转移
- mysql数据库备份的方式
- 直接拷贝物理文件
- 在sqlyog这种可视化工具中手动导出
- 在想要导出的表或者库中，右键，
- 使用命令行导出 mysqldump 命令行使用
# 一张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# 多张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql
# 数据库 mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql
# 导入
# 登录的情况下，切换到指定的数据库
# source 备份文件
# 也可以这样
mysql -u用户名 -p密码 库名<备份文件
- 假设你要备份数据库，防止数据丢失。
- 把数据库给别人，直接给sql即可。
9. 规范数据库设计
9.1 为什么需要设计
- 当数据库比较复杂的时候，我们就需要设计了
- 糟糕的数据库设计
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦、异常（屏蔽使用物理外键）
- 程序的性能差
- 良好的数据库设计
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
- 软件开发中，关于数据库的设计
- 分析需求，分析业务和需要处理的数据库的需求
- 概要设计：设计关系图 E-R 图
设计数据库的步骤（个人博客）
- 收集信息，分析需求
-
用户表（用户登录注销，用户的个人信息，写博客，创建分类）
-
分类表（文章分类，谁创建的）
-
文章表（文章信息）
-
评论表
-
友链表（友情链接信息）
-
自定义表（系统信息，某个关键的字，或者一些主字段）
key:value -
关注表(粉丝数)
-
说说表（发表心情， id…content…create_time）
- 标识实体（把需求落到每个字段）
- 标识实体之间的关系
- 写博客：user --> blog
- 创建分类：user --> category
- 关注：user --> user
- 友链：links
- 评论：user --> user --> blog
9.2 数据库三大范式（了解）
- 为什么需要数据规范化？
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
- 三大范式
- 第一范式（1NF）
- 原子性：保证每一列不可再分
- 第二范式（2NF）
- 前提：满足第一范式
- 第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）
- 每张表只描述一件事情
- 第三范式（3NF）
- 前提：满足第一范式和第二范式
- 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
（规范数据库的设计）
- 规范性和性能的问题：
- 关联查询的表不得超过三张表
- 考虑商业化的需求和目标（成本，用户体验），数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性
- 故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据库降低为小数据量的查询：索引）
扫一扫
11万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
