MySQL InnoDB 引擎的持久性与性能

MySQL 事务的 ACID 特性中，D 代表持久性（Durability）：在使用 InnoDB 引擎时，当返回客户端一个成功完成事务的确认时， InnoDB 就会保证数据的一致性，即使该数据在此时还没有写入磁盘，因为 InnoDB 引擎的重做日志已经连续地记录了已完成的事务。InnoDB 不是在事务执行的时候直接将数据写入磁盘，即不会立即将更新的数据写入磁盘，而是由 InnoDB 存储引擎的后台 worker 线程负责执行。可以根据在配置文件中的配置来设置日志写入磁盘的频率，默认情况下是每次事务会刷写到磁盘一次。

不同的持久性设置会对数据库的性能产生很大的影响，默认情况下配置文件 my.ini 或 my.cnf 中 innodb_flush_log_at_trx_commit 的值为 1：

mysql> select @@global.innodb_flush_log_at_trx_commit;
+-----------------------------------------+
| @@global.innodb_flush_log_at_trx_commit |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

不同的设置会改变 InnoDB 的持久性和性能，该选项有 3 个值：

1（默认）	对于每个事务日志，InnoDB 都会把日志写入（write）并刷写（flush）到磁盘。速度最慢，但是最持久
2	InnoDB 将所有发生的事务日志写入（write）磁盘，并且每秒对这些数据刷写（flush）一次
0	InnoDB将最近一秒中所发生的所有事务日志写入（write）磁盘，然后将这些日志刷写（flush）到磁盘。

（写日志在 write 时发生，写入数据文件到磁盘在操作系统 flush buffer 时发生）

在一些场景下可以通过破坏真正的持久性来显著提高性能。

测试一下 MyISAM 引擎、 InnoDB 引擎（不同持久性下）的插入速度。

测试环境：

CentOS6.6 64-bit/2G

MySQL 5.6.29

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.29-log |
+------------+

PHP 7.0.7

[root@localhost test]# php -v
PHP 7.0.7 (cli) (built: Feb 11 2017 16:47:30) ( NTS )
Copyright (c) 1997-2016 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2016 Zend Technologies

表结构：

MyISAM

    CREATE TABLE `m` (
        `id` int(10) NOT NULL AUTO_INCREMENT,
        `number` int(10) NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1

InnoDB

    CREATE TABLE `i` (
        `id` int(10) NOT NULL AUTO_INCREMENT,
        `number` int(10) NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

PHP 文件：

<?php 

set_time_limit(0);

$host       = 'localhost';
$user       = 'root';
$pwd        = 'root';
$database   = 'test';

$mysqli = new mysqli($host, $user, $pwd, $database);
if($mysqli->connect_error)
{
    die('Connect Error: '.$mysqli->connect_error);
}

$table_name = 'example_myisam';
// $table_name = 'example_innodb';
$times = 10000;

$sql_truncate   = "TRUNCATE TABLE {$table_name}";
$mysqli->query($sql_truncate);

$time = -microtime(true);
for($i = 0; $i < $times; $i++)
{
    $sql = "INSERT INTO {$table_name} (`number`) VALUES ({$i})";
    if(false === $mysqli->query($sql))
    {
        die('ERROR: '.$mysqli->error);
    }
}
$time += microtime(true);
echo '用时',$time,'秒';

分别测试 1w 条数据和 10w 条数据不同情况下所耗费的时间：

MyISAM

InnoDB.innodb_flush_log_at_trx_commit =1

InnoDB.innodb_flush_log_at_trx_commit =2

InnoDB.innodb_flush_log_at_trx_commit =0

第1次

第2次

第3次

平均

第1次

第2次

第3次

平均

第1次

第2次

第3次

平均

第1次

第2次

第3次

平均

0.697s

0.722s

0.822s

0.747s

18.204s

19.012s

17.173s

18.130s

0.842s

0.871s

1.058s

0.924s

0.796s

0.795s

0.966s

0.852s

10w

6.579s

6.421s

6.703s

6.568s

201.111s

198.429s

140.926s

180.155s

9.208s

9.566s

9.068s

9.281s

8.022s

7.838s

7.918s

7.926s