MySQL InnoDB 引擎的持久性与性能

最新推荐文章于 2024-04-03 12:30:18 发布

weixin_34004750

最新推荐文章于 2024-04-03 12:30:18 发布

阅读量119

点赞数

文章标签： php 数据库操作系统

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,'秒';