MySQL/MariaDB 按分数对行排序

Question

我有一个大约有 1300 万行的表，我的主键是一个十六进制值 VARBINARY(16) .

我使用以下查询来获取结果:

SELECT * 
FROM dbip 
WHERE ip_start <= INET6_ATON('XXX.XX.XX.XX') 
  AND addr_type = 4 
ORDER BY ip_start DESC 
LIMIT 1;

但是这个查询大约需要 0.1 秒，而应该是大约 0.02 秒或更短。

我的带宽 99% 在巴西，只有 131.270 行，我有列 country在我的数据库中。那么我该怎么做才能先在巴西搜索这个 IP，然后再在表的其余部分搜索呢？你认为这样我会获得一些毫秒吗？

我的 table :

CREATE TABLE `dbip` 
(
    `addr_type` TINYINT(1) NOT NULL,
    `ip_start` VARBINARY(16) NOT NULL,
    `ip_end` VARBINARY(16) NOT NULL,
    `country` CHAR(2) NOT NULL,
    `stateprov` VARCHAR(80) NOT NULL,
    `city` VARCHAR(80) NOT NULL,
    `latitude` FLOAT NOT NULL,
    `longitude` FLOAT NOT NULL,
    `timezone_offset` FLOAT NOT NULL,
    `timezone_name` VARCHAR(64) NOT NULL,
    `isp_name` VARCHAR(128) NOT NULL,
    `connection_type` VARCHAR(8) NULL DEFAULT NULL,
    `organization_name` VARCHAR(128) NOT NULL,
    PRIMARY KEY (`ip_start`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

这是实时数据，因此它必须很快。我也接受更改数据库的建议，我尝试了一些内存存储，但它不接受二进制值作为索引，或者它们不允许我使用 <=命令

Answer 1

这是非常好的架构，需要更多优化。

首先，您必须对所有认为可行的内容建立索引，以便立即引用和分组。索引国家代码本身会减少选择时间，即

SELECT * FROM dbip 
  WHERE 
  country="BR"
  AND addr_type = 4 
  AND ip_start <= INET6_ATON('XXX.XX.XX.')
  ORDER BY ip_start DESC;

会将选择范围缩小到 131270 行。 add_type 索引将进一步减少搜索。

其次，规范化那些重复的值，否则，您最终将难以维护数据完整性(想象一下有些存储城市名称时存在拼写错误)。此外，将大量数据加载到 DBMS 中会占用大量资源。是的，左连接这些数据是“不方便”的，但从长远来看它会节省你的时间。如果没有标准化，每行至少占用 530 个字节，如果结果返回 50k 行，那么 530bytes x 50k = 聚合前轻松 25MB(排序前)

如果没有LIMIT 1，这个查询肯定会花费几分钟以上的时间，因为它试图遍历整个 1300 万行，将大量数据聚合到内存。