V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
jss
V2EX  ›  程序员

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

  •  2
     
  •   jss · 2020-05-06 10:54:23 +08:00 · 7336 次点击
    这是一个创建于 1644 天前的主题,其中的信息可能已经有所发展或是发生改变。

    ##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
    
    第 1 条附言  ·  2020-05-06 13:17:00 +08:00

    感谢 @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
    
    64 条回复    2022-09-13 20:49:41 +08:00
    barbery
        1
    barbery  
       2020-05-06 11:01:17 +08:00
    这应该性能没啥问题吧 你确定走了 city_id 这个索引了吗?
    airfling
        2
    airfling  
       2020-05-06 11:10:05 +08:00   ❤️ 1
    city_id 索引建了没,想排序不要直接 select *,应该 selelct id,然后再根据 id 去 select *
    justfindu
        3
    justfindu  
       2020-05-06 11:10:41 +08:00
    这么点数据 in 真的毫无压力, 当然前提是 mysql 5.7 及以上.
    nita22
        4
    nita22  
       2020-05-06 11:14:55 +08:00
    city_id 建索引,select * 如果可以的话就改为覆盖索引。explain 看看实际有没有用上
    jss
        5
    jss  
    OP
       2020-05-06 11:17:15 +08:00
    @airfling 有建 city_id 索引
    jss
        6
    jss  
    OP
       2020-05-06 11:17:25 +08:00
    @barbery 有建 city_id 索引
    jsrgjcy1
        7
    jsrgjcy1  
       2020-05-06 11:20:48 +08:00
    semi-join
    yourssheng
        8
    yourssheng  
       2020-05-06 11:21:11 +08:00
    你 order by id 了。mysql 会 select 出来结果然后排序,如果 select 出来的结果很多还用到外部排序会很慢。
    nita22
        9
    nita22  
       2020-05-06 11:21:27 +08:00
    @jss 看下 city.id 是不是 varchar 类型,自动类型转换也会让索引用不上。具体还是 explain 看下
    zy445566
        10
    zy445566  
       2020-05-06 11:21:53 +08:00
    你这表多大,百万级别应该毫无压力才对,否则就是你服务器垃圾
    iyaozhen
        11
    iyaozhen  
       2020-05-06 11:23:03 +08:00
    口说无凭 explain xxx
    show create table user_info
    HunterPan
        12
    HunterPan  
       2020-05-06 11:23:08 +08:00
    符合数据的条数如果过多,可以分页搞
    jss
        13
    jss  
    OP
       2020-05-06 11:24:39 +08:00
    @nita22 有建 city_id 索引 ,possible_keys 中 有 city_id
    jss
        14
    jss  
    OP
       2020-05-06 11:26:21 +08:00
    @nita22 city_id 类型是 int, explain 看了 possible_keys 中 有 city_id
    mccreefei
        15
    mccreefei  
       2020-05-06 11:26:50 +08:00
    看下 explain 是不是 Using filesort,是的话建议使用(city_id, id)联合索引
    nita22
        16
    nita22  
       2020-05-06 11:27:34 +08:00
    @jss 实际用到的索引是看的 key 吧,还得看 Extra 里面的内容
    jss
        17
    jss  
    OP
       2020-05-06 11:28:37 +08:00
    @HunterPan 加了 LIMIT 0,20 还是一样
    jss
        18
    jss  
    OP
       2020-05-06 11:30:01 +08:00
    @mccreefei 是:Using index condition; Using where
    rrfeng
        19
    rrfeng  
       2020-05-06 11:36:28 +08:00
    直接贴出来,一段一段发干毛
    jss
        20
    jss  
    OP
       2020-05-06 11:41:31 +08:00
    @yourssheng 当 IN 城市数少于 20 个 去掉排序效果 明显 ,但是,我有 50 个以上 city_id 时 一样超时
    ConradG
        21
    ConradG  
       2020-05-06 11:43:35 +08:00
    user_info 的 city_id……索引失效概率不低。
    yaocai321
        22
    yaocai321  
       2020-05-06 11:46:29 +08:00
    那么多人建议 explain xxx 你怎么就是不听呢
    ElmerZhang
        23
    ElmerZhang  
       2020-05-06 11:47:57 +08:00   ❤️ 2
    用 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
        24
    GM  
       2020-05-06 11:50:31 +08:00
    @yaocai321 估计不懂什么叫 explain
    zhou451971886
        25
    zhou451971886  
       2020-05-06 12:02:06 +08:00
    试试关闭 ICP 再查询

    SET [GLOBAL] optimizer_switch='index_condition_pushdown=off';
    Aluhao
        26
    Aluhao  
       2020-05-06 12:05:34 +08:00
    估计是这个 ORDER BY `id` DESC 比较耗时,可以取出数据在程序上进行排序。
    wangyzj
        27
    wangyzj  
       2020-05-06 12:10:01 +08:00
    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
        28
    ConradG  
       2020-05-06 12:30:31 +08:00
    这个大概率不是语句的问题,而是数据分布的问题。
    cityId 通常是一个百到千级的数据集,而且分布上往往集中于特定的小几十个 key 下。user 表 LZ 说是百万级,那么相比下这个索引的效果本身就很有限。在分布较为平均的情况下预期查出数据都是 cityId 数 × 10000 级别的。再加上后边 delete_time 判 null 必须遍历,根据 id 倒排又是大概率不被优化直接遍历,不慢才是怪事。
    aliipay
        29
    aliipay  
       2020-05-06 12:37:27 +08:00
    @iyaozhen 这牙膏半天挤不完,哈哈哈
    lasuar
        30
    lasuar  
       2020-05-06 12:45:46 +08:00
    可以把 `show create table user_info` 和 explain 结果贴一下
    JaguarJack
        31
    JaguarJack  
       2020-05-06 12:46:57 +08:00 via iPhone
    你检索了多少行呢?很可能是 city id 占了太多,虽然走了索引,但还是检索了全表行数。你可以把 in 分批次检索。
    Heebe
        32
    Heebe  
       2020-05-06 13:03:33 +08:00
    其实这也是业务硬伤,这相当于查询到所有城市的数据之后,然后再进行一次 delete_time is null 的遍历,数据量大了,慢是肯定的。

    我建议做 4 点,
    1,依据 city_id 拆表(垂直水平都行,百万级别我建议 range 分区就行)
    2,拆分 SQL 语句,根据分区分表特征,多次查询后合并
    3,考虑 BY `id` DESC 是否有存在的必要性,假如本身就是 ID 插入的,这里其实可以忽略
    4,delete_time is null,永远都比不上 isDelete = 1 的时候来得快
    jss
        33
    jss  
    OP
       2020-05-06 13:14:17 +08:00
    @ElmerZhang 非常感谢,使用 force index 后 秒查数据
    dog82
        34
    dog82  
       2020-05-06 13:17:15 +08:00
    不要 select * 会回表,效率一下就差了很多;
    order by id 也严重影响效率。
    如果要优化建联合索引吧。
    要看查询的结果集大概多少条?超过 5%还是走全表扫描吧
    jss
        35
    jss  
    OP
       2020-05-06 13:31:53 +08:00
    @Heebe 的确,BY `id` DESC 某些时候不但多余,还影响性能; delete_time is null 我也发现了 值为 0 或 null 比 1 或-1 慢很多
    jss
        36
    jss  
    OP
       2020-05-06 13:33:57 +08:00
    @JaguarJack 发现了,在 IN 查询时并没有走索引
    yourssheng
        37
    yourssheng  
       2020-05-06 14:37:27 +08:00
    @jss explain 一下就知道咯。
    barbery
        38
    barbery  
       2020-05-06 15:08:32 +08:00
    @jss 那就肯定是 order by 影响了 mysql 的优化器,导致没有走 city id 的索引,应该走了主键 id 了,试试 order by id+0
    telami
        39
    telami  
       2020-05-06 15:14:31 +08:00
    让你贴 [可以把 `show create table user_info` 和 explain 结果贴一下] ,就贴下,在这逗人玩呢啊
    yujieyu7
        40
    yujieyu7  
       2020-05-06 15:26:05 +08:00   ❤️ 1
    不上表结构和 explain 语句,这是要人盲猜啊🤦‍♂️
    pushback
        41
    pushback  
       2020-05-06 15:34:45 +08:00
    @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
        42
    hauzi  
       2020-05-06 15:36:24 +08:00
    范围查询一般都不走索引的
    jss
        43
    jss  
    OP
       2020-05-06 15:37:54 +08:00
    @telami 已经处理好了,谢谢
    jss
        44
    jss  
    OP
       2020-05-06 15:38:27 +08:00
    @pushback 感谢,指教...
    owenliang
        45
    owenliang  
       2020-05-06 16:49:29 +08:00
    force index 不建议使用,使用 use index
    bfqymmt
        46
    bfqymmt  
       2020-05-06 17:01:47 +08:00
    学习到了。
    encro
        47
    encro  
       2020-05-06 17:04:22 +08:00
    这个其实很难优化到毫秒级的。
    去掉 delete_time,加 city_id,id 组合索引还可以达到 ms 级别,
    但是如果翻页到 100 页之后也是超过 ms 级别了吧,
    不知道楼主要求是 ms 还是 s 级别。
    jss
        48
    jss  
    OP
       2020-05-06 17:07:36 +08:00
    @owenliang USE INDEX 感觉比 FORCE INDEX 更智能一些,但是像我这种 IN 查询 是不走索引,经测试: 使用 force index 耗时 0.038s ; 使用 use index 耗时 9.969s 。
    jss
        49
    jss  
    OP
       2020-05-06 17:11:30 +08:00
    @encro 其他的不知道,我这问题 使用 force index 分页到 116 页 要 656ms
    fareware
        50
    fareware  
       2020-05-06 18:05:19 +08:00   ❤️ 2
    百万数据对 Mysql 来说也不大,慢查询大多源自索引。如 city_id 有索引还慢,肯定索引失效。
    1. 这里索引失效最大可能是 city_id 区分度过低,类比性别字段,如结果集超过总的 30%(大约),Mysql 会放弃索引走全表扫描,因为非聚簇索引需要回表。使用 force 可以解决但不优雅。
    2. order by id 存在 filesort, 需进行全字段或 rowid 排序,避免方法是放弃或根据其他字段且建立联合索引排序,如联合索引包括 city_id 和排序字段,会用到覆盖索引避免回表且无需使用 force,如联合索引还包括 where 条件,会用到索引下推。这应该就是理想情况了。
    lasuar
        51
    lasuar  
       2020-05-06 19:11:49 +08:00
    ```
    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
        52
    Leigg  
       2020-05-06 19:37:31 +08:00 via Android
    @lasuar mysql5.7
    encro
        53
    encro  
       2020-05-06 19:52:58 +08:00
    @lasuar

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

    秒查也要看是 s 还是 ms,
    差别可大,一个可能走磁盘占 iops,一个走内存。
    encro
        54
    encro  
       2020-05-06 19:54:10 +08:00
    40 楼提到 Explain 才是解决这类问题的正确办法。
    lasuar
        55
    lasuar  
       2020-05-06 20:01:01 +08:00 via Android
    @Leigg 是的
    lasuar
        56
    lasuar  
       2020-05-07 10:28:37 +08:00
    @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
        57
    luchuxue110  
       2020-05-07 10:34:39 +08:00
    马克
    jss
        58
    jss  
    OP
       2020-05-07 11:34:28 +08:00
    @lasuar limit 要加的
    zhangysh1995
        59
    zhangysh1995  
       2020-05-07 14:16:26 +08:00
    @zhou451971886 为什么要关闭这个优化?
    zhangysh1995
        60
    zhangysh1995  
       2020-05-07 14:17:56 +08:00
    不一定需要 `FORCE INDEX`,尝试一下 `USE INDEX`。https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
    zhangysh1995
        61
    zhangysh1995  
       2020-05-07 14:23:28 +08:00
    @hauzi `范围查询一般都不走索引的`,这是从经验来的嘛?我看文档 https://dev.mysql.com/doc/refman/5.6/en/range-optimization.html 说的是 indexed key 都会做优化?
    kanepan19
        62
    kanepan19  
       2020-05-07 17:09:53 +08:00
    @zhangysh1995
    我这边的场景每天 200 万, 查询 2 天 用 USE INDEX 就全表扫描了,必须 force index
    zhangysh1995
        63
    zhangysh1995  
       2020-05-08 13:53:57 +08:00
    @kanepan19 这样的啊,学习了。谢谢回复!
    qwwe01
        64
    qwwe01  
       2022-09-13 20:49:41 +08:00
    挖坟。。。最近再找类似问题发现有这么个 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:
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1835 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 16:30 · PVG 00:30 · LAX 08:30 · JFK 11:30
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.