MySQL 第26章、第27章、第28章、第29章、第30章 管理事务处理、全球化和本地化、安全管理、数据库维护、改善性能

管理事务处理

事务处理

事务处理(transaction processing)可以用来维护数据库的完整性，它 保证成批的MySQL操作要么完全执行，要么完全不执行。

正如第15章所述，关系数据库设计把数据存储在多个表中，使数据 更容易操纵、维护和重用。不用深究如何以及为什么进行关系数据库设 计，在某种程度上说，设计良好的数据库模式都是关联的。

前面章中使用的orders表就是一个很好的例子。订单存储在orders 和orderitems两个表中:orders存储实际的订单，而orderitems存储订 购的各项物品。这两个表使用称为主键(参阅第1章)的唯一ID互相关联。 这两个表又与包含客户和产品信息的其他表相关联。

给系统添加订单的过程如下。
(1) 检查数据库中是否存在相应的客户(从customers表查询)，如果 不存在，添加他/她。
(2) 检索客户的ID。
(3) 添加一行到orders表，把它与客户ID关联。
(4) 检索orders表中赋予的新订单ID。
(5) 对于订购的每个物品在orderitems表中添加一行，通过检索
出来的ID把它与orders表关联(以及通过产品ID与products表关联)。 现在，假如由于某种数据库故障(如超出磁盘空间、安全限制、表
锁等)阻止了这个过程的完成。数据库中的数据会出现什么情况?
如果故障发生在添加了客户之后，orders表添加之前，不会有什么 问题。某些客户没有订单是完全合法的。在重新执行此过程时，所插入 的客户记录将被检索和使用。可以有效地从出故障的地方开始执行此过 程。
但是，如果故障发生在orders行添加之后，orderitems行添加之前， 怎么办呢?现在，数据库中有一个空订单。
更糟的是，如果系统在添加orderitems行之中出现故障。结果是数 据库中存在不完整的订单，而且你还不知道。
如何解决这种问题?这里就需要使用事务处理了。事务处理是一种 机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完 整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们 或者作为整体执行，或者完全不执行(除非明确指示)。如果没有错误发生，整组语句提交给(写到)数据库表。如果发生错误，则进行回退(撤 销)以恢复数据库到某个已知且安全的状态。

因此，请看相同的例子，这次我们说明过程如何工作。
(1) 检查数据库中是否存在相应的客户，如果不存在，添加他/她。 (2) 提交客户信息。
(3) 检索客户的ID。
(4) 添加一行到orders表。
(5) 如果在添加行到orders表时出现故障，回退。
(6) 检索orders表中赋予的新订单ID。
(7) 对于订购的每项物品，添加新行到orderitems表。
(8) 如果在添加新行到orderitems时出现故障，回退所有添加的
orderitems行和orders行。
(9) 提交订单信息。
在使用事务和事务处理时，有几个关键词汇反复出现。下面是关于 事务处理需要知道的几个术语:

 事务(transaction)指一组SQL语句;
 回退(rollback)指撤销指定SQL语句的过程;
 提交(commit)指将未存储的SQL语句结果写入数据库表;
 保留点(savepoint)指事务处理中设置的临时占位符(place-
holder)，你可以对它发布回退(与回退整个事务处理不同)。

控制事务处理

既然我们已经知道了什么是事务处理，下面讨论事务处理的管理中 所涉及的问题。

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数 据何时应该回退，何时不应该回退。

MySQL使用下面的语句来标识事务的开始:

使用ROLLBACK

MySQL的ROLLBACK命令用来回退(撤销)MySQL语句，请看下面的语句:

使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交(implicit commit)，即提交(写或保存)操作是自动进行的。

但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交， 使用COMMIT语句，如下所示:

隐含事务关闭 当COMMIT或ROLLBACK语句执行后，事务会自 动关闭(将来的更改会隐含提交)。

使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但 是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退。

例如，前面描述的添加订单的过程为一个事务处理。如果发生错误， 只需要返回到添加orders行之前即可，不需要回退到customers表(如果 存在的话)。

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。

这些占位符称为保留点。为了创建占位符，可如下使用SAVEPOINT 语句:

更改默认的提交行为

全球化本地化

本章介绍MySQL处理不同字符集和语言的基础知识。

字符集和校对顺序

数据库表被用来存储和检索数据。不同的语言和字符集需要以不同 的方式存储和检索。因此，MySQL需要适应不同的字符集(不同的字母 和字符)，适应不同的排序和检索数据的方法。

在讨论多种语言和字符集时，将会遇到以下重要术语:

 字符集为字母和符号的集合;
 编码为某个字符集成员的内部表示;
 校对为规定字符如何比较的指令。

在MySQL的正常数据库活动(SELECT、INSERT等)中，不需要操心太多的东西。使用何种字符集和校对的决定在服务器、数据库和表级进行。

使用字符集和校对顺序

MySQL支持众多的字符集。为查看所支持的字符集完整列表，使用
以下语句:

安全管理

数据库服务器通常包含关键的数据，确保这些数据的安全和完整需 要利用访问控制。本章将学习MySQL的访问控制和用户管理。

访问控制

MySQL服务器的安全基础是:用户应该对他们需要的数据具有适当 的访问权，既不能多也不能少。换句话说，用户不能对过多的数据具有 过多的访问权。

考虑以下内容:
 多数用户只需要对表进行读和写，但少数用户甚至需要能创建和 删除表;
 某些用户需要读表，但可能不需要更新表;
 你可能想允许用户添加数据，但不允许他们删除数据;
 某些用户(管理员)可能需要处理用户账号的权限，但多数用户
不需要;
 你可能想让用户通过存储过程访问数据，但不允许他们直接访问
数据;
 你可能想根据用户登录的地点限制对某些功能的访问。

这些都只是例子，但有助于说明一个重要的事实，即你需要给用户 提供他们所需的访问权，且仅提供他们所需的访问权。这就是所谓的访 问控制，管理访问控制需要创建和管理用户账号。

管理用户

MySQL用户账号和信息存储在名为mysql的MySQL数据库中。一般 不需要直接访问mysql数据库和表(你稍后会明白这一点)，但有时需要 直接访问。需要直接访问它的时机之一是在需要获得所有用户账号列表 时。为此，可使用以下代码:

创建用户账号

删除用户账号

为了删除一个用户账号(以及相关的权限)，使用DROP USER语句，
如下所示:

设置访问权限

更改口令

数据库维护

本章学习如何进行常见的数据库维护。

备份数据

像所有数据一样，MySQL的数据也必须经常备份。由于MySQL数据 库是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据。 但是，由于这些文件总是处于打开和使用状态，普通的文件副本备份不 一定总是有效。

下面列出这个问题的可能解决方案。

 使用命令行实用程序mysqldump转储所有数据库内容到某个外部 文件。在进行常规备份前这个实用程序应该正常运行，以便能正 确地备份转储文件。

 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据 (并非所有数据库引擎都支持这个实用程序)。

 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所 有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原。

首先刷新未写数据 为了保证所有数据被写到磁盘(包括索引 数据)，可能需要在进行备份前使用FLUSH TABLES语句。

进行数据库维护

所用的空间，从而优化表的性能。

诊断启动问题

服务器启动问题通常在对MySQL配置或服务器本身进行更改时出 现。MySQL在这个问题发生时报告错误，但由于多数MySQL服务器是作 为系统进程或服务自动启动的，这些消息可能看不到。

在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL 服务器自身通过在命令行上执行mysqld启动。下面是几个重要的mysqld 命令行选项:
 --help显示帮助——一个选项列表;
 --safe-mode装载减去某些最佳配置的服务器;
 --verbose显示全文本消息(为获得更详细的帮助消息与–help
联合使用);
 --version显示版本信息然后退出。

几个另外的命令行选项(与日志文件的使用有关)在下一节列出。

查看日志文件

MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下
几种。

 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此 日志通常名为hostname.err，位于data目录中。此日志名可用 --log-error命令行选项更改。

 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此 日志文件可能会很快地变得非常大，因此不应该长期使用它。此 日志通常名为hostname.log，位于data目录中。此名字可以用 --log命令行选项更改。

 二进制日志。它记录更新过数据(或者可能更新过数据)的所有 语句。此日志通常名为hostname-bin，位于data目录内。此名字 可以用–log-bin命令行选项更改。注意，这个日志文件是MySQL 5中添加的，以前的MySQL版本中使用的是更新日志。

 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这 个日志在确定数据库何处需要优化很有用。此日志通常名为 hostname-slow.log，位于data目录中。此名字可以用–log-slow-queries命令行选项更改。 在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文
件。

改善性能

改善性能

数据库管理员把他们生命中的相当一部份时间花在了调整、试验以 改善DBMS性能之上。在诊断应用的滞缓现象和性能问题时，性能不良的 数据库(以及数据库查询)通常是最常见的祸因。

可以看出，下面的内容并不能完全决定MySQL的性能。我们只是 想回顾一下前面各章的重点，提供进行性能优化探讨和分析的一个出发点。

 首先，MySQL(与所有DBMS一样)具有特定的硬件建议。在学 习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但 对用于生产的服务器来说，应该坚持遵循这些硬件建议。

 一般来说，关键的生产DBMS应该运行在自己的专用服务器上。  MySQL是用一系列的默认设置预先配置的，从这些设置开始通常 是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大 小等。(为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;)

 MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多
个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执 行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST 显示所有活动进程(以及它们的线程ID和执行时间)。你还可以用KILL命令终结某个特定的进程(使用这个命令需要作为管理员登录)。

 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、
子查询等，找出最佳的方法。

 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。  一般来说，存储过程执行得比一条一条地执行其中的各条MySQL
语句快。

 应该总是使用正确的数据类型。

 决不要检索比需求还要多的数据。换言之，不要用SELECT *(除
非你真正需要每个列)。

 有的操作(包括INSERT)支持一个可选的DELAYED关键字，如果
使用它，将把控制立即返回给调用程序，并且一旦有可能就实际
执行该操作。

 在导入数据时，应该关闭自动提交。你可能还想删除索引(包括
FULLTEXT索引)，然后在导入完成后再重建它们。

 必须索引数据库表以改善数据检索的性能。确定索引什么不是一 件微不足道的任务，需要分析使用的SELECT语句以找出重复的 WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花 的时间太长，则可以断定其中使用的列(或几个列)就是需要索引的对象。

 你的SELECT语句中有一系列复杂的OR条件吗?通过使用多条
SELECT语句和连接它们的UNION语句，你能看到极大的性能改
进。

 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。
如果你有一些表，它们收集数据且不经常被搜索，则在有必要之
前不要索引它们。(索引可根据需要添加和删除。)

 LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。

 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面
目全非了。由于表的使用和内容的更改，理想的优化和配置也会
改变。

 最重要的规则就是，每条规则在某些条件下都会被打破。

浏览文档 位于http://dev.mysql.com/doc/的MySQL文档有许 多提示和技巧(甚至有用户提供的评论和反馈)。一定要查看 这些非常有价值的资料。