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

Question

我有一个包含三列的玛丽亚DB 我的表：

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

此外，我有一个关于列时间和传感器的主键（BTREE）

该表有2.5亿行，每10秒添加20条新记录。表上的所有SELECTS都有一个WHERE子句，要么仅在time上，要么在time和传感器上。

这在性能方面效果很好，但索引使用的磁盘空间甚至比表本身还要多（数据为2.2GB，索引为2.7GB）。这对我来说似乎有点傻，因为索引基本上是整个表，这意味着MariaDB基本上将所有数据翻了一番。

这张桌子有更好的结构吗？

Answer 1

数据库索引的整个要点是以空间换取时间。是的，索引占用的空间与表大致相同，这是正常的，尤其是当表像您这样有短行时。

如果您切换到InnoDB存储引擎，您的主键将变成所谓的聚集索引。也就是说，整个表将包含在主键的索引中。这样可以节省大量的磁盘空间。

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

请注意:您的主键在< code >(时间，传感器)上。这意味着它最适合如下所示的where子句:

 WHERE time BETWEEN start AND finish

如果您重新制作了主键，使其位于（传感器，时间）上，则适合

 WHERE sensor=somesensor AND time BETWEEN start AND finish

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

因此，您对主键列顺序的选择应该基于这两个< code>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;

该表定义提供了一个聚类主键，该主键针对先按传感器再按时间范围进行过滤进行了优化。我还添加了一个覆盖索引(基本上是表的副本),只针对按时间范围过滤进行了优化。有关覆盖索引的信息，请参见本文的最后一节。

它使用< code>TIMESTAMP数据类型。它们在表示时间上和整数一样有效，你可以从时间算法中获益。这将为您提供传感器3昨天的读数。

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

它使用一个小号而不是一个TINYINT作为传感器编号。您不太可能用完传感器编号，并且 TINYINT 数据仅在每行中有多个传感器编号时才有助于节省空间。