V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
yxjn
V2EX  ›  MySQL

请教一个 MYSQL 联合索引字段顺序的问题

  •  
  •   yxjn · 2023-03-03 11:00:45 +08:00 · 2494 次点击
    这是一个创建于 629 天前的主题,其中的信息可能已经有所发展或是发生改变。
    请教一个 MySQL 联合索引方案
    假设有一个表
    table a
    column x,y,.....

    现在有一个 sql ,select * from a where x = ? and y in (?,?,?,...)

    x 的区分度很低,y 的区分度很高。

    联合索引是建( x,y )还是( y,x )。

    考虑到区分度高索引列在前面的原则似乎是因该是( y,x )。但是考虑到索引下推,似乎又应该是( x,y )?
    21 条回复    2023-08-30 18:45:40 +08:00
    wetalk
        1
    wetalk  
       2023-03-03 11:09:20 +08:00
    in 查询,条件很多吗
    yxjn
        2
    yxjn  
    OP
       2023-03-03 11:20:33 +08:00
    @wetalk 有可能会很多
    LeegoYih
        3
    LeegoYih  
       2023-03-03 11:32:32 +08:00   ❤️ 1
    分别建 2 个索引,然后打开优化器看看执行计划,经验之谈不如手动试一试
    optional
        4
    optional  
       2023-03-03 11:34:37 +08:00 via iPhone
    单独 y 就行
    wjfz
        5
    wjfz  
       2023-03-03 11:37:14 +08:00
    导两张表分别 explain 一下得到的答案最确定。
    tairan2006
        6
    tairan2006  
       2023-03-03 11:47:55 +08:00 via Android
    y,x 吧

    如果 in 后面很多的话,即使区分度高数据集可能也很大…explain 的结果跟数据和查询条件都有关,又不是不变的…
    iffi
        7
    iffi  
       2023-03-03 12:08:15 +08:00
    # 一、多列索引字段顺序问题
    1 、尽可能将需要做范围查询的列放到索引的后面,以便优化器能使用尽可能多的索引列。
    2 、选择区分度小的字段在前面,例如 idx_sex_city(sex,city)
    # 二、减小 where in() 条件数据量,需要注意控制 IN 的中值的个数,建议 in 数量小于 200 ,(参数 eq_range_index_dive_limit)
    yxjn
        8
    yxjn  
    OP
       2023-03-03 12:16:25 +08:00
    @iffi 为什么是区分度小的放前面,不应该是区分度高的放前面么
    lovelylain
        9
    lovelylain  
       2023-03-03 12:18:17 +08:00 via Android
    为什么没人选(x,y),这样不是可以先筛掉非 x 的(虽然数量不多),再按 y 筛选吗,单看这条语句,不至于比只用 y 有副作用吧?
    wangyzj
        10
    wangyzj  
       2023-03-03 12:49:01 +08:00
    x.y 把
    explain 一下
    先重建一个新的
    wetalk
        11
    wetalk  
       2023-03-03 13:10:31 +08:00
    @yxjn 我这样写,不知道你能不能看懂,单独为 y 建索引
    select a.* from a join
    (
    SELECT ?1 as y
    union all
    SELECT ?2 as y
    union all
    SELECT ?3 as y ...
    ) as t on a.y = t.y and a.x = ?
    liuxu
        12
    liuxu  
       2023-03-03 13:16:44 +08:00
    普通情况,简单的话就(y,x)

    其他情况,你可能需要看下“基于成本的优化”、“复合索引”和“IN 子查询”相关内容

    https://www.liuquanhao.com/posts/mysql%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96%E5%99%A8%E4%BD%BF%E7%94%A8%E6%8C%87%E5%8D%97/


    除非 sql 很复杂,否则楼上将 in 手动重写成 join 其实没必要,mysql 优化器可能会自动重写
    james2013
        13
    james2013  
       2023-03-03 13:45:37 +08:00
    单独建 y 就可以了
    区分度很低放到索引里是多余的
    vagusss
        14
    vagusss  
       2023-03-03 14:26:12 +08:00
    为啥非得联合索引
    ooToo
        15
    ooToo  
       2023-03-03 14:31:43 +08:00
    select * 用不到索引下推.
    对于你的 sql ( x,y )还是( y,x )区别不大, 甚至(y)也可以
    imokkkk
        16
    imokkkk  
       2023-03-03 14:50:22 +08:00
    三星索引来看,y 建索引属于第 3 颗星,比重更大,效果更好
    yxjn
        17
    yxjn  
    OP
       2023-03-03 16:28:44 +08:00
    @ooToo 为什么 select *用不到索引下推,又不是回表
    yxjn
        18
    yxjn  
    OP
       2023-03-03 16:29:30 +08:00
    @jobmailcn 我和你一个想法,但是没有理论支撑。explain 也是能命中 x,y 索引的。
    UN2758
        19
    UN2758  
       2023-03-03 18:08:12 +08:00
    @yxjn 我也感觉很不理解,select *还是具体 column 只会影响覆盖索引,x ,y 前后顺序不影响索引下推发挥作用吧。有关系的是优化器在选择先 x 后 y 还是 yx
    liuxu
        20
    liuxu  
       2023-03-03 18:51:42 +08:00
    select * 会被 mysql 优化器重写成所有列,没什么问题

    之所以不用(x,y)是因为 x 区分度很低,扫 x 时索引页占用更多内存,而且 x 区分度低到一个层次的话,优化器可能直接走全表扫描了
    这里有一份和区分度基数有关的数据
    https://www.liuquanhao.com/posts/mysql%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96%E5%99%A8%E4%BD%BF%E7%94%A8%E6%8C%87%E5%8D%97/#%E4%B8%B4%E6%97%B6%E8%AE%A1%E5%88%92
    54qyc
        21
    54qyc  
       2023-08-30 18:45:40 +08:00
    @yxjn
    @ooToo
    select * 可以用到索引下推

    @yxjn OP 最终用的啥,这里(x,y)用到索引下推肯定比单一索引好吧?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2464 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 15:56 · PVG 23:56 · LAX 07:56 · JFK 10:56
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.