MySQL事务、索引、数据库设计以及数据库三大规范_数据库事务规范

1、什么是事务？

要么都成功，要么都失败
——————————————
1、SQL执行 A给B转账 A 1000 —>200 B 200

2、SQL执行 B收到A的钱 A 800 —>B 400
——————————————
将一组SQL放在一个批次中去执行

事务(Transaction)原则：ACID原则(脏读，幻读…)

参考博客连接：事务ACID理解
原则性(Atomcity)
要么都成功，要么都失败
一致性(Consistency)
事务前后的数据完整性要保证一致
隔离性(Isolation)
多个用户并发访问数据库时，数据库为每一个用户开启事务，不能被其他事务的操作数据所干扰多个并发事务之间要相互隔离。
持久性(Durability)
事务一旦提交不可逆，被持久化到数据库中！

隔离导致一些问题：

脏读
指一个事务读取了另外一个事务未提交的数据！
不可重复读
在一个事务内读取表中的某一行数据，多次读取结果不同。
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

执行事务

-- 事务
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启(默认的)*/

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

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

-- 提交：持久化(成功)

COMMIT


-- 回滚：回到原来的样子(失败)
ROLLBACK

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

-- 了解
SAVEPOINT 保存点名  -- (业务过多，可以暂时保存)设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

模拟转账

-- 转账
CREATE DATABASE IF NOT EXISTS `shop` CHARSET utf8 COLLATE utf8_general_ci

CREATE TABLE IF NOT EXISTS `account`(
 `id` INT(4) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(30) NOT NULL,
 `money` DECIMAL(9,2) NOT NULL, -- 在java中是一个字符串
 PRIMARY KEY(`id`)




)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`,`money`) VALUES('A',2000.00),('B',10000.00)

-- 模拟转账：事务
SET TRANSACTION = 0  -- 1关闭自动提交事务

-- 2开启一个事务(一组事务)
START TRANSACTION  

UPDATE account SET money=money-500 WHERE `name`= 'A' -- A减500
UPDATE account SET money=money+500 WHERE `name`='B'  -- B加500

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

-- 恢复默认自动提交事务
SET TRANSACTION = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

2、索引

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

2.1、索引的分类

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

• 主键索引 (PRIMARY KEY)
  唯一的标识，主键不能重复，只能有一个列作为主键
• 唯一索引 (UNIQUE KEY)
  避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
• 常规索引 (KEY/INDEX)
  默认的，index/key关键字来设置
• 全文索引 (FullText)
  在特定的数据库引擎下才有，MYISAM，快速定位数据

基础语法

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

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

-- 增加一个索引(索引名) 列名
ALTER TABLE `student` ADD FULLTEXT INDEX `studentName`

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引

SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘')
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.2、测试索引

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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

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();

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

SELECT * FROM student;

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

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

-- 分析查询
EXPLAIN SELECT * FROM app_user WHERE `name` ='用户9999';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

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

2.3、索引原则

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

索引的数据结构

Hash 类型的索引
Btree：INNODB的默认数据结构~

详情阅读：MySQL索引背后的数据结构及算法原理

3、MySQL权限管理和备份

3.1、用户管理

SQLyog可视化管理

SQL命令操作

用户表：mysql.user
本质：读这张表进行增删改查

-- 创建用户CREATE USER '用户名' IDENTIFIED BY '密码'
CREATE USER 'rygar' IDENTIFIED BY '123456'

-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456') -- mysql5.0版本

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'   -- mysql8.0版本


-- 修改密码(修改指定用户密码)
-- mysql8.0版本 ALTER USER '用户名' @'localhost' IDENTIFIED BY '密码'
SET PASSWORD FOR rygar = PASSWORD('111111')  -- mysql 5.0版本

-- %表示所有的ip都可登陆，可以远程访问
ALTER USER 'rygar'@'%' IDENTIFIED BY '111111'   -- mysql8.0版本

-- 重命名
-- RENAME USER 用户原来名字 TO 新用户名名字
RENAME USER rygar TO rygar_h


-- 用户授权	ALL PRIVILEGES 全部的权限, 库，表
-- ALL PRIVILEGES 除了给别人授权，其他都能够干
GRANT ALL PRIVILEGES ON *.* TO rygar_h

-- 查看权限
SHOW GRANTS FOR rygar_h  -- 查看指定用户的权限

SHOW GRANTS FOR root@localhost


-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM rygar_h

-- 删除用户
DROP USER rygar_h
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

3.2、MySQL备份

为什么要备份：

• 保证重要的数据不丢失

• 数据转移
  MySQL数据库备份方式

• 直接拷贝物理文件

• 使用SQLyog可视化工具手动导出
  在想要导出的表或者库中，右键，选择备份或导出
  
  

• 使用命令行导出 mysqldump 命令行使用

# mysqldump -h主机   -u用户名 -p密码  数据库   表名  >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot school student >D:/a.sql

-- 导出多张表
# mysqldump -h主机   -u用户名 -p密码  数据库   表名1 表名2 表名3  >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot school student result emp >D:/b.sql
1
2
3
4
5
6

-- 导入sql
# 登陆的情况下，切换到指定的数据库
# source 备份文件
source d:/a.sql
1
2
3
4

# 没登录的情况下
mysql -u用户名 -p密码 库名< 备份文件
1
2

备份数据库，防止数据丢失！

4、规范数据库设计

4.1、为什么需要设计

当数据库比较复杂的时候，我们就需要设计
糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性
• 方便我们开发系统
  软件开发中，关于数据库的设计
• 分析需求：分析业务和需要处理的数据库需求
• 概要设计：设计关系图E-R图
  设计数据库的步骤：(个人博客)
• 收集信息，分析需求
  用户表(用户登陆注销，用户的个人信息，写博客，创建分类)
  分类表(文章分类，谁创建的)
  文章表(文章的信息)
  评论表
  友链表(友链信息)
• 标识实体(把需求落地到每个字段)
• 标识实体之间的关系
  写博客：user -->blog
  创建分类：user --> category
  关注：user–>user
  友链：links
  评论：user-user-blog

5、数据库的规约，三大范式

为什么需要数据规范化？

• 信息重复
• 更新异常
• 插入异常
  无法正常显示信息
• 删除异常
  丢失有效的信息

三大范式

第一范式(1NF)
要求数据库表的每一列都是不可分割的原子数据项

第二范式(2NF)
必须满足第一范式的前提下，每张表只描述一件事情

第三范式(3NF)
前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范数据库的设计
规范性和性能的问题
关联查询的表不得超过三张表

• 考虑商业化的需求和目标(成本，用户体验！)数据库的性能更加重要
• 在规范性能的问题的时候，需要适当的考虑一下规范性！
• 故意给某些表加一些冗余的字段(从多表查询中变为单表查询)
• 故意增加一些计算列(从大数据量降低为小数据量的查询：索引)

6、JDBC

点击：JDBC