存储和高效查找范围数据

问：

问题陈述：我们将收到一个数字（11 位数字）的请求，并且必须在数据库中有效地查找并根据其适合的范围返回一行（上次更新）。

当前数据库结构：

使用 MySQL

目前，我们有一个表，它有 2 列，即 low_range 和 high_range，用于存储数据范围，还有另外 2 列用于存储相应的数据，即 is_active（值可以是 0 和 1）和代码（int 值，它是另一个表的 id，即 code_mapping）。

表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

要优化的查询： 我需要重新设计或优化查询，以便高效、快速地执行。

要求：12345678912

表中可能的行：

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

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;

问题： 当以 20 个请求/秒执行上述查询时，最多需要 ~20 秒。我需要优化查询或数据库，使其在 500 毫秒内执行。

我已经添加了复合索引，它以某种方式优化了查询，并且还使用了强制索引作为查询的一部分。它仍然需要 2 秒多。

解释查询：

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

期望：如何改进数据库架构或优化查询以在毫秒内获取数据。