mysql - 使用复杂查询加快搜索分页
问题描述
我正在开发一个带有底层 MySQL 数据库的 API。
我有一些涉及多个连接的复杂查询，其中一些表很大（> 2 亿行）。
我通过根据 uid 对查询结果进行排序并在最后一个 uid 之后获取页面来使用搜索分页。
对于大多数查询，这非常快，但是如果查询返回一个页面上的 uids 有很大跳跃的集合，这将变得非常慢。
例如，如果第一页返回 uids 1、2、3、4 和 5，它的速度非常快，但如果查询返回 uids 1、2、3 50000000 和 50000001，则查询变得非常慢（约 100 秒）。
有什么想法可以加快速度吗？
这是一个复杂的查询，它周围的表结构和索引：
>Q1 (fast if there is no jump in h.uid)
SELECT STRAIGHT_JOIN *
FROM h use index(ix_join) JOIN r ON r.uid = h.rID JOIN so ON so.uid = h.soID
JOIN e ON e.uid = h.eID JOIN sa ON sa.uid = h.saID
WHERE r.chr = '2' AND r.r1 >= 99999999 AND r.r2 <= 103000000 AND r.bin
IN (0, 2, 20, 21, 168, 169, 170, 171, 1347, 1348, 1349, 1350, 1351, 1352, 1353, 1354, 1355, 1356, 1357, 1358, 1359, 1360, 1361, 1362, 1363, 1364, 1365, 1366, 1367, 1368, 1369, 1370)
AND h.uid > 77178 ORDER BY h.uid
LIMIT 1000
>explain Q1
id|select_type|table |type |possible_keys |key |key_len|ref |rows |Extra |
--|-----------|------|------|--------------------------|-------|-------|------------|---------|---------------------------|
1|SIMPLE |h |ALL |ix_join | | | |162690954|Using where; Using filesort|
1|SIMPLE |r |eq_ref|PRIMARY,ix_uid,ix_bin_chr |PRIMARY|4 |hg38.h.rID | 1|Using where |
1|SIMPLE |so |eq_ref|PRIMARY,ix_uid |PRIMARY|4 |hg38.h.soID | 1| |
1|SIMPLE |e |eq_ref|PRIMARY,ix_uid |PRIMARY|4 |hg38.h.eID | 1| |
1|SIMPLE |sa |eq_ref|PRIMARY,ix_uid |PRIMARY|4 |hg38.h.saID | 1| |
>Q2 (takes about 15s regardless of jumps in h.uid)
SELECT *
FROM h use index(ix_join) JOIN r ON r.uid = h.rID JOIN so ON so.uid = h.soID
JOIN e ON e.uid = h.eID JOIN sa ON sa.uid = h.saID
WHERE r.chr = '2' AND r.r1 >= 99999999 AND r.r2 <= 103000000 AND r.bin
IN (0, 2, 20, 21, 168, 169, 170, 171, 1347, 1348, 1349, 1350, 1351, 1352, 1353, 1354, 1355, 1356, 1357, 1358, 1359, 1360, 1361, 1362, 1363, 1364, 1365, 1366, 1367, 1368, 1369, 1370)
AND h.uid > 77178 ORDER BY h.uid
LIMIT 1000
>explain Q2
id|select_type|table |type |possible_keys |key |key_len|ref |rows |Extra |
--|-----------|------|------|--------------------------|------------|-------|------------|------|-------------------------------------------------------------------|
1|SIMPLE |r |range |PRIMARY,ix_uid,ix_bin_chr |ix_bin_chr |19 | |494112|Using index condition; Using where; Using temporary; Using filesort|
1|SIMPLE |h |ref |ix_join |ix_join |4 |hg38.r.uid | 1|Using index condition |
1|SIMPLE |so |eq_ref|PRIMARY,ix_uid |PRIMARY |4 |hg38.h.soID | 1| |
1|SIMPLE |e |eq_ref|PRIMARY,ix_uid |PRIMARY |4 |hg38.h.eID | 1| |
1|SIMPLE |sa |eq_ref|PRIMARY,ix_uid |PRIMARY |4 |hg38.h.saID | 1| |
>desc h
Field |Type |Null|Key|Default|Extra |
------------|----------------|----|---|-------|--------------|
uid |int(10) unsigned|NO |PRI| |auto_increment|
type |varchar(25) |NO | | | |
score |float |YES | | | |
peak |int(11) |YES | | | |
rID |int(10) unsigned|NO |MUL| | |
saID |int(10) unsigned|NO |MUL| | |
eID |int(10) unsigned|NO |MUL| | |
soID |int(10) unsigned|NO |MUL| | |
Table |Non_unique|Key_name |Seq_in_index|Column_name |Collation|Cardinality|Sub_part|Packed|Null|Index_type|
------|----------|------------|------------|------------|---------|-----------|--------|------|----|----------|
h | 0|PRIMARY | 1|uid |A | 162690954| | | |BTREE |
h | 1|ix_join | 1|rID |A | 162690954| | | |BTREE |
h | 1|ix_join | 2|saID |A | 162690954| | | |BTREE |
h | 1|ix_join | 3|eID |A | 162690954| | | |BTREE |
h | 1|ix_join | 4|so ID |A | 162690954| | | |BTREE |
>desc r
Field|Type |Null|Key|Default|Extra |
-----|--------------------|----|---|-------|--------------|
uid |int(10) unsigned |NO |PRI| |auto_increment|
bin |smallint(5) unsigned|NO |MUL| | |
chr |varchar(5) |NO |MUL| | |
r1 |int(10) unsigned |NO | | | |
r2 |int(10) unsigned |NO | | | |
Table |Non_unique|Key_name |Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|
-------|----------|------------|------------|-----------|---------|-----------|--------|------|----|----------|
r | 0|PRIMARY | 1|uid |A | 217539807| | | |BTREE ||
r | 1|ix_uid | 1|uid |A | 217539807| | | |BTREE |
r | 1|ix_bin_chr | 1|bin |A | 18| | | |BTREE |
r | 1|ix_bin_chr | 2|chr |A | 103491| | | |BTREE |
>desc sa
Field |Type |Null|Key|Default|Extra |
---------|--------------------|----|---|-------|--------------|
uid |int(10) unsigned |NO |PRI| |auto_increment|
name |varchar(250) |NO |MUL| | |
Table |Non_unique|Key_name |Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|
-------|----------|-----------------------------|------------|-----------|---------|-----------|--------|------|----|----------|
sa | 0|PRIMARY | 1|uid |A | 574| | | |BTREE |
sa | 1|ix_name | 1|name |A | 574| | | |BTREE |
>desc so
Field |Type |Null|Key|Default|Extra |
-------------------|----------------|----|---|-------|--------------|
uid |int(10) unsigned|NO |PRI| |auto_increment|
name |varchar(250) |NO |MUL| | |
so_metadata |varchar(250) |YES | | | |
metadata_descriptor|varchar(250) |YES | | | |
url |varchar(250) |YES | | | |
insert_date |datetime |NO | | | |
Table |Non_unique|Key_name|Seq_in_index|Column_name |Collation|Cardinality|Sub_part|Packed|Null|Index_type|
-------|----------|--------|------------|-------------------|---------|-----------|--------|------|----|----------|
so | 0|PRIMARY | 1|uid |A | 2161| | | |BTREE |
so | 1|ix_name | 1|name |A | 24| | | |BTREE |
>desc e
Field |Type |Null|Key|Default|Extra |
-------------------|----------------|----|---|-------|--------------|
uid |int(10) unsigned|NO |PRI| |auto_increment|
name |varchar(250) |NO |MUL| | |
e_metadata |varchar(250) |YES | | | |
metadata_descriptor|varchar(250) |YES | | | |
Table |Non_unique|Key_name|Seq_in_index|Column_name |Collation|Cardinality|Sub_part|Packed|Null|Index_type|
-----------|----------|--------|------------|-------------------|---------|-----------|--------|------|----|----------|
e | 0|PRIMARY | 1|uid |A | 6| | | |BTREE |
e | 1|ix_name | 1|name |A | 6| | | |BTREE |
解决方案
推荐阅读
- typescript - 根据下拉选择值显示过滤器
- sql - 仅更改 sql 记录的日期部分
- html - 如何在css中选择tspecific类的最后一个孩子
- java - 从 Struts JSP 中的会话中获取 Map 值
- java - 如何参考尚未持久化子对象的子对象（Objectify）首先保存父实体？
- node.js - 天蓝色 DevOps 管道打开的文件太多
- java - 我必须启动 startSmsRetriever 多少次才能使用 SMS Retriever API 多次检索 SMS？
- c# - 如何在运行时读取 csproj xml 元素？
- angular - 业力显示此错误 TypeError: Cannot read property 'textContent' of undefined
- r - 如何在搜索最大值和最小值时排除 0？