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

覆盖索引,但是没有最左前缀匹配,性能如何

  •  1
     
  •   hackingwu ·
    hackingwu · 327 天前 · 1955 次点击
    这是一个创建于 327 天前的主题,其中的信息可能已经有所发展或是发生改变。

    有一个组合索引 idx on t1(c1, c2); 查询语句 select id, c1 from t1 where c2 = xxx; 这里会用到覆盖索引,但是却是用 c2 做条件查询,这样会全索引扫描吗,数据量大了,性能如何?

    22 条回复    2020-09-04 17:34:15 +08:00
    zhouhu
        1
    zhouhu   327 天前
    这个查询不会用到你所说的索引
    optional
        2
    optional   327 天前 via iPhone
    这个会用覆盖索引?
    hackingwu
        3
    hackingwu   327 天前
    @zhouhu 肯定会呀 ,你 explain 一下就知道了,走覆盖索引呀
    useben
        4
    useben   327 天前
    不走索引, 就是全表扫描的性能
    qianProgrammer
        5
    qianProgrammer   327 天前
    type 是 index 吧,扫描了整个索引树,只是正好匹配到了你的条件,不如最左匹配的效率高,但也比 all 全表扫描快
    Jooooooooo
        6
    Jooooooooo   327 天前
    这和没索引一样
    wangritian
        7
    wangritian   327 天前
    覆盖索引应该是指索引树上包含了本次查询的全部字段,无需回表,你的 sql 是符合的
    但查询条件 c2 不在 t1 的开头,所以走全表扫描
    yukong
        8
    yukong   327 天前
    这不是全表扫描了吗
    CODEWEA
        9
    CODEWEA   327 天前
    怎么可能呢
    wps353
        10
    wps353   327 天前
    在 8.0.13 以前的话用不到覆盖索引,在 8.0.13 后,如果说 a 的 Cardinality 比较低的话,有可能通过 skip index scan 使用到。
    zhangysh1995
        11
    zhangysh1995   327 天前
    根据文档应该是全表扫描 或者尝试选过滤高的

    If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization (see Section 8.2.1.3, “Index Merge Optimization”), or attempts to find the most restrictive index by deciding which index excludes more rows and using that index to fetch the rows.

    https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html
    hackingwu
        12
    hackingwu   327 天前
    @wangritian 我的 c1,c2 信息都在索引里,不会再回表啦。
    hackingwu
        13
    hackingwu   327 天前
    @zhangysh1995 我这不是 single-column index, 我这是组合索引呀 ,不是你文档描述的场景
    SjwNo1
        14
    SjwNo1   327 天前
    直接没走你的联合索引吧 自建索引树都没走怎么利用覆盖索引(有点忘了
    wangritian
        15
    wangritian   327 天前
    @hackingwu 突然发现自己上面的描述不对,应该是走了全索引树扫描。我猜优化器发现你的 t1 索引包含了全部需求字段,所以在 t1 索引树上做全部扫描,而没有去主键索引树(原表)做扫描,因为 t1 树的字节大小一定小于原表,更节省磁盘 I/O 。所以你的 explain 结果用到了覆盖索引。说的不对请指正。
    changdy
        16
    changdy   327 天前
    心疼题主....那么多人都说直接走全表,不知道有没有怀疑人生...

    狗尾续貂 , 也请教各位一个问题 , mysql 的分区表如何 ? 目前系统业务量比较大, 想用 mysql 本身的分区表实现类似分表功能.
    RedisMasterNode
        17
    RedisMasterNode   327 天前
    心疼楼主...还有这么多人说走全表,一定要坚持有自己的观点呀^_^特别是你觉得有足够的东西支撑观点的时候

    这个查询毫无疑问(正常情况下)会走你的(c1, c2)索引,前提是你的表可能得不止有 id, c1, c2 这 3 个字段,理由是主键索引的每个页能够存放下的数据行比(c1, c2)索引每个页能存放下的数据行更少,如果扫描主键获取结果,需要扫过更多的数据页,如果扫(c1, c2)索引,尽管没有用上"索引"的查询特性,但是可以通过扫更少的页得到正确结果。

    性能的差距 = 主键索引体积 vs (c1, c2)索引体积,当你的表字段越多,越大的时候,差距越明显;反例则是如果表只有 id, c1, c2 三个字段的时候,扫描的时间理论上应该是非常接近的,并且优化器在选择上两个索引都有可能选择使用
    RedisMasterNode
        18
    RedisMasterNode   327 天前
    em 补充一点,在字段少(但不仅有 id, c1, c2 )的情况下,如果 c1, c2 是 varchar 之类的类型,未必会真的选择(c1, c2)索引进行索引扫描,因为选择使用什么查询路径是根据 cardinality 值来决定的,这个值是通过定期对页进行采样、统计的结果,是个预估值,如果不巧 mysql 记录的主键索引和(c1, c2)索引的预估值与实际不符,那有可能会采取(实际性能更差)的主键扫描来执行
    maigebaoer
        19
    maigebaoer   327 天前 via Android
    sql 已经是黑箱子了😂以 explain 为准吧!猜测是因为索引已经包含了所有需要的信息,直接从索引取值了。
    pkoukk
        20
    pkoukk   327 天前
    不会走全表扫描(主键扫描),但是会走你这个索引的全索引扫描。
    怎么说呢,肯定比全表强,但是到底强多少得看你这个索引的区分度
    zhangysh1995
        21
    zhangysh1995   326 天前
    @hackingwu 你应该看下上线文,这里是说组合索引用不了,如果有 single column 索引会用的。和你的情况没有冲突。
    zhouhu
        22
    zhouhu   326 天前
    @hackingwu 不好意思,我说错了。试了一下,确实是可以的。不过实际上也是不推荐这种用法吧。如果索引能全部加载进内存,速度应该还行。
    关于   ·   帮助文档   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   3272 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 03:59 · PVG 11:59 · LAX 20:59 · JFK 23:59
    ♥ Do have faith in what you're doing.