MySQL(二)事务、索引的分类、作用和创建、数据库管理

MySQL-2

学习视频： B站 狂神说java – https://www.bilibili.com/video/BV1NJ411J79W

1、事务

1.1、事务的简介

什么是事务？事务的原则是什么？

事务最简单的来说就是 要么都成功，要么都失败。

例子：

1. SQL执行 A给B 转账 A原来有1000 -200给B B+200

2. SQL执行 B收到A的钱 A=800 B=200

   不能结束之后，A有1000 B有200，这就违背了事务的一致性原则，即数据的完整性要保持一致。 不能多钱，也不能少了。

• 事务就是将一组SQL语句放在同一批次内去执行。
• 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
• MySQL事务处理只支持InnoDB和BDB数据表类型

事务的原则： ACID原则：原子性、一致性、持久性、隔离性。

• 原子性（Atomicity）：要么都成功，要么都失败，是不可能停止在中间某个环节的。 事务在执行过程中发生错误，就会进行**回滚（ROLLBACK）**到事务开始之前的状态，就像事务从未发生执行过。
• 一致性（Consistency）：事务前后的数据完整性要保证一致。像上面的转账例子一样，事务的数据必须一致，主要特征就是保护性和不变性。
• 持久性（Durability）：— 事务提交，事务一旦提交则不可逆，被持久化到数据库中！并不会发生回滚 ROLLBACK。
• 隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间相互隔离。 这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

原子一致持久隔离。

隔离所导致的一些问题：脏读、不可重复、幻读。

事务的隔离级别：

脏读：将一个事务读取到了另一个事务。

两个事务之间操作的时候发生了混淆。

不可重复读：在一个事务内读取表中的某一行数据时，多次读取结果不同。（并不代表一定是错误，有些时候只是场合不对。）

虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行）

脏读和虚读都是两个事务之间发生了混淆，不过脏读中数据变量没有变，即还是 A B C，而虚读是指读取了这个事务中不该有的数据，变成了 A B C D。

1.2、测试事务实时转账

在MySQL中，默认的是 开启事务自动提交。

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

进行事务的流程： 在使用事务时应该先关闭自动提交。

– 手动处理事务
set autocommit =0 – 关闭自动提交

– 事务开启
start transaction – 标记一个事务的开始，从这个之后的 sql 都在同一事务内
insert xxx
insert xxxx

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

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

– 事务结束
set atuommit=1 – 开启自动提交

– 保存点
savepoint 保存点名 – 设置一个事务的保存点
rollback to savepoint 保存点名 – 回滚到保存点
release savepoint 保存点名 – 撤销保存点

例子： 模拟转账事务

```sql
############## 模拟事务  ##########
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci  -- 创建 shop数据库
USE shop

-- 创建账单 account  数据表
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',1000); 
-- 此时输入的A是字符串的意思，并不是表名，所以用单引号，不用`。

-- 模拟转账： 事务
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; -- 恢复默认值，开启自动提交

结果：

2、索引

2.1、索引简介和分类

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

索引的本质：索引是数据结构。

建立了索引，相当于在获取数据的时候读取键值，和python中字典类似，一个键值对？这样读取速度会变快。

索引的作用：

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化.

索引的分类

• 主键索引 （primary key）：唯一的标识，主键不可重复，只能有一个列作为主键

• 唯一索引（unique key）：避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

• 常规索引（key/index）：默认的，index，key关键字来设置

• 全文索引（FUllText）：在特定的数据库引擎下才有，MyISMA。用于快速定位数据

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

SQL中的索引分为两种，聚焦索引和非聚焦索引。 这个参考链接为：https://blog.csdn.net/happyheng/article/details/53143345

聚焦索引：

• 聚焦索引 可以认为就是 顺序排列。 例如我们通常将 id 设为 自增列，主键自增的就是 聚焦索引，且只能有一个主键即一个聚焦索引。 因为这个是顺序排列的， 所以你知道了 id = 1， 想找第100条数据，直接找 id=100 的时候就可以了，一次就能查询出来。

非聚焦索引：

• 非聚焦索引 可以理解为是一种 有序的目录。例如 表studen中，id为主键， 但是 id_grade即对应的年级，这不是主键，那么在存储的时候就是无序的。 例如id为1的 id_grade可能是大一， id为2的人id_grade可能为大四。 那么我们在寻找id_grade=大四的人，就只能去进行遍历， 不能直接顺序查找快速得到。
• 所以我们需要为这个 id_grade 去增加非聚焦索引，然后就会对 id_grade进行排序， 对它自己这个产生的目录去进行查询就行了。 所以，在这种的非聚焦索引中，不重复的数据越多，查询的速度越快， 效率越高。

基础语法的例子：

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

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

-- 创建普通索引 
-- CREATE INDEX(索引名) ON 表明(列名1， 列明2)
-- 修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1，列名2,...);
CREATE INDEX studentName ON student(NAME);
ALTER TABLE school.student ADD INDEX PASSWORD(pwd);
SHOW INDEX FROM student;

-- 增加一个全文索引
-- ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(列名1, 列名2...);
ALTER TABLE school.student ADD FULLTEXT INDEX gogogo(sex, address);

-- 移除索引  
-- DROP INDEX index_name ON talbe_name
-- ALTER TABLE table_name DROP INDEX index_name 
DROP INDEX studentName ON student;
DROP INDEX studentName1 ON student;
ALTER TABLE student DROP INDEX studentPassword;
SHOW INDEX FROM student;

2.2、SQL编程创建100万条数据测试

########## 创建100万数据  #########
DELIMITER $$ -- 写函数之前必须写，标志

CREATE FUNCTION mock_data()
RETURN INT
BEGIN
   DECLARE num INT DEFAULT 1000000;
   DECLARE i num INT DEFAULT 0;
   
   WHILE i < num DO
     -- 插入数字, i默认为0递增，
          INSERT INTO app_user(`name`, `email`,
          SET i = i+1;
    END WHILE

2.3、索引原则

索引原则

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

索引的数据结构

Hash 类型的索引

Btree:innodb 的默认数据结构

3、数据库管理

3.1、权限管理和备份

主机这里选择的是什么，在登陆的时候就选择什么。点击+号进行新的连接：

3.2、用户管理

--创建用户

-- 创建用户 create user 用户名 identified by ‘密码’
create user ALZN identified by '123456'

CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
  - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
  - 只能创建用户，不能赋予权限。
  - 用户名，注意引号：如 'user_name'@'192.168.1.1'
  - 密码也需引号，纯数字密码也要加引号
  - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
  
-- 修改密码（修改当前用户名密码）set password= password('新密码')
SET PASSWORD= PASSWORD('123456')
-- 修改密码（修改指定用户名密码）
set password for root = password('123456')

-- 重命名 rename user 原用户名 to 新用户名
rename user root to rootdouble

-- 用户授权 grant all privileges 全部的权限 库.表
-- 除了给别人授权，其他都能够干
grant all privileges on *.* to ALZN
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges 表示所有权限
  - *.* 表示所有库的所有表
  - 库名.表名 表示某库下面的某表
  
-- 查询权限
show grants FOR ALZN -- 查看制定用户的权限
show grants for root@localhost

-- root 用户的权限
-- 撤销权限 revoke 那些权限， 在哪个库撤销 给谁撤销
revoke all privileges on *.* from ALZN
-- 撤销权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

-- 删除用户
drop user kuangshen

3.3、MySQL备份

mysql备份

为什么要备份：

• 保证重要的数据不丢失
• 数据转移

MySQL 数据库备份的方式

• 直接拷贝物理文件
• 在可视化工具中手动导出
• 使用命令行导出 mysqldump 命令行使用

备份方式的例子：

在可视化工具中导出：

在cmd 命令窗口进行导出：

在命令窗口进行导入：

需要先将mysql进行登录：mysql -u用户名 -p密码 库民<备份文件

mysql -u root -p123456

# mysqldump -h 主机   -u 用户名   -p 密码   数据库  表名   > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/1.sql
导出 从本地导出 用户名 密码 数据库名 表名 》指向 path（导出的路径）

（导出多张表，在表名后空格 表名）
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
　　
 \# 导入
 \# 导出
 \# source 备份文件
 1. 在登录mysql的情况下：
 source d:1.sql
2. 在不登录的情况下：
mysql -u用户名 -p密码 库名<备份文件

4、如何设计一个数据库

4.1、规范的数据库设计

我们为什么要去设计数据库呢？ 当数据库比较复杂的时候，就需要我们去进行设计。这是因为：

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦、异常【屏蔽使用物理外界】
• 程序的性能差

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性
• 方便我们开发系统

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计:设计关系图E-R图

4.2、数据库的三大范式

三大范式

为什么需要数据规范化?

不合规范的表设计会导致的问题：

• 信息重复

• 更新异常

• 插入异常

• 无法正确表示信息

• 删除异常

• 丢失有效信息

第一范式 (1NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式(3NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范化和性能的关系：

关联查询的表不得超过三张表

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

对于设计一个关于博客的数据库：

设计数据库的步骤：

• 收集信息，分析需求

• • 用户表（用户登陆注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章的信息）
  • 评论表
  • 友链接（友链信息）
  • 自定义表（系统信息，某个关键的子，或者一些主字段）key: value

• 标识实体（把需求落地到每个字段）

• 标识实体之间的关系

• • 写博客：user–>blog
  • 创建分类：user -->category
  • 关注：user–>User
  • 友链： links
  • 评论： user-user-blog