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
gssong
V2EX  ›  MySQL

abc 联合索引查 bc 走不走索引

  •  1
     
  •   gssong · 2020-03-24 08:31:52 +08:00 · 9472 次点击
    这是一个创建于 1684 天前的主题,其中的信息可能已经有所发展或是发生改变。

    abc 联合索引查 bc 走不走索引 我觉得按照最左前缀匹配不走,可是面试官说走,说看看 MySQL 内部优化啥的,有没有大佬解答一下

    56 条回复    2023-08-26 17:18:39 +08:00
    find
        1
    find  
       2020-03-24 08:38:17 +08:00 via iPhone
    不走
    Guozi1989
        2
    Guozi1989  
       2020-03-24 08:39:43 +08:00
    印象中是不走的 坐等大佬解答
    OysterQAQ
        3
    OysterQAQ  
       2020-03-24 08:39:48 +08:00 via iPhone
    索引下推
    2379920898
        4
    2379920898  
       2020-03-24 08:40:27 +08:00
    前缀索引 了解下
    dilu
        5
    dilu  
       2020-03-24 08:47:39 +08:00   ❤️ 1
    不走
    假设列 a:1 2 3 4 5 6
    列 b: a b c d e f
    列 c: X Y Z W T
    以 abc 为维度建立联合索引,真实的索引是这样的
    (1a) (1b) ... (4a) .... (1aW) (1aT) ....
    并且 B 树数第一层节点存储与内存中
    xiaxiaocao
        6
    xiaxiaocao  
       2020-03-24 08:51:21 +08:00   ❤️ 1
    看怎么定义"走不走"了,非前缀匹配的情况下,MySQL 虽然无法直接用联合索引定位,但是可以全部扫描索引,也算是一种使用吧,总比扫表要好。
    qloog
        7
    qloog  
       2020-03-24 09:00:51 +08:00
    必然不走
    labulaka521
        8
    labulaka521  
       2020-03-24 09:01:47 +08:00 via Android
    abc ab a 的走
    skymei
        9
    skymei  
       2020-03-24 09:05:57 +08:00
    前缀原则,不走
    baozijun
        10
    baozijun  
       2020-03-24 09:18:02 +08:00
    不走
    xiaxiaocao
        11
    xiaxiaocao  
       2020-03-24 09:23:29 +08:00
    我再回复一次吧,我想面试官可能要问的是类似覆盖索引这种情况,不一定是用索引树直接定位,但是可以扫描整个索引,这种也算"用到索引"了
    xkeyideal
        12
    xkeyideal  
       2020-03-24 09:26:28 +08:00
    B+树走最左匹配
    ic2y
        13
    ic2y  
       2020-03-24 09:31:04 +08:00
    baozijun
        14
    baozijun  
       2020-03-24 09:32:43 +08:00
    abc,如果 a,b,c 都有索引的话,即 idx_a_b_c,也就是索引按照 a->b->c 的排序方式形成 B+树,可以使用 a and b and c,依次走 a b c 的索引. 如果只是 a c 不走 b 的话只走 a 的索引,因为 a 值符合条件的话会接着走 b,如果没有 b 就没法接着走下去.
    yuhuigreed
        15
    yuhuigreed  
       2020-03-24 09:39:19 +08:00
    最左前缀匹配原则,abc 建索引的时候是基于先 a,然后基于 b 、c 有序的,查询时候想走上索引得也按照这个顺序,bc 是走不上索引的
    fancy111
        16
    fancy111  
       2020-03-24 09:40:17 +08:00   ❤️ 1
    一群人瞎几把猜,跑一次不就知道了?
    ABC 索引查 BC 的时候虽然没有用到最左侧索引覆盖,但是还是走的索引。extra 显示是 using where 。
    RRRoger
        17
    RRRoger  
       2020-03-24 09:41:02 +08:00
    加个字段 col_reverse, 存 cba [旺柴]
    skymei
        18
    skymei  
       2020-03-24 09:47:11 +08:00
    SELECT * FROM tbl_name WHERE col1=val1;
    SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

    SELECT * FROM tbl_name WHERE col2=val2;
    SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

    If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but do not use an index to perform lookups because (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).
    ahsjs
        19
    ahsjs  
       2020-03-24 09:53:06 +08:00
    避免打脸,explain 跑一次,b,c type 为 ALL 明细全表了,还叫用索引?
    dovme
        20
    dovme  
       2020-03-24 09:57:23 +08:00
    这个代表走了还是没走?
    https://sm.ms/image/PKq9yu8QGxtv7s3
    gssong
        21
    gssong  
    OP
       2020-03-24 09:59:01 +08:00
    @fancy111
    可是 Using where: 仅仅表示 MySQL 服务器在收到存储引擎返回的记录后进行“后过滤”( Post-filter )。 不管 SQL 语句的执行计划是全表扫描( type=ALL)或非唯一性索引扫描( type=ref)
    raysonlu
        22
    raysonlu  
       2020-03-24 10:00:06 +08:00
    项目实践中发现,你的搜索条件只有 ABC 的时候,你怎么写顺序都没问题,没有什么最左原则匹配,但如果出现了一个条件 D 后,就不一定了,有时候 BCD 走索引,有时候不走,其实这些都是 MYSQL 解析语句的时候有个步骤是优化语句,看你有无开这个,尽管开了也看它是否人工智能(zhang)帮你优化到尽量用索引,这视乎你的语句复杂度和建立的索引种类,2200 年了,MYSQL 简单的优化还是有的。
    ahsjs
        23
    ahsjs  
       2020-03-24 10:03:01 +08:00
    @raysonlu 说的是没有 A 的时候,讨论的应该不是优化这个
    gssong
        24
    gssong  
    OP
       2020-03-24 10:06:31 +08:00
    @xiaxiaocao 我觉得可能是理解错面试官意思了,他可能问的索引包括这个联合索引之外的索引
    gssong
        25
    gssong  
    OP
       2020-03-24 10:11:12 +08:00
    @ahsjs 和楼下的结果 type 是 index
    xsm1890
        26
    xsm1890  
       2020-03-24 10:14:31 +08:00
    最左前缀原则
    fancy111
        27
    fancy111  
       2020-03-24 10:14:32 +08:00
    @gssong 是的,所以这只是文字游戏,实际上没有用到我们说的索引,只是有可能优化得好查询的时候不是全表,而是之前留下的缓存表。
    littlewing
        28
    littlewing  
       2020-03-24 10:18:01 +08:00
    走索引,但是不走索引
    littlewing
        29
    littlewing  
       2020-03-24 10:18:40 +08:00
    @littlewing 顺序扫描索引,你可以说是走了,也可以说是没走。这样玩文字游戏的面试官直接扇他巴掌
    opengps
        30
    opengps  
       2020-03-24 10:20:19 +08:00
    回归下索引本身,想想大字典,因为找了 a 才能找到 b,然后才能找到 c,所以不从头开始的索引都没法使用
    pangsq
        31
    pangsq  
       2020-03-24 10:23:32 +08:00
    explain 一把试试。
    1. 用的 mysql 5.6.40 ,innodb
    2. create table abcd (a varchar(255), b varchar(255), c varchar(255), d varcahr(255));
    3. alter table abcd add index abc(a,b,c);
    4. explain select a,b from abcd; # using index
    5. explain select b,c from abcd; # using index
    6. explain select c,d from abcd; # null
    EmdeBoas
        32
    EmdeBoas  
       2020-03-24 10:24:34 +08:00   ❤️ 1
    @dovme 走了,因为二级索引里面已经包含了所有要的字段数据,所以去扫二级索引的树,不用回表;你改一下表结构,新增一些字段结果就不同了,不会命中索引
    baozijun
        33
    baozijun  
       2020-03-24 10:28:55 +08:00
    我错了...看了下文档,发现只用 bc 的话还是会遍历索引 叶子节点进行查找,找到了 b 节点会顺着往下找到 c,所以还是走了索引. @dovme 我感觉 联合索引不用加上主键,因为默认生成的主键(聚簇)索引已经覆盖了.
    ahsjs
        34
    ahsjs  
       2020-03-24 10:30:09 +08:00
    @pangsq 大佬 d varchar 都打错了,确定试了?
    gssong
        35
    gssong  
    OP
       2020-03-24 10:31:37 +08:00
    表中有 abc 三个字段,都是 int 类型,执行出来 type=index extra 为 Using where; Using index
    表中有 abcd 四个字段,都是 int 类型,执行出来 type = all extra 为 Using where
    aliipay
        36
    aliipay  
       2020-03-24 10:39:30 +08:00
    @gssong 是 index 还是 all 和 select 内容有关的
    xman99
        37
    xman99  
       2020-03-24 10:40:37 +08:00
    包含 a 的话走, 如果是 innodb 引擎,b 存在普通二级索引, 也可以使用到索引的。abc 联合索引,需要包含 a 字段才会生效的
    dovme
        38
    dovme  
       2020-03-24 10:43:51 +08:00
    @EmdeBoas #32 你是对的,我新增一个字段 d,然后还是执行刚才的 sql,没有走索引.
    pangsq
        39
    pangsq  
       2020-03-24 10:46:41 +08:00
    @ahsjs == 见谅,先在 mysql 里跑的后敲在这的,没复制
    awanganddong
        40
    awanganddong  
       2020-03-24 11:05:56 +08:00
    联合索引,给我感觉就是多字段联合排序,先按 A 排,再按 B 排。但是实际上,这个问题比较复杂,必须由示例而定,毕竟查询优化器会选择,如果工作上遇到还是 explain 一波。

    有些跑题了
    jamesz
        41
    jamesz  
       2020-03-24 11:49:06 +08:00 via iPhone
    索引跳跃扫描算法可以不走前缀
    qW7bo2FbzbC0
        42
    qW7bo2FbzbC0  
       2020-03-24 12:09:24 +08:00
    如果 A 是主键的话,可以走索引,如果不是,不可
    gy123
        43
    gy123  
       2020-03-24 12:21:57 +08:00
    根据 select 字段决定,如果查字段只有索引字段,则可能走,因为不需要回表直接遍历索引树即可,避免查询查询主表中大量数据,其实还是看内部优化器
    Jooooooooo
        44
    Jooooooooo  
       2020-03-24 12:47:13 +08:00
    答案是不走

    只查 bc 不需要回表也能叫走索引吗? 是这么定义的吗?
    cholerae
        45
    cholerae  
       2020-03-24 13:04:53 +08:00
    有可能走啊,mysql 8.0 有 index skip scan 。
    xiaowangge
        46
    xiaowangge  
       2020-03-24 13:16:19 +08:00
    MySQL 8.0 doc https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html



    SELECT * FROM tbl_name WHERE col1=val1;
    SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

    SELECT * FROM tbl_name WHERE col2=val2;
    SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;



    If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but do not use an index to perform lookups because (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

    如果在(col1,col2,col3)上存在索引,则只有前两个查询使用该索引。 第三和第四个查询确实涉及索引列,但不使用索引来执行查找,因为(col2)和(col2,col3)不是(col1,col2,col3)的最左前缀。
    nekoneko
        47
    nekoneko  
       2020-03-24 14:48:39 +08:00
    走了也没走
    如果有 abcd 列,查 bc 用 bc 做 where 条件,那么会遍历索引
    如果查 bcd 列,用 bc 做 where 条件,那么不走索引
    cxshun
        48
    cxshun  
       2020-03-24 16:47:36 +08:00   ❤️ 6
    要分几种情况:
    1 、假设你的表就 a,b,c 三个字段,那么你建了(a,b,c)这样的索引,然后按 b,c 搜索(即 select * from t where b = xx and c = xx ),这样会走
    为什么走索引呢,主要是因为查询的所有字段在索引中都可以找到,根本就不需要回表,直接覆盖索引了
    2 、假设你的表有 a,b,c,d 四个字段,那么你建了(a,b,c)这样的索引,然后按 b,c 搜索(即 select * from t where b = xx and c = xx ),那么此时就不会走索引了,因为走了索引也需要回表,对性能优化没太大用处,当然,你可以把索引改为(a,b,c,d ),然后又可以命中了。
    vindurriel
        49
    vindurriel  
       2020-03-24 17:47:37 +08:00 via iPhone
    即使 mysql 有优化 做功能也不应该依赖这个 老老实实建好索引才是正确的选择 否则换个 db 甚至换个版本就出问题
    lucky215
        50
    lucky215  
       2020-03-24 18:21:26 +08:00
    @cxshun 说的靠谱
    再补充下,如果在 a,b,c,d 四个字段的情况下,select b,c from t where b = xx and c = xx,也是会走索引的,因为还是覆盖到了
    CRVV
        51
    CRVV  
       2020-03-24 18:22:02 +08:00
    这个问题经常有人问,标准答案是不走。

    至于到底走不走,必须要说清楚是哪个数据库,哪个版本,表里有多少行,每个字段是什么类型,每个字段的取值范围。基本上没人知道这些问题的答案,所以 PostgreSQL 的文档说的是

    This index could in principle be used for queries that have constraints on b and/or c with no constraint on a — but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index.

    拿文档来回答这个问题,那就是可以走索引( This index could in principle be used )

    具体来说,假设三个字段里,a 是性别,b 是年龄,c 是身高。
    你可以把 select * from table where b = 18 and c = 170 拆开成 select * from table where a = '男' and b = 18 and c = 170 union all select * from table where a = '女' and b = 18 and c = 170
    这样就可以走索引了,很有可能比不走索引要快。
    数据库可以帮你这样优化,但到底会不会优化呢? in most cases the planner would prefer a sequential table scan.

    https://www.postgresql.org/docs/12/indexes-multicolumn.html
    1424659514
        52
    1424659514  
       2020-03-24 18:22:11 +08:00
    @cxshun 老哥牛批
    mitoop
        53
    mitoop  
       2020-03-25 00:18:19 +08:00
    @cxshun 正解 同意 @vindurriel 的说法 老老实实建立好索引 即使用了覆盖索引 还是会 type=index 比 type=all 好那么一点 如果就 abc 三个字段 和 type=all 几乎一样了
    xiaowangge
        54
    xiaowangge  
       2020-03-28 19:46:28 +08:00
    应该换个更详细的问题,表结格和 explain 分析图:

    参考这个:

    mysql 索引最左匹配原则的理解
    https://www.zhihu.com/question/36996520
    54qyc
        55
    54qyc  
       2023-08-26 00:18:39 +08:00
    @CRVV 但是这适用于 mysql 吗?
    54qyc
        56
    54qyc  
       2023-08-26 17:18:39 +08:00
    @dovme type = index 扫描索引。extra= using index 不用回表因为 abc 二级索引有全部数据。extra= using where 用 where 子句过滤扫描的行。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   986 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 20:18 · PVG 04:18 · LAX 13:18 · JFK 16:18
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.