MySql事务、索引和备份
1 事务管理
1.1 概念
（1）什么是事务
事务就是将一组SQL语句放在同一批次内去执行，只要一个SQL语句出错，则该批次内的所有SQL都将被取消执行。
事务可以把一组SQL指令打包在一起，要么同时成功，要么同时失败。
MySQL只有InnoDB和BDB数据引擎支持事务处理。
（2）事务的四个属性——ACID
原子性（Atomicity）：事务中的指令是一个完整的操作，不可分割。
一致性（Consistency）：当事务完成时，数据必须处于一致状态。
隔离性（Isolation）：所有并发事务是彼此隔离的，独立的不应以任何方式相互依赖或影响。
持久性（Durability）：事务执行后不管成功与否，对数据库的变更是永久的。
1.2 MySQL中的事务语法
（1）MySQL中的事务默认是自动提交的
SHOW VARIABLES LIKE 'autocommit'; #查看数据库自动提交设置（2）可以通过设置AutoCommit变量来改变事务自动提交设置。
SET AUTOCOMMIT = 0; # 关闭自动提交模式
SET AUTOCOMMIT = 1; # 开启自动提交模式（3）事务控制语句（TCL）：
START TRANSACTION; # 开始一个事务,标记事务的起始点
COMMIT; # 提交一个事务给数据库
ROLLBACK; # 将事务回滚，数据回到本次事务的初始状态
SET AUTOCOMMIT = 1; # 开启MySQL数据库的自动提交下图显示事务语句的执行过程：
1.3 事务执行的简单演示：
# 创建Account表模拟银行转账过程
DROP DATABASE IF EXISTS MyBank;
CREATE DATABASE MyBank;
USE MyBank;
CREATE TABLE Account(
accountNo INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
PASSWORD VARCHAR(50) NOT NULL,
balance DECIMAL(10,2) NOT NULL
);
INSERT INTO Account VALUES(1001, '张三', '123', 300);
INSERT INTO Account VALUES(1002, '李四', '123', 500);
SELECT * FROM Account;
# 事务的简单用法
START TRANSACTION;
UPDATE Account SET balance=balance-200 WHERE accountNo=1001;
UPDATE Account SET balance=balance+200 WHERE accountNo=1002;
ROLLBACK; #回滚事务
COMMIT; #提交事务1.4 使用逻辑封装事务（存储过程）
# 删除“转账”存储过程
DROP PROCEDURE IF EXISTS usp_transfer;
# 创建“转账”存储过程
DELIMITER //
CREATE PROCEDURE usp_transfer(fromAccount INT, toAccount INT, money DECIMAL)
BEGIN
START TRANSACTION;
UPDATE Account SET balance=balance+money WHERE accountNo=toAccount; #A账号+money
UPDATE Account SET balance=balance-money WHERE accountNo=fromAccount; #B账号-money
SELECT balance INTO @result FROM Account WHERE accountNo=fromAccount; #检查B账号金额
IF @result<0 THEN #金额为负Rollback，否则提交Commit
ROLLBACK;
ELSE
COMMIT;
END IF;
END //
# 调用“转账”存储过程，金额不合理，回滚
CALL usp_transfer(1001,1002,500);
# 调用“转账”存储过程，金额合理，转账成功
CALL usp_transfer(1001,1002,150);2 索引
2.1 索引的概念
（1）概念
索引是一种数据库对象，是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，也就是提高数据查询的效率。
此外，索引还可以加速表和表之间的连接，实现表与表之间的参照完整性；使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间。
（2）分类
按索引的物理结构来划分，索引可以分为：“聚集索引”和“非聚集索引”。
“聚集索引”是指数据库表行中数据的物理顺序与键值（索引）的逻辑顺序相同，反之就是“非聚集索引”。每个数据表中，聚集索引只有一个，而非聚集索引则可以有多个。
按索引的逻辑结构来划分，索引又可以分为：主键索引（PRIMARY KEY）、唯一索引（UNIQUE）、常规索引（INDEX）和全文索引（FULLTEXT）。
（3）推荐阅读
索引深入理解 理解MySQL——索引与优化 - YY哥 - 博客园
索引相关帮助 MySQL 索引 | 菜鸟教程
2.2 索引使用语法：
（1）主键索引（primary key）
主键是某一个属性或属性的组合能唯一标识一条记录。如：学生表（学号，姓名，班级，性别等） ，学号就是唯一标识的，可作为主键
创建主键（primary key）的时候，数据库会自动为我们添加上主键索引。主键索引通常就是聚集索引。主键索引的创建如下所示。
CREATE TABLE Grade (
GradeID INT(11) AUTO_INCREMENT PRIMARY KEY,
#省略代码……
#主键索引也可在字段字义之后，如
# PRIMARY KEY(`GradeID`)
);（2）唯一索引（unique）
唯一键是避免同一个表中某数据列中的值重复，与主键不同只能有一个不同，唯一键可有多个。
创建唯一键（unique）的时候，数据库会自定为我们添加上唯一索引。唯一键索引的创建如下所示。
CREATE TABLE Grade (
GradeID INT(11) AUTO_INCREMENT PRIMARY KEY,
GradeName VARCHAR(32) NOT NULL UNIQUE
#或 UNIQUE KEY (GradeName)
);唯一索引也可以直接创建：
CREATE UNIQUE INDEX 索引名称 ON 表名( 字段名(长度) );（3）常规索引（index）
除了主键和唯一键之外，数据表还可以添加常规索引。
常规索引是最基本的索引，是默认的BTREE类型的索引，也是我们大多数情况下用到的索引。可以快速定位特定数据，但不会对数据产生约束。
常规索引的创建语法如下所示。
方法一，直接创建索引：
CREATE INDEX 索引名称 ON 表名( 字段名(长度) );方法二，修改表结构的方式添加索引：
ALTER TABLE table_name ADD INDEX index_name ON(column_name(length));方法三，创建表的时候同时创建索引
CREATE TABLE 表名 ( .
…省略表结构定义…,
INDEX 索引名称(字段名(长度))
);2.3 练习：给数据库表student添加索引
（1）学号StudentNo，添加主键索引；
（2）电话Phone，添加唯一索引；
（3）姓名StudentName，常规索引。
2.4 全文索引（full text）
全文索引并不是关系型数据库的概念。在大量的文本数据集中，如果希望通过关键字匹配来进行查询过滤，也就是要基于相似度的查询，而不是原来的精确数值比较，全文索引就是为这种场景设计的。
由于精确性问题，全文索引在平时的业务中用到的不多，使用会受到以下限制：
（1）MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
（2）MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
（3）只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引；
（4）适用于较大的数据集；
（5）对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
创建语法：
方式一，直接创建：
CREATE FULLTEXT INDEX 索引名称 ON 表名(字段名);方法二，创建表时创建全文索引：
CREATE TABLE table_name(
...省略表结构...,
FULLTEXT 索引名称 ( 字段名(长度) )
);示例：
CREATE FULLTEXT INDEX fix_album_description ON album(description);
SELECT * FROM album WHERE MATCH(description) AGAINST('罗大佑');2.5 索引的管理总结
（1）创建索引
直接创建
CREATE INDEX 索引名称 ON 表名( 字段名(长度) );创建表时添加；
建表后追加
ALERT TABLE 表名 ADD 索引类型 (数据列名);（2）删除索引
DROP INDEX 索引名 ON 表名
ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE 表名 DROP PRIMARY KEY（3）查看索引
SHOW INDEX FROM 表名2.6 索引使用准则
索引不是越多越好
不要对经常变动的数据加索引
小数据量的表建议不要加索引
索引一般应加在查找条件的字段
3 数据库备份
3.1 使用MySQL命令备份
（1）备份的作用：
转储数据库；搜集数据库进行备份；将数据转移到另一个SQL服务器(不一定是MySQL服务器)。
（2）导出备份语法
mysqldump -h 主机名 –u 用户名 –p [options]
数据库名 [ table1 table2 table3 ] > path/filename.sql例如备份myschool数据库：
mysqldump -u root -p myschool > d:/myschool.sql（3）导入备份语法
mysql –u root –p 数据库名< /path/filename.sql;3.2 使用SQL语句导入导出
导出：
SELECT … INTO OUTFILE 'file_name' FROM 表名;导入：
LOAD DATA INFILE 'file_name ' INTO TABLE tbl_name[FIELDS];腾讯云开发者
