MySQL(事务、索引、三大范式)

---

1.事务

https://www.runoob.com/mysql/mysql-transaction.html

1.1 什么是事务

一组数据库操作语句就构成一个事务，即将一组SQL放在一组批次中执行，且这些SQL语句要么都成功，要么都失败。

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

事务4个原则(ACID)：

• 原子性(Atomicity)：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性(Consistency)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性(Isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
• 持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。事务一旦提交就不可逆。

事务隔离性----隔离级别：
MySQL事务隔离级别和实现原理
https://blog.csdn.net/dengjili/article/details/82468576

1.2 模拟事务

-- 事务基本流程 --

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

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

-- 2.开启事务
START TRANSACTION -- 接下来的sql都在同一个事务内

-- 3.成功则提交：持久化 COMMIT
-- 3.失败则回滚：回到起始 ROLLBACK
-- 事务保存点 SAVEPOINT 名称，可ROOLBACK TO SAVEPOINT 回滚。删除保存点RELEASE SAVEPOINT

-- 4.结束事务
SET autocommit = 1; -- 开启自动提交

模拟转账

-- 模拟转账
create database shop character set utf8 collate utf8_general_ci;
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=utf8;

insert into `account`(`name`,`money`) values ('A',1000.00),('b',2000);
UPDATE `account` set `name`='B' where id=2;

set autocommit = 0; -- 1.关闭自动提交
start transaction;  -- 2.开启事务
UPDATE `account` SET money=money-500 where `name`='A';
UPDATE `account` SET money=money+500 WHERE `name`='B';
commit; -- 3.提交事务
rollback; -- 3.回滚
set autocommit = 1; -- 4.结束事务，恢复默认

2.索引

索引(index)是帮助MySQL高效获取数据的数据结构。

2.1 索引的分类

• 主键索引(PRIMARY KEY)：主键不可重复，唯一标识
• 唯一索引(UNIQUE KEY)：避免重复的列出现，可重复
• 常规索引(KEY/INDEX)
• 全文索引(FULLTEXT)

官网

2.2索引的使用

mysql索引总结

-- 1.创建表时给字段增加索引
CREATE TABLE `student`(
    `studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

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

-- 2.创建完毕后，增加索引     `索引名`(`列名`)
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`);

EXPLAIN分析sql执行状况：【MySQL优化】——看懂explain

2.3 测试索引

-- 1.创建表时给字段增加索引
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：女）',
    `pwd` 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用户表';

SET GLOBAL log_bin_trust_function_creators = 1;
-- 插入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`,`pwd`,`age`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',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`='用户100000'; -- 0.946sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户100000'; -- 查了992906条数据
-- 创建索引第三种方式 create index 索引名 ON 表名(`字段`)
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name`='用户100000'; -- 0.002sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户100000'; -- 只查了一条数据

索引一般用在数据量很大时。索引好处很多，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

索引原则

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

MySQL索引背后的数据结构及算法原理

3.权限管理和备份

3.1 用户管理

1. SQLyog管理

2. SQL命令

用户表：mysql.user

本质：对这张表进行操作

-- 创建用户
CREATE USER kk IDENTIFIED BY '123456';
-- 重命名
RENAME USER kk TO kk1;
-- 授全部权限
GRANT ALL PRIVILEGES ON *.* TO kk1;
-- 查看权限
SHOW GRANTS FOR kk1;
SHOW GRANTS FOR root@localhost;
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM kk1;
-- 删除用户
DROP USER kk1;

3.2 MySQL备份

• 拷贝磁盘文件
• 在SQLyog手动导出
• 使用命令行导出 mysqldump

  # mysqldump -h 主机 -u 用户名 -p 数据库 表1 表2  > 导出的位置+文件名称
  mysqldump -h localhost -u root -p school student >D:/a.sql
  # 导入   source  -u 用户名 -p 库名< 文件位置 
  

4.规范数据库设计

4.1 数据库设计

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

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

4.2 三大范式

三大范式 https://www.cnblogs.com/wsg25/p/9615100.html

• 第一范式（1NF）要求数据库表的每一列都是不可分割的原子数据项。
• 第二范式（2NF）前提：满足第一范式。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。即每张表只描述一件事。
• 第三范式（3NF）前提：满足第一、二范式。确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范性和性能问题