浅析MySQL InnoDB数据库引擎-技术开发专区

浅析MySQL InnoDB数据库引擎

作者：IT168 周卫丰编辑：赵建凯 2007-07-25 09:20 来源：IT168�

【IT168 开源数据库】InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation)，因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与其它的不一样，举例来说，在 MyISAM 中，表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小，不过目前很多操作系统都支持大文件系统，所以这个限制我们可以不必考虑了。

一、InnoDB表空间管理

为了使用 InnoDB 表引擎，必须在‘my.cnf’或‘my.ini’文件中详细指定 InnoDB 的启动配置。

innodb_data_home_dir:

innodb_data_home_dir="E:/MySQL Datafiles/"

该参数我们也可以不指定,而在另外一个变量innodb_data_file_path中给出绝对路径。

innodb_data_file_path：

如果该参数不指定,MySQL-4.0 将默认地在 innodb_data_home_dir 目录下建立一个 10 MB 大小自扩充(auto-extending)的文件‘ibdata1’(在MySQL-4.0.0 与 4.0.1 中数据文件的大小为 64 MB 并且是非自扩充的(not auto-extending))。

文件声明格式:

file_name:file_size[:autoextend[:max:max_file_size]]

innodb_data_file_path=/path1/ibdata1:30M;/path2/ibdtat2:30M:autoextend

如果我们想为InnoDb添加表空加,我们需要把嘴喙一个表空间的autoextend属性关闭,再在配置文件my.ini中的innodb_data_file_path参数后边添加数据文件。该操作需要重启MYSQL数据库才可生效。

二、InnoDb 配置

innodb 管理的两个磁盘文件是表空间文件和日志文件.

表空间定义:

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

文件声明格式:

 file_name:file_size[:autoextend[:max:max_file_size]]

innodb_data_home_dir 声明文件存放目录.

mysql 读取配置文件的顺序:

`/etc/my.cnf' Global options.
`DATADIR/my.cnf' Server-specific options.
`defaults-extra-file' The file specified with the --defaults-extra-file option.
`~/.my.cnf' User-specific options.

在 Linux x86 上不要把内存设置太高，内存使用下面的加起来不要超过2G:

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

InnoDb 参数说明:

innodb_additional_mem_pool_size

存储数据字典信息和内部结构信息, 如果你的表越多,这个需要的内存就越多,如果你预留的空间不够,就开始象系统申请内存.errlog 会有错误. 缺省设置为1M.

innodb_autoextend_increment

当表空间满时字段扩展大小.

innodb_buffer_pool_size

数据和索引用的缓存大小.一般时系统物理内存的50~80% .

更多的InnoDB参数我们在第七章MYSQL数据库优化中详细讲述。

三、InnoDB表事务、隔离级别

1 事务

MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理，其他的类型是不支持的。InnoDB表事务控制我们在第三章介绍InnoDB引擎的时候已经给出过一个简单例子。InnoDB默认的是自动提交，我们在前边的例子中在事务开始的时候执行了BEGIN和SET AUTOCOMMIT=0操作来保证事务的可用性。MYSQL事务控制有两种方式：

（1）用begin,rollback,commit来实现（显式）

       begin *开始一个事务
       rollback *事务回滚
       commit  *事务确认

（2）直接用set来改变mysql的自动提交模式（隐式）

MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！我们
可以通过：

     set autocommit=0  *禁止自动提交
     set autocommit=1 *开启自动提交

一个事务应用的简单例子：

mysql> CREATE TABLE t (id INT) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.45 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from t;
Empty set (0.00 sec)

2 隔离级别

MYSQL提供四种事务隔离级别：

•序列化（SERIALIZABLE）
•可重复读（REPEATABLE READ）
•提交读（READ COMMITTED）
•未提交读（READ UNCOMMITTED）

未提交读——这通常称为 'dirty read'：non-locking SELECT 的执行使我们不会看到一个记录的可能更早的版本；因而在这个隔离级别下是非 'consistent' reads；这级隔离级别的运作如同 READ COMMITTED。处于这个隔离级的事务可以读到其他事务还没有提交的数据。如果这个事务使用其他事务未提交的变化作为计算的基础，然后那些未提交的变化被他们的父事务撤销，则会导致误差。

提交读——在一个事务中已经COMMIT的数据可以在其他事务中看到。如果这个事务频繁提交的话，其他的大的查询事务中可能会得到多个不同的结果。

可重复读——这是 InnoDB 默认的事务隔离级。在一个事务中所有读都是连续的。

序列化——提供最大程度的隔离，如果每个事务都以这种隔离级运行就会影响Mysql的性能，因为需要大量的资源来使大量事务在任一时刻不被看到。如果一个事务在执行一个SELECT操作，另外的事务不允许执行UPDATE操作，这个隔离级别下的SELECT操作被阴式地转换为 SELECT ... LOCK IN SHARE MODE。

设置事务的隔离级别:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

需要super优先权执行这个操作。

取得当前事务的隔离级别：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.44 sec)

默认情况下，隔离级别变量的值是基于每个会话设置的，但是可以通过添加global关键字对所有的会话进行全局设置。

REPEATABLE READ级别事务一致读的例子（显式）：

SESSION 1：

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

SESSION 2：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
Empty set (0.00 sec)

SESSION 1：

mysql> COMMIT;
Query OK, 0 rows affected (0.08 sec)

SESSION 2：

mysql> select * from t;
Empty set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

REPEATABLE READ级别事务一致读的例子（隐式）：

SESSION 1：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

SESSION 2：

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

SESSION 1：

mysql> commit;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t values(3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

SESSION 2：

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

SESSION 1：

mysql> commit;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.05 sec)

SESSION 2：

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

3 锁

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定，对BDB表进行页级锁定，对InnoDB表进行行级锁定。

MySQL中用于 WRITE（写）的表锁的实现机制如下：如果表没有加锁，那么就加一个写锁。否则的话，将请求放到写锁队列中。

MySQL中用于 READ（读）的表锁的实现机制如下：如果表没有加写锁，那么就加一个读锁。否则的话，将请求放到读锁队列中。

当锁释放后，写锁队列中的线程可以用这个锁资源，然后才轮到读锁队列中的线程。这就是说，如果表里有很多更新操作的话，那么 SELECT 必须等到所有的更新都完成了之后才能开始。从 MySQL 3.23.33 开始，可以通过状态变量 Table_locks_waited 和 Table_locks_immediate 来分析系统中的锁表争夺情况：

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 3008994 |
| Table_locks_waited    | 0       |
+-----------------------+---------+
2 rows in set (1.36 sec)

我还是习惯用共享锁和独占锁的概念：

共享锁S：事务获得共享锁允许读取一条记录。

独占锁X: 事务获得独占锁允许更新或者删除一条记录。

如果事务T1在表t上获得了一个共享锁：事务T2也可以在表t上获得一个共享锁，两个事务可以同时在表t上获得共享锁；事务T2如果想在表t上获得一个独占锁，将不能立即获得。

如果事务T1在表t上获得一个独占锁：其实事务不能在表t上获得任何类型的锁。

InnoDB支持多粒度锁，行锁和表锁，和ORACLE类似，MYSQL也有意向锁的概念：
意向共享锁和意向排它锁。我们介绍一下意向锁的概念：

表是由行组成的，当我们向某个表加锁时，一方面需要检查该锁的申请是否与原有的表级锁相容；另一方面，还要检查该锁是否与表中的每一行上的锁相容。比如一个事务要在一个表上加S锁，如果表中的一行已被另外的事务加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。

意向锁的含义是如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁。

如：对表中的任一行加锁时，必须先对它所在的表加意向锁，然后再对该行加锁。这样一来，事务对表加锁时，就不再需要检查表中每行记录的锁标志位了，系统效率得以大大提高。

由此我们可以看出MYSQL中的两种意向锁：意向共享锁和意向排它锁。

几种类型锁定的兼容性：

LOCK TABLE示例：

SESSION 1：

mysql> lock tables t write;
Query OK, 0 rows affected (0.02 sec)

SESSION 2：

mysql> select * from t;

SESSION 1：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

SESSION 2：

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|   33 |
+------+
3 rows in set (59.28 sec)

InnoDB 中各 SQL 语句的锁定

SELECT ... FROM ... :

这是一个 consistent read，不以锁定方式读取数据库的快照，除非事务的隔离级被设置为 SERIALIZABLE，在这种情况下将在它所读取的记录索引上设置共享的 next-key locks。

SELECT ... FROM ... LOCK IN SHARE MODE :

在所读取的所有记录索引上设置同享的锁定。

SELECT ... FROM ... FOR UPDATE :

在所读取的所胡记录索引上设置独占地(exclusive)锁定。

INSERT INTO ... VALUES (...) :

在插入的记录行上设置一个独占地锁定；注意这个锁定并不是一个 next-key lock ，并不会阻止其它用户在所插入行之前的间隙(gap)中插入新记录。如果产生一个重复键值错误，在重复索引记录上设置一个共享的锁定。如果在一个表中定义了一个 AUTO_INCREMENT 列，InnoDB 在初始化自增计数器时将在与自增列最后一个记录相对应的索引上设置一个独占的锁定。在访问自增计数器时， InnoDB 将设置一个特殊的表锁定模式 AUTO-INC ，这个锁定只持续到该 SQL 语句的结束而不是整个事务的结束。

INSERT INTO T SELECT ... FROM S WHERE ...

在已插入到表 T 中的每个记录上设置一个独占的(无 next-key)锁定。以一个 consistent read 搜索表 S ，但是如果 MySQL 打开了日志开关将在表 S 上设置一个共享的锁定。在从备份中进行前滚(roll-forward)修复时，每个 SQL 语句必须严格按照原先所执行的顺序运行，所以 InnoDB 不得不设置锁定。

CREATE TABLE ... SELECT ...

与上项相似，以 consistent read 或锁定方式完成 SELECT 。

REPLACE

如果没有一个 unique key 冲突，它的执行与 insert 一致。否则将在它所要更新的记录上设置一个独占的锁定。

UPDATE ... SET ... WHERE ... :

在搜索时所遭遇到的记录上设置一个独占的锁定。

DELETE FROM ... WHERE ...

在搜索时所遭遇到的每一个记录上设置一个独占的锁定。如果一个表上有 FOREIGN KEY 约束，所有需要检查约束条件的 insert, update, 或 delete 将在它所要检查约束的记录上设置记录共享级的锁定。同样在约束失败时，InnoDB 也设置这个锁定。

LOCK TABLES ...

设置表锁定。在 MySQL 的代码层(layer of code)设置这些锁定。InnoDB 的自动死锁检测无法检测出有关下列情形的表锁定：查看下面的一个章节。同时查看第 14 章节 'InnoDB 限制与不足' 有关下列内容：自从 MySQL 提供行锁以来，将有可能发生当其他用户设置了行级锁定时你又对该表设置了锁定。But that does not put transaction integerity into danger. 在 3.23.50 版本以前， SHOW TABLE STATUS 应用于一个自增表时将在自增列的最大记录索引上设置一个独占的行级锁定。这就意味着 SHOW TABLE STATUS 可能会引起一个事务的死锁，这可能是我们所意想不到的。从 3.23.50 开始，在读取自增列值时将不再设置任何锁定，除非在某些情况下，比如在数据库启动后没有任何记录。

连续读不能满足的需求:

我们在讲述隔离级别的时候讲述的时候给过事务一致读的例子，但是有些情况下我们利用MYSQL的一致读却不能满足我们的需求，比如：我们有个系统需要对一个表的ID进行编号max(id)+1：

SESSION 1：

mysql> select max(id) from t;
+---------+
| max(id) |
+---------+
|       1 |
+---------+
1 row in set (0.09 sec)

mysql> insert into t values(2);
Query OK, 1 row affected (0.00 sec)

SESSION 2：

mysql> select max(id) from t;
+---------+
| max(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

SESSION 1：

mysql> insert into t values(2);
Query OK, 1 row affected (0.20 sec)

mysql> commit;
Query OK, 0 rows affected (0.11 sec)

mysql> commit;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    2 |
+------+
3 rows in set (0.00 sec)

这样2个SESSION同时进行操作可能会得到相同的编号。

我们下边的例子中读取数据的时候对数据加Read locks for Updating锁.

SESSION 1：

mysql> delete from t where id=2;
Query OK, 2 rows affected (0.09 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select max(id) from t for update;
+---------+
| max(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> insert into t values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

SESSION 2：

mysql> select max(id) from t for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

SESSION 1：

mysql> commit;
Query OK, 0 rows affected (0.09 sec)

SESSION 2：

mysql> select max(id) from t for update;
+---------+
| max(id) |
+---------+
|       2 |
+---------+
1 row in set (0.02 sec)

mysql> insert into t values(3);
Query OK, 1 row affected (0.09 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

四、InnoDB外键约束

InnoDB引擎的表支持外键约束，和其它数据库一样，子表INSERT的数据必须要在主表中存在。其它引擎是否支持外键约束没有做测试。

关注我们