一、前言
存储引擎(storage engine)是MySQL的专用称呼,数据库行业老大哥Oracle,以及SQL Server,PostgreSQL等都没有存储引擎的说法。
MySQL区别于其他数据库的重要特点就是,其插件式(pluggable)的表存储引擎。
引擎(engine)是外来音译词,习惯上认为是发动机,好像和数据库搭不上关系。
最早MySQL的存储引擎称为“数据表处理器”,可能是听起来太老土,后来才改成高大上的存储引擎。
存储引擎的功能是接收上层传下来的指令,然后对表中的数据进行读取或写入的操作。提醒一下,存储引擎操作的对象是表(table),而不是数据库(database)。
MySQL5.5版本之后开始采用InnoDB为默认存储引擎,之前版本默认的存储引擎为MyISAM。
我们来看一下MySQL8.0支持哪些存储引擎:
mysql> showengines;
+--------------------+---------+---------+--------------+------+------------+
| Engine | Support | Comment | Transactions| XA | Savepoints |
+--------------------+---------+---------+--------------+------+------------+
| FEDERATED | NO | | NULL | NULL | NULL |
| MEMORY | YES | | NO | NO | NO |
| InnoDB | DEFAULT | | YES | YES | YES |
|PERFORMANCE_SCHEMA | YES | | NO | NO | NO |
| MyISAM | YES | ... | NO | NO | NO |
| MRG_MYISAM | YES | | NO | NO | NO |
| BLACKHOLE | YES | | NO | NO | NO |
| CSV | YES | | NO | NO | NO |
| ARCHIVE | YES | | NO | NO | NO |
+--------------------+---------+---------+--------------+------+------------+
9 rows in set (0.00sec)
可以看到MySQL8.0支持9种存储引擎,默认使用InnoDB,而且只有InnoDB支持事务(Transactions)和分布式事务(XA),保存点(Savepoints)就是事务回滚所需要的功能。
昆仑分布式数据库实现的是分布式数据库集群的功能,使用的是InnoDB存储引擎。
二、各种存储引擎的特征介绍
2.1 Federated
Federated存储引擎,提供了访问远程MySQL数据库服务器上表的方法,本地并不存放数据,数据全部放到远程服务器上,本地需要保存表的结构和远程服务器的连接信息。
2.2 Memory
Memory存储引擎,也称HEAP存储引擎,数据保存在内存中,表结构保存在磁盘上。
如果数据库重启或者发生崩溃,表中的数据都将消失。非常适用于存储临时数据的临时表。其数据只存储于内存中,读写当然非常快,但使用时要考虑内存消耗。
2.3 Performance_schema
Performance_schema存储引擎,是MySQL数据库系统专用引擎,用户不能创建这种存储引擎的表。
系统默认数据库performance_schema中的表就是采用这种存储引擎。数据库performanceschema用于监控MySQL在一个较低级别的运行过程中的资源消耗、资源等待等情况。
2.4 Blackhole
Blackhole存储引擎,充当一个“黑洞”,接受数据,但将其扔掉,不存储数据,类似于Linux系统中的/dev/null文件。
这么特别的黑洞存储引擎,主要作用在于:Replication场景实现中继或过滤,验证转储文件语法,测量开启binlog日志所带来的额外开销,查找和存储引擎无关的其他方面的性能瓶颈。
2.5 CSV
CSV存储引擎,会在MySQL安装目录data文件夹中,和该表所在数据库名相同的目录生成一个.CSV文件,它可以将CSV类型的文件当做表进行处理,相比其他存储引擎的文件内容,可以直接查看和编辑。
2.6 Archive
Archive存储引擎,仅仅支持最基本的插入(insert)和查询(select)两种功能。Archive拥有很好的压缩机制,比MyISAM、InnoDB存储引擎更加节约存储空间。
可以用于:日志记录,打卡记录,天气信息记录等不需要数据更新的场景。
2.7 MyISAM
MyISAM存储引擎,是MySQL早期默认的存储引擎,拥有较高的插入、查询速度,表锁设计,支持全文索引,但不支持事务和外键。
如果表主要是用于插入新记录和读出记录,那么选择MyISAM能实现处理高效率。
2.8 MRG_MyISAM
MRG_MyISAM存储引擎,是一组MyISAM的组合,也就是说,他将MyISAM引擎的多个表聚合起来,但是他的内部没有数据,真正的数据依然是MyISAM引擎的表中,但是可以直接进行查询、删除更新等操作。
2.9 InnoDB
InnoDB存储引擎,是MySQL当前版本默认的存储引擎,支持事务安全表(ACID),支持行锁定和外键。
由于其支持事务处理、外键、支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高,要求实现并发控制,需要频繁的更新、删除操作的数据库,那选择InnoDB有很大的优势。
三、测试对比MyISAM和InnoDB不同场景下的差异
测试环境,机械硬盘的centos8虚拟机,MySQL最新版本8.26,mariadb客户端链接库。创建存储引擎分别为MyISAM和InnoDB的两个表:
create table tb_myisam(
id integer primarykey,
value integer) engine=myisam;
create table tb_innodb(
id integer primarykey,
value integer) engine=innodb;
3.1 插入对比
clock_gettime(CLOCK_REALTIME,&ts_start);
for(int i=1; i<=10000; i++)
{
sprintf(buf, "insert into tb_xxx(id,value)values(%d,%d)", i, i);
mysql_real_query(&conn, buf,strlen(buf));
}
clock_gettime(CLOCK_REALTIME, &ts_end);
耗时分别为:15秒(MyISAM),39秒(InnoDB)。
3.2 普通键值查询对比
clock_gettime(CLOCK_REALTIME,&ts_start);
for(int i=1; i<=10000;i++)
{
sprintf(buf,"select * from tb_xxx where value=%d", i);
mysql_real_query(&conn,buf, strlen(buf));
MYSQL_RES*mysql_res = mysql_store_result(&conn);
MYSQL_ROW row =mysql_fetch_row(mysql_res);
mysql_free_result(mysql_res);
}
clock_gettime(CLOCK_REALTIME,&ts_end);
耗时分别为:140秒(MyISAM),23秒(InnoDB)。
3.3 主键索引查询对比
将上一步的查询语句改成按主键索引查询
sprintf(buf, "select * from tb_xxx where id=%d", i);
耗时分别为:1.25秒(MyISAM),1.30秒(InnoDB)。
3.4 更新对比
clock_gettime(CLOCK_REALTIME,&ts_start);
for(int i=1; i<=10000; i++)
{
sprintf(buf, "update tb_xxx setvalue=%d where id=%d", i+1, i);
mysql_real_query(&conn, buf,strlen(buf));
}
clock_gettime(CLOCK_REALTIME, &ts_end);
耗时分别为:15秒(MyISAM),44秒(InnoDB)。
3.5 删除对比
clock_gettime(CLOCK_REALTIME,&ts_start);
for(int i=1; i<=10000; i++)
{
sprintf(buf, "delete from tb_xxxwhere id=%d", i);
mysql_real_query(&conn, buf,strlen(buf));
}
clock_gettime(CLOCK_REALTIME, &ts_end);
耗时分别为:14秒(MyISAM),45秒(InnoDB)。
综合测试结果,MyISAM普通查询速度对比InnoDB慢了很多,其他测试性能都比InnoDB好,当然这是在没有事务的场景下做的测试,笔者的测试实例比较简单,只能作为参考,不能代表实际的应用场景。
KunlunDB项目已开源
【GitHub:】
https://github.com/zettadb
【Gitee:】
https://gitee.com/zettadb
END