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
54qyc
V2EX  ›  MySQL

ab 联合索引只 where b 是否会走索引的问题

  •  
  •   54qyc · 2023-08-26 14:13:47 +08:00 · 2088 次点击
    这是一个创建于 455 天前的主题,其中的信息可能已经有所发展或是发生改变。

    mysql 官方的文档乱得一匹,找了半天找到了答案。

    很多人(包括 OP )觉得不走索引是因为 Mysql 文档这么写的: 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). 这里是说 not use an index to perform lookups

    另一处在 Skip Scan Range Access 一节是这么写的:

    CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
    INSERT INTO t1 VALUES
      (1,1), (1,2), (1,3), (1,4), (1,5),
      (2,1), (2,2), (2,3), (2,4), (2,5);
    INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
    INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
    INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
    INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
    ANALYZE TABLE t1;
    
    EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
    

    To execute this query, MySQL can choose an index scan to fetch all rows (the index includes all columns to be selected), then apply the f2 > 40 condition from the WHERE clause to produce the final result set. 这里提到会使用索引扫描。

    所以意思就是会用索引扫描但是不会用索引查找(lookup). 所以索引还有其他用途吗?一会 index scan 一会 use an index to lookup. 还有 use an index to do 其他的吗? 中文里的走索引意思不只指的用索引查询(lookup).

    此外:在这个表中聚集索引叶子结点存的是 id, f1, f2 。 联合索引也是存的 f1, f2, id 。这里为啥会扫联合索引而不是聚集索引。这俩索引在这个表下 size 应该是差不多的吧。

    第 1 条附言  ·  2023-08-26 16:58:04 +08:00
    第 2 条附言  ·  2023-08-26 17:14:47 +08:00

    (f1,f2)看错了以为是二级索引,没想到文档中定义的是聚集索引。那这个文档仍然也没说明是否走二级索引扫描。

    回到标题的问题:假设ab 是二级联合索引, 只where b. 看到兄弟帖子20楼的图上explain 给出的是using index 所以估计还是扫描了联合索引。https://www.v2ex.com/t/655560#

    这里的前提是:对于 explain output type中有index 以及all 这2种分类,index应该就是不包括聚集索引?而all就是聚集索引扫描又叫做全表扫描?

    16 条回复    2023-08-27 11:41:02 +08:00
    Ericcccccccc
        1
    Ericcccccccc  
       2023-08-26 14:21:39 +08:00
    不会, 你简单思考下索引的数据结构, 是怎么被存储/使用就能很快明白, 无法用 b 来索引.
    sunhuawei
        2
    sunhuawei  
       2023-08-26 14:53:13 +08:00
    楼上应该没仔细看正文。
    op 的疑问是:1. 第二个例子中 where f2 > 40 会使用(f1, f2)索引。2. 为什么聚集索引和联合索引差不多,不使用聚集索引。

    以我个人拙见,MySQL can choose an index scan to fetch all rows ,指的就是全表扫描,建表语句中 PRIMARY KEY(f1, f2)显性得创建了主键索引,那它就是这张表的聚集索引。所以 where f2 > 40 就会使用且只会使用 PRIMARY KEY(f1, f2),因为没别的索引了。
    至于索引查找和索引扫描的区别,这是两种策略,有时单独使用有时相互配合,具体可以问下 chatGPT 。
    Immortal
        3
    Immortal  
       2023-08-26 14:59:03 +08:00
    不会
    我记得在高性能 mysql 一书中就有一章讲最左原则来着
    nightfog
        4
    nightfog  
       2023-08-26 15:37:58 +08:00 via iPhone
    “MySQL can choose an index scan to fetch all rows (the index includes all columns to be selected), then apply the f2 > 40 condition from the WHERE clause to produce the final result set”
    这段话应该没有走索引得意思,看上去是全表扫。
    hangszhang
        5
    hangszhang  
       2023-08-26 15:42:54 +08:00
    你可以简单把索引列的字段看作是 Java 里面的 compareTo 方法里面的比较元素,比如 index(a,b),就是先比较 a 的大小,如果 a 相等,再来比较 b 列。MySQL 也是这么做的,( a,b )存储在 MySQL B+ 树里面就是按照这个顺序存储的,找的时候也是按照二分查找在这棵树里面来找。回到这个 case ,查询条件里面没有 b 的话,就无法比较大小,也就无法使用到这棵 B+树进行搜索,也就无法使用到这个索引
    makelove
        6
    makelove  
       2023-08-26 15:58:34 +08:00
    很明显不可能,组合索引是二字段组一起排序的,相当于一整个字串,你字串查找也只能开头 xxx% 才能走索引
    LeeSeoung
        7
    LeeSeoung  
       2023-08-26 16:50:24 +08:00
    MySQL 8.0.13 开始支持 index skip scan 这里有例子 楼上说不行的同学可以更新下,但是这个特性也是有使用条件的,具体看这篇文章吧
    https://blog.csdn.net/weixin_34677764/article/details/116106524
    54qyc
        8
    54qyc  
    OP
       2023-08-26 17:15:44 +08:00
    @sunhuawei 更新下了下 append 。
    me1onsoda
        9
    me1onsoda  
       2023-08-26 17:52:57 +08:00
    用脚指头想一下,应该是不会。ab 联合索引可能是这样的(1,2),(2,1), b 这一列不是有序的,没法走索引
    est
        10
    est  
       2023-08-26 18:53:09 +08:00
    其实也是可能用的。比如 a 是可以遍历的。你可以在 sql 里把 a 所有的值全部 or 一遍。
    54qyc
        11
    54qyc  
    OP
       2023-08-26 19:17:01 +08:00
    @me1onsoda 走索引这里不是指使用索引查找, 还包含扫描索引的情况。
    iseki
        12
    iseki  
       2023-08-26 19:43:13 +08:00 via Android
    可以用到索引,但不是那么简单高效的直接按 B🌳查找,而是更复杂的堆扫描位图扫描。当 planner 认为这样更划算时就会这么做
    sunhuawei
        13
    sunhuawei  
       2023-08-26 21:34:38 +08:00
    @54qyc #8 目前我收集到的主流说法是:1. 当出现`覆盖索引`的情况时,where f2 也许会使用此联合索引 2. 当 f2 列的值非常唯一(即重复值占比高)时,也许会使用此联合索引,仔细想想确实比全表扫描(即聚集索引)快。
    当然,我觉得查询优化器绝非如此简单生硬,应该还有很多文档都没记录的规则。如果是应付面试,我觉得上面两条答案够了。如果真想研究,只有嚼源码这一条路。
    liprais
        14
    liprais  
       2023-08-26 22:04:54 +08:00
    @sunhuawei 不用看源码,optimizer trace 看一下就知道了
    shinyruo2020
        15
    shinyruo2020  
       2023-08-27 01:47:37 +08:00
    其实理论上是可以用到索引的,了解下跳跃索引扫描,利用索引的有序性,就算只有第二列仍然可以利用索引过滤部分数据,只是 mysql 优化器的局限性而已
    ZZ74
        16
    ZZ74  
       2023-08-27 11:41:02 +08:00
    看的真捉急,特意注册了来回复。
    最左匹配没问题。例子也没问题,官网文档也没问题。
    例子中表就两列,组合成主键,select 了所有列的时候选择对主键索引做扫描,就能拿到结果

    所以说走了索引,但是又和平时所谓的走索引不同。
    所以说走了索引,但是又和平时所谓的走索引不同。
    所以说走了索引,但是又和平时所谓的走索引不同。

    英文描述也十分清楚
    “MySQL can choose an index scan to fetch all rows (the index includes all columns to be selected), then apply the f2 > 40 condition from the WHERE clause to produce the final result set”
    在索引包含所有 select 列的情况/前提下(the index includes all columns to be selected)
    会选择扫描索引来拿到所有行 MySQL can choose an index scan to fetch all rows
    然后才会过滤>40 的拿到最终结果。

    至于这种情况下的优化,比如说到的跳跃索引扫描,那要看 mysql 的做法了。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2821 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 13:28 · PVG 21:28 · LAX 05:28 · JFK 08:28
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.