【Mysql】事务和索引 权限管理与备份的学习笔记

事务和索引 权限管理与备份

什么是事务Transaction

• 事务就是将一组SQL语句放在同一批次内去执行
• 同生共死

举个栗子春暖花开
------------------------------------------
sql执行 A给B转账  A(1000)  B(200)  A->200->B
sql执行 B收到A转账 A(800)  B(400)
-------------------------------------------
这两个sql语句必须同时成功 或者 同时失败 就是一个事务

事务的特性(原则)ACID

• 原子性Atomicity
  • 事务是一个整体，要么全部执行，要么全部不执行
  • 如果执行过程中出错，则应该回滚rollback到事务开始前的状态
• 一致性Consistency
  • 事务前后的数据完整性要保持一致
  • 例如:事务执行前A、B的存款都是100，事务内容是A转账B，则事务执行完成之后，需要满足A和B的存款和还是200
• 持久性Duration
  • 事务没有提交，数据被恢复到原状
  • 事务已经提交，数据的变化被持久化到数据库
• 隔离性Isolation
  • 不用的事务之间互不干扰

三种并发读(隔离所导致的一些问题)

• 脏读
  • 一个事务处理过程中，读取了另一个事务还未提交的数据
  • 针对还未提交的数据
• 不可重复读
  • 一个事务处理过程中，对同一数据的多次查询得到不同的结果，这是由于另一个事务在多次查询间隔修改了这个数据
  • 针对被修改且已提交的数据
• 幻读
  • 一个事务处理过程中，读取了另一个事务新增加或删除的数据，导致前后读取不一致
  • 针对新增删且已提交的数据

--===============================事务=================================
1.使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/
--注意:
--1.MySQL是默认开启事务自动提交的
--2.使用事务时应先关闭自动提交
2.事务开启与结束
SET autocommit = 0  --关闭事务自动提交
START TRANSACTION  --标记一个事务的开启，从这个之后的sql都在同一个事务中
INSERT xx
INSERT xx
COMMIT  --提交:提交一个事务给数据库(成功)
ROLLBACK  --回滚:数据回到本次事务的初始状态(失败)
SET autocommit = 1  --标记事务的结束，开启事务自动提交
--==================了解知识(保存点)==================
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
3.模拟转账的案例
/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `cash` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO account(`name`,`cash`) VALUES('A',2000.00),('B',10000.00);
-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
rollback;  --回滚
SET autocommit = 1; -- 恢复自动提交

事务隔离级别

• 三种并发读与四种事务隔离级别的总结
  

索引

• 索引的作用
  • 提高查询速度
  • 确保数据的唯一性
• 索引的分类与基本语法

1.主键索引 Primary Key
主键索引只能有一个，对应的列的值不能重复
2.唯一索引 Unique
唯一索引可能有多个，每个唯一索引对应的列的值不能重复
3.常规索引 Index/Key
默认的 每一列的值可以相同
4.全文索引 FullText
快速定位特定数据
5.基本语法
SHOW INDEX FROM student;  --显示所有的索引信息
CREATE INDEX id_app_user_name ON app_user(name);  --创建普通索引  CREATE INDEX 索引名 on 表(字段)
DROP INDEX 索引名 ON 表名;  --删除索引
ALTER TABLE 表名 DROP PRIMARY KEY;  --删除主键索引
EXPLAIN SELECT * FROM student;  --EXPLAIN分析sql语句执行性能
ALTER TABLE student ADD FULLTEXT INDEX stuname(stuname);  --增加一个全文索引 INDEX 后面的为索引名，括号里为列名
EXPLAIN SELECT *FROM student WHERE MATCH(stuname) AGAINST('love');  --使用全文索引
-- 全文搜索通过 MATCH() 函数完成
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。

• 索引效率的测试

1.批量插入100W条数据
--建表app_user
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';
--批量插入数据100w条
DROP FUNCTION IF EXISTS mock_data;
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), '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();
2.效率测试
--无索引
SELECT * FROM app_user WHERE name = '用户9999'; --0.993sec
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'; --rows: 992759  992759次找到
--创建普通索引  CREATE INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(name);
SELECT * FROM app_user WHERE name = '用户9999'; --0.001sec
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'; --rows: 1  1次找到

• 索引创建的原则
  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表建议不要加索引
  • 索引一般应加在查找条件的字段

用户权限管理

--用户信息表
mysql.user
--创建用户  CREATE USER 用户名 IDENTIFIED BY [PASSWORD]
CREATE USER kuangshen IDENTIFIED BY '123456';
--删除用户
DROP USER 用户名
--修改当前用户密码
SET PASSWORD = PASSWORD('密码');
--修改指定用户密码
SET PASSWORD FOR 用户名 = PASSWORD('密码');
--重命名用户
RENAME USER old_user TO new_user
--用户授权  ALL PRIVILEGES所有权限
GRANT ALL PRIVILEGES ON *.* TO 用户名;
--查询权限
SHOW GRANTS FOR 用户名;  --查看指定用户权限
SHOW GRANTS FOR root@localhost;  --查看当前用户权限
--撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM 用户名;  --撤销所有权限

数据库备份

• 为什么需要数据库备份？
  • 保证重要数据不丢失
  • 数据转移
• 数据库备份的方式
  • 直接拷贝物理文件
  • 数据库管理工具 如sqlyog导出
    • 在需要备份的表上，右键，选择备份或导出
  • 命令行导出 mysqldump命令行使用

mysqldump命令行使用方法
1.导出
mysqldump -h主机 -u 用户名 -p 密码 数据库名 (表名,表名,表名...) >保存位置
--举个栗子春暖花开:`mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql`
2.导入
======================登录mysql情况下======================
source 备份文件路径
--具体步骤 1 登录 2 切换到指定数据库 3 source d:/a.sql
=====================不登录mysql情况下=====================
mysql -u用户名 -p密码 数据库名 < 备份文件路径