问题
我有以下表定义:
id BIGINT
resource_id INT
timestamp DATETIME
data JSON
主键是id,我在(resource_id, timestamp)上有一个索引。数据是一个 json 对象(3KB),在这个 FLOAT 类型的对象中有 5 个参数(除其他外)定期获取。表大小约为 60GB 和 15,000,000 行。每分钟记录每个资源 ID 的数据。
我想优化以下查询:
SELECT
resource_id
timestamp
data->'$.p1' /* can also include the other parameters in the select p2-p5 */
FROM table
WHERE resource_id = ? AND timestamp BETWEEN ? AND ?;
我试过的
- 为每个参数创建一个虚拟列并为每个参数创建一个索引(resource_id, timestamp, pX)。这将查询从 70 秒显着提高到 8 秒(获取 6 个月),但我担心索引的数量会减慢我的插入速度。此外,我不能再在查询中包含多个参数,因为只能使用一个索引。
我还研究了按 resource_id 进行的分区,但我不确定这是否会有所帮助,因为 mysql 将分区限制为 8192,并且资源很容易超过这个值。
关于如何以 mysql 允许的速度获取这些数据的任何想法?
在读取数据时,以非规范化格式(如 JSON)存储数据在性能方面通常不是很好,尤其是在大量数据上。因此,将 JSON 数据标准化为单独的列
parameter
是朝着正确方向迈出的一步。大概你创建了 5 个索引,因为有 5parameters
,对吧?...单个表上的 5 个索引大致是我的目标,但这不是一个硬性规则。真正了解它们是否会对您的写入性能产生负面影响的唯一方法是对其进行测试。但我相信您甚至不需要创建这么多索引,因为您没有
parameters
在任何谓词(JOIN
、、WHERE
或HAVING
子句)中使用,您只是SELECT
在查询中使用它们。相反,只要您的索引涵盖所有字段,它就适用于查询的每次迭代。我相信定义为的单个索引(resource_id, timestamp, p1, p2, p3, p4, p5)
应该涵盖您。此外,如果 的字段组合
(resource_id, timestamp)
保证唯一,那么您可以将它们定义为主键,这将导致它们成为表的聚集索引。这将相应地对实际表本身进行排序,并在您想要阅读任何parameter
列时为您提供保护。