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

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

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

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

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

1.原子性(Atomicity),即事务不可拆分

2.一致性(Consistency),事务中各部分功能要么都成功,要么都不修改

3.隔离性(Isolation),每个事务处理之间互不影响

4.持久性(Durability),事务处理的结果永久保存

MySQL事务实现方式

1.SET AUTOCOMMIT =0

关闭自动提交模式.

MySQL中默认是自动提交,使用事务前应先关闭自动提交

2.START TRANSACTION

开始一个事务,标记事务的起始点

3.执行事务的语句部分

4.COMMIT/ROLLBACK

如果SQL语句成功,则通过COMMIT提交一个事务给数据库,SQL语句失败,则通过ROLLBACK将事务回滚,数据回到本次事务的初始状态

5.SET AUTOCOMMIT=1

开启MySQL数据库的自动提交

数据库索引

索引的作用：

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

索引分为:

1.主键索引(PRIMARY KEY)

2.唯一索引(UNIQUE)

3.常规索引(INDEX)

4.全文索引(FULLTEXT)

1.主键索引(PRIMARY KEY )

某一个属性或属性的组合能唯一标识一条记录.通常在设置主键的时候会自动加上主键索引
特点:
1.最常见的索引类型
2.确保数据记录的唯一性
3.确定特定数据记录在数据库中的位置

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

2.唯一索引(UNIQUE)

作用:避免同一个表中某数据列中的值重复

主键索引和唯一索引区别:
(1)一个表中主键索引只能有一个,唯一索引可以有多个
(2)主键索引的列不能有空值(NULL),唯一索引的列允许有空值

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

3.常规索引(INDEX)

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

CREATE TABLE `result`(
	#省略一些SQL语句
    INDEX/KEY `ind`(`studentNo`,`subjectNo`)  #创建表时添加
)

ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`)  #建表后追加

4.全文索引(FULLTEXT)

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

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

ALTER TABLE employee ADD FULLTEXT(`first_name`);     #建表后追加

管理索引

1.创建索引

(1)创建表时添加

CREATE TABLE 表名(
      字段名  属性 PRIMARY KEY
      字段名  属性 UNIQUE
或  UNIQUE KEY 索引名(字段名)
      INDEX 索引名(字段名) 
      FULLTEXT 索引名(字段名) 
)ENGINE=MYISAM;

(2)建表后追加

ALTER TABLE 表名 ADD 索引类型(数据列名)

2.删除索引

#删除有索引名的索引
DROP INDEX 索引名 ON 表名
ALTER TABLE 表名 DROP INDEX 索引名
#删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY

3.查看索引

SHOW INDEX(或KEYS) FROM 表名

索引准则

1.索引不是越多越好
2.不要对经常变动的数据加索引
3.小数据量的表建议不要加索引
4.索引一般应加在查询条件的字段(WHERE/ ORDER BY)

添加正确的索引:

1.在WHERE、ORDER BY子句中经常使用的字段
2.字段的值是多个(例如性别字段则不适合)
3.字段内容不是经常变化的(如果经常变化的字段,添加索引反而降低性能)
4.不宜过多添加索引(每添加一条索引都会占用磁盘空间)

分析SQL语句的执行性能

分析SQL语句的执行性能

语法:

EXPLAIN 表名(DESC 表名)
EXPLAIN SELECT语句

分析结果中type为连接类型,从最好到最差的连接类型为:
const、eq_reg、ref、range、indexhe和ALL

MySQL的备份和还原

备份

备份必要性:
1.保证重要数据不丢失
2.数据转移

备份类型

(1)热备份:边使用边备份

(2)冷备份:在数据库不工作的时候备份

(1)全部备份:备份全部数据

(2)增量备份:只备份新变动的数据部分

MySQL数据库备份方法

1.mysqldump备份工具

mysqldump客户端作用:

1.转储数据库
2.收集数据库进行备份
3.将数据转移到另一个SQL服务器中(不一定是MySQL服务器)

mysqldump -h主机名 -u用户名 -p [options] 数据库名 [table1 table2 table3] >path/filename.sql

mysqldump常用选项

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

2.数据库管理工具,如SQLyog

3.物理备份,直接拷贝数据库文件和相关配置文件

4.SQL语句导出数据

  SELECT * INTO OUTFILE 'file_name' FROM tbl_name

输出的文件不能提前存在,否则报错

还原

1.用SOURCE语法还原

SOURCE在DOS命令中的MySQL命令行里执行:

SOURCE 库名 /path/db_name.sql;

/path/是一个绝对路径,并且必须是mysql运行用户有权限读取的文件
需要先创建和选择数据库

use 库名;
SOURCE ......  

2.用mysql客户端还原

在DOS命令中输入

mysql -u用户名 -p密码  库名 </path/db_name.sql

3.SQL语句导入数据

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

新建的表的列类型需一致