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

mysql事务的处理

概念

事务就是将一组SQL语句放在同一批次内去执行
如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行
MySQL事务处理只支持InnoDB和BDB数据表类型（myisam不支持）

事务的acid原则

原子性：事务处理包括一组sql语句，这一组sql语句是当一个整体执行的，不能单独执行其中的某一条，要么全部成功，要么全部失败
一致性：确保数据库能够正确的改变状态后，能够成功地提交事务，这些数据要么处于事务提交前的状态，要么处于事务提交后的状态
隔离性：一个数据库里存在多个事务处理，每个事务之间是互不影响的 独立的
持久性：一旦事务成功提交之后，数据就会被永远地修改和保留，如果回滚，表示没有操作过

事务的实现方式

SET AUTOCOMMIT 来改变自动提交模式
SET AUTOCOMMIT = 0; # 关闭自动提交模式
SET AUTOCOMMIT = 1; # 开启自动提交模式
MySQL中默认是自动提交
使用事务时应先关闭自动提交

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

需求：lilei从银行账户里取出1000元，账户减少10000元，相应的自己的现金应该增加1000元
set autocommit=0;
start transaction;
update account set cash=cash-1000 where user_name=‘ICBC’;
update account set cash=cash+1000 where user_name=‘lilei’;
commit;
#rollback;
set autocommit=1;
select * from account;

索引

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

主键索引

主键：某一个属性组能唯一标识一条记录
特点：最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置-不需要全表搜索，将索引存在文件中

唯一索引

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

常规索引

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

全文索引

作用：快速定位特定数据
只能用于MyISAM类型的数据表
只能用于char varchar text数据类型
适合大型数据集

添加索引方式一：创建表，声明列属性时添加上

create table test1(
id int(4) primary key,
testno varchar(10) unique,
c varchar(50),
d varchar(20),
e text,
index index_c (c,d),–最左原则
fulltext (e)
)engine=myisam;

添加索引方式二：创建表，将所有列都声明完毕后，再添加索引

create table test2(
id int(4) ,
testno varchar(10),
c varchar(50),
d varchar(20),
e text,
primary key(id),
unique key(testno),
index index_c (c,d),
fulltext (e)
)engine=myisam;

添加索引方式三：先创建表，创建表完毕后，修改表添加索引

create table test3(
id int(4) ,
testno varchar(10),
c varchar(50),
d varchar(20),
e text
)engine=myisam;
alter table test3 add primary key(id);
alter table test3 add unique key(testno);
alter table test3 add index(c,d);
alter table test3 add fulltext(e);

添加索引应用

explain selcect * from student where studentno=‘1000’;

修改表为myisam
alter table student engine=myisam;
添加全文索引
alter table student add fulltext(studentname);
全文索引的应用
查找的数据量不能超过50%；
explain select *from student where match(studentname) against (‘love’);

删除索引

drop index testno on test3;
alter table test3 drop index c;
删除主键索引：
alter table test3 drop primary key;

显示索引信息

show index from student;

索引准则

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

数据库备份

必要性：
保证重要数据不丢失
数据转移
备份方法：

MySQLdump备份，source命令恢复

mysqldump 和mysql是同级命令
mysqldump --help|more 帮助查看mysqldump命令
mysqldump -h 主机名 -u 用户名 -p [option] 数据库名[table1 table2] >path/filename.sql
预存文件目录须有该目录读写权限
备份整个库
mysqldump -uroot -pok school >h:/chenxuan/school.sql
备份表
mysqldump -uroot -pok school grade student >h:/chenxuan/school1.sql
备份时跳过drop table语句
mysqldump -uroot -pok --skip-add-drop-table school grade student >h:/chenxuan/school2.sql
insert into 时加上列名
mysqldump -uroot -pok --skip-add-drop-table -c school grade student >h:/chenxuan/school2.sql
恢复：
source 命令恢复
先连接mysql
mysql -uroot -pok
use test;
source h:/chenxuan/school.sql
mysql恢复:
mysql -uroot -pok test < h:/chenxuan/school.sql

数据库管理工具-sqlyog

school数据库-备份导出-sql文件
选择结构和数据
选择导出的sql文件路径
选择写入文件选项
恢复
选择需要导入的数据库-导入-以sql转储文件导入数据库-选择sql文件
注意事项：打钩的选项看清楚，不影响恢复

sql命令方式来备份和恢复

将school数据库student表中的学号、姓名两列备份出去
use school;
1、备份数据
备份出去的文件不可以提前存在
select studentno,studentname
into outfile ‘h:/chenxuan/student.sql’
from student;
2、将备份出去的数据恢复到test数据库的stutab表中来
use test;
create table stutab(
id int(4),
sname varchar(20)
);
load data infile ‘h:/chenxuan/student.sql’ into table stutab(id,sname);