MySql 数据库引擎 MyISAM与InnoDB 怎么选择

Fork Me On Github
zodream 编程技术 2019年12月

MySql 数据库引擎 MyISAM与InnoDB 怎么选择

介绍

InnoDB

Mysql 5.5 版本开始, InnoDB是默认的表存储引擎, 其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读、同时被设计用来最有效的利用以及使用内存和CPU

MyISAM

基于传统的ISAM类型, ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法

比较

MyISAM InnoDB
支持事务、回滚 不支持 支持,默认封装成事务提交,多条最好使用一个事务
支持外键 不支持 支持
表级锁 表、行(默认)级锁,行锁是实现在索引上,可能会导致“死锁”
全文索引 支持 5.6 以后支持(使用sphinx插件更好)
count(*) 事先保存表的总行数 遍历(加了wehre则一样)
索引、主键 允许没有索引和主键,索引都是保存行的地址 没有则生成一个不可见的6字节的主键
安全性 更安全
高并发 容易表损坏 效率更好
巨大数据量 利用CPU效率更高
查询、更新、插入的效率 更高
加索引查询 更快
加索引更新 慢1/2 慢1/30
内存、空间 占用更大
存储文件 frm是表定义文件,myd是数据文件,myi是索引文件 frm是表定义文件,ibd是数据文件

测试:

测试环境:win10 MySQL8.0 php7.4

CPU 占用差不多、内存占用不高、机械硬盘写入跑满

sql
       

CREATE TABLE `test_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `created_at` INT(10) NULL,
  PRIMARY KEY (`id`));
1234567

|引擎类型|MyISAM|InnoDB|性能相差 -------|-----|----|----|---| 循环插入1万记录|324.83178091049【√】|712.46580886841|1倍 事务插入1万记录|313.85579895973|1.7757570743561【√】|300倍 主键更新100次|2.9796991348267【√】|7.0310151576996|1倍 非主键更新100次|8.5254480838776|9.1009860038757|差不多 查询所有count100次|0.016912937164307【√】|0.25129389762878|20倍 where查询count100次|0.017408847808838|0.021618127822876|差不多 查询单条主键100次|0.018386125564575|0.018260955810547|差不多 查询单条非主键100次|0.27979707717896【√】|0.45667195320129|1倍 like查询100次|0.31684398651123【√】|0.49412107467651|0.5倍 删除单条主键100次|3.5270810127258【√】|7.0672898292542|1倍 删除单条非主键100次|4.6953358650208【√】|11.668270111084|2倍 删除所有|0.23814415931702【√】|0.81685304641724|3倍

php
                                                                         
use Zodream\Database\DB;
use Zodream\Debugger\Domain\Timer;

DB::getEngine();
$timer = new Timer();
// 循环插入 10 万 记录
for ($i = 0; $i < 10000; $i ++) {
    DB::insert(sprintf('INSERT INTO `test_log` (`name`, `created_at`) VALUES (\'test%s\', \'%s\');', $i, time()));
}
$timer->record('insert 10000');
// 事务插入 10 万 记录
DB::transaction(function (\Zodream\Database\Engine\Pdo $pdo) {
    for ($i = 0; $i < 10000; $i ++) {
        $pdo->getDriver()->exec(sprintf('INSERT INTO `test_log` (`name`, `created_at`) VALUES (\'test%s\', \'%s\');', $i, time()));
    }
});
$timer->record('insert trans 10000');

$data = DB::select('SHOW TABLE STATUS where Name=\'test_log\';');
$size = $data[0]['Data_length'] + $data[0]['Index_length'];
$timer->record('free '. $size);
echo $size;
// 更新 主键
for ($i = 0; $i < 100; $i ++) {
    DB::update(sprintf('UPDATE `test_log` SET `name`=\'test_ttt_%s\' WHERE `id`=\'%s\';', $i, $i * 50 + 5));
}
$timer->record('update');
// 更新 非主键
for ($i = 0; $i < 100; $i ++) {
    DB::update(sprintf('UPDATE `test_log` SET `created_at`=\'%s\' WHERE `name`=\'test%s\';', $i, $i * 30 + 4));
}
$timer->record('update name');
// 查询 所有 count
for ($i = 0; $i < 100; $i ++) {
    DB::select('SELECT count(*) as count FROM test_log;');
}
$timer->record('select count');
// 查询 where count
for ($i = 0; $i < 100; $i ++) {
    DB::select(sprintf('SELECT count(*) as count FROM test_log WHERE `id`=\'%s\';', $i * 40 + 3));
}
$timer->record('select count where');
// 查询 单条主键
for ($i = 0; $i < 100; $i ++) {
    DB::select(sprintf('SELECT * FROM test_log WHERE `id`=\'%s\';', $i * 40 + 3));
}
$timer->record('select one');
// 查询 单条非主键
for ($i = 0; $i < 100; $i ++) {
    DB::select(sprintf('SELECT * FROM test_log WHERE `name`=\'test%s\';', $i * 40 + 3));
}
$timer->record('select one name');
// 查询 like
for ($i = 0; $i < 100; $i ++) {
    DB::select(sprintf('SELECT * FROM test_log WHERE `name` like \'%%%s%%\';', $i * 40 + 3));
}
$timer->record('select like');
// 删除 单条主键
for ($i = 0; $i < 100; $i ++) {
    DB::delete(sprintf('DELETE FROM `test_log` WHERE `id`=\'%s\';', $i * 60 + 3));
}
$timer->record('delete');
// 删除 单条非主键
for ($i = 0; $i < 100; $i ++) {
    DB::delete(sprintf('DELETE FROM `test_log` WHERE `name`=\'test%s\';', $i * 60 + 3));
}
$timer->record('delete name');
// 删除所有
DB::delete('DELETE FROM `test_log` WHERE 1');
$timer->record('delete all');
$timer->end();
$timer->log();
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273

实际选择

思考方向:

  1. 数据库是否有外键:innodb 支持
  2. 是否需要事务支持:innodb 支持
  3. 用什么样的查询模式:如果表中绝大多数都只是读查询,可以考虑MyISAM,如果既有读也有写,请使用InnoDB
  4. 数据有多大
  5. MyISAM恢复起来更困难

日志【MyISAM】

只插入,不修改

小型的应用或项目【MyISAM】

实在不会选,那么直接按项目的大小来选择

参考

  1. MyISAM与InnoDB两者之间区别与选择,详细总结,性能对比
  2. MySQL 8.0 Reference Manual: The MyISAM Storage Engine
  3. MySQL 8.0 Reference Manual: Chapter 15 The InnoDB Storage Engine
  4. MyISAM与InnoDB 的区别(9个不同点)
点击查看全文
0 212 0