MariaDB / MySQL:一个值列表的索引结构

Question

我有一个MariaDB MyISAM表，有三列:

`time` int not null, 
`sensor` tinyint not null, 
`value` decimal (6,4) not null

我也有一个主键(BTREE)在列time和sensor

表有2.5亿行，每10秒增加20条新记录。表上的所有select都有一个WHERE子句，要么只在time上，要么在time和sensor上。

这在性能方面工作得很好，但是索引比表本身使用更多的磁盘空间(2.2GB用于数据，2.7GB用于索引)。这对我来说似乎有点傻，因为索引基本上是整个表，这意味着MariaDB实际上已经将所有数据翻了一倍。

这个表有更好的结构吗?

Answer 1

数据库索引的全部意义在于用空间换取时间。是的，索引占用和表一样多的空间是正常的，特别是当表的行很短的时候，就像你的表一样。

如果你切换到InnoDB存储引擎，你的主键将成为一个所谓的集群索引。也就是说，整个表将包含在主键的索引中。这样可以节省很多磁盘空间。

你应该切换到InnoDB: MyISAM是一个传统的存储引擎，并没有得到太多的关注从MariaDB的开发人员。如果由于某些原因InnoDB不适合您，请切换到更现代的Aria存储引擎。它类似于MyISAM。与MyISAM一样，它不使用聚类PK索引。

请注意:主键在(time, sensor)上。这意味着它最适合这样的子句:

WHERE time BETWEEN start AND finish

如果你把主键改成了(sensor, time)，那么

就适合了

WHERE sensor=somesensor AND time BETWEEN start AND finish

为什么?MySQL随机访问BTREE索引到第一个符合条件的行，然后依次扫描到最后一个符合条件的行。您可以在这里和这里阅读有关多列索引的内容。

因此，您选择的主键列顺序应该基于这两个WHERE模式中哪一个对性能更关键:仅时间范围还是传感器和时间范围。

如果这是我的表，我会这样定义它:

CREATE TABLE series (
time TIMESTAMP NOT NULL DEFAULT current_timestamp(),
sensor SMALLINT(6) NOT NULL DEFAULT '0',
value DECIMAL(6,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (sensor, time) USING BTREE,
INDEX time_covering (time, sensor, value) USING BTREE
) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;

这个表定义提供了一个优化的集群主键，可以根据传感器和时间范围进行过滤。我还添加了一个覆盖索引(基本上是表的副本)，该索引仅针对时间范围进行了优化。有关覆盖索引的信息，请参阅本文的最后一节。

使用TIMESTAMP数据类型。它们在将时间表示为整数时同样有效，而且你得到了时间算术的好处。这是昨天传感器3的读数。

WHERE time >= CURDATE() - INTERVAL 1 DAY
AND time < CURDATE()
AND sensor = 3

它使用SMALLINT而不是TINYINT作为传感器编号。你不太可能用完传感器数量，TINYINT数据只有在每行有几个传感器时才能帮助节省空间。