查询速度非常慢，有“喜欢”和“订单”

Question

我有以下问题：

SELECT  k from 
tbl t
WHERE 
(LOWER( col1 ) LIKE '9000000003%')
AND 
(col2=12334534 )
order by  t.col1  ASC
LIMIT 0,50

tbl大约有187000行

当我把WHERE和ORDER BY放在一起时，这个查询非常慢(我不能等到它完成)。但是，如果删除ORDER BY或LIKE条件查询，则在可行的时间内运行。

当我将查询分成两个查询时，这个查询也执行得相当快：

SELECT k from (
select * from 
tbl t
WHERE 
(LOWER( col1 ) LIKE '9000000003%')
AND 
(col2=12334534 )
) x
order by x.col1  ASC
LIMIT 0,50

主要问题是查询是生成的，所以我不能重写它。

我如何优化它？

解释计划

id   select_type  table   type   possible_keys    key       key_len   ref  rows   extra
'1', 'SIMPLE',    'tbl', 'index',     NULL,     'col1_idx', '768',    NULL, '50', 'Using where'

DDL

field  type         null    key   default extra
k      bigint(20)   NO      PRI     
col1   varchar(255) YES     MUL     
col2   bigint(20)   NO          

PS我完全可以访问mysql服务器(我使用5.6)

Answer 1

这是您的查询：

SELECT k
from  tbl t
WHERE LOWER( col1 ) LIKE '9000000003%' AND 
      col2 = 12334534
order by t.col1 ASC
LIMIT 0, 50;

lower()完全没有必要，因为模式只有数字(加上默认的排序规则对like不区分大小写)。因此，您可以这样重写查询：

SELECT k
from  tbl t
WHERE col1 LIKE '9000000003%' AND 
      col2 = 12334534
order by t.col1 ASC
LIMIT 0, 50;

然后，可以使用索引来加快查询速度。最好的指标是：

create index tbl_col2_col1_k on tbl(col2, col1, k);

这将使查询速度惊人地快。