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

[pushback]

@jss 默认就是 by id，null 也是要占用内存的，建议设置默认值吧，如果 city_id 有索引，也就是 orderby 受影响了吧，sql 调优我习惯
select [query column] from table left join foreign_table on [foreign key] where column = [column] order by [order column] 上面 4 个影响点去调试 ，建议 lz 多调调

[hauzi]

范围查询一般都不走索引的

[jss]

@telami 已经处理好了，谢谢

[jss]

@pushback 感谢，指教...

[owenliang]

force index 不建议使用，使用 use index

[bfqymmt]

学习到了。

[encro]

这个其实很难优化到毫秒级的。
去掉 delete_time，加 city_id,id 组合索引还可以达到 ms 级别，
但是如果翻页到 100 页之后也是超过 ms 级别了吧，
不知道楼主要求是 ms 还是 s 级别。

[jss]

@owenliang USE INDEX 感觉比 FORCE INDEX 更智能一些，但是像我这种 IN 查询 是不走索引，经测试： 使用 force index 耗时 0.038s ； 使用 use index 耗时 9.969s 。

[jss]

@encro 其他的不知道，我这问题 使用 force index 分页到 116 页 要 656ms

[fareware]

百万数据对 Mysql 来说也不大，慢查询大多源自索引。如 city_id 有索引还慢，肯定索引失效。
1. 这里索引失效最大可能是 city_id 区分度过低，类比性别字段，如结果集超过总的 30%（大约），Mysql 会放弃索引走全表扫描，因为非聚簇索引需要回表。使用 force 可以解决但不优雅。
2. order by id 存在 filesort, 需进行全字段或 rowid 排序，避免方法是放弃或根据其他字段且建立联合索引排序，如联合索引包括 city_id 和排序字段，会用到覆盖索引避免回表且无需使用 force，如联合索引还包括 where 条件，会用到索引下推。这应该就是理想情况了。

[lasuar]

```
CREATE TABLE IF NOT EXISTS million_user_info (
id bigint(7) PRIMARY KEY AUTO_INCREMENT,
city_id int,
delete_time TIMESTAMP,
others VARCHAR(111) DEFAULT "" NOT NULL
);
CREATE INDEX idx_city_id ON million_user_info (city_id,id);（无独立 city_id 索引）
```
模拟环境：
125 万条数据；插入的 city_id 在楼主给出的 city_id 范围内，others 为干扰随机 str
查询语句：
```
SELECT * FROM `million_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 `million_user_info`.`delete_time` IS NULL ORDER BY `id` DESC LIMIT 1000;
```
测试结果：
当 delete_time=null 的行数实际只有 1 行时，秒查；
当 delete_time=null 的行数实际有 10w 行时，且 SQL 无 limit，耗费 11s ；
当 delete_time=null 的行数实际有 10w 行时，且 SQL 加 limit 1000，秒查；
当 delete_time=null 的行数实际有 10w 行时，且 SQL 包含 force index，无 limit，耗费 15.9s ；
当 delete_time=null 的行数实际有 10w 行时，且 SQL 包含 force index，加 limit 1000，耗费 4.9s ；
加上单独的 city_id 索引后，上述测试结果无明显变化。

[Leigg]

@lasuar mysql5.7

[encro]

@lasuar

作者实际 delete_time=null 应该接近 100%（不会大部分用户被删除了吧），
所以我前面说很难。
除非去掉 AND `million_user_info`.`delete_time` IS NULL 这个条件。

秒查也要看是 s 还是 ms，
差别可大，一个可能走磁盘占 iops，一个走内存。

[encro]

40 楼提到 Explain 才是解决这类问题的正确办法。

[lasuar]

@Leigg 是的

[lasuar]

@encro [环境：mysql 5.7.29 ，测试机远程连接云主机上的 db]
反复测试后的结果显示，IN 后面跟超过 1 个元素就不会走任何索引，加上 order by id 才走了主键索引，走主键索引比加上 force index(idx_city_id)要快很多，后者 explain 的结果显示走的 idx_city_id 索引，rows 为表总行数的两倍(why?)。另外加了 delete_time is null 也是会走主键索引，记得加上 limit 。
（我分别把测试表中的 delete_time is null 行数调整为 0/10w/all，与楼主同样的 SQL+limit 10000 都只需要 0.Xs ，楼主执行的 SQL 是不含 limit 的吗？如果不含 limit，我这里的执行时间超 1min，不可能在生产环境不加 limit 吧。）

[luchuxue110]

马克

[jss]

@lasuar limit 要加的

[zhangysh1995]

@zhou451971886 为什么要关闭这个优化？

[zhangysh1995]

不一定需要 `FORCE INDEX`，尝试一下 `USE INDEX`。https://dev.mysql.com/doc/refman/8.0/en/index-hints.html