MariaDB数据存储引擎的选择

MariaDB数据存储引擎的选择

像MySQL一样MariaDB，也有很多数据存储引擎，其官方网站对每个引擎都有非常详细的介绍。

写这篇文章之前，我一直以为InnoDB才是王道，事实上，每种存储引擎都有各自的特点，就像不同的编程语言一样，没有优劣之分，只有适用的场景不同。这里列举几个常用的数据存储引擎：

引擎名称	特点
MyISAM	不支持事务和外键，但insert和select速度非常快
Aria	MyISAM存储引擎的增强版，支持自动崩溃安全恢复
XtraDB	InnoDB的增强版，支持事务处理
CSV	将数据保存在CSV文件中
MEMORY	将数据保存在内存中，读写速度快，但关闭时数据会丢失
SphinxSE	Sphinx使用的引擎，支持数据全文检索

作为一篇关注性能的文章，本文重点测试MyISAM、Aria和XtraDB的的性能。当然，三者只有XtraD支持事务，测试是在不使用事务的场景下进行的。

测试环境

本性能测试环境如下：

• CPU：I7
• 内存：8G
• OS：Windows 10 64位
• 硬盘类型：SSD
• MariaDB：10.4.12

分别用三种引擎创建三张表，MariaDB为了与MySQL兼容，要使用XtraDB，还是需要指定为InnoDB。

CREATE TABLE `performance`.`log_xtradb`(  
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `time` DATETIME NOT NULL,
  `level` ENUM('info','debug','error') NOT NULL,
  `message` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE `performance`.`log_myisam`(  
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `time` DATETIME NOT NULL,
  `level` ENUM('info','debug','error') NOT NULL,
  `message` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM CHARSET=utf8;

CREATE TABLE `performance`.`log_aria`(  
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `time` DATETIME NOT NULL,
  `level` ENUM('info','debug','error') NOT NULL,
  `message` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ARIA CHARSET=utf8;

插入性能

单条插入

单条插入的测试结果如下表所示：

条数\数据库	XtraDB	MyISAM	Aria
1000	0:00:02.819683	0:00:00.712045	0:00:02.679292
2000	0:00:05.645522	0:00:01.245605	0:00:04.919199
3000	0:00:08.640754	0:00:01.007064	0:00:08.578473
4000	0:00:11.783709	0:00:02.630398	0:00:09.977887
5000	0:00:15.312341	0:00:02.340841	0:00:15.730704
6000	0:00:17.619624	0:00:04.742592	0:00:18.107170
7000	0:00:21.330231	0:00:03.628463	0:00:19.398530
8000	0:00:24.137878	0:00:04.868233	0:00:21.840180
9000	0:00:26.558606	0:00:05.701740	0:00:26.071422
10000	0:00:30.758629	0:00:03.409164	0:00:29.096098
平均	0:00:00.002993	0:00:00.000551	0:00:00.002844

单条数据插入XtraDB与Aria相差不大，而MyISAM表现特别突出，比二者的速度快5倍以上。

批量插入

批量插入的测试结果如下表所示：

条数\数据库	XtraDB	MyISAM	Aria
1000	0:00:00.024932	0:00:00.010970	0:00:00.015546
2000	0:00:00.031166	0:00:00.034886	0:00:00.039567
3000	0:00:00.078076	0:00:00.031006	0:00:00.062644
4000	0:00:00.079511	0:00:00.046863	0:00:00.103948
5000	0:00:00.077702	0:00:00.062485	0:00:00.094910
6000	0:00:00.097492	0:00:00.063765	0:00:00.105757
7000	0:00:00.093764	0:00:00.093483	0:00:00.143457
8000	0:00:00.105830	0:00:00.077884	0:00:00.150275
9000	0:00:00.106734	0:00:00.093512	0:00:00.187510
10000	0:00:00.123175	0:00:00.093367	0:00:00.234525
平均	0:00:00.000015	0:00:00.000011	0:00:00.000021

即便是批量插入操作，MyISAM的性能也要比InnoDB和Aria好一些。

查询性能

经过了多次插入测试，我两个数据库里插入了很多数据，此时用下面的sql查询表中的数据量：

SELECT COUNT(0) FROM LOG_XTRADB;
SELECT COUNT(0) FROM LOG_MYISAM;
SELECT COUNT(0) FROM LOG_ARIA;

三个表都是11110000条数据，查询XtraDB用时2.761秒，MyISAM和Aria都是0秒，原因是MyISAM和Aria记录了表内的数据量，直接取出了，如果使用where条件后，三者的执行逻辑就相同了。

无索引

先查询一下time字段的最大值和最小值：

SELECT MAX(TIME), MIN(TIME) FROM LOG_XTRADB;
SELECT MAX(TIME), MIN(TIME) FROM LOG_MYISAM;
SELECT MAX(TIME), MIN(TIME) FROM LOG_ARIA;

XtraDB用时4.892秒，MyISAM用时3.676秒，Aria用时5.526秒。

接下来测试过滤time字段在0点到1点之间的数据，并对time字段排序：

SELECT * FROM LOG_XTRADB WHERE TIME > '2020-02-04 00:00:00' AND TIME < '2020-02-04 01:00:00' ORDER BY TIME;
SELECT * FROM LOG_MYISAM WHERE TIME > '2020-02-04 00:00:00' AND TIME < '2020-02-04 01:00:00' ORDER BY TIME;
SELECT * FROM LOG_ARIA WHERE TIME > '2020-02-04 00:00:00' AND TIME < '2020-02-04 01:00:00' ORDER BY TIME;

XtraDB用时5.399秒，MyISAM用时4.163秒，Aria用时5.314秒。

然后测试查询level字符是info的数据：

SELECT * FROM LOG_XTRADB WHERE LEVEL = 'info';
SELECT * FROM LOG_MYISAM WHERE LEVEL = 'info';
SELECT * FROM LOG_ARIA WHERE LEVEL = 'info';

XtraDB用时0.099秒，MyISAM用时0.026秒，Aria用时0.028秒。
最后测试查询message字段值为debug的数据：

SELECT * FROM LOG_XTRADB WHERE MESSAGE = 'debug';
SELECT * FROM LOG_MYISAM WHERE MESSAGE = 'debug';
SELECT * FROM LOG_ARIA WHERE MESSAGE = 'debug';

XtraDB用时0.025秒，MyISAM用时0.003秒，Aria用时0.003秒。

无索引的情况下，MyISAM的查询性能比XtraDB能好上不少。

有索引

分别对三个表中的每个字段创建索引：

ALTER TABLE `performance`.`log_xtradb`   
  ADD  INDEX `time` (`time`),
  ADD  INDEX `level` (`level`),
  ADD FULLTEXT INDEX `message` (`message`);
ALTER TABLE `performance`.`log_myisam`   
  ADD  INDEX `time` (`time`),
  ADD  INDEX `level` (`level`),
  ADD FULLTEXT INDEX `message` (`message`);
ALTER TABLE `performance`.`log_aria`   
  ADD  INDEX `time` (`time`),
  ADD  INDEX `level` (`level`),
  ADD FULLTEXT INDEX `message` (`message`);

XtraDB用时6分56秒，MyISAM用时4分8秒，Aria用时4分43秒。再用上面的测试项目进行测试，结果如下表所示：

项目\数据库	XtraDB	MyISAM	Aria
查询time字段最值	0.010秒	0.002	0.002秒
过滤time字段并排序	0.020秒	0.009秒	0.015秒
查询level字段	0.023秒	0.010秒	0.003秒
查询message字段	0.036秒	0.003秒	0.003秒

有索引的情况下，MyISAM的查询性能依然比XtraDB更有优势。

总结

不能盲目追求XtraDB的传说。在业务过程中应该综合考虑，对于数据量大的表，尽量使用MyISAM引擎，避免事务操作，只有对必须启用事务的表，才选择XtraDB作为数据存储引擎。