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

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

MySQL的事务处理

• 事务就是将一组SQL语句放在同一批次内去执行
• 如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行

注意：MySQL事务处理只支持InnoDB和BDB数据表类型

• 事务必须具备以下四个属性，简称 ACID 属性
  • 原子性（Atomicity）
  • 一致性（Consistency）
  • 隔离性（Isolation）
  • 持久性（Durability）

MySQL的事务实现方法

• SET AUTOCOMMIT
  • 使用SET语句来改变自动提交模式

SET AUTOCOMMIT  = 0;    -- 关闭自动提交模式
SET AUTOCOMMIT  = 1;    -- 开启自动提交模式
1
2

• 注意：
  • MySQL中默认是自动提交
  • 使用事务时应先关闭自动提交

• START TRANSACTION
  • 开始一个事务,标记事务的起始点
• COMMIT
  • 提交一个事务给数据库
• ROLLBACK
  • 将事务回滚，数据回到本次事务的初始状态
• SET AUTOCOMMIT = 1;
  • 开启MySQL数据库的自动提交

MySQL事务处理步骤

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-buMC8PiD-1662540988774)(G:\笔记文档\mdp\数据库\SharedScreenshot.jpg)]

示例

-- 事务脏读现象

-- 关闭自动提交
set autocommit=0;
-- 开启事务
START TRANSACTION;

-- 过程体
update yhzz SET money = money + 100 WHERE id = 1;
UPDATE yhzz SET money = money - 100 + 'aaa' WHERE id = 2;

-- 回滚
ROLLBACK;

-- 重新开启自动提交
SET autocommit = 1;

COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

数据库索引

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

主键索引

• 某一个属性或属性的组合能唯一标识一条记录
  • 如：学生表（学号，姓名，班级，性别等） ，学号就是唯一标识的，可作为主键
• 特点
  • 最常见的索引类型
  • 确保数据记录的唯一性
  • 确定特定数据记录在数据库中的位置

 CREATE TABLE  `Grade` (
       `GradeID`  INT(11)   AUTO_INCREMENT    PRIMARY KEY,
        -- 省略代码……
        -- 主键索引也可在字段字义之后，如
        -- PRIMARY KEY(`GradeID`)    
)
1
2
3
4
5
6

唯一索引（UNIQUE）

• 作用
  • 避免同一个表中某数据列中的值重复（null可以重复）
• 与主键索引的区别
  • 主键索引只能有一个
  • 唯一索引可有多个

 CREATE TABLE  `Grade` (
	`GradeID`  INT(11)  AUTO_INCREMENT  PRIMARY KEY,
	`GradeName`  VARCHAR(32)  NOT NULL  UNIQUE
	-- 或  UNIQUE  KEY  `GradeID`  (`GradeID`)
)
1
2
3
4
5

常规索引（INDEX）

• 作用：快速定位特定数据
• 注意
  • index和key关键字都可设置常规索引
  • 应加在查找条件的字段
  • 不宜添加太多常规索引，影响数据的插入、删除和修改操作

CREATE TABLE  `result` (
       //省略一些代码
     INDEX/KEY   `ind`  (`studentNo`, `subjectNo`)	-- 创建表时添加
)

ALTER TABLE  `result`  ADD  INDEX  `ind`  (`studentNo`, `subjectNo`);	-- 创建后追加
1
2
3
4
5
6

全文索引（FULLTEXT）

• 作用：快速定位特定数据
• 注意
  • 只能用于MyISAM类型的数据表
  • 只能用于 CHAR 、 VARCHAR、TEXT数据列类型
  • 适合大型数据集

 CREATE TABLE  `student` (
       #省略一些SQL语句
       FULLTEXT (`StudentName`) 	-- 创建表时添加
)ENGINE=MYISAM;

ALTER TABLE employee ADD FULLTEXT (`first_name`);	-- 创建后追加
1
2
3
4
5
6

管理索引

• 创建索引
  • 创建表时添加
  • 建表后追加

ALERT TABLE 表名 ADD 索引类型（数据列名）
1

• 删除索引

DROP  INDEX 索引名  ON   表名
ALTER TABLE 表名   DROP  INDEX  索引名
ALTER TABLE 表名   DROP  PRIMARY KEY
1
2
3

• 查看索引

SHOW  INDEX(或KEYS) FROM 表名
1

索引准则

• 在WHERE、ORDER BY 子句中经常使用的字段
• 字段的值是多个（例如性别字段则不适合）
• 索引不是越多越好
  • 不宜过多添加索引
    • 每添加一条索引都会占用磁盘空间
• 不要对经常变动的数据加索引
  • 经常变化的字段，添加索引反而降低性能
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

MySQL的备份

• 数据库备份必要性
  • 保证重要数据不丢失
  • 数据转移
• MySQL数据库备份方法
  • mysqldump备份工具
  • 数据库管理工具，如SQLyog
  • 直接拷贝数据库文件和相关配置文件

mysqldump数据库备份

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

-- 语法
mysqldump  -h 主机名 –u 用户名 –p   [options]   数据库名  
[ table1 table2 table3 ]   
> path/filename.sql	-- 预存文件目录，须有该目录读写权限

-- 示例
-- 备份myschool数据库如： 
 > mysqldump -u root -p  myschool
 > d:/myschool.sql
  EnterPassword: *****
1
2
3
4
5
6
7
8
9
10

mysqldump常用选项

• 可通过mysqldump --help 查看该命名的选项

MySQL数据库的恢复

• 方法一
  • 用 SOURCE 语法
    • /path/是一个绝对路径，并且必须是mysql 运行用户有权限读取的文件
    • SOURCE 在MySQL命令行里执行

SOURCE  /path/db_name.sql;		-- 都要先创建或选择数据库
1

• 方法二
  • 用 mysql 客户端

mysql –u root –p  dbname  
<  /path/db_name.sql;
1
2

利用SQL语句导出、导入数据

SELECT   *   INTO   OUTFILE   'file_name'  
    FROM   tbl_name					-- 导出数据
-- 输出的文件不能先存在，否则报错

LOAD   DATA   INFILE   'file_name '   
INTO   TABLE   tbl_name[FIELDS]		-- 导入数据
1
2
3
4
5
6

bname
< /path/db_name.sql;

### 利用SQL语句导出、导入数据

```sql
SELECT   *   INTO   OUTFILE   'file_name'  
    FROM   tbl_name					-- 导出数据
-- 输出的文件不能先存在，否则报错

LOAD   DATA   INFILE   'file_name '   
INTO   TABLE   tbl_name[FIELDS]		-- 导入数据
1
2
3
4
5
6
7
8
9
10