首页 > 数据库 >MySQL事务、索引、数据恢复和备份

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

时间：2024-10-10 09:50:52浏览次数：10  
标签：数据恢复 事务 name -- money 备份 索引 MySQL 1000

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  -- 直接导入表

标签：数据恢复,事务,name,--,money,备份,索引,MySQL,1000
From： https://blog.csdn.net/qq_63533204/article/details/142813698

相关文章

  • mysql占用内存过大问题排查
    如果MySQL占用内存过高，可以按照以下步骤进行排查：一、检查MySQL配置参数查看 innodb_buffer_pool_size：这个参数决定了InnoDB存储引擎缓冲池的大小，它会占用大量内存。如果设置得过大，可能导致内存占用过高。可以通过查询 SHOWVARIABLESLIKE'innodb_buffer_pool_size......
  • 基于SpringBoot+MySQL+SSM+Vue.js的电影票信息管理系统(附论文)
    获取见最下方名片获取见最下方名片获取见最下方名片演示视频基于SpringBoot+MySQL+SSM+Vue.js的电影票信息管理系统(附论文)技术描述开发工具：Idea/Eclipse数据库：MySQLJar包仓库：Maven前端框架：Vue/ElementUI后端框架：Spring+SpringMVC+Mybatis+SpringBoot......
  • 基于SpringBoot+MySQL+SSM+Vue.js的二手家电管理系统(附论文)
    获取见最下方名片获取见最下方名片获取见最下方名片演示视频基于SpringBoot+MySQL+SSM+Vue.js的二手家电管理系统(附论文)技术描述开发工具：Idea/Eclipse数据库：MySQLJar包仓库：Maven前端框架：Vue/ElementUI后端框架：Spring+SpringMVC+Mybatis+SpringBoot文......
  • 记宝塔面板MySQL启动失败问题之解决
    检查错误日志登录到宝塔面板。前往 软件管理 -> 数据库 ->选择对应的MySQL版本-> 查看日志。查看最近的错误信息，通常会给出启动失败的具体原因。检查MySQL配置文件配置文件通常位于 /etc/my.cnf 或 /etc/mysql/my.cnf。确保配置文件中的设置正确无误，例如数......
  • 织梦数据库备份恢复
    织梦CMS（DedeCMS）的数据库备份和恢复是一个常见的维护任务。以下是一些详细的步骤，帮助你完成数据库的备份和恢复过程，并解决图片无法显示的问题。数据库备份使用phpMyAdmin备份数据库登录到你的网站控制面板（如cPanel）。找到并打开phpMyAdmin。选择你的织梦CMS数据库。点击“......
  • K8S配置MySQL主从自动水平扩展
    前提环境操作系统Ubuntu22.04K8S1.28.2集群（1个master+2个node）MySQL5.7.44部署在K8S的主从集群metrics-serverv0.6.4概念简介在K8s中扩缩容分为两种●Node层面：对K8s物理节点扩容和缩容，根据业务规模实现物理节点自动扩缩容●Pod层面：一般会使用Deployment中的Re......
  • MySql基础：数据类型
    1.数据类型的整体分类    说明：在mysql表中建立属性列都是列名称在前类型在后。2.整数数据类型        说明：在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。可以通过UNSIGNED来说明某个字段是无符号的类型字节最小值最大值（带符号的/无符......
  • Java项目实战II基于Java+Spring Boot+MySQL的墙绘产品展示交易平台设计与实现(源码+数
    目录一、前言二、技术介绍三、系统实现四、文档参考五、核心代码六、源码获取全栈码农以及毕业设计实战开发，CSDN平台Java领域新星创作者，专注于大学生项目实战开发、讲解和毕业答疑辅导。获取源码联系方式请查看文末一、前言在当今多元化、个性化的家居装饰市场中，墙......
  • Java项目实战II基于Java+Spring Boot+MySQL的作业管理系统设计与实现(源码+数据库+文
    目录一、前言二、技术介绍三、系统实现四、文档参考五、核心代码六、源码获取全栈码农以及毕业设计实战开发，CSDN平台Java领域新星创作者，专注于大学生项目实战开发、讲解和毕业答疑辅导。获取源码联系方式请查看文末一、前言在教育信息化的大背景下，作业管理作为教学......
  • mysql基础汇总
    命令行操作启动数据库服务：netstartmysql；进入mysql：mysql-uroot-p;退出mysql：shutdown;基础操作库操作：查看所有库：showdatabases;创建库：createdatabase库名;使用库：use数据库名;删除库：dropdatabase数据库名;表操作：查看所有表：showtables;查看表结构：desc......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99