[求优化] 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

[ConradG]

user_info 的 city_id……索引失效概率不低。

[yaocai321]

那么多人建议 explain xxx 你怎么就是不听呢

[ElmerZhang]

用 force index 强制使用 city_id 那个索引

SELECT * FROM `user_info` FORCE INDEX(idx_city_id) 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

把 idx_city_id 换成你实际的 city_id 那个索引的名字

[GM]

@yaocai321 估计不懂什么叫 explain

[zhou451971886]

试试关闭 ICP 再查询

SET [GLOBAL] optimizer_switch='index_condition_pushdown=off';

[Aluhao]

估计是这个 ORDER BY `id` DESC 比较耗时，可以取出数据在程序上进行排序。

[wangyzj]

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) ) ORDER BY `id` DESC

试一下这句

[ConradG]

这个大概率不是语句的问题，而是数据分布的问题。
cityId 通常是一个百到千级的数据集，而且分布上往往集中于特定的小几十个 key 下。user 表 LZ 说是百万级，那么相比下这个索引的效果本身就很有限。在分布较为平均的情况下预期查出数据都是 cityId 数 × 10000 级别的。再加上后边 delete_time 判 null 必须遍历，根据 id 倒排又是大概率不被优化直接遍历，不慢才是怪事。

[aliipay]

@iyaozhen 这牙膏半天挤不完，哈哈哈

[lasuar]

可以把 `show create table user_info` 和 explain 结果贴一下

[JaguarJack]

你检索了多少行呢？很可能是 city id 占了太多，虽然走了索引，但还是检索了全表行数。你可以把 in 分批次检索。

[Heebe]

其实这也是业务硬伤，这相当于查询到所有城市的数据之后，然后再进行一次 delete_time is null 的遍历，数据量大了，慢是肯定的。

我建议做 4 点，
1，依据 city_id 拆表（垂直水平都行，百万级别我建议 range 分区就行）
2，拆分 SQL 语句，根据分区分表特征，多次查询后合并
3，考虑 BY `id` DESC 是否有存在的必要性，假如本身就是 ID 插入的，这里其实可以忽略
4，delete_time is null，永远都比不上 isDelete = 1 的时候来得快

[jss]

@ElmerZhang 非常感谢，使用 force index 后 秒查数据

[dog82]

不要 select * 会回表，效率一下就差了很多；
order by id 也严重影响效率。
如果要优化建联合索引吧。
要看查询的结果集大概多少条？超过 5%还是走全表扫描吧

[jss]

@Heebe 的确，BY `id` DESC 某些时候不但多余，还影响性能; delete_time is null 我也发现了 值为 0 或 null 比 1 或-1 慢很多

[jss]

@JaguarJack 发现了，在 IN 查询时并没有走索引

[yourssheng]

@jss explain 一下就知道咯。

[barbery]

@jss 那就肯定是 order by 影响了 mysql 的优化器，导致没有走 city id 的索引，应该走了主键 id 了，试试 order by id+0

[telami]

让你贴 [可以把 `show create table user_info` 和 explain 结果贴一下] ，就贴下，在这逗人玩呢啊

[yujieyu7]

不上表结构和 explain 语句,这是要人盲猜啊🤦‍♂️