mysql 备份 索引_MySQL的事物、视图、索引、备份和恢复

MySQL的事物、视图、索引、备份和恢复

使用事物插入多条成绩记录

在使用数据库时，只要发生数据传输、数据存储、数据交换等操作，就有可能产生数据故障，这时，如果没有采取数据备份和数据恢复的措施，就会导致数据丢失。

事物是指将一系列数据操作捆绑成为一个整体进行统一管理。如果某一事物执行成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事物执行时遇到错误且必须取消或回滚，则数据将全部恢复到操作前的状态，所有数据的更改均被清除

什么是事物

事物是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。因此事物是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事物是作为最小的控制单元来使用的，特别适用于多用户同时操作的数据库系统。

事物是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有4个属性，即原子性、一致性、隔离性及持久性，这些特性通常简称为ACID。

原子性

事物是一个完整的操作。事物的个元素是不可分的(原子的)。事物中的所有元素必须作为一个整体提交或回滚。如果事物中的任何元素失败，则整个事物将失败。

一致性

当食物完成时，数据必须处于一致状态。也就是说，在事物开始之前，数据库中存储的数据处于一致状态。在正在进行的事物中，数据可能处于不一致的状态，如数据可能有部分被修改。然而，当事物完成时，数据必须再次回到已知的一致状态。通过事物对数据所做的修改不能损坏数据，或者说事物不能使数据存储处于不稳定的状态。

隔离性

对数据进行修改的所有并发事物使彼此隔离的，这表明事物必须是独立的，它不应以任何方式依赖或影响其他事物。修改数据的事物可以在另一个使用相同数据的事物开始之前访问这些数据，或者在另一个使用相同数据的事物结束之后访问这些数据。另外，当事物修改数据时，如果任何其他进程正在使用相同的数据，则直到该事务成功提交之后，对数据的修改才能生效。

持久性

事物的持久性指不管系统是否发生了故障，事物处理的结果都是永久的。

一个事务成功完成之后，它对数据库的改变是永久性的，即使出现故障也是如此。也就是说，一旦事物被提交，事物的效果会被永久地保留在数据库中。

如何执行事物

MySQL中提供了多种存储引擎来支持事物，支持事物的存储引擎又InnoDB和BDB。InnoDB存储引擎管理事物主要通过UNDO日志和REDO日志实现，MyISAM存储引擎不支持事物。

只是扩展：任何一种数据都会拥有各种各样的日志，用来记录数据库的运行情况、日常操作、错误信息等，MySQL也不例外。为了维护MySQL服务器，经常需要在MySQL数据库中进行日志操作

UNDO日志：复制事物执行前的数据，用于在事物发生异常时回滚数据。

REDO日志：记录在事物执行中，对数据进行的每条更新操作，当事物提交时，该内容将被刷新到磁盘。

默认设置下，每条SQL语句就是一个事物，即执行SQL语句后自动提交。为了达到将几个操作作为一个整体的目的，需要使用BEGIN或START TRANSACTION开启一个事物，或者执行命令SET AUTOCOMMIT=0，来禁止当前会还的自动提交，命令后面的语句作为事物的开始。

MySQL使用下列语句来管理事物

开始事物，语法格式：

BEGIN;

#或

START TRANSACTION

#这个语句显式地标记一个事物地起始点

提交事物，语法格式

COMMIT;

#这个语句标志一个事物成功提交。自事物开始至提交语句之间执行的所有数据更新将永远地保存在数据#库数据文件中，并释放连接时占用的资源。

回滚(撤销)事物，语法格式：

ROLLBACK;

#清除自事物起始点至该语句所做地所有数据更新操作，将数据状态回滚到事物开始前，并释放由事物控#制的资源。

BEGIN或START TRANSACTION语句后面的SQL语句对数据的更新操作都将记录在事物日志中，直至遇到ROLLBACK语句或COMMIT语句。如果事务中某一操作失败且执行了ROLLBACK语句，那么在开启事物语句之后所有更新的数据都能回滚到事物开始之前的状态。如果事务中的所有操作都全部正确完成，并且使用了COMMIT语句向数据库提交更新数据，则此时的数据又处在新的一致状态。

一个事务的所有语句成功执行并提交后，对数据所做的更新将一起提交。其他会话读取到的时更新后的数据。

在数据库中，为了有效保证并发读取数据的正确性，提出了事物隔离级别。在MySQL中，事物的默认隔离界别时REPEATABLE-READ(可重读)隔离级别。

+ 设置自动提交关闭或开启事物

+ MySQL默认开启自动提交模式，即未指定开启事物时，每条SQL语句都是单独的事物，执行完毕自动提交。可以关闭自动提交模式，采取手动提交或回滚事物。

+ 语法格式如下

```

SET autocommit=0/1;

#值为0：关闭自动提交。

#值为1：开启自动提交。

#执行SET autocommit=0后关闭自动提交，从下一条SQL语句开始则开启了新事物，需使用#COMMIT或ROLLBACK语句结束该事务。

```

+ 编写事物需要遵从以下原则

+ 事物尽可能简短。事物启动至结束在数据库管理系统中会保留大量资源，以保证事物的原子性、一致性、隔离性和持久性。如果在多用户系统中，较大的事物将会，较大的事物将会占用系统的大量资源，使系统不堪重负，影响软件的运行性能，甚至导致系统崩溃。

+ 事务中访问的数据量尽量最少。当并发执行事物处理时，事物操作的数据量越少，事物之间对操作数据的争夺就越少。

+ 查询数据时不要使用事物。对数据进行浏览查询操作并不会更新数据库的数据，因此不使用事物查询数据，以避免占用过量的系统资源。

+ 在事物处理过程中尽量不要出现等待用户输入的操作。在处理事物的过程中，如果需要等待用户输入数据，那么事物会长时间地占用资源，有可能造成系统阻塞。

使用视图查看成绩记录

如何提高表操作地安全性呢？我们可以把重复使用的复杂的查询结果保存成视图，也可以以某表数据如员工信息表为基础，设定不同访问权限的视图，不同岗位的员工们调用不同的视图来获得自己有权查看的相关数据，以保证数据访问的安全性。

借助视图把复杂的代码封装保存起来，当需要时，只需调用并执行对应的视图就可以轻松地完成任务。

什么时视图

视图是一种查看数据库中一个或多个表中数据的方法。视图是一种虚拟表，通常是作为来自一个或多个表的行或列的子集创建。当然，它也可以包含全部的行和列。但是，视图并不是数据库中存储的数据值的集合，它的行和列来自查询中引用的表。在执行时，它直接显示来自于表中的数据。

视图充当着查询中的表筛选器的角色。定义视图的查询可以基于一个或多个表，页可以基于其他视图、当前数据库或其他数据库。

视图通常用来进行以下3中操作：1.筛选表中的行；2.防止未经许可的用户访问敏感数据；3.将多个物理数据抽象为一个逻辑数据表

使用视图可以给用户和开发人员带来很多好处，具体如下：

对最终用户的好处：1.结果更容易理解。创建视图时可以将列名改为有意义的名称，使用户更容易理解所代表的内容。在视图中修改列名不会影响基表的列名；2.获得数据更容易。很多人对SQL不太了解，因为对他们来说，创建多个表的复杂查询很困难，通过创建视图可以方便用户访问多个表中的数据。

对开发人员的好处：1.限制数据检索更容易。开发人员有时需要隐藏某些行或列中的信息。通过使用视图，用户可以灵活地访问它们需要的数据，同时保证同一个表或其他表中的其他数据的安全性。要实现这一目标，可以在创建视图时将对用户保密的列排除在外；2. 维护应用程序更方便。调试视图比调试查询更容易，跟踪视图中各个步骤的错误更为容易，这是因为所有的步骤都是视图的组成部分。

如何创建和使用视图

使用SQL语句创建视图

使用SQL语句创建视图的语法格式如下

CREATE VIEW 视图名

AS

注意：在SQL语句命名规范中，视图一般以view_xxx或v_xxx的样式来命名。与创建数据表相同，在创建视图之前，如果在数据库中已存在同名视图，需要先删除再创建

使用SQL语句删除视图

使用SQL语句删除指定视图的语法格式如下。

DROP VIEW [IF EXISTS] 视图名

使用SQL语句查看视图数据

使用SQL语句查看视图数据的语法格式如下

SELECT 字段1，字段2... ...FROM view_name;

#使用查询语句SELECT查询视图，即可获得数据结果集。

使用视图的注意事项

每个视图中可以使用多个表。

与查询类似，一个视图可以嵌套另一个视图，但最好不要超过三层

对试图数据进行添加、更新和删除操作直接一项原表中的数据。

当视图来自多个表时，不允许添加和删除数据。

说明：从一个或多个表或视图中导出的虚拟表，其结构和数据是建立在对表的查询基础上的。理论上它可以像普通的物理表一样使用，如增加、删除、修改和查询等。利用视图更新数据实际上是对数据库中的原始数据表进行更新操作。因此使用视图修改数据库数据会有许多限制，所以一般在实际开发中视图仅用作查询。

知识扩展：对视图和表中的数据进行查询、删除、更新等操作很类似，那么如何区分视图和表呢？在MySQL数据库安装成功后，会自动创建系统数据库 information_schema，在该数据库中存在一个包含视图信息的表views，可以通过views来查看所有视图的相关信息。SQL语句如下：USE information_schema;SELECT * FROM views/G

创建数据表索引

索引提供指针以指针存储在表中指定列的数据值，再根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似：铜鼓哦搜索索引找到特定的值，再跟随指针到达包含该值的行。

什么是索引

在数据库中，索引使数据库程序无需对整个表进行扫描，就可以再其中找到所需数据。在数据库中，由于数据存储在数据表中，因此索引使创建在数据库表对象上的，由表中的一个字段或多个字段生成的键组成，这些键存储在数据结构B-树或哈希表中，通过MySQL可以快速有效地查找与键值相关联的字段，根据索引的存储类型，可以将索引分为B-树索引(BTREE)和哈希索引(HASH)。InnoDB和MyISAM存储引擎支持B- 树索引。

索引的作用是通过使用索引，大大提高数据库的检索速度，改善数据库性能。

索引分类

普通索引

普通索引使MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。它的唯一任务是加快对数据的访问速度。因此，应该只为那些最常出现在查询条件(WHERE)或排序条件(ORDER BY)中的数据列创建索引。

唯一索引

唯一索引不允许两行具有相同的索引值。

如果现有数据中存在重复的键值，则一般情况下多数数据库都不允许创建唯一索引。若已创建了唯一索引，则当插入新数据使表中的键值重复时，数据库将拒绝接收此数据。创建了唯一索引的列允许有空值。

提示：若创建了唯一约束，则将自动创建唯一索引。尽管唯一索引有助于找到信息，但为了获得最佳性能，仍建议使用主键约束。

主键索引

在数据库中为表定义主键时将自动创建主键索引，主键索引是唯一索引的特殊类型。

主键索引要求主键中的每个值是非空、唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

复合索引

在创建索引时，并不是只能对其中一列创建索引，与创建主键一样，可以将多个列组合作为索引，这种索引成为符合索引。

需要注意的是，只有在查询中使用了组合索引最左边的字段时，索引才会被使用，即第一个字段作为前缀的集合。

全文索引

全文索引的作用是在定义索引的列上支持支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或TEXT类型的列上创建，主要用于在大量文本文字中搜索字符串，此时使用全文搜索的效率将大大高于使用SQL的LIKE关键字的效率。MySQL4.5中只有MyISAM存储引擎支持全文索引。

空间索引

空间索引是对空间数据类型的列建立的索引，如GEOMETRY、POINT等。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

创建索引

使用 CREATE INDEX语句可以在已经存在的表上添加索引，基本语法如下

CREATE [UNQUE|FULLTEXT|SPATIL] INDEX index name

ON table_name(column_name[length])

UNQUE|FULLTEXT|SPATIL:分别表示唯一索引、全文索引和空间索引，为可选参数；

index_name:指定索引名；

column_name:指定需要创建索引的列；

length:指定索引长度，可选参数，只有字符串类型才能指定索引长度。

删除索引

删除索引的语法格式如下。

DROP INDEX table_name.index name;

关于索引的删除需要注意以下两点。

删除表时，该表的所有索引将同时被删除。

删除表中的列表时，如果要删除的列是索引的组成部分，则该列也会从索引中删除

使用索引可加快数据检索速度，但没有必要为每个列都建立索引。因为索引自身也需要维护，并占用一定资源，可以按照下列标准选择建立索引。

频繁搜索的列。

经常排序、分组的列。

经常用作连接的列(主键/外键)。

请不要使用下面的列创建索引。

仅包含几个不同值的列。

表中仅包含几行。为小型表创建索引可能不太实用，因为在索引中搜索数据所花的时间比表中逐行搜索所画的时间更长。

经验：在SQL语句中，特别是在SELECT语句中正确使用索引可以大大提高查询速度，从而提升应用程序的运行性能。作为一名软件工程师在编写和调试SQL语句时，要具有优化SQL语句的意识。下面几条经验可供实际工作中参考。

查询时减少使用*返回全部列，不要返回不需要的列。

索引应该尽量小，在字节数小的列上建立索引。

WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他表达式之前。

避免在ORDER BY 子句中使用表达式。

根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理。

查看索引

在MySQL中，可以使用SHOW INDEX语句查看已创建的索引。语法格式如下。

SHOW INDEX FROM table_name;

数据的备份和恢复

在任何数据库环境中，计算机系统的各种软硬件故障或者认为误操作导致的数据损害都是很难避免的，为了防止数据丢失，将损失降到最低，定期对数据库进行备份时非常必要的，以便发生意外情况后可以即使恢复数据

使用mysqldump命令备份数据库

mysqldump命令格式

语法格式如下

mysqldump -u username -h host -p password

dbname[tbname1[,tbname2... ...]]>filename.sql

其中：

username:表示用户名。

host：表示登录用户的主机名称，如本机为主机可省略。

password:表示登录密码。

dbname:表示需要备份的数据库。

tbname:表示需要备份的数据表，可以指定多张表，为可选项，如备份整个数据库则此向省略；

filename.sql:表示备份文件的名称。

mysqldump是DOS系统下的命令，在使用时无需进入mysql命令行，否则将无法执行。

mysqldump的常用参数

mysqldump还有一些其他参数可以用来定制备份过程。

参数

描述

-add-drop-table

在每个CREATE TABLE 语句前添加DROP TABLE语句，默认是打开的，可以用-skip-add-drop-table来取消

-add-locks

该选项会在INSERT语句中捆绑一个LOCK TABLE 和 UNLOCK TABLE语句，防止记录再次导入时，其他用户对表进行操作，默认是打开的

-t或-no-create-info

只导出数据，而不添加CREATE TABLE语句

-c或--complete-insert

在每个INSERT 语句列上加上列名，在将数据导入另一个数据库时有用

-d或--no-data

不写表的任何信息，只转储表的结构

-opt

该选项是速记参数，等同于指定如下参数项--add-drop-tables-add-locking --create-option --disable-keys-extended-insert --lock-tables-quick ---set-charset

使用mysql命令恢复数据库

对于备份数据库生成的包含有建库、建表、插入数据等SQL语句的文本文件，可以通过mysql命令还原到新的数据库中，实现数据库的恢复。该命令语法格式如下

mysql -u username -p [dbname]

username 表示用户名；

dbname 表示数据库名；

filename.sql 为执行数据库备份后生成的文件。

该命令执行成功后，备份文件中的语句将在指定的数据库中恢复原有数据

注意：在执行该语句之前，必须在MySQL服务器中创建新数据库，如果不存在新数据库，恢复数据库过程将会出错

mysql命令是DOS环境下的恢复数据库命令，如果已经登陆了MySQL服务器，也可以使用source命令恢复数据库。语法格式如下

source filename;

#其中，filename为数据库备份文件。

#在执行source命令之前，同样需要先创建新数据库，并且使用USE语句选择该数据库，否则将会出现错误。

通过复制文件实现数据备份和恢复

MySQL服务器中的数据在磁盘中是以文件形式保存的，所以可以直接复制MySQL数据库的存储目录及文件进行备份。MySQL默认的数据库文件存储目录在不同的操作系统下有所不同。

由于MySQL服务器的数据文件在服务运行期间总是处于打开和使用状态，这样会导致文件副本备份不一定总是有效。因此，在复制数据文件之前，需要先停止MySQL服务。这种方法虽然简单，但并不是最好的方法。一般情况下，MySQL服务在使用过程中不允许被停止，并且这种方法对InnoDB存储引擎的表不适用。使用这种方法备份的数据最好还原到相同版本的服务器中，不同的版本可能不兼容。

注意：使用此种方法备份数据库时，为了保证所备份数据的完整性，在停止MySQL数据库服务器之前，需要限制性FLUSH TABLES语句将所有数据写入到数据文件中

表数据导出到文本文件

铜鼓哦对表数据的导出和导入，可以实现在MySQL数据库服务器与其他数据库服务器之间移动数据。导出操作是指将数据从MySQL数据表复制到文本文件。数据导出的方式有多种，这里主要介绍使用SELECT... ... INTO OUTFILE语句导出数据。其语法格式如下。

SELECT [file_name] FROM tablename

[WHERE condition]

INTO OUTFILE 'filename' [OPTION]

从上述语法中可以看出，该导出语句分成以下两部分。

普通的数据查询语句，主要用来获取所要导出到文本文件中的数据。

通过参数filename指定导出数据的目标文件。

知识扩展：为使导出的文本文件可读性更好，可在SELECT... ...INTO OUTFILE语句后设置相应参数选项，常用参数选项如下：

FIELDS TERMINATED BY 'string'：用来设置字段的分隔符为字符串对象(string)，默认为“\t”

FIELDS [OPTIONALLY] ENCLOSED BU 'char':用来设置字段值的字符符号，如果使用了OPTIONALLY，则只有CHAR和VARCHAR等字符数据字段。默认情况下不适用任何符号。

FIELDS ESCAPED BY 'char':用来设置转义字符的字符符号，默认情况下使用“\”字符。

LINES STRING BY 'char':用来设置每行开头的字符符号，默认情况下不适用任何符号。

LINES TERMINATED BY 'string ':用来设置每行结束的字符符号，默认情况下使用“\n”字符串。

若需要导出文件的显示格式，每条数据记录为一行，每行数据记录以“>”开头，字段值之间以“、”分隔，各数值使用引号括起来

文本文件导入到数据表

所谓导入操作，是指将数据从文本文件加载到MySQL数据库表里。同样，导入数据的方式也有多种。语法格式如下。

LOAD DATA INFILE filename INTO TABLE tablename [OPTION]

filename:用来指定文本文件的路径和文件名。

tablename:用来指定导入表的名称。

知识扩展：如果导出文件中使用了参数选项改变了显示格式，那么导入数据时同样需要设置相应参数。常用参数选项与使用SELECT... ...INTO OUTFILE导入参数对应。