查询效率的奇怪问题！

Question

cDNA_map:
+----+------------+---------+---------+-------------+--------+----------+
| id | cluster_id | read_id | cDNA_id | cDNA_offset | strand | mismatch |
+----+------------+---------+---------+-------------+--------+----------+
|  1 |          1 |       1 |       5 |        3129 |      1 |        0 | 
|  2 |          2 |       3 |       7 |         264 |      1 |        0 | 
|  3 |          3 |       2 |       7 |        1013 |      1 |        0 | 
|  4 |          4 |       4 |       7 |        1200 |      1 |        0 | 
|  5 |          5 |       5 |       7 |        1247 |      1 |        0 | 
+----+------------+---------+---------+-------------+--------+----------+
reads_info:
+----+--------------------+-------------------------------------+---------+
| id | name               | seq                                 | quality |
+----+--------------------+-------------------------------------+---------+
|  1 | read1|cds5|(27686. | GGGATCACTTGATATTACAGAAAACCAGCTATCAT | NULL    | 
|  2 | read2|cds7|(34956. | GCTCTTCTCCTCCAAGTATAACTGCCATTGCCGCT | NULL    | 
|  3 | read3|cds8|(35879. | CATAGAAATTCCGCCTCCTCGTCCTAAACGAAAAC | NULL    | 
|  4 | read4|cds9|(34769. | TGAGAAACAAAACACAGCTCTGCAAGATCAAAACT | NULL    | 
|  5 | read5|cds10|(34722 | CTCCAGCTTCATCATCTGATGATTCAGATGAGACT | NULL    | 
+----+--------------------+-------------------------------------+---------+查询语句：
select a.cluster_id,b.name,a.cDNA_offset,a.cDNA_offset+34,a.strand,a.mismatch from cDNA_map a inner join reads_info b on a.read_id=b.id where a.cDNA_id=14;
其中 reads_info 有 600万行， cDNA_map 有380万行， cDNA_map 上id 主键，cDNA_id索引 ，    reads_info 上id 主键 ，   查询极慢。就算是当 a.cDNA_id=14  时候a 表 只有 800行 inner join b表 都极慢。（一般情况下是 某个a.cDNA_id下 会有30000行 和 b表 inner join）
但是 另外有一中情况： reads_info 有 100万行， cDNA_map 有80万行，索引一样，  当a.cDNA_id=14时候 a表有 2000行 ，inner join b表 也算快了。

Answer 1

查询 主要是 在 cDNA_map 里面 找到 指定 cDNA_id 的行 ，用到 reads_info 表 只是 找到cDNA_map里面对应read_id  在reads_info里面的 name。

Answer 2

执行 explain select a.cluster_id,b.name,a.cDNA_offset,a.cDNA_offset+34,a.strand,a.mismatch from cDNA_map a inner join reads_info b on a.read_id=b.id where a.cDNA_id=14
看看是否用到索引。查询慢一般都是没有用得索引引起的

Answer 3

cDNA_map  的  read_id ，cDNA_id 建了索引，  reads_info id 是主键。 但还是慢！ 怎么回事？

Answer 4

mysql> explain select a.cluster_id,b.name,a.cDNA_offset,a.cDNA_offset+34,a.strand,a.mismatch from cDNA_map a inner join reads_info b on a.read_id=b.id where a.cDNA_id=14;
+----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows  | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------+
|  1 | SIMPLE      | a     | ref    | cDNA2,cDNA3   | cDNA2   | 4       | const                | 17183 |       | 
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | hongshu_68.a.read_id |     1 |       | 
+----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------+
2 rows in set (0.63 sec)索引是这样的：
create index cDNA2 on cDNA_map(cDNA_id);
create index cDNA3 on cDNA_map(read_id);

Answer 5

row: 17183 一下取这么多数据，当然慢了。而且在大的ＳＱＬ中还要做运算（a.cDNA_offset+34）。这样当然慢了。在添加一个索引：create index cDNA3 on cDNA_map(read_id,cDNA_id); 
试一下。

Answer 6

去除 +35 计算了 ，而且 也 建立了 cDNA_map(read_id,cDNA_id) 索引  ，还是慢，要10分钟以上，（我等了10分钟，强行关掉了）

Answer 7

不应该啊，把其他的索引删掉。cDNA_map(read_id,cDNA_id)保留。然后explain看结果是否用到那个key了

Answer 8

select a.cluster_id,b.name,a.cDNA_offset,a.cDNA_offset+34,a.strand,a.mismatch from (select * from cDNA_map where a.cDNA_id=14) a left join reads_info b on a.read_id=b.id;

Answer 9

mysql> explain select a.cluster_id,b.name,a.cDNA_offset,a.cDNA_offset+34,a.strand,a.mismatch from cDNA_map a inner join reads_info b on a.read_id=b.id where a.cDNA_id=14;
+----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------+
¦ id ¦ select_type ¦ table ¦ type  ¦ possible_keys ¦ key    ¦ key_len ¦ ref                  ¦ rows  ¦ Extra ¦
+----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------+
¦  1 ¦ SIMPLE      ¦ a    ¦ ref    ¦ cDNA2,cDNA3  ¦ cDNA2  ¦ 4      ¦ const                ¦ 17183 ¦      ¦
¦  1 ¦ SIMPLE      ¦ b    ¦ eq_ref ¦ PRIMARY      ¦ PRIMARY ¦ 4      ¦ hongshu_68.a.read_id ¦    1 ¦      ¦
+----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------+
2 rows in set (0.63 sec) explaine
就是这样 。 只用到 cDNA2 索引