InnoDB的性能调优(一)
时间：2008-05-20 来源：worldwar
性能调优的几个提示：
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
性能优化的一些主要参数：
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 的数据文件在所有平台上二进制兼容，因此只要只要两台机器的浮点数格式一致，你就可以在这两台机器之间移动文件。
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
性能优化的一些主要参数：
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 的数据文件在所有平台上二进制兼容，因此只要只要两台机器的浮点数格式一致，你就可以在这两台机器之间移动文件。
相关阅读 更多 +
