› 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 · Mar 3, 2023 · 3987 views
    This topic created in 1355 days ago, the information mentioned may be changed or developed.
    请教一个 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 replies  •  2023-08-30 18:45:40 +08:00
    wetalk
    wetalk
    Mar 3, 2023
    1
    in 查询,条件很多吗
    yxjn
    yxjn
    OP
    Mar 3, 2023
    2
    @wetalk 有可能会很多
    LeegoYih
    LeegoYih
    Mar 3, 2023❤️1
    3
    分别建 2 个索引,然后打开优化器看看执行计划,经验之谈不如手动试一试
    optional
    optional
    Mar 3, 2023 via iPhone
    4
    单独 y 就行
    wjfz
    wjfz
    Mar 3, 2023
    5
    导两张表分别 explain 一下得到的答案最确定。
    keenkiller
    keenkiller
    Mar 3, 2023 via Android
    6
    y,x 吧

    如果 in 后面很多的话,即使区分度高数据集可能也很大…explain 的结果跟数据和查询条件都有关,又不是不变的…
    iffi
    iffi
    Mar 3, 2023
    7
    # 一、多列索引字段顺序问题
    1 、尽可能将需要做范围查询的列放到索引的后面,以便优化器能使用尽可能多的索引列。
    2 、选择区分度小的字段在前面,例如 idx_sex_city(sex,city)
    # 二、减小 where in() 条件数据量,需要注意控制 IN 的中值的个数,建议 in 数量小于 200 ,(参数 eq_range_index_dive_limit)
    yxjn
    yxjn
    OP
    Mar 3, 2023
    8
    @iffi 为什么是区分度小的放前面,不应该是区分度高的放前面么
    lovelylain
    lovelylain
    Mar 3, 2023 via Android
    9
    为什么没人选(x,y),这样不是可以先筛掉非 x 的(虽然数量不多),再按 y 筛选吗,单看这条语句,不至于比只用 y 有副作用吧?
    wangyzj
    wangyzj
    Mar 3, 2023
    10
    x.y 把
    explain 一下
    先重建一个新的
    wetalk
    wetalk
    Mar 3, 2023
    11
    @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
    liuxu
    Mar 3, 2023
    12
    普通情况,简单的话就(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
    james2013
    Mar 3, 2023
    13
    单独建 y 就可以了
    区分度很低放到索引里是多余的
    vagusss
    vagusss
    Mar 3, 2023
    14
    为啥非得联合索引
    ooToo
    ooToo
    Mar 3, 2023
    15
    select * 用不到索引下推.
    对于你的 sql ( x,y )还是( y,x )区别不大, 甚至(y)也可以
    imokkkk
    imokkkk
    Mar 3, 2023
    16
    三星索引来看,y 建索引属于第 3 颗星,比重更大,效果更好
    yxjn
    yxjn
    OP
    Mar 3, 2023
    17
    @ooToo 为什么 select *用不到索引下推,又不是回表
    yxjn
    yxjn
    OP
    Mar 3, 2023
    18
    @jobmailcn 我和你一个想法,但是没有理论支撑。explain 也是能命中 x,y 索引的。
    UN2758
    UN2758
    Mar 3, 2023
    19
    @yxjn 我也感觉很不理解,select *还是具体 column 只会影响覆盖索引,x ,y 前后顺序不影响索引下推发挥作用吧。有关系的是优化器在选择先 x 后 y 还是 yx
    liuxu
    liuxu
    Mar 3, 2023
    20
    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
    54qyc
    Aug 30, 2023
    21
    @yxjn
    @ooToo
    select * 可以用到索引下推

    @yxjn OP 最终用的啥,这里(x,y)用到索引下推肯定比单一索引好吧?
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Privacy   ·   Solana   ·   946 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 47ms · UTC 21:06 · PVG 05:06 · LAX 13:06 · JFK 16:06
    ♥ Do have faith in what you're doing.