postgresql 更好的方法或数据库存储5000万范围数据和快速搜索操作

uelo1irk  于 2023-11-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(261)

**Problem Statement:**我们将收到一个数字(11位数)的请求,并且必须在数据库中有效地查找,并根据它适合的范围返回一行(最后更新)。此外,我已经使用mysql并使用适当的索引进行负载测试,但是它需要更多的时间。
当前DB结构:

使用MySQL
目前,我们有一个表,它有2列,即low_range和high_range,存储数据范围,还有2列存储相应的数据,即is_active(值可以是0和1)和code(int值,是另一个表的id,即code_mapping)。

表1名称:range_mapping

DB架构:

  1. create table range_mapping (
  2. `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  3. `low_range` decimal(11,0) NOT NULL,
  4. `high_range` decimal(11,0) NOT NULL,
  5. `is_active` tinyint(1) NOT NULL DEFAULT 1,
  6. `code` int(8) DEFAULT NULL,
  7. PRIMARY KEY (`id`),
  8. KEY `idx_comp_is_active_low_high_range` (`is_active`, `low_range`, `high_range`)
  9. ) ENGINE=InnoDB AUTO_INCREMENT=26891234 DEFAULT CHARSET=utf8
  10. Table 2 name: code_mapping

字符串
DB架构:

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

**待优化查询:**我需要重新设计或优化查询,以便更高效、更快地执行。

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

解释查询:

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

输出:

  1. id: 1
  2. select_type: SIMPLE
  3. table: a
  4. type: range
  5. possible_keys: idx_comp_is_active_low_high_range
  6. key: idx_comp_is_active_low_high_range
  7. key_len: 11
  8. ref: NULL
  9. rows: 227190
  10. Extra: Using index condition; Using filesort
  11. ***************************
  12. id: 1
  13. select_type: SIMPLE
  14. table: b
  15. type: eq_ref
  16. possible_keys: PRIMARY
  17. key: PRIMARY
  18. key_len: 4
  19. ref: testbackup.a.code
  20. rows: 1
  21. Extra: Using where

**咨询电话:**12345678912
表中可能的行:

  1. low_range: 12345678901 high_range: 12345678913
  2. low_range: 12345678910 high_range: 12345678912
  3. low_range: 12345678902 high_range: 12345678920

问题:

哪个数据库可以用来存储数百万的范围数据,并进行有效的查找。如果可能的话,用mysql有更好的方法吗?
我已经使用了mysql,并做了负载测试与适当的索引,但它是需要更多的时间。期望是查找应该是快速和有效的~500毫秒。

dohp0rv5

dohp0rv51#

PostgreSQL内置了range类型,它提供了一个@>包含操作符,由GiST和SP-GiST索引支持。问题是,索引查找的性能取决于样本的分布。如果我故意将所有填充样本移出目标范围,我可以在0.06 ms内从50 M行中挑出目标:demo1(这些演示是我做的测试的缩小示例)
| 查询计划|
| --|
| 仅索引使用test_r_idx对public.test进行扫描(cost=0.42..26414.98 rows=457289 width=22)(实际时间=0.043..0.047 rows=3 loops=1)|
| 输出:r|
| 索引条件:(test.r @> '12345678912'::bigint)|
| 堆提取:0|
| 规划时间:0.234 ms|
| 执行时间:0.060 ms|
如果我完全随机地生成它们,情况也是一样的,但是将范围宽度缩小到100(示例中的范围宽度是12、2、18)-在50 M的规模上,如此高的数字重叠是不可能的,选择性很高,索引扫描很快。如果我将范围宽度限制提高到100'000,它仍然只需要0.16 ms。demo2
如果我生成50 M * 完全 * 随机范围,它们中的大多数将跨越如此宽,它们不太可能 * 不 * 重叠并捕获目标,使其全部跳转到7 s,因为它通过了近一半的堆,因为索引在这种情况下并没有真正的帮助

  1. select setseed(0.1);
  2. create table test (r int8range);
  3. insert into test
  4. select int8range(least(a,b),greatest(a,b),'[]') from (
  5. select (random()*2e10)::int8 a,(random()*2e10)::int8 b
  6. from generate_series(1,(random()*15e6)::int))_;
  7. insert into test values (int8range(12345678901,12345678913,'[]'));
  8. insert into test
  9. select int8range(least(a,b),greatest(a,b),'[]') from (
  10. select (random()*2e10)::int8 a,(random()*2e10)::int8 b
  11. from generate_series(1,(random()*15e6)::int))_;
  12. insert into test values (int8range(12345678910,12345678912,'[]'));
  13. insert into test
  14. select int8range(least(a,b),greatest(a,b),'[]') from (
  15. select (random()*2e10)::int8 a,(random()*2e10)::int8 b
  16. from generate_series(1,(random()*15e6)::int))_;
  17. insert into test values (int8range(12345678902 ,12345678920,'[]'));
  18. insert into test
  19. select int8range(least(a,b),greatest(a,b),'[]') from (
  20. select (random()*2e10)::int8 a,(random()*2e10)::int8 b
  21. from generate_series(1,(select 5e7-count(*) from test)))_;
  22. create index on test using gist(r);
  23. vacuum analyze test;
  24. explain analyze verbose
  25. select * from test where r@>12345678912;

字符串
| 查询计划|
| --|
| 公共测试上的序列扫描(成本=0.00..943473.30行=23519755宽=22)(实际时间=18.990..6629.042行=23618706循环=1)|
| 输出:r|
| 过滤器:(test.r @> '12345678912'::bigint)|
| 删除过滤器:26381294|
| 计划时间:0.069 ms|
| JIT:|
| 功能:2个|
| 选项:Inlining true,Optimization true,Expressions true,Deforming true|
| 时序:生成0.355 ms,内联3.575 ms,优化11.550 ms,发射3.765 ms,总计19.244 ms|
| 执行时间:7608.159 ms|

展开查看全部

相关问题