MySQL的事务处理方法

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

1.1事务的概述

事务：就是一组SQL语句放在同一批次内去执行,一系列操作要么都执行成功！要么都失败！

银行转账：

业务（一套固定的顺序流程，例如：请假、报销、办卡、入学、登陆、注册……）

一个SQL语句出错,该批次内所有的SQL都将被取消执行

注意：

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

RDB安全：事务支持好

NoSQL事务：一般并不是太强（Redis是假事务） BDB才支持事务

MySQL：存储引擎 INNODB（默认、高频采用）

MyISAM也是常用存储引擎，但是他在事务等方面不支持，但的查询性能比较强，也支持全文索引

其实在使用了支持事务的引擎之后，事务就已经在使用了！因为MySQL中默认自动提交事务！

1.2实现的方法

(1)自行开启事务BEGIN START TRANSACTION(tx)
BEGIN;

1.将张三的余额扣除500
UPDATE
bank
SET money = money - 500
WHERE
name = ‘张三’;

2.将李四的余额添加500
UPDATE
bank
SET money = money + 500
WHERE
name = ‘李四’;

如果没有出现异常 则应该提交事务
COMMIT;
如果出现异常 事务应该进行回滚
ROLLBACK;

(2)通使用SET语句来改变自动提交模式

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

注意：

MySQL中默认是自动提交

使用事务时应先关闭自动提交

START TRANSACTION

开始一个事务、标记事务的起始点

COMMIT

提交一个事务给数据库

ROLLBACK

将事务回滚，数据回到本次事务的初识状态

DDL会直接结束事务！并使操作生效
BEGIN;

例如：

在事务中执行DDL语句CREATE

CREATE DATABASE hehe;
DELETE FROM bank WHERE name = ‘李四’;
TRUNCATE TABLE bank;

COMMIT;

未来和Java 、Python 、JS等集成使用的方式
/*
JDBC Java DataBase Connectivity Java数据库连接技术
try{
// 开启事务
// 1.调用数据库执行史浩然信息更新
// 2.调用数据库执行孔钦瑞信息更新
// 提交COMMIT
}catch(Exception e){
e.printStackTrace();
// 回滚ROLLBACK
}

*/

2.MySQL的ACID原则

四大特性：原子性：(在化学上讲原子为最小单位 不可再分)事务是不可分割!要么都成功要么都失败！

一致性：事务执行前和执行后数据总量不变！要么是提交前状态，或是提交后状态，例如转账

隔离性：多个事务之间应该彼此独立，不受影响。

事务并发引发的问题可能有：脏读、不可重复度、幻读/虚读

​ 脏读：一个事物读取到了另一个事务未提交的数据！(基本不允许，因为事务有可能回滚)

​ 不可重复读：一个事物在执行期间读取了多次内容，结果不一样。主要针对另一个事务对同样的内容进行了修改操作(UPDATE,DELETE)

​ 幻读/虚读：一个事物在执行全表操作时，另一个事务进行了插入操作，那么第一个事务就会发现还有未执行完的内容。(INSERT)

(MySQL)四大隔离级别：读未提交、读已提交、可重复读、串行化

读未提交：任何事物并发问题都无法解决！(基本不采用)

读已提交：能解决脏读问题！(Oracle数据库默认)

可重复读：MySQL默认！可以解决脏读和不可重复读问题！(MySQL默认)

串行化：最严格的隔离级别！可以解决所有的并发问题！(基本不采用)

持久性：事务在执行完毕之后必须能够将数据持久保存到数据库中！

视图：它是一种[虚拟表] 可以用来降低查询复杂度！也可以适用于不同群体对数据的不同要求！
SELECT
stu.stuName,sub.subjectName,r.result,r.examDate
FROM
result r,student stu,subject sub
WHERE
r.stuId = stu.stuId AND r.subjectId = sub.subjectId
ORDER BY
r.result DESC , r.examDate DESC;

视图创建
CREATE VIEW stu_result_list AS
SELECT
stu.stuName,sub.subjectName,r.result,r.examDate
FROM
result r,student stu,subject sub
WHERE
r.stuId = stu.stuId AND r.subjectId = sub.subjectId
ORDER BY
r.result DESC , r.examDate DESC;

视图的使用【视图是一张虚拟表，你对他的改动会最终影响到真实数据物理表！】
SELECT * FROM stu_result_list;

视图删除
DROP VIEW stu_result_list;

视图注意事项：
1.视图仅仅作为查询使用即可！不要进行修改等操作！
2.视图也可以进行嵌套使用
3.视图的数据是来自于对应的物理表！

4.数据库索引

索引：提升查询性能！ 将一个列或者一组列进行相关组合排序使用。
原理：类似于字典前的目录页
数据库索引

作用：

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

分类：

• 主键索引(PRIMARY KEY)
• 唯一索引(UNIQUE)
• 常规索引(INDEX)
• 复合索引
• 全文索引(FULLTEXT)
• 空间索引

EXPLAIN 函数 分析 select 语句

示例：
SELECT * FROM articles  WHERE MATCH (title,body) AGAINST ('database');

##创建索引

-- 方法一在创建表时加入
CREATE TABLE indexTest(
`NAME` VARCHAR(20),
`a` VARCHAR(20),
ID INT,
grade INT,
class MEDIUMTEXT,
-- 主键索引
PRIMARY KEY(ID),
-- 唯一索引
UNIQUE KEY(NAME),
-- 普通索引
INDEX (a),
-- 文本索引
FULLTEXT(class),
-- 复合索引
INDEX (a,grade)
)

-- 方法二 在创建表后加入
ALTER TABLE indextest ADD UNIQUE uni_name(name);
ALTER TABLE indextest ADD FULLTEXT text_class(class);
ALTER TABLE indextest ADD INDEX index_a_grade(a,grade);
-- 添加主键索引，也就是添加主键
ALTER TABLE indextest ADD PRIMARY KEY pk_id(id);

删除索引

-- 除删除主键外的索引
DROP INDEX 键名 ON 表名;
ALTER TABLE 表名 DROP INDEX 键名;
示例：
DROP INDEX name ON indextest;
ALTER TABLE indextest DROP INDEX class;
-- 删除主键索引，也就是删除主键
ALTER TABLE indextest DROP PRIMARY KEY;

主键索引

主键：

某一个属性组能唯一标识一条记录

如：学生表（学号，姓名，班级，性别等），学好就是唯一标识的，可作为主键

特点：

最常见的索引类型

确保数据记录的唯一性

确定特定数据记录在数据库中的位置

唯一索引

唯一索引（UNIQUE）

作用：

​ 避免同一个表中某数据列中的值重复

与主键索引的区别

​ 主键索引只能有一个

​ 唯一索引可有多个

常规索引（INDEX）

作用：

​ 快速定位特定数据

注意：

​ index和key关键字都可设置常规索引

​ 应加在查找条件的字段

​ 不宜添加太多常规索引，影响数据的插入、删除和修改操作

复合索引(

作用：

​ 提高查询速度

注意：

​ 复合索引在使用时，要按照查询条件的顺序来组合。

​ 何时使用复合索引：根据where条件建创建索引;

全文索引

全文索引（Full Text）

作用：

• 快速定位特定数据

注意：

• 只能用于MyISAN类型的数据表
• 只能用于CHAR、VARCHAR、TEXT数据列类型
• 适用大型数据集

explain 分析语句查询的性能

查看表的索引

SHOW INDEX FROM 表名;

索引准则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数量的表建议不要加索引
• 索引一般应加在查找条件的字段

/*
索引并不是一味的创建即可提升性能！ 因为索引也会占用存储空间！而且索引列会在操作时进行索引验证…
需要谨慎的设计和使用！
设计：
[建议]
1.频繁搜索的列！
2.频繁用作于查询选择的列！
3.经常排序分组的列！
4.经常用于连接的列！
[不建议]
5.仅包含几个不同值的列！例如：性别、学历…
6.在表中仅包含几行的列！ 数据不多！
使用：
1.尽量不要使用*来查找数据！
2.索引列应该尽量小，在字节数小的列上建立！
3.如果where中有多个条件表达式 那么索引列的条件表达式要放在前面
4.避免对索引进行计算表达式！
*/

5.MySQL数据库的备份和恢复

数据库备份必要性

• 保证重要数据不丢失
• 数据转移

MySQL数据库备份方法

• mysqldump备份工具
• 数据库管理工具，如SQLyog
• 直接拷贝数据库文件和相关配置文件

mysqldump和mysql同级的命令

备份数据库：

mysqldump [参数选项] -u用户名 -p -h主机地址 要备份的数据库名 [要备份的数据表列表]  > 输出的地址

mysqldump客户端

作用：

转出数据库

搜集数据库进行备份

将数据转移到另一个SQL服务器（不一定是MySQL服务器）

mysqldump --help|more

MySQL数据库的恢复

方法一：

用SOURCE语法

​ /path/是一个绝对路径，并且必须是mysql运行用户有权限的文件

​ SOURCE在MySQL命令行里执行

use 数据库名
SOURCE /path/db_name.sql;

方法二：

用mysql客户端

mysql -u root -p dbname < /path/db_name.sql;

备份某个表的数据

SELECT 字段名... INTO OUTFILE '路径' FROM 表名

还原某个表的数据

LOAD DATA INFILE '路径' INTO TABLE 表名（字段名）

参数选项：
-add-drop-table 表示在创建表语句前都添加drop table语句 可以通过-skip-add…取消
–add-locks 表示在插入数据前后加上锁表和解锁语句 可以防止插入出错！
-t (-table 减去table) 导出数据时不添加数据结构创建的语句 CREATE TABLE
-c (complete完整的) 可以帮助我们在导出数据时 添加语句可以加上字段列表
-d (-data 减去data) 导出时 只要表结构 不要数据