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

MySQL的事务处理

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

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

事务的特性

事务必须具备以下四个属性，简称ACID 属性

原子性（Atomicity）

原子是最小单元，不能再分割。事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。

一致性（Consistency）

事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号金额之和应该是不变的。

隔离性（Isolation）

隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。也就是说，在事中务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据（但如果有等级，则会看到）。
例如：在A事务中，查看另一B事务(正在修改张三的账户金额)中张三的账户金额，要查看到B事务之前的张三的账户金额，要么查看到B事务之后张三的账户金额。事务隔离性分等级。

持久性（Durability）

一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

MySQL的事务实现方法步骤

数据库索引

准则

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

作用

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

分类

主键索引（PRIMARY KEY）

某一个属性或属性的组合能唯一标识一条记录
特点：
最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置

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

唯一索引（UNIQUE）

作用：避免同一个表中某数据列中的值重复
与主键索引的区别：主键索引只能有一个，唯一索引可有多个。

 CREATE TABLE  `Grade` (
	`GradeID`  INT(11)  AUTO_INCREMENT  PRIMARY KEY,
	`GradeName`  VARCHAR(32)  NOT NULL  UNIQUE
	#或  UNIQUE  KEY  `GradeID`  (`GradeID`)
)

常规索引（INDEX）

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

#建表时添加
CREATE TABLE  `result` (
       //省略一些代码
     INDEX/KEY   `ind`  (`studentNo`, `subjectNo`)
)
#建表后添加
ALTER TABLE  `result`  ADD  INDEX  `ind` 
     (`studentNo`, `subjectNo`);

全文索引（FULLTEXT）

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

#建表时添加
CREATE TABLE  `student` (
       #省略一些SQL语句
       FULLTEXT (`StudentName`) 
)ENGINE=MYISAM;
#创建后追加
ALTER TABLE employee ADD FULLTEXT (`first_name`);

管理索引

创建索引

#追加

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

删除索引

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

查看索引

SHOW  INDEX(或KEYS) FROM 表名

分析SQL语句的执行性能

EXPLAIN  表名  （DESC 表名）
EXPLAIN  SELECT语句

提示：使用 \G 结尾能竖排显示

添加正确的索引

在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: *****

>mysqldump -uroot -p -c myschool subject> d:/subject.sql

mysqldump常用选项

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

MySQL数据库的恢复

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

SOURCE     /path/db_name.sql;

方法二
用 mysql 客户端

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

都要先创建或选择数据库

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

导出数据

SELECT   *   INTO   OUTFILE   'file_name'  
    FROM   tbl_name

输出的文件不能先存在，否则报错

导入数据

LOAD   DATA   INFILE   'file_name '   
    INTO   TABLE   tbl_name[FIELDS
    ```