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

MySQL的事务处理

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

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

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

• 原子性（Atomicity）（事务是一个不可分割的工作单位，事务中的操作要么都发生要么都不发生）

• 一致性（Consistency）（数据的一致性）

• 隔离性（Isolation）（事务与事务之间相互隔离）

• 持久性（Durability）（永久写入硬盘，永久保存）

MySQL的事务实现方法：

SET AUTOCOMMIT

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

• MySQL中默认是自动提交

• 使用事务时应先关闭自动提交

SET AUTOCOMMIT  = 0;    # 关闭自动提交模式
SET AUTOCOMMIT  = 1;    # 开启自动提交模式

START TRANSACTION

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

COMMIT

提交一个事务给数据库

ROLLBACK

将事务回滚，数据回到本次事务的初始状态

SET AUTOCOMMIT = 1;

开启MySQL数据库的自动提交

代码：

mysql数据库自动开启了事务提交，所以应先关闭自动提交，改为手动提交
SET AUTOCOMMIT  = 0;    # 关闭自动提交模式（0关闭1开启）
​
START TRANSACTION #开启事务
​
....sql代码....
​
COMMIT #提交事务  或  ROLLBACK #事务回滚
​
SET AUTOCOMMIT = 1;#开启事务提交，以便接下来使用

MySQL事务处理步骤示意图：

脏读：

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据

当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据， 就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下

UPDATE account SET money=money + 100 WHERE name=’B’;  
(此时A通知B)
​
UPDATE account SET money=money - 100 WHERE name=’A’;

当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读）， 而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

视图

什么是视图？ 视图是由查询结果形成的一张虚拟表。

什么时候要用到视图？ 如果某个查询结果出现的非常频繁，也就是，要经常拿这个查询结果来做子查询

视图的创建语法： create view 视图名 as select 语句; 

数据库索引

什么叫数据库索引？ 索引是对数据库中一列或者多列的值进行排序的一种数据结构。重点：对列的值进行排序的数据结构。使用索引可以快速访问数据库中的记录。

索引的主要用途是什么？ 执行select语句时候会使用索引，索引主要用来提高查询性能。由于索引是经过某种算法优化过的，因而查找次数要少的多。

不使用索引情况下，sql查询语句是怎么执行的？ 如果没有索引，必须遍历整个表。例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的。

作用

• 提高查询速度

• 确保数据的唯一性

• 可以加速表和表之间的连接，实现表与表之间的参照完整性

• 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间

• 全文检索字段进行搜索优化

分类

• 主键索引（PRIMARY KEY）

• 唯一索引（UNIQUE）

• 常规索引（INDEX）

• 全文索引（FULLTEXT）

#添加主键索引
ALTER table student add PRIMARY Key(StudentNo);
#添加唯一索引
ALTER table student add UNIQUE(StudentName);
#添加普通索引
ALTER table student add INDEX(IdentityCard);
#全文索引
ALTER table student add FULLTEXT(Phone)
​
explain  select .....可以看到执行效率

主键索引

某一个属性或属性的组合能唯一标识一条记录如：学生表（学号，姓名，班级，性别等） ，学号就是唯一标识的，可作为主键

特点：最常见的索引类型，确保数据记录的唯一性，确定特定数据记录在数据库中的位置

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

唯一索引

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

与主键索引的区别

• 主键索引只能有一个

• 唯一索引可有多个

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

常规索引

作用：快速定位特定数据

注意：

• index和key关键字都可设置常规索引

• 应加在查找条件的字段

• 不宜添加太多常规索引，影响数据的插入、删除和修改操作

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

全文索引

作用：快速定位特定数据

注意：

• 只能用于MyISAM类型的数据表

• 只能用于 CHAR 、 VARCHAR、TEXT数据列类型

• 适合大型数据集

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

分词器：

WITH PARSER ngram表示使用ngram分词器进行索引。

ngram是一种用于全文搜索的技术，它将文本分成更小的片段，从而使得搜索更加灵活。

ALTER TABLE student ADD FULLTEXT dizhi(address) WITH PARSER ngram;
SELECT * FROM student WHERE MATCH(address) AGAINST('北京')

这个语句，使用ngram分词器来创建全文索引，分词为'北京'。

管理索引

创建索引：创建表时添加 建表后追加

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

删除索引

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

查看索引

SHOW  INDEX(或KEYS) FROM 表名

索引准则：

• 索引不是越多越好

• 不要对经常变动的数据加索引

• 小数据量的表建议不要加索引

• 索引一般应加在查找条件的字段

添加正确的索引：

• 在WHERE、ORDER BY 子句中经常使用的字段

• 字段的值是多个（例如性别字段则不适合）

• 字段内容不是经常变化的（经常变化的字段，添加索引反而降低性能）

• 不宜过多添加索引（每添加一条索引都会占用磁盘空间）

哪些情况需要创建索引？（面试题）

• 主键自动建立唯一索引

• 频繁作为查询条件的字段应该创建索引

• 查询中与其他表关联的字段，外键关系建立索引

• 频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引

• WHERE条件里用不到的字段不创建索引

• 单键/组合 复合索引的选择问题，who?(在高并发下倾向创建组合索引)

• 查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度

• 查询中统计或者分组字段

哪些情况不要创建索引？

• 表记录太少

• 经常增删改的表 （ 提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和 DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。）

• 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

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常用选项

符号名称	描述
--add-drop-table	导出sql 脚本会加上DROP TABLE IF EXISTS 语句默认是打开的，可以用 --skip-add-drop-table 来取消
--add-locks	该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句 好处：防止记录被再次导入时，其他用户对表进行的操作，默认是打开的
-t 或--no-create-info	忽略不写重新创建每个转储表的CREATE TABLE语句
-c或 --complete-insert	在每个INERT语句的列上加上字段名在数据库导入另一个数据库时非常有用
-d 或 --no-data	不写表的任何行信息。对于只想转储表的结构很有用
--where "where-condition",    -w "where-condition"	只转储给定的WHERE条件选择的记录
--opt	该选项是速记；等同于指定 --add-drop-tables --add-locking --create-option --disable-keys--extended-insert --lock-tables --quick --set-charset

可通过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]