带范围标准的连接表上的MySQL优化

Question

我将通过在一个表中使用另一个表中的范围（由两列表示）中的单个位置来连接两个表。

但是，表现太慢了，大约20分钟。 我已经尝试在表上添加索引或更改查询。 但表现仍然不佳。

所以，我要求优化加入速度。

---

以下是对MySQL的查询。

mysql> SELECT `inVar`.chrom, `inVar`.pos, `openChrom_K562`.score 
    -> FROM `inVar` 
    -> LEFT JOIN `openChrom_K562` 
    -> ON (
    -> `inVar`.chrom=`openChrom_K562`.chrom AND 
    -> `inVar`.pos BETWEEN `openChrom_K562`.chromStart AND `openChrom_K562`.chromEnd 
    ->); 

inVar和openChrom_K562是我使用的表。

inVar存储每行中的单个位置。

openChrom_K562存储由chromStart和chromEnd指示的范围信息。

inVar包含57902行并且openChrom_K562分别具有137373行。

---

表上的字段。

mysql> DESCRIBE inVar; 
+-------+-------------+------+-----+---------+-------+ 
| Field | Type  | Null | Key | Default | Extra | 
+-------+-------------+------+-----+---------+-------+ 
| chrom | varchar(31) | NO | PRI | NULL |  | 
| pos | int(10)  | NO | PRI | NULL |  | 
+-------+-------------+------+-----+---------+-------+ 

mysql> DESCRIBE openChrom_K562; 
+------------+-------------+------+-----+---------+-------+ 
| Field  | Type  | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| chrom  | varchar(31) | NO | MUL | NULL |  | 
| chromStart | int(10)  | NO | MUL | NULL |  | 
| chromEnd | int(10)  | NO |  | NULL |  | 
| score  | int(10)  | NO |  | NULL |  | 
+------------+-------------+------+-----+---------+-------+ 

---

指数内置表

mysql> SHOW INDEX FROM inVar; 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
| inVar |   0 | PRIMARY |   1 | chrom  | A   |  NULL |  NULL | NULL |  | BTREE  |   | 
| inVar |   0 | PRIMARY |   2 | pos   | A   |  57902 |  NULL | NULL |  | BTREE  |   | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 

mysql> SHOW INDEX FROM openChrom_K562; 
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
| openChrom_K562 |   1 | start_end |   1 | chromStart | A   |  137373 |  NULL | NULL |  | BTREE  |   | 
| openChrom_K562 |   1 | start_end |   2 | chromEnd | A   |  137373 |  NULL | NULL |  | BTREE  |   | 
| openChrom_K562 |   1 | chrom_only |   1 | chrom  | A   |   22 |  NULL | NULL |  | BTREE  |   | 
| openChrom_K562 |   1 | chrom_start |   1 | chrom  | A   |   22 |  NULL | NULL |  | BTREE  |   | 
| openChrom_K562 |   1 | chrom_start |   2 | chromStart | A   |  137373 |  NULL | NULL |  | BTREE  |   | 
| openChrom_K562 |   1 | chrom_end |   1 | chrom  | A   |   22 |  NULL | NULL |  | BTREE  |   | 
| openChrom_K562 |   1 | chrom_end |   2 | chromEnd | A   |  137373 |  NULL | NULL |  | BTREE  |   | 
+----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 

---

执行计划中关于MySQL

mysql> EXPLAIN SELECT `inVar`.chrom, `inVar`.pos, score FROM `inVar` LEFT JOIN `openChrom_K562` ON (inVar.chrom=openChrom_K562.chrom AND `inVar`.pos BETWEEN chromStart AND chromEnd); 
+----+-------------+----------------+-------+--------------------------------------------+------------+---------+-----------------+-------+-------------+ 
| id | select_type | table   | type | possible_keys        | key  | key_len | ref    | rows | Extra  | 
+----+-------------+----------------+-------+--------------------------------------------+------------+---------+-----------------+-------+-------------+ 
| 1 | SIMPLE  | inVar   | index | NULL          | PRIMARY | 37  | NULL   | 57902 | Using index | 
| 1 | SIMPLE  | openChrom_K562 | ref | start_end,chrom_only,chrom_start,chrom_end | chrom_only | 33  | tmp.inVar.chrom | 5973 |    | 
+----+-------------+----------------+-------+--------------------------------------------+------------+---------+-----------------+-------+-------------+ 

似乎只有它在两个表看chrom优化。然后在表格中进行蛮力比较。

有没有什么办法可以进一步优化如索引的位置？

（这是我第一次发布的问题，遗憾的发布质量较差。）

Answer 1

chrom_only很可能是一个坏的索引选择你的加入，你只有CHROM 22个值。

如果我已经解释这个权利，如果使用start_end

SELECT `inVar`.chrom, `inVar`.pos, `openChrom_K562`.score 
FROM `inVar` 
LEFT JOIN `openChrom_K562` 
USE INDEX (`start_end`) 
ON (
`inVar`.chrom=`openChrom_K562`.chrom AND 
`inVar`.pos BETWEEN `openChrom_K562`.chromStart AND `openChrom_K562`.chromEnd 
)