乐趣区

关于mysql:聊一聊MySQL的存储引擎

jiezi

一、前言

存储引擎 (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

退出移动版