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 占用差不多、内存占用不高、机械硬盘写入跑满
CREATE TABLE `test_log` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`created_at` INT(10) NULL,
PRIMARY KEY (`id`));
|引擎类型|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倍
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();
实际选择
思考方向：
- 数据库是否有外键：innodb 支持
- 是否需要事务支持：innodb 支持
- 用什么样的查询模式：如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB
- 数据有多大
- MyISAM恢复起来更困难
日志【MyISAM】
只插入，不修改
小型的应用或项目【MyISAM】
实在不会选，那么直接按项目的大小来选择
参考
- MyISAM与InnoDB两者之间区别与选择，详细总结，性能对比
- MySQL 8.0 Reference Manual: The MyISAM Storage Engine
- MySQL 8.0 Reference Manual: Chapter 15 The InnoDB Storage Engine
- MyISAM与InnoDB 的区别（9个不同点）
转载请保留原文链接： https://zodream.cn/blog/id/90.html