主节点SELECT查询快但从节点慢的MariaDB性能优化问询

环境与背景 #

• 运行MariaDB数据库记录瞬时事件，1000余台传感器向MaxScale集群写入数据，集群采用1主2从架构，主节点负责写入，事务同步至两个从节点
• 表结构包含EventTime（datetime类型，时间序列字段）、SensorID（varchar(20)类型，传感器标识字段）；数据增长极快，2个月已达4亿行，最终预计将达到20亿行

慢查询详情 #

执行以下SELECT查询时，主节点耗时基本在10秒内完成，但从节点耗时长达300-1000秒：

SELECT * FROM `table0` 
WHERE 
    (EventTime >= '2023-03-23 00:00:00' OR '2023-03-23 00:00:00' is null) 
AND 
    (EventTime <= '2023-03-23 23:59:59' OR '2023-03-23 23:59:59' is null) 
AND 
    (SensorID IN ('SL-1031-QL') OR COALESCE('SL-1031-QL') is null) 

执行计划（主从节点一致） #

MariaDB [db1000]> explain SELECT * FROM `table0` WHERE (EventTime >= '2023-03-23 00:00:00' OR '2023-03-23 00:00:00' is null) AND (EventTime <= '2023-03-23 23:59:59' OR '2023-03-23 23:59:59' is null) AND (EventID IN ('SL-1031-QL') OR COALESCE('SL-1031-QL') is null);
+------+-------------+-------+------+--------------------------+---------+---------+-------+--------+------------------------------------+
| id   | select_type | table | type | possible_keys            | key     | key_len | ref   | rows   | Extra                              |
+------+-------------+-------+------+--------------------------+---------+---------+-------+--------+------------------------------------+
|    1 | SIMPLE      | table0   | ref  | index_3,index_2,cindex_0 | index_2 | 62      | const | 365040 | Using index condition; Using where |
+------+-------------+-------+------+--------------------------+---------+---------+-------+--------+------------------------------------+
1 row in set (0.318 sec)

表索引信息 #

MariaDB [db1000]> show index from db1000.table0;
+-------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table0   |          0 | PRIMARY  |            1 | SensorID         | A         |   218159126 |     NULL | NULL   |      | BTREE      |         |               |
| table0   |          1 | index_3  |            1 | EventTime | A         |    21815912 |     NULL | NULL   |      | BTREE      |         |               |
| table0   |          1 | index_2  |            1 | EventID          | A         |      433715 |     NULL | NULL   |      | BTREE      |         |               |
| table0   |          1 | cindex_0 |            1 | EventTime | A         |    36359854 |     NULL | NULL   |      | BTREE      |         |               |
| table0   |          1 | cindex_0 |            2 | EventID          | A         |   218159126 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.000 sec)

已尝试的优化操作 #

• 调整从节点innodb_buffer_pool_size至内存的50%，并设置innodb_buffer_pool_instances=8，查询性能无明显提升；主节点innodb_buffer_pool_size仅128M（不足内存的1%），innodb_buffer_pool_instances=1，性能仍远优于从节点
• 分别测试query_cache_type=OFF+query_cache_size=0和query_cache_type=ON+query_cache_size=16777216两种配置，查询耗时无明显差异

核心疑问 #

推测主节点查询更快是因为数据已缓存至内存，无需从磁盘读取，而从节点存在大量磁盘IO操作。请问该如何优化从节点的SELECT性能？

内容的提问来源于stack exchange，提问作者Chi-Hsiu Liang