查看:1766|回复:0
MySQL优化简明指南发表时间:2010-03-09 16:50:11

大虾

0分享0主题0帖子

学徒一级

(初出茅庐)


在数据库表丢失或损坏的情况下,备份你的数据库是很重要的。如果发生系统崩溃,你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态。有时,正是MySQL管理员造成破坏。管理员已经知道表已破坏,用诸如vi或Emacs等编辑器试图直接编辑它们,这对表绝对不是件好事!备份数据库两个主要方法是用mysqldump程序或直接拷贝数据库文件(如用cp、cpio或tar等)。 每种方法都有其优缺点: mysqldump与MySQL服务器协同操作。直接拷贝方法在服务器外部进行,并且你必须采取措施保证没有客户正在修改你将拷贝的表。如果你想用文件系统备份来备份数据库,也会发生同样的问题:如果数据库表在文件系统备份过程中被修改,进入备份的表文件主语不一致的状态,而对以后的恢复表将失去意义。文件系统备份与直接拷贝文件的区别是对后者你完全控制了备份过程,这样你能采取措施确保服务器让表不受干扰。mysqldump比直接拷贝要慢些。 mysqldump生成能够移植到其它机器的文本文件,甚至那些有不同硬件结构的机器上。直接拷贝文件不能移植到其它机器上,除非你正在拷贝的表使用MyISAM存储格式。ISAM表只能在相似的硬件结构的机器上拷贝。在MySQL 3.23中引入的MyISAM表存储格式解决了该问题,因为该格式是机器 无关的,所以直接拷贝文件可以移植到具有不同硬件结构的机器上。只要满足两个条件:另一台机器必须也运行MySQL 3.23或以后版本,而且文件必须以MyISAM格式表示,而不是ISAM格式。

不管你使用哪种备份方法,如果你需要恢复数据库,有几个原则应该遵守,以确保最好的结果:

定期实施备份,建立一个计划并严格遵守

让服务器执行更新日志。当你在崩溃后需要恢复数据时,更新日志将帮助你。在你用备份文件恢复数据到备份时的状态后,你可以通过运行更新日志中的查询再次运用备份后面的修改,这将数据库中的表恢复到崩溃发生时的状态。 以文件系统备份的术语讲,数据库备份文件代表完全倾倒(full dump),而更新日志代表渐进倾倒(incremental dump)。

使用一种统一的和易理解的备份文件命名机制

象backup1、buckup2等不是特别有意义。当实施你的恢复时,你将浪费时间找出文件里是什么东西。你可能发觉用数据库名和日期构成备份文件名会很有用。例如:

%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02

%mysqldump menagerie >/usr/archives/mysql/menagerie.1999-10-02

你可能想在生成备份后压缩它们。备份一般都很大!你也需要让你的备份文件有过期期限以避免它们填满你的磁盘,就象你让你的日志文件过期那样。用文件系统备份备份你的备份文件。如果遇上了一个彻底崩溃,不仅清除了你的数据目录,也清除了包含你的数据库备份的磁盘驱动器,你将真正遇上了麻烦。也要备份你的更新日志。将你的备份文件放在不同于用于你的数据库的文件系统上。这将降低由于生成备份而填满包含数据目录的文件系统的可能性。

用于创建备份的技术同样对拷贝数据库到另一台机器有用。最常见地,一个数据库被转移到了运行在另一台主机上的服务器,但是你也可以将数据转移到同一台主机上的另一个服务器。

1.使用mysqldump备份和拷贝数据库

当你使用mysqldumo程序产生数据库备份文件时,缺省地,文件内容包含创建正在倾倒的表的CREATE语句和包含表中行数据的INSERT语句。换句话说,mysqldump产生的输出可在以后用作mysql的输入来重建数据库。 你可以将整个数据库倾倒进一个单独的文本文件中,如下:

%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02

输出文件的开头看起来象这样:

# MySQL Dump 6.0#

# Host: localhost Database: samp_db

#---------------------------------------

# Server version 3.23.2-alpha-log

## Table structure for table ’absence’#

CREATE TABLE absence( student_id int(10)

unsigned DEFAULT ’0’ NOT NULL, date date DEFAULT ’0000-00-00’ NOT NULL, PRIMARY KEY (student_id,date));

## Dumping data for table ’absence’#

INSERT INTO absence VALUES (3,’1999-09-03’);

INSERT INTO absence VALUES (5,’1999-09-03’);

INSERT INTO absence VALUES (10,’1999-09-08’);

......

文件剩下的部分有更多的INSERT和CREATE TABLE语句组成。

如果你想压缩备份,使用类似如下的命令:



%mysqldump samp_db │ gzip >/usr/archives/mysql/samp_db.1999-10-02.gz

如果你要一个庞大的数据库,输出文件也将很庞大,可能难于管理。如果你愿意,你可以在mysqldump命令行的数据库名后列出单独的表名来倾到它们的内容,这将倾倒文件分成较小、更易于管理的文件。下例显示如何将samp_db数据库的一些表倾到进分开的文件中:

%mysqldump samp_db student score event absence >grapbook.sql

%mysqldump samp_db member president >hist-league.sql

如果你生成准备用于定期刷新另一个数据库内容的备份文件,你可能想用--add-drop-table选项。这告诉服务器将DROP TABLE IF EXISTS语句写入备份文件,然后,当你取出备份文件并把它装载进第二个数据库时,如果表已经存在,你不会得到一个错误。如果你倒出一个数据库以便能把数据库转移到另一个服务器,你甚至不必创建备份文件。要保证数据库存在于另一台主机,然后用管道倾倒数据库,这样mysql能直接读取mysqldump的输出。例如:你想从主机pit-viper.snake.net拷贝数据库samp_db到boa.snake.net,可以这样很容易做到:



%mysqladmin -h boa.snake.net create samp_db

%mysqldump samp_db │ mysql -h boa.snake.net samp_db

以后,如果你想再次刷新boa.snake.net上的数据库,跳过mysqladmin命令,但要对mysqldump加上--add-drop-table以避免的得到表已存在的错误:

%mysqldump --add-drop-table samp_db │ mysql -h boa.snake.net samp_db

mysqldump其它有用的选项包括:

--flush-logs --lock-tables 组合将对你的数据库检查点有帮助。

--lock-tables 锁定你正在倾倒的所有表

--flush-logs 关闭并重新打开更新日志文件,新的更新日志将只包括从备份点起的修改数据库的查询。这将设置你的更新日志检查点位备份时间。(然而如果你有需要执行个更新的客户,锁定所有表对备份期间的客户访问不是件好事。)如果你使用 --flush-logs 设置检查点到备份时,有可能最好是倾倒整个数据库。如果你倾倒单独的文件,较难将更新日志检查点与备份文件同步。在恢复期间,你通常按数据库为基础提取更新日志内容,对单个表没有提取更新的选择,所以你必须自己提取它们。缺省地,mysqldump 在写入前将一个表的整个内容读进内存。这通常确实不必要,并且实际上如果你有一个大表,几乎是失败的。你可用 --quick 选项告诉 mysqldump 只要它检索出一行就写出每一行。为了进一步优化倾倒过程,使用 --opt而不是 --quick--opt 选项打开其它选项,加速数据的倾倒和把它们读回。用 --opt 实施备份可能是最常用的方法,因为备份速度上的优势。然而,要警告你,--opt 选项确实有代价,--opt 优化的是你的备份过程,不是其他客户对数据库的访问。--opt 选项通过一次锁定所有表阻止任何人更新你正在倾倒的任何表。你可在一般数据库访问上很容易看到其效果。当你的数据库一般非常频繁地使用,只是一天一次地调节备份。一个具有 --opt 的相反效果的选项是--dedayed。该选项使得 mysqldump 写出 INSERT DELAYED 语句而不是 INSERT 语句。如果你将数据文件装入另一个数据库并且你想是这个操作对可能出现在该数据库中的查询的影响最小,--delayed 对此很有帮助。 --compress 选项在你拷贝数据库到另一台机器上时很有帮助,因为它减少网络传输字节的数量。下面有一个例子,注意到--compress 对与远端主机上的服务器通信的程序才给出,而不是对与本地主机连接的程序:

%mysqldump --opt samp_db │ mysql --compress -h boa.snake.net samp_db

mysqldump有很多选项,详见《MySQL参考手册》。

三、表类型

很多MySQL用户可能很惊讶,MySQL确实为用户提供5种不同的表类型,称为DBD、HEAP、ISAM、MERGE和MyIASM。DBD归为事务安全类,而其他为非事务安全类。

3.1、事务安全

DBD

Berkeley DB(DBD)表是支持事务处理的表,由Sleepycat软件公司(http://www.sleepycat.com)开发。它提供MySQL用户期待已久的功能-事务控制。事务控制在任何数据库系统中都是一个极有价值的功能,因为它们确保一组命令能成功地执行。

3.2、非事务安全

HEAP

HEAP表是MySQL中存取数据最快的表。这是因为他们使用存储在动态内存中的一个哈希索引。另一个要点是如果MySQL或服务器崩溃,数据将丢失。

ISAM

ISAM表是早期MySQL版本的缺省表类型,直到MyIASM开发出来。建议不要再使用它。

MERGE

MERGE是一个有趣的新类型,在3.23.25之后出现。一个MERGE表实际上是一个相同MyISAM表的集合,合并成一个表,主要是为了效率原因。这样可以提高速度、搜索效率、修复效率并节省磁盘空间。

MyIASM

这是MySQL的缺省表类型。它基于IASM代码,但有很多有用的扩展。MyIASM比较好的原因:

MyIASM表小于IASM表,所以使用较少资源。

MyIASM表在不同的平台上二进制层可移植。

更大的键码尺寸,更大的键码上限。


3.3、指定表类型

你可在创建表时指定表的类型。下例创建一个HEAP表:

mysql>CREATE TABLE email_addresses TYPE=HEAP (

->email char(55) NOT NULL,

->name char(30) NOT NULL,

->PRIMARY KEY(email) );

BDB表需要一些配置工作,参见http://www.mysql.com/doc/B/D/BDB_overview.html。

3.4、更多的表类型

为了使MySQL管理工作更有趣,即将发布的MySQL 4.0将提供两种新的表类型,称为Innobase和Gemeni。

4、优化工具

MySQL服务器本身提供了几条内置命令用于帮助优化。

4.1、SHOW

你可能有兴趣知道MySQL服务器究竟更了什么,下列命令给出一个总结:

mysql>show status;

它给出了一个相当长的状态变量及其值的列表。有些变量包含了异常终止客户的数量、异常终止连接的数量、连接尝试的次数、最大并发连接数和大量其他有用的信息。这些信息对找出系统问题和低效极具价值。

SHOW还能做更多的事情。它可以显示关于日志文件、特定数据库、表、索引、进程和权限表中有价值的信息。详见MySQL手册。

4.2、EXPLAIN

当你面对SELECT语句时,EXPLAIN解释SELECT命令如何被处理。这不仅对决定是否应该增加一个索引,而且对决定一个复杂的Join如何被MySQL处理都是有帮助的。

4.3、OPTIMIZE

OPTIMIZE语句允许你恢复空间和合并数据文件碎片,对包含变长行的表进行了大量更新和删除后,这样做特别重要。OPTIMIZE目前只工作于MyIASM和BDB表。



您需要登录以后才可以回帖    登录|注册