事务、索引、权限管理和备份-优快云博客

本文链接：https://blog.youkuaiyun.com/liujaiqi111/article/details/131732092

事务

什么是事务

要么都成功，要么都失败

SQL执行，A给B转账 A：800 B：200 A—200—>B
SQL执行，B收到A的钱 A：600 B：400

以上例子：

如果1执行成功，2执行失败，那么A的钱少了，B的钱也没有多。
如果2执行成功，1执行失败，那么A的钱没少，而B的钱变多了。

由此可见，两条语句必须要求要么都执行成功，要么都执行失败。

事务是一系列的动作，它们综合在一起才是一个完整的工作单元，这些动作必须全部完成，如果有一个失败的话，那么事务就会回滚到最开始的状态，仿佛什么都没发生过一样。在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

以下部分内容参考自博客：事务ACID理解_dengjili的博客-优快云博客

事务的原则

ACID原则：原子性，一致性，隔离性，持久性（脏读，幻读…)

原子性是事务的基础，持久性和隔离性是手段，一致性是目的。
- 原子性（Atomicity）
  
  要么都成功，要么都失败
- 一致性（Consistency）
  
  事务前后的数据完整性要保证一致
- 隔离性（Isolation）
  
  事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
- 持久性（Durability）
  
  事务一旦提交则不可逆，被持久化到数据库中。
隔离所导致的一些问题：
- 脏读：
  
  指一个事务读取了另外一个事务未提交的数据。
- 不可重复读：
  
  在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，知识某些场合不对）
- 幻读（虚读）
  
  是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

测试事务实现转账

-- ============ 事务 ===============
-- MySQL是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启（默认）

-- 手动处理事务
SET autocommit = 0
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的SQL都在同一个事务内

INSERT xx
START xx

-- 提交：持久化到数据库（执行成功）
COMMIT 
-- 回滚：回到原来的样子（执行失败）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交

-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 游戏中存档点，回滚到保存点
RELEASE SAVEPOINT 保存点名字 -- 删除释放某个保存点 注意：一个事务一旦结束，会自动删除该事务中所定义的所有保存点

模拟转账

-- ============== 模拟转账 ================
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
USE shop

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=utf8mb4

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; -- 恢复默认值

索引

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

通过索引可以更快速的获取数据库的结果。

索引的分类

MySQL索引有四种：PRIMARY、INDEX、UNIQUE、FULLTEXT，这四种都是单列索引，也就是他们都是作用于单个一列，所以也称单列索引；但是一个索引也可以作用于多个列上，称为组合索引或复合索引。

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

主键索引（PRIMARY KEY)
- 唯一的表示，主键是不可重复的，非空且唯一，一张表只能有一个主键，但主键可以由多个列组合而成，叫做联合主键（但极其不推荐）
唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，有多个列被表为唯一索引，声明唯一索引的列意味着这一列中每一行的数据都不能重复，但是可以为空。
常规索引（KEY / INDEX）
- 默认的，通过index或key设置，没有任何约束。
全文索引（FULLTEXT）
- 在特定的数据引擎下才有，如MyISAM。
- 快速定位数据。

索引的三种创建方式：

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

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

-- 增加一个索引的三种方式：
-- 1.在创建表的时候添加索引
-- 2.ALTER 修改表添加索引
-- 3.直接创建索引 create

-- 增加一个全文索引 	格式：索引名（列名）
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`); -- 方式2
CREATE INDEX id_app_user_name ON app_user(`name`); -- 方式3

-- EXPLAIN 分析SQL执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('赵')

测试索引

-- ============ 测试索引 =============
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用户表'

-- 使用函数插入100万条数据
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),'215465456@12132',CONCAT('151',FLOOR(RAND()*(999999999-100000000)+1000000000)),FLOOR((RAND()*2)),UUID(),FLOOR(RAND()*100));
		SET i = i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data();

SELECT * FROM app_user WHERE `name` = '用户9999'; -- 耗时0.588 sec

-- 创建索引，给表中的字段添加索引，方式三
-- id_表名_字段名
CREATE INDEX id_app_user_name ON app_user(`name`)
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; -- 耗时0.012 sec

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

索引原则

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

索引的数据结构：

Hash类型的索引
B+树，InnoDB默认的索引结构

权限管理和备份

用户管理

SQLyog可视化界面

SQL命令操作

用户表：mysql数据库下的user表

本质：读这张表进行增删改查

里面有各种权限，也对应着可视化界面中可以赋予的各种权限

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

-- 修改密码（当前用户）
SET PASSWORD = PASSWORD('11111') -- 此语句已经被淘汰了
-- 修改密码（指定用户）
SET PASSWORD FOR luck1y = PASSWORD('11111')

-- 给用户重命名
-- RENAME USER 原来的名字 TO 新的名字
RENAME USER luck1y TO luck1y777

-- 授予全部权限（除了给别人授权这个权限，其他权限都可以）
-- GRANT ALL PRIVILEGES ON 库.表 TO 用户名
GRANT ALL PRIVILEGES ON *.* TO luck1y777

-- 查询权限
SHOW GRANTS FOR luck1y777 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- 查看root的权限（root的权限有 grant）

-- 撤销权限
-- REVOKE ALL PRIVILEGES ON 库.表 FROM 用户名
REVOKE ALL PRIVILEGES ON *.* FROM luck1y777

-- 删除用户
DROP USER luck1y777

MySQL备份

为什么要备份：

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

MySQL数据库备份的方式

直接拷贝物理文件，data文件
在SQLyog这类可视化工具界面，在表或者数据库上右键，点击备份或导出（一般选择导出结构和数据）

使用命令行导出mysqldump（命令行，不是SQLyog可视化界面）

mysql5.7版本开始，考虑到mysql的安全性而更改对

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

# 导入/恢复数据库
# 登录情况下切换到指定的数据库 导入
source d:/a.sql