今天同事说mysql某表查询的非常慢,数据量79万,执行时间大约是20秒,sql发我了
select count(1) from cs_ship where deleted=1
AND appear_time >=STR_TO_DATE('2023-02-14 00:00:00',"%Y-%m-%d %H;%i:%s') AND(
mmsi shipname LIKE CONCAT('皖明光货,%) OR
shipName LIKE CONCAT('皖明光货,%') OR
shipname_updated LIKE CONCAT('皖明光货,1%)
经过我的优化后,执行速度15秒,还是不够快
select count(1) from cs_ship where deleted=1
AND appear_time >=STR_TO_DATE('2023-02-14 00:00:00','%Y-%m-%d %H;%i:%s') and
( mmsi_shipname LIKE '%皖明光货%' or
shipName LIKE '%皖明光货%' or
shipname_updated LIKE '%皖明光货%')
经过我的再次优化后,执行速度2秒,差不多了
select count(1) from (
select t.* from cs_ship t where deleted=1
AND appear_time >=STR_TO_DATE('2023-02-14','%Y-%m-%d')
) s
where
s.mmsi_shipname LIKE '皖明光货%' or
s.shipName LIKE '皖明光货%' or
s.shipname_updated LIKE '皖明光货%'
经验总结,同事虽然加了索引,但是一次查询还是慢,用了子查询之后就快了,YYDS哦