千万级范围数据高效查询：数据库选型与MySQL优化咨询

问题背景 #

收到11位数字请求，需在数据库中快速定位该数字所属范围的**最后更新（id最大）**单行数据。当前使用MySQL并配置索引，但负载测试时查询耗时超出预期，目标将查询耗时控制在500ms以内。

当前数据库结构 #

1. range_mapping表 #

create table range_mapping (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`low_range` decimal(11,0) NOT NULL,
`high_range` decimal(11,0) NOT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT 1,
`code` int(8) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_comp_is_active_low_high_range` (`is_active`, `low_range`, `high_range`)
) ENGINE=InnoDB AUTO_INCREMENT=26891234 DEFAULT CHARSET=utf8

2. code_mapping表 #

create table code_mapping (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `nameIdx` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4410 DEFAULT CHARSET=utf8

待优化查询语句 #

select a.low_range, a.high_range, b.name from range_mapping AS a 
LEFT JOIN code_mapping AS b ON a.code = b.id 
WHERE a.is_active = 1 and 12345678912 BETWEEN a.low_range AND a.high_range 
ORDER BY a.id DESC 
limit 1;

查询执行计划（Explain） #

执行语句：

explain select a.low_range, a.high_range, b.name 
from range_mapping AS a force index(idx_comp_is_active_low_high_range) 
LEFT JOIN code_mapping AS b ON a.code = b.id 
WHERE a.is_active = 1 and 12345678912 BETWEEN a.low_range AND a.high_range 
ORDER BY a.id DESC limit 1; 

输出结果：

id: 1
select_type: SIMPLE
table: a
type: range
possible_keys: idx_comp_is_active_low_high_range
key: idx_comp_is_active_low_high_range
key_len: 11
ref: NULL
rows: 227190
Extra: Using index condition; Using filesort

***************************
id: 1
select_type: SIMPLE
table: b
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: testbackup.a.code
rows: 1
Extra: Using where

查询执行分析（Explain Analyze） #

-> Limit: 1 row(s)  (cost=65515 rows=0.104) (actual time=529..529 rows=1 loops=1)
    -> Nested loop left join  (cost=65515 rows=0.104) (actual time=529..529 rows=1 loops=1)
        -> Filter: ((a.is_active = 1) and (535387481 between a.low_range and a.high_range))  (cost=0.21 rows=0.104) (actual time=529..529 rows=1 loops=1)
            -> Index scan on a using PRIMARY (reverse)  (cost=0.21 rows=2) (actual time=1.25..490 rows=531614 loops=1)
        -> Filter: (a.code = b.id)  (cost=0.961 rows=1) (actual time=0.0347..0.0347 rows=1 loops=1)
            -> Single-row index lookup on b using PRIMARY (id=a.code)  (cost=0.961 rows=1) (actual time=0.0331..0.0331 rows=1 loops=1)

测试示例 #

• 请求数字：12345678912
• 匹配行示例：

low_range: 12345678901 high_range: 12345678913 
low_range: 12345678910 high_range: 12345678912
low_range: 12345678902 high_range: 12345678920

MySQL优化方案 #

1. 优化索引，消除文件排序 #

当前查询需要按id倒序取最新行，但现有索引无法同时满足范围匹配和排序需求。创建覆盖索引，将排序字段包含进来：

CREATE INDEX idx_active_low_high_id ON range_mapping (is_active, low_range, high_range, id);

该索引可先过滤is_active=1，再定位low_range <= 请求数字 <= high_range的范围，同时直接获取id用于排序，避免回表和filesort。

2. 调整查询逻辑，减少扫描范围 #

原查询需扫描所有符合范围的行再排序，可改为优先查找id最大的匹配行，找到即停止扫描：

SELECT a.low_range, a.high_range, b.name
FROM range_mapping AS a
LEFT JOIN code_mapping AS b ON a.code = b.id
WHERE a.is_active = 1
  AND a.low_range <= 12345678912
  AND a.high_range >= 12345678912
ORDER BY a.id DESC
LIMIT 1;

结合新索引，MySQL可利用索引顺序快速定位最大id的匹配行，无需扫描大量数据。

3. 数据类型优化 #

将low_range和high_range从decimal(11,0)改为bigint，整数类型的比较和索引效率更高：

ALTER TABLE range_mapping MODIFY COLUMN low_range bigint NOT NULL;
ALTER TABLE range_mapping MODIFY COLUMN high_range bigint NOT NULL;

4. 分区表优化 #

若数据量超千万级，可按low_range或id进行范围分区，减少扫描的数据分区数量：

ALTER TABLE range_mapping PARTITION BY RANGE (low_range) (
    PARTITION p0 VALUES LESS THAN (10000000000),
    PARTITION p1 VALUES LESS THAN (20000000000),
    ...
);

替代数据库方案 #

1. PostgreSQL + 原生范围类型 #

PostgreSQL支持int8range原生范围类型，可直接创建范围索引，查询效率更高：

CREATE TABLE range_mapping (
    id serial PRIMARY KEY,
    num_range int8range NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    code int
);
CREATE INDEX idx_active_num_range ON range_mapping (is_active, num_range);

查询语句简化为：

SELECT num_range, b.name
FROM range_mapping a
LEFT JOIN code_mapping b ON a.code = b.id
WHERE is_active = true AND num_range @> 12345678912::bigint
ORDER BY id DESC LIMIT 1;

2. 时序数据库（如InfluxDB） #

若数据按ID递增写入，且查询总是取最新匹配行，可利用时序数据库的分区特性快速定位最新数据，适合高并发场景。

3. 内存数据库（如Redis） #

将活跃范围数据加载到Redis Sorted Set中，以low_range为score存储high_range, id, code信息。查询时通过ZRANGEBYSCORE筛选low_range <= X的条目，再过滤high_range >= X的记录并取id最大的，适合超高并发场景。

内容的提问来源于stack exchange，提问作者Pacemaker 753