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

最新推荐文章于 2024-05-28 17:10:36 发布
最新推荐文章于 2024-05-28 17:10:36 发布
阅读量175 收藏
点赞数 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
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【Mysql】大批量(百万级)数据插入数据库应该怎么做(提高效率)？
小树じ的博客
04-07 1万+
一：在数据库中进行操作 1.合并sql语句(一个sql插入单条数据转为一个sql插入多条数据) 一个sql插入一条数据： insert into table_name(id,name) values(1,"小明"); insert into table_name(id,name) values(2,"小红"); 一个sql插入多条数据: insert into table_name(id,name) values(1,"小明")(2,"小红"); 原因：合并后减少了日志生成量 2.
百万级MySQL的数据量，该如何快速的完成数据迁移？
emprere的博客
07-18 1338
往期热门文章：1，《往期精选优秀博文都在这里了！》2、呕心沥血总结的14张思维导图，教你构建 Python核心知识体系（附高清下载）3、一份来自亚马逊技术专家的Google面试指南，Gi...
Mysql学习（4）——MySQL设计规范总结
qq_35854212的博客
11-18 403
一、数据库命令规范 1、所有数据库对象名称必须使用小写字母并用下划线分割 2、所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 3、数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 4、临时库表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期(时间戳)为后缀 5、所有存储相同数据的列名和列类型必须一致（一般作为关...
向MySQL插入100w条数据，该怎么做？
坐上高铁闯红灯的博客
12-06 3574
使用MySQL存储过程插入 那么从sql层面上我们该怎么插入100万条数据呢，无脑直接写个循环？那就使用存储过程试试看吧 DROP PROCEDURE IF EXISTS proc_insert() DELIMITER $ CREATE PROCEDURE pro_insert2() BEGIN DECLARE i INT DEFAULT 1; WHILE i<=1000000 DO INSERT INTO student VALUES(i,'马老师','1970-02-02','广东深圳',60,6
【MySQL】5秒插入100万行数据
BLAZAR'
12-20 1600
数据库 CREATE TABLE `product` ( `pid` int NOT NULL AUTO_INCREMENT, `pname` varchar(64) DEFAULT NULL COMMENT '商品名', `price` double DEFAULT NULL COMMENT '价格', PRIMARY KEY (`pid`) ) ENGINE=InnoDB AUTO_INCREMENT=1048561 DEFAULT CHARSET=utf8mb4 COLLATE=utf
狂神Java学习笔记————MySQL（五）————事务，索引，用户管理，数据库备份和规范数据库数据（三大范式）
hyx的博客
06-23 383
事务 要么都成功，要么都失败（类似try代码块？） 一一一一一一一一一一一一一 A给B转账 SQL1 A账面转出 SQL2 B账面收到 一一一一一一一一一一一一一 若SQL2执行时或执行前出现错误，则SQL1也不生效，否则，A账面白白减少。 事务原则：ACID原则：原子性，一致性，隔离性，持久性（脏读，幻读） 原子性：对于一个事务中的多个动作，要么都成功要么都失败，不可分离 一致性：针对一个事务操作前与后的状态一致 持久性：事务结束后的数据不会因外界原因导致数据丢失（若事务没有提交则应该恢复原状，否则保
Mysql索引学习笔记
G189D的博客
08-03 1362
MySQL教程,非常完整,适合初学者学习与复习
MySQL 索引学习笔记
m0_47396773的博客
03-19 797
MySQL 索引学习笔记
教你如何6秒钟往MySQL插入100万条数据的实现
09-08
主要介绍了教你如何6秒钟往MySQL插入100万条数据的实现，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
MySQL学习笔记之为什么表数据删掉一半，表文件大小不变（十三）
12-14
1.参数 innodb_file_per_table ...索引在InnoDB里的数据是用B+树存储的。 当我们删除R4这个记录，InnoDB引擎会把R4,标记为删除，以后再插入一个 ID 在 300-600之间的记录，就会复用这个位置。但是磁盘文件的大小不会
3分钟如何向MySQL数据库中插入100万条数据
12-14
一、编写测试案例向MySQL数据库中插入百万条数据。测试数据表建表脚本如下：use db_xk; drop table if exists tb_test2; create table tb_test2 ( id int primary key auto_increment， subject varchar(50) ...
MySQL学习笔记：插入数据
12-14
本文为本人学习书籍《MySQL必知必会》笔记系列，欢迎持续关注，有问题随时留言评论，一起探讨学习~ 19 插入数据 数据插入INSERT分为： 插入完整的行； 插入行的一部分； 插入多行； 插入某些查询结果 19.1插入完整的...
MySQL创建存储过程批量插入10万条数据
01-19
MySQL创建存储过程批量插入10万条数据 存储过程 1、首先防止主键冲突，我们清空表。 TRUNCATE table A_student; 2、编写存储过程 delimiter ‘$’; CREATE PROCEDURE batchInsert(in args int) BEGIN declare i int ...
mysql支持的存储引擎有哪些
JustinMars的博客
05-28 36
选择存储引擎时应根据具体应用场景和需求来决定。例如： - 如果需要事务和高数据完整性，选择InnoDB。 - 如果读操作频繁且不需要事务，选择MyISAM。 - 如果需要快速的临时数据存储，选择MEMORY。 - 如果需要数据压缩和存档，选择ARCHIVE。
【MySQL精通之路】SQL优化(1)-查询优化(1)-WHERE子句
Anakki的博客
05-22 623
与PRIMARY KEY或UNIQUE索引上的WHERE子句一起使用的表，其中所有索引部分都与常量表达式进行比较，并定义为NOT NULL。由于MySQL会自动进行类似的优化，因此您通常可以避免这项工作，并将查询保留为更易于理解和维护的形式。因为MySQL优化器的开发工作还在进行中，所以这里并没有记录MySQL优化器所有的优化。您可能会试图重写查询以加快算术运算，同时牺牲可读性。，这有助于简化join连接。有关更多信息和示例，请参见“有关更多信息，请参见“空表或只有一行的表。
mysql内存结构
m0_74163986的博客
05-22 307
2.mysql8开始有change buffer(只针对非唯一的二级索引页)，dml语句时，如果数据页没在buffer pool中，则先在change buffer中缓存数据的变更，未来读取数据的时候再合并恢复到buffer pool，然后刷新到磁盘。1.buffer pool,缓冲池，主内存的一个区域，缓存磁盘上经常操作的真实数据，是增删改查首先操作数据的地方（如果没有数据则先从磁盘加载），在缓冲池修改完后再以一定频率刷新到磁盘，减少磁盘io，加快处理速度。段：分为数据段，索引段，回滚段。
mysql实战——mysql主从复制管理
最新发布
zyjzyjjyzjyz的博客
05-28 367
一、常见的管理操作1、查看主库状态2、查看从库复制的状态3、在主库上查看从库ip和端口信息。
c# mysql 加锁解锁
photon222的博客
05-21 256
然后使用LOCK TABLES语句对mytable进行写操作锁定，这将阻止其他事务对该表进行写操作。在完成了需要执行的业务逻辑之后，使用UNLOCK TABLES解锁表，并提交事务。在C#中操作MySQL实现加锁和解锁，通常是通过执行特定的SQL语句来完成。MySQL支持表级锁定和行级锁定。请注意，行级锁定通常是通过在SELECT语句中使用FOR UPDATE子句来实现的，这通常在事务中进行处理。务必确保在使用事务和锁定的时候遵循最佳实践，例如尽可能减少锁定的时间和范围，以避免阻塞其他数据库操作。
如何往mysql数据库中插入100万条数据
03-11
可以使用以下步骤往mysql数据库中插入100万条数据： 1. 创建一个表格，定义好需要插入的字段和数据类型。 2. 使用循环语句，往表格中插入数据，每次插入一条数据。 3. 在循环语句中，使用事务来保证数据的完整性和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值