时间戳

首页 > 解决方案 > 在mysql上以最快的方式查找数字序列，

mysql - 在mysql上以最快的方式查找数字序列，

问题描述

我有十亿行。每一行都是一个数字序列：

32098;1278;23902;8469
42710;17864;32230
230984;812918;420322;182972
339028;232329;2190120;23302;182972
232329;17864;32230;23302;182972

如何存储该数据并在其中搜索，因此查找任何子序列的搜索时间最短：

示例：搜索序列“17864;32230”输出：

42710;17864;32230
232329;17864;32230;23302;182972

我试过的：

有更快的方法吗？

标签： mysqlsql

解决方案

搜索序列“17864;32230”输出是否会选择接下来的两个值：“17864;123456;32230”、“123456;32230;17864”？– 秋名

@akina, "17864;123456;32230", "123456;32230;17864" 不能作为输出，因为它们不包含序列 "17864;32230" – JoJo

即你的序列是位置相关的......好吧。要找到的序列是否始终是 2 值的，或者它的长度（以元素为单位）可能会有所不同？– 秋名

@Akina，要找到的序列始终是 2 值的。你是对的:) – JoJo

“数组”中的每个单独值是否都有上限？例如，不超过 6 位数字...... – Akina

@Akina，你说得对，在我的具体情况下，序列中的数字限制为 8 位数字 – JoJo 10 分钟前

寻找这个解决方案：

小提琴

CREATE TABLE sourcetable ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                           dataarray TEXT );
INSERT INTO sourcetable (dataarray) VALUES 
('32098;1278;23902;8469'),
('42710;17864;32230'),
('230984;812918;420322;182972'),
('339028;232329;2190120;23302;182972'),
('232329;17864;32230;23302;182972');

-- create indexing table
CREATE TABLE indexingtable ( id BIGINT UNSIGNED NOT NULL,
                             sequence BIGINT UNSIGNED NOT NULL,
                             PRIMARY KEY (sequence, id) );
-- and fill it
INSERT IGNORE INTO indexingtable
-- assume not more than 6 elements per "array"
WITH cte AS ( SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 )
SELECT id, CONCAT(LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(dataarray, ';', num), ';', -1), 9, '0'),
                  LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(dataarray, ';', num+1), ';', -1), 9, '0'))
FROM sourcetable, cte;

-- search for "17864;32230"
SET @criteria := 17864000032230;

-- perform searching
SELECT sourcetable.*
FROM sourcetable
JOIN indexingtable USING (id)
WHERE sequence = @criteria;

编号 | 数据数组                      
-: | :--------------------------------
 2 | 42710;17864;32230              
 5 | 232329;17864;32230;23302;182972

EXPLAIN 
SELECT sourcetable.*
FROM sourcetable
JOIN indexingtable USING (id)
WHERE sequence = @criteria;

编号 | 选择类型 | 表| 隔断 | 类型 | 可能的键 | 关键 | key_len | 参考 | 行 | 过滤 | 额外的                   
-: | :------------ | :------------ | :--------- | :----- | :------------ | :-------- | :-------- | :-------------------------------------------- | ---: | --------: | :------------------------
 1 | 简单 | 索引表 | 空       | 参考 | 初级 | 初级 | 8 | 常量 | 2 | 100.00 | 使用哪里；使用索引
 1 | 简单 | 源表 | 空       | eq_ref | 初级 | 初级 | 8 | fiddle_KJQBRBTPCZAIOJRJHGJJ.indexingtable.id | 1 | 100.00 | 无效的

db<>在这里摆弄

在indexingtable十亿个源记录上，查询创建将是一个极其漫长和昂贵的过程。我建议将源数据导出为文本（SELECT .. INTO OUTFILE），使用任何脚本/编程语言将其转换，然后导入indexingtable. 它也会很长，但比查询快得多。

推荐阅读