Вопрос по mysql – Оптимизация MySQL для таблиц соединения с критериями диапазона

3

Я собираюсь объединить две таблицы, используя одну позицию в одной таблице к диапазону (представленному двумя столбцами) в другой таблице.

Тем не менее, производительность слишком низкая, что составляет около 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 в двух таблицах. Затем выполните сравнение методом «перебор» в таблицах.

Есть ли способы сделать дальнейшую оптимизацию, например, индексацию позиции?

(It is my first time posting the question, sorry for the poor posting quality.)

@ Ilion, я пытался, нет ничего другого ... Eric Ho
Изменится ли план запроса вообще, если вы поместите раздел BETWEEN в предложение WHERE вместо того, чтобы включить его в предложение ON? (Я не ожидаю, что это улучшит ситуацию, но, кажется, стоит проверить.) Ilion

Ваш Ответ

1   ответ
0

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
)
Error: User Rate Limit Exceeded Eric Ho
Error: User Rate Limit Exceededsimilar question here.

Похожие вопросы