Mysql in查询慢优化方法

表总共记录800w:

           mysql中in集合中只有20个左右,查询时间大约6s,无法走索引

           例如:

SELECT
 sum(num)
FROM
 `****`
WHERE
 `create_time` >= 1584892800
AND `create_time` < 1584979200
AND `source` = 5
AND `uid` IN (
 23, 24, 26, 29, 32, 33, 38, 42, 52, 59, 68, 69, 71, 72, 73, 79, 83,
 85, 108, 111, 139, 229, 261, 280, 281, 283, 296, 298, 308, 401, 423,
 490, 523, 650, 653, 776, 903, 913, 966, 997, 1030, 1381, 1704, 1809)
AND `coin_id` = 1
LIMIT 1

原因1:in中结合条件太多,mysql认为全表检索比索引要快,就不走索引了;

原因2:mysql参数eq_range_index_dive_limit设置太小,导致in不走索引

解决方案:

1.语句强制走索引,tp5可以db('*** force index(idx_x5)')即可强制走索引

SELECT
 sum(num)
FROM
 `****` force index(idx_x5)
WHERE
 `create_time` >= 1584892800
AND `create_time` < 1584979200
AND `source` = 5
AND `uid` IN (
 23, 24, 26, 29, 32, 33, 38, 42, 52, 59, 68, 69, 71, 72, 73, 79, 83,
 85, 108, 111, 139, 229, 261, 280, 281, 283, 296, 298, 308, 401, 423,
 490, 523, 650, 653, 776, 903, 913, 966, 997, 1030, 1381, 1704, 1809)
AND `coin_id` = 1
LIMIT 1

2.eq_range_index_dive_limit参数调大到200,sql即可自动走索引,不用强制