对「MySQL JSON列」和「MongoDB」进行简单的基准测试
这是《MySQL Casual Advent Calendar 2017》第19天的文章。
昨天是bringer1092的《使用memcached轻松访问NDB Cluster》。
与去年的文章完全不同，今年我想要探讨一下MySQL的JSON列和MongoDB的简易基准测试。我之所以有这个念头，是因为我想在我的GitHub上留下一些东西。
请说明验证步骤。
1. 在同一台服务器上设置 MySQL 和 MongoDB。
2. 将“mysql_mongodb_simple_bench.sh”放置在任意目录中。
3. 将“json-templete.hbs”放置在/tmp目录下。
4. 执行“mysql_mongodb_simple_bench.sh”，并转为交互模式。
5. 输入“1”使用dummyjson命令生成测试数据。
6. 输入“1”在MySQL中执行基准测试。
7. 执行“mysql_mongodb_simple_bench.sh”，输入“2”跳过生成测试数据步骤。
8. 输入“2”在MongoDB中执行基准测试。
9. 检查“mysql_bench_result.log”/“mongodb_bench_result.log”的内容。
使用的脚本已在以下URL公开（欢迎Pull Request）。
https://github.com/kakuka4430/mysql_mongodb_simple_bench
验证环境
本次基准测试是在以下环境下进行的。
OSCentOS 7.4CPUXeon(R) E5-2403 8coreMEM62 GBDisk一般的なHDD
生成和使用的测试数据是100万条（约146M）的JSON文件。
MySQL和MongoDB的配置文件保持默认状态，不进行修改。
然而，为了允许只有MySQL加载测试数据，我们添加了secure_file_priv=’/tmp’的设置。
验证结果
以下的数字是整理了使用time命令输出的“real”时间。
■ MySQL: MySQL是一种开源的关系型数据库管理系统。
■ 无SQL
我发现了MongoDB比较快。然而，如果继续这样下去，MySQL的优势就会消失，所以让我们来进行调优吧。
调整1：更改服务器参数。
我先在my.cnf文件中尝试添加了以下设置。
query_cache_type=OFF
innodb_buffer_pool_size=4G
innodb_log_file_size=512M
innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_checksum_algorithm=crc3
skip_innodb_doublewrite
结果如下。虽然时间稍微缩短了一些，但基本上并没有太大的变化。
选项一：使用虚拟列进行索引添加。
正如参考网站所述，无法在JSON列上创建索引。
但是，可以使用虚拟列来模拟使用索引。
・SELECT count(*) FROM bench WHERE JSON_EXTRACT(col1,'$.nation') = 'Japan'
・UPDATE bench SET col1 = JSON_SET(col1, '$.phone', '000-000-0000') WHERE JSON_UNQUOTE( JSON_EXTRACT(col1,'$.phone') ) LIKE '080%'
・DELETE FROM bench WHERE JSON_EXTRACT(col1,'$.birth') >= '1995-01-01'
让我们为本次基准测试中使用的上述3个查询创建一个有效的索引。
mysql> ALTER TABLE bench ADD bench_nation VARCHAR(100) AS (JSON_UNQUOTE(col1->"$.nation"));
mysql> ALTER TABLE bench ADD INDEX (bench_nation);
mysql> ALTER TABLE bench ADD bench_phone VARCHAR(15) AS (REPLACE(JSON_UNQUOTE(col1->"$.phone"), '-', ''));
mysql> ALTER TABLE bench ADD INDEX (bench_phone);
mysql> ALTER TABLE bench ADD bench_birth DATE AS (JSON_UNQUOTE(col1->"$.birth"));
mysql> ALTER TABLE bench ADD INDEX (bench_birth);
如果虚拟列能够成功创建，我认为在重新加载数据后将会得到以下的输出。
mysql> DELETE FROM bench;
mysql> LOAD DATA INFILE '/tmp/sample_data.json' INTO TABLE bench(col1);
mysql> SELECT * FROM bench LIMIT 1\G
*************************** 1. row ***************************
id: 1000002
col1: {"id": 0, "name": "Drew Remus", "birth": "1997/02/08", "email": "drew.remus@protheon.xyz", "phone": "000-031-0950", "nation": "Tanzania, United Republic of"}
bench_nation: Tanzania, United Republic of
bench_phone: 0000310950
bench_birth: 1997-02-08
改变查询以使用虚拟列。在这种情况下，可以利用索引。
mysql> EXPLAIN SELECT count(*) FROM bench WHERE bench_nation = 'Japan';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | bench | NULL | ref | bench_nation | bench_nation | 103 | const | 3970 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
mysql> EXPLAIN UPDATE bench SET col1 = JSON_SET(col1, '$.phone', '000-000-0000') WHERE bench_phone LIKE '080%';
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+------+----------+------------------------------+
| 1 | UPDATE | bench | NULL | range | bench_phone | bench_phone | 18 | const | 1014 | 100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+------+----------+------------------------------+
mysql> EXPLAIN DELETE FROM bench WHERE bench_birth >= '1995-01-01';
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+--------+----------+-------------+
| 1 | DELETE | bench | NULL | range | bench_birth | bench_birth | 4 | const | 110872 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------+--------+----------+-------------+
使用此表格定义，我们将使用查询再次执行基准测试。
由于使用了CREATE TABLE IF EXISTS语句，所以不会重新创建表格。
请取消执行脚本中的查询注释。
由于索引的启用，SELECT和UPDATE的速度显著提高。然而，INSERT（LOAD DATA）的速度却明显变慢。另外，DELETE的时间变化不大。
可以考虑INSERT操作中虚拟列的存在导致了开销问题。
对于DELETE操作，可能是由于删除行数较多。
mysql> SELECT count(*) FROM bench WHERE bench_birth >= '1995-01-01';
+----------+
| count(*) |
+----------+
| 49979 |
+----------+
暂时先让我们把重点放在MySQL一侧。
附加选项1：对MongoDB进行调优
因为只对MySQL进行各种处理是不公平的，所以我也想简单调整一下MongoDB。就像对MySQL一样，我们来创建一些索引。
# echo 'db.bench.createIndex({nation : 1})' | mongo d1
# echo 'db.bench.createIndex({phone : 1})' | mongo d1
# echo 'db.bench.createIndex({birth : 1})' | mongo d1
由于MongoDB每次进行基准测试时都会重新创建表格，所以我们需要修改脚本。
以目前的状况进行基准测试的结果如下。索引的效果果然非常显著啊…。
附加项目2：尝试使用MySQL 8.0
我在顺应圣诞倒计时的潮流中，将MySQL 5.7升级到了MySQL 8.0。
# systemctl stop mysqld
# yum remove mysql-community-server
# vi /etc/yum.repos.d/mysql-community.repo
→ 5.7のリポジトリを「disabled」にし、8.0のリポジトリを「enabled」に
# yum install mysql-community-server
# mv /etc/my.cnf.rpmsave /etc/my.cnf
# vi /etc/my.cnf
→ "query_cache_type=OFF"をコメントアウト
# systemctl start mysqld
# mysql_upgrade -u root -p
结果如下所示。即使在MySQL8.0中，情况也没有太大改变……。
汇总
总结以下是本次执行的主要基准测试结果（两次执行的平均时间）。无论是MySQL还是MongoDB，都明白了索引的重要性！
如果有机会，我想进一步深入研究每个处理过程延迟的原因和对策。
以下是参考网站：
以下是几个网址：
1. https://yakst.com/ja/posts/3841
2. https://www.s-style.co.jp/blog/2017/06/420/
3. https://docs.mongodb.com/manual/indexes/
请注意，以上网址是为了提供背景信息，无需翻译。
