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

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 

• 資深大佬 : barbery

  这应该性能没啥问题吧 你确定走了 city_id 这个索引了吗？

• 資深大佬 : airfling

  city_id 索引建了没，想排序不要直接 select *，应该 selelct id，然后再根据 id 去 select *

• 資深大佬 : justfindu

  这么点数据 in 真的毫无压力, 当然前提是 mysql 5.7 及以上.

• 資深大佬 : nita22

  city_id 建索引，select * 如果可以的话就改为覆盖索引。explain 看看实际有没有用上

• 主 資深大佬 : jss

  @airfling 有建 city_id 索引

• 主 資深大佬 : jss

  @barbery 有建 city_id 索引

• 資深大佬 : jsrgjcy161

  semi-join

• 資深大佬 : yourssheng

  你 order by id 了。mysql 会 select 出来结果然后排序，如果 select 出来的结果很多还用到外部排序会很慢。

• 資深大佬 : nita22

  @jss 看下 city.id 是不是 varchar 类型，自动类型转换也会让索引用不上。具体还是 explain 看下

• 資深大佬 : zy445566

  你这表多大，百万级别应该毫无压力才对，否则就是你服务器垃圾

• 資深大佬 : iyaozhen

  口说无凭 explain xxx
  show create table user_info

• 資深大佬 : HunterPan

  符合数据的条数如果过多，可以分页搞

• 主 資深大佬 : jss

  @nita22 有建 city_id 索引 ，possible_keys 中 有 city_id

• 主 資深大佬 : jss

  @nita22 city_id 类型是 int, explain 看了 possible_keys 中 有 city_id

• 資深大佬 : mccreefei

  看下 explain 是不是 Using filesort，是的话建议使用(city_id, id)联合索引

• 資深大佬 : nita22

  @jss 实际用到的索引是看的 key 吧，还得看 Extra 里面的内容

• 主 資深大佬 : jss

  @HunterPan 加了 LIMIT 0,20 还是一样

• 主 資深大佬 : jss

  @mccreefei 是：Using index condition; Using where

• 資深大佬 : rrfeng

  直接贴出来，一段一段发干毛

• 主 資深大佬 : jss

  @yourssheng 当 IN 城市数少于 20 个 去掉排序效果 明显 ，但是，我有 50 个以上 city_id 时 一样超时

• 資深大佬 : 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 语句,这是要人盲猜啊

• 資深大佬 : 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

• 資深大佬 : 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 这样的啊，学习了。谢谢回复！