聊一聊MySQL的存储引擎

2022-01-20 00:00:00 数据数据库支持引擎分别为

一、前言

存储引擎(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好，当然这是在没有事务的场景下做的测试，笔者的测试实例比较简单，只能作为参考，不能代表实际的应用场景。

END

相关文章