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

47次阅读

共计 3805 个字符,预计需要花费 10 分钟才能阅读完成。

一、前言

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

正文完
 0