MySQL事务、索引、数据恢复和备份

于 2024-10-10 09:58:18 发布
阅读量583 收藏 4
点赞数 14
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_63533204/article/details/142813698
版权

1 事务

1.1 事务介绍

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系

统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

就比如: 张三给李四转账1000块钱，张三银行账户的钱减少1000，而李四银行账户的钱要增加

1000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。

正常情况: 转账这个操作, 需要分为以下这么三步来完成 , 三步完成之后, 张三减少1000, 而李四

增加1000, 转账成功 :

异常情况: 转账这个操作, 也是分为以下这么三步来完成 , 在执行第三步是报错了, 这样就导致张

三减少1000块钱, 而李四的金额没变, 这样就造成了数据的不一致, 就出现问题了。

为了解决上述的问题，就需要通过数据的事务来完成，我们只需要在业务逻辑执行之前开启事务，执行

完毕后提交(commit)事务。如果执行过程中报错，则回滚(rollback)事务，把数据恢复到事务开始之前的状态

注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐

式的提交事务。

1.2 事务操作

我们先准备一下数据：

drop table if exists account;
create table account(
  id int primary key AUTO_INCREMENT comment 'ID',
  name varchar(10) comment '姓名',
  money double(10,2) comment '余额'
) comment '账户表';
insert into account(name, money) VALUES ('张三',2000), ('李四',2000);

根据上面的示例我们先看一下正常情况：

-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的。

测试异常情况：

-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
出错了....      -- 这里咱们故意让语句执行出错
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

我们把数据都恢复到2000， 然后再次一次性执行上述的SQL语句(出错了.... 这句话不符合SQL语

法,执行就会报错)，检查最终的数据情况, 发现数据在操作前后不一致了。

1.2.1 控制事务一( SET @@autocommit = 0)

1). 查看/设置事务提交方式

SELECT @@autocommit ; -- 查看事务的提交方式  1为自动提交 MySQL默认为1   0为手动提交
SET @@autocommit = 0 ; -- 设置为手动提交

其实本人不建议这样使用事务 应为如果设置为0 往后的SQL操作 都要进行手动提交 除非再设置为1

2). 提交事务

COMMIT;

3). 回滚事务

ROLLBACK;

注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提

交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

来看一下上述案例：

SELECT @@autocommit ;
SET  autocommit = 0; -- 1默认自动提交事务
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
出错了....
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
COMMIT; -- 没有出错就提交事务
ROLLBACK; -- 出错了 就回滚事务 让数据回到最初的状态

1.2.2 控制事务二(START TRANSACTION)

1). 开启事务

START TRANSACTION 或 BEGIN ; -- 这里咱们只开启一个事务  
-- 注意只开启一次事务  执行commit或者rollback后 这个事务就结束了

2). 提交事务

COMMIT;

3). 回滚事务

ROLLBACK;

我们继续看一下这种方法操作案例：

START TRANSACTION; -- 开启事务

select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
出错了....
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
COMMIT;
ROLLBACK;

1.3 事务四大特性(ACID)

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立 环境下行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

上述就是事务的四大特性，简称ACID。

1.4 并发事务问题

1). 赃读：一个事务读到另外一个事务还没有提交的数据。

比如B读取到了A未提交的数据。

2). 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

事务A两次读取同一条记录，但是读取到的数据却是不一样的。

3). 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据

已经存在，好像出现了 "幻影"。

1.5 事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

1). 查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

2). 设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意：事务隔离级别越高，数据越安全，但是性能越低。

2 索引

2.1 索引介绍

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足

特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构

上实现高级查找算法，这种数据结构就是索引。

作用：

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

2.2 索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持

情况

注意： 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

2.3 索引分类

2.3.1 索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

2.3.2 聚集索引&二级索引

而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则:

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索 引

2.4 索引语法

1). 创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

创建表时添加:

 CREATE TABLE  `student` (
       #省略一些SQL语句
       PRIMARY KEY(`studentId`)
       FULLTEXT (`StudentName`) 
)ENGINE=engine;

创建后追加:

ALTER TABLE student ADD FULLTEXT (`StudentName`);

2). 查看索引

SHOW INDEX FROM table_name ;

3). 删除索引

DROP  INDEX 索引名 ON    表名
ALTER TABLE 表名   DROP  INDEX  索引名
ALTER TABLE 表名   DROP  PRIMARY KEY

2.5 索引设计原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。

2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索

引。

3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，

避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增

删改的效率。

7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含

NULL值时，它可以更好地确定哪个索引最有效地用于查询。

3 数据的备份与恢复

3.1 使用mysqldump数据库备份

作用：

  • 转储数据库
  • 搜集数据库进行备份
  • 将数据转移到另一个SQL服务器(不一定是MySQL服务器)

基本的语法：

mysqldump  -h 主机名 –u 用户名 –p   [options]   数据库名  
[ table1 table2 table3 ]   > path/filename.sql
-- path/filename.sql 为路径名 一般为绝对路径

要注意：预存文件目录，须有该目录读写权限

path/filename.sql 为路径名 一般为绝对路径

示例 ：

  1. 备份单个数据库 
mysqldump -u root -p my_database > path/filename.sql

运行后会提示输入 root 用户的密码，备份将保存为 指定路径下的filename.sql

  1. 备份多个数据库

可以使用 --databases 参数来备份多个数据库。

mysqldump -u root -p --databases db1 db2 > path/filename.sql

  1. 备份所有数据库
mysqldump -u root -p --all-databases > path/filename.sql

  1. 备份单个表

要备份单个表，可以指定表名：

mysqldump -u root -p my_database my_table > path/filename.sql
-- 同样的道理要是多张表就写多个表名

  1. 备份并压缩

可以使用 gzip：

mysqldump -u root -p my_database | gzip > path/filename.sql.gz

mysqldump常用选项：

符号名称

描述

--add-drop-table

导出sql脚本会加上 DROP TABLE IF EXISTS 语句

默认是打开的，可以用 --skip-add-drop-table 来取消

--add-locks

该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句

好处：防止记录被再次导入时，其他用户对表进行的操作，默认是打开的

-t 或--no-create-info

忽略不写重新创建每个转储表的CREATE TABLE语句

-c 或 --complete-insert

在每个INERT语句的列上加上字段名

在数据库导入另一个数据库时非常有用（这个比较常用）

-d 或 --no-data

不写表的任何行信息。对于只想转储表的结构很有用

--where "where-condition", -w "where-condition"

只转储给定的WHERE条件选择的记录

--opt

该选项是速记；等同于指定

--add-drop-tables --add-locking

--create-option --disable-keys--extended-insert

--lock-tables --quick

--set-charset

3.2 MySQL数据库的恢复(导入)

使用MySQL命令行语句恢复：

mysql -u 用户名 -p 数据库名 < 文件路径.sql

在导入之前，确保目标数据库已经存在。

使用SOURCE 在MySQL命令行里执行恢复(导入)：

mysql –u root –p -- 进入MySQL命令行
 USE 数据库名; -- 使用数据库
 source  文件路径.sql

3.3 使用sql语句进行备份(导出)与恢复(导入)

使用 SELECT ... INTO OUTFILE 备份(导出)：

在导出之前我们要先检查一下secure-file-priv 的限制

secure-file-priv 是MySQL给的安全路径 文件一般导出到其对应的文件夹里面

示例如下：

-- 先检查 MySQL 允许文件导出的安全路径
  SHOW VARIABLES LIKE 'secure_file_priv'   -- 若查询结果为 C:\mysql\secure-files
 SELECT * INTO OUTFILE 'C:/mysql/secure-files/d1.sql'
FROM account  -- 直接导出表

路径使用：在 Windows 上使用正斜杠（/）或双反斜杠（\\）

使用 LOAD DATA INFILE 恢复(导入)数据 ：

和导出一样 先检查一下secure-file-priv 的限制

示例如下:

-- 先检查 MySQL 允许文件导出的安全路径
  SHOW VARIABLES LIKE 'secure_file_priv'   -- 若查询结果为 C:\mysql\secure-files
 SLOAD DATA INFILE 'C:/mysql/secure-files/d1.sql'
INTO TABLE account  -- 直接导入表
late summer182
关注
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL入门：事务、索引、备份和恢复
qq_52695495的博客
01-07 896
一、使用事务 1.什么是事务 事务（transaction）是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有命令作为一个整体一起向系统提交或撤销操作请求。事务是一个不可分割的工作逻辑单元。 事务是指将一系列数据操作捆绑成为一个整体进行统一管理。若是某一事务执行成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分，若是事务执行遇到错误而且必须取消或回滚，则数据将全部恢复到操作前的状态，所有数据的更改均会删除。 比方说转账问题，张三想把某一银行卡里的余额转给李四，那
a5MySQL事务、索引、数据恢复和备份
m0_70083523的博客
09-07 1454
事务、视图、索引、存储过程、触发器
5.数据库-MySQL事务、索引、数据恢复和备份
QAZ412803的博客
09-23 1317
事务就是将一组SQL语句放在同一批次内去执行如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行。/path/是一个绝对路径，并且必须是mysql 运行用户有权限读取的文件。如：学生表（学号，姓名，班级，性别等） ，学号就是唯一标识的，可作为主键。如：学生表（学号，姓名，班级，性别等） ，学号就是唯一标识的，可作为主键。使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间。可以加速表和表之间的连接，实现表与表之间的参照完整性。不宜添加太多常规索引，影响数据的插入、删除和修改操作。
MySQL之数据备份和恢复， 语句优化和事务回滚
python -学习笔记
09-04 1169
1.数据备份(mysqldump,在Linux终端操作)     1.命令格式:         mysqldump -u用户名 -p 源库名 &gt; ****.sql     2.源库名的表示方式         --all-databases     备份所有的库         库名                 备份一个库         -B 库1 库2 库3       备份多个库...
mysql如何进行视图恢复_mysql事务 视图 索引 备份和恢复
weixin_33311334的博客
01-27 1006
事务1.事务:概念:是作为单个逻辑工作单元执行的一系列操作多个操作作为一个整体向系统提交,要么都执行,要么都不执行事务是一个不可分割的工作逻辑单元默认情况下 , 每条单独的SQL语句视为一个事务2.事务的特性: (简称ACIO属性)1.原子性(Atomicity): 事务是一个完整的操作,事务的各布操作是不可分的(原子的),要么都执行,要么都不执行2.一致性(Consistency);当事务完成时...
MySQL事务及实现原理
热门推荐
张彦峰的博客
01-17 81万+
MySQL事务简单介绍+事务特性分析+事务隔离级别+事务实现原理
MySQL binlog日志详解 查询和数据恢复 细节 ops范围数据恢复 时间节点范围 MySQL数据恢复 数据库备份 主从复制 增量恢复
Benjamin CSDN博客
06-30 2714
MySQL binlog日志详解 查询和数据恢复 细节 按时间点 按操作id顺序范围 数据恢复 数据库备份 主从复制 增量恢复 MySQL Binlog是二进制格式的日志文件，二进制日志记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中。用来记录Mysql内部对数据库的改动（只记录对数据的修改操作）。可以用来查看数据库的变更历史（具体的时间点所有的SQL操作）、数据库增量备份和恢复（增量备份和基于时间点的恢复）、Mysql的复制（主主数据库的复制、主从数据库的复制）。
数据库（MySQL的备份和恢复）
qq_64227183的博客
01-24 1409
数据库（MySQL的备份和恢复）
MySQL事务、索引、数据恢复和备份全套电子课件完整版ppt整本书电子教案最全教学教程整套课件.pptx
06-08
MySQL事务、索引、数据恢复和备份全套电子课件完整版ppt整本书电子教案最全教学教程整套课件.pptx
mysql mysqldump数据备份和增量备份
12-15
MySQL 数据库系统是广泛使用的开源关系型数据库管理系统，它的数据备份和恢复对于系统的稳定性和安全性至关重要。本篇文章主要探讨如何利用 `mysqldump` 工具进行全量备份和增量备份，这两种备份策略在不同场景下都...
MySQL数据库和MySQL管理、备份与恢复
12-14
1. 数据库备份：理解全量备份和增量/差异备份的概念，以及如何使用mysqldump工具进行这些备份。此外，还将讨论二进制日志（binlog）在备份中的作用。 2. 文件系统备份：除了数据库的逻辑备份，还会涉及对数据文件的...
基于微信小程序的生鲜订购小程序系统(SpringBoot+Vue+Uniapp+MySQL)
源码好优多
10-06 1811
开发者不再需要手动配置大量的 XML 文件或繁琐的注解，框架提供了默认的配置，根据项目的依赖关系和约定，自动完成配置。系统测试主要是为了避免用户在使用时发生问题，增强用户体验感，为了不影响用户的使用，我们需要从多角度、多思路去考虑系统可能遇到的问题，通过不同的模拟场景来发现缺陷并解决问题。添加用户时，必填项不填，检验系统是否有非空检验；登录功能测试方案：当需要登入该系统时，通过账户密码等功能点进行验证，用户在输入时需要输入与数据库内存储的数据匹配的内容，当其中某项输入错误时系统将提示输入错误。
MySQL 联合索引底层存储结构及索引查找过程解读
lizi88888的博客
10-09 384
大家好，我是 Lorin ，联合索引（Composite Index）又称复合索引，它包括两个或更多列。与单列索引不同，联合索引可以覆盖多个列，这有助于加速复杂查询和过滤条件的检索。联合索引的列顺序非常重要，因为查询优化器会按照索引列的顺序执行搜索。本文将从联合索引基本概念、底层存储结构、索引查找过程、实践建议几个方面图文并茂进行详细介绍。
深入了解 MySQL 中的 JSON_CONTAINS
学习,分享一直在路上
10-05 990
函数用于检查一个 JSON 文档中是否包含另一个 JSON 文档，
MySQL数据的增删改查
最新发布
2301_79985750的博客
10-09 943
基于比较运算符和逻辑运算符，可以对复杂的条件进行过滤，从而达到要求，其中，区间查询，模糊查询以及null查询都是条件查询。限制查询结果集的条数，通过分页查询可以有效的控制一次查询出的结果集中的记录条数，可以有效的减少数据库的压力，同时对用户也比较友好。例如，查姓唐同学的成绩，%代表唐后面所有字符都被选中，类似还有%唐，%唐%，分别表示唐前面的字符被选中，唐左右的字符被选中。这是一个非常危险的操作，如果数据量太大，会消耗大量的内存和CPU资源，可能导致数据库服务器的性能下降。，特别是在多表查询的情况下。
MYSQL｜高频 SQL 50 题｜550. 游戏玩法分析 IV
糖炒栗子
10-09 643
SELECTplayer_id,FROMactivityGROUP BYplayer_id：通过 WITH 子句，定义一个公共表达式；MIN函数找到每个玩家首次登陆日期；按照 player_id 进行分组。可以讲 CTE 理解为一个临时表，它包含了每个玩家的首次登陆日期，供住查询中使用。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值