mysql - 加速查询 : ORDER BY with LIMIT (indexing? )

Question

我正在尝试根据给定的位置信息从基因表中找到最接近的基因。这是一个例子:

SELECT chrom, txStart, txEnd, name2, strand FROM
wgEncodeGencodeCompV12 WHERE chrom = 'chr1' AND txStart < 713885 AND
strand = '+' ORDER BY txStart DESC LIMIT 1;

我的测试运行速度很慢，这是有问题的。

这是一个 EXPLAIN使用默认索引的输出(通过 chrom ):

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | wgEncodeGencodeCompV12 | ref | chrom | chrom | 257 | const | 15843 | Using where; Using filesort |

使用了 Filesort 并且可能导致所有的迟缓？

我尝试通过索引 (chrom, txStart, strand) 来加快排序速度, 或者只是 txStart一个人，但它只会变慢(？)。我的推理是 txStart选择性不足以成为一个好的索引，在这种情况下全表扫描实际上更快吗？

这是 EXPLAIN带有附加索引的输出:

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | wgEncodeGencodeCompV12 | range | chrom,closest_gene_lookup | closest_gene_lookup | 261 | NULL | 57 | Using where |

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | wgEncodeGencodeCompV12 | range | chrom,txStart | txStart | 4 | NULL | 1571 | Using where |

表结构

CREATE TABLEwgEncodeGencodeCompV12(<br/>
binsmallint(5) unsigned NOT NULL,<br/>
namevarchar(255) NOT NULL,<br/>
chromvarchar(255) NOT NULL,<br/>
strandchar(1) NOT NULL,<br/>
txStartint(10) unsigned NOT NULL,<br/>
txEndint(10) unsigned NOT NULL,<br/>
cdsStartint(10) unsigned NOT NULL,<br/>
cdsEndint(10) unsigned NOT NULL,<br/>
exonCountint(10) unsigned NOT NULL,<br/>
exonStartslongblob NOT NULL,<br/>
exonEndslongblob NOT NULL,<br/>
scoreint(11) default NULL,<br/>
name2varchar(255) NOT NULL,<br/>
cdsStartStatenum('none','unk','incmpl','cmpl') NOT NULL,<br/>
cdsEndStatenum('none','unk','incmpl','cmpl') NOT NULL,<br/>
exonFrameslongblob NOT NULL,<br/>
KEYchrom(chrom,bin),<br/>
KEYname(name),<br/>
KEYname2(name2)<br/>
)

有没有办法提高效率？感谢您的宝贵时间!

(更新)解决方案:结合两个评论者的建议显着缩短了运行时间。

Answer 1

在您的情况下(在单个表上查询，没有连接，没有复杂的东西)了解每列中值的分布以及数据库服务器如何利用索引很重要。当您有一个字段具有相当大范围的不同值时，那么应该使用该字段进行索引。 (例如，strand 上的索引只会拆分 + 或 - 中的整个数据，下游过滤器必须处理 + 或 - 结果集中的每一行，这接近最坏的情况)

到目前为止，我们知道 txStart在查询的有趣列中具有最差异化的值分布。

因此，您的查询绝对应该在该列上使用索引查询!但是 btree 索引，而不是哈希索引(运算符 < 、 <= 、 > 等在 btree 上很快，但在哈希上不是)。

在 txStart 上仅使用一个 (btree) 索引再试一次(我知道你已经试过了，但请再试一次，避免所有二级索引等。)

多列索引很好，但它们的复杂性使它们不如普通的单列索引快，MySQL 的优化器在选择最佳索引时相当愚蠢;-)

另一个重要因素可能是动态行大小(因为使用了 longblob 列)。但在这方面，我并不了解 MySQL 的当前状态。