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

Mysql 只查状态字段优化

  •  
  •   marine2c · 2023-05-09 10:06:49 +08:00 · 3210 次点击
    这是一个创建于 562 天前的主题,其中的信息可能已经有所发展或是发生改变。

    如题,表有 300 万数据的样子,状态字段是 varchar 的,只有 5 个状态,只查某个状态的全部数据时很慢,状态字段加索引也没用,怎么解决

    第 1 条附言  ·  2023-05-09 16:13:39 +08:00
    感谢各位大佬,定时任务最好的方案还是加上时间范围去筛选一遍,时间是有索引的
    34 条回复    2023-05-09 19:24:24 +08:00
    brader
        1
    brader  
       2023-05-09 10:14:15 +08:00
    sql 语句试下强制指定索引
    hhjswf
        2
    hhjswf  
       2023-05-09 10:22:10 +08:00 via Android
    只有 5 个状态,你加个 der 的索引。。浪费空间,增加插入开销。索引要用在有区分度的字段。
    同步到 es 库查询?
    lookStupiToForce
        3
    lookStupiToForce  
       2023-05-09 10:22:57 +08:00
    “只有 5 个状态,只查某个状态的全部数据时很慢”
    你的意思是每个状态平均 60 万行数,然后你要一次取出这么多的数据?

    慢是当然的,mysql 的优化器要么走全表扫,要么走索引然后来 60 万次(准)随机 IO ,都会慢
    你真有这种业务需求,要么上 redis ,要么按这个状态字段建分区表,前者当然更好,后者如果你用的 ssd 性能也够用,硬盘的话,看你这 60 万行数到底占多大空间了
    xwayway
        4
    xwayway  
       2023-05-09 10:25:54 +08:00
    5 个字段,区分度并不高啊。散列散列,所以还是没懂散的意思啊。
    我猜你是不是 select * from xxx where status = 'xxx' 查询的,要不先改成 select * from xxx where id in(select id from xxx where status = 'xxxx')?
    jtwor
        5
    jtwor  
       2023-05-09 10:40:32 +08:00
    看看执行计划,先确认是否命中索引
    sujin190
        6
    sujin190  
       2023-05-09 10:41:39 +08:00
    区分度不高索引没啥用,你看下查询计划这种情况应该是不会用状态索引的,如果有 limit 限制数量且不多的情况下不会慢的,如果还慢应该是加了排序了吧,这种情况应该是排序慢,也许你需要加索引的字段是排序字段才对
    devilweime
        7
    devilweime  
       2023-05-09 10:43:54 +08:00
    让交互改下,再加个默认时间查询范围之类
    fiypig
        8
    fiypig  
       2023-05-09 10:47:29 +08:00
    看业务场景, 加时间条件
    4lieS
        9
    4lieS  
       2023-05-09 10:56:30 +08:00
    你这慢不是慢在索引,是慢在平均 60W 次的磁盘读写数据上了。
    可以尝试加上其他查询条件创建联合索引,比如(时间,状态)这样。
    spicy777
        10
    spicy777  
       2023-05-09 11:02:42 +08:00
    怎么感觉不像说的那么回事,把表结构贴下呗
    liuxu
        11
    liuxu  
       2023-05-09 11:15:52 +08:00
    5 个固定状态数量不用 varchar ,用 enum ,再检查下带宽,300 万 varchar 二级索引问题不大
    urnoob
        12
    urnoob  
       2023-05-09 11:26:18 +08:00
    尝试下建 hash 索引。这种索引类型,理论上你这列就只有五个 hash 值。
    避免全表扫描,只需要扫描只有 5 条记录的 hash 索引记录
    有尝试的话麻烦反馈下结果
    marine2c
        13
    marine2c  
    OP
       2023-05-09 11:56:45 +08:00
    @brader
    @hhjswf 状态索引没用,走的全表扫描
    marine2c
        14
    marine2c  
    OP
       2023-05-09 11:57:20 +08:00
    @xwayway 差不多是这个意思,但是你 select id from xxx where status = 'xxxx'不还是走全表扫描么
    marine2c
        15
    marine2c  
    OP
       2023-05-09 11:57:59 +08:00
    @urnoob 还没尝试,hash 索引没用过
    brader
        16
    brader  
       2023-05-09 11:59:45 +08:00
    @marine2c #13 所以我才让你强制指定索引啊,你尝试指定了没有
    marine2c
        17
    marine2c  
    OP
       2023-05-09 12:01:59 +08:00
    @brader 状态没建索引啊,这种索引建了不会被大佬屌么,哈哈
    brader
        18
    brader  
       2023-05-09 12:05:33 +08:00
    @marine2c #17 这点自主权都没有吗,你们管开发管这么死了,别人能实现需求不就行了
    opengps
        19
    opengps  
       2023-05-09 12:12:18 +08:00
    你这 5 个状态,是不是可以用 5 个表去存呢?
    seth19960929
        20
    seth19960929  
       2023-05-09 12:13:10 +08:00
    先用 ID 取范围比如,
    id > 0 && id <= 30000 and status=xxx
    id > 30000 && id <= 60000 and status=xxx
    然后开 100 个线程去查询
    Xusually
        21
    Xusually  
       2023-05-09 12:35:57 +08:00
    只有 5 个状态的话,加索引除了徒增 io 外没啥作用。
    还是尝试走其他查询条件的索引先缩小扫描范围。
    lovelylain
        22
    lovelylain  
       2023-05-09 12:47:47 +08:00 via Android
    只有 5 个状态,状态有索引,A 状态有 299 万数据,其他状态 1 万数据,查其他状态应该能走索引吧,查 A 不会走,如果每个状态都差不多多,可能也不会走。
    ksc010
        23
    ksc010  
       2023-05-09 12:59:28 +08:00
    要不要先把这个几个状态修改为枚举
    zhzy0077
        24
    zhzy0077  
       2023-05-09 13:23:34 +08:00
    每次要查 60 万行数据?是明细结果还是聚合结果?
    60 万行明细数据查出来要做啥?如果是聚合结果的话就按照正常的 OLAP 的实践去做就好了
    wolfie
        25
    wolfie  
       2023-05-09 13:40:37 +08:00
    枚举建索引,增加开销(✔)、提高速度(❌)
    贴表结构,常用查询条件。
    CaptainAmerica
        26
    CaptainAmerica  
       2023-05-09 13:41:04 +08:00
    区分度太低了 索引没啥用的
    bk201
        27
    bk201  
       2023-05-09 13:43:14 +08:00
    分页获取数据,比如一次拉取 100 条。应该没有场景需要一次性拉去 60 多万数据的。
    marine2c
        28
    marine2c  
    OP
       2023-05-09 14:11:23 +08:00
    @zhzy0077
    @bk201 定时任务处理状态异常的,每次会 limit 500 ,但还是很慢
    zhzy0077
        29
    zhzy0077  
       2023-05-09 14:27:48 +08:00
    @marine2c 定时任务多就跑一次?比如 5 分钟跑一回,那你只要遍历过去 5 分钟新增的数据就行了,还是说你每个定时周期都会有 300 万行新数据?
    如果是每个定时周期都会有 300 万行新数据的话,最好和业务对一下能不能用流的方式去做
    Tenlearn
        30
    Tenlearn  
       2023-05-09 14:35:57 +08:00
    @xwayway 括号里的语句不还是通过辅助索引回表吗?跟直接 Status 查记录底层一样的吧?
    Tenlearn
        31
    Tenlearn  
       2023-05-09 14:38:25 +08:00
    没有时间字段吗?这数据量会不会越来越大,传统 DB 起码再加个时间
    Ayanokouji
        32
    Ayanokouji  
       2023-05-09 15:35:59 +08:00
    定时任务加时间范围
    emmmbu
        33
    emmmbu  
       2023-05-09 16:01:49 +08:00
    状态异常的也不会很多吧,状态字段加索引吧
    xuanbg
        34
    xuanbg  
       2023-05-09 19:24:24 +08:00
    @hhjswf 状态最终总归要变成最终的那个值的,所以不是没有区分度。在查中间状态时,反而有特别大的区分度。我估计 OP 查的某个状态是最终状态,所以索引毫无作用。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3107 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 12:35 · PVG 20:35 · LAX 04:35 · JFK 07:35
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.