MySQL的事务、索引、数据库备份及还原
事务
事务就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行
注意：MySQL事务处理只支持InnoDB和BDB数据表类型
事务的特性
①原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
②一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
③隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
④持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
MySQL的事务实现
①使用SET语句来改变自动提交模式：SET AUTOCOMMIT=1
②开始一个事务，标记事物的起始点：START TRANSACTION
③提交一个事务给数据库：COMMIT
④将事务回滚，数据回到本次事务的初始状态ROLLBACK
示例：
顾客A在线购买一款商品，价格为500.00元，采用网上银行转账的方式支付
假如顾客A银行卡的余额为2000.00元，且向卖家B支付购买商品费用500.00元，起始卖家B的账号金额10000.00元
创建数据库shop和创建表account并插入2条数据
CREATE TABLE account(
id INT(11) AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(32),
cash DECIMAL(9,2)
)
INSERT INTO `account` (`name`,`cash`) VALUES ('customer',2000.00);
INSERT INTO `account` (`name`,`cash`) VALUES ('shop',100000.00);
// mysql事务模拟
set autocommit=0;
start transaction;
update account set cash=cash-2000 where name='bank';
update account set cash=cash+2000 where name='shop';
#rollback
commit
set autocommit=1;
数据库索引
作用
①提高查询速度
②确保数据的唯一性
③可以加速表和表之间的连接，实现表与表之间的参照完整性
④使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
⑤全文检索字段进行搜索优化
分类
①主键索引（PRIMARY KEY）
②唯一索引（UNIQUE）
③常规索引（INDEX）
④全文索引（FULLTEXT）
主键索引
某一个属性或属性的组合能唯一标识一条记录
特点：
最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置
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
索引准则
①索引不是越多越好
②不要对经常变动的数据加索引
③小数据量的表建议不要加索引
④索引一般应加在查找条件的字段
示例：
给数据库表student添加索引：
学号StudentNo，添加主键索引
身份证IdentityCard，添加唯一索引
邮箱Email，添加常规索引
alter table student add primary key(StudentNo);
alter table student add unique(IdentityCard);
alter table student add index 'myIndex'(Email)
查看SQL语句的执行性能
EXPLAIN 表名 （DESC 表名）
EXPLAIN SELECT语句
正确添加索引
①在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
mysqldump常用选线：
MySQL数据库的恢复
①source命令
SOURCE /path/db_name.sql;
/path/是一个绝对路径，并且必须是mysql 运行用户有权限读取的文件
SOURCE 在MySQL命令行里执行
②用 mysql 客户端
mysql –u root –p dbname < /path/db_name.sql;