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

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 设计数据库的步骤:(举例个人博客)

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

9.2 三大范式

为什么要数据规范化?

  • 信息重复

  • 更新异常

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

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

三大范式:

第一范式(1NF)
原子性:保证每一列不可再分

第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情

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

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值