MySQL(五) 事务，索引，用户管理和备份

事务

要么都成功，要么都失败（类似try代码块？）
一一一一一一一一一一一一一

• A给B转账
  - SQL1 A账面转出
  - SQL2 B账面收到
  一一一一一一一一一一一一一
  若SQL2执行时或执行前出现错误，则SQL1也不生效，否则，A账面白白减少。

事务原则：ACID原则：原子性，一致性，隔离性，持久性（脏读，幻读）

• 原子性：对于一个事务中的多个动作，要么都成功要么都失败，不可分离
• 一致性：针对一个事务操作前与后的状态一致
• 持久性：事务结束后的数据不会因外界原因导致数据丢失（若事务没有提交则应该恢复原状，否则保持改变）
• 隔离性：针对多个用户同时操作，主要排除事务间的相互影响

脏读：读取了其他事务还未提交的数据
不可重复读：在一个事务内多次读取同一数据时结果不同
幻读：读取到其他事务插入的数据，导致前后读取结果不一致

-- ============事务===========
-- mysql默认开启事务自动提交
SET autocommit = 0;-- 关闭事务自动提交
SET autocommit = 1;-- 开启事务自动提交

-- 手动处理事务
SET autocommit = 0;-- 1.关闭事务自动提交
-- 事务开启
START TRANSACTION; -- 2.标记一个事务的开始
UPDATE xxx;-- 3. 事务内容
UPDATE xxx;
-- 提交：持久化（成功）
COMMIT;
-- 回滚：回到执行前的状态（失败）
ROLLBACK;
-- 事务结束
SET autocommit = 1;-- 开启事务自动提交

-- 了解内容：
SAVEPOINT xxx; -- 设置一个名为xxx的保存点
ROLLBACK TO SAVEPOINT xxx;-- 回滚到xxx保存点
RELEASE SAVEPOINT xxx;-- 删除保存点xxx

模拟场景

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

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


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

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

COMMIT; -- 提交事务，就被持久化了
ROLLBACK;-- 回滚
SET autocommit=1; -- 恢复默认值

索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。即索引其实是一种数据结构。

索引的分类

常用函数不常用，唯一索引不唯一

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

测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 取消函数声明
DROP FUNCTION mock_data;
-- 插入100万数据.
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),'19224305@qq.com','123456789',FLOOR(RAND()*2),RAND()*999999,FLOOR(RAND()*90));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data(); -- 执行此函数 生成一百万条数据

测试索引查询效率

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999';
-- rows=993816 select用时0.472sec
-- 添加索引
CREATE INDEX id_app_user_name ON app_user(`name`);
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999';
-- rows=1 select用时0.001 sec

索引原则

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

底层数据结构：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

权限管理和备份

用户管理

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

• 创建用户 修改密码和重命名

-- 创建用户
CREATE USER hyx IDENTIFIED BY '123456';
-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456');
-- 修改制定用户密码
SET PASSWORD FOR xxx = PASSWORD('123456');
-- 重命名
RENAME USER xxx TO yyy;

• 授予和查看权限

-- 用户授权
-- 授予全部权限（在所有库中的所有表上） 给yyy（默认没有给别人授权的权限）
-- 要想加入grant权限 在创建语句后添加“with grant option”
GRANT ALL PRIVILEGES ON *.* TO yyy;
-- 查看权限
SHOW GRANTS FOR yyy;-- 查看指定用户的权限
SHOW GRANTS FOR root@localhost;-- 查看root权限

• 撤销权限和删除用户

 `-- 撤销权限revoke
-- 撤销所有权限 在所有库中所有表中 yyy的
REVOKE ALL PRIVILEGES ON *.* FROM yyy;
-- 删除用户
DROP USER yyy;`

数据库备份

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

导出

• 拷贝物理文件
• 使用可视化工具中，手动导出
• 使用命令行 mysqldump（cmd）

# mysqldump -h主机 -u用户名 -p密码 数据库名 表名 >保存路径/文件名
# 可以一次导出一个数据库的多张表，也可以去除表名直接导出数据库
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

导入

source 备份文件

规范数据库设计

糟糕的数据库设计：

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

良好的数据库设计：

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

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

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

设计数据库的步骤：（个人博客为例）

• 收集信息，分析需求
  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，创建者）
  • 文章表（文章的信息）
  • 友链表（友链信息）
  • 自定义表（系统信息，某个关键字，或者一些主字段） key：value
• 标识实体
  • 写博客：user→blog
  • 创建分类：user→category
  • 关注：user→user
  • 友链：links
  • 评论：user→user→blog
  • 。。。

三大范式

问题：

• 信息重复
• 更新异常
• 插入异常
• 删除异常

三大范式

第一范式（1NF）

• 要求数据库表中每一列都是不可再分的原子数据项
• 比如：若某表中学生班级格式为“X级X班”，可把其分为年级和班级两项，则不符合第一范式。
  第二范式（2NF）
• 在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码部分的函数依赖）
• 第二范式需要确保数据库表中每一列都与主键相关，而不能只与主键的一部分相关，而不能只与主键的一部分相关。
• 如图转自百度百科的例子，此表中货物类型和id共同组成主键，而注意事项一栏显然之和主键的一部分“货物类型”有关，因此此例不满足第二范式。
  
  第三范式（3NF）
• 在2NF基础上，任何非属性不依赖于其他非主属性（在2NF基础上消除传递依赖）
• 第三范式要求确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
• 如某学生表中属性有：学号、姓名、性别、年级、班级、班主任姓名、班主任年龄。其中班主任姓名和班主任年龄明显依赖于年级、班级，不符合第三范式。

（规范数据库的设计）

规范和性能的问题
阿里要求：关联查询的表不能超过三张

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