MySQL学习笔记三（事务、索引、权限管理与备份、三大范式，插入100万条数据，阿里规范）

于 2022-08-05 17:41:28 发布
阅读量202 收藏
点赞数 1
分类专栏： MySQL 文章标签： mysql 学习 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ZhangZhenBo99/article/details/126181491
版权

事务、索引、权限管理与应用、三大范式，插百万数据，阿里规范

6. 事务

6.1 什么是事务

  1. 事务就是将一组SQL语句放在同一批次内去执行 经典例子：转账
  2. 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
  3. MySQL事务处理只支持InnoDB和BDB数据表类型
    事务其实就是最小的不可分割的工作单元，事务能够保证一个业务的完整性。

事务的原则：
ACID原则：原子性，一致性，隔离性，持久性 （脏读，幻读）
原子性（Atomicity）：
一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
要么都不成功，要么都失败
一致性（Consistency）：
一方面，在事务开始之前和事务结束以后，数据库的完整性没有被破坏；另一方面，写入的数据必须完全符合所有的预设规则。
事务前后数据完整性要保证一致
隔离性（Isolation）：
不同的会话或线程，操作数据库的时候可能产生多个事务。如果同时操作一张表或同一行数据，必然产生并发或干扰操作。隔离性要求事务间对表或数据操作是透明的，互相不存在干扰的，通过这种方式保证一致性。
事务的隔离性是多个用户并发数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离
持久性（Durability）：
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
不可逆

6.2 隔离导致的一些问题

脏读：
指一个事务读取了另外一个事务未提交的数据
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定错误，只是某些场合不对）
虚读（幻读）：
是指在一个事务内读取了别的事务插入的数据，导致前后读取不一致。

6.3 执行事务的原理与模拟场景

-- 执行事务的原理：
-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

注意：

1.MySQL中默认是自动提交
2.使用事务时应先关闭自动提交

-- 开始一个事务,标记事务的起始点
START TRANSACTION  

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

保存点（了解即可）

SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

模拟场景！！！
模拟转账 首先建立数据库

-- 转账 
-- 创立数据库
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',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' -- A加500

COMMIT; -- 提交事务以后，被持久化！
ROLLBACK;-- 回滚

7. 索引

MySQL官方对索引的定义为：
索引（index）是帮助MySQL高效获取数据的数据结构。 原本0.5s 使用后0.00001s
提取句子主干，就可以得到索引的本质：索引是数据结构
索引的作用：

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

7.1 索引的分类

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

索引可以提高查询速度，但是会影响插入记录的速度。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

基础用法：
– 索引的使用：
– 1. 在创建表的时候给字段增加索引
– 2.创建完毕以后，增加索引

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

-- 增加一个索引
ALTER TABLE school1.`student` ADD FULLTEXT INDEX `studentname`(`studentname`);


-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT *FROM student; -- 非全文索引

SELECT * FROM student WHERE 

EXPLAIN SELECT *FROM student WHERE MATCH(studentname)AGAINST('刘');

7.2 测试索引（插入100万条数据）

创建表：

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用户表'

插入100万条数据

DROP FUNCTION IF EXISTS mock_dataa;
DELIMITER $$
CREATE FUNCTION mock_dataa()
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),'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_dataa();


-- 查看数据看其耗时
SELECT *FROM app_user WHERE `name` = '用户9999';  -- 2.339 sec
-- 展示查询多少数据以后出现结果
EXPLAIN SELECT *FROM app_user WHERE `name` = '用户9999';

-- id _ 表名 _ 字段名
-- create INDEX 索引名 on表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT *FROM app_user WHERE `name` = '用户9999'; -- 0.001 sec
EXPLAIN SELECT *FROM app_user WHERE `name` = '用户9999';

总结：索引在小数据量的时候，用处不大，但是在大数据的时候，效果明显

7.3 索引的原则

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

8. 权限管理与备份

8.1 用户管理

SQLyog可视化管理
操作小人添加删除，自己定义功能和密码用户名
在这里插入图片描述
SQL命令操作

SQL命令操作：
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY'密码'
CREATE USER zhanzhenbo IDENTIFIED BY'123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('密码')

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR zhangzhenbo = PASSWORD('密码')

-- 用户重命名  RENAME USER 原名字 TO 新名字
RENAME USER zhangzhenbo TO zhenbozhang

-- 用户授权  ALL PRIVILEGES  全部的权限 ， 库，表
-- ALL PRIVILEGES 除了给被人授权，其余都能干
GRANT ALL PRIVILEGES ON *.* TO zhenbozhang

-- 查询权限
SHOW GRANTS FOR zhenbozhang  -- 查看指定用户权限
SHOW GRANTS FOR root@localhost 

-- Root用户权限：GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
-- 撤销权限 REVOKE 那些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM zhenbozhang

-- 删除用户
DROP USER zhenbozhang

权限列表

-- 权限列表
ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限
ALTER    -- 允许使用ALTER TABLE
ALTER ROUTINE    -- 更改或取消已存储的子程序
CREATE    -- 允许使用CREATE TABLE
CREATE ROUTINE    -- 创建已存储的子程序
CREATE TEMPORARY TABLES        -- 允许使用CREATE TEMPORARY TABLE
CREATE USER        -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW        -- 允许使用CREATE VIEW
DELETE    -- 允许使用DELETE
DROP    -- 允许使用DROP TABLE
EXECUTE        -- 允许用户运行已存储的子程序
FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX     -- 允许使用CREATE INDEX和DROP INDEX
INSERT    -- 允许使用INSERT
LOCK TABLES        -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS     -- 允许使用SHOW FULL PROCESSLIST
REFERENCES    -- 未被实施
RELOAD    -- 允许使用FLUSH
REPLICATION CLIENT    -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE    -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT    -- 允许使用SELECT
SHOW DATABASES    -- 显示所有数据库
SHOW VIEW    -- 允许使用SHOW CREATE VIEW
SHUTDOWN    -- 允许使用mysqladmin shutdown
SUPER    -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE    -- 允许使用UPDATE
USAGE    -- “无权限”的同义词
GRANT OPTION    -- 允许授予权限

8.2 MySQL备份

为什么要备份：
 保证重要数据不丢失
 数据转移 AB
MySQL数据库备份的方式：
 直接拷贝物理文件
 在SQLyog 这种可视化工具中手动导出
 使用命令行导出 mysqldump 命令行使用
导出数据库
：mysqldump -root -ppassword school student result >D:/s.sql
mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 >物理磁盘位置/文件名
导出多张表 直接加表名，空格分开就可以

mysqldump -root -ppassword school student >D:/s.sql
mysqldump -h 主机 -u 用户名 -p 密码 数据库 >物理磁盘位置/文件名

导入数据库
登陆的情况下，切换到指定的数据库
Source 备份文件
Source d:/a.sql （登录的情况下）
Mysql -u用户名 -p密码 库名<备份文件

假设你要备份数据库，放置数据丢失，把数据库给朋友，sql文件给别人即可

9. 规范数据库设计

糟糕的数据库设计：

  1. 数据冗余，浪费空间
  2. 数据库插入和删除都会很麻烦，可能出现异常【屏蔽使用物理外键】
  3. 程序性能差

良好的数据库设计：

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

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

  1. 分析需求：分析业务和需要处理的数据库的需求
  2. 概要设计：设计关系E-R图（流程图）

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

	收集信息，分析需求
   	用户表（用户登录注销，用户的个人信息，写博客，创建分类）
   	分类表（文章分类，谁创建的）
   	文章表（文章的信息）
   	评论表
   	友情链接表（友链信息）
   	自定义表（系统信息，某个关键的字，或者一些主字段）key：value
	标识实体（把需求落地到每个字段）
	标识实体之间的关系
   	写博客：user-->blog
   	创建分类：user-->category
   	关注：user-->user
   	友情链接：links
   	评论：user-user-blog

9.2 三大范式

为什么要数据规范化？

  • 信息重复

  • 更新异常

  • 插入异常
    -无法正常显示信息

  • 删除异常
    - 丢失有效信息

三大范式：

第一范式（1NF）
原子性：保证每一列不可再分

第二范式（2NF）
前提：满足第一范式
每张表只描述一件事情

第三范式（3NF）
前提：满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

阿里规范性和性能的问题：
阿里的规定：关联查询到额表不得超过三张表

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值