MySQL的事务与索引

MySQL

1.事务

事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。

1.1 什么是事务

要么都成功要么都失败
事务就是将一组SQL语句放在同一批次内去执行
如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
MySQL事务处理只支持InnoDB和BDB数据表类型
- 1.SQL 执行 A给B转账 A 1000 --> B 200
- 2.SQL 执行 B收到A的钱 A 800 --> B400

1.2 事务原则： ACID原则原子性，一致性，持久性，隔离性（脏读，幻读…）

参考博客链接 https://blog.csdn.net/dengjili/article/details/82468576

原子性(Atomic)
- 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样
一致性(Consist)
- 一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。
持久性(Durable)
- 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
隔离性(Isolated)
- 隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

隔离导致的一些问题

脏读
- 指一个事务读取了另外一个事务未提交的数据
不可重复读
- 在一个事务内读取表中的某行数据，多次读取结果不同。（这个不一定是错误只是某些场合不对）
幻读（虚读）
-  是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行）
幻读和不可重复读有些类似，但是幻读强调的是集合的增减，而不是单条数据的更新。

1.3 事务隔离级别

为了解决以上的问题，主流的关系型数据库都会提供四种事务的隔离级别。事务隔离级别从低到高分别是：读未提交，读已提交，可重复读，串行化。事务隔离级别越高，越能保证数据的一致性和完整性，但是执行效率也越低，所以在设置数据库的事务隔离级别时需要做一下权衡

mysql默认是可重复读
读未提交
- 读未提交(Read Uncommitted)，是最低的隔离级别，所有的事务都可以看到其他未提交的事务的执行结果。只能防止第一类更新丢失，不能解决脏读，可重复读，幻读，所以很少应用于实际项目。

读已提交
- 读已提交(Read Committed)，在该隔离级别下，一个事务的更新操作只有在该事务提交之后，另外一个事务才可能读取到同一笔数据更新后的结果。可以防止脏读和第一类更新丢失，但是不能解决可重复和幻读的问题。
可重复读（重要）
- 可重复读(Repeatable Read)，mysql默认的隔离级别。在该隔离级别下，一个事务多次读同一个数据，在这个事务还没有结束时，其他事务不能访问该数据（包括了读写），这样就可以在同一个事务内两次读到的数据是一样的。可以防止脏读、不可重复读、第一类更新丢失，第二类更新丢失的问题，不过还是会出现幻读。

串行化
- 串行化(Serializable)，这是最高的隔离级别。它要求事务序列化执行，事务只能一个接着一个的执行，不能并发执行。在这个级别，可以解决上面提到的所有并发问题，但是可能导致大量的超时现象和锁竞争，通常不会用这个隔离级别。

1.4 测试事务实现转账

执行事务：相关命令

-- ==========事务====================
-- mysql默认开启事务自动提交的
  SET  autoccommit = 0;/*关闭*/
  SET  autoccommit = 1;/*开启（默认）*/
  -- s手动处理事务
  SET  autoccommit = 0; -- 关闭自动提交

--  事务开启
  START TRANSACTION  -- 标记一个事务的开始，从这个开始的sql  都在同一个事务中
  INSERT xx
   INSERT  yy

--  提交  持久化（成功）
 COMMIT
-- 回滚   回到原来的样子（失败）

ROLLBACK
--  事务结束
 SET  autoccommit = 1;-- 开启自动提交

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

模拟场景

/*
课堂测试题目

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';-- A减500
UPDATE account SET cash=cash+500 WHERE `name`='B';-- B加500
COMMIT; -- 提交事务就会被持久化了
# rollback; --回滚
SET autocommit = 1; -- 恢复自动提交

2.索引

2.1 索引的介绍

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构
提取句子主干就可以得到索引的本质： 索引是数据结构

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

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引
常规索引（KEY/INDEX）
- 默认的，index,key关键字来设置
全文索引（FULLTEXT）
- 在特定的数据库引擎下才有, MySAM
- 快速定位数据

基础语法

/*
#方法一：创建表时
  　　CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );


#方法二：CREATE在已存在的表上创建索引
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(长度)] [ASC |DESC]) ;
                           
                           
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#显示索引信息: SHOW INDEX FROM student;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/


-- 索引的使用
-- 1、在创建表的时候给字段增加索引
--  2、创建完毕后  增加索引

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

--  增加一个索引 (索引名：列名)
ALTER TABLE school.`student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
--  EXPLAIN 分析SQL执行的状况
EXPLAIN SELECT * FROM student;  -- 非全文索引
EXPLAIN  SELECT * FROM  student WHERE MATCH(StudentName)  AGAINST('韩')

2.2 测试索引

创建使用的表

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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

插入100万数据

-- 插入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();

索引效率测试

-- 加索引前
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.159 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.642 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.631 sec

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';-- 分析看到查询了99999条记录

SELECT * FROM student

-- id _ 表名 _ 字段名
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);

-- 加索引后
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.009 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; -- 查询一条记录

索引在小数据量的时候，用处不大，但是再大数据的时候，区分十分明显

2.3 索引原则

索引不是越多越好
不要对经常变动的数据加索引

小数据量的表不需要加索引

索引一般加在常用来查询的字段上

索引的数据结构

Hash类型的索引
Btree：InnoDB默认的

参考博客：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

3.MySQL权限管理与备份

3.1 用户管理

方式一：SQLyog可视化管理
方式二： SQL命令操作

用户表：mysql.user
- 本质：对这张表的增删改查

-- 创建用户  CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER sanjin IDENTIFIED BY '123456'

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


-- 修改密码（修改指定用户密码）

SET PASSWORD FOR sanjin = PASSWORD('111111')


-- 重命名  rename user 原名字 to 新名字
RENAME USER sanjin TO sanjin2

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

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

-- ROOT用户权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM sanjin2

-- 删除用户
DROP USER sanjin2

以上的操作都是物理外键，数据库级别外键，我们不建议使用！（避免数据库过多造成困扰）

最佳实践
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）。
- 我们想使用多张表的数据，想使用外键（程序去实现）。

3.2 MySQL 备份

为什么要备份：
- 保证重要的数据不丢失
- 数据转移

mysql数据库备份的方式
- 直接拷贝物理文件
- 在sqlyog这种可视化工具中手动导出:在想要导出的表或者库中，右键选择备份或导出
- 使用SQL命令行 mysqldump导出

#导出一张表
#mysqldump -h 主机 -u 用户名 -p 密码 数据库名 表名 >导出位置
mysqldump -hlocalhost -uroot -p123456 myschool student >D:/a.sql

# 多张表
#mysqldump -h 主机 -u 用户名 -p 密码 数据库名 表1 表2 表3 >导出位置
mysqldump -hlocalhost -uroot -p123456 myschool student result >D:/b.sql

# 数据库
#mysqldump -h 主机 -u 用户名 -p 密码 数据库名 >导出位置
mysqldump -hlocalhost -uroot -p123456 myschool  >D:/c.sql

#导入
#在登陆的情况下,切换到指定的数据库
#source 备份文件
source D:/a.sql

#没有登陆的情况下
mysql -u用户名 -p密码 库名<备份文件

4.规范数据库设计

4.1 为什么要需要设计

当数据库比较复杂的时候，我们就需要设计了
糟糕的数据库设计：
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据库的完整。
- 方便我们开发系统
软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图 E-R 图

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

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

4.2 数据库三大范式

为什么需要数据规范化
- 信息重复
- 更新异常
- 插入异常:无法正常显示信息
- 删除异常:丢失有效的信息

三大范式

第一范式 (1st NF)
- 第一范式的目标是确保每列的原子性，如果每列都是不可再分的最小数据单元，则满足第一范式
第二范式(2nd NF)
- 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）
- 第二范式要求每个表只描述一件事情
第三范式(3rd NF)
- 如果一个关系满足第二范式，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式
- 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性！
- 故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

6.更多参考

狂神-MySQL最新教程

posted @ 2022-10-13 15:57 哼哼哈￥阅读(38) 评论(0) 编辑收藏举报

刷新页面返回顶部

MySQL的事务与索引

MySQL

1.事务

1.1 什么是事务

1.2 事务原则 ： ACID原则 原子性，一致性，持久性，隔离性 （脏读，幻读…）

1.3 事务隔离级别

1.4 测试事务实现转账

2.索引

2.1 索引的介绍

2.2 测试索引

2.3 索引原则

3.MySQL权限管理与备份

3.1 用户管理

3.2 MySQL 备份

4.规范数据库设计

4.1 为什么要需要设计

4.2 数据库三大范式

6.更多参考

公告

1.2 事务原则： ACID原则原子性，一致性，持久性，隔离性（脏读，幻读…）