[求优化] mysql 百万数据 IN 查询

[jss]

##mysql 城市 IN 查询超时

MySql 代码

SELECT * FROM `user_info` WHERE (  `city_id` IN (45757,45967,46044,46126,46288,46473,46642,46769,46919,47078,47119,45758,45762,45786,45811,45822,45839,45850,45870,45877,45892,45905) ) AND `user_info`.`delete_time` IS NULL ORDER BY `id` DESC

[zhangysh1995]

@hauzi `范围查询一般都不走索引的`，这是从经验来的嘛？我看文档 https://dev.mysql.com/doc/refman/5.6/en/range-optimization.html 说的是 indexed key 都会做优化？

[kanepan19]

@zhangysh1995
我这边的场景每天 200 万， 查询 2 天 用 USE INDEX 就全表扫描了，必须 force index

[zhangysh1995]

@kanepan19 这样的啊，学习了。谢谢回复！

[qwwe01]

挖坟。。。最近再找类似问题发现有这么个 BUG
https://bugs.mysql.com/bug.php?id=97001

There is an additional case of the same poor optimization, if queries with large IN() lists are used, however I've not been able to generate a reproducible test case that does not require production data and circumstances. The query could be: