MySQL 的 InnoDB 表类型

Tags: database mysql

之前我们介绍了 HEAP 表类型,一种将数据保存在内存中的表类型,这篇文章中我们将介绍 InnoDB 表类型。标准的 MyISAM 表类型是网站应用的理想类型,因为相对于写来说读会更多,并且不需要事务支持。当然如果不适用这些条件时候,InnoDB 表可能就是最好的选择了。这篇文章面向哪些非常数据 MySQL ,但仅使用 MyISAM 表类型的用户。

InnoDB 的特性

  • ACID 兼容事务

  • 完全的完整性约束

  • 行级锁

  • 表保存在表空间中(MyISAM 是每个表一个文件)

什么是 ACID 兼容事务?

数据库请求是线性的(select, update 或者 insert)一个接着一个的。当一个用户执行一些请求时,可能有其他用户也在同时执行数据库请求,因此最重要的是执行的结果需要保持一致。'事务 (transaction)' 是一系列相关的 SQL 语句组合成的逻辑整体,比如,一个 sales 事务可能包括更新 customer、sales 以及 product 表,当操作完成后事务会进行提交(commit)。为了保证数据的完整性,事务需要满足四个条件(使用 ACID 缩写):Atomicity(原子性)、Consistency(一致性)、Isolation(隔离性)、Durability(持久性)。很简单,原 子性的意思是事务只有整体完成或者失败,事务中单独的 SQL 不可能被单独完成;一致性代表数据的状态和规则,却表这些状态是被维护的。比如,每张发票应该有相应的客户,在事务执行期间,这规则可以被打破(比如发票 数据可以在客户数据前被插入),不过一旦事务完成,需要恢复一致性;隔离性是指在事务过程中修改的数据不能被其他事务访问直到这个事务完成,两个减少银行 存款的事务不能同时处理一个账户的存款(如果你有两个事务从 $1000 的账户减少 $100,当第一个完成后,第二个事务应该是从 $900 账户上减少 $100)每个事务的执行应该是隔离的;持久性是指一旦事务提交,数据需保持一致,因此,如果在事务执行过程中数据库挂了,在恢复后数据应该保持事务最后 提交时的状态。

创建 InnoDB 表

作为更复杂的表类型,InnoDB 表相对于默认的 MyISAM 表需要更多的管理。InnoDB 表创建在表空间(tablespace)中,这与 MyIASM 不同它仅仅是文件系统上的一个文件。假设你运行的是 MySQL 4(4.0.15 是本文编写时的稳定版),MySQL 会在数据目录中创建 ibdata1 文件(数据目录通常是 Windows 下的 C:\MYSQL\data 或 Unix 系统下的  /usr/local/mysql/databasejournal/usr/local/var 目录)。文件初始 10M 且自动增长,当数据变大时会自动增长 8M 数据空间。之前版本的 MySQL 4 设置表为 64MB 并且不会自动增长,意味着当空间满后,你无法增加新数据。

由于这些都是自动的,你所要做的就是使用 CREATE 语句创建 InnoDB 表,下面是一个示例:

CREATE TABLE innodb_table1(field1 INT, field2 CHAR(10), INDEX (field1)) 
TYPE=INNODB;

语法和平常的建表语句差不多,不过在结尾增加了 tpye 定义。

InnoDB 配置选项

这里有些重要的配置需要关注,所有的配置都在 MySQL 配置文件(通常是 my.cnf 或者 my.ini)中配置。最重要的是指定表可用空间的 innodb_data_file_path (包括数据和索引)比如:

innodb_data_file_path = ibdata1:10M:autoextend

上面的是默认设置,指定默认文件为 ibdata1 并且初始大小为 10M 并且自动增长。当然,你可以设置数据文件指向文件系统的其他位置,比如:

innodb_data_file_path = 
/disk1/ibdata1:500M;/disk2/ibdata2:300MB;/disk3/ibdata3:100MB:autoextend

你通常需要确保文件是自动增长的,这样当你逻辑空间增长时可以有足够的空间保存数据,不过物理空间用完了就没办法了。如果你想使用绝对路径配置数据文件路径,你需要将 innodb_data_home_dir 设置为空字符串。配置修改完成后,需要重启 MySQL 服务器。

下面是 InooDB 的一些配置变量以及简要描述:

  • innodb_data_home_dir:InnoDB 数据文件路径。如果留空会使用 datadir,如果设置为空字符串,可以在 innodb_data_file_path 配置绝对路径

  • innodb_mirrored_log_groups:需要设置为 1 (与数据库日志组相同的数字)

  • innodb_log_group_home_dir:InnoDB 日志文件保存路径(若未设置会使用 datadir

  • innodb_log_files_in_group:日志组中的日志文件个数(日志是转储的)。默认为 2 基本已经足够了。

  • innodb_log_file_size:每个日志文件的大小(MB)。默认为 5MB。如果设置的太大,在失败恢复时会非常慢。

  • innodb_log_buffer_size:日志缓存。设置的越大,在事务执行过程中会较少的执行磁盘写操作,推荐 8MB

  • innodb_flush_log_at_trx_commit:0,1 或 2。基于安全原因大多数情况需要设置为 1,它会在事务提交后将日志写入磁盘并且 flush 磁盘。0 会每秒执行一次这种操作,2 会立即写入磁盘但每秒 flush 磁盘。

  • innodb_log_arch_dir:如果启用日志归档,这个目录用来保存归档的日志文件。需要与 innodb_log_group_home_dir 一致。

  • innodb_log_archive:设置为 0 ,MySQL 会使用它自己的日志文件恢复

  • innodb_buffer_pool_size:内存缓冲大小(字节)。越大越好(专门的数据库服务器大于80%)

  • innodb_buffer_pool_awe_mem_mb:从 MySQL 4.1 开始使用,仅针对 Windows 操作系统。这个变量设置在 Address Windowing Extension(AWE) 中的缓冲大小(最大 64000)

  • innodb_additional_mem_pool_size;用来保存内部数据结构的内存池大小。推荐 2MB,如果在 MySQL 日志中发现类似于 'allocating memory for the operating system' 的信息时,你需要增加它的大小。

  • innodb_file_io_threads:I/O 线程个数,在 Unix 上推荐 4 个,Windows 需要更多

  • innodb_lock_wait_timeout:在回滚前事务等待的时间(秒)(进在外部死锁是使用,比如 LOCK TABLE 语句)

  • innodb_flush_method:flushing 方法(默认是 fdatasync 它通常来说比较快,不过在一些系统上 O_DSYNC 会快一些)

  • innodb_force_recovery:只有当你希望从给一个孙华的数据库上 dump 数据时使用。如果需要使用这个选项请参考手册。

如果出现问题

当你启动数据库时,MySQL 会创建表空间、数据文件和日志文件。如果出现错误,通常会有连个原因:权限和配置文件的语法错误。确保 MySQL 服务进程对你计划用来保存数据文件的目录拥有权限且目录需要已存在。在重新尝试前,删除启动失败时写入的所有文件。

在后续的文章中,我们会讨论 InnoDB 选项的更多细节,不过现在你已经知道了如何创建他们,最好的获取知识的方式是尝试使用它们(当然是要在一个测试环境),祝你好运!

本文链接:http://www.4byte.cn/learning/37786/mysql-de-innodb-biao-lei-xing.html