我们的科学应用程序需要存储和查询许多分子的基本参数。每个分子有 2 到 2800 万行,但分子数量预计会保持较小(目前为 4)。这是我们正在使用的表:
CREATE TABLE `mol_trans` (
`species_id` int(11) DEFAULT NULL,
`wl_vac` double DEFAULT NULL,
`upper_id` int(11) DEFAULT NULL,
`lower_id` int(11) DEFAULT NULL,
`prob` double DEFAULT NULL,
`flag` tinyint(4) DEFAULT NULL,
KEY `spid_flag_wl` (`species_id`,`flag`,`wl_vac`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
PARTITION BY LIST (`species_id`)
(PARTITION `CaO` VALUES IN (6115) ENGINE = InnoDB,
PARTITION `CN3` VALUES IN (6121) ENGINE = InnoDB,
PARTITION `CN2` VALUES IN (6119) ENGINE = InnoDB,
PARTITION `AlO` VALUES IN (6109) ENGINE = InnoDB)
(分区在这里是为了在需要时更容易删除整个分子,否则这将是一个痛苦的大DELETE
。在添加分区之前,性能问题就已经存在了。)
我将使用 10.3.39-MariaDB-0+deb10u1(使用命令行客户端通过 UNIX 域套接字连接)进行测试,但我们在 Windows 10 上的 MySQL 5.6 和 MariaDB 10.11 上看到了相同的问题。
以下查询在我的机器上大约需要45秒,使用以下方法测量time echo "$QUERY" | mysql $DATABASE >/dev/null
:
select
mtr.prob,
mtr.lower_id,
mtr.upper_id
from
mol_trans mtr
where (
mtr.species_id=6115
and mtr.wl_vac > 766.0
and mtr.wl_vac < 883.0
and mtr.flag = 1
)
order by mtr.wl_vac;
该查询生成 3024559 行并且似乎使用了索引:
+------+-------------+-------+------+---------------+--------------+---------+-------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+--------------+---------+-------------+----------+-------------+
| 1 | SIMPLE | mtr | ref | spid_flag_wl | spid_flag_wl | 7 | const,const | 14158123 | Using where |
+------+-------------+-------+------+---------------+--------------+---------+-------------+----------+-------------+
我尝试过将数据库转换为 PostgreSQL,虽然我不完全相信转换结果,但同一台机器上相同的查询在不到 6 秒的时间内返回了超过 300 万行。但是 MySQL/MariaDB C 连接器 API 是我们的应用程序已经编写的,我们希望保持集中更新数据库的便利性。
问题:如何加快 MySQL 的速度,使查询完成所需的时间更短,至少在本地服务器上,更接近 PostgreSQL 的 6 秒?我尝试启用 255 字节直方图并运行ANALYZE TABLE mol_trans PERSISTENT FOR ALL
,但这使情况变得更糟(运行相同的查询最多需要 2 分钟)。令人惊讶的是,OPTIMIZE TABLE mol_trans
查询时间恢复到约 40 秒(通过重新创建表)。此外,如果我执行set profiling=on
并运行ANALYZE
查询,则大部分时间显示为花费在发送数据上:
+------------------------+-----------+
| Status | Duration |
+------------------------+-----------+
| Starting | 0.000078 |
| Checking permissions | 0.000005 |
| Opening tables | 0.000021 |
| After opening tables | 0.000004 |
| System lock | 0.000004 |
| Table lock | 0.000004 |
| Init | 0.000028 |
| Optimizing | 0.000027 |
| Statistics | 0.000088 |
| Preparing | 0.000021 |
| Sorting result | 0.000008 |
| Executing | 0.000003 |
| Sending data | 40.324591 |
| End of update loop | 0.000032 |
| Query end | 0.000002 |
| Commit | 0.000003 |
| Closing tables | 0.000003 |
| Unlocking tables | 0.000001 |
| Closing tables | 0.000008 |
| Starting cleanup | 0.000002 |
| Freeing items | 0.000006 |
| Updating status | 0.000011 |
| Reset for next command | 0.000002 |
+------------------------+-----------+
当与远程服务器通信时,我可以在提交查询后很快看到查询结果以文本形式出现在 Wireshark 中(终端保持沉默,直到收到整个结果)。有没有办法加快文本格式化过程?MariaDB 文档表明准备好的语句可能会导致使用二进制协议,这可能会导致序列化速度更快。或者是吗?我编译了一个测试程序mysql_store_result
,使用和下载查询结果mysql_stmt_fetch
,看起来这两种方法的工作速度大致相同。
我还有其他选择吗?
我相信您自己找到了缓慢问题的答案:
在您提到的评论中:
您针对 PostgreSQL 测量的 6 秒仍然可能是内核通过 UNIX 域套接字将数据从一个进程传输到另一个进程的开销。像 SQLite 这样的嵌入式数据库本质上比守护进程更快。
“发送数据”是一个转移注意力的话题
可以看出,
sql/sql_select.cc
除了将结果集序列化并发送给用户之外,调用JOIN::exec_inner()
之前还执行许多其他工作。因此,即使查询花费大量时间“发送数据”,问题仍然可能是由于查询的计划和执行方式造成的,而不是协议开销。stage_sending_data
do_select()
使用索引的方法不止一种
以下两个查询仅在
FORCE INDEX
语句上有所不同:FORCE INDEX
FORCE INDEX
就其本身而言,查询优化器似乎更喜欢使用
spid_flag_wl
索引的前缀(请参阅:type=ref
和key_len=7
),然后按 过滤行WHERE
。对于FORCE INDEX
,使用整个索引(key_len=16
,这似乎对应于两个int
s 后跟一个double
;另外,type=range
)。仅使用索引的前缀,查询规划器预计会找到 1400 万行,但发现的行数是原来的两倍,并且只需要提取其中的约 10% (r_filtered
)。使用完整索引,查询规划器不仅发现的行数少于估计的行数,而且所有行数都适用。(有关如何解释 的输出,请参阅EXPLAIN和ANALYZE
ANALYZE SELECT
。)使用
FORCE
,卢克不幸的是,(根据我的经验)没有任何
ANALYZE TABLE
帮助 MariaDB 根据键值的分布自动选择使用完整索引。FORCE INDEX
但由于索引是专门为此查询设计的,因此用于指导查询优化器并没有什么坏处。该解决方案提高了我尝试过的所有内容的查询性能,从 Windows 10 上的 MySQL 5.6 到 GNU/Linux 上的 MariaDB-10.3.39-0+deb10u1 和 11.1.2。这已被报告为 bug MDEV-32646。
建议增加 pH 值(谁需要酸?)
MyISAM 是一个针对读取操作繁重的环境进行优化的存储引擎,这正是这个科学应用程序的用途。作为维护过程,写入很少发生,并且如果表确实损坏,则相对容易从头开始创建表。遵循Vassilis Virvilis 的极好建议,我尝试使用 重新创建该表
ENGINE=MyISAM
。我在3,528 秒内得到了查询结果,这超过了我使用客户端-服务器数据库引擎得到的所有其他结果。