MySQL优化(InnoDB)

转自:http://www.freelamp.com/1015398272/index_html 
http://www.freelamp.com/1015470737/index_html 
InnoDB 介绍（一） 
InnoDB 是 MySQL 上第一个提供外键约束的引擎，除了提供事务处理外，InnoDB 还支持行锁，提供和 Oracle 一样的一致性的不加锁读取，能增加并发读的用户数量并提高性能，不会增加锁的数量。 
InnoDB 的设计目标是处理大容量数据时最大化性能，它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。 

InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 有它自己的缓冲池，能缓冲数据和索引，InnoDB 还把数据和索引存放在表空间里面，可能包含好几个文件，这和 MyISAM 表完全不同，在 MyISAM 中，表被存放在单独的文件中，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。 



MySQL 的源代码中，从 3.23.34a 开始包含 InnoDB 表，并在 MySQL -Max 的二进制版本中激活。 

如果你使用源码版本下载，自己编译的话，需要采用 --with-innodb 选项： 

./configure --with-innodb 

在 MySQL-Max-3.23 上使用 InnoDB 时，必须在 my.cnf 或者 my.ini 文件中的 [mysqld] 小节指定启动参数： 

innodb_data_file_path=ibdata:30M 

InnoDB 以 GNU GPL 版本 2 的许可发布。 

MySQL-Max-3.23: 这个产品可以用于生产环境使用。 
MySQL-4.0: 这个是开发版本，和 3.23 相比增加了多表删除，查询结果缓冲，SSL 通信，4.0.1 为 Beta 版本。 

在 3.23 中，必须至少指定 innodb_data_file_path 的大小，但是在 MySQL-4.0 中这个值默认为 64MB，文件名为 ibdata1。 

为了得到好的性能，必须指定 InnoDB 参数，例如： 
对于一台 128MB 内存， 10GB 硬盘的 Windows NT 机器的配置如下： 

[mysqld] 

innodb_data_home_dir = c:\ibdata 
# 数据文件必须能容下数据和索引 
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M 
# 设置缓冲池大小为内存的 50 - 80 % 
set-variable = innodb_buffer_pool_size=70M 
set-variable = innodb_additional_mem_pool_size=10M 
innodb_log_group_home_dir = c:\iblogs 
# log_arch_dir 必须和 log_group_home_dir 一样 
innodb_log_arch_dir = c:\iblogs 
innodb_log_archive=0 
set-variable = innodb_log_files_in_group=3 
# 设置日志文件大小为缓冲池的 15%。 
set-variable = innodb_log_file_size=10M 
set-variable = innodb_log_buffer_size=8M 
# 如果丢失最近的几个事务影响不大的话，设置 flush_log_at_trx_commit 为 0。 
innodb_flush_log_at_trx_commit=1 
set-variable = innodb_file_io_threads=4 
set-variable = innodb_lock_wait_timeout=50 

注意 InnoDB 不会自己建立目录，你必须使用操作系统命令建立目录，并注意 MySQL 的权限。 

第一次运行 MySQL 时，建议使用命令行方式。在 Windows 下，用命令行开启： 

C:\MYSQL>mysqld-max --standalone --console 

在 Windows 下设置配置文件的原则是： 
my.cnf 和 my.ini 只能是一个有效。 
my.cnf 放在 C: 根目录下， my.ini 文件放在 WINDIR 目录下，C:\WINDOWS 或者 C:\WINNT。 

在 Linux 下面，初始配置文件按下面的顺序搜索： 

/etc/my.cnf 全局选项 
COMPILATION_DATADIR/my.cnf 服务器范围的选项 
defaults-extra-file 采用 --defaults-extra-file=.... 的文件 
~/.my.cnf 用户指定的选项 

假设你有一台 512MB 内存，三台 20GB 硬盘(路径分别为："/"，"/dr2"，"/dr3“)的服务器，设置的例子为： 

[mysqld] 
innodb_data_home_dir = / 
innodb_data_file_path = ibdata/ibdata1:2000M;dr2/ibdata/ibdata2:2000M 
set-variable = innodb_buffer_pool_size=350M 
set-variable = innodb_additional_mem_pool_size=20M 
innodb_log_group_home_dir = /dr3/iblogs 
innodb_log_arch_dir = /dr3/iblogs 
innodb_log_archive=0 
set-variable = innodb_log_files_in_group=3 
set-variable = innodb_log_file_size=50M 
set-variable = innodb_log_buffer_size=8M 
innodb_flush_log_at_trx_commit=1 
set-variable = innodb_file_io_threads=4 
set-variable = innodb_lock_wait_timeout=50 
#innodb_flush_method=fdatasync 
#innodb_fast_shutdown=1 
#set-variable = innodb_thread_concurrency=5 

为了提升性能，我们把日志文件和数据文件放在不同的磁盘，另外还可以采用裸的磁盘分区来存储数据以提升性能。 

需要提醒的是，在 Linux x86 上不要把内存设置太高，glibc 会把进程堆增长到线程堆栈之上，能把服务器给搞垮，下面的值接近于 2GB 时就很危险： 

innodb_buffer_pool_size + key_buffer + 
max_connections * (sort_buffer + record_buffer) + max_connections * 2 MB 

每个线程将使用 2MB(二进制版本为 256 KB) 的堆栈，在最坏的情况下，还会使用 sort_buffer + record_buffer 的附加内存。 

后续的文章，我们还将介绍 InnoDB 表的建立，MyISAM 到 InnoDB 的转换，外键约束的使用，自动增长列的操作，InnoDB 数据库的备份和恢复，事务的使用，性能调优等内容。 

请继续关注，FreeLAMP.com 为您提供的企业级的开放源码数据库解决方案。 

InnoDB 介绍（二） 
上一节我们简要介绍了 InnoDB 及其基本的配置参数，本文要讲述的是性能调优的一些参数配置以及安全，备份恢复等。MySQL 要进入企业级的运作，对于一个 DBA 而言，这些是必备的知识。因此，本文将详细叙述性能调优方面的知识。 

性能优化的一些主要参数： 

set-variable = max_connections=200 
set-variable = record_buffer=1M 
set-variable = sort_buffer=1M 
# 如果为 MyISAM 表的话，设置 key_buffer 为内存的 5 - 50 % ， 
# 但是对于 InnoDB 来说，必须保证 key_buffer+ InnoDB 缓冲池小于内存的 80%。 
set-variable = key_buffer=... 

另外值得注意的几个参数是： 

innodb_file_io_threads 文件 i/o 的线程数，一般为 4，但是在 Windows 下，可以设置得较大。 

innodb_fast_shutdown InnoDB 缺省在关闭之前清理缓冲，这个过程可能需要几分钟，在极端的情况下可能需要几个小时，如果这个参数设置为 1 的话，InnoDB 就略过这个过程，从 3.23.50 开始这个参数的缺省值为 1 。 

innodb_thread_concurrency InnoDB 努力把操作系统线程保留下来，缺省的值是 8，如果你的性能比较低， innodb_monitor 显示很多线程等待信号，应该把这个值设置得小一些，如果你的系统有很多处理器和磁盘则可以调高这个值。建议的值为处理器数目加上磁盘数。 

配置好 my.cnf 或者 my.ini 的参数之后，就可以启动 MySQL 建立 InnoDB 了，第一次建立之前，建议你手工启动，这样你就可以看到 InnoDB 的建立过程： 
heikki@donna:~/mysql-3.23.48/sql> mysqld 
020204 23:17:12 InnoDB: The first specified data file /dr2/tmp/heikki/data/ibdata1 
did not exist: 
InnoDB: a new database to be created! 
InnoDB: Setting file /dr2/tmp/heikki/data/ibdata1 size to 20 MB 
InnoDB: Database physically writes the file full: wait... 
020204 23:17:16 InnoDB: Data file /dr2/tmp/heikki/data/ibdata2 did not exist: new 
to be created 
InnoDB: Setting file /dr2/tmp/heikki/data/ibdata2 size to 200 MB 
InnoDB: Database physically writes the file full: wait... 
020204 23:17:41 InnoDB: Data file /dr2/tmp/heikki/data/ibdata3 did not exist: new 
to be created 
InnoDB: Setting file /dr2/tmp/heikki/data/ibdata3 size to 1000 MB 
InnoDB: Database physically writes the file full: wait... 
020204 23:21:37 InnoDB: Log file ./ib_logfile0 did not exist: new to be created 
InnoDB: Setting log file ./ib_logfile0 size to 10 MB 
InnoDB: Database physically writes the file full: wait... 
020204 23:21:39 InnoDB: Log file ./ib_logfile1 did not exist: new to be created 
InnoDB: Setting log file ./ib_logfile1 size to 10 MB 
InnoDB: Database physically writes the file full: wait... 
020204 23:21:41 InnoDB: Log file ./ib_logfile2 did not exist: new to be created 
InnoDB: Setting log file ./ib_logfile2 size to 10 MB 
InnoDB: Database physically writes the file full: wait... 
InnoDB: Doublewrite buffer not found: creating new 
InnoDB: Doublewrite buffer created 
InnoDB: Creating foreign key constraint system tables 
InnoDB: Foreign key constraint system tables created 
020204 23:21:45 InnoDB: Started 
mysqld: ready for connections 

如果采用 mysqladmin shutdown 命令关闭时，显示： 

020204 23:34:45 mysqld: Normal shutdown 

020204 23:34:45 InnoDB: Starting shutdown... 
020204 23:34:47 InnoDB: Shutdown completed 
020204 23:34:47 mysqld: Shutdown Complete 

进入日志文件所在的目录，我们可以看到 ib_arch_log_0000000000 这样的文件。 

下面我们来建立一个 InnoDB 表，假设我们用 mysql test 进入： 

CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB; 

查看状态： 
SHOW TABLE STATUS FROM test LIKE 'CUSTOMER' 

需要注意的是，不要手工删除 InnoDB 数据库目录下的 .frm 文件，而是采用 create table 和 drop table 的方法。 

把 MyISAM 转化为 InnoDB 

*注意* MySQL 的系统表， user 或者 host 等不能转化为 InnoDB 格式，他们必须采用 MyISAM 格式！！ 

如果你想以后建立的所有表都为 InnoDB 格式，在配置文件中加入： 
default-table-type=innodb 

InnoDB 没有一个特殊的索引建立优化机制，因此不能采取 export/import ，然后建立索引的办法来转化，最快的办法是把表类型改变为 InnoDB ，然后直接插入数据： 

ALTER TABLE ... TYPE=INNODB 或者建立一个空的具有同样结构的 InnoDB 表，然后插入数据： 
INSERT INTO ... SELECT * FROM .... 

为了更好的控制数据插入进程，最好把很大的表分批插入： 

INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey > something 
AND yourkey <= somethingelse; 

等数据全部插入完毕后，可以重新命名表名。 

在插入过程中，要把 InnoDB 缓冲池设置得大点，以减少磁盘 I/O。同时还应该把日志文件和日志缓冲加大。 

需要注意的是，不要让表空间用尽，如果 ALTER TABLE 用完了表空间，将会回滚，如果磁盘不够的话，这个过程会持续几个小时。 


外键约束 

从 MySQL 3.23.43b 开始， InnoDB 就支持外键约束，作为数据引用完整性的重要特色，InnoDB 在外键支持上开创了 MySQL 各种表类型的先河。 

建立外键的方法： 

例子： 

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; 
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), 
FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INNODB; 

两表都必须为 InnoDB 类型，外键和被引用的键，必须是索引中的一个列，InnoDB 不会自动为外键建立索引，必须明确指明。 

如果 CREATE TABLE 语句发生 1005 号错误，错误信息字符串引用 150 号错误，那么就是外键约束起了作用。 

只有在父行中包含 NULL 的列，才允许在子行中包含 NULL。 

目前任何 ALTER TABLE 操作都会删除外键约束，因此，建议采用 DROP TABLE 然后 CREATE TABLE 来修改 schema。因为 ALTER TABLE 在内部使用 RENAME TABLE 操作，这样就能混淆外键的引用，同样的 CREATE INDEX 也是作为 ALTER TABLE 来处理的，也不能用于外键约束的表。 

InnoDB 允许你 drop 任何表，即使这样会打破外键，这样操作的结果就是约束也被 drop 了。 

查看表 T 中的外键约束状态： 

SHOW TABLE STATUS FROM yourdatabasename LIKE 'T' 

InnoDB 还不支持 CASCADE ON DELETE 或者其特殊的约束选项。（我们知道 Oracle 里面有这个东东了） 

处理自动增量的列的时候需要注意的问题是：自动增量的列不参加锁机制和事务处理，因此回滚时，会造成序列中间的空档。 

增加 InnoDB 数据和日志文件 
不能增大 InnoDB 数据文件的长度，增加表空间的办法是新添加数据文件： 
innodb_data_file_path，然后重新启动 MySQL 。 

同样的，目前你还不能删除数据文件，为了减小数据文件的长度，需要导出数据，建立新数据库，然后再导入。 

如果需要改变日志文件的大小，可以先关闭 MySQL ，然后把旧的日志文件移动到一个备份目录，修改 my.cnf ，重新启动 MySQL ，确信 InnoDB 正常启动后，可以把旧的日志文件删除了。 


InnoDB 数据库的备份： 
关闭 MySQL ，并确保正常关闭。 
复制所有的数据文件。 
复制所有的 InnoDB 日志文件。 
复制 my.cnf 配置文件。 
复制所有 InnoDB 表的 .frm 文件。 

目前还没有热备份或者增量备份的工具。对于要求高可靠的服务器而言，可以采用复制的形式。 
数据恢复的过程，就是把前面备份的这些文件重新考回原来的位置。这种情况只有在数据文件损坏的情况下，才有必要。一般的数据库瘫痪，重新启动 MySQL ，InnoDB 会恢复事务。 

如果确实有表空间不能回复的话，可以采用 SELECT INTO OUTFILE 来恢复数据。这个时候需要在 my.cnf 中设置： 
set-variable = innodb_force_recovery = 4 

InnoDB 的数据文件在所有平台上二进制兼容，因此只要只要两台机器的浮点数格式一致，你就可以在这两台机器之间移动文件。 

锁机制的叙述和解释比较繁杂，这里跳过原文第八章( http://www.innodb.com/ibman.html )关于锁机制的描述，我们直接讲述性能调优的几个提示： 

1. 如果 CPU 资源利用率小于 70% 的话，你的负载可能是由于磁盘引起的，可能有太多的事务提交，或者缓冲池太小。建议增加缓冲池。 

2. 把多个修改包在一个事务里面， InnoDB 每次提交事务需要刷新日志到磁盘，而磁盘的速度和 CPU 相比一般都很慢，因此，建议不要频繁提交事务。 

3. 如果你能承受最近几个事务的丢失，建议设置 innodb_flush_log_at_trx_commit 为 0 ，InnoDB 每秒刷新一次日志。 

4. 加大日志文件，组合起来的所有日志文件大小应该和缓冲池一样大，很小的日志文件将会导致很多不必要的磁盘写操作，大日志文件的缺陷是恢复时间会加长。 

5. 同样需要加大日志缓冲，例如 8 MB。 

6. 采用 VARCHAR 类，而不是 CHAR 类型，小的表更适合缓冲池，从而较少了磁盘 I/O。 
7. 在 Linux 和一些 Unix 系统上，采用 Unix fsync 命令刷新文件，但是有些情况下，可能会很慢，可以修改 innodb_flush_method 为 O_DSYNC 来提高写性能。 

8. 导入数据时，要确保 autocommit=1 没有打开，在导入的 SQL 文件最前面加入： 

set autocommit=0; 
commit; 
如果采用 mysqldump --opt 选项，你得到的 dump 文件能快速导入 InnoDB 表。 

9. 留意大批量插入时的回滚操作。 
10. 留意那些消耗磁盘资源的操作，采用 DROP TABLE 或者 TRUNCATE 来清空表，而不是DELETE FROM 。. 

11.如果要插入多行时， 使用多行 INSERT ，来减轻客户端和服务器端的通信量。 

INSERT INTO yourtable VALUES (1, 2), (5, 5); 

上面的多行（原文为 multi-line，我认为应该为 multi-row）。这条提示不仅仅适用于 InnoDB ，对于其他表类型也适用。 

从 3.23.42 开始，InnoDB 包括了 InnoDB 监视器，能输出InnoDB 内部状态的信息，这些数据在性能调优时十分有用，当打开时，监视器能每15秒在服务器端输出数据到标准输出，如果不是从命令行启动的话，这些输出会写入到 .err 文件中，在 Windows 平台上，必须在 DOS 命令行下以 --standalone --console 参数启动。 

主要信息包括： 

每个激活的事务锁住的表以及记录 
事务的锁等待 
线程的信号等待 
延迟的文件 i/o 请求 
缓冲池统计以及删除和插入缓冲合并的活动 

启动的命令为： 

CREATE TABLE innodb_monitor(a int) type = innodb; 

停止的语法是： 
DROP TABLE innodb_monitor; 

如果数据库关闭时，监视器还在运行，下次启动监视器前必须先关闭。 

用同样的语法你可以启动 innodb_lock_monitor 以及 innodb_tablespace_monitor 

除了锁机制以外，本文没有讲述的内容还有表和索引的结构，记录的物理结构，文件空间的管理，错误处理，InnoDB 表的限制等，请继续关注本站的后续报道。 

在后面的报道中，您将会了解到 InnoDB 的一些物理设计仿照了 Oracle ，因此，能够获得和 Oracle 可以类比的性能并不足以为奇。