mysql - 使用 like 和 order by 查询非常慢

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);

这应该会使查询变得非常快。