2020-08-15 MySQL事务、索引、数据恢复和备份

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

MySQL的事务处理

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

事务的ACID原则

• 原子性(Atomic)
• 一致性(Consist)
• 隔离性(Isolated)
• 持久性(Durable)

事务的实现

使用set语句来改变自动提交模式

set autocommit=0;
set autocommit=1;

MySQL的事务实现方法

start transaction #开始一个事务,标记事务的起始点
commit #提交一个事务给数据库
rollback #将事务回滚,数据回到本次事务的初始状态
set autocommit=1; #还原MySQL数据库的自动提交

MySQL事务处理步骤

1. 关闭自动提交 set autocommit=0;
2. 开始一个事务,标记事务的起始点 start transaction;
3. 提交一个事务给数据库 commit; 或者 将事务回滚,所有commit中的操作将被取消 rollback;
4. 还原MySQL数据库的自动提交 set autocommit=1;

create table account(
	id int(4) primary key auto_increment,
	name varchar(32) not null,
	cash decimal(9,2) not null
);
insert into account(name ,cash)
calues ('A',2000),('B',10000
);

set autocommit =0;
start transaction;
update account set cash=cash-500 where `name`='A';
update account set cash=cash+500 where `name`='b';
commit;
set autocommit=1;

数据库索引

• 作用:
  • • 提高查询速度
  • • 确保数据的唯一性
  • • 可以加速表和表之间的连接,实现表与表之间的参照完整性
  • • 使用分组和排序子句进行数据检索,可以显著减少分组和排序的时间
  • • 全文检索字段进行搜索优化
• 分类:
  • • 主键索引(primary key)
  • • 唯一索引(unique)
  • • 常规索引(index)
  • • 全文索引(fulltext)
      主键索引
• 某一个属性组能唯一标识一条记录
  特点:
• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置
  唯一索引
• 唯一索引(unique)
  • • 避免同一个表中某个数据列中的值重复
• 与主键索引的区别
  • • 主键索引只能有一个 且不能为空
  • • 唯一索引可有多个 可以为空
      全文索引
• 作用:
  • • 快速定位特定数据
• 注意
  • • 只能用于Myisam类型的数据表
  • • 只能用于char varchat 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);


#使用全文索引
select * from student where match(studentname) against('内容');

删除索引

drop index testno on test3;#方法一
alter table test3 drop index c; #方法二
alter table test3 drop primary key;#删除主键

显示索引信息

show index from student;

索引准则

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

MySQL的备份

• 数据库备份必要性
  • • 保证重要数据不丢失
  • • 数据转移
• MySQL数据库备份方法
  • • mysqldump备份工具:和MySQL同级的命令
  • • 数据库管理工具,如SQLyog
  • • 直接拷贝数据库文件和相关配置文件
      mysqldump
• 作用:
  • • 转储数据库
  • • 搜集数据库进行备份
  • • 将数据转移到另一个SQL服务器(不一定是MySQL服务器)

mysqkdump -h主机名 -u 用户名 -p密码  [options] 数据库名>备份到哪里
mysqldump -troot -p --sakp-add-drop-table myschool grade studemt>h:/xx/school.sql
如:h:/xx/school.sql

source命令恢复

mysql -hlocalhost -u用户 -p密码 
use 库名;
database changed
source 备份路径 如:h:/xx/school.sql
二法:
exit;
mysql -uroot -p密码 test<h:/xx/school.sql